MySQL索引原理及其优化

网上都说学会 mysql 需要学会两个部分,索引和事务,其实在最近的Mysql学习过程中,我觉得应该是有三个部分的,索引,查询,事务.其中的查询主要是指查询优化即编写高效率的 SQL 语句.

本文记录一下学习MySQL的索引过程中的一些知识.主要为阅读《高性能MySQL》的一些理解和扩展.

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构.

这是MySQL官方对于索引的定义,可以看到索引是一种数据结构,那么我们应该怎样理解索引呢?一个常见的例子就是书的目录.我们都已经养成了看目录的习惯,拿到一本书时,我们首先会先去查看他的目录,并且当我们要查找某个内容时,我们会在目录中查找,然后找到该片段对应的页码,再根据相应的页码去书中查找.如果没有索引(目录)的话,我们就只能一页一页的去查找了.

在MySQL中,假设我们有一张如下记录的表:

如果我们希望查找到年龄为15的人的名字,在没有索引的情况下我们只能遍历所有的数据去做逐一的对比,那么时间复杂度是O(n).

而如果我们在插入数据的过程中, 额外维护一个数组,将age字段有序的存储.得到如下数组.

[10,15,18,20,21]
 |  |  |  |  |
[x1,x4,x2,x3,x5]
复制代码

下面的x是模拟数据再磁盘上的存储位置.这个时候如果我们需要查找15岁的人的名字.我们可以对盖数组进行二分查找.众所周知,二分查找的时间复杂度为O(logn).查找到之后再根据具体的位置去获取真正的数据.

PS:MySQL中的索引不是使用的数组,而是使用的B+树(后面讲),这里用数组举例只是因为比较好理解.

索引能为我们带来什么?

如上面所说,索引能帮助我们快速的查找到数据.其次因为索引中的值是顺序储存,那么可以帮助我们进行orderby操作.而且索引中也是存储了真正的值的,因此有一些的查询直接可以在索引中完成(也就是覆盖索引的概念,后面会提到).

总结一下索引的优点就是(《高性能》书中总结的):

• 减少查询需要扫描的数据量(加快了查询速度)
• 减少服务器的 排序 操作和创建临时表的操作(加快了groupby和orderby等操作)
• 将服务器的随机IO变为顺序IO(加快查询速度).

索引有哪些缺点呢?

首先索引也是数据,也需要存储,因此会带来额外的存储空间占用.其次,在插入,更新和删除操作的同时,需要维护索引,因此会带来额外的时间开销.

总结一下:

• 索引占用磁盘或者内存空间
• 减慢了插入更新操作的速度

实际上,在一定数据范围内(索引没有超级多的情况下),建立索引带来的开销是远远小于它带来的好处的,但是我们仍然要防止索引的滥用.

都有哪些类型的索引?

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

MySQL主要有以下几种索引:

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

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

B-树索引和B+树索引

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

B-树

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

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

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

B+树

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

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

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

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

怎样创建高性能的索引?

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

前缀索引和索引选择性

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

卡塞尔学院

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

答案是肯定的,而使用 卡塞尔 的话,是可以减少索引的大小到原来的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 索引,示例数据如下:

在这份数据中,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 表中的数据来获取更加详细的数据.

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

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

// 查看当前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/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),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 .

2019-06-01 完成