记录生活中的点点滴滴

0%

mysql高级2.3:性能分析

这篇文章写一下MySQL的性能分析:MySQL瓶颈、Explain和索引失效

MySQL常见瓶颈

  • CPU:CPU在饱和的时候一般发生在数据表数据装入内存或从磁盘中读取数据的时候
  • IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈:通过top free iostat wmstat来查看系统的性能状态

Explain

是什么(查看执行计划)

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句。从而知道MySQL是如何处理的SQL预计的,分析你得查询或是表结构的性能瓶颈

能干嘛

能看到下面一些信息:

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

怎么玩

  • Explain + SQL语句

  • 执行计划包含的信息:

    • id
    • select_type
    • table
    • paratitions
    • type
    • possible_keys
    • key
    • key_len
    • ref
    • rows
    • filtered
    • Extra

id

select 查询的序列号,包含一组数字,表示查询中执行的select 子句或操作表的顺序

3种情况:

  • id相同:执行顺序由上到下

  • id不同:如果是子查询,id的序列号会递增,id值越大,优先级越高,越先被执行

  • id相同不同:就是同时存在

select_type

有哪些?

SIMPLE PRIMARY SUBQUERY DERIVED UNION UNION RESULT

表示查询的类型,主要用于区别普通查询、联合查询、子查询等的复合查询

SIMPLE

简单的select查询,查询中不含有子查询或者 UNION

PRIMARY

查询中包含任何复杂查询的子部分,最外层查询被标记为

SUBQUERY

在SELECT或WHERE列表中包含了子查询

DERIVED

在FROM列表中包含的子查询被标记为 DERIVED(衍生),MySQL会递归这些子查询并把结果放在临时表中

UNION

若第二个SELECT出现在UNION之后,则被标记为UNION

若UNION包含在FROM子句的子查询中,外层SELECT被标记为:DERIVED

UNION RESULT

从UNION表中获取结果的RESULT

type

type所显示的是查询使用了哪种类型,type包含的类型包括如下的几种:

从最好到最差依次是:

1
system > const > eq_ref > ref > range > index > All

一般来说,得保证查询至少达到range级别,最好能达到ref。

system

表中只有一条记录(等于系统表),这是const类型的特例,平时不会出现,一般可以忽略。

const

表示通过索引一次就找到了,const用于比较 primary key 或者 unique 索引,因为只匹配一行数据,所以很快。如将主键置于 where 列表中,MySQL就能将该查询转换成一个常量。

eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一性索引扫描。

ref

非唯一索引扫描,返回匹配某个单独值的所有行。

本质上也是一种索引访问,它返回所有匹配某个单独的值的行。

然而,它可能会找到多个符合条件的,所以它应该属于查找和扫描的混合体。

range

只检索给定范围的行,使用一个索引来选择行,key列显示了使用了哪个索引,一般就是在你的WHERE语句中出现了between、>、<、in等查询,比全表扫描好,只需开始与索引某一点,而结束于另一点。

index

Full Index Scan,index与All的区别为index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小(也就是说虽然All和index都会读全表,但index是从索引中读取的,而All是从硬盘中读取的)。

All

Full Table Scan,将遍历全表以找到匹配的行。

possible_keys

显示可能应用在这张表上的索引,一个或多个,查询涉及到的字段,若存在索引,则索引被列出,但不一定被查询实际使用。

key

实际使用的索引。

注意:若key为NULL,则没有使用索引。(其中原因包括没有建立索引或索引失效)

若查询中使用了覆盖索引(select 后要查询的字段刚好和建立的索引字段完全相同),则该索引进出现在key列表中。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精度的情况下,长度越短越好。

key_len显示的值为索引字段的最大可能长度,并非实际长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。

rows

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

Extra

包含不适合在其他列中显示但有十分重要的额外信息。

  • Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为文件排序。

  • Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表,常见于排序,order by和group by

  • Using index:表示对应的select操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效率不错

    • 如果同时出现了using where,表明索引被用来执行索引键值的查找
    • 如果同时出现了using where,表明索引被用来读取数据而非执行查找动作
  • Using where:表明使用了where过滤

  • using join buffer:使用了连接缓存

  • impossible where:where子句的值总是false,不能用来获取任何元祖

  • select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段完成优化。

  • distinct:优化distinct操作,在找到第一匹配的元祖后即找到同样值的动作

索引失效

建表SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `mysql_advanced`.`staffs`( 
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20), `age` INT,
`pos` VARCHAR(20),
`add_time` DATETIME,
PRIMARY KEY (`id`)
);
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('1', 'z3', '22', 'manager', '2020-12-14 18:56:30');
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('2', 'July', '23', 'dev', '2020-12-14 18:56:30');
INSERT INTO `staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('3', '2000', '23', 'dev', '2020-12-14 18:56:30');

# 创建索引
create index idx_name_age_pos on staffs(name,age,pos);

案例(索引失效)

全值匹配我最爱

最佳左前缀法则(带头索引不能死,中间索引不能断)

如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。
正确的示例参考上图。

错误实例1:带头索引死

错误实例2:中间索引断

不要在索引上做任何操作(计算、函数、自动/手动类型转换),不然会导致索引失效而转向全表扫描

mysql存储引擎不能继续使用索引中范围条件(bettween、<、>、in等)右边的列

尽量使用覆盖索引(只查询索引的列(索引列和查询列一致)),减少select *

索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描

索引字段上使用 is null / is not null 判断时,会导致索引失效而转向全表扫描

不过这个 is null 也没啥毛病呀???

索引字段使用like以通配符开头(%字符串)时,会导致索引失效而转向全表扫描

由结果可知,like以通配符结束相当于范围查找,索引不会失效。与范围条件(bettween、<、>、in等)不同的是:不会导致右边的索引失效。

问题:解决like %字符串%时,索引失效问题的方法?

使用覆盖索引可以解决。

索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描

索引字段使用 or 时,会导致索引失效而转向全表扫描

优化总结口诀:

全值匹配我最爱,最左前缀要遵守。

带头大哥不能死,中间兄弟不能断。

索引列上少计算,范围之后全失效。

LIKE百分写最右,覆盖索引不写星。

不等空值还有or,索引失效要少用。

一般性建议

  • 对于单键索引,尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  • 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来选择合适索引的目的