MySQL数据库的存储引擎及索引

北海冥鱼未眠

已于 2022-05-09 08:06:22 修改

阅读量2.2k

点赞数

分类专栏： MySQL 面试题文章标签： spring java

于 2022-03-27 21:30:57 首次发布

本文链接：https://blog.csdn.net/qq_45401910/article/details/123781343

版权

面试题同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

MySQL

5 篇文章 0 订阅

订阅专栏

MySQL存储引擎

MySQL的存储引擎有很多种，使用show engines命令查看MySQL支持的存储引擎
在这里插入图片描述
我们这里主要介绍一下InnoDB和MyISAM存储引擎

MyISAM存储引擎（mysql-5.1版本之前默认引擎）

在这里插入图片描述

InnoDB存储引擎（mysql-5.1版本之后默认引擎）

在这里插入图片描述

相关的命令

查看mysql默认的存储引擎：show variables like '%storage_engine%';
修改默认的存储引擎：在配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB
可以在建表的时候指定存储引擎

样例：

drop table IF EXISTS `role_Menu`;
create table `role_Menu`(
    role_Menu_Id int(11) unsigned NOT null AUTO_INCREMENT COMMENT '角色菜单关系表主键',
    roleId int(11) DEFAULT NULL COMMENT '角色Id',
    menuId int(11) DEFAULT NULL COMMENT '菜单Id',
    
    PRIMARY KEY (role_Menu_Id) USING BTREE,
    Index index_roleId(roleId)

)ENGINE=InnoDB DEFAULT charset=utf8;

关于MySQL的字符集

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢？我想了一下，可能是因为 Mysql 刚开始开发那会，Unicode 还没有辅助平面这一说呢。那时候，Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数，对于 CHAR 数据类型来说，需要为字符串保留足够的长。当使用 utf8 字符集时，需要保留的长度就是 utf8 最长字符长度乘以字符串长度，所以这里理所当然的限制了 utf8 最大长度为 3，比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，我想一个是为了向后兼容性的考虑，还有就是基本多文种平面之外的字符确实很少用到。个人觉得也可以说是一个bug吧。
Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

MySQL的索引

为什么要使用索引

索引就像书的目录一样，可以帮助我们快速定位到要寻找的内容。它的目的就是为了提高数据查询的效率，将随机IO变为顺序IO，加速表和表之间的连接。

索引的数据结构

索引底层的数据结构有哈希表，有序数组，和搜索树。

针对于MyISAM存储引擎而言，B+树索引的叶子节点保存的是数据的物理地址，其叶子节点data域存放的是数据记录的地址，数据文件和索引文件是分离的。

针对于InnoDB存储引擎而言, InnoDB 要求每个表都必须要有主键,因为InnoDB本身就是基于主键来组织的数据存储。B+树索引的叶子节点保存数据本身，其数据本身就是索引文件。

InnoDB为什么选择B+树作为索引？

哈希索引虽然能够提供O（1）的时间复杂度，但是对范围查询和排序无法很好地支持，最终会导致全表扫描。

B树因为会在非叶子节点存储数据，占用存储空间，导致IO的次数更多，效率低。

B+树的非叶子节点不存储数据，树的高度更低，磁盘IO的次数更少，效率高，同时B+树的叶子节点通过指针相互连接，减少顺序遍历带来的随机IO。

主键索引

主键索引也就是我们说的聚集索引。上面说过主键索引是基于主键来创建的 B+ 树索引结构，如果没有指定主键，也找不到任何一列不重复的列可以作为主键的情况下，InnoDB 会新增一个隐藏列 RowId 作为主键继而创建聚集索引。在mysql数据库当中主键上会自动添加索引对象。

二级索引(非主键索引)

二级索引就是指除了主键索引外的索引。主键索引和所有的二级索引都是各自维护各自的 B+ 树结构，但是有个不同的地方在于，二级索引的叶子节点存储的不是数据，而是主键索引对应的主键值。
即二级索引不再保存一份 data 数据，而是去主键索引中查数据。那么对于二级索引查找一条数据索要做的操作就是：
首先在二级索引中找到叶子节点对应的数据主键值；根据这个主键值去聚集索引中找到真正对应的数据行。所以这里需要两次 B+ Tree 查找。这也就是我们所谓回表操作

覆盖索引

覆盖索引简单来说就是只查询索引就能获取到数据不必再回表查询，换句话说要查询的列已经被索引列覆盖。也就是我们要建立联合索引。

联合索引

有的时候我们会对多个列建立一个索引，这种索引被称为联合索引。而关于联合索引的建立和使用，从工作开始你的各位 “师长” 都在教导你要遵循 “左前匹配原则”，这就涉及到索引失效的问题了。

索引下推（超链接）

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
在不使用ICP的情况下，在查询条件有多个的时候，在主键上使用模糊查询，根据查询的结果再判断另一个条件是否符合。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
相关命令：set optimizer_switch='index_condition_pushdown=off';

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性，增加查询的扫描次数。索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

索引失效的场景

在这里插入图片描述

另外还有mysql估计使用全表扫描要比使用索引快,则不使用索引。
由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。