专栏名称: SegmentFault思否

SegmentFault （www.sf.gg）开发者社区，是中国年轻开发者喜爱的极客社区，我们为开发者提供最纯粹的技术交流和分享平台。

面试官出的 MySQL 索引的问题，这篇文章全给你解决！

SegmentFault思否 · 公众号 · 程序员 · 2019-10-23 11:45

正文






    
SegmentFault 社区专栏：好好学java
作者：欧阳思海
前言

这篇文章会讲解索引的基础知识，但主要是关于 MySQL 数据库的 B+ 树索引的相关原理，里面的一些知识都参考了 MySQL 技术内幕这本书，也算对于这些知识的总结。

1、索引的管理

索引有很多中类型：普通索引、唯一索引、主键索引、组合索引、全文索引，下面我们看看如何创建和删除下面这些类型的索引。

1.1 索引的创建方式

索引的创建是可以在很多种情况下进行的。

直接创建索引


CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))

[UNIQUE|FULLLTEXT] ：表示可选择的索引类型，唯一索引还是全文索引，不加话就是普通索引。
table_name ：表的名称，表示为哪个表添加索引。
column_name(length) ：column_name 是表的列名，length 表示为这一列的前 length 行记录添加索引。

修改表结构的方式添加索引


ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))

创建表的时候同时创建索引


CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
)

1.2 主键索引和组合索引创建的方式

前面讲的都是普通索引、唯一索引和全文索引创建的方式，但是，主键索引和组合索引创建的方式却是有点不一样的，所以单独拿出来讲一下。

组合索引创建方式

创建表的时候同时创建索引


CREATE TABLE `table` (
    `id` int(11) NOT NULL




    
 AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name(id,title)
)

修改表结构的方式添加索引


ALTER TABLE table_name ADD INDEX name_city_age (name,city,age); 

主键索引创建方式

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。


CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`)
)

1.3 删除索引

删除索引可利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句， DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理，语法如下。

(1) DROP INDEX index_name ON talbe_name
(2) ALTER TABLE table_name DROP INDEX index_name
(3) ALTER TABLE table_name DROP PRIMARY KEY

第 3 条语句只在删除 PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，因此不需要指定索引名。

1.4 索引实例

上面讲了一下基本的知识，接下来，还是通过一个具体的例子来体会一下。

step1：创建表


 create table table_index(
    id int(11) not null auto_increment,
    title char(255) not null,
    primary key(id)
);

step2：添加索引

首先，我们使用直接添加索引的方式添加一个普通索引。


CREATE




    
 INDEX idx_a ON table_index(title);

接着，我们用修改表结构的时候添加索引。


ALTER TABLE table_index ADD UNIQUE INDEX idx_b (title(100));

最后，我们再添加一个组合索引。

 ALTER TABLE table_index ADD INDEX idx_id_title (id,title);

这样，我们就把前面索引的方式都用上一遍了，我相信你也熟悉这些操作了。

step3：使用` SHOW INDEX `命令查看索引信息

如果想要查看表中的索引信息，可以使用命令 SHOW INDEX ,下面的例子，我们查看表 table_index 的索引信息。


 SHOW INDEX FROM table_index\G;

得到上面的信息，上面的信息什么意思呢？我们逐一介绍！

step4：删除索引

直接删除索引方式


DROP INDEX idx_a ON table_index;

修改表结构时删除索引

ALTER TABLE table_index DROP INDEX idx_b;

1.5 Cardinality 关键字解析

在上面介绍了那么多个关键字的意思，但是 Cardinality 这个关键字非常的关键，优化器会根据这个值来判断是否使用这个索引。在 B+ 树索引中，只有高选择性的字段才是有意义的，高选择性就是这个字段的取值范围很广，比如姓名字段，会有很多的名字，可选择性就高了。

一般来说，判断是否需要使用索引，就可以通过 Cardinality 关键字来判断，如果非常接近 1，说明有必要使用，如果非常小，那么就要考虑是否使用索引了。

需要注意的一个问题时，这个关键字不是及时更新的，需要更新的话，需要使用 ANALYZE TABLE ，例如。


analyze table table_index;

因为目前没有数据，所以，你会发现，这个值一直都是 0，没有变化。

InoDB 存储引擎 Cardinality 的策略

在 InnoDB 存储引擎中，这个关键字的更新发生在两个操作中：insert 和 update。但是，并不是每次都会更新，这样会增加负荷，所以，对于这个关键字的更新有它的策略：

表中 1/16 的数据发生变化
InnoDB 存储引擎的计数器 stat_modified_conter >2000000000

默认 InnoDB 存储引擎会对 8 个叶子节点进行采样，采样过程如下：

B+ 树索引中叶子节点数量，记做 A
随机取得 B+ 树索引中的 8 个叶子节点。统计每个页不同的记录个数，分别为 p1-p8
根据采样信息得到 Cardinality 的预估值： (p1+p2+p3+…+p8)*A/8

因为随机采样，所以，每次的 Cardinality 值都是不一样的，只有一种情况会一样的，就是表中的叶子节点小于或者等于 8，这时候，怎么随机采样都是这 8 个，所以也就一样的。

1.6 Fast Index Creation

在 MySQL 5.5 之前，对于索引的添加或者删除，每次都需要创建一张临时表，然后导入数据到临时表，接着删除原表，如果一张大表进行这样的操作，会非常的耗时，这是一个很大的缺陷。

InnoDB 存储引擎从 1.0.x 版本开始加入了一种 Fast Index Creation （快速索引创建）的索引创建方式。

这种方式的策略为：每次为创建索引的表加上一个 S 锁（共享锁），在创建的时候，不需要重新建表，删除辅助索引只需要更新内部视图，并将辅助索引空间标记为可用，所以，这种效率就大大提高了。

1.7 在线数据定义

MySQL5.6 开始支持的在线数据定义操作就是：允许辅助索引创建的同时，还允许其他 insert、update、delete 这类 DM 操作，这就极大提高了数据库的可用性。

所以，我们可以使用新的语法进行创建索引：

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
[ALGORITHM = {DEFAULT|INPLACE|COPY}]
[LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]

ALGORITHM 指定创建或者删除索引的算法

COPY：创建临时表的方式
INPLACE：不需要创建临时表
DEFAULT：根据参数 old_alter_table 参数判断，如果是 OFF ,采用 INPLACE 的方式

LOCK 表示对表添加锁的情况

NONE：不加任何锁
SHARE：加一个 S 锁，并发读可以进行，写操作需要等待
EXCLUSIVE：加一个 X 锁，读写都不能并发进行
DEFAULT：先判断是否可以使用 NONE ，如不能，判断是否可以使用 SHARE ，如不能，再判断是否可以使用 EXCLUSIVE 模式。

2、B+ 树索引的使用

2.1 联合索引

联合索引是指对表上的多个列进行索引，这一部分我们将通过几个例子来讲解联合索引的相关知识点。

首先，我们先创建一张表以及为这张表创建联合索引。


create




    
 table t_index(
a char(2) not null default '',
b char(2) not null default '',
c char(2) not null default '',
d char(2) not null default ''
)engine myisam charset utf8;

创建联合索引

alter table t_index add index abcd(a,b,c,d);

插入几条测试数据

insert into t_index values('a','b','c','d'),
('a2','b2','c2','d2'),
('a3','b3','c3','d3'),
('a4','b4','c4','d4'),
('a5','b5','c5','d5'),
('a6','b6','c6','d6');

到这一步，我们已经基本准备好了需要的数据，我们可以进行更深一步的联合索引的探讨。

我们什么时候需要创建联合索引呢

索引建立的主要目的就是为了提高查询的效率，那么联合索引的目的也是类似的，联合索引的目的就是为了提高存在多个查询条件的情况下的效率，就如上面建立的表一样，有多个字段，当我们需要利用多个字段进行查询的时候，我们就需要利用到联合索引了。

什么时候联合索引才会发挥作用呢

有时候，我们会用联合索引，但是，我们并不清楚其原理，不知道什么时候联合索引会起到作用，什么时候又是会失效的？

带着这个问题，我们了解一下 联合索引的最左匹配原则 。

最左匹配原则：这个原则的意思就是创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引。

下面，我们用几个例子来看看这个原则。

EXPLAIN SELECT * FROM t_index WHERE a = 'a' \G;

我们看看这条语句的结果，首先，我们看到使用了索引，因为查询条件中带有最左边的列 a，那么利用了几个索引呢？这个我们需要看 key_len 这个字段，我们知道 utf8 编码的一个字符 3 个字节，而我们使用的数据类型是 char(2)，占两个字节，索引就是 2*3 等于 6 个字节，所以只有一个索引起到了作用。


EXPLAIN SELECT * FROM t_index WHERE b = 'b2' \G;

这个语句我们可以看出，这个没有使用索引，因为 possible_keys 为空，而且，从查询的行数 rows 可以看出为 6（我们测试数据总共 6 条），说明进行了全盘扫描的，说明这种情况是不符合最左匹配原则，所以不会使用索引查询。

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY d \G;

这种情况又有点不一样了，我们使用了一个排序，可以看出使用了索引，通过 key_len 为 12 可以得到使用了 2 个索引 a、b ，另外在 Extra 选项中可以看到使用了 Using filesort ，也就是文件排序，这里使用文件排序的原因是这样的：上面的查询使用了 a、b 索引，但是当我们用 d 字段来排序时，（a，d）或者（b，d）这两个索引是没有排序的，联合索引的使用有一个好处，就是索引的下一个字段是会自动排序的，在这里的这种情况来说，c 字段就是排序的，但是 d 是不会，如果我们用 c 来排序就会得到不一样的结果。

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY c \G;

是不是可以看到，当我们用 c 进行排序的时候，因为使用了 a、b 索引，所以 c 就自动排序了，所以也就不用 filesort 了。

讲到这里，我相信通过上面的几个例子，对于联合索引的相关知识已经非常的透彻清晰了，最后，我们再来聊几个常见的问题。

Q1：为什么不对表中的每一个列创建一个索引呢

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

Q2：为什么需要使用联合索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的 sql: select col1,col2,col3 from test where col1=1 and col2=2。那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。减少 io 操作，特别的随机 io 其实是 dba 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出 10% 的数据，如果只有单值索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出 1000w10% 10% *10%=1w，效率提升可想而知！

覆盖索引

覆盖索引是一种从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，所以大小远小于聚集索引，因此可以大大减少 IO 操作。覆盖索引的另外一个好处就是对于统计问题有优化，我们看下面的一个例子。


explain select count(*) from t_index \G;

如果是 myisam 引擎，Extra 列会输出 Select tables optimized away 语句，myisam 引擎已经保存了记录的总数，直接返回结果，就不需要覆盖索引优化了。

如果是 InnoDB 引擎，Extra 列会输出 Using index 语句，说明 InnoDB 引擎优化器使用了覆盖索引操作。

2.2 索引提示

MySQL 数据库支持索引提示功能，索引提示功能就是我们可以显示的告诉优化器使用哪个索引，一般有下面两种情况可能使用到索引提示功能（INDEX HINT）：

MySQL 数据库的优化器错误的选择了某个索引，导致 SQL 运行很慢
某 SQL 语句可以选择的索引非常的多，这时优化器选择执行计划时间的开销可能会大于 SQL 语句本身。

这里我们接着上面的例子来讲解，首先，我们先为上面的 t_index 表添加几个索引；

alter table t_index add index a (a);
alter table t_index add index b (b);
alter table t_index add index c (c);

接着，我们执行下面的语句；

EXPLAIN SELECT * FROM t_index WHERE a = 'a'