Eaten by a Grue's questions

O MySQL se recusa a iniciar e eu só consegui iniciar em modo somente leitura usando innodb_force_recovery = 6. Todas as respostas que encontrei sobre esse tópico sugerem fazer um backup, deletar ibdata e ib_logfiles e então deixá-los serem recriados:

• https://serverfault.com/questions/592793/mysql-crashed-and-wont-start-up
• innodb_force_recovery quando corrupção do InnoDB
• https://stackoverflow.com/questions/41997197/mysql-data-recovery-with-innodb

No meu caso, como innodb-file-per-table está habilitado, estou pensando se também preciso excluir arquivos como /var/lib/mysql/MY_DATABASE/table.ibd?este. Além disso, como o registro de refazer está desabilitado, não há nenhum arquivo de log para remover.

O erro específico que recebi foi este:

[InnoDB] O servidor foi encerrado quando o redo logging do InnoDB foi desabilitado. Os arquivos de dados podem estar corrompidos. Você pode tentar reiniciar o banco de dados com innodb_force_recovery=6

Neste ponto, consegui iniciar no modo somente leitura e fiz um backup de todas as tabelas que preciso. O que especificamente devo excluir? Ou há uma abordagem melhor para reconstruir completamente essas tabelas?

Estou pensando em simplesmente desinstalar o MySQL completamente e limpar o diretório inteiro, mas estou esperando feedback aqui se houver uma abordagem mais simples.

Li recentemente sobre uma configuração chamada Percona Server max_binlog_filesque restringe o número total de arquivos binlog. Isso é exatamente o que eu preciso. Atualmente, o meu binlog_expire_logs_secondsestá definido para 3 dias, o que funciona muito bem 99% do tempo. No entanto, atualmente estou envolvido em um projeto que executa uma grande quantidade de consultas e os binlogs consumiram mais de 70 GB em apenas algumas horas. Nesse caso, eu realmente preciso limitar o número de arquivos.

Existe alguma configuração nativa do MySQL para conseguir isso? Qual é a melhor maneira de restringir o espaço total em disco consumido pelos binlogs?

Eu gostaria de classificar todas as linhas em uma tabela, uma por categoria em um loop. Por exemplo, dados os seguintes dados:

+---------+-------------+
| item_id | category_id |
+---------+-------------+
| 4013738 |        1102 |
| 4016142 |        1102 |
| 4027380 |        1102 |
| 4029166 |        1014 |
| 4031335 |        1125 |
| 4031984 |        1014 |
| 4031986 |        1014 |
| 5034654 |        1123 |
| 5034656 |        1125 |
| 5034662 |        1125 |
| 5034735 |        1109 |
| 5034736 |        1109 |
| 5034737 |        1109 |
| 5040226 |        1123 |
| 5040227 |        1123 |
+---------+-------------+

O conjunto de resultados desejado deve ser assim:

+---------+-------------+
| item_id | category_id |
+---------+-------------+
| 4029166 |        1014 |
| 4013738 |        1102 |
| 5034735 |        1109 |
| 5034654 |        1123 |
| 4031335 |        1125 |
| 4031984 |        1014 |
| 4016142 |        1102 |
| 5034736 |        1109 |
| 5040226 |        1123 |
| 5034656 |        1125 |
| 4031986 |        1014 |
| 4027380 |        1102 |
| 5034737 |        1109 |
| 5040227 |        1123 |
| 5034662 |        1125 |
+---------+-------------+

Os item_idem categorias consecutivas também devem ser ordenados. Se algumas categorias contiverem mais itens do que outras, o ciclo deve continuar com a mesma lógica com as linhas restantes.

Esta seria uma tarefa bastante trivial em uma linguagem de script como php, mas eu não posso pensar em como realizá-la em sql.

Aqui está um violino db com dados de amostra: https://www.db-fiddle.com/f/ioZzvoQfnSNiowe6Rp7QP5/0

No MySQL, estou tentando algo como a seguinte consulta, mas não obtendo os resultados desejados:

SELECT ROUND(t.price / t.qty, IF(qty > 1, 4, 2)) AS unit_cost
FROM (
    SELECT 0.10 AS price, 1 AS qty
    UNION
    SELECT 2.60 AS price, 25 AS qty
) t

O que eu gostaria é:

+-----------+
| unit_cost |
+-----------+
|  0.10     |
|  0.1040   |
+-----------+

Mas por algum motivo o resultado é:

+-----------+
| unit_cost |
+-----------+
|  0.100000 |
|  0.104000 |
+-----------+

Curiosamente, isso funciona:

SELECT ROUND(0.10000, IF (1=1, 2, 4));

Então eu sei que o arredondamento condicional é possível. Como posso alcançar o conjunto de resultados desejado?

Estou realizando backups regulares usando mysqldump em um esquema que consiste inteiramente em tabelas InnoDB. A documentação do mysqldump tem a dizer sobre --single-transaction:

A opção --single-transaction e a opção --lock-tables são mutuamente exclusivas porque LOCK TABLES faz com que quaisquer transações pendentes sejam confirmadas implicitamente.

No entanto, aproximadamente 1 bilhão de postagens e respostas de blog aqui (e em outros lugares da rede de troca de pilha) recomendam:

mysqldump --single-transaction --skip-lock-tables my_database > my_database.sql

Se as duas opções forem mutuamente exclusivas, acho que especificar --single-transactiondeve ser suficiente. No entanto, os documentos também afirmam isso sobre --opt:

Esta opção, habilitada por padrão, é um atalho para a combinação de --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick -- set-charset.

O que me leva a acreditar que --lock-tablesestá ativado por padrão.

Preciso especificar --skip-lock-tablesjunto com --single-transactionou apenas definir o último o suficiente para garantir que as tabelas não sejam bloqueadas durante um despejo?

Por que a conversão desse resultado REGEXP_SUBSTR()para um DECIMAL falha?

SELECT
    REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted,
    CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS DECIMAL(8,2)) AS cost_1,
    CAST((SELECT _extracted) AS DECIMAL(8,2)) AS cost_2,
    CAST((SELECT _extracted) * 1 AS DECIMAL(8,2)) AS cost_3,
    CAST('14.18' AS DECIMAL(8,2)) AS cost_4;

+------------+--------+--------+--------+--------+
| _extracted | cost_1 | cost_2 | cost_3 | cost_4 |
+------------+--------+--------+--------+--------+
| 14.18      |  14.00 |  14.00 |  14.18 |  14.18 |
+------------+--------+--------+--------+--------+

Lançar uma string simples como cost_4parece funcionar. Multiplicar o REGEXP_SUBSTR()resultado por 1também parece funcionar. Mas simplesmente lançar o resultado como eu fiz cost_1e cost_2não produzir a versão correta de ponto fixo do _extracted.

Estranhamente, no meu aplicativo, usando a referência anterior, como fiz, cost_2realmente produz o resultado correto. Não foi possível reproduzir em outro lugar, mas achei que vale a pena mencionar.

Isso será difícil de reproduzir, mas espero que alguém possa esclarecer meu problema com base na lógica envolvida. Depois de ter alguns problemas intermitentes com deadlocks durante uma transação, implementei uma estratégia de repetição que é mais ou menos assim (PHP):

public function execute_query($sql) {
    $try     = 1;
    $log_msg = '';
    while (true) {
        $query     = $this->link->query($sql);
        $error_no  = $this->link->errno;
        $error_msg = $this->link->error;
        if (!$error_no){
            if ($try > 1) {
                $this->debug_log->write('Deadlock Succeeded after ' . $try . ' Tries ::: ' . $sql);
            }
            return $query;
        } else {
            $log_msg = 'Error: ' . $error_msg . ' ::: Error No: ' . $error_no . ' ::: ' . ($try > 1 ? $try . ' Tries ::: ' : '') . $sql;
            $this->debug_log->write($log_msg);
            if ($error_no == 1213 && $try < self::DEADLOCK_RETRY) {
                // retry when deadlock occurs
                sleep($try * 2);
                $try++;
            } else {
                throw new ErrorException($log_msg);
                exit();
            }
        }
    }
}

Isso parece ter tido exatamente o efeito oposto que eu esperava - que é que parece que a primeira metade da minha transação é revertida, mas a segunda metade é confirmada.

Por exemplo:

START TRANSACTION;
Query 1
Query 2
Query 3 (deadlock occurs, query gets retried and succeeds on 2nd attempt)
Query 4
Query 5
COMMIT;

No final de tudo isso, fico com as Consultas 3, 4 e 5 sendo executadas com sucesso, mas as Consultas 1 e 2 não. Não entendo como isso é possível, mas já aconteceu duas vezes e não consegui reproduzir o impasse para testar ou desenvolver uma estratégia de trabalho.

Alguém pode explicar por que tentar novamente uma consulta com falha em uma transação InnoDB faria com que metade da transação fosse revertida e a outra metade fosse confirmada?

ATUALIZAÇÃO (tl;dr;) :

Eu arquivei um relatório de bug aqui: https://bugs.mysql.com/bug.php?id=99593 que já foi reconhecido e uma solução alternativa fornecida. Veja a resposta abaixo para detalhes.

Certas consultas parecem estar lutando no MySQL 8.0.20 e eu estou querendo saber se alguém pode apontar para alguma resolução possível. No momento eu tenho o servidor antigo funcionando, ainda na versão 5.7.30, então é fácil fazer A/B dos resultados de desempenho. Ambos os servidores possuem 32 GB de RAM, configuração quase idêntica e todas as tabelas são InnoDB. Aqui estão algumas das configurações (relevantes):

innodb_flush_log_at_trx_commit = 0
innodb_flush_method            = O_DIRECT
innodb_file_per_table          = 1
innodb_buffer_pool_instances   = 12
innodb_buffer_pool_size        = 16G
innodb_log_buffer_size         = 256M
innodb_log_file_size           = 1536M
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_io_capacity             = 5000
innodb_thread_concurrency      = 0

Exemplo 1:

SELECT DISTINCT vehicle_id, submodel_id, store_id
FROM product_to_store pts
    JOIN product_to_vehicle ptv USING (product_id)
WHERE vehicle_id != 0 AND pts.store_id = 21;

Essa consulta produz a seguinte explicação:

MySQL 8.0.20 (a consulta leva 24 segundos):

+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys                             | key                      | key_len | ref            | rows   | filtered | Extra                        |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
|  1 | SIMPLE      | pts   | NULL       | ref  | PRIMARY,product_id,store_id,store_product | store_id                 | 4       | const          | 813308 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | ptv   | NULL       | ref  | product_vehicle_submodel,vehicle_product  | product_vehicle_submodel | 4       | pts.product_id |     53 |    50.00 | Using where; Using index     |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+

MySQL 5.7.30 (a consulta leva 12 segundos):

+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys                             | key                      | key_len | ref            | rows   | filtered | Extra                        |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+
|  1 | SIMPLE      | pts   | NULL       | ref  | PRIMARY,product_id,store_id,store_product | store_product            | 4       | const          | 547242 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | ptv   | NULL       | ref  | product_vehicle_submodel,vehicle_product  | product_vehicle_submodel | 4       | pts.product_id |     22 |    50.00 | Using where; Using index     |
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+

As duas tabelas em questão são idênticas em ambos os servidores. Neste caso parece que o plano é um pouco diferente mas tenho outros assim:

Exemplo 2:

SELECT DISTINCT vehicle_type_id, vehicle_type_name
FROM base_vehicle bv
    INNER JOIN vehicle_type vt USING (vehicle_type_id);

Este produz explicações idênticas em ambos os servidores, mas em média 0,07 segundos no MySQL 5.7 e 0,30 segundos no MySQL 8, aproximadamente 4 vezes mais!

+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key               | key_len | ref                | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+
|  1 | SIMPLE      | vt    | NULL       | index | PRIMARY         | vehicle_type_name | 194     | NULL               |   11 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | bv    | NULL       | ref   | vehicle_type_id | vehicle_type_id   | 2       | vt.vehicle_type_id | 6428 |   100.00 | Using index                  |
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+

Estou completamente perdido neste momento e espero que alguém possa ajudar a esclarecer o que pode estar levando a um desempenho tão ruim após essa atualização.

ATUALIZAÇÃO: Conforme solicitado, aqui estão os esquemas das tabelas envolvidas nas consultas mencionadas acima:

CREATE TABLE `product_to_store` (
  `product_id` int NOT NULL,
  `store_id` int NOT NULL,
  PRIMARY KEY (`product_id`,`store_id`),
  KEY `product_id` (`product_id`),
  KEY `store_id` (`store_id`),
  KEY `store_product` (`store_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `product_to_vehicle` (
  `product_to_vehicle_id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `vehicle_id` mediumint NOT NULL DEFAULT '0',
  `submodel_id` smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_to_vehicle_id`),
  KEY `submodel_id` (`submodel_id`),
  KEY `product_vehicle_submodel` (`product_id`,`vehicle_id`,`submodel_id`),
  KEY `vehicle_product` (`vehicle_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `vehicle_type` (
  `vehicle_type_id` smallint NOT NULL AUTO_INCREMENT,
  `vehicle_type_name` varchar(64) NOT NULL,
  PRIMARY KEY (`vehicle_type_id`),
  KEY `vehicle_type_name` (`vehicle_type_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `base_vehicle` (
  `vehicle_id` mediumint NOT NULL AUTO_INCREMENT,
  `year` smallint NOT NULL DEFAULT '0',
  `make_id` smallint NOT NULL DEFAULT '0',
  `model_id` mediumint NOT NULL DEFAULT '0',
  `vehicle_type_id` smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (`vehicle_id`),
  KEY `make_id` (`make_id`),
  KEY `model_id` (`model_id`),
  KEY `year_make` (`year`,`make_id`),
  KEY `year_model` (`year`,`model_id`),
  KEY `vehicle_type_id` (`vehicle_type_id`),
  KEY `ymm` (`year`,`make_id`,`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

STATUS DA TABELA: É interessante notar que TABLE_ROWSestão errados para ambos. select count(1) from product_to_vehicle;me dá 18330148 em ambos os casos e as tabelas 8.0 são resultado de dump e importação para 8.0, então não há razão para que sejam diferentes.

Status da Tabela em 8.0.20

+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+
| TABLE_NAME         | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+
| base_vehicle       | InnoDB |      10 | Dynamic    |      72210 |             36 |     2637824 |               0 |     12681216 |   4194304 |         150814 | 2020-05-14 04:16:34 | NULL                | NULL       | utf8_general_ci |     NULL |                |
| product_to_store   | InnoDB |      10 | Dynamic    |    2636946 |             32 |    86622208 |               0 |    124452864 |   5242880 |           NULL | 2020-05-14 04:24:26 | 2020-05-14 04:31:18 | NULL       | utf8_general_ci |     NULL |                |
| product_to_vehicle | InnoDB |      10 | Dynamic    |   22502991 |             50 |  1147092992 |               0 |   1274970112 |   7340032 |       23457421 | 2020-05-14 05:15:41 | 2020-05-14 05:24:36 | NULL       | utf8_general_ci |     NULL |                |
| vehicle_type       | InnoDB |      10 | Dynamic    |         11 |           1489 |       16384 |               0 |        16384 |         0 |           2190 | 2020-05-14 04:29:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+

Status da Tabela em 5.7.30

+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+
| TABLE_NAME         | Engine | Version | Row_format | table_rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | table_collation | Checksum | Create_options |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+
| base_vehicle       | InnoDB |      10 | Dynamic    |      70716 |             52 |     3686400 |               0 |     11124736 |   4194304 |         150814 | 2020-05-14 01:04:16 | NULL        | NULL       | utf8_general_ci |     NULL |                |
| product_to_store   | InnoDB |      10 | Dynamic    |    2517116 |             39 |    99270656 |               0 |    144637952 |   7340032 |           NULL | 2020-05-08 22:36:31 | NULL        | NULL       | utf8_general_ci |     NULL |                |
| product_to_vehicle | InnoDB |      10 | Dynamic    |   15627279 |             37 |   584024064 |               0 |   1739882496 | 685768704 |       23457421 | 2020-05-14 01:03:35 | NULL        | NULL       | utf8_general_ci |     NULL |                |
| vehicle_type       | InnoDB |      10 | Dynamic    |         11 |           1489 |       16384 |               0 |        16384 |         0 |           2190 | 2020-05-08 22:36:31 | NULL        | NULL       | utf8_general_ci |     NULL |                |
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+

Mais análises:

8.0.20

EXPLAIN ANALYZE SELECT DISTINCT vehicle_id, submodel_id, store_id FROM product_to_store pts JOIN product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21;

| -> Table scan on <temporary>  (actual time=0.001..3.453 rows=60193 loops=1)
    -> Temporary table with deduplication  (actual time=27786.823..27795.343 rows=60193 loops=1)
        -> Nested loop inner join  (cost=3222988.86 rows=14633875) (actual time=0.064..6910.370 rows=8610547 loops=1)
            -> Index lookup on pts using store_id (store_id=21)  (cost=81628.75 rows=813308) (actual time=0.041..176.566 rows=420673 loops=1)
            -> Filter: (ptv.vehicle_id <> 0)  (cost=0.26 rows=18) (actual time=0.006..0.014 rows=20 loops=420673)
                -> Index lookup on ptv using product_vehicle_submodel (product_id=pts.product_id)  (cost=0.26 rows=36) (actual time=0.006..0.011 rows=20 loops=420673)

5.7.30

EXPLAIN format = JSON SELECT DISTINCT vehicle_id, submodel_id, store_id FROM product_to_store pts JOIN product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2711880.30"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "pts",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "product_id",
              "store_id",
              "store_product"
            ],
            "key": "store_product",
            "used_key_parts": [
              "store_id"
            ],
            "key_length": "4",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 547242,
            "rows_produced_per_join": 547242,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "1067.75",
              "eval_cost": "109448.40",
              "prefix_cost": "110516.15",
              "data_read_per_join": "8M"
            },
            "used_columns": [
              "product_id",
              "store_id"
            ]
          }
        },
        {
          "table": {
            "table_name": "ptv",
            "access_type": "ref",
            "possible_keys": [
              "product_vehicle_submodel",
              "vehicle_product"
            ],
            "key": "product_vehicle_submodel",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "4",
            "ref": [
              "pts.product_id"
            ],
            "rows_examined_per_scan": 18,
            "rows_produced_per_join": 5097113,
            "filtered": "50.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "562530.32",
              "eval_cost": "1019422.75",
              "prefix_cost": "2711880.30",
              "data_read_per_join": "77M"
            },
            "used_columns": [
              "product_to_vehicle_id",
              "product_id",
              "vehicle_id",
              "submodel_id"
            ],
            "attached_condition": "(`ptv`.`vehicle_id` <> 0)"
          }
        }
      ]
    }
  }
}

E aqui está uma comparação da segunda consulta:

8.0.20

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7186.24"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "vt",
            "access_type": "index",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "vehicle_type_name",
            "used_key_parts": [
              "vehicle_type_name"
            ],
            "key_length": "194",
            "rows_examined_per_scan": 11,
            "rows_produced_per_join": 11,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "0.25",
              "eval_cost": "1.10",
              "prefix_cost": "1.35",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "vehicle_type_id",
              "vehicle_type_name"
            ]
          }
        },
        {
          "table": {
            "table_name": "bv",
            "access_type": "ref",
            "possible_keys": [
              "vehicle_type_id"
            ],
            "key": "vehicle_type_id",
            "used_key_parts": [
              "vehicle_type_id"
            ],
            "key_length": "2",
            "ref": [
              "vt.vehicle_type_id"
            ],
            "rows_examined_per_scan": 6519,
            "rows_produced_per_join": 71712,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "13.69",
              "eval_cost": "7171.20",
              "prefix_cost": "7186.24",
              "data_read_per_join": "1M"
            },
            "used_columns": [
              "vehicle_id",
              "vehicle_type_id"
            ]
          }
        }
      ]
    }
  }
}

5.7.30

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14684.01"
    },
    "duplicates_removal": {
      "using_temporary_table": true,
      "using_filesort": false,
      "nested_loop": [
        {
          "table": {
            "table_name": "vt",
            "access_type": "index",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "vehicle_type_name",
            "used_key_parts": [
              "vehicle_type_name"
            ],
            "key_length": "194",
            "rows_examined_per_scan": 11,
            "rows_produced_per_join": 11,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "1.00",
              "eval_cost": "2.20",
              "prefix_cost": "3.20",
              "data_read_per_join": "2K"
            },
            "used_columns": [
              "vehicle_type_id",
              "vehicle_type_name"
            ]
          }
        },
        {
          "table": {
            "table_name": "bv",
            "access_type": "ref",
            "possible_keys": [
              "vehicle_type_id"
            ],
            "key": "vehicle_type_id",
            "used_key_parts": [
              "vehicle_type_id"
            ],
            "key_length": "2",
            "ref": [
              "vt.vehicle_type_id"
            ],
            "rows_examined_per_scan": 6647,
            "rows_produced_per_join": 73126,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
              "read_cost": "55.61",
              "eval_cost": "14625.20",
              "prefix_cost": "14684.01",
              "data_read_per_join": "1M"
            },
            "used_columns": [
              "vehicle_id",
              "vehicle_type_id"
            ]
          }
        }
      ]
    }
  }
}

Estranhamente, esses números parecem indicar que o MySQL 8 tem um custo geral menor, mas ainda está executando muito mais lentamente.

Recentemente, atualizei um servidor para o MySQL 8.0.20 de 5.7.30 e encontrei este erro em uma consulta onde nunca havia aparecido no passado. Parece estar relacionado de alguma forma à inclusão de SQL_CALC_FOUND_ROWSà medida que o erro desaparece quando é removido. Eu aumentei sort_buffer_sizee agora as coisas parecem bem, mas fico me perguntando por que isso aconteceria de repente no MySQL 8?

Posso fornecer mais detalhes sobre a consulta e o esquema específicos envolvidos, se necessário, mas minha pergunta é mais geral " algo mudou na versão 8 que causaria isso "?

Recentemente, desenvolvi um script que gera vários processos para importar tabelas em paralelo usando mysqlimport e um --tab type mysqldump export. No servidor de desenvolvimento ele funciona muito bem e comparado a um tipo de importação padrão mysql db_name < backup.sql, ele reduz o tempo de cerca de 15 minutos para 4 ou 5 minutos.

O problema é que em nosso servidor de produção esse script parece estar bloqueando tabelas em todo o sistema. Ou seja, estou importando um backup para um banco de dados completamente diferente, mas nossas tabelas de aplicativos ativos ainda acabam bloqueadas. Um SHOW PROCESSLIST confirma que as tabelas em nosso banco de dados ativo estão realmente bloqueadas, mas nenhuma consulta INSERT ou UPDATE está sendo executada em nenhuma tabela desse banco de dados.

Por que isso está acontecendo? Existe uma variável de configuração/configuração que eu possa ajustar para evitar que esse bloqueio aconteça?

Tenho notado repetidas vezes que quando EXPLAINconsulto com uma GROUP BYcláusula, recebo filesortcomo condição extra. Há muito tempo eu li uma sugestão para usar GROUP BY NULLnesses casos para evitar o filesort, e de fato elimina essa filesortcondição de aparência desagradável.

Eu pensaria que, se nenhuma ORDER BYcláusula estiver presente, o dbms apresentaria apenas uma ordem arbitrária ou o que for mais eficiente, em vez de ordenar por alguma coluna misteriosa que exija uma classificação de arquivos. Parece estranho para mim que eu precise incluir uma direção extra que basicamente equivale a dizer "não faça nada estúpido" .

Minha pergunta é por que isso é necessário e está adicionando ORDER BY NULLrealmente ajudando o desempenho?

Suponha que eu tenha uma consulta como:

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

Eu tenho um índice ativado ide um índice ativado, columnmas geralmente adiciono um índice composto com ambos, o que pode melhorar a eficiência de consultas como essa. Minha pergunta é sobre a ordem das colunas no índice. Por tentativa e erro, descobri que às vezes o DBMS prefere o índice unido primeiro e às vezes prefere o WHEREíndice primeiro.

Na consulta acima, existe uma regra rígida e rápida que eu possa seguir para saber qual ordem de chave funcionará melhor?

Normalmente, apenas adiciono os dois índices, executo EXPLAINa consulta e verifico qual é o preferido, depois removo o outro. Mas esse processo parece que pode ser melhorado por uma melhor compreensão da lógica envolvida na determinação da ordem do índice.