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

2017.03.22

1 ・什么是指数？
2 ・如何使用 EXPLAIN
3 ・注意事项
4 ・检查表结构和索引
5 ・创建索引
6 ·概括

这是来自Beyond的Haraoka。

这是前文内容的延续。

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

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

如果发现 SQL 查询速度慢，应该找出原因并排除。
修复 SQL 查询慢的方法有很多，但
基本原则是确保索引得到正确使用。

・什么是指数？

索引，顾名思义，
是一种用于快速访问数据的标题。
最常用的基本索引是B树索引。

[详情见下文]
wiki索引（数据库）
B树索引简介

正确使用索引和不使用索引之间的
可能不是几倍，而是几百倍甚至几千倍，这使得索引非常有效。

・如何使用 EXPLAIN

要分析 SQL，请使用 EXPLAIN 函数。
使用方法很简单：只需在要分析的 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 解释输出列

柱子	意义
`ID`	`选择` 标识符。
`选择类型`	`选择` 类型
`桌子`	输出行表
`类型`	组合型
`可能的键`	可选索引
`钥匙`	实际选定的索引
`key_len`	选定键长
`参考`	要与索引进行比较的列
`行`	已检查行数的估计
`额外的`	附加信息

[参考]
EXPLAIN 输出格式

・注意事项

`select_type
如果存在

`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 | | | |

有一行名为“基数”，它表示列中的值的数量。
该数值越大，索引的过滤效率越高（高基数）；
数值越小，索引的过滤效率越低（低基数）。
让我们创建一个尽可能高效的索引。