mysql性能分析三驾马车_查询性能优化理论篇-如何合理的设计与优化查询

最新推荐文章于 2024-01-16 10:08:04 发布

Sei Kyo

最新推荐文章于 2024-01-16 10:08:04 发布

阅读量128

点赞数

文章标签： mysql性能分析三驾马车

本文链接：https://blog.csdn.net/weixin_42512183/article/details/113306490

版权

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';

-------|--------|----------|---------|-------------------|-------------------|----------------|------------------------------------------------------------------------------------------------|------------|-----------|--------------------|---------------|-----------|------|----------------|------|--------------------------------|-------------------|

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;

+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+

+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+

| 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;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select (select count(*) from city) - count(*) from city where id <= 5;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

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;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

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;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

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);

+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+

+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+

+----+-------------+------------+------------+--------+---------------+-----------+---------+-------------+------+----------+-------------+

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.

转载请注明来源，否则严禁转载。

Sei Kyo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql性能分析三驾马车_查询性能优化理论篇-如何合理的设计与优化查询

1.引言我们在上一篇文章中讨论了高性能的索引设计，实际上除了需要设计优秀的库表结构，建立最优的索引，还需要设计合理的查询，即优秀的库表、索引和查询三驾马车保证了MySQL的高效读写，查询优化、索引优化和库表结构优化需要齐头并进。当发现查询性能不佳时，我们需要考虑哪些因素呢？当明确知道需要改变MySQL的执行计划，那么需要做什么来改变执行计划呢。为了更好的设计出高性能的查询语句，我们还需要知道MyS...
复制链接

扫一扫