MySQL面试题

李建坤坤

已于 2023-02-07 20:54:04 修改

阅读量924

点赞数

分类专栏：数据库文章标签： mysql 数据库

于 2022-12-30 18:56:29 首次发布

本文链接：https://blog.csdn.net/m0_48038376/article/details/128496801

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

索引是什么？

在mysql中，索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成
可以用来快速查询数据表中有某一特定值的记录。
通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列即可。

索引的解读

在MySQL的中，索引在大数据量查询的情况下必须用到索引，索引类似于目录。
如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止。
使用索引后会将磁盘块以树桩结构保存，查询数据时会大大降低磁盘块的访问数量。
想象一下，每条数据的查询都是一个io扫描的话，假设一张表有一百万条数据，你要找的数据刚好就是最后一条，那么就要进行一百万次io，这样就使数据库的性能大幅度降低了，索引就是解决这样的问题的。

哪些些情况需要创建索引

1、主键自动建立唯一索引
2、频繁作为查询条件的字段
3、查询中与其他表关联的字段，外键关系建立索引
4、单键/组合索引的选择问题，高并发下倾向创建组合索引
5、查询中排序的字段，排序字段通过索引访问大幅提高排序速度
6、查询中统计或分组字段

哪些些情况不需要创建索引

1、表记录太少
2、经常增删改的表
3、数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
4、频繁更新的字段不适合创建索引(会增加IO负担)
5、where条件里用不到的字段不创建索引

索引可选的数据结构有哪些

二叉树
红黑树
hash
B-Tree

测试数据结构可用的网站

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树作为索引数据结构的缺点

二叉树本身是有序树；
树中包含的各个节点的度不能超过 2，即只能是 0、1 或者 2；
二叉查找树的特点就是左子树的节点值比父亲节点小，而右子树的节点值比父亲节点大
二叉树简单理解就是，比我大的放右边，比我小的放左边。这样就能二叉寻找了，但是又个问题：
假设数字是有序的，例如，数据库表的id是自增的，那么后面的每一位数都会在右边，二叉树直接就变成链表了。可见，二叉树hold不住。
在这里插入图片描述

那就使用二叉树的进阶版，红黑树。

红黑树作为索引数据结构的缺点

每个节点或者是黑色，或者是红色。
根节点是黑色。
每个叶子节点(NIL)是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点]
如果一个节点是红色的，则它的子节点必须是黑色的。
从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。[这里指到叶子节点的路径
在这里插入图片描述
简单的说，就是插入的节点，会取到中间点作为父节点。
例如：一次插入三个数， 1 ，2，3。按照二叉树的话，就是链表结构了，1->2->3。
但是红黑树会自动调整，取出2作为上层，左边为1，右边为3。

红黑树解决了二叉树成为链表的尴尬情况了，但是同样的，又衍生出了新的问题。
数据库在插入数据的时候，用到红黑树的话，会频繁的变更树的结构。
并且红黑树最大的问题是，没有办法控制树的高度，假设红黑树有一千层的话，而想要拿到的数据又刚好在最底层，那么在查询的时候就会去做相对应的IO磁盘扫描。依然造成很大程度的资源损耗。
所以，如果数据库索引用红黑树结构的话，还是hold不住的。

B+树作为索引数据结构的有点

B+树也是多路平衡查找树。
B+树中只有叶子节点存储真实的数据，非叶节点只存储键。在MySQL中，这里所说的真实数据，可能是行的全部数据（如Innodb的聚簇索引），也可能只是行的主键（如Innodb的辅助索引），或者是行所在的地址（如MyIsam的非聚簇索引）。
（1）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样。
（2）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
（3）非叶子节点的子节点数=关键字数(百度百科。根据各种资料，这里有两种算法的实现方式，另一种为非叶节点的关键字数=子节点数-1(维基百科)，虽然数据排列结构不一样，但其原理还是一样的。Mysql 的 B+树是用第一种方式实现)。

B+树可以说是为了磁盘而生的，因为B+树的所有数据都存在叶子节点中，包存了父节点的指针，最重要的是，B+树最高只有三层，也就是说，无论数据在哪，最多只需要三次IO扫描便可以拿到数据。大大的节省了IO磁盘扫描次数。所以索引有利于增加数据获取的速度。

MySQL索引的类型

聚集索引：
聚集索引是索引结构和数据一起存放的索引。类似于字典的正文，当我们根据拼音直接就能找到那个字。

非聚集索引：
非聚集索引是索引结构和数据分开存放的索引。类似于根据偏旁部首找字，首先找到该字所在的地址，再根据地址找到这个字的信息。

聚集索引与非聚集索引区别及优缺点

区别：
1.聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个
2.聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
3.聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
4.非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
5.索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

优势与缺点：
聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

存储引擎InnoDB和MyISAM的区别

InnoDB是MySQL默认的存储引擎。
InnoDB 支持事务，MyISAM不支持事务。
InnoDB支持行级锁和外键，MyISAM不支持。
InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。
InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计。
MyISAM 引擎采用的是非聚簇式(即使是主键)设计,索引文件和数据文件不在同一个文件中

MySQL索引调优

MySQL逻辑架构

MySQL索引调优

什么是回表

MySQL innodb的主键索引是簇集索引。
也就是索引的叶子节点存的是整个单条记录的所有字段值。
不是主键索引的就是非簇集索引，非簇集索引的叶子节点存的是主键字段的值。
回表是就是执行一条sql语句，需要从两个b+索引中去取数据。
比如
表tbl有a,b,c三个字段，其中a是主键，b上建了索引
然后编写sql语句SELECT * FROM tbl WHERE a=1
这样不会产生回表，因为所有的数据在a的索引树中均能找到。
SELECT * FROM tbl WHERE b=1这样就会产生回表，
因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。
索引覆盖就是查这个索引能查到你所需要的所有数据，不需要去另外的数据结构去查。其实就是不用回表。怎么避免？不是必须的字段就不要出现在SELECT里面。或者b,c建联合索引。但具体情况要具体分析，索引字段多了，存储和插入数据时的消耗会更大。这是个平衡问题