MySQL 索引的使用

发表于 阅读次数： 评论数： 本文字数： 4.8k 阅读时长 ≈ 4 分钟

索引的介绍

索引是一种特殊的文件（InnoDB 数据表上的索引是表空间的一个组成部分），包含了对数据表里所有记录的引用指针。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，需要确保该索引是应用在 SQL 查询语句的条件 (一般是 WHERE、JOIN 子句的条件)。

索引的类型（四种）

  • FULLTEXT：即为全文索引，目前只有 MyISAM 引擎支持，其可以在 CREATE TABLE，ALTER TABLE，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引
  • HASH：由于 HASH 的唯一性及类似键值对的形式，很适合作为索引，HASH 索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在 = 和 in 条件下才高效，对于范围查询、排序及组合索引仍然效率不高
  • BTREE：一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口 Root 开始，依次遍历 Node，获取 Leaf，这是 MySQL 里默认和最常用的索引类型
  • RTREE：在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎有 MyISAM、BDb、InnoDb、NDb、Archive

索引的种类（五种）

  • 普通索引：仅加速查询（BTREE 类型）
  • 全文索引：对文本的内容进行分词和搜索
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）+ 表中可以有多个唯一索引
  • 主键索引：加速查询 + 列值唯一（不可以有 NULL） + 表中只能有一个主键索引
  • 组合索引：又叫 联合索引，多列组成一个索引，专门用于组合搜索，其效率大于索引合并（使用多个单列索引组合搜索）

索引的常用操作

  • 创建索引
1
2
3
4
5
6
7
8
9
10
11
--创建普通索引
CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1, col_name_2);

--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1, col_name_2);
  • 通过修改表结构创建索引
1
ALTER TABLE table_name ADD INDEX index_name(col_name);
  • 创建表时直接指定索引
1
2
3
4
5
CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name(col_name)
);
  • 删除索引
1
2
3
4
5
--直接删除索引
DROP INDEX index_name ON table_name;

--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
  • 其它相关命令
1
2
3
4
5
6
7
8
9
10
11
12
13
--查看表结构
desc table_name;

--查看创建表的SQL
show create table table_name;

--查看索引
show index from table_name;

--查看执行时间
set profiling = 1;
SQL ...
show profiles;

索引的优缺点是什么

  • 优点

    • 可以提高数据检索的效率，降低数据库的 IO 成本
    • 通过索引列对数据进行排序，可以降低数据排序的成本，降低 CPU 的消耗

  • 缺点

    • 高并发写入会影响性能
    • 虽然索引大大提高了查询效率，但同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE 操作。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
    • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用磁盘空间的。一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的体积会膨胀得很快

哪些情况需要创建索引

一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <、<=、=、>、>=、BETWEEN、IN 以及某些时候的 LIKE 才会使用索引。例如下述的 SQL 语句，就需要对 city 和 age 列建立索引，由于 mytable_m 表的 userame 也出现在了 JOIN 子句中，因此也有对它建立索引的必要。

1
SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

特别注意

上面提到只有某些时候的 LIKE 才需建立索引，这是因为在以通配符 % 开头作查询时，MySQL 不会使用索引；只有以通配符 % 结尾做查询时，MySQL 才会使用到索引。但有一种情况例外，那就是当触发了覆盖索引（select 的数据列只从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖）的情况下，以通配符 % 开头作查询 MySQL 也会使用索引。例如：如果表里面只有 id 和 username 两个字段且都加了索引，那么 select * like '%username' 查询也是会使用索引的，前提是 select 数据列都加了索引。

哪些情况不要创建索引

  • 表记录太少
  • 增删改非常频繁的字段
  • WHERE 条件里用不到的字段
  • 唯一性太差的字段，尽管频繁作为查询条件，例如：性别字段

哪些字段应该创建索引

  • 查询中与其他表关联的字段，例如外键应该建立索引
  • WHERE 和 JOIN 子句中，较频繁作为查询条件的字段应该创建索引
  • 查询中排序（order by）、分组（group by）、统计的字段应该建立索引

哪些情况索引不会生效

  • 计算、函数导致索引失效
  • != 或者 <> 会导致索引失效
  • or 前后存在非索引的列，会导致索引失效
  • is null 可以使用索引，is not null 可能无法使用索引（不同版本和数据决定）
  • like 查询，以 % 开头的查询不会使用索引，除非 select 数据列都加了索引（触发覆盖索引）
  • 对于组合索引（联合索引），如果不满足最左前缀法则，则索引不会生效
  • 类型转换会导致索引失效，比如列类型是字符串，那一定要在查询条件中将数据使用单引号包裹起来，否则索引不生效
  • 如果数据库优化器估算使用全表扫描要比使用索引快，则不会使用索引

索引使用的注意事项是什么

  • 针对普通查询

    • 避免使用 select *
    • 连接表时注意条件类型需要一致
    • 创建表时尽量使用 char 代替 varchar
    • 使用 count (1) 或 count (列) 代替 count (*)
    • 使用表连接（JOIN）来代替子查询（Sub-Queries）

  • 针对索引使用

    • 使用组合索引代替多个单列索引（经常使用多个条件查询时）
    • 索引散列值（重复多的值）不适合建索引，例如：性别字段
    • 索引不会包含有 NULL 值的列，只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL 值，那么这一列对于此组合索引就是无效的，因此在数据库设计时不要让字段的默认值为 NULL
    • 不要在列上进行运算，例如 select * from users where YEAR(adddate) < 2007，将在每个行记录上进行运算，这将导致索引失效而进行全表扫描，因此可以改成 select * from users where adddate < '2007-01-01'
    • 尽量使用短索引，对串列进行索引，如果可以应该指定一个前缀长度。例如：如果有一个 CHAR (255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引；短索引不仅可以提高查询速度，还可以节省磁盘空间和 I/O 操作
    • MySQL 5.0 之前，SQL 查询只能使用一个索引，因此如果 WHERE 子句中已经使用了索引的话，那么 order by、group by 中的列是不会使用索引的。因此如果数据库默认排序可以符合要求的情况下，不要使用排序操作，同时尽量使用不包含多个列的排序，如果需要最好给这些列创建组合索引

如何查看索引的使用效果

获取执行计划

Explain + 查询 SQL，用于显示 SQL 执行信息参数，根据参考信息可以进行 SQL 优化或者判断索引是否生效

查看索引的使用情况

1
show status like '%Handler_read%';
  • handler_read_key：这个值越高越好，越高表示使用索引查询到的次数越多
  • handler_read_rnd_next：这个值越高，说明查询效率低效

索引的最左前缀法则

使用组合索引（联合索引）时，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效 (这一列后面的字段的索引失效)。

最左前缀法则的使用

使用是案例一

案例内容：组合索引 + where 子句的使用

使用是案例二

案例内容：组合索引 + where 子句的使用

假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。

1
2
# 创建组合索引
mysql> create index idx_phone_name_age on t_user(phone, name, age);
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
31
# 索引不会失效，因为遵循最左前缀法则
mysql> explain select * from t_user where phone = '123456' and name = 'Amy' and age = 20;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 221     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+

# 索引不会失效，最左前缀法则和过滤条件的顺序无关
mysql> explain select * from t_user where name = 'Amy' and age = 20 and phone = '123456';
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 221     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------------------+------+----------+-------+

# 索引部分失效，因为跳跃了 name 这里一列
mysql> explain select * from t_user where phone = '123456' and age = 20;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    25.00 | Using index condition |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

# 索引全部失效，因为跳跃了 phone 这里一列，将不遵循最左前缀法则
mysql> explain select * from t_user where name = 'Amy' and age = 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

使用是案例三

案例内容：组合索引 + where 子句 + order by 子句的使用

假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。

  • 创建组合索引
1
mysql> create index idx_phone_name_age on t_user(phone, name, age);
  • 无过滤条件（如 where 条件，不包括 limit 条件），索引不会生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 索引全部失效，因为没有过滤条件
mysql> explain select * from t_user order by phone, name, age;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+

# 索引全部失效，因为没有过滤条件，不包括 `limit` 条件
mysql> explain select * from t_user order by phone, name, age limit 5;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+

# 索引不会失效，因为有过滤条件
mysql> explain select * from t_user where phone = '123456' order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | NULL  |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
  • 查询字段与过滤字段对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 索引部分生效
mysql> explain select * from t_user where phone = '123456' and age > 20 order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+

# 索引完全生效，因为触发了覆盖索引
mysql> explain select phone, age from t_user where phone = '123456' and age > 20 order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+--------------------------+
  • 排序字段的顺序和索引的顺序不一致对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 排序字段的顺序和索引的顺序一致时，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, name, age;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+

# 排序字段的顺序和索引的顺序不一致时，会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, age, name;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
  • 多个索引字段排序时，排序方向（升序、降序）不一致对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 多个索引字段排序时，排序方向（升序、降序）一致，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone desc, name desc, age desc;
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Backward index scan; Using index |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+----------------------------------+

# 多个索引字段排序时，排序方向（升序、降序）不一致，会触发 `filesort`
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_phone_name_age | idx_phone_name_age | 63      | const |    4 |   100.00 | Using index; Using filesort |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------------+

最左前缀法则的知识点

  • 在创建组合索引的时候，过滤性（唯一性）最好的字段在索引字段顺序中，位置越靠左边越好
  • 组合索引出现范围查询时，应该尽量把这个字段放在索引顺序的最右边
  • 在创建组合索引的时候，应该尽量包含 where 子句中的字段
  • 应该尽量避免造成索引失效的情况

最左前缀法则的关键点

  • 如果排序字段不在索引列上，MySQL 就会触发 filesort，导致查询性能降低；有两种排序算法：单路排序和双路排序
  • 无过滤不索引，即如果没有过滤条件（如 where 条件，不包括 limit 条件），索引不会生效
  • 在 SQL 语句中，如果索引字段的顺序写错了，会触发 filesort
  • 使用 order by 查询时，如果索引字段的位置非最左，会触发 filesort
  • 使用 order by 查询时，如果索引字段的排序方向（升序、降序）不一致，会触发 filesort
  • 熟练使用 explain，必要时使用 optimizer_trace

最左前缀法则的面试题

面试题目

MySQL 使用 Innodb 引擎，请简述 MysQL 索引的最左前缀法则如何优化 Order By 语句。

  • 在 SQL 语句中，一定要有过滤条件（如 where 条件），否则执行 order by 时索引会失效
  • 首先要对 SQL 进行分析，检查必要的查询字段、过滤字段、排序字段是否按顺序创建好了素引
  • 如果查询字段没有创建索引，可能会产生回表操作，导致触发 filesort，降低查询性能
  • 排序字段的顺序和索引的顺序不一致时，会触发 filesort，降低查询性能
  • 多个索引字段排序时，如果排序方向（升序、降序）不一致，会触发 filesort，降低查询性能
  • 使用 explain 观察查询类型和索引利用情况
  • 尽可能减少不必要的 filesort

MySQL 查询只能使用一个索引？

MySQL 5.0 之前，SQL 查询只能使用一个索引，所以要合理使用组合索引，而不是单列索引。与其说是 “数据库查询只能用到一个索引”，倒不如说和全表扫描、只使用一个索引的查询速度比起来，去分析多个索引二叉树更加耗费时间，所以绝大多数情况下数据库都是用一个索引。特别注意：从 MySQL 5.1 开始，引入了索引合并优化技术，对同一个表可以使用多个索引分别进行条件扫描。

1
select count(1) from table1 where column1 = 1 and column2 = 'foo' and column3 = 'bar';

例如上面的语句，当数据库有 N 个索引并且查询中分别都要用上它们的情况下：查询优化器（用于生成执行计划）需要进行 N 次主二叉树查找（这里主二叉树的意思是最外层的索引节点），此时的查找流程大概是：查出第一条 column1 主二叉树等于 1 的值，然后去第二条 column2 主二叉树查出 foo 的值并且当前行的 coumn1 必须等于 1，最后去 column3 主二叉树查找 bar 的值并且 column1 必须等于 1 和 column2 必须等于 foo。如果这样的流程被查询优化器执行一遍，就算不死也半条命了，查询优化器可等不及把以上计划都执行一遍，贪婪算法（最近邻居算法）可不允许这种情况的发生。所以当遇到上面的语句，数据库只要用到第一个筛选列的索引（column1），就会直接去进行表扫描了。所以与其说是数据库只支持一条查询语句只使用一个索引，倒不如说 N 个独立索引同时在一条语句使用的开销比只使用一个索引还要大。最佳推荐是使用 index(column1, column2, column3） 这种组合索引，此组合索引可以把 B+Tree 结构的优势发挥得淋漓尽致。一条主二叉树（column=1），查询到（column=1）节点后基于当前节点进行二级二叉树（column2=foo）的查询，在二级二叉树查询到（column2=foo）后，去三级二叉树（column3=bar）查找，这样查询效率会高跟多。