Mysql索引

一、一条select语句

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

二、索引介绍

　　索引的优势是可以提高数据检索的效率，降低数据库的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);

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

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

　　前缀索引：在文本类型如CHAR、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;

　　删除索引：

drop 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使用此主键值在聚簇索引中搜索行记录

　　1、聚簇索引（主键索引）

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

 

 

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

 

 

 　　2、辅助索引

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

 

 

 　　3、组合索引

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

　　

 

 

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

　　那么对于组合索引的创建原则：

　　　　（1）频繁出现在where条件中的列，建议使用组合索引

　　　　（2）频繁出现的order by和group by语句中的列，建议按照顺序去创建组合索引；

　　　　（3）常出现在select中的列，也建议创建组合索引

　　4、覆盖索引

　　上面说到辅助索引中叶子节点存储的数据是主键索引的key，因此需要进行一次回表，但是如果要是索引中就包含了要查询的所有数据（例如abc三列是一个组合索引，select只查询ab两列的值），那么就不需要回表再使用主键key进行一次索引查询了，这种索引就叫做覆盖索引。

　　覆盖索引还是一种辅助索引，由于辅助索引存储的数据是主键索引的key，因此相比主键索引来说，存储的内容更小。因此覆盖索引也是一种很常见的优化手段。

　　5、索引条件下推ICP

　　举个例子，还是abc为组合索引，那么当使用查询条件 a = 1 and b < 10 and c = 5这样的条件查询时，由于最左匹配原则，同时又由于b列使用了范围查询，因此只有索引ab两列生效，其实InnoDB查询组合索引时就会将所有的a=1 and b < 10的数据查询出来，然后，是否开启ICP的差异就来了。

　　如果不开启ICP，那么InnoDB就会将所有查询到的数据使用主键索引key进行回表查询，然后将查询的结果全部返回给mysql的服务层，服务层再根据a = 1 and b < 10 and c = 5做一次过滤；

　　如果开启了ICP，那么InnoDB就会将所有的数据再使用c = 5做一次判断，将不满足条件的数据剔除，然后将满足条件的数据进行回表查询，然后返回给mysql服务层。

　　可以看到，如果存在a = 1 and b < 10 and c != 5的数据时，开启ICP会少进行回表操作，从而提高了效率。

　　总结：不使用ICP时，不满足最左前缀的索引条件的比较是在存储引擎中进行的，非索引条件的比较是在mysql服务层进行的；使用ICP时，所有的索引条件的比较都是在存储引擎中进行的，非索引条件的比较是在mysql服务层进行的。

五、索引创建原则

　　1、哪些情况需要创建索引

　　　　（1）频繁出现在where条件判断、order排序、group by分组

　　　　（2）select频繁查询的列，其实这里可以考虑创建组合索引和覆盖索引

　　　　（3）多表join关联查询，on字段两边的字段都要创建索引

　　2、创建索引的一些建议：

　　　　（1）表记录很少的表不需要创建索引

　　　　（2）一个表的索引个数不能过多（会浪费空间；对数据进行变更时会变慢；太多的索引也会增加优化器的选择时间）

　　　　（3）频繁更新的字段不建议作为索引

　　　　（4）区分度低的字段，不要做索引

　　　　（5）在InnoDB中，主键索引建议使用自增的长整型，避免使用很长的字段（主键索引一个页节点是16K，主键字段越长，一个页存储的数据就越少，就会导致层高变高，IO增多）

　　　　（6）不建议用无序的值作为索引，例如UUID（更新数据时，会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间）

　　　　（7）尽量创建组合索引，而不是单列索引（节省空间，最好可以创建覆盖索引）