如何调整 SQL 以适应使用 MySQL 运行的系统第 2 部分 ~SQL 分析 ~

这是《Beyond》里的原冈。

这是前面内容的延续。

如何将 SQL 调整到运行在 MySQL 上的系统 - 发现瓶颈 -

这次，我将解释如何分析我发现的瓶颈 SQL。

如果发现大量 SQL，请排除原因。
修改SQL的方法有很多，但
基本思想是适当使用索引。

・什么是指数？

顾名思义，索引
是一个可以让您快速访问数据的标题。
最常用的基本索引是一种称为 B 树索引的结构。

【详情见下文】
Wiki索引（数据库）
B树索引简介

正确使用或不使用索引，
其效果不仅是数倍，而是数百倍、甚至数千倍，因此非常有效。

・使用说明

使用EXPLAIN来分析SQL。
使用起来很简单，只需在要分析的SQL前添加“EXPLAIN”即可。

如果使用explain来分析，会输出如下分析结果。

用于主键搜索

mysql > 解释 select col_b from tbl_a where col_b=100000; +----+------------+----------------+-- ------+------+---------+---------+---------+ ------+ -------+ | select_type | key_len | 额外 | ------------+--------+----------------+---- -----+-- -------+-------+------+| 简单 | 常量 | 1空 | +----+-------------+----------------+---- ---+---- -----------+---------+---------+--------+-- ----+--- ----+ 1 行一组（0.01 秒）

用于索引搜索

mysql > 解释 select col_b from tbl_a where col_c=2000000; +----+------------+----------------+-- ----+----------------+------------+------------+---- ---+------+ | id | 类型 | key_len | --------+----------------+--------+----------------+ ---------+---------+--------+------+------+ | 1 | | tbl_a | col_c | 1 | +----+------------- --+--------+----------------+-------------+-------- - +--------+------+-----+

用于全扫描

mysql > 解释 select col_b from tbl_a where col_d='aaa'; ------+----------------+------+---------+--------+--- -----+-------------+ | select_type | 类型 | key_len | ----------+----------------+------+----------------+ ------+---------+------+---------+------------+ | 1 | 简单 | NULL | 500000 | ------+------+----------------+--------+---------+--- -- -+---------+-------------+

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

mysql > 解释 select col_b from tbl_a where col_d='aaa' order by col_e; --+-----+------+------+---------+-----+ --------+ --------------------------+ | select_type | 类型 | key_len | ---+-------------+----------------+---- --+--------- ------+------+---------+------+---------+------------ -------------------+ | 简单 | NULL | NULL | 使用文件排序 | +------------+----------------+--------+ ------------ ---+-----+---------+------+---------+- -------------- -----------------+

表 8.1 EXPLAIN 输出列

・注意事项

小心是否有select_type

type
ALL 正在对表进行全量扫描，因此使用索引比较重，

INDEX A 全量不会有太大影响。正在执行索引扫描，很重。

key
使用索引，如果为NULL则不使用

按行
基本上，如果该值很大，请小心。

额外的
Using filesort和Usingtemporary

【参考】
2014年MySQL初学者索引调优要点总结

・检查表结构和索引

使用desc检查表结构

mysql > desc tbl_a; +------------------------+-------------------- -+------+------+----------------------+--------+ | 字段 |类型 | 空 | 额外 | - ----+------+-----+----------------------+--------+ | col_b | 没有 | NULL | | varchar(20) | (5) | 空 | +--------------------+------------ ---- ---+------+----+--------------------+-------- +

使用 show index 检查索引

mysql > 显示 account_public 的索引； +----------------+------------+------------- ---+--------------+-----------------+------------ +------------+---------+---------+--------+-------- ---+---------+| 表 | 索引中的序列 | 排序规则 | | 索引_类型 | +------------+------------ ------+--------------+----------------+---------- --+ -------------+----------+---------+--------+----- ---- ---+---------+-----------+ | 0 | 500000 | | NULL | 0 | 80000 | +--------------+--------- --------+------------+--- ----------+------------+---- ----+--------+------------ --+---------+---------------- +

有一行叫做“Cardinality”，它是列值的类型。
该数字越大，索引过滤效率越高（基数越高），
该数字越小，索引过滤效率越低（基数较低）。
创建尽可能高效的索引。

・添加索引

如果发现ALL全扫描、索引全扫描、Using filesort等，则在目标表上创建索引。

在 tbl_a(col_d) 上创建索引 col_d;

索引的
创建/删除/信息显示/添加

创建索引后，尝试再次解释并检查效果。
我们还将按原样运行 SQL，并比较添加索引前后的执行速度。
耐心地重复，直到找到有效的方法。

·概括

• 使用EXPLAIN分析SQL
• 尽可能切换到基于索引的搜索
• 如果type=ALL，type=INDEX，则使用索引
• 使用文件排序和使用临时文件时要小心
• 如果行数很大，请使其尽可能小
• 创建和使用具有大基数的高索引

顺便

• 子查询很慢，所以如果可能的话避免使用它们。
• 不要进行不必要的连接

[其他参考文章]
Yahoo!内部举办MySQL调优研讨会