Mysql优化之索引详解

一、什么是索引

我们知道，mysql数据库中表的数据都是存在磁盘上的，而我们在平常进行sql查询时，通常都是读取磁盘上的数据到内存中。在这个过程中最耗时的操作就是磁盘I/O。所谓的mysql优化 查询优化等最本质的优化原则就是减少磁盘I/O次数，从而提高数据库查询的效率。而索引就是帮助我们解决这个问题的一种方式，什么是索引？

索引就是帮助Mysql高效获取磁盘数据的一种排好序的数据结构。

既然索引是一种数据结构，那么到底什么样的数据结构可以作为索引呢？一般常用的数据结构有一下几种：

1. 二叉树
2. 红黑树（平衡二叉树）
3. Hash
4. B-Tree
5. B+Tree（本文重点）

下面依次说明这几种数据结构是否适合作为Mysql索引。

二叉树：这种数据结构不做过多的详细说明（不清楚的可以从网上查一下），就是右边节点的数据大于左边接点的数据的顺序。但它并不适合作为Mysql的索引，原因如下。

1.数据按照顺序插入时，二叉树就演化成了一个链表，链表的查询效率并不高。

2.当数据量达到几百万甚至几千万时，树的高度会过高，从而遍历的次数会增多，而每次遍历一个节点就是一次磁盘I/O.

红黑树：这种数据结构又叫平衡二叉树，也是一种二叉树，不过它解决了二叉树的一个缺点，在顺序插入时，它可以通过左旋或者右旋的方式避免演化成链表。但是红黑树也不是Mysql数据索引的合适选择，原因如下。

1.数量过多时，树高不可控，会变的很高甚至达到几千几万的高度，查询效率会很低。

2.左旋和右旋操作还是比较耗时操作的。

Hash:一般是数组+链表结合的一种结构，通过hash(index)计算定位数据在数组中的位置，这种数据结构在某些场景是可以作为Mysql的索引的，而Mysql也确实将hash作为索引的一个选项，不过一般不常用,工作原理：通过计算索引字段的值，定位到数组上的位置，并保存。

优点：

1.只需要一次hash计算就能定位到位置。

2.某些场景（没有或者很少的hash冲突，且仅“=”和“in操作时”）效率比索引树要高，只需一次hash计算就可以了  无论是查询数据还是插入索引数据都很快

缺点：

1.受数据结构的影响，这种索引结构只支持“=”和“in”操作，不支持like，范围查找等操作。

2.需要解决hash冲突（一般是链表法）

总结：因为hash这种数据结构的约束性太大，一般在工作中不太常用。

B-Tree:这种数据结构也是一种树形结构，而Mysql的常用索引结构B+Tree（我们本文研究的重点结构）是B-Tree的一种优化。先来认识一下这种数据结构

特点：

1.叶子节点具有相同的深度，且叶子节点的指针为空

2.所有节点的元素不重复

3.节点从左到右依次递增且每个节点存储索引值和data数据

因为这种结构在单个节点上可以存储多个索引元素，从而可以避免像二叉树那样树高过高的情况，因此比较适合Mysql的数据库索引，当然Mysql对这中数据结构进行了一种优化 ：B+Tree。

二、B+Tree索引结构

B+Tree是B-Tree的演进，作为Mysql中最常用的一种数据结构，它有如下特点，先看图：

特点：

1.非叶子节点不存储data数据，只存储索引的冗余，可以存放更多的索引

2.从左到右依次递增

3.叶子节点包含所有的索引元素，且存储data数据

4.叶子节点使用指针连接，这使得进行范围查找时提高查找效率。

与B-Tree的不同点：

1.B-Tree每个节点都会保存data数据，B+Tree只在叶子节点保存data数据

2.B-Tree叶子节点之间并没有指针连接，范围查询是需要多次遍历索引树。B+Tree叶子节点之间有指针连接  范围查询时不用多次遍历索引树，只需要获取右边节点数据即可。

3.B-Tree所有节点元素不重复，B+Tree子节点都有父节点元素的冗余。

针对于这种B+Tree索引结构，Mysql有两种实现方式（引擎）：MyISAM和InnoDB。

1.首先我们要知道两种存储引擎都是形容表的

2.MyISAM实现的索引是非聚集索引

3.InnoDB引擎实现的索引是聚集索引（Mysql比较常用的存储引擎）

注意：聚集索引是索引和表数据存储在一起的索引，非聚集索引就是索引和数据不存储在一起的索引。

MyISAM引擎实现的索引（非聚集索引）：

例如：我有一个表 t_test_myisam  使用MyISAM引擎

 那么它在磁盘上存储的结构是怎么样的？

 

 它会生成三个文件：

.frm:是记录表结构的文件

.MYI:就是我们创建的索引的存储文件

.MYD:存储表中的数据，索引文件中保存的地址指针就是指向此文件中的数据。

因此MyISAM实现的是一个非聚集索引 大概情况如下：

叶子节点存储的data不是表中的其他列的值，而是指向索引所在行的磁盘地址。

InnoDB引擎实现的索引

例如表t_test_innodb使用InnoDB引擎

那么它在磁盘上存储的结构如下：

 .frm:是记录表结构的文件

.ibd:保存的是索引和表数据，是一个使用B+Tree组织的索引和表数据文件

特点：

1.主键索引的叶子节点存储的是索引所在行的其他列字段的值（是一个聚集索引）

2. 二级索引的叶子节点存储的是主键值，然后根据主键值查询主键索引树这就是回表。

3.表数据本事就是使用B+Tree组织的一个索引+数据的索引文件（.ibd文件）

4.叶子节点包含了所有的索引和表数据的值

针对以上索引结构做以下说明：

1.在建表时  必须制定主键。因为你不指定主键的话，mysql会从其它值不重复的字段中找一个作为主键，如果值都有重复的，则会帮忙建立一个隐藏的主键，为了提高效率 我们不应该把这件事麻烦mysql。应该自己完成。

2.主键最好是整形的。因为整形的数据在索引树中，比较大小时速度很快，如果是varchar类型的还要转化为ascall码进行比较，相比效率不高。

3.主键最好自增。因为在索引树中插入的数据，不是自增有序的话，比例插入节点中间，可能会导致节点的分裂和父节点的变化等，而自增的主键插入只需要在节点右面在插入索引元素即可 减少了节点的分裂处理。

4.为什么二级索引叶子节点保存的是主键的值？为什么不保存索引列所在行的其他列的值？

我的理解是这样的：

第一：如果二级索引也保存表的其他列数据，那么如果索引有多个 则就需要保存三分表数据  这比较占磁盘空间

第二: 在新插入数据记录时，需要维护多个索引树的叶子节点的数据，这样会产生数据不一致的风险，比如插入一天数据，维护主键索引的叶子节点后，还要去维护二级索引的叶子节点，其中要有一个因为服务等原因失败了 就会有数据不一致的风险。但是只需要维护一个主键索引一份 就不会有这个问题。

小知识：到此我们差不多把mysql的索引结构已经清晰的了解了，那么问题来了，一张表中存储多少数据合适？

我们知道索引是树形的，磁盘I/O次数等同于树的高度，那么假设我们树高是3的话。

一般数据库数据存储是以页为单位的，每页大小16k左右。

假设主键索引中主键使用Bigint类型占8B，指针占6B则总共占  8+6=14B

那么

联合索引： 假设有联合索引如下

KEY 'idx_name_age_position' （'name','age','position'）USING BTREE

1.联合索引遵循最左列原则，根据联合索引的迅速 从左到右的一次进行排序，先根据name字段排序，name相同的在根据age字段排序  在name和age相同情况下在按照position排序

2.sql语句查询  也遵循最左列原则  

where name="Bill" and age = "20" and position="dev"; 会使用联合索引进行查询

where  name="Bill" and age = "20"; 会使用name和age联合索引中的两个索引字段

 where  age = "20" and position="dev";  不会使用索引，因为在联合索引中name为确定的情况下，age和position的顺序是不确定的，无法使用索引进行定位。

where name="Bill"  and position="dev"; 会使用联合索引中的name的索引字段，在没有确定age的情况下，position的顺序是不确定的  所以 无法使用position索引字段。

 总结：

1.Mysql索引为B+Tree树形结构

2.MyISAM引擎实现非聚集索引，索引树中叶子节点存储的是索引所在行的磁盘地址；InnoDB引擎实现的是聚集索引（针对主键索引），叶子节点存储的是索引元素+表数据。二级索引叶子节点存储的是索引元素+主键值

3.数据查询效率优化===》减少磁盘I/O===>减少索引树的高度。