Índice de usuários do MySQL 5.6 em vez do tipo de intervalo, selecione em uma tabela grande e consulta simples

Recentemente migramos do MySQL 5.5 para 5.6 ( 5.6.44-86.0-log Percona Serverpara ser mais preciso) e temos um grande problema. Consultas aparentemente simples são executadas por minutos em vez de milissegundos em uma tabela com mais de 300 milhões de registros.

Esta é a tabela em questão:

CREATE TABLE `articles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) unsigned NOT NULL,
  `date` double(16,6) NOT NULL,
  `score` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `feed_id_date` (`feed_id`,`date`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Sim, dateé doubleporque precisamos de precisão de microssegundos. Uma solução que ainda não exploramos é transformá-lo em bigint.

Aqui está uma consulta típica que falha:

mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826,6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
| id | select_type | table | type  | possible_keys     | key          | key_len | ref  | rows      | Extra                                    |
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
|  1 | SIMPLE      | a     | index | feed_id_date,date | feed_id_date | 12      | NULL | 339355570 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+-------------------+--------------+---------+------+-----------+------------------------------------------+
1 row in set (0.00 sec)

Observe o número de linhas. É praticamente uma varredura de mesa completa. Essa consulta é executada por 3 a 5 minutos, o que é inaceitável para uma carga OLTP. Agora veja as duas consultas a seguir onde selecionamos as duas feed_idem questão uma a uma:

mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6826) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys     | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | a     | range | feed_id_date,date | feed_id_date | 12      | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT a.id FROM articles a WHERE a.feed_id IN (6827) AND a.date < 1564469723.424363 ORDER BY a.date DESC LIMIT 20;
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys     | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | a     | range | feed_id_date,date | feed_id_date | 12      | NULL |  473 | Using where; Using index |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

E, de fato, este é o número correto de linhas para cada feed_id:

mysql> SELECT COUNT(*) FROM articles a WHERE a.feed_id=6826;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM articles a WHERE a.feed_id=6827;
+----------+
| COUNT(*) |
+----------+
|      474 |
+----------+
1 row in set (0.00 sec)

Isso é muito intrigante para mim. Temos todos os tipos de combinações na INcláusula. Alguns usuários têm mais de 1.000 feed_idregistros que funcionam bem, mas em algumas situações dois registros na INcláusula são suficientes para fazer uma varredura completa da tabela.

No EXPLAINé visível que o problema só surge quando o typeis index, e não range. Em nossa instância antiga do MySQL 5.5 EXPLAIN, as mesmas consultas e o mesmo conjunto de dados sempre mostram o rangetipo e nunca tivemos esse problema.

Isso pode estar relacionado à configuração? Aqui está o meu my.cnf:

[mysqld]

skip-external-locking
skip-name-resolve

transaction-isolation           = READ-COMMITTED

max_connections                 = 5000
max_user_connections            = 4500
back_log                        = 2048
max_allowed_packet              = 128M

sort_buffer_size                = 256K
read_buffer_size                = 128K
read_rnd_buffer_size            = 256K

join_buffer_size                = 8M
myisam_sort_buffer_size         = 8M
query_cache_limit               = 1M
query_cache_size                = 0
query_cache_type                = 0
key_buffer                      = 10M
thread_stack                    = 256K
thread_cache_size               = 100
tmp_table_size                  = 256M
max_heap_table_size             = 1G
query_cache_min_res_unit        = 1K

character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_buffer_pool_size         = 46G
innodb_buffer_pool_instances    = 32
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 16M
innodb_file_per_table           = 1
innodb_io_capacity              = 50000

A VM possui 64 GB de RAM e armazenamento de 100k+ iops, mas isso certamente não está relacionado ao hardware.