前言
上一篇文章 《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来证实.
无法命中索引的一些操作
- 查询条件中使用不等于操作符
!= - 非前缀使用like
like '%gaga%'. - or操作符必须每个字段都建立索引
- 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,那么我们应该额外添加一张表或者一列来记录这个数值,而不是每次进行查询.
优化关联查询
- 确保on/where语句中的列上有索引.
- 确保order by / group by 只根据一个表上的字段进行,这样才有使用索引进行排序分组的可能性.
优化limit语句
当limit offset,limit中的offset值很大时,查询的性能会直线下降,
使用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) ...;
完。
原作者：呼延十
