mysql一索引概述二

目录

一 索引的常见模型

1 哈希表

2 有序数组

3 二叉搜索树

3 多叉树

二 InnoDB 的索引模型

三 索引维护  -----从性能、存储空间的角度

四  离散性原则、 覆盖索引、最左前缀原则、索引下推

---

---

一 索引的常见模型

用于提高读写效率的数据结构很多，这里我先给你介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树

1 哈希表

是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以 找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一 个确定的位置，然后把value放在数组的这个位置。不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

 

优点   1，新增数据数据效率高   因为hash()计算和链表的插入效率都很高

           2，等值查询的效率高     查询过程  先hash()在顺序遍历链表

缺点   区间查询的效率低    因为整个数据结构不是有序的 所以哈希索引做区间查询的速度是很慢

 

2 有序数组

优点  等值查询，区间查询的效率都高   用二分法就可以快速得到，这个时间复杂度是O(log(N))

缺点  更新数据效率低，  往中间插入一个记录就必须得挪动后面所有的记录，成本太高

所以，有序数组索引只适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口信
息，这类不会再修改的数据。
 

3 二叉搜索树

经典数据结构了。还是上面根据身份证号查名字的例子，如果我们用二叉搜索树来实现的话，示意图如下所示：

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查
ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径 得到。这个时间复杂度是O(log(N))。
当然为了维持O(log(N))的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更 新的时间复杂度也是O(log(N))。

3 多叉树

多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。
为什么使用多叉树？    

1，减少树高-->减少数据块（叶子节点）的查询次数

2， 索引不止存在内存中，还要写到磁盘上

你可以想象一下一棵100万节点的平衡二叉树，树高20。一次查询可能需要访问20个数据块。在 机械硬盘时代，从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说，对于一个100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要20个10 ms的时间，这个查询可真够慢的。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N叉”树。这里，“N叉”树中的“N”取决于数据块的大小。
以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200 的3次方个值，这已经17亿了。

考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整 数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。N叉树由于在读写上的性能优点，以及适配磁配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
 

二 InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。 又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

每一个索引在InnoDB里面对应一棵B+树。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。
这个表的建表语句是：
mysql> create table T(  id int primary key,   k int not null,  name varchar(16),  index (k) )engine=InnoDB; 
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，

两棵树的示例示意图如下。

图4 InnoDB的索引组织结构
从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引 （secondary index）。

根据上面的索引结构说明，我们来讨论一个问题：

基于主键索引和普通索引的查询有什么区别？

如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到 ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
 

三 索引维护  -----从性能、存储空间的角度

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，如果插 入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。

如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

基于上面的索引维护过程说明，我们来讨论一个案例：
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。除了考虑性能外，我们还可以从存储空间的角度来看。假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型（bigint）则 是8个字节。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
 

有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：
1. 只有一个索引；
2. 该索引必须是唯一索引。
你一定看出来了，这就是典型的KV场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
 

四  离散性原则、 覆盖索引、最左前缀原则、索引下推

 

离散性原则

匹配列前缀可用到索引 like 9999% 不一定会走索引，mysql优化器会根据 该列的离散性做具体分析，如果该列的离散性不高，有可能直接全表扫描。

 

覆盖索引：

例如  mysql  表table a 使用innodb数据引擎 ，表中有两个字段 id  ,k   中字段k上建立索引

如果我执行 select * from T where k between 3 and 5；需要进行两次回表；

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的 值已经在k索引树上了，因此可以直接提供查询结果，不需要回表

目的减少回表，由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。为了直观地说明这个概念，我们用（name，age）这个联合索引来分析

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左 前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

可以通过安排索引内的字段顺序，达到少维护一个索引的目的。

那么，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使 用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、 (b) 这两个索引。
这时候，我们要考虑的原则就是空间了。比如上面这个市民表的情况，name字段是比age字段大 的 ，那我就建议你创建一个（name,age)的联合索引和一个(age)的单字段索引

索引下推

在联合索引（name, age）的情况下 执行如下sql

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

根据前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满 足条件的记录ID3。当然，这还不错，总比全表扫描要好。
当然是判断其他条件是否满足。
在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。
而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

在图3和4这两个图里面，每一个虚线箭头表示回表一次。
图3中，在(name,age)索引里面我特意去掉了age的值，这个过程InnoDB并不会去看age的值， 只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。
图4跟图3的区别是，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的 记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判断， 就只需要回表2次。

小结

数据库索引的概念，包括了覆盖索引、前缀索引、索引下推。 你可以看到，在满足语句需求的情况下， 尽量少地访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。
 

建立索引需要注意什么？

1. 索引列的数据长度能少则少。
2. 索引一定不是越多越好，越全越好，一定是建合适的。
3. 匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引；
4. Where 条件中 not in 和 <>操作无法使用索引； 匹配范围值，order by 也可用到索引；
5. 多用指定列查询，只返回自己想到的数据列，少用select *；
6. 联合索引中如果不是按照索引最左列开始查找，无法使用索引；
7. 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
8. 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；