Mysql索引的深度剖析

索引是什么

维基百科对数据库索引的定义：
数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。

首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。
但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为他它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

索引类型

在InnoDB里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引引用primay key）、唯一索引。
普通：也叫非唯一索引，是最普通的索引，没有任何的限制
唯一：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primay key创建。
全文Fulltext针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

索引存储模型推演

二分查找

双11过去之后，你女朋友玩猜数字游戏。
猜猜双十一花了多少钱，给5次机会。
10000？ 低了。 30000？高了。接下来你会猜多少呢？
20000。为什么你不猜11000，也不猜29000呢？

其实这个就是二分查找的一种思想，也叫折半查找，每一次，我们都把候选数据缩小了一半。如果数据已经排过序的话，这种效率是非常高的。
所以第一个，我们可以考虑用有序数组作为索引的数据结构。
有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变index），所以只适合存储静态的数据。
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不高。
所以，有没有可以使用二分查找的链表呢？

二叉查找树（BST Binary Search Tree）

二叉查找树的特点：
左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

二叉查找树既能够实现快速查找，又能实现快速插入。
但是二叉查找树有一个问题：
就是他的查找耗时和这棵树的深度相关的，在坏的情况下时间复杂度会退化成O(n)。
什么情况是最坏的情况呢？
数据结构的动态演示

造成它倾斜的原因是什么呢？
因为左右子树深度差太大，这棵树的左子树根本没有节点—也就是它不够平衡。所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？

平衡二叉树（AVL Tree） 左旋、右旋

AVL Trees（Balanced binary search trees）
平衡二叉树的定义：左右子树深度差绝对值不能超过1。
比如左子树的深度是2，右子树的深度只能是1或者3.
这个时候我们再按顺序插入1,2，3,4，5,6，不会变成一颗斜树

插入 1、2、3。 我们注意看：当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在 2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它 没有子节点，所以就会违反平衡二叉树的定义。
那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们 要把 2 提上去，这个操作叫做左旋。


应该存储三块的内容：
第一个是索引的键值。比如我们再id上面创建了一个索引，我在用where id=1的条件查询的时候就会找到索引里面的id的这个键值。
第二个是数据的磁盘地址，因为索引的作用就是查找数据的存放的地址。
第三个，因为是二叉树 ，它必须还要有左子树和右子节点的引用，这样我们才能找到下一个节点。

InnoDB逻辑存储结构

Mysql的存储结构分为5级：表空间、段、族、页、行

Table Space

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。

段Segment

表空间是由各个段组成的，常见的有数据段、索引段、回滚段等，段是一个逻辑的概念。一个ibd文件（独立表空间文件）里面会由很多个段组成。
创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以2。

族 Extent

一个段（Segment）又由很多的族（也可以叫区）组成，每个区的大小是1M（64个连续的页）。
每个段至少会有一个族，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是族。

页 Page

为了高效管理物理空间，对族进一步细分，就得到了页。族是有连续的页（Page）组成的空间，一个族中有64个连续的页。（1M/16kb=64）。这些页面在物理上和逻辑上都是连续的。
跟大多数数据库一样，InnoDB也有页的概念（也可以称为块），每个夜默认16KB。页时InnoDB存储引擎磁盘管理的最小单位，通过innodb_page_size设置。
一个表空间最多拥有2^32个页，默认情况下一个页的大小为16kb，也就是一个表空间最多存储64TB的数据。

注意，文件系统中，也有页的概念。
操作系统和内存打交道,最小单位是页Page。文件系统的内存通常是4K

假设一行数据大小是1K,那么一个数据页可以放16行这样的数据。
eg： 一个页放3行数据。

往表中插入数据时，如果一个页面已经写完，产生一个新的页面。如果一个族的所有页面都被用完，会从当前页面所在段新分配一个族。
如果数据不是连续的，往已经写满的页中插入数据，会导致页面分裂。


页分裂逻辑

行Row

InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。

AVL树用于存储索引数据

索引的数据，是放在硬盘上的。查看数据和索引的大小。
当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次IO。InnoDB操作磁盘的最小单位是一页（或者一个磁盘块），大小是16k（16384字节）。
那么，一个树节点就是16K的大小。
如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间。
所以每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。
如果是机械硬盘时代，每次要从磁盘读取数据需要10ms左右的寻址时间，交互次数越多，消耗的时间越多

比如上面这张图，我们一张表里面有6条数据，当我们查询id=37的时候，要查询两个子节点，就需要跟磁盘交互3次，如果我们有几百万的数据呢？这个时间就更加难以估计。

多路平衡查找树（B Tree）分裂、合并

Balanced Tree
这个就是我们的多路平衡查找树，叫做B tree （B代表平衡）
跟AVL树一样，B树在枝点和叶子节点存储键值、数据地址、节点引用。
它有一个特点：分叉数（路数）永远比关键字数多1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。

B Tree的查找规则是什么样的呢？
比如我们要在这张表里面查找15,。
因为15小于17，走左边
因为15大于12，走右边
在磁盘块7里面就找到了15，只用了三次IO.

B+树（加强版多路平衡查找树）

B tree 的效率已经很高了，为什么Mysql还要对B tree进行改良，最终使用了B+tree呢？

总体来说，这个B树的改良版本解决的问题比Btree更全面。
我们来看一下InnoDB里面的B+树的存储结构。

MySQL中的B+Tree有几个特点：
1，它的关键字的数量是跟路数相等的。
2，B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据，搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

eg：
假设一条记录是1K，一个叶子节点（一页）可以存储16条记录。非叶子节点可以存储多少个指正？
假设所以字段是bigint类型，长度为8字节。指针大小再InnoDB源码中设置为6字节，这样一共14字节。非叶子节点（一页）可以存储16384/14=1170个这样的单元（键值+指针），代表有1170个指针。

树深度为2的时候，有11702个叶子节点，可以存储的数据为1170117*16=21902400

在查找数据时一次页的查找代表一次IO，也就是说，一张2000万左右的表，查询数据最多需要访问3次磁盘。

3，B+ Tree的每个叶子节点增加了一个指向相邻叶子节点的指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
4，它是根据左闭右开的区间[)来检索数据。

我们来看一下B+tree的数据寻址的过程：
1）比如我们要查找28，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下寻址，28是[28,66)的左闭右开区间的临界值，所以会走中间的子节点，然后继续搜索，它又是【28,34）的左闭右开的区间临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。
2）第二个，如果是范围查找，比如要查询从22到60的数据，当找到22之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）

B+Tree的特点：
1）它是B tree 的变种，B Tree 能解决的问题，他都能解决。B tree 解决的两大问题是什么？（每个节点存储更多的关键字，路数更多）
2）扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整颗树B+Tree拿到所有数据）
3)B+ Tree的磁盘读写能力相对于B tree来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4）排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5）效率更加稳定（B+tree永远在叶子节点拿到数据，所以IO次数是稳定的）

为什么不用红黑树

必须满足5个约束：
1，节点分为红色或者黑色
2，根节点必须是黑色的
3，叶子节点都是黑色的Null节点
4，红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）
5，从任意节点出发，到其每个叶子节点的路径包含相同数量的黑色节点

基于以上原则，可以推导出：
从根节点到叶子节点的最长路径（红黑相间的路径）不大于最短路径（全部都是黑色节点）的2倍
为什么不用红黑树？ 1，只有两路。2，不够平衡
红黑树一般只放在内存里面用。例如java的treeMap

索引方式

在Navicat的工具中，创建索引，索引方式有两种，Hash和B tree。
HASH：以KV的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。

哈希索引有什么特点呢？
第一个，它的时间复杂度是O（1），查询速度比较快。因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。
第二个，我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询（=IN），不支持范围查询（<> <= >= between and ）.
另外一个就是如果字段重复值很多的时候，会出现大量的哈希冲突（采用啦联发解决），效率会降低。

B+ Tree落地形式

MySQL架构
MySQL是一个支持插件式存储引擎的数据库。在MySQL里面，每个表在创建的时候都可以指定它所使用的存储引擎。

MySQL数据存储文件

首先，MySQL的数据都是文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址。在MySQL中有这么一个参数，我们来看一下。

MyISAM

在MyISAM里面，另外有两个文件
一个是.MYD文件，D代表Data，是MyISAM的数据文件
一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引，比如我们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。
也就是说，在MyISAM里面，索引和数据是两个独立的文件。

那我们怎么找到数据呢？
MyISAM的B+Tree里面，叶子节点存储的数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

这里是主键索引，如果是辅助索引，有什么不一样呢？
在MyISAM里面，辅助索引也在这个.MYI文件里面。
辅助索引跟主键索引存储和检索数据的方式没有任何区别，一样是在索引文件里面找到磁盘地址，然后再数据文件里面获取数据。

InnoDB

InnoDB只有一个文件（.ibd）
在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。
在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据

什么叫做聚集索引（聚族索引）？
索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫做聚集索引）。
在InnoDB里面，它组织数据的方式叫做（聚集）索引组织表（clustered index organize table），所以主键索引是聚集索引，非主键都是非聚集索引。
如果在InnoDB里面主键是这样存储的，那主键之外的索引，比如我们在name字段上建的普通索引，又是怎么存储和检索数据的呢？

InnoDB中，主键索引和辅助索引是有一个主次之分的。
辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。
比如我们用name索引查询name=‘青山’,它会在叶子节点找到主键值，也就是id=1，然后再到主键索引的叶子节点拿到数据。

为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？如果主键的数据类型比较大，是不是比存地址更消耗空间呢？
我们前面说到B tree是怎么实现一个节点存储多个关键字，还保持平衡呢？
是因为有分叉和合并的操作，这个时候键值的地址会发生变化，所以辅助索引里面不能存储地址。

另外一个问题，如果一张表没有主键怎么办？
1，如果我们定义了主键（Primary key），那么InnoDB会选择主键作为聚集索引。
2，如果没有显式定义主键，这InnoDB会选择第一个不包含有Null值的唯一索引作为主键索引。
3，如果也没有这样的唯一索引，这InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

索引的使用原则

我们容易有以下一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这样呢？

列的离散度

第一个叫做列的离散度，我们先来看一下列的离散度的公式、
count（distinct（column_name））：count（*）,列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。
简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。
了解了离散度的概念后，我们再来思考一个问题，我们在name上建立索引和gender上面建立索引有啥区别。
当我们在gender上建立的索引区检索数据的时候，由于重复值太多，需要扫描的行数就越多。

联合索引最左匹配

前面我们说的都是针对单列创建的索引，单有的时候我们的多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。

覆盖索引

回表
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一颗索引树，这个过程就叫回表。

select * from user_innodb where name = 'qingsan';

在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

我们先来创建一个联合索引：

 ALTER TABLE user_innodb DROP INDEX comixd_name_phone; ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666'; 
EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666'; 
EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';

Extra里面的值为“Using Index”代表使用了覆盖索引。

select * ，用不到覆盖索引。

索引条件下推

索引的创建和使用

因为索引对于改善查询性能的作用是巨大的。所以我们的目标是尽量使用索引。

索引的创建

1，在用于where判断order排序和join的（on）字段上创建索引
2，索引的个数不要过多
–浪费空间，更新变慢
3，区分度低的字段，例如性别，不要建索引
离散度太低，导致扫描行数过多。
4，频繁更新的值，不要作为主键或者索引
页分裂
5，组合索引把散列性高（区分度高）的值放在前面
6，创建复合索引，而不是修改单列索引

什么时候用不到索引

1，索引列上使用函数（replace\substr\sum\count\avg）表达式计算等

explain SELECT * FROM `t2` where id+1 = 4;

2，字符串不加引号，出现隐士转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone; 
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain SELECT * FROM `user_innodb` where name = 136; 
explain SELECT * FROM `user_innodb` where name = '136';

3，like条件中前面带%

explain select *from user_innodb where name like 'wang%'; 
explain select *from user_innodb where name like '%wang';

4，负向查询
Not Like不能：

explain select *from employees where last_name not like 'wang

!= （<>）和 NOT IN 在某些情况下可以：

explain select *from employees where emp_no not in (1) 
explain select *from employees where emp_no <> 1

注意一个SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。