详解 2021 最底层 Mysql 索引原理及其优化

2021 年 11 月 12 日
本文字数：6021 字
阅读完需：约 20 分钟

对于 MySQL 来说,在服务器层并不实现索引,而是交给存储引擎来实现的,因此不同的存储引擎实现的索引类型不太一样.InnoDB 作为当前使用最为广泛的存储引擎,使用的是 B+树索引,因此我们大部分时间提到的索引也都是指的它.

=============================================================================================================

MySQL 主要有以下几种索引:

B-树索引/B+树索引
哈希索引
空间数据索引
全文索引

本文只学习 B-树索引和 B+树索引.

B-树索引和 B+树索引

===========

这里不会特别详细的解释 B-树和 B+树的数据结构原理,有兴趣的小伙伴可以移步参考文章中的文章.或者通过 google 自行了解.

=============================================================

B-树

===

B-树是一棵多路平衡查找树,对于一棵 M 阶的 B-树有以下的性质:

===============================

根节点至少有两个子女.
每个节点包含 k-1 个元素和 k 个孩子,其中 m/2 <= k <= m.
每一个叶子节点都包含 k-1 个元素,其中 m/2 <= k <= m.
所有的叶子节点位于同一层.
每个节点中的元素从小到大排列,那么 k-1 个元素正好是 k 个孩子包含的值域的划分.

这么说可能会有一些难理解,可以将 B-树理解为一棵更加矮胖的二叉搜索树.

B+树

===

B+树是 B-树的进阶版本,在 B-树的基础上又做了如下的限制:

==============================

每个中间节点不保存数据,只用来索引,也就意味着所有非叶子节点的值都被保存了一份在叶子节点中.
叶子节点之间根据自身的顺序进行了链接.

这样可以带来什么好处呢?

中间节点不保存数据,那么就可以保存更多的索引,减少数据库磁盘 IO 的次数.
因为中间节点不保存数据,所以每一次的查找都会命中到叶子节点,而叶子节点是处在同一层的,因此查询的性能更加的稳定.
所有的叶子节点按顺序链接成了链表,因此可以方便的话进行范围查询.

怎样创建高性能的索引?

===========

由于优化索引和优化查询一般是分不开的,因此这一块可能会包含部分的查询优化内容.

=======================================

前缀索引和索引选择性

==========

如果希望给一个很长的字符串上添加索引,那么可以考虑使用前缀索引.在正式介绍前缀索引之前,我们先大概考虑一下索引的工作步骤,数据库使用索引进行查找的时候,一般是如下几步:

====================================================================================

在索引的 B+树上找到对应的值,比如找到学校名称为卡塞尔学院的一条记录,并且拿到这条数据在磁盘上的地址.
根据地址去磁盘上查找,拿到该条数据所有的值.

那么假如在所有的学校名称的值中,卡塞尔就可以唯一的标识这条数据,那么用卡塞尔来做索引是否可以达到和卡塞尔学院做索引相同的效果?

答案是肯定的,而使用卡塞尔的话,是可以减少索引的大小到原来的 60%的.这就是前缀索引的作用.

前缀索引: 在对一个比较长的字符串进行索引时,可以仅索引开始的一部分字符,这样可以大大的节约索引空间,从而提高索引效率.但是这样也会降低索引的选择性.

索引的选择性: 不重复的值/所有的值. 可以看出索引的选择性为 0-1,最高的就是该列唯一,没有重复值.所以唯一索引的效率是比较好的.

但是在一般情况下,较长的字符串的一些前缀的选择性也是比较好的,这个我们可以算出来.使用下面的语句:

select

count(distinct left(school_name,3))/count(*) as sch3,

count(distinct left(school_name,4))/count(*) as sch4,

count(distinct left(school_name,5))/count(*) as sch5,

count(distinct school_name)/count(*) as original

from

user;

其中查找到的 original 就是原本的选择性,sch3,sch4,sch5 分别是取该列的前 3,4,5 个字符作为索引的时候的选择性.逐步增加这个数值,当选择性与原来相差不大的时候,就是一个比较合适的前缀索引的长度.(一般情况下是这样,但是也有例外,当数据极其不均匀时,这样的前缀索引会在某个特殊的 case 上表现很差劲).

找到合适的长度之后,就可以创建一个前缀索引了:alter table user add index sch_pre3(`school(3)`)

注意:前缀索引和覆盖索引是很难一起使用的,我今天早上刚试过,对索引的优化进行到这一步之后无功而返,具体的原因在下面介绍完覆盖索引之后解释.

联合索引

====

一般我们都是有对多个列进行索引的需求的,因为查询的需求多种多样.这个时候我们可以选择建立多个独立的索引或者建立一个联合索引.大多数时候都是联合索引更加合适一些.

假设我们要执行这个语句:select * from user where school_name = '卡塞尔' and age > 20,我们在 school 和 age 上分别建立两个独立的索引,那么我们预期这条查询语句会命中两个索引,但是使用 explain 命令查看会发现不一定.这是一个玄学的过程.个人没有研究清楚.

从理论上来讲,MySQL 在 5.0 之后的版本里面对支持合并索引,也就是同时使用两个索引,但是 MySQL 的优化器不一定这样认为,他可能会认为,查询两次 B+树的代价高于查询一次索引之后去数据表进行过滤,因此会选择只用一个索引.(我在自己的 5 张表上做了类似此 case 的测试,结果都是只使用了一个索引.)

创建联合索引的语法:alter table user add index school_age(`school`,`age`).

使用联合索引的时候,有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配,例如上面的 school_age 联合索引,当仅使用 age 作为查询条件的时候是不能使用的,也就是说 select * from user where age =20 是不能命中上面的联合索引的.

在不考虑任何查询的情况下,我们应该讲选择性高的列放在联合索引的前面,但是实际上我们更多的是通过查询来反推索引,以使某个固定的查询可以尽可能的命中索引以提高查询速度.毕竟我们建立索引的目的也是为了加快查询的速度.

因此联合索引的优化更多的是根据某个或者某些语句来优化的,不具备一个通用的法则.

最左前缀索引的原理

=========

当数据列有序的时候,mysql 可以使用索引,那么假设我们建立了 school_age 索引,示例数据如下:

====================================================

<table><tbody><tr><td>school</td><td>age</td></tr><tr><td>a</td><td>12</td></tr><tr><td>b</td><td>12</td></tr><tr><td>b</td><td>14</td></tr><tr><td>b</td><td>15</td></tr><tr><td>c</td><td>1</td></tr></tbody></table>

在这份数据中,school 字段是完全有序的,索引 school 可以使用索引.

而从全表来看,age 字段不是有序的,因此无法直接使用索引,那么观察一下数据表,在什么时候 age 有序呢?在 school 进行定值匹配的时候,例如当 school=b 的时候,对于这三条数据而言,age 是有序的,因此可以使用 age 索引.这就是最左前缀的原理.

此外,最左前缀索引只能使用一个范围查询,例如 select * from user where school > a, select * from user where school = a and age > 12,都是可以命中索引的,但是 select * from user where school > a and age > 12 中,仅 school 可以命中索引,这也可以从上面得出结论.因为当 school 是范围匹配的时候,mysql 无法确认 age 字段是否严格有序,比如 school 的范围匹配命中了 b,c 的四条数据,那么 age 就不是有序的.无法使用后续的索引.

聚簇索引

====

聚簇索引不是一种索引类型,而是一种存储数据的方式.Innodb 的聚簇索引是在同一个数据结构中保存了索引和数据.

=======================================================

因为数据真正的数据只能有一种排序方式,所以一个表上只能有一个聚簇索引.Innodb 使用主键来进行聚簇索引,没有主键的话就会选择一个唯一的非空索引,如果还还没有,innodb 会选择生成一个隐式的主键来进行聚簇索引.为什么 innodb 这么执着的需要搞一个聚簇索引呢,因为一个数据表中的数据总得有且只有一种排序方式来存储在磁盘上,因此这是必须的.

这也是 innodb 推荐我们使用自增主键的原因,因为自增主键自增且连续,在插入的时候只需要不断的在数据后面追加即可.设想一下使用 UUID 来作为主键,那么每一次的插入操作,都需要找到当前主键在已排序的主键中的位置,然后插入,并且要移动该主键后的数据,以使得数据和主键保持相同的顺序,这无疑是代价非常高的.

也是因为这个原因,在其他索引的叶子节点中,存储的"数据"其实不是该数据的真实物理地址,而是该数据的主键,查找到主键之后,再根据主键进行一次索引,拿到数据.

聚簇索引和非聚簇索引的区别可以用一个简单的例子来说明:

当我们拿到一本书的时候,目录就是主键,是一个聚簇索引,因为在目录中连续的内容,在正文中也是连续的,当我们想要查看迎着阳光盛大逃亡章节,只需要在目录中找到它对应的页面,比如 459,然后去对应的页码查看正文即可.

而非聚簇索引呢,则类似于书后面的附录专有名词索引一样(二级普通索引),当你查找邦达列夫的时候,附录会告诉你,这个名词出现在了迎着阳光盛大逃亡一节,然后你需要去目录(主键索引)中再次查找到对应的页码.

覆盖索引

====

当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引.

======================================

设想有如下的查询语句:

select

school_name,age

from

user

where

school_name = '金色莺尾花学院'

这个语句根据学校名称来查询数据行的学校名称和年龄,从上面的数据查询的步骤我们可以知道,当在索引中找到要求的值的时候,还需要根据主键去进行一次索引,以拿到全部的数据,然后从其中挑选出需要的列,返回.但是现在索引中已经包含了所有的需要返回的列,那么就不用进行回数据表查询的操作了,此外索引的大小一般是远远小于真正的数据大小的,覆盖索引可以极大的减少从磁盘加载数据的数量.

为什么前缀索引和覆盖索引无法一起使用?

因为前缀索引的目的是用前缀来代表真正的值,他们在选择性上几乎没有区别,但是 MySQL 仍然无法判断真正的数据是什么,比如阿里巴巴和阿里妈妈在前缀为 2 的时候是一样的,但是为了确保你查询阿里巴巴的时候不会出现阿里妈妈的内容,是需要回到数据表拿到数据再次进行一个精准匹配来进行过滤的.

因此,覆盖索引无法和列前缀索引一起使用,这是我用一个早晨的时间测试得出的结论.

删除掉冗余和重复的索引

===========

有一些索引是从未在查询中使用过,却白白增加数据插入时开销的,对于这种索引我们应该及时的进行删除.

================================================

比如在主键上再建立一个普通索引,无疑是毫无作用的.

还比如在有联合索引 school_age 的情况下,再建立一个 school 的独立索引,因为索引的最左前缀匹配原则,school_age 是完全可以命中对 school 的单独查询的,因此后者可以删掉.

如何查看索引的一些相关信息?

==============

索引信息

====

在 mysql 中可以使用 show index from table_name 来查看某个表上的索引,它将会有如下的输出:

===========================================================

或者使用 show create table table_name 来查看建表语句,其中包含创建索引的语句.

索引大小

====

在 5.0 以后的版本中,我们可以通过查看 information_schema.TABLES 表中的数据来获取更加详细的数据.

=============================================================

该表各字段的含义如下表:

<table><tbody><tr><td>字段</td><td>含义</td></tr><tr><td>Table_catalog</td><td>数据表登记目录</td></tr><tr><td>Table_schema</td><td>数据表所属的数据库名</td></tr><tr><td>Table_name</td><td>表名称</td></tr><tr><td>Table_type</td><td>表类型[system view</td></tr><tr><td>Engine</td><td>使用的数据库引擎[MyISAM</td></tr><tr><td>Version</td><td>版本，默认值 10</td></tr><tr><td>Row_format</td><td>行格式[Compact</td></tr><tr><td>Table_rows</td><td>表里所存多少行数据</td></tr><tr><td>Avg_row_length</td><td>平均行长度</td></tr><tr><td>Data_length</td><td>数据长度</td></tr><tr><td>Max_data_length</td><td>最大数据长度</td></tr><tr><td>Index_length</td><td>索引长度</td></tr><tr><td>Data_free</td><td>空间碎片</td></tr><tr><td>Auto_increment</td><td>做自增主键的自动增量当前值</td></tr><tr><td>Create_time</td><td>表的创建时间</td></tr><tr><td>Update_time</td><td>表的更新时间</td></tr><tr><td>Check_time

【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

复制代码

</td><td>表的检查时间</td></tr><tr><td>Table_collation</td><td>表的字符校验编码集</td></tr><tr><td>Checksum</td><td>校验和</td></tr><tr><td>Create_options</td><td>创建选项</td></tr><tr><td>Table_comment</td><td>表的注释、备注</td></tr></tbody></table>

我们可以通过一些查询语句来获取详细的信息,比如:

// 查看当前 MySQL 服务器所有索引的大小(以 MB 为单位,默认是字节)

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES

// 查看某一个库的所有大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'XXX';

// 查看某一个表的索引大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'yyyy' and table_name = "xxxxx";

// 汇总查看一个库中的数据大小及索引大小

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(102410241024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(102410241024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(102410241024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'xxxxx';

对 tables 表的数据的所有查看方式都是可以的,其中还包含了一些表格本身的数据信息,但是因为和本文的主题不符合,这里就不举例子了.

注意:上面的表格是有缓存的,当更新数据库索引之后,最好执行 analyze table xxxx,然后再进行查看.MySQL 会在表格数据发生较大的变化时才更新此表(大小变化超过 1/16 或者插入 20 亿行).

索引碎片

====

在索引的创建删除过程中,不可避免的会产品索引碎片,当然还有数据碎片,我们可以通过执行 optimize table xxx 来重新整理索引及数据,对于不支持此命令的存储引擎来说,可以通过一条无意义的 alter 语句来触发整理,比如:将表的存储引擎更换为当前的引擎,alter table xxxx engine=innodb.

================================================================================================================================================================

参考文章

====

点击这里免费领取

目录！

===

发布于: 21 小时前阅读数: 3

发布

暂无评论

创作场景

详解 2021 最底层 Mysql 索引原理及其优化

评论