1.引言
我们在上一篇文章中讨论了高性能的索引设计,实际上除了需要设计优秀的库表结构,建立最优的索引,还需要设计合理的查询,即优秀的库表、索引和查询三驾马车保证了MySQL的高效读写,查询优化、索引优化和库表结构优化需要齐头并进。
当发现查询性能不佳时,我们需要考虑哪些因素呢?当明确知道需要改变MySQL的执行计划,那么需要做什么来改变执行计划呢。为了更好的设计出高性能的查询语句,我们还需要知道MySQL优化器的内部机制并了解优化器哪些做得还不够,有优化空间。
2.查询优化目标
可以将一个查询SQL看做是对数据库发起的查询任务,这个任务可能对应于多个子任务,要提高查询性能,既可以减少查询的任务数,又可以通过减少子任务的执行时间来得到目标。其中,改变查询执行计划可以类比于使用更优秀的算法;而减少子任务执行时间常用的方法之一也是缓存,类比于递归算法优化的记忆化技术。
一次查询基本可以分成:从存储引擎检索数据 + 服务层做数据处理,包含排序、分组等。完成这些任务需要一定的资源开销,比如网络、CPU计算、生成统计信息和执行计划、锁等待等。在从底层存储引擎检索数据的调用操作,这些调用需要在内存操作、CPU操作,内存不足的IO操作上消耗时间。
在每一个消耗大量时间的查询案例中,我们都能看到一些不必要的操作:比如某些操作被额外的重复执行了很多次,某个操作执行得太慢。
优化查询的目的是:减少消除这些操作所花费的时间。
3.查询不佳需要考虑的因素
查询不佳最基本的原因是:访问的数据太多。也许某些查询不可避免的需要筛选大量数据,但这并不太常见,大部分性能低下的查询都可以通过减少访问的数据量来进行优化。在做性能优化时需要把以下2点做为你SQL优化的思考方向:是否检索了大量超过需要的数据。
MySQL服务层是否在分析大量超过需要的数据行。
3.1.是否检索了大量超过需要的数据
应用程序员千万别把数据库当成一个存储和检索数据的黑盒,多余的数据不仅会额外增加MySQL的负担,并增加网络开销,同时也会消耗应用服务器的CPU、内存资源。多余的数据会被应用程序白白丢弃真的挺浪费的。这里有一些常见的浪费行为:查询不需要的数据:比如总共100条,但是我只需要前面10条,不应该去数据库查询所有数据,然在在应用层取前面10条,正确的做法是在查询条件后带上LIMIT进行限制。
多表关联时返回所有列:其实我只是需要主表的记录,关联的表字段用来做筛选。比如在MySQL测试数据库sakila中,需要查询所有在电影Academy Dinasaur中出现的演员。错误的查询如下:
select * from actor
inner join film_actor using(actor_id)
inner join film using(film_id)
where film.title = 'Academy Dinosaur';
film_id|actor_id|first_name|last_name|last_update |last_update |title |description |release_year|language_id|original_language_id|rental_duration|rental_rate|length|replacement_cost|rating|special_features |last_update |
-------|--------|----------|---------|-------------------|-------------------|----------------|------------------------------------------------------------------------------------------------|------------|-----------|--------------------|---------------|-----------|------|----------------|------|--------------------------------|-------------------|
1| 1|PENELOPE |GUINESS |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 10|CHRISTIAN |GABLE |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 20|LUCILLE |TRACY |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 30|SANDRA |PECK |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 40|JOHNNY |CAGE |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 53|MENA |TEMPLE |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 108|WARREN |NOLTE |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 162|OPRAH |KILMER |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 188|ROCK |DUKAKIS |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
1| 198|MARY |KEITEL |2006-02-15 04:34:33|2006-02-15 05:05:03|ACADEMY DINOSAUR|A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies| 2006| 1| | 6| 0.99| 86| 20.99|PG |Deleted Scenes,Behind the Scenes|2006-02-15 05:03:42|
正确做法
select actor.* from actor
inner join film_actor using(actor_id)
inner join film using(film_id)
where film.title = 'Academy Dinosaur';
actor_id|first_name|last_name|last_update |
--------|----------|---------|-------------------|
1|PENELOPE |GUINESS |2006-02-15 04:34:33|
10|CHRISTIAN |GABLE |2006-02-15 04:34:33|
20|LUCILLE |TRACY |2006-02-15 04:34:33|
30|SANDRA |PECK |2006-02-15 04:34:33|
40|JOHNNY |CAGE |2006-02-15 04:34:33|
53|MENA |TEMPLE |2006-02-15 04:34:33|
108|WARREN |NOLTE |2006-02-15 04:34:33|
162|OPRAH |KILMER |2006-02-15 04:34:33|
188|ROCK |DUKAKIS |2006-02-15 04:34:33|
198|MARY |KEITEL |2006-02-15 04:34:33|总是取出所有列:每次需要抱有怀疑的目光审视:select *,返回所有列可能会导致无法完成索引覆盖扫描优化,查询很可能必须走二次回表查询,这样会给MySQL服务器带来额外的IO、内存和CPU的消耗,因此DBA一般会限制select * 的使用。这里有同学会说我有办法规避不用select * ,我用select all_fields替换 select *,这么做的原因在于我查下出所有字段后,应用程序可以做更灵活的处理。一个SQL语句可以尽可能的满足各种应用场景,提高了代码的复用。这种从代码复用的角度思考问题是可以的,但任何选择都有机会成本,你需要直到你这么做了可能对性能产生什么影响,能不能接受?重复查询相同的数据:对于这个问题,最后的解决办法是加缓存,第一次查找时缓存数据。
3.2.是否在扫描额外的记录
对MySQL来说,衡量查询开销有三个指标:响应时间。
扫描的行数。
返回的行数。
大部分人可能只关心响应时间而忽略扫描的行数和返回行数。但其实响应时间并没有什么一致的规律或公式。参考扫描行数和返回行数评估查询开销更加可行。实际上分析查询扫描行数是非常有帮助的,即使不同行的访问代价不同(较短行和较长行访问代价不同;内存中的行和磁盘中的行访问代价不同)。
扫描行数和返回行数比例
如果扫描的行数和返回的行数一样,那这个查询简直太完美了,但实际上如果扫描行数:返回行数在1:1到10:1之间,这种查询就还行。
访问类型
评估性能开销时,需要考虑从表中找到一行的成本,MySQL有好几种访问方式,速度从慢到快,扫描行数从多到少分为:全表扫描。
索引扫描。
范围扫描。
唯一索引查询。
常数引用。
等。我们不需要记住这些访问类型,但需要明白扫描表、扫描索引、范围访问和单值访问的概念。
索引如何避免扫描过多的行
如果查询没有办法找到合适的访问类型,那么最好的办法就是增加一个合适的索引。索引对于高效查询如此重要,原因就在于索引能够让MySQL以最高效、扫描行数最少的方式找到需要的记录。
还是以sakila这个测试库来举例。首先执行以下查询。
mysql> select * from film_actor where film_id=1;
+----------+---------+---------------------+
| actor_id | film_id | last_update |
+----------+---------+---------------------+
| 1 | 1 | 2006-02-15 05:05:03 |
| 10 | 1 | 2006-02-15 05:05:03 |
| 20 | 1 | 2006-02-15 05:05:03 |
| 30 | 1 | 2006-02-15 05:05:03 |
| 40 | 1 | 2006-02-15 05:05:03 |
| 53 | 1 | 2006-02-15 05:05:03 |
| 108 | 1 | 2006-02-15 05:05:03 |
| 162 | 1 | 2006-02-15 05:05:03 |
| 188 | 1 | 2006-02-15 05:05:03 |
| 198 | 1 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
10 rows in set (0.00 sec)
挺快的,查看其执行计划,如下。
mysql> explain select * from film_actor where film_id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
从执行可以看出,MySQL在索引idx_fk_film_id上使用了ref访问类型来执行查询,预估访问10行数据,而我们结果集也是10行数据,很高效了。
如果我们删除索引,再来看看。
mysql> alter table film_actor drop foreign key fk_film_actor_film;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table film_actor drop key idx_fk_film_id;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from film_actor where film_id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5462
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
删除索引后,访问类型变成了一个全表扫描(ALL),MySQL预估需要扫描5073条记录来完成这个查询。从Extra看出使用了where条件来筛选存储引擎返回的记录。
注:
MySQL有三种方式使用where条件筛选数据,效率从高到低依次是:在索引中使用WHERE条件来过滤不匹配的记录,这是在存储引擎层完成的。
使用索引覆盖扫描来返回记录,直接从索引中过滤不需要的记录并返回命中的结果,这是在MySQL服务层完成的,但无需再回表查询记录。
从数据表中返回数据,然后过滤掉不满足条件的记录,这在MySQL服务层完成。
无法避免的数据扫描
对于某些统计类需求,如下所示:
select actor_id, count(*) from film_actor group by actor_id;
这个查询需要读几千行数据,但是只返回了200条结果。也许扫描的大部分数据对结果集没有帮助,但是没办法。
最后总结一下,如果发现查询需要扫描大量的数据但是只返回少数的行,那么通常可以尝试下面的方法去优化它:使用覆盖索引扫描,把所有需要用的列都放到索引中,这样存储引擎就无需回表获取对应行就可以返回结果了。
改变库表结构,比如统计类使用单独的汇总表。
重写查询,让MySQL以更优的方式执行这个查询。
4.重写查询
本节,我们来讨论一下通过重写查询来优化查询的话题。在优化有问题的查询时,有两种思路:改写查询,充分利用索引等手段获得相同的结果集,但查询具有更好的性能。
修改应用代码,用另一种方式完成查询,最终达到一样的目的,获得实际需要的结果。
4.1.一个复杂查询还是多个简单查询
写一个复杂的查询,让数据库尽可能完成更多的工作,这个结论的假设前提是:网络通信、查询解析和优化是一件代价很高的事情。
但实际上,MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的结果集方面很高效,这让笔者想起了微服务架构,不完全一样,但有点类似。加上现代网络,不论是带宽还是延迟,都比以前快很多,在一个通用服务器上,也能够运行每秒超过10万次查询。运行多个小查询不是什么问题。
如果多个查询可以简化工作,提高效率,那么为什么不做呢?但辩证的看,如果应用设计时,一个查询就能够胜任还写成多个独立查询是不明智的。
4.2.切分查询
将一个大查询分而治之,比如很多时候你需要清理旧的过期了的数据,比如清理3个月前的数据,如果一次性删除,你可能需要一次锁很多数据,占用整个事务日志,耗尽系统资源,并且会导致很多小的重要的查询被阻塞。采用切分方法,如下所示:
rows_affected = 0
do {
rows_affected = do_query(
"delete from messages where created < DATE_SUB(now(), inteval 3 MONTH) limit 10000"
)
} while rows_affected > 0
一次删除1万行数据是一个比较高效且对服务器影响较小的做法。如果每次删除后sleep一段时间在继续删除,这样做可以将服务器原本一次性的压力分散到一个很长的时间中,其实就是分散风险的思想。这让我想到了通过定投来防止基金高买低卖的风险。
4.3.分解关联查询
企业级应用开发中基本都会要求少用join,多做应用关联,为什么呢,明明一条SQL就可以搞定的,为什么要做这种拆分?
如下所示,是一种join查询。
select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';
一般DBA不让应用开发人员这么做,为此需要分解关联查询:首先,通过tag表筛选数据,如下所示:
select * from tag where tag = 'mysql';假设,根据tag查出对应的id为1234,则根据这个条件继续查询第二张表,如下所示:
select * from tag_post where tag_id = 1234;根据第二张表查出对应post_id为123,456,789,9098,则带上这个条件查询第三张表,如下所示:
select * from post where id in (123,456,789,9098);
这样就完成了join的分解。
可是,为什么需要这么做的?
有人说,这样做后代码逻辑更加清晰,可阅读性强,笔者并不是特别认可这种解释。真正分解join查询的原因在于:有助于重复利用单表的查询缓存结果,让查询效率更高。比如,上面分解后第一步对tag的查询如果已经缓存,则可以跳过这一步进入第二步。基于单表查询可以重复利用缓存结果。这里的缓存可以是应用层缓存也可以是MySQL的Query Cache。
查询分解有利于减少锁的竞争。
使用in()代替关联查询可以让MySQL按照ID顺序进行查询,这可能比随机的关联要更高效。
应用层关联有利于数据库拆分。
5.查询执行过程
充分了解MySQL的查询执行细节有助于设计出更加合理的查询。一旦理解了查询执行细节,很多查询优化工作实际上就是遵循一些原则,让优化器按照预想的合理的方式运行。
如下所示是MySQL执行查询的路径。
大致过程如下:客户端发送一条查询给MySQL服务器。
服务端先检查查询缓存,如果命中,则立即返回结果。否则进入下一阶段。
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划。
根据执行计划调用存储引擎层的API来执行查询。
将结果返回给客户端并缓存查询结果。
通信协议
MySQL客户端和服务器之间的通信协议是半双工的,这意味着在任何时刻,要么是服务端向客户端发送数据,要么是客户端向服务端发送数据。一旦一端开始发送消息,另一端要接收完整的消息才能响应。MySQL的通信相对简单。
多数连接MySQL的库函数都可以获得结果集并缓存到内存,因此很多时候看起来好像是从MySQL服务器获取数据,其实是从库函数的缓存获取数据。当然多数库函数也提供查询无缓存的API。
查询缓存
在解析一个查询语句前,若缓存是打开的,MySQL首先会检查是否命中缓存,这个检查是通过哈希查找实现的。
查询优化处理
MySQL使用基于成本的优化器,我们可通过查询当前会话的Last_query_cost来得知MySQL计算当前的查询成本。
mysql> select SQL_NO_CACHE count(*) from film_actor;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+
1 row in set, 1 warning (0.03 sec)
mysql> show status like 'Last_query_cost';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| Last_query_cost | 558.199000 |
+-----------------+------------+
1 row in set (0.00 sec)
这个结果表明MySQL优化器需要做558次数据页的随机查找才能完成上面的查询,而这个计算结果是通过统计信息计算得到的,统计信息包含:每个表或者索引的页面个数。
索引的基数,即索引中不同值的个数。
索引和数据行的长度。
索引分布情况。
优化器在做评估时,不考虑缓存,它假设任何数据都需要一次磁盘IO。、
MySQL评估并不保证完全正确,统计信息不准、执行计划的成本估算和真实执行的成本也很可能不同,种种原因都可能造成MySQL无法选择最优的执行计划。
总之,MySQL优化器是一个非常复杂的部件,它使用了很多优化策略来生成最优的执行计划。
下面我们大致了解一下MySQL优化器能够做的优化:重新定义表的关联顺序:数据表的关联并不总是按照查询语句中指定的顺序进行的。这让笔者想到了JMM模型的指令乱序执行。
将外连接转换成内连接:where条件、库表结果可能导致一个外连接等价于内连接。
使用等价变换规则,比如 5 = 5 and a > 5等价于a > 5。
优化count()、min()、max()。
预估并转换成常量表达式:当MySQL检测到一个表达式可以转换成一个常量时,就会一直把该表达式作为常数进行优化。例如,在一个索引列上执行min()函数。
mysql> select film.film_id, film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id=1;
+---------+----------+
| film_id | actor_id |
+---------+----------+
| 1 | 1 |
| 1 | 10 |
| 1 | 20 |
| 1 | 30 |
| 1 | 40 |
| 1 | 53 |
| 1 | 108 |
| 1 | 162 |
| 1 | 188 |
| 1 | 198 |
+---------+----------+
10 rows in set (0.00 sec)
mysql> explain select film.film_id, film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id=1;
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
MySQL分2步来执行查询计划,第一步由于是主键索引,所以MySQL明确知道会返回一行数据,第二步优化器已经明确知道有多少个值需要做索引查询。覆盖索引扫描。
子查询优化:MySQL在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问。
列表in()比较:MySQL会将in()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件。
关联查询
MySQL关联查询时这样做的:首先关联查询第一个表的记录,再嵌套查询下一个关联表,然后再回溯到上一个表,MySQL这种嵌套循环实现关联的方式较嵌套循环关联。关联查询优化是MySQL优化器最重要的部分之一。
6.优化特定类型的查询
优化count
此方法针对MyISAM有效。
-- 优化前
mysql> select count(*) from city where id > 5;
+----------+
| count(*) |
+----------+
| 4074 |
+----------+
1 row in set (0.01 sec)
-- 优化后
mysql> select (select count(*) fnt(*) from city) - count(*) from city where id <= 5;
+----------------------------------------+
| (select count(*) from city) - count(*) |
+----------------------------------------+
| 4074 |
+----------------------------------------+
1 row in set (0.00 sec)
-- 查看执行计划
mysql> explain select count(*) from city where id > 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | city | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 4079 | 99.88 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select (select count(*) from city) - count(*) from city where id <= 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
| 1 | PRIMARY | city | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 6 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
优化关联查询
关联查询需注意以下几点:确保on或者using子句中的列上有索引。比如A、B两表通过列c关联,优化器的关联顺序是B、A,那么,我们只需要对A表对c加索引,而无需在B上加索引。
确保group by 和 order by中的表达式只涉及一个表的列,这样MySQL可通过索引来优化这个过程。且group by 最好使用标识列分组效率会更高。比如
-- 优化前
mysql> select actor.first_name,actor.last_name, count(*) from film_actor inner join actor using(actor_id) group by actor.first_name, actor.last_name;
-- 优化后
mysql> select actor.first_name,actor.last_name, count(*) from film_actor inner join actor using(actor_id) group by film_actor.actor_id;
优化limit分页
LMIT最大的问题是当偏移量非常大时,如果无法命中索引,则代码非常高。
比如,limit 10000, 20, 这时MySQL会查询10020条记录,然后返回最后20条,前面的10000条记录将会丢弃,代价非常高。
方法1:覆盖索引
数据库:sakila
-- 优化前
mysql> explain select * from film order by title limit 50, 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
可以看出MySQL需要做大量的文件排序操作(Using filesort),操作类型是全表扫描,扫描行数预估1000行。
优化后
mysql> explain select title from film order by title limit 50, 5;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_title | 514 | NULL | 55 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
方法二:延迟关联
mysql> explain select film_id, description
-> from film
-> inner join (
-> select film_id from film order by title limit 50, 5
-> ) as lim using(film_id);
+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 55 | 100.00 | NULL |
| 1 | PRIMARY | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | lim.film_id | 1 | 100.00 | NULL |
| 2 | DERIVED | film | NULL | index | NULL | idx_title | 514 | NULL | 55 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
延迟关联让MySQL扫描尽可能少的页,获取需要访问的记录后再根据关联列回原表查询需要的所有列。
方法3:Limit转换成已知位置。
mysql> explain select title from film where position between 50 and 54 order by position;
总结
MySQL查询需要将查询SQL转换成执行计划任务,然后运行查询计划,查询优化分析的方向是减少执行任务,减少执行任务执行的时间。一般而言,慢查询主要在于查询过程中检索了大量超过需要的数据,扫描了大量不需要的记录。查询优化分3个方向:尽可能使用覆盖索引、通过改变库表结构进行优化以及
重写查询。本文最后部分分析了查询执行过程以及针对特定类型SQL优化的几个例子。
The end.
转载请注明来源,否则严禁转载。