MySQL面试篇章——MySQL索引

MySQL 索引

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。

由于索引也是需要存储成索引文件的，因此对索引的使用也会涉及到磁盘I/O操作。如果索引创建过多，使用不当，会造成SQL查询时，进行大量无用的磁盘I/O操作，降低了SQL的查询效率，适得其反，因此需要掌握良好的索引创建原则！

索引分类

索引是创建在表上的，是对数据库表中的一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度！

物理上（聚集索引 & 非聚集索引）/ 逻辑上（…）

索引的优缺点：

优点：提高查询效率

缺点：索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O会造成CPU负荷太重

• 普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引（创建新表 & 已创建表，数量是不限的，一张表的一次SQL查询只能用一个索引 where a = 1 and b = ‘M’）
• 唯一性索引：使用 UNIQUE 修饰的字段，值不能够重复，主键索引就隶属于唯一性索引
• 主键索引：使用 PRIMARY KEY 修饰的字段会自动创建索引（MyISAM、InnoDB）
• 单列索引：在一个字段上创建索引
• 多列索引：在表的多个字段上创建索引（uid + cid，多列索引必须使用到第一列，才能够用到多列索引，否则索引用不上）
• 全文索引：使用 FULLTEXT 参数可以设置全文索引，只支持 CHAR、VARCHAR、TEXT 类型的字段上，常用于数据量较大的字符串类型上，可以提高查询速度（线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高效的搜索 elesticsearch 简称 es；C++开源的搜索引擎；搜狗的workflow）

索引创建和删除

• 创建表的时候指定索引字段

CREATE TABLE index1(id INT,name VARCHAR(20),sex ENUM('male', 'female'),INDEX(id, name),INDEX `index name` (sex) # 没有名字，会默认会生成# show create table 进行查看创建SQL语句
)# Create Table: CREATE TABLE `student` (
#  `uid` int unsigned NOT NULL AUTO_INCREMENT,
#  `name` varchar(50) NOT NULL,
#  `age` tinyint unsigned NOT NULL,
#  `sex` enum('M','W') NOT NULL,
#  PRIMARY KEY (`uid`),
#  KEY `nameidx` (`name`)
#) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 #COLLATE=utf8mb4_0900_ai_ci

• 在已经创建的表上添加索引

CREATE [UNIQUE] INDEX 索引名 ON 表明 (属性名 (length) [ASC | DESC]);# create index nameidx on student(name); // 默认btree索引# 添加 hash 索引
# create index ageidx on studetn(age) using hash;

• 删除索引

DROP INDEX 索引名 ON 表名;

• 查看所有索引

show indexes from 表明;

1、经常作为 where 条件过滤的字段考虑添加索引

2、字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度 key_len 过长

3、索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了（索引失效）

索引的执行过程

explain 查看执行计划

使用explain查看SQL的执行计划，分析索引的执行过程，mysql的user权限示例如下：

可以看到使用了主键索引，共扫描了1行，Using index 表示直接从索引树上查询到结果，不需要回表

• 由于Host和User设立了联合索引，也就是多列索引，而多列索引只有第一列的索引使用了，才能进行索引查询，不然不会进行索引查询

explain 结果字段分析

• select_type

  simple：表示不需要union操作或者不包含子查询的简单select语句。有连接查询时，外层的查询为simple且只有一个

  primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary且只有一个

  union：union连接的两个select查询，除了第一个表外，第二个以后的表的select_type都是union

  union result：包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为null

• table

  显示查询的表明；

  如果不涉及对数据库的操作，这里就显示null

  如果显示为尖括号就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生的

  如果是尖括号括起来<union M, N>也是一个临时表，表示这个结果来自于union查询的id为M，N的结果集

• type

  const：使用唯一索引或者主键，返回记录一定是1行已经的等值where条件时，通常type就是const

  ref：常见于辅助索引的等值查找，或者多列主键，唯一索引中，使用第一个列之外的列作为等值查找会出现；返回数据不唯一的等值查找也会出现

  range：索引范围扫描，常见于使用<，>，is null，between，in，like等运算符的查询中

  index：索引全表扫描，把索引从头到尾扫一遍；常见于使用索引列就可以处理不需要读取数据文件的查询，可以使用索引排序或者分组的查询

  all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录

• ref

  如果使用常数等值查询，这里显示const

  如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段

• Extra

  • using filesort：排序时无法用到索引，常见于 order by 和 group by 语句中
  • using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据

索引的底层实现原理

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘块（对应索引树的节点），索引树越低，越”矮胖“，磁盘I/O次数就越少

MySQL支持两种索引，一种是B-树索引，一种是哈希索引，两者在数据查询的效率是非常高的

主要对于InnoDB存储引擎进行讨论，基于B-树的索引结构（MySQL实际采用的是B+树结构）

B-树

B-树是一种m阶平衡树（一般来说m=300~500），叶子节点都在同一层，由于每一个节点存储的数据量比较大，索引整个B-树的层数是比较低的，基本上不超时三层。

二分查找：时间复杂度O(logn)

由于磁盘的读取也是按block块操作的（内存是按page页面操作的），因此B-树的节点大小一般设置为和磁盘块大小一致，这样一个B-树节点，就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引的时候，磁盘I/O的操作次数是最少的（MySQL的读写效率，主要集中在磁盘I/O上）

select * from student where uid = 5（uid添加了索引）;

uid 有索引 ==> 存储引擎 ==> kernel（操作系统） > 磁盘I/O（读索引文件）> 内存上 ==> 用索引的数据构建B-树加速搜索

一次磁盘I/O读取的磁盘块的内容，刚好存储在B树的一个节点中

因为非叶子节点都有左右子树，所以指针域 = 数据域 + 1

假设有2000W条数据，用AVL存储2000W条数据，构建下来有25层（计算器以log10为底，所以得这么除）

如果 m = 500，最多三层，也就是最多花费三次的磁盘I/O，大大减少了速率

扩展：B-树的缺点

• 每个节点中有key，也有data，但是每一个节点的存储空间是有限的，如果data数据较大时会导致每个节点能存储的key的数据很小
• 当存储的数据量很大时同样会导致B-树的高度较大，磁盘I/O次数花费增大，导致效率降低

面试考点：为什么MySQL（MyISAM和InnoDB）索引底层采用B+树，而不是B-树？

1、索引 + 数据内容分散在不同的节点上，离根节点近的，搜索就快；离根节点远的，搜索就慢！

2、每一个非叶子节点上，不仅仅要存储索引（key），还要存储索引值所在的那一行的data数据。一个节点所能存放的索引key的个数，比只存储key值的节点的个数要少得多。（例如，索引key要存4个字节，对应data数据也要4个字节，那就是8个字节，每一块数据，但是如果只存索引key，那就可以存两个，数据最后去叶子节点上找）

3、这棵树不方便做范围搜索，整表遍历看起来也不方便

例如：where age > 11 and age < 18，相当于17的左右子树都要去查，然后12的左右子树也要去查。很不方便（看上图）

因此由于以上的三个原因 ==> MySQL采用B+树来构建索引树

B+树

一句话：B+树最后其实就是一个循环链表，串起来了，所以说最后的查询就是在循环链表中进行查询

特点：

• 每一个非叶子几点，只存放索引key，不存放数据data，好处就是一个节点存放的索引key更多，B+树在理论上来说，层数会更低一些，搜索的效率会更好一些
• 叶子节点上存储了所有的索引值（数据data），搜索每一个索引对应的值data，都需要在叶子节点上，这样子每一行记录搜索的时间是平均的
• 叶子节点被串在一个链表当中，形成一个有序的链表，如果要进行索引树的搜索&整表搜索，直接遍历叶子节点的有序链表即可！或者做范围查询的时候，直接遍历叶子节点的有序链表即可

面试考点：B-树和B+树在存储结构上有什么不同？

1、B-树的非叶子节点，存了索引key和对应的数据地址，而B+树的非叶子节点只存了索引key，不存数据data，因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询的会快一些

2、B-树由于每个节点都存储了索引key和对应的数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；而B+树所有的数据都存储在叶子节点上，因此在B+树上搜索索引key，找到对应数据的时间是比较平均的，没有快慢之分

3、在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有的叶子节点被连结成了有序链表结构，因此做整表遍历和区间查找是非常容易的

哈希索引

哈希索引由哈希表实现的，哈希表对数据并不排序，因此不适合做区间查找，效率非常低，需要搜索整个哈希表结构

时间复杂度O（1），链式哈希表

优点：搜索的效率更好、磁盘I/O花费要少

hash(name) = hashkey % bucket_num （哈希冲突）

哈希表中的元素没有任何顺序可言！只能进行等值比较

select * from student where name = 'zhangsan';select * from student where name like 'zhang%'; error

• 范围搜索，前缀搜索，order by 排序这些操作，哈希索引都不适合！！！
• 它没办法处理磁盘上的数据，加载到内存上构建高效的搜索数据结构，因为它没有办法减少磁盘I/O的次数

聚集和非聚集索引

MyISAM（*.MYD，*.MYI）

select * from student where name = 'zhangsan';

没有索引：直接会在整张表中进行全查（整表搜索）

有索引：进行*.MYI的索引文件的查询，生成B树，然后进行二分查找

主键索引

• MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址（通过这个地址去查找数据data）。

• 即主键对应的data是数据的地址（具体的数据都需要去寻址，在一个data数据表中）

辅助索引（二级索引）

• 在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯 一的，而辅助索引的key可以重复，如果给其它字段创建辅助索引，结构图如下：

总结：MyISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据 没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储，例如一个user 表，会在磁盘上存储三个文件 user.frm（表结构文件） user.MYD（表的数据文件） user.MYI（表的索引文件）。

MyISAM的索引方式也叫做非聚集索引

InnoDB（*.IBD）

• InnoDB存储引擎的索引关键字和数据是存放在一起的

select * from student where name = 'zhangsan';

没有索引：会自动生成B树

有索引：会根据索引数据生成一张新的B树

主键索引

• uid 是主键
• 主键索引树：

• 每个索引下面对应的是对应的数据（例如：15 => 34 Bob）

辅助索引（二级索引）

• InnoDB的辅助索引，叶子节点上存放的是索引关键字和对应的主键（即如果要获取数据，需要通过主键进行回表查询主键索引树，进行获取到数据）

select * from student where name = 'Alice';

1、先搜索name的二级索引树，找到Alice对应的主键uid=18

2、再拿uid=18回表在书简索引树搜索uid那一行记录

问题：select * from student where age = 20 order by name；如果只给age添加索引，行不行？还有什么没有考虑到？

不行，如果只有age键索引，会有using filesort！，因此需要创建age、name的多列索引

先按照age进行排序，再按照name进行排序；age相同，按name进行排序

总结：辅助索引的B+树，先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。从索引树上可以看到，InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘存储上，例如创建一个user 表，在磁盘上只存储两种文件，user.frm（存储表的结构），user.ibd（存储索引和数据）。

InnoDB的索引树叶节点包含了完整的数据记录，这种索引叫做聚集索引。

两者的区别：MyISAM比InnoDB多了磁盘I/O的使用，它需要读取*.MYI文件获取索引数据，进行生成B树（相当于多了一部I/O操作，两个文件）

自适应哈希索引

InnoDB存储引擎监测到同样的二级索引不断被使用；那么它会根据这个二级索引，在内存上根据二级索引树（B+树）上的二级索引值，在内存上构建一个哈希索引，来进行加索引搜索

• 自适应哈希索引本身的数据维护也是要耗费性能的，并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能！我们需要根据参数指标，来具体分析是否打开或者关闭自适应哈希索引

show engine innodb status\G1、RW-latch等待的线程数量（自适应哈希索引默认分配了8个分区），同一个分区等待的线程数量过多
2、走自适应哈希索引的频率（低）和二级索引树搜索的频率（高）

面试考点：SQL和索引的优化问题

通过explain分析SQL

项目 ==> 业务 ==> 千条、万条SQL

流程：从什么地方能够获取哪些运行时间长，耗性能的SQL，然后再通过explain去分析它