Mysql查询语句优化

前言

上一篇文章 《MySQL索引原理机器优化》讲了索引的一些原理以及优化方案,这一次学习对查询的优化,毕竟快速的查找到数据才是我们的最终目的.

分析查询

想要对一条查询语句进行优化,首先要对其进行分析,MySQL提供了这个机制, 可以通过explain sql 或者desc sql的语法去获取MySQL对某一条语句的执行计划(MySQL优化之后的),explain的用法这里就不再赘述了,在另外一篇文章中有详细的解读.

查询优化

对一条sql的优化可以分为两部分,第一部分是对语句的优化,比如将子查询改写为join等,第二部分是与索引相关的优化,在这一阶段可能会修改语句以让查询尽可能的命中索引,甚至会通过修改索引来达到这个目的.

与索引相关的优化

首先我们需要让查询尽可能的命中索引,通常情况下在一张表上会有各种花里胡哨的查询,我们很难让每一个查询都完美命中,因此我们假设认为我们在为bad case 做优化,不考虑对其他的查询造成的影响.

最左前缀

在使用联合索引的时候,要想多字段命中索引,需要遵循最左前缀原则.

假如现在表上有school_age的联合索引,那么下面的语句是可以使用索引的:

# 根据school查询
select * from user where school = '卡塞尔'
# 根据scholl和age进行查询
select * from user where school = '卡塞尔' and age = 12

而直接通过age的查询是无法命中索引的,select * from school where age = 10,这一点可以通过explain来证实.

无法命中索引的一些操作

  1. 查询条件中使用不等于操作符!=
  2. 非前缀使用like like '%gaga%'.
  3. or操作符必须每个字段都建立索引
  4. where语句中有数学运算或者函数.

尽量的使用覆盖索引

在查询语句中,如果返回的字段较少,那么应该尽量的使需要的所有字段包含在索引中,这样可以使用覆盖索引来加快查询速度.

查询语句优化

检查语句

查询语句优化的第一步,首先从大的层面上分析一下语句,得到以下问题的答案:

是否请求了不需要的数据?

这个最常出现的是请求了过多的列,也就是select *,此外还有查询了10000行但是在拿到前10行之后就扔掉了其他的数据.

是否扫描了过多的数据?

在理想的情况下,我们希望能到做到扫描的数据行数和需要返回的数据行数是一样的,但是在实际操作中比较困难,但是我们首先应该检查这一项,已确定当前扫描的行数是必要的.

重构语句

拆分复杂查询

当一个语句太过于复杂的时候,我们总是难以掌握它的性能,因此我们可以将一个复杂的查询拆分成多个查询,然后在应用程序中进行关联.

改写子查询

子查询想必关联查询,性能一般是较差的,因此可以将子查询改为关联表查询.

具体的优化策略

优化count()

count()函数需要扫面大量的数据,在MyISAM中速度是比较快的,但是在其他存储引擎却不是,对count()语句可以有以下的优化策略.

确定是否真的需要数量

曾经见过一个count(*)的语句,但是对结果的使用仅仅是判断结果是否大于0,这时语句可以大大的减少扫面的数量来达到相同的作用:

select 1 from user where age = 100 limit 1.

这样仅需要扫描一行数据就可以达到相同的作用.

使用近似值

当表中数据量非常大的时候,很多的count查询是不需要精确计数的,此时可以使用其他近似值,比如explain中的行数,比如information_schema.tables中的行数等.

添加汇总表

如果需要经常的进行count,那么我们应该额外添加一张表或者一列来记录这个数值,而不是每次进行查询.

优化关联查询
  1. 确保on/where语句中的列上有索引.
  2. 确保order by / group by 只根据一个表上的字段进行,这样才有使用索引进行排序分组的可能性.
优化limit语句

limit offset,limit中的offset值很大时,查询的性能会直线下降,这个问题在单独的一篇文章中提过,可以看这里 limit语句的优化.

使用hint优化查询

MySQL提供了一些用于我们”提示”MySQL服务器应该怎样进行这个查询,需要注意的是,使用hint很有可能不会给你的程序带来性能上的提升,反而可能是性能下降,因此在使用前请确保自己了解该hint的作用.这里列举一些常用的hint的作用.

SQL_NO_CACHE

该提示让mysql不对这条数据的结果进行缓存.SELECT SQL_NO_CACHE xxx, yyy FROM TABLE;

SQL_CALHE

告诉mysql这条语句的结果需要缓存.SELECT SQL_CALHE * FROM TABLE;

HIGH_PRIORITY

告诉MySQL这条数据的优先级很高,在竞争一些互斥的资源时,这条语句将最先获得资源,SELECT HIGH_PRIORITY * FROM TABLE;

LOW_PRIORITY

与上面一个相反.

DELAYED

该hint会在insert和replace的时候使用,可以是的MySQL服务器立即返回结果,但是插入操作则在表空闲的时候进行.insert delayed into .....

STRAIGHT_JOIN

该hint告诉MySQL按照语句中的顺序进行多个表的关联操作,不要进行”优化”.select STRAIGHT_JOIN * from table1 join table2.

SQL_BUFFER_RESULT

该hint告诉mysql,将查询结果放入到临时表中,然后尽快释放表锁.SELECT SQL_BUFFER_RESULT * FROM TABLE ...;

SQL_BIG_RESULT和SQL_SMALL_RESULT

这两个hint只可以在select语句使用,它告诉MySQL结果集很大/很小.因此MySQL可以使用内存/文件进行排序等操作.SELECT SQL_BIG_RESULT * FROM TABLE ...;

FORCE INDEX和IGNORE INDEX

这两个hint告诉MySQL此查询语句强制使用或者不使用哪个索引.SELECT * FROM TABLE FORCE INDEX (FIELD) ...;

完。

ChangeLog

2019-06-03 完成

以上皆为个人所思所得,如有错误欢迎评论区指正。

欢迎转载,烦请署名并保留原文链接。

联系邮箱:huyanshi2580@gmail.com


本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券