MySQL常见面试题（四）

😀前言
在进行数据库设计和优化的过程中，我们不得不面对多样化的技术和方法来确保我们的系统可以高效、可靠地运行。为了深入了解和掌握这个领域，我们将讨论InnoDB存储引擎的多种索引类型，以及索引的不同方面和分类。我们还将深入探讨为什么通常推荐使用自增列作为主键，以及主键和唯一键之间的区别。最后，我们将提供一系列可用于SQL语句优化的方法。
.
了解这些概念不仅可以帮助我们更好地设计和维护数据库系统，还可以帮助我们识别和解决性能问题，从而确保数据库的高效运行。

🏠个人主页：尘觉主页
在这里插入图片描述

🧑个人简介：大家好，我是尘觉，希望我的文章可以帮助到大家，您的满意是我的动力😉😉

在csdn获奖荣誉: 🏆csdn城市之星2名
⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　💓Java全栈群星计划top前5
⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　🤗 端午大礼包获得者
⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　🥰阿里云专家博主
⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　⁣⁣⁣⁣　😉亚马逊DyamoDB结营

💕欢迎大家：这里是CSDN，我总结知识的地方，欢迎来到我的博客，感谢大家的观看🥰
如果文章有什么需要改进的地方还请大佬不吝赐教先在次感谢啦😊

文章目录

InnoDB中只有B+树索引吗？

InnoDB存储引擎不仅仅有B+树索引，它还支持全文索引、哈希索引。

InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)。使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。我们对这个自适应哈希索引能够干预的地方很少，只能设定是否启用和分区个数。

从MySQL5.6.x开始，InnoDB开始支持全文检索，内部的实现机制就是倒排索引。但是MySQL整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，所以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎。

什么是密集索引和稀疏索引？

密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键。

mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引，innodb存储引擎：有且只有一个密集索引。

所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的普通二级索引。

为什么要用自增列作为主键？

1、如果我们定义了主键(PRIMARY
KEY)，那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放

因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

3、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

4、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE
TABLE来重建表并优化填充页面。