- 找到慢查询的方法
- 造成慢查询的原因
- 了解MySQL执行过程
- 通信协议
- 查询缓存
- 查询优化处理
- 语法解析和预处理
- 查询优化器
- 查询执行引擎
- 结果返回给客户端
- MySQL查询的一些优化建议
- MySQL优化器的局限
- 优化特定类型的查询
- 优化COUNT()查询
- Reference
查询优化、索引优化、库表结构优化需要齐头并进，一个不落。
找到慢查询的方法
- 使用SHOW FULL PROCESSLIST查看慢查询（可以使用KILL命令杀掉有问题的查询）
- 分析慢查询日志(mysqldumpslow)
-- 查看慢查询时间
show variables like "long_query_time";默认10s
-- 查看慢查询配置情况
show status like "%slow_queries%";
-- 查看慢查询日志路径
show variables like "%slow%";
-- 查看表被锁状态
show OPEN TABLES where In_use > 0;造成慢查询的原因
查询性能低下的最基本原因是访问的数据太多，因此，不妨经常问自己两个问题：
- 查询是否返回返回了超出需求的数据（太多的行或太多的列）
- MySQL服务层是否扫描了过多的数据行
对于第一类慢查询，也就是由于返回了过多的列或行导致的慢查询，往往是因为我们编程时没有意识到或者考虑不周导致。这里面还有一种情况，就是我们出于简化开发的考虑，经常会返回所有列，这种做法是值得考虑的，但需要知道这背后的代价是什么，如果有用不到的TEXT或BLOB的列，最好就不要每次查询都返回了。
对于第二类慢查询，就需要借助执行计划来进行分析了
了解MySQL执行过程
了解MySQL的执行过程是优化查询的基础，不要盲目相信一些所谓的“军规”或者“诀窍”，这些经验都有一定的适用范围，只有了解了执行过程，我们才能够知道它们的局限性，并有能力做出根据具体应用情况，做出一些“反常识”的优化。
通信协议
MySQL使用半双工通信协议，这意味着同一时间要么是客户端向服务器发送数据，要么是服务器向客户端发送数据，两者无法同时进行。这种简单快速的协议也意味着一旦一端开始发送数据，另一端要接受完整个消息才能响应它。
- SHOW FULL PROCESSLIST可以查看正在执行的SQL处于那种状态
- max_allowed_packet可以设置服务器接受的最长的查询语句大小
shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M查询缓存
- 如果查询缓存打开，服务器会首先检查这条查询是否命中缓存
- 查询缓存使用哈希方式，这意味着sql必须完全匹配（包括大小写、参数）才能命中
查询优化处理
语法解析和预处理
解析SQL合法性并生成“语法树”
查询优化器
MySQL通过查询优化器把上一步解析得到的“语法树”转化为执行计划，一条查询通常有若干种执行方式，优化器会从中选择一条它认为成本最低的。MySQL成本的最小单位是基于随机读取一个4K数据页的成本，再加上一些估算某些操作代价的“因子”，通过SHOW STATUS LIKE ‘Last_query_cost’可以查看上一条查询的成本。
优化器的优化规则是相当复杂的，我们最好不要“自以为比优化器更聪明”。
查询执行引擎
- 执行引擎根据执行计划来完成整个查询。
- 执行引擎实现了handler API接口，查询中的每个表都有一个handler实例，执行计划的大量操作就是通过调用这些接口完成的。
结果返回给客户端
- 如果查询缓存打开，结果会被缓存起来
- 结果集是增量返回的，也就是说开始产生第一条结果时，就开始向客户端发送。这样既可以减少服务端的内存使用，也可以让客户端第一时间得到结果。
MySQL查询的一些优化建议
不要盲目相信任何优化建议，这些建议可能仅仅在某些场景或某些MySQL版本中是正确的，所以一定要查看执行计划。
MySQL优化器的局限
- IN中的子查询会被改写为关联子查询，而不是先执行IN中的内层查询，再根据结果执行外层查询（5.5以及之前版本）
- 加在外层的限制条件无法推到内层，因此，在使用UNION时考虑好是否要在内层也加限制以提高查询效率
- MySQL中的关联都是循环嵌套关联，并没有哈希关联，但可以通过哈希索引“曲线救国”
- MySQL无法并行执行
- MySQL不支持松散索引扫描，也就是当无法跳过联合索引的第一列，而使用后面的列。
优化特定类型的查询
优化COUNT()查询
- COUNT(*)直接返回行数，COUNT(expr)返回expr不为NULL的行数
Reference
