🍖索引原理与慢查询优化

引入

本篇博客偏理论, 将会介绍以下知识:

• 索引介绍
• 索引原理
• 索引的数据结构(二叉树--->平衡二叉树--->B树--->B+树)
• 聚集索引与辅助索引
• MySQL索引管理
• 创建和删除索引的语法
• 创建索引后的测试 (查询速度的变化)
• 如何正确使用索引
• 回表
• 覆盖索引
• 联合索引
• 最左前缀匹配
• 索引下推
• MySQL查询优化 : explain
• 慢查询优化的基本步骤
• 慢日志管理

一.索引介绍

1.什么是索引

• 索引是对数据库表中一列或多列的值进行排序的一种数据结构, 使用索引可以快速访问数据库表中的特定信息
• 为数据库建立索引, 就好比为书建立目录

2.为什么要有索引

• 优化数据查询效率

数据库的数据一般存储在磁盘中, 相比较内存, 磁盘的访问速度较慢索引就是可以帮助数据库快速从磁盘中找到数据的一种数据结构

• 注意 : 创建索引后会降低增、删、改的效率

虽然会降低, 但是一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重

3.为表创建的索引是不是越多越好?

• 多数情况下, 我们知道索引能够提高查询效率, 但过多也会影响应用程序效率, 怎么加才是关键
• 一个应用程序的设计, 数据上过多或过少的索引都会引发应用程序的效率问题, 所以我们需要找到一个平衡点
• 当表有大量数据的情况下, 创建索引的速度会很慢, 并且对于写的性能也会大幅度降低

4.索引应该什么时候加才最合适

• 任何一个软件都有其吸引用户的亮点, 亮点背后对应的是热数据, 无疑开发人员对热数据的所对应的数据库字段有哪些, 应该在开发软件的过程中就提前为相应的字段加上索引, 而不是等软件上线后让DBA发现慢查询sql后再做处理

原因 :

1.一个软件慢会影响用户体验, 但是慢的原因有很多, 你不能立即确定就是 SQL 的问题, 当你定位到 SQL 问题的时候就已经过去很久了, 问题没有得到及时的解决

2.大多数DBA都是管理型DBA而非开发型, 所以即便是DBA从日志中看到了慢查询sql, 也会因为其不懂业务而很难分析出慢的原因

二.索引原理

1.索引的原理

• 通过不断的缩小想要查询的数据范围筛选出最终的结果

就比如买火车票(无索引) : 如果没有12360火车票订购软件, 摆在我们面前的就是成千上万辆火车, 选择那一辆的条件有火车类型、出发和终点、时间等等, 我们需要一辆一辆火车去比对自己的筛选条件, 运气好第一辆就是要找的火车, 运气不好第一千辆才是要找的火车

加入索引 : 现在我们只需要在12360软件上选择高铁, 就能筛选掉不是高铁的火车, 缩小了查询范围; 再输入出发点和终点, 又缩小了查询范围; 再输入时间, 范围又减少, 最终找到自己需要的车次, 由不固定查询次数变成很小的固定查询次数

2.磁盘I/O与预读

• I\\O延迟

IO延迟 = 平均寻道时间 + 平均延迟时间(一般为9ms)--->例子:假设当前硬盘转轴(盘片)转速是7200/min，也就是120/s，那么转一圈需要花费1/120≈8ms，半圈也就是4ms(假设找到数据要半圈)

9ms左右对于我们来讲很短, 但对于一台500-MIPS的机器来说每秒可以执行5亿条指令, 换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间, 这简直是场灾难

• 预读

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助

三.索引的数据结构

索引的数据结构是 B+树, 而 B+树 是经过 二叉排序树 到 二叉平衡树 再到 B树 最后到 B+树 演变过来的, 下面简单介绍一下:

1.二叉排序树(二叉查找树)

• 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点(就是最下面一排)

对于一列数字 : 5、6、7、8、9、10

• 如果我们需要找到 key=9 的节点, 先将 9 与根节点比较, 大于根节点, 于是往右边找; 继续与右边的 10 比较, 小于, 于是往左边找, 正好找到九

利用二叉排序树我们只需要3次即可找到匹配的数据; 如果在数字列中一条条的查找的话，我们需要5次才能找到

2.平衡二叉树(AVL树)

• 平衡二叉树可以说是二叉排序树的改进版, 是特殊的二叉排序树

上面我们讲解了利用二叉排序树可以快速的找到数据; 但是，如果上面的二叉排序树是这样的构造：

平均查找长度是3, 如果我们调整一下关键字的序列

调整之后平均查找长度是 2.2, 从上面我们可以看出平均查找长度与数的高度有关, 平均查找长度越小, 查找速度就越快, 所以我们应该尽可能的让这棵树矮

• 怎么判断一颗二叉树是否是平衡二叉树?

这里引入了平衡因子的概念, 左子树的高度减右子数的高度就是平衡因子, 平衡因子的绝对值小于或等于一就是平衡二叉树, 大于一就是非平衡二叉树, 如下图平衡因子为 4 就是非平衡二叉树

我们调整一下关键字序列, 各子数平衡因子绝对值都小于或等于 1, 那么这就是一颗平衡二叉树

3.B 树 ( Balanced Tree）多路平衡查找树

• 我们知道平衡二叉树每个节点只能存储一个键值和数据

如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低

• 为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树, 也就是 B树

从上图可以看出，B树相对于平衡二叉树，每个节点(B树中节点称之为页)存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多

假设每个节点可以储存两个值(不代表只能存两个), 我们找到75:

• 先与 页1 比较,在 35 右边找到 p3指针 定位到 页4
• 与 页4 中的索引对比, 在 65-87 之间, 找到指针 p2, 定位到 页10
• 与 页10 中的索引对比, 找到相对应的 75

4.B+ 树

• B+ 树是对 B树的进一步优化

• 通过上图我们来对比下 B+ 树与 B树的不同:

• B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据

• 之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快

• B+ 树的阶数是等于键值的数量的，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

• 一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO

• 3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要两次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高

• B+ 树的两种性质

• 索引字段要尽量的小 : 磁盘块的大小也就是一个数据页的大小，是固定的. 如果数据项占的空间越小，数据项的数量越多，树的高度就越低, 查询过的IO次数就越少. 这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降, 下降则会导致每层可存储的数据就少, 因为磁盘块是固定的, 从而要增加层次, 进而导致树增高, 树增高意味着找到底层数据的IO次数增多, 导致查询速度大幅度下降
• 索引的最左匹配特性 : 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的. 比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据. 但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。 比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性

5.总结 B+ 树优点

• 在二叉树、平衡二叉树、B树的基础上做了进一步优化, 只有叶子节点放真正的数据，这意味着在等量数据的前提下,B+树的高度是最低的
• B+的叶子节点都是排好序的，这意味着在范围查询上，B+树比B树更快，快就快在一旦找到一个树叶节点，就不需要在再从树根查起了

四.聚集索引与辅助索引

数据库中的 B+树 索引可以分为聚集索引（clustered index）和辅助索引（secondary index）, 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的, 不同的是 :

聚集索引的叶子节点存放的是一整行完整的信息, 而辅助索引的叶子节点存放的并非完整信息(下面介绍)

1.聚集索引 (Clustered Index)

InnoDB 聚集索引的叶子节点存储行记录，因此 InnoDB 必须要有且只有一个聚集索引

• 如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引

• 如果表没有定义 PK，则第一个不为空且唯一(NOT NULL UNIQUE) 的列就是聚集索引

• 否则 InnoDB 会另外创建一个隐藏的 ROWID 作为聚集索引

由于这种机制是直接定位行记录，因此使得基于 PK 的查询速度非常快

2.辅助索引（ Secondary Index ）

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）

• 与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据

• 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引

• 当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录

五.Mysql 索引管理

1.功能

• 索引的功能就是加速查找
• mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

2.mysql 中常用的索引(键)

• 普通索引: index 加速查找
• 唯一索引:
  • 主键索引: primary key 加速查找+约束
  • 唯一索引: unique key 加速查找+约束
• 联合索引:
  • 联合主键索引: primary key
  • 联合唯一索引: unique(字段1, 字段2, ...)
  • 联合普通索引: unique(字段1, 字段2, ...)

上面的三种索引, 唯一索引除了可以增加查询速度之外各自还具有约束条件, 而普通索引index key没有任何的约束条件，只是用来帮助你加快速查询数据

注意：联合索引不是用来加速查询用的，不在我们的而研究范围之内

3.索引的两大类型

• 我们可以在创建索引的时候, 为其指定索引类型(两类)

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

• 不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引

六.创建和删除索引语法

1.创建的三种方法

• 语法

🍓方式一 : 创建表时建索引
create table [表名] (
[unique|fulltext|spatial] [index|key] [索引名] [字段名(长度)] [asc|desc]
);

🍓方式二 : 在已存在的表上创建
create [unique|fulltext|spatial] index [索引名]
on [表名] [字段名(长度)] [asc|desc];

🍓方式二 : alter 在已存在的表上创建索引
alter table [表名] add [unique|fulltext|spatial] index
[索引名] [字段名(长度)] [asc|desc];

• 示例代码

🍓方式一
create table t01(
id int,
name char(10),
age int,
sex enum(\"male\",\"female\"),
unique key unique_id(id),
index index_name(name) # index没有key
);

🍓方式二
create index index_age on t01(age);

🍓方式三
alter table t01 add index index_sex(sex);

2.删除索引

drop index [索引名] on t01; # 语法
drop index index_age on t01; # 示例

七.索引测试

1.先准备一张表, 并插入大量的数据

🍓创建表
create table t01(
id int,
name varchar(10),
sex enum(\"male\",\"female\"),
email varchar(18)
);

🍓创建存储过程,进行自动插入记录
delimiter %%%
create procedure p01()
begin
declare i int default 1;
while(i<3000000)do
insert t01 value(i,\"shawn\",\"male\",concat(\"shawn\",i,\"@163.com\"));
set i=i+1;
end while;
end %%%
delimiter ;

🍓查看存储过程
show create procedure p01\\G # \\G 垂直显示结果

🍓调用存储过程
call p01(); # windows执行测试大概一个半小时,3百万条记录,200多M

🍓删除存储过程
drop procedure p01;

2.没有建立索引的情况下测试查询速度

select * from t01 where id=3000000;

没有索引,mysql不知道有没有这条记录, 所以从头到尾的对记录进行遍历,有多少磁盘块就要进行多少I\\O,速度很慢

3.为表的某个字段建立索引(表已经存在大量记录,创建速度会很慢)

create index index_id on t01(id); # 为 id 字段建立普通索引

观察 data 文件夹下的 t01 表数据文件大小增加了

4.使用建立了索引的字段设置为条件进行查询

select * from t01 where id=3000000; # 可以观察到速度明显的提升

5.步骤分析

• mysql先去索引表里根据 b+树 的搜索原理很快搜索到 id 等于3000000的记录，直接命中索引, IO大大降低，因而速度明显提升

• 我们以没有建立索引的字段设置为条件来进行查询, 可以发现速度依然很慢

  select * from t01 email=\"shawn3000000@163.com\"; # 并且记录越大查询越慢

  

• 对 email 字段建立索引试试

create index index_email on t01(email); # 字段数据越大,建立的时间越长(所以建议不要使用数据很大的字段建立索引,这里只是做实验)
select * from t01 where email=\"shawn3000000@163.com\"; # 再去查询,可以发现速度是数量级的提升

八.正确使用索引

并不是说创建了索引就一定能加速查询, 有些情况就算命中了索引也未必能起到很好的提速效果, 下面来测试一下各种情况 (如果不想看过程,可以直接看小结末尾的结论)

1.范围查询情况 : 或者说条件不明确, 条件中有 : >、>=、<、<=、!= 、between...and...、like

• 大小于 : >、<

• 不等于 : !=

• between...and...

• 模糊匹配 : like

2.区分度情况 : 区分度表示字段不重复的比例, 区分度越大, 扫描的速度就越快,像主键唯一, 而性别字段区分度就很低

出现上面的情况就是因为字段的区分度太低, 在 B+树 中对于这些字段无法比较大小, 因为值都是相等的, 毫无疑问,只能增加树的高度来保证这些数据的存储, 树的高度越高, 查询速度就越慢

3.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4.索引列不要参与计算或者函数, 比如薪资乘与12 : 不要使用字段相乘(salary*12=10000), 可以使用该字段所对应的值相乘(salary=10000*12)

5.索引下推技术

🍓\"and\"与\"or\"的逻辑
[条件1] and [条件2] : 所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
[条件1] or [条件2] : 只要有一个条件成立则最终结果就成立

🍓\"and\"的工作原理
条件：
a = 10 and b = \'xxx\' and c > 3 and d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

🍓\"or\"的工作牌原理
条件：
a = 10 or b = \'xxx\' or c > 3 or d =4
索引：
制作联合索引(d,a,b,c)
工作原理:
对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

6.最左前缀匹配原则

7.总结 (怎么建立索引能提高查询效率)

• 对区分度高并且占用空间小的字段建立索引
• 针对范围查询命中了索引，如果范围很大，查询效率依然很低，如何解决
  • 要么把范围缩小
  • 要么就分段取值，一段一段取最终把大范围给取完
• 不要把查询字段放到函数或者参与运算
• 索引下推技术,mysql自动选择查询速度最优的那条语句 (默认是开启）
• 索引覆盖 (下面介绍)
• 最左前缀匹配原则 (下面介绍)

8.其他注意事项

• 避免使用select *
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合

九.回表、覆盖索引、联合索引、最左前缀

匹配原则、索引下推

1.创建一张表, 并插入记录

create table user(
-> id int not null auto_increment,
-> name char(16) not null,
-> age int not null,
-> primary key(id), # id 为主键并设置索引(聚集索引)
-> index index_name(name)); # name 字段设置索引(辅助索引)

insert user(name,age) value
-> (\"shawn\",23),
-> (\"song\",22),
-> (\"hai\",20),
-> (\"xing\",18),
-> (\"yanxi\",45),
-> (\"zichen\",25);

2.回表

select * from user where id=2;

上面为主键查询方式, 即通过聚集索引, 能找到 id 为 2 的完整记录

select * from user where name=\"song\";

上面为辅助索引查询方式, 则需要先搜索 name 索引树，得到 song 对应的 id 值为 2，再到 id 索引树搜索一次, 这个过程称为回表

• 结论 : 所以基于辅助索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用聚集索引

3.覆盖索引

select id from user where name=\"hai\";

上面语句查询的条件是 name 字段, name 字段有索引树, 并且上面保存有 name 和 id 的值, 可以直接提供查询结果, 不需要进行回表操作, 也就是说, 在这个查询里面, 索引 name 已经覆盖了我们所要查询的 id 字段需求, 这就称为覆盖索引

select age from user where name=\"xing\";

上面语句通过 name 索引树找到 name 字段对应的 \"xing\" 和 id 值, 但没有 age 字段信息, 于是通过 id 字段进行回表操作查找到满足条件的数据

• 回表操作无疑是降低效率的, 我们可以再为 age 建立索引, 避免索引太多可能引起的效率问题, 也可以为其建立联合索引

4.联合索引、最左匹配原则

🔰联合索引是指对表上的多个列合起来做一个索引. 联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列

🔰最左前缀匹配原则, 是非常重要的原则, mysql会从左到右进行匹配

• 先删除 name 字段的索引, 再与 age 一起建立联合索引

drop index index_name on user;
create index index_name_age on user(name,age); # 实际应用中应该把最常用的字段放在最左边

• 当查询条件中出现以下字段，可以命中联合索引，因为符合最左前缀原则

select name,age from user where name=\"song\"; # 条件字段 name
select name,age from user where name=\"song\" and age>18; # 条件字段 name + age

• 查询条件中只出现一个 age 字段，不能命中联合索引

select name,age from user where age=2; # 条件字段 age (不会走联合索引)

5.索引下推 (mysql自动开启)

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询

使用最左前缀匹配原则 + 联合查询可以加快查询速度, 如果我们的条件存在范围查询, 那么 SQL 语句是怎么运行的呢?

select * from user where name like \"s%\" and age=22;

如上表的记录, \"s\" 开头的记录有两条

• Mysql 5.6 以前 没有索引递推这个优化

Innodb 会忽略 age 这个字段, 直接通过 name 来进行查询, 在(name,age)这个联合索引上找到两条结果, 然后拿到 id 为 1 和 2 进行\"两次回表查询\"

• Mysql 5.6 及以后 添加了索引下推这个优化

Innodb 不会忽略 age 这个字段, 而是在索引内部就判断了 age 是否等于 22, 不等于 22 的记录直接跳过
因此在(name,age)这个联合索引上只匹配到一个记录, 此时拿着这一个 id 去回表到所有的数据只需要\"回表一次\"

十.MySQL查询优化 : explain

官方文档 : https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

ps : 强调 rows 是核心指标，绝大部分 rows 小的语句执行一定很快。所以优化语句基本上都是在优化rows

1.explain 是什么

explain 简称查看执行计划，使用 explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL 是如何处理 SQL 语句的

2.explain 如何使用

语法 : explain + [SQL语句]

3.explain 的作用

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

4.执行计划表包含的字段信息

explain select * from t01;

5.计划表字段说明

• id 字段

• select_type 字段

• table 字段

• type 字段

非常重要的项, 显示连接使用的类型, 按最优到最差的类型排序

• possible_keys 字段

• key 字段

• key_len 字段

• ref 字段

• rows 字段

• Extra 字段

6.示例

explain select * from t01 where id=100000;
explain select * from t01 where id>10000 and id<20000;
explain select * from t01 where id>20000;

十一.慢查询优化的基本步骤

• 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
• where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
• explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
• order by limit 形式的sql语句让排序的表优先查
• 了解业务方使用场景
• 加索引时参照建索引的几大原则
• 观察结果，不符合预期继续从第一个步骤开始分析

十二.慢日志管理

慢日志管理 : https://www.cnblogs.com/songhaixing/p/14448814.html

来源：https://www.cnblogs.com/songhaixing/p/14448846.html
图文来源于网络，如有侵权请联系删除。