MySQL -- 索引性能分析

mysql索引性能分析

查看SQL执行频率

MySQL 客户端连接成功后,通过show[session|global] status 命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERTUPDATEDELETESELECT的访问频次:

1
SHOW GLOBAL STATUS LIKE 'Com_______'; # 一个 '_' 代表一个字符  

结果如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 0     |
| Com_import    | 0     |
| Com_insert    | 0     |
| Com_repair    | 0     | 
| Com_revoke    | 0     |
| Com_select    | 8     |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.00 sec)

查询慢日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启,执行一下SQL查看慢日志开启状态:

1
SHOW VARIABLES LIKE 'slow_query_log';

需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

1
2
3
4
5
# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为2秒,SQL语句执行时间炒锅2秒就会视为慢查询,记录慢查询日志
long_query_time=2

重启MySQL服务,本机环境为win10的WSL2,直接执行以下命令:

1
sudo service mysql restart

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:

1
2
# 查看是否支持
SELECT @@have_profiling;

默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:

1
2
3
4
5
# 查看开关状态
SELECT @@profiling;

# 设置开关状态
SET profiling = 1;

执行完查询语句后再执行show profiles,结果如下:

1
2
3
4
5
6
7
8
9
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00025800 | select @@profiling     |
|        2 | 0.00125875 | show tables            |
|        3 | 0.00014975 | select * form user     |
|        4 | 0.01059300 | select * from user     |
|        5 | 0.00152175 | select * from user_log |
+----------+------------+------------------------+

explain 执行计划

EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。语法如下:

1
2
3
# 直接在select语句之前加上关键字 explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

结果如下:

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

EXPLAIN 输出列如下:

Column JSON Name Meaning
id select_id SELECT 标识符
select_type None SELECT 类型
table table_name 输出行table
partitions partitions 匹配的分区
type access_type 连接类型
possible_keys possible_keys 可能的索引选择
key key 实际选择的索引
key_len key_length 所选键的长度
ref ref 与索引比较的列
rows rows 估计要检查的行
filtered filtered 按table条件过滤的行百分比
Extra None 扩展信息

😀 Note 详细解释可参考 [官方文档]

  • id 行标识,如果没有子查询或者联合查询,这个值是1,id列值越大越先执行,如果一样大,那么就从上往下依次执行

  • select_type 查询的类型,可以是下表的任何一种类型

    select_type 类型说明
    SIMPLE 简单SELECT(不使用UNION或子查询)
    PROMARY 最外层的SELECT
    UNION UNION中第二个或之后的SELECT语句
    DEPENDENT UNION UNION中第二个或之后的SELECT语句取决于外面的查询
    UNION RESULT UNION的结果
    SUBQUERY 子查询中的第一个SELECT
    DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
    DERIVED 衍生表(FROM自居中的子查询)
    MATERIALIZED 物化子查询
    UNCACHEABLE SUBQUERY 结果集无法缓存的子查询,必须重新评估外部查询的每一行
    UNCACHEABLE UNION UNION中第二个或之后的SELECT,属于无法缓存的子查询

DEPENDENT 意味着使用了关联子查询

  • table:输出行所引用表的名称,也可以是以下值之一:

    • <unionM,N>: 引用id为M和N UNION之后的结果。
    • : 引用id为N的结果派生出的表,派生表可以是一个结果集,例如派生自FROM中子查询的结果。
    • : 引用id为N的子查询结果物化得到的表,即生成一个临时表保存子查询的结果。
  • partitions:v5.7之前该项是explain partitions显示的选项,v5.7之后成为了默认选项,该列显示的为分区表中的分区情况,非分区表该字段为空(null)。

  • type( 重要 ):连接类型

    • system 表中只有一行数据或者是空表,这是const类型的一个特例,且只能用于MyISAMMemory表,如果是InnoDB引擎表,type列在这个情况通常都是all或者index

    • const 最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时,join类型为const。其他数据库也叫做唯一索引扫描

    • eq_ref 多表join时,对于来自前面表的每一行,在当前表中只能找到一行,这可能是除了systemconst之外最好的类型,当主键或唯一非NULL索引的所有字段都被当做join连接时会出现此类型。 eq_ref可用于使用’=‘操作符作比较的索引列,比较的值可以是常量,也可以是使用此表之前读取的表的列表达式。

    • ref 对于来自前面表的每一行,在此表的索引中可以匹配到多行,若连接只用到索引的最左前缀或索引不是主键或唯一索引时,使用 ref 类型(也就是说,此类型能够匹配到多行连接)。 ref 可用于使用 =<=> 操作符作比较的索引列。

    • fulltext 用于全文索引的时候是这个类型,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在,mysql不管代价,优先选择使用全文索引

    • ref_or_null 类似于ref类型,只是增加了null值得比较,实际用的不多

    • index_merge 此类型表示查询使用两个以上索引,最后取交集或者并集,常见 and, or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range

    • unique_subquery 用于WHERE中的 IN 形式子查询,子查询返回不重复值唯一值,可以完全替换子查询,效率更高。 该类型替换了下面形式的 IN 子查询的 ref: value IN (select primary_key FROM single_table WHERE some_expr)

    • index_subquery 该连接类型类似于unique_subquery。适用于非唯一索引,可以返回重复值

    • range 索引范围查询,常见于使用= <> > >= < <= IS NULL <=> BETWEEN IN 或者 LIKE 等运算符的查询中。

    • index 索引全表扫描,把索引从头到尾扫一遍。这里包含两种情况: 一种是查询使用了覆盖索引,那么它只需要扫描索引就可以获得数据,这个效率要比全表扫描要快,因为索引通常比数据表小,而且还能避免二次查询。在 extra 中显示 Using index,反之,如果在索引上进行全表扫描,没有 Using index的提示。

    • all 全表扫描,性能较差

  • possible_keys:查询可能用到的索引,如果此列是 NULL,则没有用到索引。

  • key:表示MySQL实际决定使用的索引,如果MySQL决定使用其中一个 possible_keys 索引来查找行,则该索引将作为键值列出。

  • key_length:查询用到的索引长度(字节), 如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,用多少算多少。例如组合索引为idx_username_create_time,当查询语句为 select * from user where username='qqq'时,只用到了username字段,那么key_len只会计算username字段占用的字节数,假如username字段类型为varchar(50) NOT NULL,字符编码为utf8mb3(utf8编码一个字符占用3个字节,gbk编码一个字符占2字节),记录字符串长度占用2字节,记录字段不为空占用1字节(若没有NOT NULL限制则不占用这1个字节)那么key_len为 50 * 3 + 2 + 1 = 153字节

  • ref:如果使用的是常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列表发生了内部隐式转换,这里可能显示为func

  • rows( 重要 ):rows也是一个重要的字段,这是MySQL估算的需要扫描的行数(不是精确值)。这个值非常直观显示SQL的效率好坏,原则上rows越少越好。

  • filtered:该字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。

  • Extra( 重要 ):EXPLAIN中很多额外的信息会在Extra字段显示,常见的有以下:

    • distinct: 在select部分使用了distinct关键字
    • Using filesort: 当Extra中有Using filesort时,表示MySQL需额外的排序操作,不能通过索引顺序到达排序效果,一般有 Using filesort 都建议优化去掉,因为这样的查询CPU资源消耗较大。
    • Using index: “覆盖索引扫描”,表示查询在索引树种就可查找所需数据,不用扫描表数据文件,往往说明性能不错。
    • Using temporary: 查询有使用临时表,一般出现于排序,分组和多表join的情况,查询效率不高,建议优化。
皖ICP备20014602号
Built with Hugo
Theme Stack designed by Jimmy