mysql查询性能优化_MySQL-查询性能优化

在日常开发中，程序员写的最多的除了bug之外，应该算是SQL语句了。SQL的质量影响了程序的响应速度，只有利用MySQL的特性，才能让MySQL更有效的执行查询SQL，充分发挥MySQL的优势，并避开它的弱点。

为什么查询速度会慢?

在编写SQL之前，需要清楚一点 -- 真正重要的是响应时间。

如果我们把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，就是要优化其子任务，要么消除其中的一些子任务，要么减少子任务的执行次数。

慢查询

通过开启MySQL中的慢查询，可以明确的掌握系统中有哪些SQL查询在影响整个系统的性能，从而更准确的去针对这些SQL进行优化。

查询慢查询开启状态--ON/OFF

show variables like 'slow_query%';

开启慢查询日志

set global slow_query_log='ON';

查询超时记录时间：s

show variables like 'long_query_time';

设置查询超时(10s)记录

set global long_query_time=10;

查询性能低，最基本的原因是访问的数据太多。某些查询可能不可避免的需要筛选大量数据，但大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

使用LIMIT对查询数据进行限制

开发者通常会误以为MySQL只会返回需要的数据(例如SELECT 取出100条记录，只在页面显示前20条记录)，实际上MySQl却是先返回全部结果集后再进行计算。最简单有效的解决方法就是在查询后面加上LIMIT。

多表关联查询只取需要的列

例：查询所有在电影《七宗罪》中的演员

SELECT * FROM actor INNER JOIN filmactor USING(actorid) INNER JOIN film USING(film_id) WHERE

film.title='Se7en';

上述SQL将返回这三个表的全部数据列。正确的打开方式应该如下只取需要的列:

SELECT actor.* FROM actor...

看到SELECT　＊的时候都需要用怀疑的眼光审视

SELECT　＊会取出全部列，这样会让优化器无法完成索引覆盖扫描这列优化，还会为服务器带来额外的I／O、内存和CPU的消耗。因此项目组都会严格禁止SELECT　＊的写法。

合理使用缓存

当程序出现不断重复执行相同的查询SQL，然后每次返回的结果都一样的情况，作为一名合格的程序员，你应该考虑到将初次查询的结果集缓存起来。如果查询缓存是打开的，MySQL会优先检查这个查询是否命中缓存中的数据。这个检查是通一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。若当前的查询恰好命中缓存且用户权限没有问题，那么MySQL会跳过所有其他阶段，直接冲缓存中拿到结果并返回给客户端。

查询优化器

经过MySQL优化器选择的执行计划并不一定是最快的执行方式。

下面是一些MySQL能够处理的优化类型：重新定义关联表的顺序

将外连接转化成内连接

使用等价变换规则：MySQL可以使用一些等价变换来简化并规范表达式。例，(５＝５　AND　ａ＞５)将被改写为ａ＞５。

优化COUNT()，MIN()和　MAX()：索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，找某列的最小值，只需要查询B－Tree索引的最左端的记录。

覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回所需的数据，而无需查询对应的数据行。

提前终止查询

列表IN()的比较：MySQl将IN()列表中的数据先进行排序，然后通过二分法查找的方式来确定列表中的值是否满足条件，这是一个Ｏ(log　ｎ)复杂度的操作，等价的转换成OR查询的复杂度为O(ｎ)，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。

高效利用索引覆盖索引:如果一个索引包含所有需要查询的字段值,就称之为覆盖索引我们通常会根据WHERE条件来创建合适的索引,MySQl可以使用索引来直接获取列的数据。如果索引中的叶子节点已经包含要查询的数据,就没有必要回表查询了,减少I/O提高效率。

索引的最左前缀匹配:查询语句使用LIKE时,应使用key%,避免全表扫描。SELECT　．．．FROM xx WHERE col LIKE 'key%';

使用用户自定义变量

用户自定义变量是一个很容易被忽略的MySQL特性,但是如果能够用好,发挥其潜力在某些场景下可以写出非常高效的查询语句

用户自定义变量是一个用来存储内容的临时容器,可使用SET和SELECT语句来定义:

SET @one:=1;

SET @min_actor:=(SELECT MIN(actor_id) FROM actor);

SET @last_week:=CURRENT_DATE - INTERVAL 1 WEEK;

可以在任意可以使用表达式的地方使用自定义变量.

SELECT ... WHERE col <= @last_week;

一下场景不能使用用户自定义变量:使用自定义变量的查询,无法使用查询缓存

不能在使用常量或标识符的地方使用自定义变量,例如表名,列名,LIMIT子句中

用户自定义变量的生命周期是在一个连接中生效,所以不能用他们来做连接间的通信

MySQL优化器在某些场景下可能会将这些变量优化掉

赋值符号:=的优先级非常低,所以需要注意 赋值表达式应该使用明确的括号

使用自定义变量示例:

#实现对电影演员演出场次的排名

#只有当前演员参演的电影数量和前一个演员不同时排名才会发生变化

#使用三个变量分别存储当前的排名,前一个演员的排名,当前演员参演的电影数量

SET @curr_cnt := 0,@prev_cnt := 0,@rank :=0;

SELECT actor_id,

@curr_cnt := cnt AS cnt,

@rank := IF(@prev_cnt <> @curr_cnt,@rank + 1,@rank) AS rank,

@prev_cnt := @curr_cnt AS dummy

FROM (

SELECT actor_id,COUNT(*) AS cnt

FROM film_actor

GROUP BY actor_id

ORDER BY cnt DESC

LIMIT 10

) AS der;