（十四）MySQL数据库-深入理解MySQL索引原理和实现-概念

我的系统版本为CentOS7.5，MySQL版本为5.7.26

索引提供了一种快速定位数据的方法，就像如果你要在字典中找一个字，如果没有目录你需要一个一个的查找，需要对数据进行全盘扫描。但是通过拼音的目录可以快速定位。创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

索引类型

一般我们在通过数据管理软件或者命令来新建索引时是这样定义的

1. 主键索引(primary key)：主键索引不允许重复不允许空值，最好设置一个自增ID作为主键索引，如果没有指定则系统会自动选择一个没有null的唯一索引列作为主键索引，如果没有系统会建立一个隐藏列row_id作为主键索引

2. 唯一索引(unique)：添加了唯一约束就有了唯一索引,唯一索引可以有多个null，一个表中可以有多个唯一索引

3. 普通索引(normal)：一般是先建表，后面再创建索引，普通索引使用的最多，对于数据量不多或者增删改操作较多的表不建议使用索引，数据量较大初次建立索引会消耗较长时间

4. 全文索引(fulltext)：主要针对文本段落等，在5.6以前全文索引只能应用MyISAM引擎，只有 char、varchar、text能建立全文索引

聚集索引与非聚集索引

主键索引就属于聚集索引，一个表有且只有一个聚集索引。当在一个表中写入数据时，系统需要知道这条数据放在表文件的什么位置，这时聚集索引就发挥了作用，如果我们在一个有自增ID为主键表的尾部插入一条数据时，如果不指定ID，则新生成表内最大ID加1作为新的ID，数据在写入时根据ID将数据写在表文件的末端。如果写入的数据ID不是最大，则mysql需要移动ID后面的数据，腾出空间供新的数据使用。也就是说聚集索引的顺序及数据在磁盘存储的顺序

除了主键索引以为的索引都是非聚集索引。非聚集索引的顺序与数据存放顺序不一致，所以可以建立多个非聚集索引。例如可以理解为字典中按照拼音形成的目录就是聚集索引，它决定了文字在整个字典中的位置，偏旁目录是非聚集索引，可以根据偏旁查找文字，但是偏旁目录并不决定文字在字典中的位置

也可以和聚集索引叫主索引，非聚集索引叫副主索引

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的。

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的数据和主键索引存储在一起。

聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。

在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

按实现方法分类

哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

全文索引

最长使用的普通索引(normal)是一种左前缀索引，系统会根据数据的左侧数据建立B+tree，所以在LIKE匹配时%在左侧索引会失效。左前缀索引可以只占用较小的空间来建立索引

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

注意：

*对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引

*在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

*在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

BTree索引和B+Tree索引

BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

每个叶子结点的高度一样，等于h；
每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
叶子结点指针都为null；
非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

BTree的结构如下：

在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

BTree的查询、插入、删除过程可以参考：https://blog.csdn.net/endlu/article/details/51720299

B+Tree索引

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非叶子结点不存储数据，只存储键值；
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

B+Tree的结构如下：

B+Tree对比BTree的优点：

1、磁盘读写代价更低

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

2、查询速度更稳定

由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

带顺序索引的B+TREE

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

带顺序B+Tree的结构如下：

索引的使用策略

什么时候要使用索引？

主键自动建立唯一索引；

经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；

作为排序的列要建立索引；

查询中与其他表关联的字段，外键关系建立索引;

高并发条件下倾向组合索引；

用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

经常增删改的列不要建立索引；

有大量重复的列不建立索引；

表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。

在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。

LIKE操作中，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。

在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）

在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。

字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=‘99999’。

在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。

如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。

尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引