MySQL进阶 (2)：Mysql 索引分类及使用策略

导读

提到Mysql​的优化，索引优化是必不可少的。添加合适的索引能够让应用的并发能力和抗压能力得到明显的提升。应用性能的瓶颈主要是在"(select)"查询语句。

什么是索引？

在关系数据库中，索引是一种单独的，物理的对数据库表中一列或多列的值进行排序的一种存储结构。用大白话来讲，就是为了加速对表中的数据行的检索，而创建的一种分散的存储结构。

索引的作用？

索引就相当于图书的目录，让你查找书中的某篇文章，不用一页一页的去翻找，而是先从目录大纲中寻找关键字标题，快速定位其文章所在的页码

比如，可以把数据库中的 表(table) 当作是一本书，table中一行一行的数据就是书里每篇文章的内容，即row，文章标题可看作是 table 中的 column字段 ，目录就是对 column 列建立的索引。

☛ 当表中有大量记录时，对表查询

第一种：没有目录，即全表扫描，将所有记录一一取出和查询条件对比，然后返回满足条件的记录，这种方式会消耗大量数据库系统时间，并造成大量磁盘I/O，因为数据是存放在磁盘中的，存取数据都要访问磁盘。

第二种：有目录，即有索引，就是在表中建立索引，也就是建立书的目录，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID 快速找到表中对应的记录。

索引分类：数据结构

MySQL索引按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。

	InnoDB	MyISAM	Memory
B+tree索引	√	√	√
Hash索引	×	×	√
Full-text索引	√（MySQL5.6+）	√	×

注意：InnoDB实际也支持Hash索引，但只能由存储引擎引擎自动优化创建，不能人为创建

1. FULLTEXT

即为全文索引，MyISAM引擎支持，InnoDB从MySQL5.6版本开始支持。可以在create  table ，alter table ，create index 使用，不过目前只有 char、varchar ，text 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决 where name like “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。是MySQL里默认和最常用的索引类型。

索引分类：物理存储

MySQL索引按叶子节点存储的是否为完整表数据分为：聚簇索引、二级索引（辅助索引）。全表数据存储在聚簇索引中，聚簇索引以外的其他索引叫做二级索引，也叫辅助索引。

☛ 聚簇索引

也称为主键索引，其每个叶子节点存储了一行完整的表数据，叶子节点间按id列递增通过指针连接，提高区间访问的性能。

!! 注意：InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非null值的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增row_id列作为主键id。

MyISAM 引擎实现 

MyISAM不存在聚簇索引。其主索引和辅助索引结构一致，只是主索引要求key是唯一的，而辅助索引的key可重复。因此MyISAM表可以没有主键。叶子节点不存储表数据，而是表数据的地址。数据和索引是分开存储的

☛ 二级索引

MyISAM其主索引和辅助索引结构一致，叶子节点不存储表数据，而是表数据的地址。

也称辅助索引（ps：InnoDB才有辅助索引的概念，MyISAM没有），叶子节点并不存储表数据，而是存储了聚簇索引所在列的值，即主键的值。如下

回表查询

辅助索引搜索需要检索两遍，由于辅助索引的叶子节点不存储完整的表数据，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录：

!! 注意：回表会增大查询耗时。但回表也不是必须的，当select的所有字段在单个二级索引中都能够找到，就无需回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。

索引分类：索引种类

1. 主键索引

加速查询 + 列值唯一（不可以有null）+ 表中只有一个

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，给字段设置主键时，会自动创建主键索引，通常在创建表时一起创建。如下

create table a (  
	id int primary key auto_increment,  -- id为主键索引
	name varchar(20)   
);  

2. 唯一索引

加速查询 + 列值唯一（可以有null）

建立在unique字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。如下：

-- 1.建表时指定
create table d(
	id int primary key auto_increment , 
	name varchar(32) unique  -- name为唯一索引
) 

-- 2.对已存在表，创建
create unique index [索引名] ON table_name(col_name);

3. 普通索引

仅加速查询，主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找

建立在普通字段上的索引被称为普通索引。

-- 方式1.直接创建
create index 索引名 on table_name(column1,column2);

-- 方式2.修改表创建
alter table table_name add index [索引名](column1,column2);

4. 全文索引

搜索数据表中的字段是否包含关键字，就像搜索引擎中的模糊查询。

☆ Mysql全文索引版本限制

1. MySQL5.6版本以下，只有MYISAM引擎表支持全文索引；mySQL5.6以上，Innodb引擎表也支持全文索引。
2. 因Mysql使用空格作为分词的分隔符，故只支持英文检索。从MySQL5.7版本开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

☆ 创建/使用全文索引

1、假设 article文章表结构如下：

2、创建全文索引

-- 使用 ngram 解释器（支持中英文）
create fulltext index full_body on article(body) with parser ngram;

-- 仅支持英文检索
create fulltext index full_body on article(body);

3、使用

match (字段名1,字段名2...) against('关键词1 关键词2  ... ');

5. 组合索引

多列值组成一个索引，专门用于组合搜索，其效率大于索引合并，称为联合索引或复合索引、组合索引。

create index [索引名] ON table_name(col_name_1,col_name_2);

6. 前缀索引

对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。

前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率，语法如下：

alter table 表名 add index [索引名](字段(长度));

-- 例如：
alter table article add index id_Prefix(title(4));

!! 注意：如果索引的列字符很长，索引则会很大且变慢，故可以创建列的部分长度的索引，节约索引空间 从而提高索引效率

 

建立索引的几大原则

• 在where条件中经常使用
• 该字段的内容不是唯一的几个值（例如性别字段，唯一性太差，不推荐）
• 字段内容不是频繁变化。（每次更新还需额外维护索引文件）

① 最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

② =和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

③ 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段都要求是0.1以上，即平均1条扫描10条记录。

④ 索引列不能参与计算

保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’不能使用到索引，因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

⑤ 尽量的扩展索引

不要新建索引。表中已有索引a，现在要加(a,b)，只需修改原来的索引即可。

☁ 思考：为什么区分度不高的字段不适合做索引？

惯用的百分比界线是"30%"。匹配的数据量超过一定限制时，查询器优化器会放弃使用索引

答：例如性别只有男女，值出现的几率几乎相等，不如不要索引，MySQL的查询优化器，发现某个值出现在表的数据行中的百分比很高时，它一般会忽略索引，进行全表扫描。

在这些情况下，还不如不要索引，因为MySQL他还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

扩展：MySQL索引数据结构

MySQL 的索引底层数据结构 默认使用的是 B+Tree。笔者有对二叉树和B树系列数据结构有详细讲解，感兴趣的伙伴，请翻阅笔者MySQL专栏中，MySQL索引结构系列。

除此之外，还有很多高效的数据结构，例如：B-Tree、数组、哈希表、二叉搜索树、红黑树等。

☁ 思考：为什么 MySQL 不选择这些数据结构作为索引呢？

๑ 缘起

MySQL 作为存储数据的组件，主要操作数据的增删改查。其中查询比例占据一半以上。因此一个数据库选择何种数据结构作为索引，主要考虑因素就是数据结构对增删改查操作的效率如何，尤其是查询操作（通常查询操作包括等值查询、范围查询等）。

▎数据结构：数组

内存中一块连续的内存空间。定义一个数组对象，对象的指针指向了这块内存的起始地址，如果知道数组元素的下标，就能计算出该下标所对应的元素的内存地址。

☛ 分析

查找：对于一个有序数组，查找过程可使用二分查找法进行查找，时间复杂度为 O(logn)，效率非常高。因为是有序数组，范围查找也很高效：只需要找到起始元素即可。

更新：如果在知道元素下标的情况下，更新操作也非常快

删除：对于删除操作，如果不考虑空洞的话（将某个对应下标处的元素置为 null，这块连续内存块中相当于有个空洞），删除操作也很快。

插入：往数组中间插入一个数据，需将数组中要插入的目标位置，后面的所有元素先往后挪动一个位置，才能插入新的数据。

➳ 结论

插入操作涉及到数组的复制，要插入的数据越靠前，则需复制的数据就越多，不仅需要额外开辟内存，复制操作的消耗时间也需要考虑。在生产环境中，一张表的数据往往都是上千上万条，其大小可想而知。因此，从插入数据这一角度来看，数组不太适合作为 MySQL 索引的数据结构。

▎数据结构：哈希表

一种 key-value 形式的数据结构，底层采用数组+链表结构来实现。

存储原理：将 key 通过一个哈希函数计算出一个数字，以该数字作为数组的下标，将 value 存放到对应下标的数组中。

哈希冲突：对于不同的 key，在经过哈希函数计算后，可能出现相同的值。这就意味着同一个数组下标处要存放两个元素，此时会将数组中的元素变为一个链表，通过链表将这两个元素串联起来。

☛ 分析

根据上面哈希表的特点来看，对于增删改查操作，都是先根据 key 计算出一个值，就能定位到数据的目标位置了，时间复杂度都是 O(1)，速度特别快。

问题：但是碰到范围查询其缺点就暴露出来了，例如 between...and、>=、<=等范围查找操作。

由于 key 是经过哈希函数计算，再存放数据的，意味着数据的存放可能不是有序的，如果进行范围查找，只能对整个哈希表进行遍历，筛选出符合条件范围的数据。等同与全表扫描，对于数据量大的请况，全表扫描是非常耗时的！

➳ 结论

因此，从范围查询的场景来看，哈希表不太适合作为 MySQL 索引的数据结构。哈希表适用于等值查询的场景，例如非关系型数据库redis，其存储都是都是 key-value

▎数据结构：二叉树

每个节点最多只有2个子结点的树称之为二叉树，常用的二叉树类型有：二叉查找树、AVL 树（平衡树）、红黑树等。通过中序遍历达到有序性，保证范围查询的支持。

根据二叉树的特性可得出：其查找时间复杂度就是树的高度。

☛ 分析

二叉查找树：满二叉树情况下，查找的时间复杂度为 O(logn)。在不停的动态往树中插入和删除数据时，二叉查找树可能会退化成链表（左右树极不平衡），其查找时间复杂度就变成了 O(n)，性能不够稳定。

AVL 树（平衡树）：防止二叉树极端情况下退化成链表，衍生出来的。规定其左右2个子树的高度差<1，两边相对平衡，称之为平衡树。动态增、删数据过程中，为维护平衡避免树退化成链表，会进行额外的旋转操作，其旋转操作过于复杂，消耗一定性能。整体操作复杂度均为 O(logn)。

红黑树：一种近似平衡（不完全平衡），结点非黑即红的树，它的树高最高不会超过 2logn，因此查找时间复杂度为 O(logn)，无论是增删改查，性能都十分稳定。工程上，很多地方都使用的是红黑树这种数据结构，例如 Java 中的 HashMap、TreeMap 等。

非严格的平衡，换取增删节点时其旋转次数的降低，三次之内的旋转就能解决树的不平衡，AVL是严格平衡树，在增删除节点时，根据不同情况，旋转的次数比红黑树要多。所以红黑树的插入效率更高。（适合增删少，频繁查询的场景）

➳ 结论

AVL 树和红黑树的各操作性能相对稳定，但其二叉树的每个结点最多只有2个子节点，如果存储大量数据，树的高度会非常高。每遍历一层结点，就需要从磁盘读取一次数据（即发生一次 IO），磁盘 IO次数=节点所在的树高度。性能太差，存储大量数据场景下，不适合当作索引的数据结构

▎数据结构：B树系列

B树系列又可以分为：B-Tree 与 B+Tree

特点：树的每个结点可以拥有多个子结点，每个结点又能存储多个关键字（二叉树每个结点最多2子个节点，每个结点中只存储一个关键字），称为多叉树。

☛ 分析

B-Tree：无论叶子结点和非叶子结点，都存储索引值和数据

B+Tree：只有叶子结点会存放索引值和数据，非叶子结点只会存放索引值本身。对于非叶子结点，B+Tree 存放的索引值数量会远远大于 B-Tree（因为一个结点的空间是有限的，B-Tree 要存放索引+数据，而 B+Tree 只需存放索引），其子结点数也更多。

➳ 结论

存储同样大小的数据文件，B+Tree存储数据，其树的高度会远小于B-Tree高度，意味着发生的磁盘 IO 次数会更少，性能更优。因此MySQL 索引的数据结构使用的是 B+Tree。