mysql - MySQL index_merge 导致查询运行速度慢 10 倍
问题描述
我有一个具有以下属性的表：
+-----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | <null> | auto_increment |
| c2 | varchar(255) | YES | MUL | <null> | |
| c3 | int(11) | YES | | <null> | |
| c4 | varchar(255) | YES | | <null> | |
| c5 | varchar(255) | YES | | <null> | |
| c6 | int(11) | YES | MUL | <null> | |
| c7 | int(11) | YES | | <null> | |
| c8 | int(11) | YES | | <null> | |
| c9 | datetime | YES | | <null> | |
| c10 | datetime | YES | | <null> | |
| c11 | char(40) | YES | UNI | <null> | |
| c12 | tinyint(1) | NO | MUL | 1 | |
| c13 | text | YES | | <null> | |
| c14 | int(11) | YES | MUL | <null> | |
| c15 | varchar(64) | YES | MUL | <null> | |
+-----------------------+--------------+------+-----+---------+----------------+
show index from table_one;显示以下输出：
+-------------------+------------+--------------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_one | 0 | PRIMARY | 1 | id | A | 1621972 | NULL | NULL | | BTREE | | |
| table_one | 0 | c11 | 1 | c11 | A | 1621972 | NULL | NULL | YES | BTREE | | |
| table_one | 0 | c2_c6_c8_and_c14_unique | 1 | c2 | A | 1621972 | NULL | NULL | YES | BTREE | | |
| table_one | 0 | c2_c6_c8_and_c14_unique | 2 | c6 | A | 1621972 | NULL | NULL | YES | BTREE | | |
| table_one | 0 | c2_c6_c8_and_c14_unique | 3 | c8 | A | 1621972 | NULL | NULL | YES | BTREE | | |
| table_one | 0 | c2_c6_c8_and_c14_unique | 4 | c14 | A | 1621972 | NULL | NULL | YES | BTREE | | |
| table_one | 1 | c12 | 1 | c12 | A | 1 | NULL | NULL | | BTREE | | |
| table_one | 1 | c6 | 1 | c6 | A | 20794 | NULL | NULL | YES | BTREE | | |
| table_one | 1 | c14 | 1 | c14 | A | 577 | NULL | NULL | YES | BTREE | | |
| table_one | 1 | c15 | 1 | c15 | A | 5 | NULL | NULL | YES | BTREE | | |
+-------------------+------------+--------------------------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
现在，当我运行以下查询时，平均需要大约 5.8 秒：
select * from table_one
where c6 = 12345 and c14 = 12
limit 10 offset 0;
当我运行explain上述查询时，它说它已使用index_merge：
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+------+-------------------------+
| 1 | SIMPLE | table_one | index_merge | ....................... | c14, c6 | 5,5 | NULL | 9 | Using intersect(c14,c6);|
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+------+-------------------------+
但是，如果我强制表仅使用索引c6，它会以 0.6 秒的平均值返回结果：
select * from table_one force index(c6) where c6 = 12345 and c14 = 12 limit 10 offset 0;
为什么 MySQL 单独使用index_merge并使其变慢？我知道我没有复合索引c6, c14，但它们单独存在。
此外，explain强制索引的查询显示执行查询所访问的行数更多，但仍快 10 倍。
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
| 1 | SIMPLE | table_one | ref | ....................... | c6 | 5 | const | 22388 | Using where; |
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
当有人以高速率访问 API 时，这会导致我们的生产下降。MySQL 在 59 秒内没有返回任何结果，并且查询超时。
另外，由于我们已经有 200 万个条目，因此我无法在不停机的情况下添加复合索引或更改架构。
当前的临时修复是添加force index(c6)到查询中，但我不确定它的可扩展性如何，或者我们以后可能会遇到问题。
编辑 1
缓慢可能是因为完成的顺序index_merge吗？
c6有关和的更多信息c14：c6将其视为国家c14和州。
编辑 2：2020-06-15 07:52:35 UTC：
我尝试通过强制索引来运行查询，c14结果它慢了大约 3 倍：
select * from table_one force index(c14) where c6 = 12345 and c14 = 12 limit 10 offset 0;
查询耗时 2.1 秒。
查询给出以下explain输出：
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
| 1 | SIMPLE | table_one | ref | ....................... | c14 | 5 | const | 730 | Using where; |
+----+-------------+---------------------+-------------+-----------------------------+---------+---------+-------------------------------+--------+-------------------------+
查询要访问的行数比强制打开索引时少 30 倍c6。即这里的行是 730，而在前面的查询中，它的行数是 22k。即使要访问的行数较少，哪些因素会使该索引变慢？
如果有任何帮助，请提供更多信息：
mysql> select count(*) from table_one where c14 is null;
+----------+
| count(*) |
+----------+
| 7490 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from table_one;
+----------+
| count(*) |
+----------+
| 1936278 |
+----------+
1 row in set (1.68 sec)
mysql> select count(*) from table_one where c6 is null;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
解决方案
考虑使用 (dbname); ALTER TABLE table_one ADD INDEX table_one_c6_and_c14 (c6,c14);
删除查询中的强制请求。
请告诉我们创建多列索引需要多长时间。以及完成查询的时间，现在有了适当的索引。
推荐阅读
- php - 在 PHP 中将 XMP 数据写入 JPEG 图像的最简单方法是什么？
- jenkins - 从文件中获取作业 DSL 脚本时。如何在 jenkins helm chart jcasc 部分以及格式中完成此操作？
- c# - 来自后台工作者的 C# TCP 侦听器
- node.js - 如何在 fetch API React 中更改本地地址
- azure-devops - 使用容器的 Azure Pipeline 作业验证失败
- ios - Fastlane 没有看到 cocoapods 行动
- javascript - 此表达式不可构造
- java - 如何从 HTTP REST 调用中获取完整的 EntityResponse 对象？
- python - 了解以下创建变量的python代码
- .net - 使用 VS Code 时，我收到此错误消息“无法找到 .NET Core SDK。确保已安装 .NET Core SDK 并且在路径上