MySQL只学有用的--MYSQL索引原理及使用
索引的基础理解
索引是数据库系统里面最重要的概念之一，索引就是为了提高数据查询的效率，就像书的目录一样。一个字典如果想找其中的一个字的解释，如果不借助目录的情况下，那估计要找很长一段时间了。索引就是数据库的目录。 我们学习索引就像是在学校查字典。
索引的常见模型（字典目录的编写形式）
下面我们介绍三种常见的数据结构：哈希表、有序数组、搜索树
1. 哈希表
哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key,就可以找到其对应的值即value。 哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。
多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下表示 ：
因为身份证号全国都是唯一的，所以我们用身份证号作为key，用姓名作为
图中，User2和User4根据身份证算出来的值都是N，但没关系，后面还跟了一个链表。假设，这时候你要查ID_card_n2对应的名字是什么，处理步骤就是：首先，将ID_card_n2通过哈希函数算出N;然后，按顺序遍历后面的链表，找到User2。
需要注意的是，图中四个ID_card_n 的值并不是递增的，这样做的好处是增加新的User时速度会很快，只需要往后面追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度 是很慢的。
你可以设想下，如果你现在要找身份证号在[ID_card_X，ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。
所以，哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
java语言中的hashMap就是用的这种数据结构，里面有一个概念叫做哈希碰撞，就是指将key 进行哈希运算之后，得到的值相等，value应该怎么存储。就是通过上面所说的，数组+链表的方式来保存的。
2. 有序数组
关于数组的介绍请看另一篇文章
数组
3. 二叉树
先来看一张图
二叉树的特别是：每个节点的左儿子小于父节点，父节点又小于右儿子。 这样如果你要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA->UserC->UserF->User2这个路径得到。这个时间复杂度是O(log(N))。
当然为了维护O(log(N))的查询复杂度，你就需要保持这颗树是平衡二叉树。为了做这个保证，更新的时间复杂度为O(log(N))。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树的相对于多叉树搜索效率最高，但是实际上大多数的数据库存储去并不使用二叉树。其原因是，索引不止存在于内存，还要写到磁盘上。
你可以想像一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。查询的时候是非常耗时的。为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。因此便使用N叉树。
以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。
聊一聊InnoDB的索引模型（B+树）
在InnoDB中，索引使用的数据模型为B+树。B+实际上是一个升级版的N叉树。下面介绍一下他的特性。
- B+树的非叶子节点不保存关键记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
- B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
- B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
- 非叶子节点的子节点数=关键字数（来源百度百科）（根据各种资料 这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1（来源维基百科)，虽然他们数据排列结构不一样，但其原理还是一样的Mysql 的B+树是用第一种方式实现）;
- B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
- B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。
（百度百科算法结构示意图）
（维基百科算法结构示意图）
假设我们有一个主键列为ID的表，表中有字段K，并且在K上有索引。
建表语句为：
表中R1~R5的（ID,K）值分别为（100，1）、（200,2）、（300,3）(500,5)、（600，6），两棵树的示例图：
介绍几个概念，最后一行为叶子节点， 根据叶子节点的内容不同，索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整合数据（图中用R 表示 ）。在InnoDB里，主键索引 也被称为聚簇索引。
非主键索引的叶子节点内容是主键的值。比如，我们创建一个用户表，用户ID为主键， name上创建了索引，name中索引的值为用户ID。 在InnoDB里，非主键索引也被称为二级索引。
下面介绍一下基于这两种索引的查询有什么区别？
语句一
select * from t where id =500
这是使用的主键查询，只需要搜索ID这一颗B+树。
语句二
select * from t where k =5
使用的是普通索引，需要分两步。第一步先搜索K索引树，得到ID 的值 为500。第二步 使用到ID索引树搜索ID=500.这个过程称为回表。
我们应该更多的使用主键索引。
索引的维护（《新华字典》的目录修改）
B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行ID的值为700，则只需要在R5的记录后面插入一个新记录。 如果新插入的ID值为400，需要逻辑上挪动后面的数据，空出位置。
如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部署数据过去。这个过程称为页分裂。性能会有影响。页分裂还会影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
为了解决这个问题，因为我们推荐主键是由自增主键来做，一些分布式的ID生成器中也是这么实现的。 主键越小越好，从小到大递增越好。
SQL索引执行过程–回表
create table T(
ID Int primary key,
k int NOT null Default 0,
s varchar(16) NOT null Default '',
index K(k)
) engine = innoDB;
分析一下SQL执行流程：
- 在K索引树上找到k=3的记录，取得ID=300;
- 再到ID索引树查到ID=300对应的R3;
- 在K索引树取下一个值K=5,取得ID=500;
- 再回到ID索引树查到ID=500对应的R4;
- 在K索引树取下一个值
在这个过程中，回到主索引树搜索的过程，称为回表。
索引创建技巧–索引覆盖
如果执行的语句是
select ID from t where k between 3 and 5
这时只需要查ID的值，而ID的值已经在K索引树上了，因此可以直接提供查询结果，不需要进行回表。在这个查询里面，索引K已经"覆盖了"我们的查询需求，我们称为索引覆盖。
覆盖索引可以减少树的搜索次数，所以可以显著提升查询性能。
索引创建技巧—最左前缀原则
先来看一个由name 和age组成的联合索引
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。
当你的逻辑需求是查询到所有名字是”张三“的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
如果你要查询的是所有名字第一个字是”张”的人，你的SQL语句的条件是"where name like ‘张%’"。 这时，你也能够用上这个索引，查找到一个符合条件的记录的ID3，然后向后遍历，直到不满足条件为止。
索引下推
看一下这个SQL
select * from t where name like '张%' and age = 10;
索引下推会对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表。
InnoDB在索引内部就判断了age是否等10，对于不等于10的记录，直接判断并路过。在这次SQL查询中，只需要对ID4,ID5进行回表搜索。
小结
- 索引主键要短，要连续否则会导致页分裂，影响性能。
- 索引创建的时候要注意索引覆盖，最左匹配，索引下推等优化技巧。
- 索引的数据结构是B+树。
引用
B+树数据结构
主要内容为<极客时间MYSQL实战45讲>的学习所得。
交个朋友好吗？
以上内容均为读书所得， 更多有趣有料的科技资讯请关注公众号。（交个朋友）
猿来衣舍
这是博主开的淘宝小店，主要经营舒适保暖的服饰，有袜子、主题卫衣、保暖衣。欢迎大家选购。
我们也在着手开发脚手架，到时候会做为商店福利的赠送给老用户
打开淘宝搜索 “猿来衣舍”这四个字就可以搜到小店，希望大家多多支持。
一个人能够走多远，关键在于与谁同行，我用跨越山海的一路相伴，希望得到您用金钱的称赞。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
