MySQL常见面试题（三）

😀前言
在当今数据驱动的时代，数据库管理成为企业和组织的核心组件。其中，数据库的性能优化是确保信息可以快速、准确地检索的关键要素。这通常通过正确实现和管理数据库索引来实现。索引不仅可以大大提高数据库的查询性能，还可以帮助维持数据的完整性和一致性。本文将深入探讨MySQL数据库中的不同类型的索引，包括其特点和实现方式。我们还将讨论如何有效地使用这些索引来优化数据库性能，以及选择索引结构的重要性。

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

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

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

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

文章目录

简单描述MySQL各个索引的区别

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。

索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。

MySQL的索引对数据库的性能有什么影响

索引（Index）是帮助MySQL高效获取数据的数据结构，所以索引可以极大的提高数据的查询速度。

但是每建立一个索引都要为它建立一棵B+树，一棵很大的B+树由许多数据页组成会占据很多的存储空间。

而且每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引，同时这些操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

为什么MySQL的索引要使用B+树而不是B树？

答案见下一小节

InnoDB一棵B+树可以存放多少行数据？

当然在实际的数据库中，一个节点可以存储的数据可以很多，为什么？

计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。Innodb 的所有数据文件（后缀为 ibd 的文件），他的大小始终都是 16384（16k）的整数倍。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。

对于B+树而言，只有叶子节点存放数据，非叶子节点存放的是只保存索引信息和下一层节点的指针信息。一个非叶子节点能存放多少指针？

其实这也很好算，我们假设主键 ID 为常用的bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170个。

那么可以算出一棵高度为2的B+树，存在一个根节点和若干个叶子节点能存放 1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的
**B+ **树可以存放： 1170117016=21902400 **条这样的记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，就能满足千万级的数据存储。

那么为什么MySQL****的索引要使用B+树而不是B树？

而 B 树和B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。