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 查询转换成某个位置的查询

 

posted on   uestc2007  阅读(282)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示