045.数据库索引知识梳理

一、什么是索引

我们都知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，在数据库中的默认查找算法是最简单，最直接的顺序查找算法。而顺序查找算法在大数据量的情况下，需要查找的内容越靠后所花费的时间就越久。

为了解决这个问题，数据库设计引入了索引这一概念，索引就是为了提高大数据量下数据的查找效率而生的。

二、索引的数据结构

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引是一种排好序的数据结构。如果把数据库看作是一本书，索引就类似于这本书的目录。通过索引数据库引擎就能快速的定位到对应的内容。

索引有多种数据结构，常见的数据结构有：二叉查找树、红黑树、Hash表、B 树。

1、二叉查找树

​ 指的是一种基于二分查找概念的二叉树，数据量大时相对于顺序查找能较大提升数据查询效率

2、红黑树

​ 又称为自平衡二叉查找树，是二叉查找树的优化版本。它左右树高度相差不会高于1，当高于1时会自动进行平衡，让树不至于太失衡。

3、Hash表

​ 哈希表结构只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，同时对应的在哈希表中保存指向每个数据行的指针

4、B 树

​ 一般二叉树每个节点上只存储一个元素，而 B 树每个节点上可以存储多个数值元素。B树与红黑树相似，会自平衡树。与红黑树不同的是，B树是多叉树，且所有的叶子节点都处于同一层级。不存在层级高度差的问题，所以又称为平衡多路查找树。

B 树可以看作是红黑树的一种扩展，即他允许每个节点有M-1个子节点。

• 根节点至少有两个子节点
• 每个节点有M-1个key，并且以升序排列
• 位于M-1和M key的子节点的值位于M-1 和M key对应的Value之间
• 其它节点至少有M/2个子节点

示意图：

B+树是对B树的一种变形树，它与B树的差异在于：

• 有k个子结点的结点必然有k个关键码
• 非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中
• 树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录

示意图：

5、小结

MySql 中索引的数据结构通常默认用的是 基于 B树 的 B+树。

因为采用二叉查找树和红黑树遇到大数据量时，树的高度会很高，查找起来效率依然不高。而 Hash 表虽然数据指定查找（=）效率比 B+ 树高，但在范围查找（><）方面 Hash 表支持不行。B+ 树虽然指定查找效率比 Hash 表差一点，但范围查找支持很好。所以采用 B+ 树更优。

三、InnoDB 索引的特性

InnoDB 表数据文件本身是按 B+ 树组织的一个索引结构文件。

MyISAM是非聚集索引，索引文件和数据文件是分离存储的。而 InnoDB 的索引是聚集索引，索引文件和数据都在一个B+ 树结构文件中， 聚集索引-叶节点包含了完整的数据记录。

InnoDB 表必须有主键，并且推荐使用整型的自增组件。因为 InnoDB 数据文件是采用 B+ 树组织的，如果没有主键，数据无法组织与维护的。而 索引查找中涉及了大量的比较操作，使用整型便于比较操作，也更节省空间。如不使用自增遇到数据插入 B+ 树前面节点整棵树需要重新计算树结构，分裂节点自动平衡会造成很大的性能开销。

为什么非主键索引结构叶子节点存储的是主键值？为了数据一致性和节省空间。

1. 保持一致性：
当数据库表进行DML操作时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。

2. 节省存储空间：
Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

四、索引的类型

在数据库中，主键和唯一标识都同时具有约束和索引的特性，给某个字段设置了主键约束或唯一约束就同时设置了索引。通过设置主键约束、唯一约束创建索引，称为间接创建索引。

• 索引的类型有：主键索引、唯一索引、普通索引、全文索引。
• 索引可以在表创建时增加，也可以在表创建完后增加，可以用 alter 语法或者 create 语法
• 同一个字段可以设置多种索引

1、主键索引（primary key）

主键索引和唯一索引非常类似。
事实上，主键索引仅是一个具有名称 primary 的 unique 索引。这表示一个表只能包含一个 primary key。

主键具有唯一性，在一个表中只能有一个字段作为主键，且值不能重复。

表创建时设置:

-- 不指定主键名语法：PRIMARY KEY (字段名)

CREATE TABLE a (
	`id` INT ,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 指定主键名语法：CONSTRAINT 主键名 PRIMARY KEY (字段名)

CREATE TABLE a (
	`id` INT ,
	CONSTRAINT `pk_id` PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

表创建后增加:

-- 语法：ALTER TABLE 表名 ADD PRIMARY KEY 字段名 (主键约束名)

ALTER TABLE `a` ADD PRIMARY KEY `id` (`pk_id`)

2、唯一索引（unique key）

唯一索引字段值唯一，唯一索引可以设置多个但值不允许重复，在一个表中可以有多个唯一字段。唯一索引和主键索引的设置方法类似。

表创建时设置：

• 语法：UNIQUE KEY (字段名)

表创建后增加：

• 语法：ALTER TABLE 表名 ADD UNIQUE KEY 字段名 (唯一约束名)

3、普通索引（key / index）

默认的索引，可用 key 或 index 来设置。

表创建时设置：

-- 语法：INDEX 索引名 (字段名) USING 索引数据结构

CREATE TABLE a(
	`id` INT,
	INDEX `index_id` (`id`) USING BTREE
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

表创建后增加：

-- 语法：CREATE INDEX 索引名 ON 表名(字段名)

CREATE INDEX index_name ON a(name);

4、全文索引（Full Text）

当在MySQL中存储了文本，比如某个字段的值为坚决贯彻党的十八大精神，我们想用贯彻和十八大作为关键字时都可以搜索到这条记录。那么只能使用like关键字。而对于like我们知道，当不是用左边前缀搜索的时候，无法命中索引，因此对于这条语句 select * from article where content like '%贯彻%'，MySQL只能进行全表扫描，逐一进行匹配，这样的效率极其低下。

而全文索引的数据结构为 Full Text，通过建立倒排索引，可以极大的提升检索效率。

目前，MySQL仅可以在char、varchar、text属性的列上建立全文索引。

除了数据结构不同，创建全文索引的时机与创建其他类型的索引没什么不同：

表创建时设置：

-- 语法：FULLTEXT INDEX 索引名 (字段名)

CREATE TABLE b(
	`name` VARCHAR(100),
	FULLTEXT INDEX `index_name` (`name`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

表创建后增加：

-- 语法：CREATE FULLTEXT INDEX 索引名 ON 表名(字段名)

CREATE FULLTEXT INDEX full_name ON a(name);

MySQL的全文索引查询有多种模式，常用的两种为自然语言模式和 Boolean 模式：

自然语言模式：

-- 语法：SELECT 显示列 FROM 表名 WHERE MATCH (搜索序列) AGAINST('搜索内容' IN NATURAL LANGUAGE MODE)

SELECT * FROM article WHERE MATCH (title,content) AGAINST('贯彻' IN NATURAL LANGUAGE MODE);

Boolean 模式通过一些操作符,来指定搜索词在结果中的包含情况：

-- 语法：SELECT 显示列 FROM 表名 WHERE MATCH (搜索序列) AGAINST('+贯彻 -精神' IN BOOLEAN MODE)

SELECT * FROM article WHERE MATCH (title,content) AGAINST('搜索表达式' IN BOOLEAN MODE);

注意：在 MySQL 5.6 版本以前，只有 MyISAM 存储引擎支持全文引擎。在5.6版本中，InnoDB加入了对全文索引的支持，但是不支持中文全文索引。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。

5、索引操作

5.1、查看索引：

-- 语法：SHOW INDEX FROM 表名

SHOW INDEX FROM a;

5.2、删除索引

-- 语法：ALTER TABLE 表名 DROP INDEX 索引名

ALTER TABLE a DROP INDEX index_name;

五、联合索引

相比单索引，联合索引的应用场景更广。联合索引又叫复合索引。联合索引中的字段遵循从左到右的使用原则，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是 index (a,b,c)， 可以支持 （a）| （a,b）| （a,b,c） 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

比如有一条语句：

select * from user where adress='beijing' and name='张三';

如果我们是在表上分别创建单个索引 adress name 的话，由于 SQL 查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提升了很高效率。

但是如果在 adress、name 两列上创建联合索引的话将带来更高的效率。如果我们创建了 ( id, adress,name) 的联合索引，那么其实相当于创建了 (id,adress,name)、(id,adress)、(id) 三个索引，这被称为最佳左前缀特性。

因此我们在创建联合索引时应该将最常用作限制条件的列放在最左边，依次递减。

创建联合索引：

-- 语法：CREATE INDEX 索引名 ON 表名(字段序列)

CREATE INDEX index_id_name ON a(id,name);

六、执行分析

explain 命令可以用来分析 SQL 语句执行情况，比较使用索引和未使用的执行效率。

-- 语法：EXPLAIN 执行语句

EXPLAIN SELECT * FROM a;

七、建立索引优化时需要注意的问题

设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

1，索引不要过度使用

​ 如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，

2．什么情况下使用索引

• 表的主键、外键必须有索引

• 数据量超过 300 的表应该有索引

• 经常与其他表进行连接的表，在连接字段上应该建立索引

• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

• 索引应该建在选择性高的字段上

• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

• 查询中排序的字段：排序的字段如果通过索引去访问那将大大提高排序速度

• 查询中统计或分组统计的字段

3、什么情况下应不建或少建索引

• 表记录太少

• 频繁进行数据操作的表，不要建立太多的索引

• 数据重复且分布平均的表字段：如一个表有10万行记录，有一个字段 a 只有 true 和 false 两种值，且每个值的分布概率大约为50%，那么对这种表 a 字段建索引一般不会提高数据库的查询速度。

​ 索引有优点也有缺点，索引中的字段被更新的时候，不仅仅要更新表中的数据，还要更新索引数据，以确保索引信息是准确的，这使得 IO 访问量较大增加，加大了整个存储的负载.
​ 当然,，不是存在更新的字段就适合创建索引，如果通过这个字段的更新并不是很多，可能较长时间才会执行一次更新，查询反而比更新更频繁，那这样的字段还是适合创建索引得。更新所带来的额外性能开销也是可以接受的。

4、索引失效问题

• 索引不会包含有NULL值的列
• SQL 查询只使用一个索引：
  • 只设置了单索引得话，多个字段查询只会使用最左索引，where 子句和 order by 子句不能同时使用索引，可根据需求创建复合索引。
• like 语句如何使用索引也是一个问题
• 不要在列上进行运算
• NOT IN 不会使用索引，NOT IN 可用 NOT EXISTS代替。

八、总结

数据库利用各种各样的快速查找、定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引查询速度能加快成千上万倍。

但索引也不应该滥用，不当使用反而会加大性能开销。应根据业务实际需求酌情使用。

注意：以下内容大多基于 MySql 测试，其他数据库也差不多。

---

2021年11月15日 稿