Chenaa's Notes

尘事如潮人如水

0%

mysql07_mysql优化

发表于 更新于 分类于

优化基础_explain详解

explain命令可以帮助我们了解mysql的执行计划，当运行SQL语句时哪种策略预计会被优化器采用。

explain 输出字段详解

1
2
3
4
5
6
7
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段出现的可能进行解释：

id

执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置

select_type

显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY。

  • simple 简单子查询，不包含子查询和union
  • primary 包含union或者子查询，最外层的部分标记为primary
  • subquery 一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
  • derived 派生表——该临时表是从子查询派生出来的，位于form中的子查询
  • union 位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
  • union result 用来从匿名临时表里检索结果的select被标记为union result
  • dependent union 顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
  • subquery 子查询中第一个SELECT语句
  • dependent subquery 和DEPENDENT UNION相对UNION一样

table

显示这一行的数据是关于哪张表的

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

  • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
  • index: Full Index Scan，index与ALL区别为index类型只遍历索引树
  • range:只检索给定范围的行，使用一个索引来选择行
  • ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  • eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  • const/system:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
  • NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

Key

key列显示MySQL实际决定使用的键（索引）

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好 。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

  • Using where:意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中,表示mysql服务器将在存储引擎检索行后再进行过滤

    1.查询条件中的相关列，不是索引字段， 全表扫描后，通过Using where过滤获取所需的数据。
    2.（type=ref)非唯一性索引扫描，但是由于索引未覆盖所有查询条件(字段d并未包含在聚集索引PRIMARY中)，在存储引擎返回记录后，仍然需要过滤数据
    3. WHERE筛选条件不是索引的前导列，导致不走索引，而走全表扫描。

  • Using index:表示直接访问索引就能够获取到所需要的数据（索引覆盖），不需要通过索引回表。

  • Using index condition：Extra为Using Index Condition 表示会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。(使用索引下推)

  • Using temporary:表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

  • Using filesort:MySQL中无法利用索引完成的排序操作称为“文件排序”

  • Using join buffer:改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

  • Impossible where:这个值强调了where语句会导致没有符合条件的行。

  • Select tables optimized away:这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

参考：
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

mysql查询优化器

查询优化器的局限性

UNION的限制

如果希望UNI0N的各个子句能够根据LIMT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在 UNION的各个子句中分别使用这些子句。

1
2
3
4
5
6
7
8
select first name last name
FROM Sakila. actor
ORDER BY last name)
UNION ALL
(SELECT first name, last name
FROM sakila customer
ORDER BY last name)
LIMIT 20

这条查询将会把 actor中的200条记录和 customer表中的599条记录存放在一个临时表中,然后再从临时表中取出前20条。

优化方式：可以通过在UN0N的两个子查询中分别加上一个LIMIT20来减少临时表中的数据。

索引合并优化(Index merge optimization)

MySQL在 5.0版本中引入新特性：索引合并优化(Index merge optimization)，当查询中单张表可以使用多个索引时，同时扫描多个索引并将扫描结果进行合并。

该新特性可以在一些场景中大幅度提升查询性能，但受限于MySQL糟糕的统计信息，也导致很多场景查询性能极差甚至导致数据库崩溃。

并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性,但是 MySQL做不到。

最大值最小值优化

对于MIN()和MX()査询, MySQL的优化做得并不好。这里有一个例子:

1
mysql> SELECT MIN(actor_id)FROM sakila. actor WHERE first name ='PENELOPE';

因为在 first name字段上并没有索引,因此 My SQL将会进行一次全表扫描。如果MySQL能够进行主键扫描,那么理论上,当MyQL读到第一个满足条件的记录的时候,就是我们需要找的最小值了,因为主键是严格按照 actor id字段的大小顺序排列的。但是 MySQL这时只会做全表扫描。

在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。下面是一个无法运行的SQL,虽然这是一个符合标准的SQL语句。

1
2
3
4
5
6
mysql> UPDATE tbl as outer_tbl
SET cnt = (
SELECT count (*) FROM tbl AS inner_ tbl
WHERE inner_tbl.type =  outer_tbl.type

ERROR 1093(HYooo): You cant specify target table outer tbl for update in FROM clause

可以利用临时表绕过

1
2
3
4
5
6
7
mysql> UPDATE tbl
INNER JOIN(
		SELECT type, count (*) As cnt
		FROM tbl
		GROUP BY type
	)AS der USING(type)
 set tbl.cnt=der.cnt

优化器提示(hint)

如果对优化器选择的执行计划不满意,可以使用优化器提供的几个提示(hint)来控制最终的执行计划。

STRAIGHT JOIN

这个提示可以放置在 SELECT语句的 SELECT关键字之后,也可以放置在任何两个关联表的名字之间。

第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。

第二个用法则是固定其前后两个表的关联顺序。

MySQL可能会花费大量时间在“ statistics”状态,加上这个提示则会大大减少优化器的搜索空间。

SQL_SMALL_RESULT 和 SQL_BIG_RESULT

这两个提示只对 SELECT语句有效。它们告诉优化器对 GROUP BY或者 DISTINCT查询如何使用临时表及排序。 SQL_SMALL_RESULT告诉优化器结果集会很小,可以将结果集放在内存中的索引临时表,以避免排序操作。

如果是 SQL_BIG_RESULT,则告诉优化器结果集可能会非常大,建议使用磁盘临时表做排序操作。

FOR UPDATE 和 LOCK IN SHARE MODE

严格来说这两个不是优化器提示

这两个提示主要控制 SELECT语句的锁机制,但只对实现了行级锁的存储引擎有效。

唯一内置的支持这两个提示的引擎就是 InnodB。另外需要记住的是,这两个提示会让某些优化无法正常使用,例如索引覆盖扫描。

USE INDEX、 IGNORE INDEX 和 FORCE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录。

在MyQL5.1和之后的版本可以通过新增选项FOR ORDER BY 和FOR GROUP BY来指定是否对排序和分组有效。

FORCE INDEX和 USE INDEX基本相同。
当发现优化器选择了错误的索引,可以使用该提示。

慢sql优化

如何发现慢sql？

  1. 带有慢sql统计的数据库连接池 如druid连接池
  2. mysql自带慢sql日志
  3. 中间件监控，如cat

慢sql排查(优化数据访问)

优化慢sql主要从两点出发：

  1. 是否请求了不需要的数据
  2. 是否扫描额外记录

是否请求了不需要的数据？

一个表中存在text字段，但是这个字段一般情况下不需要访问，但是业务代码中每次都请求了这个字段，造成大量冗余数据返回，产生慢sql。

是否扫描额外记录

这个主要是用explain去评估sql扫描的行数，和执行计划。然后通过重写查询的方式来实现优化。(重写查询的方式下文会详细说明)

优雅地使用索引

利用索引对mysql进行优化是最常见的优化方式。

  1. 利用覆盖索引避免回表
  2. 利用索引排序
  3. 利用索引下推 在索引上过滤数据

只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时, MySQL才能够使用索引来对结果做排序。

如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序。

ORDER BY子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求;否则,MySQL都需要执行排序操作,而无法利用索引排序。

索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。

使用前缀索引

MySQL是支持前缀索引的，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

字符串本身前缀区分度不够的问题

  1. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  2. 是使用hash字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
  3. 直接创建完整索引，这样可能比较占用空间；

索引失效

索引失效指优化器放弃走索引的情况。

对索引字段做函数操作

1
select count(*) from tradelog where month(t_modified)=7;

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

1
select * from tradelog where id + 1 = 10000

对于不改变有序性的函数，也不会考虑使用索引。对于这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以，需要你在写SQL语句的时候，手动改写成 where id = 10000 -1才可以。

隐式类型转换导致索引失效

gmt_modified > ‘2020-05-01 00:00:00’ 会将字符串转为时间戳，会走索引

1
2
3
错误的例子：select * from test where tu_mdn=13333333333;

正确的例子：select * from test where tu_mdn='13333333333';

如何避免隐式类型转换?
只有当清楚的知道隐式类型转换的规则，才能从根本上避免产生隐式类型转换。

隐式字符编码转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

导致没有走trade_detail表的tradeid索引

其他索引失效

  • mysql中使用不等于!= 或者<> 会走全表扫描
  • 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
  • like “%abc”，通配符在最左侧

排序优化

无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，My SQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大
则需要使用磁盘,不过 MySQL将这个过程统一称为文件排序( filesort),即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”, My SQL使用内存进行“快速排序”操作。如果内存不够排序,那么 MySQL会先将数据分块,对每个独立的块使用“快速排序”进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排好序的块进行合并( merge),最后返回排序结果。

mysql有两种排序算法：

  1. 全字段排序(单次传输排序)
  2. rowid排序(两次传输排序)

定义一个表:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

全字段排序执行流程如下

  1. 初始化sort_buffer，确定放入name、city、age这三个字段；
  2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
  3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name做快速排序；
  7. 按照排序结果取前1000行返回给客户端。

先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果。

rowid排序

如果sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

rowid排序执行流程：

  1. 初始化sort_buffer，确定放入两个字段，即name和id；
  2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
  3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
  4. 从索引city取下一个记录的主键id；
  5. 重复步骤3、4直到不满足city=’杭州’条件为止，也就是图中的ID_Y；
  6. 对sort_buffer中的数据按照字段name进行排序；
  7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端

读取行指针和需要排序的字段,对其进行排序,然后再根据排序结果读取所需要的数据行。

优化

mysql排序优化主要从这几个方面入手

  1. 是否能用索引排序
  2. 控制sort_buffer大小和max_length_for_sort_data大小

重构查询

切分查询

例如一条删除语句，需要删除10W条数据，为了避免一次锁住很多数据，占满事务日志导致阻塞，可以切分为多个删除语句，每次删除1000条。

分解关联查询

如果查询非常复杂，可以考虑将复杂查询拆分为多个简单查询，在应用层做关联，因为应用层可以通过加机器来线性提升性能，但是数据库的资源比较宝贵，无法直接扩展。

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
  • 将查询分解后,执行单个查询可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。
  • 可以减少冗余记录的查询。在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。
  • 这样做相当于在应用中实现了哈希关联,而不是使用 MySQL的嵌套循环关联。

优化特定类型的查询

优化Limit分页

可以使用延迟关联，来优化limit分页

1
2
3
4
5
6
7

mysql> SELECT film.film_id, film.description
 FROM sakila.film
INNER JOIN (
SELECT film id FROM sakila film
ORDER BY title LIMIT 50, 5 )
AS lim USING (film id)

有时候也可以将Limit查询转换为已知位置的查询,让 MySQL通过范围扫描获得到对应的结果。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNI0N査询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将 WHERE、 LIMIT、ORDER BY等子句“下
推”到UNI0N的各个子查询中,以便优化器可以充分利用这些条件进行优化

除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL,这一点很重要。

如果没有ALL关键字, MySQL会给临时表加上 DISTINCT选项,这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

优化关联查询

  1. 用小表做驱动表
  2. 被关联的表要建好索引

优化COUNT()查询

在不同的MySQL引擎中，count(*)有不同的实现方式。

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；

InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。

不同的count用法

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

至于分析性能差别的时候，你可以记住这么几个原则：

  1. server层要什么就给什么；
  2. InnoDB只给必要的值；
  3. 现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

  1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
  2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。

按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)。

distinct 和 group by的性能比较

如果表t的字段a上没有索引，那么下面这两条语句：

1
2
select a from t group by a order by null;
select distinct a from t;

这种group by的写法，并不是SQL标准的写法。标准的group by语句，是需要在select部分加一个聚合函数。

1
2
3
select a,count(*) from t group by a order by null;

这条语句的逻辑是：按照字段a分组，计算每组的a出现的次数。在这个结果里，由于做的是聚合计算，相同的a只出现一次。

没有了count(*)以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段a做分组，相同的a的值只返回一行。而这就是distinct的语义，所以不需要执行聚合函数时，distinct 和group by这两条语句的语义和执行流程是相同的，因此执行性能也相同。

这两条语句的执行流程是下面这样的。

  1. 创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引；
  2. 遍历表t，依次取数据插入临时表中：
    • 如果发现唯一键冲突，就跳过；
    • 否则插入成功；
  3. 遍历完成后，将临时表作为结果集返回给客户端

mysql配置优化

mysql配置主要交给dba来配置，一般不会出现问题，如果出现问题则需要根据实际问题，来判断和哪个mysql配置有关，并查看相关配置。主要是这么几个方面，脏页的缓存池大小，刷盘的速度，一般频繁刷盘会导致mysql抖动。