- 秋招将至,整理了数据库优化面试知识,坚持每日学习、打卡、秋招Offer满满哦!
- 题目会持续更新,且保证有质量,期待你的参与,一起打卡,一起进步!
- 本篇文章讲解的角度是由浅入深,因而文章篇幅较长,请大家坚持看完,一定对你们有所帮助！
一: 面试官:谈谈你对Mysql索引的认识
(一): 回答问题的方式
一般回到面试官问题的时候,可以按照五部曲的方式,使用这个逻辑回答面试官问题,Offer跑不了(但是为了更简单的了解本片文章的知识,文章的顺序就不按照这个顺序来编写,大家理解后,可以根据自己的方式来组装回答),即:
1、 xx是什么
2、 xx解决了什么问题/有什么作用
3、 xx是如何使用的
4、 xx在使用过程中遇到了什么问题
5、 在遇到了xx问题后我是如何处理的
(二): 索引是什么
索引是对某一列或多个列的值进行预排序的数据结构,通过它可以提高查询数据的效率。
(三): 索引的分类
聚集索引(Clustered index)
非聚簇索引/普通/辅助索引(Secodary index)
(四): 什么是聚簇索引和非聚簇索引
可能很多人看过一些博客,有些博客将聚簇索引也当做是一种具体索引类型,这个观点是错误的,聚簇索引并不是一种具体索引类型,而是索引类型的一种归类(就像动物划分为脊椎动物和无脊椎动物一样,索引也划分为聚簇索引和非聚簇索引两类)。
术语中"聚簇"的含义是表示将数据行和相邻的键值紧密的存储在一起,所以,定义一种索引是属于聚簇索引类型还是非聚簇索引类型主要看它底层的实现,如果索引和数据存放在一起则说明它是聚簇索引.如果索引和数据是分开存放,则属于非聚簇索引。
看到这里是不是有些人感觉一脸蒙蔽,来人,上图(大家先不用纠结图中标注的意思,后面详细的解释图上的含义,先直观感受一下两者的特点):
看完图片,我们再来看关于两者更详细的定义,相信大家看完后会对它们的理解会更深刻。
1、聚簇索引:
将索引和数据存放在同一个地方,索引结构的叶子节点存放的就是对应的行记录数据,聚簇索引规定了数据在表中的物理存储顺序,因此一个表只能有一个聚簇索引,但是该索引可以是有多个列组成。
2、非聚簇索引:
也叫辅助索引,它是将数据和索引分开存储,索引结构的叶子节点存储的是指向实际数据的地址指针,一个表中可以存在多个非聚簇索引,最多可创建249个(来源百度百科)
3、聚簇索引和非聚簇索引的对比:
(1)、聚簇索引查询效率高,因为叶子节点存储了实际数据,查询到索引就可以查询到数据,非聚簇索引则不行
(2)、因为索引和数据存在一起,减少了磁盘的IO次数,而非聚簇索引在查询到索引后还需要根据索引中的实际数据指针去查询真实的数据
(3)、一个表中只能有一个聚簇索引,因为索引和数据是存放在一起的,一个数据不可能同时存在多个地方,但是可以有多个非聚簇索引
(4)、因为聚簇索引的叶子节点存放了数据,因此相比非聚簇索引会占用更大的空间
4、 Mysql中聚簇索引和主键有什么关联,主键选定有什么规则
官方文档中写到,在Mysql中,聚簇索引其实就是主键索引一种术语(可以理解为专业的名称),具体图如下:
在MySQL中，只用Innodb引擎才采用聚簇索引，其他的存储引擎像MyISAM采用非聚簇索引。主键索引的规则如下:
（1）如果表定义了PK(主键)，则PK就是聚集索引
（2）如果表没有定义PK(主键)，则第一个not NULL unique列(唯一约束)当做聚簇索引
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引,官方描述如下图:
(五): 索引是在哪里实现的
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。
什么是存储引擎层，什么是服务层?要理解这两个概念,我们得先知道服务端对客户端进程发送的请求做了哪些处理,需要经历的流程如下,下面详细介绍每一个流程负责的功能:
1、连接处理模块:
该模块主要是管理客户端的连接,客户端可以通过TCP/IP、命名管道、共享内存、套接字等方式与服务端进行连接,服务端接收到连接后,会专门生成一个线程去处理客户端的请求,当完成客户端的请求后,该线程不会被销毁,而是放入线程池中,从而减少了频繁创建和删除线程的消耗,大大节省了系统资源和提高了效率。
客户端每次发起连接请求时,都会携带用户名、密码等验证信息,如果服务器验证不通过,则会拒绝连接,同时,如果很多客户端同时请求连接,为了避免服务端程序崩溃和提高效率,可以限制最大的连接数量。
2、解析和优化模块
客户端和服务端建立完连接后,服务端会有专门的线程对客户端的请求做处理,此时服务端接受到的是客户端发送的一段文本信息,需要转换成自己可以识别的信息，具体步骤如下:
(1)查询缓存:
为了提高响应效率,Mysql服务端程序会根据客户端请求的信息生成对应的缓存,如果请求的信息符合缓存中的,则直接返回,无需再去与底层进行更多的交互。
但是、Mysql服务器程序并不能像人一样智能,如果两次的请求文本不一样如多了空格、大小写以及每次调用会返回不同的值的函数等情况时,都不会命中缓存。
使用到了缓存,就涉及到对缓存维护,Mysql中的缓存检测程序会监测到缓存涉及的每一张表,如果表中的数据或者结构发生改变,如执行了insert、alter等命令时,那么它会将该表对应的缓存进行失效和删除。因为维护缓存是需要很大的开销,特别是表很多的情况下,所以Mysql8.0时已经将查询缓存这个流程删除。
(2) 语法解析:
如果请求没有命中缓存,则进入到语法解析的步骤,因为服务端程序接收到的是客户端发送过来的文本信息,Mysql服务端程序要从文本中将具体的请求解析出来,如查询什么字段,查询哪一些表等
(3) 查询优化:
经过语法解析步骤后,服务端程序已经知道客户端请求的信息,如请求的表,数据等,但是,此时服务器程序还不会立马根据这些信息去执行.它会解析出来的语句进行一些优化,如:子连接转为关联,内外连接查询等,以达到最大的优化效率,优化的结果就是生成一个执行计划,就是平常通过Explain关键字看到的一个结果。
3、存储引擎模块
经过了连接处理和解析优化俩步骤后,实际上还是没有对实际的数据进行任何的处理,Mysql中,将对数据存储和提取的操作抽取到了一个叫存储引擎的模块中。
在逻辑上,我们看到的是表的数据是一行行的形式,但实际物理层面上,表的数据如何存储、如何读取表的数据、这都是存储引擎需要负责的操作,Mysql中提供了不同的存储引擎,不同的存储引擎存储的数据结构可能不相同,采用的算法也可能不同。
4、总结
为了管理方便,将连接处理/管理、查询缓存、语法解析、查询优化等不涉及到真实数据存取的功能划分为Mysql Server层的功能。
把涉及到真实数据存取的功能划分为存储引擎模块的功能,Mysql Server层通过各个存储引擎提供的API进行访问响应的存储引擎,Mysql通过查询优化生成了执行计划后,通过调用存储引擎提供的API获取到对应的数据返回给客户端即可。
(六): InnoDB和MyISAM存储引擎中聚簇索引和非聚簇索引的区别
既然说不同的存储引擎可能具有不同的索引类型和实现,那么作为Mysql中最常用的两种存储引擎InnoDB和MyISAM,它们的索引类型有哪些区别呢,下面通过具体的图片去更直观的感受一下(**图画的有点丑,大家将就一下,后面会继续提高画图水平....**):
**1、InnoDB引擎的索引特点**
2、MyISAM引擎的索引特点
3、InnoDB引擎和MyISAM引擎索引的对比
4、总结
InnoDB的的辅助索引(Secodary key)的叶子节点存放的是索引字段的键值加主键值。因此，辅助索引(Secodary key)是先通过二级索引查询首先查到记录的主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块,而聚簇索引是直接将索引的键值和数据存储在一起,找到索引就可以找到数据,所以,InnoDB的聚簇索引和辅助索引是有差别的。
而MyISAM的辅助索引(Secodary key)叶子节点存放的还是列值与行号的组合，叶子节点中保存的是指向真实数据的物理地址。所以MYISAM的主键索引和二级索引没有任何区别，主键索引可以当做是一个唯一、非空的索引,而辅助索引的key是可以重复的，MYISAM引擎中没有规定一定要设置主键。
(七): 为什么官方推荐InnoDB存储引擎使用自增的字段作为主键会更好
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增的,需要不断调整数据的物理地址,则每次写入的时候都需要进行重新的排序,效率大大降低,磁盘碎片也会增多。
(八): 聚簇索引的底层实现是什么
通过上文我们知道,索引的底层是一种数据结构,那它到底是哪一种数据结构呢?答案是B+ Tree,下面先来了解下B树和B+树的一些特点(你们心心念念的面试官心里没有"B树"来了…)。
1、二叉树:
每个树节点最多只有两个子树的树结构,它具有以下的特点:
(1)、所有节点最多有不超过2个子节点
(2)、最顶层的第一个节点称为根,左子树的所有节点的键值都小于根的键值,右子树上的所有节点的键值都大于根的键值
(3)、子树的键值是有序的,顺序从左到右增大。
2、二叉树的缺点:
因为二叉树只是做了结构的定义，但是没有考虑深度的情况,这样的结构可能存在多种深度的问题,从而导致查询效率降低,因而引入的平衡二叉树。
3、平衡二叉树(AVL Tree)特点:
(1)、满足二叉树的所有特点
(2)、任何节点的两个子树的高度最大差为1(高度即:从当前节点距离根节点的层数)
3、B-Tree:
(一) 定义:
B是Balance的缩写,B-tree就是B树,它是一种平衡多叉树,每个节点到叶子节点的高度都是相同的,这样保障了它的查询是稳定的。
相比于二叉树(即每个节点最多只能有两个子树),B-Tree可以利用多个分支,减少查询数据时遍历的节点数,从而提高效率。
(二) 特点:
在讲解B-tree特点前,先了解下从磁盘读取数据的一个知识: 系统从磁盘中读取数据到内存是以磁盘快(Block)为单位的,属于同一个磁盘快的数据会被一次性读取出来,而不是要什么数据就读什么数据。
InnoDB的存储引擎中存在页(Page)的概念,页是其磁盘管理的最小单位,且Mysql默认的存储引擎InnoDB的默认页大小是16KB,具体的可以通过修改innodb_page_size参数进行设置(不推荐修改)。
系统中的磁盘快的存储空间并没有InnoDB默认的16KB这么大,因此InnoDB在磁盘中读取数据时往往需要读取若干个连续的磁盘快的数据才能达到InnoDB默认读取页的大小,在查询的时候,如果页中的读取索引数据能够定位到具体数据的位置,这将可以减少磁盘I/O的次数,从而提升查询效率。
3、B+Tree:
(一) 定义: B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
(一) 特点:
B树的每个节点存储了key和data值(data存储的是数据记录除key以外的数据),但因为页的大小是固定的,因此当data数据比较大的时候,每页读取的key数量就会变小，这样就需要增加磁盘的I/O次数,从而影响效率。
但是在B+树中,除了叶子节点,其他的节点都是值存储key的信息,data的数据都是按照顺序存储在叶子节点上,这样B+树上的空间就会比较小,每一页可以存储更多的key值,从而减少磁盘的I/O,提高查询的效率。
(二) 和B-tree的区别:
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都顺序存放在叶子节点中,每次查询数据都需要都叶子节点才能获取,查询次数稳定
(九): 数据库为什么使用B+树而不是B树
1、B树只适合随机检索,而B+树同时支持随机检索和顺序检索、范围检索。
2、B+树空间利用率更高、可减少I/O次数,磁盘读写代价更低(因为索引文件较大,一般不直接存储在内存中,一般是以索引文件的形式存储在磁盘上,这样,索引的查找就存在磁盘I/O,B+树的内部节点没有指向具体信息的指针,只是作为索引使用,其内部节点比B树要小,快能够容纳的结点关键数量更多,一次性读入内存中的关键字也更多,相对的I/O次数也减少了,而I/O读写次数是影响索引检索效率的最大因素)。
3、B+树的查询效率更加稳定。B树搜索可能会在非叶子结点结束,越靠近根节点记录查询时间越短,只要找到关键字即可确定记录的存在,其性能相当于在关键字全集内做一次二分查找。而B+树任何关键字的查询都必须从根节点到叶子结点,所有的关键字的查询路径长度一样,导致每一个关键字的查询效率相当。
4、B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历,而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
5、增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。
(十): 为什么说B+树比B树更适合实际应用中作为操作系统的文件索引和数据库索引？
(1)B+树的磁盘读写代价更低
非叶子节点包含的信息更少，如果把同一节点的所有信息放在一个磁盘块中，则可以比B树放入更多的关键码。一次读入内存当中(读一个块)就能读入更多的关键码，所以降低了磁盘I/O总数。
(2)查询效率更加稳定
对任何关键字的查找都必须从根节点走到叶子节点，路径长度相同，所以对每条数据的查询效率相当。
(3)B树在提高磁盘I/O性能的同时并没有解决元素遍历效率低下的问题,而B+树因为叶子节点有链指针存在，所以遍历叶子节点即可以实现对整棵树的遍历。而在数据库中基于范围的查询是非常频繁的，B+树就能更好的支持
二: Hash索引和B+Tree索引的区别
一:什么是哈希索引
定义:
基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。
哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。更加具体的描述看下图(看图能猜出是哪个老师的也肯定是lsp了…)。
二: 相关概念
桶:
是一个能存储一条或多条记录的存储容器,一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到Hash冲突时，会在桶中进行键值的查找。
Hash冲突：
如果不同的key计算出相同的桶位置,不同的key会映射到同一个桶中，这时就会产生Hash冲突，如果Hash冲突的量很大，就会影响读取性能。
三: 底层实现
1、Hash索引底层就是Hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后再进行回表查询获取到实际的数据。
2、B+树实现是多路平衡查找树,每次查询都是从根节点出发,查询到叶子节点方可以获得所查的键值,再根据查询判断是否需要回表查询数据
四: 差异
1、hash索引进行等值查询更快(一般情况下,如果索引列重复值很多的话,会产生Hash冲突,效率不一定比B+树高)，但是却无法进行范围查询,B+树可以。
2、hash索引经过hash函数建立索引后,索引的顺序和原顺序无法保持一致,不支持范围查询,而B+树所有节点遵循: 左节点小于父子节点,右节点大于父子节点,天然支持范围查询。
3、hash索引不支持排序,因为hash索引指向的数据是无序的,因此无法起到排序的作用,而B+树可以,原理同上。
4、hash不支持模糊查询和索引的最左前缀匹配,因为Hash函数的不可预测,B+树可以使用支持左模糊模糊查询
5、Hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚集索引、覆盖索引等),直接通过索引完成查询。
6、hash索引性能不可预测,当某个键值存在大量重复时,发生hash碰撞,性能较差,稳定性不好,B+树查询比较稳定,都是从根节点到叶子结点。
三: Mysql最新版支持的存储引擎及特点
Mysql8.0版本支持10存储引擎,具体如下:
(一)、InnoDB:
是Mysql8.0中默认的存储引擎。InnoDB是 MySQL 的事务安全（符合 ACID）存储引擎，具有提交、回滚和崩溃恢复功能来保护用户数据。
InnoDB行级锁定（不升级为更粗粒度的锁定）和 Oracle 风格的一致非锁定读取提高了多用户并发性和性能。
InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了维护数据完整性， InnoDB还支持FOREIGN KEY引用完整性约束。
(二) 、MyISAM
使用这种存储引擎的表占用的空间很小,表级别的锁(表锁)限制了它的读/写负载的性能,它通常用于 Web 和数据仓库配置中的只读或以读取为主的工作场景中。
(三) 、Memory
这种存储引擎将所有的数据都存储在内存中,以便在非关键数据的环境中需要快速查找场景中进行快速访问,这个存储引擎在之前也被称为HEAP引擎。
这种引擎的使用正在逐渐减少,因为在InnoDB引擎中通过缓冲池内存区提供了一种通用且持久的方式来将大部分或者所有数据保存在内存中,且NDB引擎(也叫NDBCLUSTER)提高了为庞大的分布式数据集提供快速的键值查找。
(四) 、CSV
CSV引擎的表格其实是具有逗号分隔值的文本文件。CSV引擎中的表允许您以 CSV 格式导入或转储数据以便与读写相同格式的脚本和应用程序交换数据。
由于 CSV 表没有索引，因此您通常可以让InnoDB引擎在正常操作期间将数据保存在表中，并且仅在导入或导出阶段使用 CSV 引擎的表。
(五) 、Archive
这种存储引擎下表是紧凑的、没有索引的,主要用于于存储和检索大量很少引用的历史、存档或安全审计信息。
(六) 、Blackhole
Blackhole 存储引擎接收但不存储数据,与 Unix/dev/null设备类似。查询总是返回一个空集。这种引擎模式下的表可用于配置复制，其中 DML 语句被发送到副本服务器，但源服务器不保留自己的数据副本。
(七) 、NDB(也叫作NDBCLUSTER)
这种集群数据库引擎特别适用于要求正常运行时间高程度保障的和高可用性的应用程序。
(八) 、Merge
这种存储引擎可以让MySQL DBA 或开发人员能够对一系列相同的MyISAM表进行逻辑分组并将它们作为一个对象引用。适用于 VLDB 环境，例如数据仓库。
(九) 、Federated
这种存储引擎提供了链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库的能力。非常适合分布式或数据集市环境。
(十) 、Example
这个引擎作为 MySQL 源代码中的一个例子，描述了如何开始编写新的存储引擎。它可能是开发人员感兴趣的。存储引擎是一个什么都不做的 “存根”。您可以指定表使用这种存储引擎，但不能在其中存储或从中检索数据。
总结:您不需要对整个服务器或架构限制使用相同的存储引擎。您可以为任何表指定特定的存储引擎。例如，一个应用程序中的表可能主要使用InnoDB类型，然后定义一个CSV引擎的表用于将数据导出到电子表格，而另外定义一些 MEMORY引擎类型的表用于临时工作区,简单来说,就是可以根据自己的需求灵活定义表的存储引擎。
四: 如何选择一个存储引擎,常用的存储引擎特点对比
通过上文我们Mysql有哪些存储引擎,下面就来讲解一下常用的存储引擎的特点:
选择一个表的存储引擎时,大家可以参考上面各个存储引擎特点然后根据自己的具体业务场景去选择,下面我主要总结一些一些需要注意的细节。
1、MyISAM 引擎: 压缩 MyISAM 表仅在使用压缩行格式时才受支持。使用 MyISAM 压缩行格式的表是只读的。
2、各个引擎的Replication support是在服务器程序中实现，而不是在存储引擎中。
3、各个引擎的Encrypted data(数据加密)是在服务器中实现。
4、在 MySQL 5.7 及更高版本中，支持静态数据加密,都是在服务器中实现的。
5、MySQL Cluster NDB 7.3 及更高版本支持外键。
6、MySQL 5.6 及更高版本支持 FULLTEXT 索引。
7、MySQL 5.7 及更高版本支持地理空间索引。
8、InnoDB存储引擎在内部利用哈希索引来实现其自适应哈希索引功能。
五: 主键(聚簇索引)和唯一约束和唯一索引有什么关联
(一):主键(聚簇索引)和唯一索引相同点：
创建主键的时候会创建一个唯一索引
(二):主键(聚簇索引)和唯一索引差异：
主键本质上是一种约束，唯一索引是一个索引,本质不一样
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
主键是一种约束，目的是对这个表的某一列进行限制；唯一索引是一种索引，索引是数据库表的一个冗余结构，目的是为了更好的查询；
主键列不允许为空值，而唯一性索引列允许空值；
一个表最多只能一个主键，但是可以包含多个唯一索引；
主键可以被其他表引用为外键，而唯一索引不能。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
(三): 主键和唯一约束的比较
唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束。
指定列上都不允许有相同的值， 允许空(NULL)、但是主键不行。
主键相当于唯一索引+唯一约束+非空约束
六: MySQL常见的约束种类有哪些
定义:约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
常见的MySQL约束: 主键约束、外键约束、非空约束、检查约束(Check,如bentwen and ，大于、小于、等于、不等于）、唯一约束、默认约束。
七: 索引设计原则
虽然索引能够有效的帮我们提高查询效率,但是它也有弊端,我们对表进行数据库插入、删除、修改操作时需要重新维护索引,索引还需要占用磁盘空间,因此,索引并不是越多越好,需要有合理的设计才能达到最大的帮助。
1、索引是否常出现在where查询条件或者关联查询中
2、如果对字符串进行索引,应该制定一个索引长度,这样能够节省大量的索引空间获取前缀截取的长度: select count(*)/count(distinct left(password,prefixLen)) from 表名;通过从调整prefixLen的值（从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了
3、不能设计过多索引,因为索引需要额外磁盘空间,降低写操作的性能。
4、最左前缀匹配原则,组合索引的重要原则,mysql中会一直向右匹配直到遇到范围查询(<,>,between,like)就停止匹配
5、更新频繁的字段不适合创建索引
6、若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
7、尽量拓展索引,在原来的基础上修改,而不是新增
8、查询很少,重复值比较多的列不需要创建索引
9、定义成image、text、bit的数据类型的列不需要创建索引
八: 创建索引的方式
1、在创建表的时候创建
create table xx( key keyname(列名1、2) FULLTEXT KEY(列名) UNIQUE KEY(列名) )
2、使用alter table 添加(可以添加普通、唯一、主键索引)
alter table 表名 add index indexname(字段名)
3、使用create index命令创建普通索引和唯一索引,不能创建主键索引
create index indexname on 表名 (字段名)
九: 总结和下篇预告
相信经过这篇长达一万字的知识探索,大家对索引会有了更清楚的认识,知其然知其所以然,在面试的时候就不会慌面试管的提问。“纸上得来终觉浅,绝知此事要躬行”,看完了文章并不疑问着你已经掌握这个知识,更多的要自己去实践才能理解,希望大家能温故知新。
下篇的面试题目主要是关于数据库优化方面的知识,包括回表、覆盖索引、Explain关键字解析等。
欢迎大家关注,如果觉得文章对你有帮助,不要忘记一键三连哦,你的支持是我创作更加优质文章的动力,希望大家都能够早日拿到心仪的Offer,有任何面试问题可以私信我,欢迎大家投稿面试题目哦!
十: 参考资料
1、《高性能MySQL》
2、《Mysql是怎样运行的》
