数据库极客时间-mysql索引
Source
Edit
History

索引优化

索引优化的手段

20条索引优化规则：https://www.51cto.com/article/625199.html

1. 方法概览

使用 show index from table_name 查看一个表中的索引。

使用 explain+查询语句 ` 查看这个查询对于索引的命中情况。

使用 show status like 'Handler_read%'; 查看索引使用情况如下：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 32    |
+-----------------------+-------+
  • Handler_read_key：如果索引正在工作，Handler_read_key 的值将很高
  • Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引引用不理想
2. 使用索引还是全表扫描

如果返回表中 30% 的数据会走索引，返回超过 30% 数据就会使用全表扫描。当然这个结论太绝对，不是绝对的 30%，只是一个大概的范围。因为数据量太多，使用索引可能导致回表次数太多，应该直接走全表扫描。比如

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

mysql> select * from t;
+----+------+---------------------+
| id | a    | t_modified          |
+----+------+---------------------+
|  1 |    1 | 2018-11-13 00:00:00 |
|  2 |    2 | 2018-11-12 00:00:00 |
|  3 |    3 | 2018-11-11 00:00:00 |
|  5 |    5 | 2018-11-09 00:00:00 |
|  6 |    1 | 2022-05-14 15:14:29 |
|  7 |    1 | 2022-05-14 15:14:29 |
|  8 |    1 | 2022-05-14 15:14:29 |
|  9 |    1 | 2022-05-14 15:14:29 |
| 10 |    1 | 2022-05-14 15:14:29 |
+----+------+---------------------+

mysql> explain select * from t where a = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | a             | NULL | NULL    | NULL |    9 |    66.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上，虽然表 t 的字段 a 上有索引，但是不会使用，因为 filtered 的值就是计算返回记录的比例数，总共有 9 行，其中 6 行是 a=1 的，因此 6/9 就是 filtered 的值。Explain 命令中 Extra 中 Using where 意味着需要回表取数据；Using index 意味着不会回表。

3. 索引优化规则

  • 如果 MySQL 估计使用索引要比全表扫描还要慢，则不会使用索引。查询返回数据和全表数据的比例越低，越容易命中索引。一般是 30% 左右

  • 对索引字段做函数操作，可能会破坏索引值的有序性，无法使用索引快速定位，而只能使用全索引扫描。比如：select count(*) from t where month(t_modified)=7; (t_modifity 类型为 datetime)

  • 前导模糊查询不能命中索引。在某个字段设置了普通索引。关注 key 这一列

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> explain select * from user where name like '%a%';;   // %a% 前导模糊查询
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

    mysql> explain select * from user where name like 'n%';   // a%  非前导模糊查询
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
    | id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | user  | NULL       | range | name_index    | name_index | 363     | NULL |    2 |   100.00 | Using index condition |
    +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

  • 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符串常量值用引号引起来，而不要直接使用整数，否则可能走全表扫描。还有一点，在 MySQL 中字符串和数字做比较的话，是将字符串转换成数字的。关注 key 这一列

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> explain select * from user where name = 6;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | user  | NULL       | ALL  | name_index    | NULL | NULL    | NULL |    7 |    14.29 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

    mysql> explain select * from user where name = '6';
    +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | user  | NULL       | ref  | name_index    | name_index | 363     | const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

  • 复合索引的情况下，查询条件不包含索引列最左部分（不满足最左原则），不会命中复合索引

  • 用 or 分割开的条件，如果 or 前的条件列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。因为 or 后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加 IO 访问

  • 负向条件查询不能使用索引，可以优化为 in 查询。但是前提是区分度要高，返回数据比例要在 30% 以内
    负向条件有：!=、 <>、 not in、not exists、not like等

  • 范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between 等。
    如果查询条件中有两个范围列则无法全用到索引
    如果范围查询和等值查询同时存在，优先匹配等值查询列的索引

  • 数据库执行计算不会命中索引，计算逻辑应该尽量放在业务层处理，节省数据库的 CPU的同时最大限度的命中索引

    1
    2
    3
    4
    5
    6
    mysql> explain select * from t where a+1 = 2;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

  • 利用覆盖索引进行查询，避免回表

  • 建立索引的列，不允许为 NULL。单列索引不存 NULL 值，复合索引不存全为 NULL 的值，如果列允许为 NULL，可能会得到“不符合预期”的结果集，所以，请使用 Not NULL 约束以及默认值。对MySQL来说，null是一个特殊的值，比如不能使用 =， <， > 这样的运算符，对 NULL 做算术运算的结果都是 NULL，count 时不会包括 NULL 行等

  • 更新十分频繁的字段上不宜建立索引，因为更新操作会变更 B+树，重建索引。这个过程十分消耗数据库性能

  • 区分度不大的字段上不宜建立索引，类似于性别这种区分度不大的字段，建立索引的意义不大，不能有效过滤数据，性能和全表扫描相当

  • 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。可以提升查询性能，少扫描一次。

  • 多表关联查询时，要保证关联字段上一定要有索引

  • 索引不是越多越好，索引越多，新增和插入时消耗越大

  • 如果两个表的字符集不同，比如一个是 utf8，另一个是 utf8mb4，在做联表查询时用不到关联字段的索引。因为字符集 utf8mb4 是 utf8 的超集，所以这两种类型的字符串在做比较时，MySQL 内部的操作是，先把 utf8 字符串转为 utf8mb4 字符集，再做比较。

自己编写的SQL查询语句，要尽量使用EXPLAIN命令分析一下，做一个对SQL性能有追求的程序员