mysql查询sql及索引优化

前言：

都有哪些维度可以进行数据库调优？简言之:

• 索引失效、没有充分利用到索引——索引建立
• 关联查询太多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优化

1. group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
2. group by 先排序再分组，遵照索引建的最佳左前缀法则
3. 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
4. where效率高于having，能写在where限定的条件就不要写在having中了
5. 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
6. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

六、优化分页查询

优化思路一：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询