一、SQL调优的依据 - 测试

二、优化性能的三架马车

2.1 DDL设计优化

整体原则是根据系统将要进行的查询来设计schema。

1）选择合适的数据类型：原则是1⃣️选择够用的最小数据类型，好处是占用磁盘、内存、CPU缓存空间少，处理时需要的CPU周期也少；2⃣️优先使用简单类型，如整型比字符操作代价低。

具体来说，1⃣️主键列优先选择整数类型，速度快且可使用auto increment；2⃣️用于联表查询的关联列，如film表的filmId和actor表的filmID，无论是否设置外键，推荐使用相同的类型，以避免比较操作时的类型转换；3⃣️尽量指定列为not null，尤其是要建索引的列；如果查询中包含可为null的列，其索引，索引统计，值比较更加复杂难以优化。

2）考虑DDL设计的范式与反范式：

范式的好处：1）避免冗余数据；2）数据表更小可加载到内存；3）查询中更少的使用使用group by和distinct等开销大的查询；缺点是需要大量联表操作，通常我们需要控制单个查询的联表数目不超过12个表，想起来太阳公司的extract customer大sql。

反范式的好处：1）避免联表；2）更有效的索引策略，如select msgContent from msg join user on userID where user.type=‘vip’ order by msg.published desc limit 10，索引是msg.published。执行计划是扫描msg.published索引，对每条msg数据去user表查看是否vip用户，如果vip用户少则效率低下；如果是一张表，则用(published, usertype)作为索引可以提升查询效率。缺点是：1）数据冗余；2）数据表更大，通常我们需要控制列数不能达到数百列，因为服务层和引擎层之间通过‘行缓冲‘拷贝数据，服务层把行数据解码成各个列，列越多则开销越大。

2.2 索引设计优化

索引可以提高查询、排序操作的效率。

1）作为开发，理解什么样的查询可以应用已有索引。设计索引的时候需要考虑后续的查询；后续设计查询的时候也需要考虑应用已有的索引。就是两边都一起努力，希望尽可能多的查询操作能够通过索引完成。

2）Mysql本身，提供自适应哈希索引、聚簇索引、覆盖索引进一步提高查询效率。

索引设计的注意事项

1）多列索引优于多个单列索引：不建议为每个列单独创建索引，例如对姓/名/生日分别建立索引，当根据姓/名查询时，mysql同时使用这两个单列索引进行扫描，并将结果合并，合并算法包括与/或，称为索引合并，索引合并也是索引设计的坏味道。

2）合理的索引列顺序：通常将选择性更强的列放在前面，将用于范围查询的列放在后面。索引的选择越强则索引查询效率越高。索引的选择性指不重复的索引值和数据表总量的比值，主键索引的选择性最强；如果查询条件中的索引值(如null)搜索出1万多条数据，就是典型的选择性差，此时索引查询对于读操作的效率提升帮助较小；再比如，未登录用户的用户名均为guest，涉及guest用户的查询与正常用户查询性能相距甚远。

索引维护的注意事项

1）删除重复索引和冗余索引：重复索引是在相同的列上按照相同顺序创建的相同类型索引，如索引(A)和索引(A)。索引(A,B)和索引(A)是冗余索引，因为后者是前者的左键索引。

2）删除未使用索引

3）减少索引碎片：B+Tree的叶子节点的物理分布不是连续的，InnoDB提供添加/删除索引功能，可以通过先删除，再创建的方式消除索引的碎片化。

2.3 查询语句优化

2.3.1 时间都去哪儿了？

性能是完成某任务的时间度量，也就是响应时间；优化查询性能就是提高查询的响应速度。响应时间包括执行时间和等待时间，等待时间又包括等待IO和等待锁的时间。那么查询的时间都花在哪儿了呢？我们可以通过show full processlist查看线程状态进而查看查询的生命周期：

1）Sleep：等待客户端发送请求；2）Query：正在执行查询，或者正在返回结果给客户端；3）Locked：在服务器层等待表锁，等待InnoDB的行锁并不会在此显示；4）analysing & statistices：正在收集存储引擎统计信息，生成执行计划；5）Copying to temp table(on disk)：正在执行查询并把结果复制到临时表，在group by、文件排序和union等操作出现；6）sorting result：正在排序；7）Sending data：在多个状态间传送数据，或者正在生成结果集，或者向客户端返回数据；

---

2.3.2 查询优化的思路

1）客户端是否向数据库请求了不需要的数据：1. 可使用limit减少返回的行；2. 可通过避免使用select * 减少返回的列，但有时select * 配合缓存总体性能也不错；3. 通过缓存避免重复查询相同的数据。

2）通过日志中记录的扫描行数和返回行数，查看服务端是否扫描了不需要的数据。理想情况下，扫描行数等于返回行数；但联表查询时扫码多行才能连结为一行返回，扫描行数会明显大于返回行数。Where条件对应的3种处理方式，扫描行数从少到多依次是：1. 索引作为查询条件，在存储引擎层完成；2. 索引覆盖扫描(using index)，服务层直接从索引中过滤掉不需要的数据；3. 服务层过滤不满足条件的记录(using where)。

---

更多内容详见：https://www.jianshu.com/p/b2d20d93857c

三、优化器有所为有所不为

3.1 有所为

1）关联表顺序重排，对于join；2）min/max函数优化，基于B+Tree；3）提前终止查询，如limit；4）in子句优化；5）表达式等价转换；6）把子查询优化掉；7）将外连接转化为内连接：outer/inner join

3.2 有所不为

1）避免在in中包含子查询；2）优化器不考虑并发，也无法利用多核特定来并行执行查询；3）当在同一个表上查询和更新时，通过用as生成临时表的方式来解决。

四、分析工具

4.1 执行计划

Mysql优化器基于成本选择最优执行计划并交给执行引擎，执行计划采用指令树的形式。用户可以用explain命令请求优化器解释优化过程，查看生成的执行计划。

min/max函数优化：1. 能够使用索引时，通过查找B-tree的最左端/最右端优化min/max函数；执行计划显示select tables optimized away，表示优化器已经在执行计划中把该表移除，用常数取代；

需要服务层进行筛选的查询，执行计划的extra显示为using where。例如select id from user where id<5 and id <>1; innoDB锁定id为1-4的数据并返回给服务器层，服务器层继而过滤掉id=1；执行计划的extra显示using where。

使用覆盖索引时执行计划的extra为using index。

当通过执行计划看到对多个索引做and运算时，说明需要一个多列索引。

联表查询的排序：建议order by中所有的列来自于同一张表；如果order by中所有列来自第一个表，则查询第一张表时就进行排序，执行计划显示using filesort；否则mysql把查询结果放到临时表，在关联查询结束后进行排序，执行计划显示using temporary using filesort。

4.2 常见命令

1）show status：输出是计数，如created_tmp_tables计数器值为3表示创建3个临时表；handler_read_rnd_next计数器值为6478表示有很多没用到索引的读操作，出现在多表关联查询，子查询创建了临时表，临时表没有索引；

2）show profile：输出一个查询的各个子步骤所花费时间，比如等待锁、优化器优化、生成临时表、排序；

3）information_schema.index_statistics：用于查看索引使用频率并删除未使用的索引，统计数据来源于InnoDB记录索引访问并保存索引统计信息。

4）show full processlist查看线程状态进而查看查询的生命周期。

4.3 慢日志查询

把效率低的查询捕获到文件