MySQL之索引

索引底层结构

MySQL支持两种索引：一种是基于B树索引、一种是基于哈希表索引，这两种索引的查询效率比较高

MySQL中B树（B+树）的索引结构

B树特征

大多数的存储引擎都支持B树索引，B树通常意味着所有的值按照顺序存储，并且每个叶子节点到根的距离相同，B树索引能够加快数据访问的速度

B树特征

1. 根节点至少包含两个孩子节点
2. 树中每个结点最多含有m个孩子(m >= 2)
3. 除了根节点和叶结点外，其他每个结点至少含有ceil(m/2)个孩子，ceil为向上取整
4. 所有叶子结点位于同一层（高度相同）
5. 假设每个非终端结点中包含有n个关键字信息，其中

• Ki(i = 1…n)为关键字，且按顺序升序排列
• 关键字的个数n必须满足：[ceil(m / 2) - 1] <= n <= m - 1
• 非叶子结点的指针P[1],P[2],…,P[M];其中K[1]指向关键字小于K[1]的子树，P[M - 1] 指向关键字大于K[M - 1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树，比如看图中关键字值为8的这个结点，P1所对应的这个子树，其值均小于8

查询效率O（logn）

B+树特征（优于B树）

B+树是B树的变体，基本定义与B树相同，不同点在于：

• 非叶子结点的子树指针与关键字个数相同（所以相对于B树，B+树能够存储更多的关键字）
• 非叶子结点的子树指针P[i]，指向关键字值**[K[i], K[i+1])**的子树，注意区间为左开右闭。
• 非叶子结点仅仅用来索引，数据都保存在叶子结点中（B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字）
• B+树相对于B树更矮
• 所有叶子结点均有一个指针指向下一个叶子节点

范围查询  where age < 10 and age <35

B+ 树更适合用来做存储引擎索引

1、B+树的特点使得磁盘IO的代价更小，B+树的内部节点并没有指向关键字具体指针信息，因此其内部节点相对于B树更小,所以B+树的磁盘读写的代价更低

2、B+树的查询效率更加稳定（所有数据都存放在叶子节点，查询效率是O(logn))

3、B+树更利于数据库的扫描，进行范围查询

 哈希结构

哈希结构和B树结构都不支持范围查询，哈希表对数据不排序，范围查询效率比较低，查询时要查询整个哈希表结构

哈希结构通过一定的算法查询数据，在相同情况下，遇到大量的哈希值相等时性能不一定比B树性能高

主键索引、辅助索引、聚集索引、非聚集索引

MySQL两种主流的存储引擎是innodb和myisam

MyISAM存储引擎相关索引

主键索引

MyISAM存储引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录地址

 辅助索引

MyISAM中，主键索引和辅助索引在结构上是一样的，B+树构建辅助索引，其叶子节点data域存放的是数据地址

主键索引要求key是唯一的，辅助索引的key是可以重复的

 根据以上图可知，在MyISAM中，按照B+树的搜索算法搜索索引，如果指定key存在，则取出其data域的值，然后以data域的值为地址，读取相应地址数据

MyISAM中将索引和数据分开存储，因此MyISAM的索引方式称之为非聚集索引

在磁盘存储上,myisam非聚集索引将关键字和数据分开存储，对应student表存在三个文件：student.MYD、student.MYI、student.frm

student.frm(存储表的结构)

student.MYD(存储表的数据)

student.MYI(存放表的索引数据)

INNODB存储引擎相关索引

主键索引

INNODB中的主键索引，叶子节点中，索引关键字和数据在一起存储

 可以看到，索引关键字和数据一起存储在叶子节点上

辅助索引

在INNODB的辅助索引上，叶子结点存放的是索引的关键字和对应的主键，如图

辅助索引的B+树，先根据辅助索引的关键字找到对应的主键，再去主键索引树上找到对应的行数据

从索引树上看，INNODB的关键字和数据是存放在一块的，这种索引称之为聚集索引

在磁盘存储上，INNODB聚集索引存在两个文件，分别是student.frm和student.ibd

student.frm(存储表的结构)

student.ibd(存放索引和数据）

索引执行过程分析

1.单表查询-普通索引

对于student表来进行数据查询，通过SID来查询的时候（SID是主键），当对主键进行查询操作时，因为使用primary key主键索引(innodb会自动给主键字段创建主键索引树)，根据前面知识当通过SID作为where检索条件过滤时，首先从B+树的主键索引快速找到数据，因为是INNODB的存储引擎，将主键索引和数据都存放在一起，找到SID，就可以找到这一行数据，不用把整个表扫描一遍、效率比较高

使用name字段来查询”LG1213“名称

explain select * from student where Sname="LG1213";

 根据name字段来查询学生信息，用到了idx_name辅助索引，INNODB的辅助索引叶子节点存储的是辅助索引的值和对应的行的主键（此处对应的主键SID），根据以上的SQL，先查询name字段的辅助索引B+树，找到name=’LG1213‘的节点，获取对应的主键SID=6，然后在拿SID=6去主键索引树上寻找数据，上面SQL总共搜索两次B+索引树。

通过name找到对应主键ID

explain select SID from student where Sname = "LG1213";

上面的SQL执行过程中使用了idx_name辅助索引，在辅助索引树上根据name来查找对应行的主键，上面select中只查询SID，因此在辅助索引树上就可以拿到结果，不用再去主键索引树上来查询结果

explain select Ssex from student where Sname = "LG1213";

查询两次B+树，依次通过name辅助索引树查询到主键，再通过主键索引树查询到对应一行数据获取sex值

2.单表查询索引执行过程-普通索引+排序或分组概念

一个订单表，包含用户id，商品id，以及下单事件

CREATE TABLE `orderlist` (
  `userid` int(11) NOT NULL,
  `productid` int(11) NOT NULL,
  `date` datetime DEFAULT NULL
) ENGINE=InnoDB

查询用户id=1且按照日期升序排序的结果：

select  * from orderlist where userid=1 order by date;

分析SQL执行计划：

explain select  * from orderlist where userid=1 order by date\G

 

通过explain分析，可以看到，该SQL执行是查询了整张表，效率比较低，对数据查询userid=1之后的所有数据记录，还要按照data字段进行升序排序，Extra出现了fileSort:得到的所有的结果集，对结果集进行文件排序，

出现了该fileSort，SQL语言性能比较差，需要进行优化

给userid和date分别创建索引

create index idx_userid on orderlist(userid);
create index idx_date on orderlist(date);

重复执行SQL，查看执行计划，看有什么不同

 在SQL执行过程中，并没有选择合适的索引，通过强制指定索引：force index(索引名)

 

在使用force强制索引，如果前置使用idx_userid，SQL执行查询在辅助索引上通过关键字找到主键，然后在拿主键id，到主键索引树上去搜索数据，搜索到数据之后，需要根据date进行文件排序，效率是比较低的

给定userid的索引，给定date索引为什么出现filesort和全表扫描，

注：一个SQL查询操作，一次执行使用一个索引，因此选用某一个索引，其他索引不再起作用

继续优化，需要创建userid和date的联合索引，由于userID是where的过滤条件，因此联合索引userID在前，date在后

create index idx_userid_date on orderlist(userid,date);

再来查看SQL执行计划

 

给userid和date创建联合索引后，不会出现fileSort，根据userid=1查询辅助索引树，找到的数据也已经按照date排好序，然后在去主键索引树去查找整行数据就可以啦，效率比较高，

之所以还要使用force index（idx_userid_date）是防止SQL优化器优化索引的时候，因为测试的数据量比较小，有时候使用索引不一定比整表的效率高。

3、多表查询：连接查询索引的执行过程以及优化

使用多表查询连接的时候，MySQL会首先判断那个表小，表小主要指的是数据行数少。小表无论如何都是整表遍历的，是不使用索引的，但是大表就需要用到索引。所以在连接查询时，小表总是需要整表搜索的，建索引是没有用的，大表创建索引能够提高查询效率

小表是决定查询的次数，大表决定查询时间

 explain select * from tb1 inner join tb2 on tb1.id=tb2.id

小表没有用索引，大表用到了索引

 

tb2有3条数据，tb1有8表数据，所以tb2是小表，进行全表扫描，大表是tb1，根据索引进行过滤查询通过执行计划：先查询的是tb2的整表，然后在tb1的表的索引树上查找

 explain select * from tb1 inner join tb2 on tb1.id=tb2.id where tb1.name="li"

tb1表根据where条件过滤后，挑选出符合条件的数据后才比较大小表，tb1是有8条数据，根据检索条件过滤完剩1条数据，tb2是3条数据，此时小表就变成了tb1,tb2就是大表，tb2上的索引就可以用到了

在连接查询中，大小表的角色是不一定的，没有where子句，那么就按照表的行数来决定大小表，如果有where子句，那么就按照条件过滤后的行数来确定大小表

左前缀原则

MySQL中索引可以以一定的顺序引用多例，这种索引称之为联合索引，假如User表中name和city加联合索引就是（name,city）,从而左前缀原则指的是，如果查询的时候条件精确匹配索引的左边连续一列或者几列则可以索引

select * from user where name=XX and city=XX; //命中索引的

select * from user where name=XX ; //命中

select * from user where city=XX ; //不能命中索引

select * from user where city=XX and name=XXX; //命中索引的

 需要注意的是，查询的时候如果两个条件都永生啦，但是顺序不同，比如city =XX and name=XXX，那么现在的SQL执行引擎会自动优化为匹配联合索引的顺序，这样就能命中索引，由于最左侧原则，在创建联合索引时，索引的字段的顺序需要考虑，order by也遵从最左侧原则

索引的设计原则

可以看出，使用索引能提高查询效率，但是给表创建过多的索引，效率反而会降低，因此在设计表索引的时候，需要遵循以下的设计原则：

1、给区分度高的字段创建索引 eg：学号、身份证号

2、给经常需要排序，分组和多表联合操作的字段创建索引

3、经常作为查询条件的字段创建索引

4、索引的数据不宜过多

5、对于多列索引，优先指定最左边的列集

6、删除不再使用或者很少使用的索引