Mysql索引创建和优化策略

一、一条ｓｅｌｅｃｔ语句

一条查询语句查询时，执行流程如下：

二、索引介绍

索引的优势是可以提高数据检索的效率，降低数据库的IO成本；可以通过索引列对数据进行排序，降低数据排序成本，降低了CPU消耗。其劣势是索引会占据磁盘空间，其虽然会提高查询效率，但是会降低更新表的效率。

索引可以分为聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，如果没有特别说明，默认都是使用B+树结构的索引。

主键索引：

索引列中的值必须是唯一的，不允许有空值

alter table address add PRIMARY KEY(address_id);普通索引：

Mysql中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

alter table address add index i_city(city_id);唯一索引：

索引列中的值必须是唯一的，但是允许为空值

create UNIQUE INDEX index_city on address(city_id);全文索引：

只能在文本类型ｃｈａｒ、VARCHAR、TEXT类型字段上创建全文检索，字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文检索。但是在实际开发中很少使用，如果用到的话，可以使用solr，es等组件。

空间索引：

在Mysql5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，Mysql在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引：

在文本类型如ｃｈａｒ、VACHAR、TEXT类型的列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

alter TABLE address add index idx_district(district(3));

按照列的数量可以有单列索引（索引中只有一个列）和组合索引（使用2个以上的字段创建索引）；组合索引的使用，需要遵循最左原则；一般情况下，建议使用组合索引代替单列索引。

alter TABLE address add index ind_add2(address,address2);查看索引：show index from address;删除索引：ｄｒｏｐ index ind_add2 on address;三、索引的数据结构

索引的数据结构，需要至少支持等值查询和范围查询这两种查询需求。

常用的数据结构有Hash表、二叉树、平衡二叉树、B树、B+树，对于数据结构的变化，可以使用https://www.cs.usfca.edu/~galles/visualization/Algorithms.html进行演示。

Hash表：

HashMap、TreeMap就是Hash表结构，以键值对存储，Hash表的查询效率很高，但是只限于等值查询，不支持范围查询，如果非要使用范围查询，只能全表扫描。

二叉树：

二叉树的特点：每个节点最多有两个分叉，左子树小于父节点，右子树大于父节点。

一个完美的二叉树（平衡二叉树），查找数据时，类似于二分查找，但是二叉树有可能变成一个单链表，那么就会变成全表扫描。

平衡二叉树：

平衡二叉树就是一个类似完美的二叉树，最大的特征就是左右两个子树的层级最多相差1。在插入或者删除数据时，通过左旋或者右旋保证该特点。

但是平衡二叉树也有一些问题：（1）时间复杂度和层高有关，树有多高就需要查询多少次，也就是需要多少次磁盘IO，这样就会导致性能变差；（2）平衡二叉树不支持范围的快速查询，因为查询需要从根节点多次遍历，查询效率不高。

B树：

Mysql的数据是存放在磁盘中的，查询数据时，需要将磁盘中的数据加载到内存中，磁盘的IO非常的耗时，所以我们就需要尽量少的进行磁盘IO，但是访问二叉树的每个节点都需要进行磁盘IO，那么就需要考虑降低树的高度。

假如Key为bigint=8字节，每个节点有两个指针分别指向左子树和右子树，每个指针为4个字节，那么一个节点就需要占用16个字节，但是Mysql的InnoDB存储引擎一次IO操作会读取一个页的数据，这一个页是16K，可以看到这个页上只放16个字节，利用率非常低。如果在一个数据页上全部放上数据，充分使用数据页，可以发现，每个数据页可以放1000个数据，那么两层的树就可以存放一百万的数据，三层的树就可以存放一百亿的数据，这就是B树。

B树的主要特点就是：（1）B树的节点中存储着多个元素，每个内节点有多个分叉；（2）节点中的元素包含key值和数据，节点中的键值从小到大，也就是说，所有的节点都存储数据；（3）父节点当中的元素不会出现在子节点中；（4）所有的叶子节点都处于同一层，叶子节点具有相同的深度，叶子节点之间没有指针链接。

B树查找一次数据，其实与平衡二叉树对比数据的次数一致，但是由于读取次数（IO次数）变少，因此性能提高，并且一般情况下，B树的高度2~3层就能满足大部分的应用场景，所以使用B树可以很好的提升查询的效率。

但是B树也有缺点：（1）B树不支持范围查找，如果需要范围查找，还是需要从根节点进行多次遍历，查询效率有待提高；（2）由于非叶子节点存储的有数据，因此节点可以存储的数据量变少，导致层高变高，因此就会导致查询时IO次数增多，影响查询效率。

B+树：

B+树与B树最大的区别就是非叶子节点不能存储数据，所有的数据都存储在叶子节点。那么就是说，所有的数据都存储在叶子节点，所以每次查找都需要检索到叶子节点才能查询到数据。因此B+树可以保证快速的范围查询。

四、Mysql索引实现MyISAM索引

MyISAM的数据文件和索引文件是分开存储的，MyISAM使用B+树构建索引树时，叶子节点中存储的key是索引列的值，数据为索引列所在行的磁盘地址。

如果是主键索引，那么就直接按照上图中的key进行等值或者范围查询，查到数据后，使用指针将数据查询到，同时会将索引节点缓存在Mysql缓存中，而数据缓存依赖于操作系统的缓存。

如果是辅助索引（非主键索引），其数据结构和主键索引的数据结构一样。由于数据不是唯一的，因此就算使用等值查询，也是需要使用key的范围查询。

InnoDB索引

每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的是整行记录。

InnoDB创建聚簇索引的规则：（1）如果表上定义的有主键，InnoDB将主键索引用作聚簇索引；（2）如果没有主键，则将第一个不为NULL的唯一索引作为聚簇索引；（3）如果没有不为空的唯一索引，InnoDB会使用一个6字节长整型隐式字段rowid字段构建聚簇索引，rowid会在插入数据时自增。

除了聚簇索引之外的索引都被称为辅助索引，在InnoDB中，辅助索引中的叶子节点存储的数据是该行的聚簇索引值，InnnoDB使用此主键值在聚簇索引中搜索行记录

聚簇索引（主键索引）

InnoDB的数据和索引存储在一个.ibd文件中，InnoDB的数据组织方式，是聚簇索引。

　　由于主键索引中存储了行数据，索引使用InnoDB进行主键查询时，可以快速地获取行数据，不需要在去磁盘中获取，因此聚簇索引可以节省一次IO操作，从而提高查询效率。

辅助索引

由于辅助索引的数据存储的是聚簇索引的值，因此使用辅助索引进行查询时，当查到数据后，还需要再使用主键查询一次聚簇索引，因此会查询两次索引。流程如下所示：

组合索引

组合索引存储的key就是组合值，例如a、b、c三列作为组合索引，那么B+树的key首先按照a列进行排序，然后按照b列进行排序，最后按照c列进行排序。由于组合索引是辅助索引的一种，因此数据存储也是聚簇索引的值。如下图所示：

我们经常说的最左匹配原则就是和联合索引的索引存储结构、检索方式有关系，按照上面说的a、b、c三列说明，b列是在a列的值相同的情况下才排的顺序，如果a列不存在，b列的顺序就无从谈起，因此，必须要有a列，b列索引才能生效。因此当使用组合索引时，组合索引会从左匹配到适用范围查询（、between、like）的那一列为止，后面的索引就不再有用，例如使用了a=1 and b