如何在运行 MySQL 的系统中优化 SQL（第二部分：SQL 分析）

这是来自Beyond的Haraoka。

这是前文内容的延续。

如何优化 MySQL 系统中的 SQL：查找瓶颈

这次，我们将向您展示如何分析被发现存在瓶颈的 SQL。

一旦发现冗长的 SQL 语句，就需要找出并消除其根源。
修复 SQL 语句的方法有很多，但
基本思路是合理使用索引。

・什么是指数？

索引顾名思义，就是
一个可以快速访问数据的表格。
最常用的基本索引结构是B树索引。

【更多详情请见下文】
维基索引（数据库）
B树索引简介

因为正确使用索引
通常可以带来数百倍甚至数千倍的差异，而不仅仅是几倍。

・如何使用 EXPLAIN

EXPLAIN 用于分析 SQL。
它使用起来很简单；只需在要分析的 SQL 语句前加上“EXPLAIN”即可。

使用 explain 进行分析时，分析结果将输出如下所示。

主键搜索

mysql > explain select col_b from tbl_a where col_b=100000; +----+-------------+---------------+-------+---------------+---------+---------+-------+------+------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+---------+---------+-------+------+------+ | 1 | SIMPLE | tbl_a | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | (0.01 sec)

对于索引搜索

mysql > explain select col_b from tbl_a where col_c=2000000; +----+-------------+---------------+-------+---------------+-------------+---------+-------+------+------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | tbl_a | const | col_c | col_c | 4 | const | 1 | NULL |

进行全面扫描

mysql > explain select col_b from tbl_a where col_d='aaa'; +----+-------------+---------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----+------+---------+------+-------+-------------+ | 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where |

如果您有“使用文件排序”

mysql > explain select col_b from tbl_a where col_d='aaa' order by col_e; +----+-------------+---------------+------+---------------+------+---------+------+---------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----+-----+------+---------+------+--------------------------+ | 1 | SIMPLE | tbl_a | ALL | NULL | NULL | NULL | NULL | 500000 | Using where;

表 8.1 解释输出列

・注意事项

select_type
如果存在依赖子查询（相关子查询），请务必小心。

类型
ALL 会执行表的全表扫描，因此性能较差。建议使用索引。
但是，如果数据项数量较少（几百项），则性能影响不大。
类型 INDEX 会执行索引的全表扫描，因此性能较差。

键：
已用索引，NULL 表示未使用

行
数

如果看到排序

【参考】
MySQL 初学者索引调优技巧总结（2014）

・检查表结构和索引

检查表结构（带描述符）

mysql > desc tbl_a; +----------------------+----------------------+------+------+-------+-------+ | 字段 | 类型 | 空值 | 键 | 默认值 | 额外值 | 否 | 乘数 | 空值 | | | col_d | varchar(20) | 否 | | 0 | | | | col_e | int(5) | 否 | | 空值 |

使用 show index 命令检查索引

mysql> show index from account_public; | +---------------+------------+-----------------+--------------+-----------------+----------+-------------+----------+---------+------+------------+---------+---------------+ | tbl_a | 0 | PRIMARY | 1 | col_b | A | 500000 | NULL | NULL | | BTREE | | | | tbl_a | 0 | col_c | 1 | col_c | A | 80000 | NULL | NULL | | BTREE | | | |

有一行名为“基数”的参数，它指示列值的类型。
该数值越大，索引的缩小效率越高（高基数）；
该数值越小，索引的缩小效率越低（低基数）。
尽量创建尽可能高效的索引。

・创建索引

如果发现 ALL 全表扫描、索引全表扫描或使用文件排序，请在目标表上创建索引。

CREATE INDEX col_d ON tbl_a(col_d);

创建、删除、显示和添加参考

索引创建完成后，再次运行 explain 命令检查效果。
同时，直接运行 SQL 语句，比较添加索引前后的执行速度。
耐心重复此过程，直到找到有效的方法。

·概括

• 使用 EXPLAIN 分析 SQL
• 尽可能切换到索引搜索。
• 如果 type=ALL,type=INDEX，则使用索引
• 使用文件排序和临时文件时要小心
• 如果行数太多，就尽量减少行数。
• 创建和使用高基数索引

此外

• 子查询速度较慢，因此应尽可能避免使用。
• 避免不必要的连接。

【其他参考文章】
雅虎日本举办MySQL调优研讨会揭晓