(四）Mysql之索引介绍

索引数据结构的选择：Hash表、二叉树、平衡二叉树、（红黑树近似于平衡二叉树）、B树、B+树
1）Hash表：Java的HashMap、TreeMap就是Hash表结构，以键值对存储，时间复杂度是O（1），但是不支持范围的快速查询，范围查询时得需要全表扫描。

2）二叉查找树：二叉树的特点是每个节点最多有两个分叉，左子树与右子树数据顺序左小右大。查找的效率与树的高度相关。
不是所有的列都适合用二叉查找树，比如递增的id,用二叉查找树时会退化为链表。查找时相当于全表扫描

3）平衡二叉查找树：采用二分法思想，最主要的特征是树的左右两个子树的层级最多相差1. 在插入删除数据的时候通过左旋与右旋操作保持二叉查找树的平衡
不会出现左子树很高，右子树很低的情况。平衡二叉查找树也具备二叉查找树的特点，查询的时间复杂度是O（log2n）
缺点：
1 时间复杂度与树的高度相关，树有多高，就需要检索多少次，每个节点的读取都对应一次IO操作。
2 不支持范围查找，范围查询需要从根节点多次遍历，此时效率不高。

4）B树：改造二叉树
Mysql的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存，磁盘的IO操作非常耗时，所以我们要尽量减少磁盘的IO操作，也就是尽量减少树的高度。为了最大化利用一次IO空间，最简单的做法，每个节点存储多个元素，这样将二叉树改造成了多叉树，通过增加树的叉树，将树的高度从高瘦变为矮胖。这样的一种数据结构称为B树，B树是一种多叉平衡查找树，主要有如下特点。
1 B树的每个节点存储了多个元素，每个节点内有多个分叉
2 节点中的元素既包含键值也包含了数据，也就是在所有的节点都存储数据
3 父节点当中的元素不会出现在子节点中
4 所有的叶子节点都位于同一层，具有同样的深度，叶节点之间没有指针连接

5）B+树：改造B树
B+树与B树最主要的区别就是非叶子节点是否存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序的链表。
这样非叶子节点不存储数据，就可以存储更多的键值，将树的高度变得更低，更好地减少了磁盘IO的次数。

MyIsam索引实现：
MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。
查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

Innodb索引实现：
每个Innodb表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的是整行记录，一般情况下聚簇索引等同于主键索引，当一个表没有创建主键索引时，Innodb会自动创建一个ROWID字段来构建聚簇索引。创建规则如下：
1 表中定义主键primary key，Innodb会将主键索引引用作聚簇索引
2 如果表没有定义主键，Innodb会选择第一个不为NULL的唯一索引作为聚簇索引
3 如果以上都没有，会使用6字节长整型的隐式字段Rowid构建聚簇索引。该Rowid字段会在插入新行时自动递增。
除聚簇索引之外的索引都成为辅助索引。在Innodb中，辅助索引中的叶子节点存储的数据是该行的主键值，在检索时，Innodb使用此主键值在聚簇索引中搜索行记录（回表）。

组合索引：
组合索引的最左匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询（>、< 、betweek、like）就停止匹配。
索引使用口诀：
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上不计算，范围之后全失效
like百分写最右，覆盖索引不写*
不等控制还有or，索引失效要少用
组合索引创建原则：
1 频繁出现在where条件中的列，建议创建组合索引
2 频繁出现在order by 和 group by语句中的列，建议按照顺序去创建组合索引 order by a,b 需要组合索引列顺序（a,b），相反如果是(b,a)
是用不到索引的。
3 常出现在select语句中的列，也建议创建组合索引

索引优化建议
1. 表记录很少不需创建索引（索引是要有存储的开销）.
2. 一个表的索引个数不能过多。
（1）空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。
（2）时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。
所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。
3. 频繁更新的字段不建议作为索引。
频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。
4. 区分度低的字段，不建议建索引。（仅供参考）
比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名，手机号就非常适合建索引。
5. 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段。
主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。
6. 不建议用无序的值作为索引。例如身份证、UUID
更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。
7. 尽量创建组合索引，而不是单列索引。
优点
（1）1个组合索引等同于多个索引效果，节省空间。
（2）可以使用覆盖索引
创建原则：组合索引应该把把频繁的列，区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，可以尽量缩小筛选范围。

巴特西

(四）Mysql之索引介绍

最新文章

热门文章