从头学SQL（五）：MYSQL索引

索引简述

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

索引的创建及删除

普通索引

创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16)
NOT NULL,
INDEX [indexName] (username(length)) );

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16)
NOT NULL,
UNIQUE [indexName] (username(length)) );

使用ALTER 命令添加索引
有四种方式来添加数据表的索引：

1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
   该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
   这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
3. ALTER TABLE tbl_name ADD INDEX index_name (column_list):
   添加普通索引，索引值可出现多次。
4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为
   FULLTEXT ，用于全文索引。

参考链接：https://www.runoob.com/mysql/mysql-index.html

PRIMARY, INDEX,UNIQUE,FULLTEXT使用场景：

举个例子来说，比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段：

会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

• 那么这个会员编号，作为主键，使用 PRIMARY
• 会员姓名 如果要建索引的话，那么就是普通的 INDEX
• 会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）
• 会员备注信息 ， 如果需要建索引的话，可以选择 FULLTEXT，全文搜索。

不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。FULLTEXT索引仅可用于 MyISAM 表
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

参考链接：https://www.cnblogs.com/xuzhengzong/p/7680464.html

索引结构

Hash索引

Hash索引的底层是由Hash表来实现的，非常适合以key-value的形式查询，也就是单个key查询，或者说是等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以比较方便的提供等值查询的场景。但是对于范围查询的话，就需要进行全表扫描了。
参考链接：http://www.360doc.com/content/19/0506/14/10276798_833751025.shtml

B树索引

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。**由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。**而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B+树索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度（减少I/O操作）。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

在数据库中，B+Tree的高度一般都在2–4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

参考链接：https://blog.csdn.net/weixin_42149962/article/details/80516505

问题：MySQL中存储索引用B+树结构而不用Hash结构？

①从内存角度来说：Hash索引需要将数据全部从磁盘加载到内存中，才可以进行索引，如果数据量很大的话，不可能一次装入内存；而B+树允许数据分批加载到内存。
②从业务场景来说：对于等值（唯一）查找来说，Hash更快，但是，数据库中经常会查找多条数据，B+树数据有序，并且叶子节点又有链表相连，所以查询效率会更高。

聚集索引和非聚集索引

聚集索引和非聚集索引使用的都是B+树结构。

1、非聚集索引

非聚集索引的叶子节点为索引节点，但是有一个指针指向数据节点。

MyISAM是非聚集索引。

2、聚集索引

聚集索引叶子节点就是数据节点。

关于聚集索引，innodb会按照如下规则进行处理：

1. 如果一个主键被定义了，那么这个主键就是作为聚集索引
2. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

innodb的普通索引，唯一索引，联合索引都是辅助索引，采用非聚集索引结构。InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
参考链接：https://www.cnblogs.com/wangzhongqiu/archive/2019/04/22/10728569.html

上述过程又叫做回表。举例说明，假如我们有一个table表，主键为ID，name属性已经添加了索引，有如下sql语句：

select * from table where name=‘小明’；

select * 是查询所有的，我们首先找到检索‘小明’获得主键ID，然后用该ID检索所有属性的值。我们如果只查询ID，其实在Name字段的索引上就已经有了，那就不需要回表了。
回表查询是比较耗时的，那么我们应该如何避免回表呢？-覆盖索引

覆盖索引、联合索引，索引下推

参考链接：https://www.jianshu.com/p/bdc9e57ccf8b
参考链接：https://www.jianshu.com/p/d0d3de6832b9

覆盖索引

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中，可以直接使用索引查询而不需要回表。这就是覆盖索引，通过使用覆盖索引，可以减少搜索树的次数，是常用的性能优化手段。

假如我们现在有如下表结构

CREATE TABLE `user_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) unsigned Not NULL,
  PRIMARY KEY (`id`),
  key (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

执行**语句(A) select id from user_table where username = ‘lzs’**时，因为username索引树的叶子结点上保存有username和id的值，所以通过username索引树查找到id后，我们就已经得到所需的数据了，这时候就不需要再去主键索引上继续查找了。

执行语句(B) select password from user_table where username = 'lzs’时，流程如下

1、username索引树上找到username=lzs对应的主键id
2、通过回表在主键索引树上找到满足条件的数据

例如上面的语句B是一个高频查询的语句，我们可以建立(username,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率。当然，添加索引是有维护代价的，所以添加时也要权衡一下。

联合索引

联合索引是为了支持覆盖索引而使用的方法

最左匹配原则：

1. 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
2. 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询
   (>、<、between、like左匹配：where name like ‘%张’)等就不能进一步匹配了，后续退化为线性查找。
3. 因此，列的排列顺序决定了可命中索引的列数。

例子： 如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and
d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)
参考链接：https://blog.csdn.net/qq_35190492/article/details/104346265

mysql的b+树索引遵循“最左前缀”（最左匹配）原则，继续以上面的例子来说明，为了提高语句B的执行速度，我们添加了一个联合索引（username,password）,特别注意这个联合索引的顺序，如果我们颠倒下顺序改成（password,username),这样查询能使用这个索引吗？答案是不能的！

这是最左前缀的第一层含义：

联合索引的多个字段中，只有当查询条件为联合索引的一个字段时，查询才能使用该索引。

现在，假设我们有一下三种查询情景：
1、查出用户名的第一个字是“张”开头的人的密码。即查询条件子句为"where username like ‘张%’"
2、查处用户名中含有“张”字的人的密码。即查询条件子句为"where username like ‘%张%’"
3、查出用户名以“张”字结尾的人的密码。即查询条件子句为"where username like ‘%张’"

以上三种情况下，只有第1种能够使用（username,password）联合索引来加快查询速度。

这就是最左前缀的第二层含义：

索引可以用于查询条件字段为索引字段，根据字段值最左若干个字符进行的模糊查询。

维护索引需要代价，所以有时候我们可以利用“最左前缀”原则减少索引数量：

上面的（username,password）索引，也可用于根据username查询age的情况。当然，使用这个索引去查询age的时候是需要进行回表的，当这个需求（根据username查询age）也是高频请求时，我们可以创建（username,password,age）联合索引，这样，我们需要维护的索引数量不变。

创建索引时，我们也要考虑空间代价，使用较少的空间来创建索引：

假设我们现在不需要通过username查询password了，相反，经常需要通过username查询age或通过age查询username,这时候，删掉（username,password）索引后，我们需要创建新的索引，我们有两种选择
1、（username,age）联合索引+age字段索引 2、（age,username）联合索引+username单字段索引
一般来说，username字段比age字段大的多，所以，我们应选择第一种，索引占用空间较小。

索引下推

图片来源：https://blog.csdn.net/mccand1234/article/details/95799942

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

mysq> select * from tuser where name like ‘张 %’ and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。


图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。 在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

引用

【mysql】主键、普通索引、唯一索引和全文索引的比较
MySQL 索引
回表与覆盖索引，索引下推
mysql索引篇之覆盖索引、联合索引、索引下推
我以为我对数据库索引十分了解，直到我遇到了阿里面试官