Mysql-查询优化
一、查询慢的原因在Mysql查询过程成，出现性能问题可能存在方方面面的原因，主要包括如下几种：网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等。二、如何优化数据访问1、减少访问数据量确认应用程序是否存在检索大量超过需要的数据。mysql> explain select id, phone fromtest_user_copy limit 30000,1;+----+---
·
一、查询慢的原因
在Mysql查询过程成，出现性能问题可能存在方方面面的原因，主要包括如下几种：网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等。
二、如何优化数据访问
1、减少访问数据量
- 确认应用程序是否存在检索大量超过需要的数据。
mysql> explain select id, phone from test_user_copy limit 30000,1;
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | test_user_copy | NULL | ALL | NULL | NULL | NULL | NULL | 32868 | 100.00 | NULL |
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
//优化后写法
mysql> explain select a.id, a.phone from test_user_copy a join (select id from test_user_copy limit 30000,1) b on a.id = b.id;
+----+-------------+----------------+------------+------+---------------+-------------+---------+---------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+-------------+---------+---------------------+-------+----------+-------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 32868 | 100.00 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 3 | jeethink-cloud.a.id | 10 | 100.00 | Using index |
| 2 | DERIVED | test_user_copy | NULL | ALL | NULL | NULL | NULL | NULL | 32868 | 100.00 | NULL |
+----+-------------+----------------+------------+------+---------------+-------------+---------+---------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00009600 | explain seelct a.id, a.phone from test_user_copy a join (select id from test_user_copy limit 30000,1) b on a.id = b.id |
| 2 | 0.00026450 | explain select id, phone from test_user_copy limit 30000,1 |
+----------+------------+------------------------------------------------------------------------------------------------------------------------+
- 确认mysql服务器层是否存在分析大量超过需要的数据行。
业务层是否可以优化IO量。
2、减少不必要的数据筛选
- 查询不需要的记录
我们常常会误以为mysql会只返回需要的数据，实际上mysql却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。优化方式是在查询后面添加limit。
- 返回全部列
mysql> select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
Empty set (0.01 sec)
mysql> select actor.actor_id,actor.first_name from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title='Academy Dinosaur';
Empty set (0.00 sec)- 重复查询相同的数据
如果需要不断的重复执行相同的查询，且每次返回完全相同的数据，因此，基于这样的应用场景，我们可以将这部分数据缓存起来，这样的话能够提高查询效率。
三、执行过程优化
1、查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端。
2、查询优化处理
a、语法解析器的预处理
mysql通过关键字将SQL语句进行解析，并生成一颗解析树，mysql解析器将使用mysql语法规则验证和解析查询，例如验证使用使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等等。
b、查询优化器
当语法树没有问题之后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器的最主要目的就是要选择最有效的执行计划
mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用某种查询计划时候的成本，并选择其中成本最小的一个。
-- 查询最后一次执行的成本
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6798.599000 |
+-----------------+-------------+
1 row in set (0.00 sec)
-- 可以看到上一次查询大概需要6798个数据页才能找到对应的数据，
-- 这是经过一系列的统计信息计算来的，主要根据四个指标：
-- 每个表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况。同时，mysql有时会选择错误的执行计划，这里有如下几种原因：
- 统计信息不准确。InnoDB因为其mvcc的架构，并不能维护一个数据表的行数的精确统计信息。
- 执行计划的成本估算不等于实际执行的成本。有时候某个执行计划虽然需要读取更多的页面，但是他的成本却更小，因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很小，mysql层面并不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次IO是无法得知的
- mysql的最优执行计划可能跟我们想的不一样。mysql的优化是基于成本模型的优化，但是有可能不是最快的优化。
- mysql不考虑其他并发执行的查询。
- mysql不会考虑不受其控制的操作成本。执行存储过程或者用户自定义函数的成本。
c、优化器的优化策略
Mysql对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估。
- 静态优化。直接对解析树进行分析，并完成优化。
- 动态优化。动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关。
d、优化器的优化类型
- 重新定义关联表的顺序。数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能
- 将外连接转化成内连接，内连接的效率要高于外连接
- 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式
- 优化count、min、max等聚合函数。索引的列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较。
- 子查询优化。mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中。
- 等值传播。
-- 如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500;
-- 这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500 and film_actor.film_id > 500;e、关联查询
join的实现方式原理，主要有如下三种方式：
- Simple Nested-Loop Join。简单的嵌套循环，驱动表每一行记录去匹配表中检索匹配。
- Index nested-Loop Join。利用索引的嵌套循环，要求使用匹配表中的索引，驱动表根据关联的索引字段进项检索，当索引上找到匹配的值，再进行回表查询，如果关联的是主键，则无需回表。
- Index Nested-Loop Join。利用Join Buffer的嵌套循环，当join关联的列没有索引，就可能采用此种方式，将驱动表中的所有相关的列(关联的列和select查询的列)都先缓存到Join Buffer中，然后批量与匹配表进行匹配，默认情况下join_buffer_size=256k。在一个有N个Join关联的sql中，优化器会分配N-1个join Buffer。
mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set, 1 warning (0.00 sec)- straight join。指定join表的执行顺序按语句先后执行。
-- 默认按优化器执行
mysql> explain select *
-> from film
-> inner join film_actor using(film_id)
-> inner join actor using(actor_id);
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | film_actor | NULL | ALL | fp_filmactor | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | actor | NULL | ref | fk_actor_id | fk_actor_id | 2 | jeethink-cloud.film_actor.actor_id | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
-- 指定顺序执行
mysql> explain select straight_join *
-> from film
-> inner join film_actor using(film_id)
-> inner join actor using(actor_id);
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | film_actor | NULL | ALL | fp_filmactor | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | actor | NULL | ref | fk_actor_id | fk_actor_id | 2 | jeethink-cloud.film_actor.actor_id | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+------------------------------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
f、排序优化
- 两次排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。
这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高。
两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作。
- 单次排序
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据
-- 当需要排序的列的总大小超过max_length_for_sort_data定义的字节，
-- mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式
mysql> show variables like '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)四、特定类型的优化
1、优化count查询
---------------------count(*)------------------------------
mysql> select count(*) from test_user_copy;
+----------+
| count(*) |
+----------+
| 32767 |
+----------+
1 row in set (0.01 sec)
mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
---------------------count(1)-------------------------------
mysql> select count(1) from test_user_copy;
+----------+
| count(1) |
+----------+
| 32767 |
+----------+
1 row in set (0.01 sec)
mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
-------------------count(id)-------------------------------
-- 看起来count(id)的成本最高。
mysql> select count(id) from test_user_copy;
+-----------+
| count(id) |
+-----------+
| 32767 |
+-----------+
1 row in set (0.01 sec)
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 6798.599000 |
+-----------------+-------------+
1 row in set (0.00 sec)2、优化关联查询
- 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序。
- 确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程。
- 子查询的优化最重要的优化建议是尽可能使用关联查询代替。
- mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化。除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。
3、自定义变量优化查询
a、自定义变量的使用
set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;mysql> update t1 set lastupdated = now() where id = 1 and @now:=now();
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+---------------------+
| id | lastupdated |
+----+---------------------+
| 1 | 2022-04-22 21:14:48 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> select @now;
+---------------------+
| @now |
+---------------------+
| 2022-04-22 21:14:48 |
+---------------------+
1 row in set (0.00 sec)b、自定义变量的限制
- 无法使用查询缓存
- 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句
- 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信
- 不能显式地声明自定义变量地类型
- mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行
- 赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
- 使用未定义变量不会产生任何语法错误
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
2
0- 0
已为社区贡献4条内容
所有评论(0)