mysql查询性能优化

优化数据访问

是否请求了不需要的数据

有些查询会请求超过实际需要的数据,然后多余的数据会被应用程序丢弃。这会给mysql服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。
比如:
1、查询不需要的记录
实际页面显示10条记录,但是查询去除100条记录,解决办法查询后面使用limit 10.
2、多表关联时返回全部列
如果只需要出现的演员,千万不要按照下面的写法:

1
select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title = 'Academy';

这将返回这三个表的全部数据列。正确的写法如下:

1
select actor.* from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title = 'Academy';

3、总是取出全部列
使用select * from table 的时候需要想一想所有的列是否都是需要的,尽量抛弃这种写法
4、重复查询相同的数据
不断地重复执行相同的查询,然后每次返回完全相同的数据。比较好的方案是查询的时候将这个数据缓存起来,需要的时候从缓存中取出。

mysql是否在扫描额外的记录

mysql最简单的衡量查询开销的三个指标:响应时间、扫描行数、返回行数。
没有哪个指标能够完美的衡量查询的开销,但它们大致反映了mysql在内部执行查询时需要访问多少数据,并可以大概推算出查询运行的时间。这三个指标都会记录到mysql的慢日志中,所以检查慢日志记录是找出扫描数过多的查询的好办法。
1、响应时间
响应时间只是一个表面上的值。
响应时间是服务时间和排队时间的和。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间–可能是等I/O操作完成,可能是等待行锁等等。
当看到一个查询的响应时间的时候首先问问自己这个响应时间是否是一个合理的值。
2、扫描的行数和返回的行数
在一定程度上能说明该查询找到需要的数据的效率高不高。
对于糟糕的查询,并不是所有的行的访问代价都是相同的,较短行的访问速度更快,因为内存中的行比磁盘中的行的访问速度要快得多。
理想情况扫描的行数和返回的行数应该是相同的。

扫描的行数和访问类型

在explain语句中的type列反应了访问类型。访问类型有很多种,从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。
如果查询没有办法找到合适的访问类型,那么解决办法是加一个合适的索引,请参考另一篇文章mysql高性能索引

重构查询方式

目标是找到一个更优的方法活的实际需要的结果–不一定总是需要mysql获取一模一样的结果集,有时候可以将查询转换一种写法让其返回一样的结果,但性能更好。

一个复杂查询还是多个简单查询

设计查询的时候需要考虑的问题是否需要将一个复杂的查询分成多个简单的查询。以往的逻辑认为网络通信查询解析和优化是一件代价很高的事情。
但是mysql并不适用,mysql从设计上让连接和断开都是很轻量级,返回一个小的查询结果方面很高效,现代的网络速度也比以前快很多。
不过在设计的时候如果一个查询能够胜任的时候还写成多个独立查询是不明智的。

切分查询

有时候对于一个大的查询需要分而治之,将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分查询结果。
例如删除一个月前的日志信息,数据量很大,可以分批次一次删除一万行数据,一般来说一万行数据是一个比较高效且对服务器影响也是最小的做法。同时需要注意的是每次删除之后暂停一会儿再做下次删除,将服务器压力分散到一个很长的时间中,可以大大降低服务器的影响,还可以减少删除时锁的持有时间。

分解关联查询

可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。
分解关联查询的方式重构查询有如下优势:
1、让缓存的效率更高。
2、减少锁竞争。
3、在应用层做关联,可以容易对数据库进行拆分,更容易做到高性能和扩展。
4、查询本身效率也可能会有所提升。
5、可以减少冗余数据的查询。

查询执行的基础

mysql客户单/服务器通信协议

mysql客户单和服务器之间的通信协议是半双工的,这意味着在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户单向服务器发送数据,这两个动作不能同时发生。
客户端用一个单独的数据包将查询传给服务器,这也是为什么当查询的语句很长的时候,参数max_allowed_packet就特别重要了。一旦客户端发送请求就只能等待结果了。
相反的一般服务器响应给用户的数据通常很多,有多个数据包组成,当服务器开始响应客户端请求时,客户端必须完整的接受整个返回结果,而不能单独的只取前面的几条结果,然后让服务器停止发送请求。
mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源,让查询早点结束可以早点释放响应的资源。

查询状态

对于一个mysql连接,或者说一个线程,任何时刻都有一个状态,该状态标识了mysql当前正在做什么。
show full processlist命令查看当前的状态。
Sleep
线程正在等待客户端发送新的请求。
Query
线程正在执行查询或者正在将结果发送给客户端。
Locked
在mysql服务器层,该线程正在等待表锁。
在存储引擎级别实现的锁,例如InnoDB的行锁并不会体现在线程状态中。
Analyzing and statistics
线程正在收集存储引擎的统计信息,并生成查询的执行计划。
Copying to tmp table [on disk]
线程正在执行查询,并且将结果集都复制到一个临时表中,这种状态一般要么是在做group by操作,要么是文件排序操作,或者是union操作。
如果这个状态后面还有 on disk 标记,那么标识mysql正在将一个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进行排序。
Sending data
线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。
了解这些状态的含义非常有用,在一个繁忙的服务器上可能会看到大量的不正常的状态,例如statistics正占用大量的时间。通常表示某个地方有异常了。
可以看mysql服务器性能剖析来诊断哪个环节出现问题了。

查询缓存

如果查询缓存是打开的,mysql会优先检查这个查询是否命中查询缓存中的数据,这个检查是通过一个对大小写敏感的哈希查找实现的。
如果命中了查询缓存,在返回结果之前mysql会检查一次用户权限。

查询优化处理

语法解析器和预处理

mysql通过关键字将sql语句进行解析,并生成一颗对应的解析树,mysql解析器将使用mysql语法规则验证和解析查询。预处理器则根据一些mysql规则进一部检查及解析树是否合法:表和数据列是否存在,别名是否有歧义,下一步预处理器会验证权限。

查询优化器

mysql使用基于成本的优化器,它会尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。
可以通过查询当前回话的Last_query_cost的值来得知mysql计算当前查询的成本。
执行查询 select sql_no_cache count(*) from actor; 后执行 show status like ‘Last_query_cost’;
结果value值标识mysql的优化器任务大概需要做多少个数据页的随机查询才能完成上面的查询。
有多重原因导致mysql有乎其选择错误的执行计划,如下所示:
1、统计信息不准确。
2、执行计划汇总的成本估算不等同于实际执行的成本。
3、mysql最优可能和你像的最优不一样。
4、mysql从不考虑其他并发执行的查询,这可能会影响当前查询速度。
5、mysql不会考虑不收其控制的操作成本。例如存储过程或者用户自定义的函数的成本。
6、优化器有时候无法估算所有可能的执行计划,所以可能错过最优的执行几环。

mysql如何执行关联查询

mysql认为任何一个查询都是一次“关联”,并不是一次查询需要两张表才叫关联查询。
对于union查询,mysql先将一系列的单个查询结果放到一个临时表中,然后重新读出临时表数据来完成union查询。
mysql任何关联都执行嵌套循环关联操作,即mysql先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止。
然后根据各个表匹配的行,返回查询中需要的各个列。

执行计划

mysql并不会生成查询字节码来执行查询。mysql生成查询的一颗指令树,然后通过存储引擎执行完成这棵指令树并返回结果,最终的执行计划包含了重构查询的全部信息。
如果对某个查询执行explain extended 后,在执行 show warnings 就可以看到重构出的查询。

关联查询优化器

mysql优化器最重要的一部分就是关联查询优化,它决定了多个关联表时的顺序。通常多表关联查询的时候,可以有多重不同的关联顺序来获取相同的执行结果。
关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。
绝大多数情况下优化都是有效的,但因为不会去计算每一种关联顺序的成本,所以偶尔也会选择一个不是最优的执行计划。

排序优化

无论如何排序都是一个成本很高的操作,所以从性能考虑,尽可能避免排序或者尽可能避免大量数据进行排序。
当不能使用索引生成排序结果的时候,mysql需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过mysql将这个过程统一称为文件排序filesort,即使完全是内存排序。
mysql在进行排序的时候需要使用的临时存储空间可能会比想象的要大得多,因为mysql在排序时,对每一个排序记录都会分配一个足够长的定长空间来存放。
在关联查询的时候如果需要排序,mysql会分两种情况来处理这样的文件排序。如果order by子句中的所有列都来自关联的第一个表,那么在关联处理第一个表的时候就进行文件排序,如果是这样在explain中的结果会看到Using filesort,除此之外mysql都会先将关联的结果放到一个临时表中,等所有的关联都结束后,在进行文件排序,这样explain会看到 Using temporary;sing filesort。如果查询有limit,limit会在排序之后应用。

查询执行引擎

在解析和优化阶段,mysql将生成查询对应的执行计划,mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构,而不是字节码。

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。即使没有结果也会返回一些信息比如影响到的行数。如果可以被缓存,mysql也会在这个阶段将结果放到查询缓存中。
mysql将结果集返回客户端是一个增量、逐步返回的过程。例如一个关联操作,一旦服务器处理完最后一个关联表,开始生成第一条结果时,mysql就可以开始向客户端逐步返回结果集了。
这样做的好处是:服务器无需存储太多的结果,而消耗太多内存。可以使用SQL_BUFFER_RESULT来影响整个行为。
结果集中的每一行都会以一个满足mysql通信协议的封包发送,再通过tcp协议进行传输,在tcp传输过程中,可能对mysql的封包进行缓存然后批量传输。

mysql查询优化器的局限性

mysql的万能“嵌套查询”并不是对每种查询都是最优的。

关联子查询

mysql的子查询实现得非常糟糕。最糟糕的是where查询中包含in()的子查询语句,例如:

1
select * from film where film_id in(select film_id from actor where actor_id = 1);

mysql对in()列表中的选项有专门的优化策略,会把上面的查询改写成下面的样子:

1
select * from film where exists(select * from actor where actor_id=1 and actor.film_id=film.film_id);

这时mysql选择对file表进行全表扫描,然后根据返回的film_id逐个执行子查询,如果外层是个非常大的表,性能会非常糟糕。可以用下面的办法重写这个查询:

1
select film.* from film inner join actor using(film_id) where actorid =1

union的限制

mysql无法将限制条件从外层下推到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
例如:

1
(select name from actor order by name) union all (select name from customer order by name) limit 20;

上面这条语句会把两个表所有的记录房子啊一个临时表,然后再从临时表取出前20条。
可以改写如下:

1
2
(select name from actor order by name limit 20) union all (select name from customer order by name limit 20
) order by name limit 20;

最大值和最小值优化

对于min() 和max()mysql优化做的并不好。例如:

1
select min(actor_id) from actor where name =‘penelope’;

因为name并没有索引,因此mysql将会进行一次全表扫描。如果mysql能够进行主键扫描那么理论上mysql读到第一个满足条件的记录的时候就是我们需要的最小值了,因为主键actor_id字段的大小顺序是排列的。
可以改写如下:

1
select actor_id from actor use index(primary) where name =‘penelope’ limit 1;

并行执行

mysql无法利用多核特性来并行执行查询。

在同一个表上查询和更新

mysql不允许对同一张同时进行查询和更新。
如下图所示:

可以改写成下图所示:

优化特定类型的查询

优化count()查询

count(*)对行的数目进行计算,包含NULL。
count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
count()还有一种使用方式,count(1)这个用法和count(*)的结果是一样的。
count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0。
当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为 NULL,因此使用 sum()时需注意 NPE 问题。
正例:可以使用如下方式来避免sum的NPE问题:SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
性能问题
1.任何情况下SELECT count(*) FROM tablename是最优选择;
2.尽量减少SELECT count(*) FROM tablename WHERE COL = ‘value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
如果表没有主键,那么count(1)比count(*)快。
如果有主键,那么count(主键,联合主键)比count(*)快。
如果表只有一个字段,count(*)最快。count(1)跟count(主键)一样,只扫描主键。count(*)跟count(非主键)一样,扫描整个表。明显前者更快一些。

优化关联查询

1、确保on或者using子句中的列上有索引,在创建索引的时候就需要考虑到关联的顺序,当表A和表B用到c关联的时候,如果优化器的关联顺序是B、A,那么就不需要在B表的对应列上加索引,没用到的索引只会带俩额外的负担,只需要在关联顺序中的第二个表的相应列上创建索引。
2、确保任何的group by和order by中的表达式只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程。
3、当升级mysql的时候要注意关联语法、运算符优先级等其他可能发生变化的地方。

优化子查询

子查询优化给出的最重要的优化建议就是尽可能使用关联查询代替。

优化group by 和distinct

如果有索引,它们都可以使用索引来优化,这也是最有效的优化办法。
如果没有索引的时候,group by 使用两种策略来完成:使用临时表或者文件排序来做分组。可以使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。
在分组查询的select中直接使用非分组列通常都不是什么好主意。
如果没有通过order by子句显式的指定排序列,当查询使用group by子句的时候结果集会自动按照分组的字段进行排序,如果不关心结果集的顺序,则可以使用order by null让mysql不在进行排序。

优化limit 分页

优化分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描,恶如不是查询所有的列。
例如下面的查询:

1
select film_id ,description from film order by title limit 50,5;

如果这个表非常大,最好改成下面的样子:

1
select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);

这里的延迟关联将大大提升查询效率,这个技术也可以优化关联查询中的limit子句。

优化SQL_CALE_FOUND_ROWS

limit语句加上这个提示虽然可以获取分页的总数,但是mysql总会扫描所有满足条件的行,然后在抛弃不需要的行,该代价非常高。一个更好的设计师将页数换成下一页按钮,假设每页20条记录,那么每次查询时都用limit返回21条记录并只显示20条,如果21条存在那么显示下一页按钮否则不显示。
有时候也可以考虑使用 explain的结果中的rows列的值来作为结果集总数的近似值。

优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询。因此很多优化策略在union查询中都没法很好的使用,经常需要手工的将where、limit、order by等子句下推到union的各个子查询中,以便优化器可以充分利用这些条件尽心优化。除非切实需要服务器消除重复的行,否则就一定要使用union all,如果没有all关键字,mysql会给临时表加上distinct选项,会导致对整个临时表的数据做唯一性检查,代价非常高。

静态查询分析

percon toolkit 中的pt-query-advisor能够解析查询日志、分析查询模式、然后给出所有可能存在潜在问题的查询,并给出足够详细的建议,像是给mysql所有的查询做一次全面的健康检查。

参考
(1)《高性能MySQL》

开发者首页 wechat
欢迎您扫一扫上面的微信公众号