MySQL 查询性能优化

最新推荐文章于 2025-06-07 11:30:00 发布

weixin_33817333

最新推荐文章于 2025-06-07 11:30:00 发布

阅读量126

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python 数据结构与算法

原文链接：https://my.oschina.net/peakfang/blog/2875290

2019独角兽企业重金招聘Python工程师标准>>>

1、为什么查询速度会慢

查询是由一系列子任务组成的，要优化查询，实际上要优化其子任务，要么消除一些子任务、要么减少子任务执行次数、要么让子任务运行的更快。

查询的生命周期：从客户端，到服务器，在服务器上进行解析，生成执行计划，执行，返回结果给客户端，其中执行是最终要的阶段，其中包括检索数据到存储引擎的调用及调用后数据处理包括排序，分组等。

查询花费时间包括：网络，CPU计算，生成统计信息和执行计划，锁等待，等，尤其是底层存储引擎检索数据的调用操作。慢查询都包括不必要的额外操作，额外的操作被多次执行，操作执行的太慢优化查询的目的就是减少和消除这些操作花费的时间。

2、慢查询基础，优化数据访问

查询性能低下最基本的原因是访问的数据太多，大部分查询优化都可以通过减少访问数据量的方式进行优化。

确认应用程序是否在检索大量超过需要的数据，这意味着访问太多的行；

确认MySQL服务器层是否在分析大量超过需要的数据行；

2.1、是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后多于的数据会被应用程序丢掉，但这样会给MySQL服务器带来额外负担，增加网络开消，服务器的CPU和内存资源。

典型案例包括：

查询不需要的记录：先返回全部的结果集再进行计算，如查询100条数据，最后只用前10条，有效的方法是在查询后加limit；

多表关联时返回全部列：多表连接查询select *；

总是取出全部列：select * 优化器无法使用索引覆盖扫描，同时增加服务器的CPU、IO、内存资源，但在特殊场景这种处理能增加应用程序代码的复用性，需要综合考虑；

重复查询相同的数据：重复的执行相同的查询，每次返回相同的数据，应该在初次查询时候将数据缓存起来，需要的时候从缓存中取；

2.2、MySQL是否在扫描额外的记录

检查MySQL查询为了返回结果是否扫描了过多数据。衡量查询开销的三个指标响应时间、扫描行数、返回行数。这三个指标需要综合考虑，大概据此推算出查询运行时间，同时这三个指标都会记录到慢查询日志，可以通过检查慢查询日志找到扫描过多数据行的查询。

响应时间

响应时间是两部分之和，服务时间和排队时间，服务时间是数据库处理这个查询真正花费的时间，排队时间服务器因为等待某种资源而没有执行查询的时间，可能是IO等待，行锁，等。可以使用“快速上限估算法”估算查询响应时间。

扫描的行数和返回的行数，理想情况下扫描的行数和返回的行数是相等的，但通常扫描的行数是返回行数的好多倍；

MySQL应用where条件的方式：

在索引中使用where条件过滤不匹配的记录，在存储引擎层完成；

使用索引覆盖扫描（extra有Using index）直接从索引中过滤掉不需要的记录返回结果，这在MySQL服务器层完成，但无需再回表查询记录；

从数据表中返回数据，然后过滤不满足条件的记录（extra Using where）在MySQL服务器层完成，即通过where条件筛选存储引擎返回的记录；

针对分组统计类型的查询，没有办法减少需要扫描的行数。

查询需要扫描大量数据，但只返回少数的行优化方案：

使用索引覆盖扫描；利用汇总表；重写复杂的查询；

3、重构查询的方式

MySQL从设计上让连接和断开连接都是轻量级，在返回一个小的查询结果方面更是高效。

切分查询：

删除旧数据，大的删除需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞其他操作，可以将大的删除操作分解成小的范围删除。

delete from emp where hiredate > date_sub(now(),interval 3 month);

将单个大的delete语句换成分批删除

row_affected = 0

do{

row_affected = do_query("delete from emp where hiredate > date_sub(now(),interval 3 month) limit 10000")

} while row_affected > 0

分解关联查询：

高性能应用会将关联查询进行分解，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

分解关联查询的优势：让缓存的效率更高，单个查询减少锁争用，应用关联更容易对数据库进行拆分，单个查询本身性能提上，减少冗余记录的查询，应用中关联比MySQL自身的嵌套循环关联效率更高。

4、查询执行的基础

弄清楚MySQL是如何优化和执行查询的，查询优化工作实际上就是遵循一些原则，让优化器能够按照预想的合理的方式运行。

MySQL执行一条查询的过程

a.客户端发送一条SQL语句给服务器；

b.服务器先检查查询缓存，如果命中了缓存，立刻返回缓存中的结果，否则进入下一阶段；

c.服务器端进行SQL解析，预处理，再由优化器优化生成对应的执行计划；

d.MySQL根据执行计划，调用存储引擎的API来执行查询；

e.将结果返回给客户端；

4.1、客户端服务器通信协议

MySQL客户端服务器通信协议是“半双工”的，意味着任何时刻要么服务器向客户端发数据，要么客户端向服务器发数据，两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。客户端开始接收数据时也必须接收整个返回结果。

不同程序设计语言可以通过不同接口设置是否缓存结果集到内存。

查询状态，通过show full processlist命令可以查看线程的状态Command列，可以明确知道当那个线程的工作情况。

Command状态	状态含义
Sleep	线程正在等待客户端发送新的请求
Query	线程正在执行查询或者正在将结果发送给客户端
Locked	MySQL服务器层，线程正在等待锁
Analyzing and statistics	线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table[on disk]	线程在执行查询，将结果集复制到一个临时表一般是在做group by，文件排序，UNION操作
Sorting result	线程正在对结果集进行排序
Sending data	线程可能在多个状态传送数据，或者生成结果集，或者在向客户端返回数据

mysql> show full processlist;

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

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

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

1 row in set (0.00 sec)

4.2、查询缓存

查询缓存如果是打开状态，执行查询时，MySQL会优先在查询缓存中通过哈希查找缓存的查询，如果没有命中就会进入下一阶段处理；如果恰好命中，返回查询结果之前MySQL会检查一次用户权限，如果权限也没有问题，MySQL会跳过其他所有阶段，直接从缓存中拿结果，并返给客户端。这种情况下查询不会被解析，不用生成执行计划，不会被执行。

4.3、查询优化处理

包括多个子阶段，解析SQL、预处理、优化SQL执行计划。

a、语法解析器和预处理

MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”MySQL解析器将使用语法规则验证和解析查询；预处理器则根据一些MySQL规则进一步检查解析树是否合法，检查数据表数据列是否存在等。

b、查询优化器

优化器将解析树转化成执行计划，一条查询可能有很多种执行方式，最后结构都相同，优化器的作用是从这些执行计划中找到最好的执行计划。

MySQL使用基于成本的优化器，通过预测各种执行计划的成本，并选择其中成本最小的执行计划。可通过如下命令查询上次查询的成本（N个数据页的随机查找）

mysql> select count(1) from film_actor;

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

| count(1) |

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

| 5462 |

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

1 row in set (0.00 sec)

mysql> show status like 'Last_query_cost';

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

| Variable_name | Value |

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

| Last_query_cost | 1104.399000 |

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

1 row in set (0.00 sec)

很多种原因导致MySQL优化器选择错误的执行计划：

统计信息不准确，依赖存储引擎提供的统计信息评估成本；

执行计划的成本估算不等于实际执行的成本，查询过程中到底需要多少次物理IO是无法得知的；

MySQL不考虑并发执行的查询；

MySQL也并不是任何时候都基于成本优化，如在全文索引的MATCH()子句则基于规则；

静态优化：直接对解析树进行优化，如简单的代数替换，第一次完成后就一直有效，也叫编译时优化，只做一次；

动态优化：和查询的上下文及很多因素有关，也叫运行时优化，每次执行都要重新评估；

MySQL能够处理的优化类型：

重新定义关联表的顺序，

将外连接转换成内连接，

使用等价变换规则，使用等价变换简化表达式

优化count(),min(),max(),explain中可以看到“select tables optimized away”表示使用优化

预估并转换为常数表达式，

覆盖索引扫描，

子查询优化，将子查询转化成一种效率更高的形式；

提前终止查询，在发现已经满足查询需求时，立刻终止查询，Limit子句，“不同值、不存在”优化一般可用于distinct not exist,left join；

等值传播，

列表in()的比较，MySQL对in()的操作与其他数据库不同，处理速度很快；

4.4、数据和索引的统计信息

统计信息由存储引擎实现并提供，统计信息主要包括每个表或者索引有多少个页面，每个索引的基数，数据行和索引的长度，索引的分布信息等。

4.5、MySQL如何执行关联查询

MySQL对任何关联都执行嵌套循环关联操作，可以通过“泳道图”可视化展示关联查询过程，将右外连接转换成等价的左外连接。总是从一个表开始一直嵌套循环、回溯完成所有表关联。

MySQL的临时表是没有任何索引的。

4.6、执行计划

MySQL并不会生成查询字节码来执行查询，MySQL生成一棵指令树，然后通过存储引擎执行完这课指令树并返回结果，因为MySQL的关联特性，指令树总是一棵左侧深度优先的树

4.7、关联查询优化器

关联查询优化器会通过评估不同顺序的成本，选择一个代价最小的关联顺序，如果让优化器按照预想顺序关联可是使用straight_jion提示。当关联表过多时，关联的顺序就会非常多，MySQL不可能逐一评估其成本，优化器会选择“贪婪”搜索的方式获取最优执行计划。左连接和相关子查询会明确关联的顺序性。

4.8、排序优化

MySQL有两种排序算法，两次传输排序（旧版本），单次传输排序（新版本）。当查询所有列的总长，不超过参数max_length_for_sort_data，MySQL使用单次传输排序。MySQL排序使用的临时存储空间比较大，因为每一个排序记录都会分配一个足够长的定长空间来存储。

在关联需要排序时，如果orderby自己的字段全部来自第一个关联表，那么MySQL在处理第一个表时就进行文件排序，explain 显示Using filesort，除此之外MySQL都是先将关联结果放入一个临时表，在所有关联都结束后再进行文件排序explain 会显示Using temporary;Using filesort，如果有LIMIT，也会将limit应用在排序之后。

MySQL5.6做了改进，当只需要返回部分排序结果的时候，MySQL不再对所有结果排序，根据实际情况抛弃不满足条件的结果，再进行排序。

4.9、查询执行引擎

查询执行引擎根据查询执行计划完成整个查询，这里执行计划是一个数据结构。

4.10、返回结果给客户端

同时将结果放入查询缓存，返回结果是一个增量，逐步返回的过程，一旦关联处理完，开始生成第一条结果时就可以开始项客户端逐步返回结果了。服务端不用存储太多结果，客户端也能第一时间获得结果。

5、MySQL查询优化器的局限性

5.1、关联子查询

MySQL的子查询实现的比较糟，特别是where子句中in(子查询)，可以通过改写SQL优化，改成where exists(子查询，关联外表)

5.2、如何用好关联子查询

通过实际情况的分析和测试，确定最优的写法（几个SQL语句的实验）

5.3、UNION的限制

有时MySQL无法将限制条件从外层“下推”至内层，使得原本能够限制部分返回结果的条件无法应用到内层查询优化上。如UNION连接后的结果集取Limit N需要给每个字句添加

LIMIT，否则取出个字句表的全部数据，再取LIMIT N，LIMIT的写法。

mysql> explain (select first_name,last_name from actor order by last_name) union all (select first_name,last_name from customer order by last_name) limit 20;

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

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

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

3 rows in set (0.00 sec)

mysql> explain (select first_name,last_name from actor order by last_name limit 20) union all (select first_name,last_name from customer order by last_name limit 20) limit 20;

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

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

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

3 rows in set (0.00 sec)

5.4、索引合并优化

在MySQL5.0及更新版本，当where字句中包含复杂条件的时候，MySQL能访问单个表的多个索引以合并和交叉的方式来定位需要查找的行。

5.5、等值传递

等值传递会带来额外的消耗，如非常大的in()列表，同时存在多表关联时，优化器会将in()列表都复制应用到关联的各个表中。导致查询变慢。

5.6、并行执行

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

5.7、哈希关联

MySQL并不支持哈希关联，MySQL的所有关联都是嵌套循环，Memory存储引擎支持哈希索引，通过哈希索引关联就相当于实现了哈希关联。

5.8、松散索引扫描

MySQL并不支持松散索引扫描，无法按照不连续的方式扫描一个索引，MySQL的索引扫描需要定义一个起点和终点，即使需要的数据是这段中很少的几个，MySQL仍需要扫描这段中的每一条。实现松散索引扫描后，就不需要where子句过滤，因为松散索引扫描已经跳过了所有不需要的行。Using index for group by表示的使用松散索引扫描。

mysql> explain select actor_id,max(film_id) from film_actor group by actor_id;

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

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

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

1 row in set (0.00 sec)

5.9、最大值和最小值优化

对于MIN()和MAX()MySQL的优化做的并不好，可以将最小最大值通过排序LIMIT实现，效果不一定好。

mysql> explain select min(actor_id) from actor where first_name = 'PENELOPE';

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

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

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

5.10、在同一个表上查询更新

MySQL不允许对同一张表进行同时查询和更新，即update的set字句使用子查询，可以通过形成表来实现，改写成update join 子查询 set方式

mysql> update t1 outer_tbl set col4=(select col5 from t1 as inner_tbl where outer_tbl.id = inner_tbl.id);

ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM clause

mysql> update t1 inner join (select id, col5 as vl from t1) as inn using(id) set t1.col4=inn.vl;

Query OK, 3 rows affected, 1 warning (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

6、查询优化器的提示（hint）

如果对MySQL优化器选择的执行计划不满意，可以通过在查询语句中添加相应的提示，就可以控制该查询的执行计划。

HIGH_PRIORITY和LOW_PRORITY 控制语句的优先级，对使用表锁的存储引擎有效，他们只是简单地控制了MySQL访问某个数据表队列顺序；

DELAYED 使用该提示的语句会立即返回给客户端，插入时先将数据放入缓冲区，空闲时批量将数据写入；

STRAIGHT_JION 使关联表按指定顺序关联；

SQL_SAMLL_RESULT/SQL_BIG_RESULT 告诉优化器对group by distinct如何使用临时表及排序；

SQL_BUFFER_RESULT 将查询结果放入临时表，然后尽可能快的释放表锁；

SQL_CACHE/SQL_NO_CACHE 控制查询结果是否加入查询缓存；

SQL_CALC_FOUND_ROWS 使MySQL返回的结果集包含更多的信息；

FOR_UPDATE/LOCK IN SHARE MODE 控制实现了行级锁的存储引擎select语句的锁机制；

USE INDEX FORCE INDEX IGNORE INDEX 告诉优化器哪些索引使用或者不使用

控制优化的参数

optimizer_search_depth 优化器穷举执行计划是的限度

optimizer_prune_level 优化器根据扫描的行决定是否跳过某些执行计划

optimizer_switch 包含一系列开启/关闭优化器特性的标识位

针对优化器进行的各种特定设置，在数据库升级后需要逐个验证，因为某些优化策略可能在新版本已经失效。MySQL5.5和5.6版本的改动比较大。

7、优化特定类型的查询

7.1、优化count()查询

Count()的作用，可以统计某个列值（不包括NULL）得数量，也可以统计行数，count(*)并不会扩展至所有列，它会忽略所有列而直接统计，

关于myisam的神话，当count()查询myisam表不带where条件时非常快（因为不用去实际计算表行数，直接利用存储引擎特性直接获得这个值），带where条件后就没有明显的性能优势。

简单的优化，查询补集再计算，通过if(表达式,v1,v2)使用1,0，NULL配合sum() count来处理。

用近似值，通过explain查看表中的记录行数，不需要真正执行。

更复杂的优化，汇总表，外部缓存，快速，精确和实现简单只能三选二

mysql> explain select count(*) from customer where customer_id > 5;

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

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

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

1 row in set (0.00 sec)

mysql> explain select (select count(*) from customer) - count(*) from customer where customer_id <= 5;

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

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

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

2 rows in set (0.00 sec)

mysql> select sum(if(active = 1,1,0)) count_active from customer;

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

| count_active |

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

| 584 |

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

mysql> select count(active = 1 or null) count_active from customer;

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

| count_active |

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

| 584 |

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

7.2、优化关联查询

确保ON，USING列上有索引，关联索引一般创建在被驱动表上；

任何group by order by字句的列尽量来自同一个表，效率会更好；

升级MySQL时注意验证关联语法可能的改变；

7.3、优化子查询

子查询尽可能使用关联查询替代；

7.4、优化group by和distinct

都可以通过适当的索引来优化，当无法使用索引来优化时，使用内存临时表或者文件排序来处理；

对关联查询使用group by时通常采用查找表的标示列效率会比较好，MySQL在不同SQL模式下，group by的字段不用出现在select字句中；

优化group by with rollup 对分组结果再做一次超级聚合，最好尽可能将with rollup放到应用程序中处理；

mysql> explain select actor.first_name,actor.last_name,c.cnt from actor inner join(select actor_id,count(*) as cnt from film_actor group by actor_id) as c using(actor_id);

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

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

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

3 rows in set (0.00 sec)

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

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

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

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

2 rows in set (0.00 sec)

7.5、优化limit分页

Order by limit加上偏移量实现分页，如果有索引通常效果不错，否则MySQL需要做大量的文件排序。特别是在偏移量非常大的时候。尽可能索引覆盖，限制分页的数量，优化大偏移量数据如使用变量。

mysql> explain select film_id,description from film order by title limit 50,5;

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

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

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

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

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

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

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

7.6、优化SQL_CALC_FOUND_ROWS

分页时加SQL_CALC_FOUND_ROWS提示，

7.7、优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，尽量使用UNION ALL，UNION时MySQL会给临时表加distinct，这会导致临时表做唯一性检查，代价非常高。但总是会先放入临时表。

7.8、静态查询分析

pt-query-advisor能够解析查询日志，分析查询模式，并给出建议，可以对MySQL进行健康检查。

7.9、使用用户自定义变量

利用好用户自定义变量，在合适的场景能够写出非常高效的查询。自定义变量是一个用来存储内容的临时容器，可以使用set,select,:=来定义他们，在任何可以使用表达式的地方使用他们。

mysql> set @one := 1;

mysql> set @min_actor := (select min(actor_id) from actor);

mysql> set @last_week := current_date-interval 1 week;

mysql> select @one;

+------+

| @one |

+------+

| 1 |

+------+

mysql> select @min_actor;

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

| @min_actor |

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

| 1 |

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

mysql> select @last_week;

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

| @last_week |

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

| 2017-11-08 |

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

使用自定义变量的查询无法使用查询缓存；

不能在常量或者标识符的地方使用，如表名，limit 子句；

生命周期局限于当前连接；

在使用连接池或者持久化连接中可能会引起问题；

注意版本间的差异5.0之前是大小写敏感的；

不能显示的声明变量类型，可以在初始化时给默认值表明类型，类型会在赋值时改变；

优化器可能会将自定义变量优化掉；

赋值的顺序和赋值的时间点不固定；

使用未定义变量不时会发生“诡异”错误，导致排错困难；

优化排名语句

在给变量赋值时使用这个变量，利用变量实现“行号”功能

mysql> set @rownum := 0;

mysql> select actor_id,@rownum :=@rownum+1 as rownum from actor limit 3;

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

| actor_id | rownum |

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

| 58 | 1 |

| 92 | 2 |

| 182 | 3 |

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

3 rows in set (0.01 sec)

避免重复查询刚刚更新的数据

mysql> update t1 set lastupdate = now() id =1 and @now := now();

select @now; --无需访问数据表，非常快。

统计更新和插入的数量

确定取值的顺序

where条件是在文件排序操作之前取值。

编写偷懒的UNION

在第一个分支没有找到数据时再去找查第二个分支。正常的UNION ALL即使第一个分支已经查到了需要的数据，后边的分支扔要执行。

用户自定义变量的其他用处

查询运行时计算总数和平均值；

数据计算；

8、案例

8.1、使用MySQL构建一个队列表

select for update 尽量少用。

8.2、计算两点间的距离

MySQL只有MyISAM存储引擎支持GIS，并且支持的不好，建议不要使用。

8.3、使用用户自定义函数

9、总结

不做，少做，快速地做。

转载于:https://my.oschina.net/peakfang/blog/2875290