【MySQL】MySQL中SQL语句的索引分析

硬核项目经理

发布于 2024-04-12 14:14:30

760

发布于 2024-04-12 14:14:30

MySQL中SQL语句的索引分析

了解过索引的概念以及 B+树的概念之后，我们就来看看怎么分析一条查询语句的索引使用情况。相信不少同学应该都使用过 EXPLAIN 来分析 SQL 语句，但是具体到 EXPLAIN 中每个字段的作用，可能有不少同学还是会有点晕的。因此，我们也是以总结为主，来简单了解一下 EXPLAIN 的具体使用。

数据准备

首先当然是准备一个表，我们就假设是一个用户表。

CREATE TABLE `test_user3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `salt` char(4) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

然后随便插入一些数据，这些字段就没有写备注了，相信大家也能看明白是什么意思。插入数据的话我就直接使用 PHP 简单的插入一些随机数据。


$pdo = new PDO("mysql:host=localhost;dbname=test",'root', '123456');

for ($i = 0; $i < 2000000; $i++) {
    $d = [
        "null",
        "'".bin2hex(random_bytes(10))."'",
        "'".bin2hex(random_bytes(20))."'",
        "'".bin2hex(random_bytes(2))."'",
        "'".date("Y-m-d H:i:s", random_int(time() - 86400 * 365, time()))."'",
        "'".random_int(time() - 86400 * 365, time())."'",
        "'".random_int(-1, 4)."'",
        "'".random_int(0, 2)."'",
    ];
    $values[] ="(".implode(',', $d).")";
    if ($i % 2000 == 0) {
        $pdo->exec("insert into test_user3 values".implode(',', $values));
        $values = [];
    }
}

现在表和数据都已经准备好了，我们来运行一条查询语句试试。

mysql> select * from test_user where username = 'c375afd4d8fed8911935';
+--------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
| id     | username             | password                                 | salt | created_at          | updated_at | status | gender |
+--------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
| 199997 | c375afd4d8fed8911935 | e9fa32f5e9efbf2d75d7acc256c5cc626c8b3fed | aef9 | 2021-05-14 07:54:48 | 1632447086 |     -1 |      0 |
+--------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
1 row in set (1.02 sec)

200万条数据，查一个用户名信息现在大约是花费 1 秒多。

EXPLAIN语句

接下来，我们使用 EXPLAIN 来查看这条语句。

mysql> explain select * from test_user where username = 'f74bc5feac17dc977973';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2155732 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

在结果中，你可以看到一系列的字段，我们一个一个来看下。

id 查询中每出现一个 SELECT 都会有一条分析记录，也就会分配一个递增的 id ，但连接查询所有的都只会是 1 （连接查询中，最先出现的是驱动表，后面的是被驱动表）
select_type 查询类型，普通情况下会是 SIMPLE ，同样也是根据连接或子查询的不同会显示不同的结果；如果是连接或联合查询，驱动表会变成 PRIMARY ，其它被驱动表会变成 UNION、SUBQUERY 等等
table 表名
partitions 分区信息
type 对表的访问方法，后面会详细说这个属性的内容
possible_keys 可能用到的索引
key 实际用到的索引
ken_len 索引列的存储长度
ref 根据 type 中的一些固定类型，显示与索引列进行等值匹配的内容
rows 表的查询估计行数，预计扫描的索引记录有多少
filtered 条件过滤，这是在计算驱动表扇出时采用的策略
Extra 额外信息：using index 表示使用到了覆盖索引；using where 表示搜索条件需要到上一层 server 层判断；using filesort 表示需要使用文件排序；using temporary 表示需要借助临时表完成一些功能。后两者对性能的影响非常大，需要重点关注。其它还有很多信息，不过这几个是比较常见的。

目前，我们的表中没有建立任何索引，只有一个主键索引，因此，上面的查询中，大部分的分析结果都是 NULL ，同时 rows 显示的行数也是全部数据的数量，也就是说，在没走索引的情况下，现在我们的查询是一个全表扫描。

那么我们就来建立一个索引。

ALTER TABLE test_user ADD INDEX `idx_username` (`username`);

索引建立成功后，我们就来进行查询，并且对查询进行分析。

mysql> select * from test_user where username = 'f74bc5feac17dc977973';
+---------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
| id      | username             | password                                 | salt | created_at          | updated_at | status | gender |
+---------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
| 2199996 | f74bc5feac17dc977973 | 47fdc672a454e008ae9affebc48424ae0258439d | 5e6f | 2021-10-31 08:44:18 | 1640777682 |      0 |      0 |
+---------+----------------------+------------------------------------------+------+---------------------+------------+--------+--------+
1 row in set (0.01 sec)

mysql> explain select * from test_user where username = 'f74bc5feac17dc977973';
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | idx_username  | idx_username | 803     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

很明显，查询速度产生了质的飞跃，同时 EXPLAIN 的结果也发生了变化。type 现在变成了 ref ，possible_keys 和 key 都显示为了新建立的 idx_username 索引，rows 显示为 1 。整体的意思其实就是现在是使用了 idx_username 索引，索引类型是 ref（二级索引等值匹配），达到常量级 ref=const ，rows 只需要扫描 1 行。

type类型

EXPLAIN 的几个字段都非常重要，但比较重点的其实就是在于 type 这个字段。这个字段类型可以帮助我们了解当前查询的索引使用情况，是非常重要的一个字段。

system

当表中只有一要记录并且使用的是 MyISAM、MEMORY 的统计数据是精确的，那么查询计划结果就是 system 。

const

根据主键或唯一索引进行等值查询时，MySQL 发现能对查询条件优化成常数时，访问方法就是 const 。常数级别也就是速度非常非常快，常见于主键等值查询。

mysql> explain select * from test_user where id=29929;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

在使用主键或者唯一索引时会看到，表示只返回一行数据。连接查询时，如果被驱动表是通过主键或者不允许存储 NULL 值的唯一二级索引等值匹配的方式进行访问的，那么被驱动表就是 eq_ref 。

ref

当通过普通的二级索引列与常量进行等值匹配的方式来查询某个表时，也叫做索引查找，它是索引和扫描的混合体，。这个我们前面已经看到过了。

ref_or_null

当对普通二级索引列进行等值匹配且该索引列的值也可以是 NULL 值时，需要在索引查找的基础上再进行一次 NULL 值查找。

index_merge

产生索引合并情况下使用的索引状态。

unique_subquery

与 eq_ref 类似，但是是包含 IN 子查询的情况下会出现。

range

范围查找，也就是出现 IN 、大小于之类的情况下会出现，比较常见。

mysql> explain select * from test_user where id > 10000 order by created_at desc limit 10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | test_user | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1077866 |   100.00 | Using where; Using filesort |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

index

这个和全表扫描一样，只是在扫描表时会按索引次序来进行。它的优点是避免了排序，但如果是按随机顺序访问，开销就会很大。注意要和 Extra 中的 using index 区分开。

ALL

全表扫描。

很明显，在上面的这些类型中，const 和 ref 都是非常理想的查询状态，range 则是要看情况，毕竟它不是索引命中，而是范围查找，但是在日常的业务开发中，列表查询很难不使用范围查询。ALL 这个全表扫描肯定是最惨的了。不过某些情况下，MySQL 的优化器在计算到走索引还不如走全表时，也会直接使用全表扫描，以后我们踫到这种问题了再单独拿出来说吧。