MySQL 5.6 使用者索引而不是范圍類型選擇大表和簡單查詢

我們最近從 MySQL 5.5 遷移到 5.6（5.6.44-86.0-log Percona Server準確地說），我們遇到了一個巨大的問題。看似簡單的查詢在具有 300+ 百萬條記錄的表上執行幾分鐘而不是幾毫秒。

這是有問題的表格：

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;

是的，date因為double我們需要微秒級精度。我們尚未探索的一種解決方案是將其轉換為bigint.

這是一個失敗的典型查詢：

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)

注意行數。這實際上是一個全表掃描。此查詢執行 3-5 分鐘，這對於 OLTP 負載是不可接受的。現在看以下兩個查詢，我們feed_id一一選擇有問題的兩個：

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)

實際上，這是每個的正確行數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)

這對我來說非常令人費解。IN我們在子句中有各種組合。一些使用者有 1000 多feed_id條記錄執行良好，但在某些情況下，IN子句中的兩條記錄足以導致全表掃描。

EXPLAIN可以看出，問題僅在istype和indexnot時出現range。在我們的舊 MySQL 5.5 實例EXPLAIN上，相同的查詢和相同的數據集總是顯示range類型，我們從來沒有遇到過這樣的問題。

這可能與配置有關嗎？這是我的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

VM 有 64GB RAM 和 100k+ iops 儲存，但這肯定與硬體無關。

我通過將feed_id欄位添加到ORDER BY子句中來修復它：

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, a.feed_id 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 |  474 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+-------------------+--------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

不確定這是否是正確的方法，對我來說看起來有點 hacky，特別是因為這在 中不需要5.5，所以如果其他人有更好的解決方案，請告訴我。

引用自：https://dba.stackexchange.com/questions/244095