前言：
都有哪些维度可以进行数据库调优？简言之:
- 索引失效、没有充分利用到索引——索引建立
- 关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化
- 服务器调优及各个参数设置(缓冲、线程数等)——调整my.cnf。
- 数据过多——分库分表
关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。
- 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
- 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。
一、单表简单案例
范围条件右边的列索引失效优化
查询category_id 为1且comments>1的情况下
- type：all，全表扫描，情况不容乐观
- Using filesort：文件内排序，情况不容乐观*2
优化：
show index from article --查看表索引
新建索引
ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views) --第一种方式
CREATE INDEX idx_article_ccv ON article (category_id, comments, views) --第二种方式
再次查看执行计划
- 全表扫描已解决，但是文件排序依然存在
- 索引不合适
删除并重建索引：
DROP INDEX idx_article_ccv ON article -- 删除索引 CREATE INDEX idx_article_ccv ON article (category_id,views) --重建索引
再次查看执行计划
一般性建议：
- 对于单列索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
总之，书写SQL语句时，尽量避免造成索引失效的情况。
二、关联查询优化
1、采用左外连接
查看执行计划
没加索引的情况下，驱动表和被驱动表都是全表扫描。
- 结论：type 有All
- 由于是LEFT JOIN，所以左表是主表，因此第一次索引尝试加在主表上
只对左表class新增索引
CREATE INDEX idx_class_card ON class (card) EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card
结论：虽然type变为index，但是扫描行数依然是全表扫描。
- 只对右表book 新增索引
DROP INDEX idx_class_card on class --删除class表索引 CREATE INDEX idx_book_card ON book (card) EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card
结果：type变为ref，rows只扫描了一行。
结论：这是由于LEFT JOIN特性决定的，由于左表数据全都有，所以关键在于如何从右表进行搜索，所以右表一定要添加索引。
2、采用内连接
1、内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表
2、内连接，如果表的连接条件中有一个字段有索引，则有索引的字段所在的表会被作为被驱动表
3、在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，小表驱动大表(小表作为驱动表，大表作为被驱动表)
换成 inner join（MySQL自动选择驱动表）
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
添加索引优化
ALTER TABLE book ADD INDEX Y ( card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;
ALTER TABLE `type` ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;
3、3表关联查询优化
- 三表均没有新建索引
结论： 全表扫描，且使用了连接缓存
- 在phone和book表新增索引
CREATE INDEX idx_phone_card ON phone(card) CREATE INDEX idx_book_card ON book (card) EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card
总结
- 语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集”。
- 优先优化NestedLoop的内层循环。
- 尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
- 当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join buffer以达到性能优化的目的
三、子查询优化
MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
**子查询是MySQL的一项重要的功能，可以帮助我们通过一个sQL语句实现比较复杂的查询。但是，子查询的执行效率不高。**原因：
- 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
- 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
- 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。
结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
举例1：查询班长的信息
子查询情况
EXPLAIN SELECT * FROM student stu1 WHERE stu1.`stuno` IN ( SELECT monitor FROM class c WHERE monitor IS NOT NULL );
推荐：使用多表连接查询 EXPLAIN SELECT * FROM student stu1 JOIN class c ON stu1.stuno=c.monitor WHERE c.monitor IS NOT NULL;
举例2：取所有不为班长的同学
不推荐子查询格式 EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a WHERE a.stuno NOT IN ( SELECT monitor FROM class b WHERE monitor IS NOT NULL); 推荐，换成多表连接查询 EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT JOIN class c ON a.stuno = c.monitor WHERE c.monitor IS NULL;
四、排序优化（order by）
1、排序优化
**问题：**在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?
回答：在MySQL中，支持两种排序方式，分别是**FileSort和Index**排序。
- **Index**排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
- **FileSort**排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。
优化建议：
- SQL中，可以在WHERE子句和ORDER BY字句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY字句避免使用FilteSort排序。当然某些情况下全表扫描，或者FileSort排序不一定比索引慢。但是总的来说，我们还是要避免，以提高查询效率。
- 尽量使用Index完成ORDER BY排序。如果WHERE 和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
- 无法使用Index时，需要对FilteSort方式进行调优
2、总结
INDEX a_b_c(a,b,c) order by 能使用索引最左前缀 - ORDER BY a - ORDER BY a,b - ORDER BY a,b,c - ORDER BY a DESC,b DESC,c DESC 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引 - WHERE a = const ORDER BY b,c - WHERE a = const AND b = const ORDER BY c - WHERE a = const ORDER BY b,c - WHERE a = const AND b > const ORDER BY b,c 不能使用索引进行排序 - ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */ - WHERE g = const ORDER BY b,c /*丢失a索引*/ - WHERE a = const ORDER BY c /*丢失b索引*/
3、案例
ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序；无索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须 的
优化思路：
方案一: 为了去掉filesort我们可以把索引建成
#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);
方案二: 尽量让where的过滤条件和排序使用上索引
建一个三个字段的组合索引：
DROP INDEX idx_age_name ON student;
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
结论：
1. 两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysql选择 idx_age_stuno_name）。但是， 随着数据量的变化，选择的索引也会随之变化的 。
2. 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过 滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段 上。反之，亦然。
4、filesort算法：双路排序和单路排序
双路排序 （慢）
- MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取
对应的数据输出
- 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种
改进的算法，就是单路排序。
单路排序 （快）
从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输
出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空
间， 因为它把每一行都保存在内存中了。
结论及引申出的问题
- 由于单路是后出的，总体而言好过双路
- 但是用单路有问题
优化策略
1. 尝试提高 sort_buffer_size
2. 尝试提高 max_length_for_sort_data
3. Order by 时select * 是一个大忌。最好只Query需要的字段。
五、GROUP BY优化
- group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
- group by 先排序再分组，遵照索引建的最佳左前缀法则
- 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
- where效率高于having，能写在where限定的条件就不要写在having中了
- 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
- 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
六、优化分页查询
优化思路一：
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
优化思路二
该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询
