一、查询慢的原因

在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优化器在某些场景下可能会将这些变量优化掉,这可能导致代码不按预想地方式运行
  • 赋值符号:=的优先级非常低,所以在使用赋值表达式的时候应该明确的使用括号
  • 使用未定义变量不会产生任何语法错误

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐