面试题知识点笔记-MySQL索引

	innodb索引	MyISAM索引
叶子节点内容	指向id，存放主键值	指向指针（每一行的物理地址）
索引存放位置	和数据放在一个文件夹，数据文件本身就是主索引文件	与数据分别存放
	聚簇索引直接指向数据，非聚簇索引指向数据主键id

myisam：

表中如果没有创建索引，那么还会创建B+树吗?

没有主键mysql也会隐式创建一个rowID，但是无法使用这个隐式主键，会创建b+树

说一下B+树索引实现原理(数据结构)

b树	b+树
每个节点存放主键和数据	叶子节点存放数据，其他节点存放ey

B+树相比于B树的优点

由于B+树在内部节点上不好含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子几点上关联的数据也具有更好的缓存命中率；
B+树的叶子结点都是相链的，因此对整棵树的访问只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
总结：B+树的I/O次数更少，磁盘读写代价更低，时间更稳定，范围搜索更好

聚簇索引与非聚簇索引b+树实现有什么区别?

聚簇索引	非聚簇索引
主键作为索引值索引和数据保存在同一个B+树中，速度更快页内的记录按照主键的大小顺序排成一个单向链表。	其他(单值索引、联合索引等) 新索引要新建b+树
页和页之间根据页中记录的主键的大小顺序排成一个双向链表。（排序查找和区间范围查找更快）非叶子节点存储的是记录的主键+页号。·叶子节点存储的是完整的用户记录。（数据紧密相连，减少IO操作）	非叶子节点存储的是数据的列的值和页号，是聚簇索引中的主键id定位
INNODB支持聚簇索引，myisam不支持； MySQL表只能有一个聚簇索引，必须自增的id作为主键，如果没有innodb会隐式定义一个自增id作为主键按照排序算法插入，所以主键尽量不更新不移动，避免页分裂	需要二次查询，最后还是要在聚簇索引的b+树查询

说一下B+树中聚簇索引的查找(匹配)逻辑

从根节点，比对主键大小,根据规则跳转到对应页面，直到叶子节点找到数据或者返回i空指针。

比如上图中主键是13，小于320，就会来到页30中查询，13大于12但是小于209就来到页9，页9是叶子节点，找不到主键13的数据，返回空指针；

说一下B+树中非聚簇索引的查找(匹配)逻辑

聚簇索引主键不能重复，非聚簇索引里非主键如果没有加上约束可能会重复（如员工里按性别查询，可能有很多男员工）；
指定大小比对时需要指定匹配规则如最左匹配，比如两个列（age和name）作为索引，必须指定字符串匹配规则，实际上是比对字符串对应的ascll码，查找规则和聚簇索引差不多

平衡二叉树AVL，红黑树，B树和B+树的区别是什么?都有哪些应用场景?

AVL的出现为了解决单向链表退化情况：

因为每个节点只有一个数据，那么大数据情况下树高度会很高，而且自旋成本会越来越大。

红黑树：

两次旋转达到平衡，应用于hashmap存储

B和b+树区别上面已经提过，在MySQL5.7之前是b树，后面采用b+树。

一个b+树中大概能存放多少条索引记录?

一般三层的b+树已经能存储千万级别数据了

一般MySQL默认page = 16k（四个4k磁盘块），根节点只存键值（主键+指针+其它info，一般占10Byte）根节点可以存1600条既有1600个子节点，存满了再存下一层内容，在第三层，每一页能存的数据除了主键指针还有数据，按1k计算，所以总的数据量就是16*1600*1600

使用B+树存储的索引crud执行效率如何?

c：不改变树结构都是logn*n的时间复杂度，但是若是自旋了就会改变树结构，一般情况下不会自旋除非数据填满了。

r：不会自旋，都是logn*n

u：不改id就是和r一样

d：若删除数据后出现空页，可能会涉及自旋，更耗时间

什么是自适应哈希索引?

INNODB内部会自动实现这个功能，既在注意到某些索引值被频繁使用后再基于这些索引创建一个哈希索引来快速查找

什么是2-3树 2-3-4树?

是多叉树的一种

为什么官方建议使用自增长主键作为索引?(说一下自增主键和字符串类型主键的区别和影响)

自增主键能维持底层数据顺序写入
读取可以用二分查找定位
支持范围查找，且自带排序

使用int自增主键后最大id是10，删除id 10和9再添加一条记录，最后添加的id是几?

在MySQL5和8中中如果不重启MySQL就是延续删除之前最大id的加上去（11），如果重启就是从最小的id开始自增（9）；auto-increament参数

索引的优缺点是什么?

聚簇索引：范围查找自带顺序，顺序读写
非聚簇索引：条件查询避免全表查询scan，覆盖索引不需要回表操作
空间代价：每个新建立的索引都要建立b+树，每个节点都是16KB；
时间代价：增删改都要修改b+树索引，每一颗树都可能被频繁修改，（记录移位、页面分裂、页面回收），一般只有频繁使用的字段才建立索引

使用索引一定能提升效率吗?

索引太多时候频繁修改的操作会降低效率，读操作才会提升效率；
数据量小的时候没必要建立索引
唯一索引可能不会提高效率，比如根据手机号建立索引，在写入时候会先判断是否存在再写入，两次操作比起直接写入更消耗时间资源；

如果是大段文本内容，如何创建(优化)索引?

可以分表存储，然后创建索引，开头必须精确匹配，后面可以模糊匹配
可以使用es为大文本创建索引

什么是聚簇索引?

一个b+树，非叶子节点存放主键id，叶子节点存放id和数据

一个表中可以有多个(非)聚簇索引吗?

聚簇索引不行，索引根据主键创建，外键和普通列没什么区别，非聚簇索引可以用别的列或者组合列创建多个

CRUD时聚簇索引与非聚簇索引的区别是什么?

聚簇索引	非聚簇索引
插入操作更耗费资源，要先判断是否存在再插入	如果设置成唯一值约束也是和聚簇索引一样要先判断
排序查找范围查找效率高	很多时候要二次查找，先找一个范围的id再返回聚簇索引中查找

以下两个查询分别是覆盖索引和回表操作，如果是覆盖索引就不需要返回聚簇索引再查询一遍了，否则就要回表操作。

select name from user where name=
select name,age from user where name=

非聚簇索引为什么不存数据地址值而存储主键?

因为最终要返回聚簇索引查找，为什么不直接放地址精准定位呢？因为每一页的大小不一样，如果在页内删除了数据又写入了数据则物理地址可能会变，而MySQL自动会压缩数据可能也会改变地址，而分页操作也可能会改变地址，比如id从200变到400但存的物理地址还是原来那个就不对了。

什么是回表操作?

在非聚簇索引中查询的数据还不足以满足我们需要查找的条件，还需要返回聚簇索引的表中查找，这就是回表：

select * from user where age >20

什么是覆盖索引?

where后面的字段包含select之后的字段：

select age from user where ige >20,

非聚集索引一定回表查询吗?

不一定，如覆盖索引；

为什么要回表查询?直接存储数据不可以吗?

为了控制非聚簇索引的大小，要不然修改时候每一颗树都要修改，数据都存在主表中能减少资源，所以这就是为什么非聚簇索引叫做辅助索引。

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了?

MySQL在没有指定主键时候会隐式创建rowID辅助回表查询；

什么是联合索引，组合索引，复合索引?

都属于非聚簇索引的一种，比如可以指定某个字段或者某几个字段重新生成一颗b+树

如下图页72中2和e就是两个不同字段，c2和c3联合索引时，按照建立的先后顺序先按c2排序，c2相同再c3排序

复合索引创建时字段顺序不一样使用效果一样吗？

如上所示，不一样。

什么是唯一索引?

约束字段不重复：

CREATE TABLE customer
id INT UNSIGNED AUTO INCREMENT.
customer nO VARCHAR(200).
customer name VARCHAR(200)
PRIMARY KEY(id)，-- 主键索引:列设定为主键后会自动建立索引，唯一且不能为空。UNIQUE INDEX ukho(customer_no)，-- 唯一索引:索引列值必须唯一，允许有NULL值，且NULL可能会出现
多次。KEY idx_name(customer_name)，-- 普通索引:既不是主键，列值也不需要唯一，单纯的为了提高査询速度而创建。
KEY idx_no_name(customer_no,customer_name)-- 复合索引:即一个索引包含多个列。

唯一索引是否影响性能?

如阿里规范：

例如不同的客户同时insert和select，就可能会有脏数据产生，此时就可以把select和insert写成事务，并且保证具有较高的执行级别，此时因为锁住和并发就可能更耗性能，还不如直接在数据库层面加上唯一索引

什么时候使用唯一索引?

只要业务上有唯一特性的字段，如手机号身份证和家庭地址等

什么时候适合创建索引，什么时候不适合创建索引?
适合创建索引

频繁作为where条件语句查询字段
·关联字段需要建立索引
排序字段可以建立索引
分组字段可以建立索引(因为分组前提是排序)
统计字段可以建立索引(如.count(),max())

不适合创建索引

频繁更新的字段不适合建立索引
·where，分组，排序中用不到的字段不必要建立索引。
可以确定表数据非常少不需要建立索引
参与mysql函数计算的列不适合建索引

什么是索引下推?

没开启的话会进行两次回表，根据筛选条件在索引树中筛选第一个条件获得结果集后回表操作

进行其他条件筛选后再次回表查询

有哪些情况会导致索引失效?

计算、函数导致索引失效：

--显示查询分析
EXPLAIN SELECT * FRoM emp WHERE emp.name LIKE 'abc%';
EXPLAIN SELECT *FROM emp WHERE LEFT(emp.name,3)='abc';--索引失效

模糊匹配以%开头会导致索引失效：

EXPLAIN SELECT * FROM emp WHERE name LIKE
'%ab%';--索引失效

IS NOT NULL 失效和 IS NULL可能导致索引失效：


EXPLAIN SELECT * FROM emp WHERE emp.name Is NULL;
EXPLAIN SELECT *FROM emp WHERE emp,name IS NOTLNULL; --索引失效

复合索引如果第一个字段没有被使用到也可能失效

如果scan全扫描比走索引块，优化器会不走索引

为什么LIKE以%开头索引会失效?

name 创建索引
select * from user where name like '%明
type=all，不走索引，全表扫描

select name from user where name like '%明
覆盖索引情况下会type=index，在新的非聚簇索引b+树查找，再回表查询，如果覆盖索引没有生效会直接type=all（取决于查询优化器）

一个表有多个索引的时候，能否手动选择使用哪个索引?

查询优化器会自动选择最优情况的索引来执行，我们无法干预，只能自己了解其规则并合理配置索引

如何查看一个表的索引?

show index from t_emp;// 显示表上的索引
explain show index from t_emp;// 显示可能会用到的索引及最终使用的索引

创建复合索引后explain，可以看见最终采用了单值索引：

能否查看到索引选择的逻辑?是否使用过optimizer trace?

set session optimizer_trace="enabled=on",end_markers_in_json=oh;
SELECT *FROM information_schema.OPTIMIZER_TRACE;
set session optimizer_trace="enabled=off";

json文件细节如下，根据cost的值来选择索引