MySQL索引优化-天翼云开发者社区

存储引擎

MyISAM
- 文件结构：.myi 索引文件、.myd 数据文件、.frm 表结构文件
- 只有非聚集索引：索引文件不包含数据
- 只支持表级锁
- 不支持事务操作
- 适合读多写少：MyISAM 是非聚集索引，索引保存的是数据文件的指针，主键索引和辅助索引是独立的
InnoDB
- 文件结构：.ibd 索引数据字典文件、.frm 表结构文件
- 有聚集索引：索引文件包含数据
- 支持行级锁（只有当查询条件使用索引时才会使用行级锁，是对索引加锁，而不是对数据行加锁）
- 支持事务操作
- 适合写多读少：InnoDB 是聚集索引，通过主键索引效率很高，但是辅助索引需要两次查询

MyISAM 读速度比 InnoDB 快

索引结构

哈希表（数组 + 链表结构，散列函数映射数组下标，链表解决哈希冲突）

特点：等值查询速度快 O(1)，但不支持范围查询；同时，多列联合索引需要带上所有列，否则失效

B+树（多叉平衡树）多分查找 O(logN)

与 B-树相比：

B+树中间节点不含行数据，只含下个指针，故每个数据页可存储更多索引，树的高度变低，查询磁盘次数变少

行数据存放在叶子节点中，所有查询都要遍历到叶子节点，查询性能稳定

所有叶子节点形成有序链表，有利于范围查询

Tip：叶子节点随机插入数据会导致频繁的 页分裂，这是使用 uuid 作主键的弊端，使用 自增主键 从尾部插入可避免。在此基础上，自增主键中 雪花算法 有利于分布式部署，自增id 不利于分库分表

查询优化

索引失效 的情况

对左边的索引列作 表达式或函数运算

SELECT name,age,birthday FROM user WHERE a + 1 = 5
-- 字符串不加引号，导致左边列的类型转换成整数（函数运算），索引失效

联合索引不满足 最左前缀原则

-- 联合索引（name,age,birthday），在 B+树 中三个值作为一个索引整体，需要对比每个索引整体大小，字段从左到右，再分叉查找。
SELECT name,age,birthday FROM user WHERE name = 'Tom'; -- 走 name, 不走 age 和 birthday
SELECT name,age,birthday FROM user WHERE name = 'Tom' and age = 18; -- 走 name 和 age, 不走 birthday
SELECT name,age,birthday FROM user WHERE name = 'Tom' and age = 18 and birthday = 1600328876857; -- 全走
-- 其它的排列组合则三个字段都不走索引

-- 另外，若当前字段用到了范围（> < like），导致分叉查找出现范围，则索引不走字段后面的列
SELECT name,age,birthday FROM user WHERE name like 'T%' and age = 18 -- 走 name, 不走 age 和 birthday

使用 != 和 like %左模糊 会扫描全表
使用 is null 和 is not null 会扫描全表

查询时 覆盖索引，尽量避免回表

-- 聚簇索引：索引的叶子页直接存放数据行，一个表只有一个聚簇索引，主键索引为聚簇索引，反之不成立。
-- 回表：非聚簇索引（二级索引）的 B+ 树叶子节点不包含行数据，只包含聚簇索引字段数据。如果需要查询其它字段，则需要回表去查询聚簇索引的多叉树。
-- 覆盖索引：如果建立的索引上就已经有 SELECT 需要的字段，就不需要回表。

-- eg: 主键索引 id, 普通索引 name
SELECT age FROM user WHERE name = 'Tom';  -- 回表

-- 设置成联合索引（name, age）
SELECT age FROM user WHERE name = 'Tom';  -- 无需回表

索引下推 减少回表次数

-- 索引下推是 MySQL5.6 引入的优化，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返回聚簇索引多叉树进行查询，减少回表次数

-- 设置联合索引（name,age），若表中有（Tom, 18）（Tom, 19）两条记录
SELECT birthday FROM user WHERE name like 'T%' and age = 18 -- 走 name, 不走 age
-- MySQL5.6之前，两条记录都回表；有了索引下推后，只有（Tom, 18）回表

区分度不高 的列没有必要设置索引

-- 区分度：COUNT(DISTINCT col)/COUNT(*)
-- 区分度太低的字段，比如 state, 加了索引扫描出来的行数也很多

长字符串使用 前缀索引，降低索引空间

-- 前缀索引中，通过区分度的大小来设置前缀的长度

遵循小表驱动大表原则，没走索引 驱动 走索引

-- MySQL表连接查询算法是 Nest Loop Join
-- 通过循环遍历驱动表的数据，作为被驱动表数据查询的过滤条件，最后对结果合并
-- 驱动表不会走索引，被驱动表走索引
-- 可用 EXPLAIN 执行计划查询，第一行出现的表是驱动表

-- exists 外表是驱动表；内表是被驱表，字段可使用索引。外小内大
select A.id from A where exists (select 1 from B where B.id = A.id);

-- in 内表是驱动表；外表是被驱表，字段可使用索引。外大内小
select A.id from A where A.id in (select id from B);

-- 对于 join 查询
-- 当连接查询没有 where 条件时：
--      1. left join 查询时，左表是驱动表，右表是被驱动表；
--      2. right join 查询时，右表是驱动表，左表是被驱动表；
--      3. inner join 查询时，能走索引的是被驱动表
-- 当连接查询带有 where 条件时：能走索引的是被驱动表

-- 由于 Nest Loop 算法，如果内嵌表不走索引，那每次外循环时，内嵌表都得遍历全表
-- eg: 小表a, 大表b
--     on a.id = b.a_id
--     若 a.id有加索引，b.a_id没有加索引，则 a 是被驱动表
--     若 a.id有加索引，b.a_id也有加索引，则 b 是被驱动表

Select 执行顺序

引擎在执行下面每一步时，都会在内存中形成一张虚拟表，然后对虚拟表进行后续操作，并释放没用的虚拟表的内存，以此类推

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <inner join, left join, right join ...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

唯一索引和普通索引

查询过程

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

更新过程

change buffer：更新操作中，如果 数据页 在内存中就直接更新内存，等后续落地；若数据页不在内存中，通过 change buffer 可使 更新操作 不加载 数据页 进内存，而是先记录在 cb；在下次查询数据页时，将数据读入内存，并整合 cb 的更新操作。change buffer 减少更新过程中读磁盘的消耗，适用 写多读少 的业务场景。

唯一索引 下 change buffer 失效，每次更新都得加载数据页判断唯一性；普通索引 可使用 change buffer

如何选择：两类索引在查询性能上差距微乎其微，主要考虑更新性能。尽量选择 普通索引，并开启 change buffer（若所有的更新后面马上伴随记录的查询，则应该关闭 change buffer）

count() 效率

优先使用 count(*)：count(*) ≈ count(1) > count(主键id) > count(字段) ：

注：count() 是 Server层 执行的聚合函数，需对 InnoDB 返回的数据进行 非空判断。但是 count(*) 是例外，Server 作了优化，不做判断空值的逻辑，直接按行累计加1。

慢查询

set global slow_query_log=ON  -- 开启慢查询
set global long_query_time=2  -- 超过2s算作慢查询，记录到日志中
set global long_querise_not_using_indexex=ON  -- 记录下没有使用索引的查询语句

# 慢查询相关配置
show variables like '%slow_query%';
# 查看正在执行的进程
show processlist;
# 查看当前事务信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G;
# 查看当前事务持有锁信息
SELECT * FROM performance_schema.data_locks\G;

执行计划

MySQL整体逻辑架构图可以分为 Server层和存储引擎层

Server层:
    - 连接器：负责跟客户端建立连接、获取权限
    - 查询缓存：执行SQL语句之前，先查缓存，没有再执行
    - 分析器：SQL词法分析、SQL语法分析
    - 优化器：索引选择，选择执行效率高的，生成执行计划
    - 执行器：操作存储引擎，返回执行结果
存储引擎层：
    有 InnoDB、MyISAM 等，负责数据的存储和提取

Explain

可以查出SQL语句在优化器中的执行计划，分析SQL的执行效率其输出字段和含义如下

id 表示查询中 select语句的执行顺序

id 相同，执行顺序从上到下；不同则大的先（大的是子查询）

select_type select语句对应的查询类型

主要是区别普通查询、联合查询、子查询等的复杂查询

table select语句执行的表名

type 查询类型，即搜索行记录的大概范围

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all
得保证查询达到 range 级别，最好达到 ref
    - const：在简单查询时，通过主键或唯一索引列与常数比较时，所以表最多有一个匹配行
    - eq_ref：在连接查询时，被驱动表是通过主键或唯一索引列与常数比较时，被驱动表为 eq_ref
    - ref：不使用唯一索引，而是使用普通索引或者唯一性索引部分前缀，可能会找到多个符合条件的行
    - range：使用索引获取范围区间的记录，通常出现在 in, between, >, <, >= 等操作中
    - index：扫描全表索引 eg select * from group 其中group表的字段都有索引
    - all：扫描全表，没走索引（index是扫描索引块，而all是扫描数据块）

key 查询中用到的索引名称

key_len 查询中用到的索引字段长度

- char(n)：n字节长度 varchar(n)：2字节，如果是utf-8，则长度3n + 2
  字符串存储时，若该索引列可以存储NULL值，则key_len多1个字节
- tinyint：1字节  smallint：2字节  int：4字节  bigint：8字节　
- date：3字节  timestamp：4字节  datetime：8字节

ref 查询中用到的索引字段

rows 优化器预计要读取并检测行数

采样预测，有误差；同时优化器不一定使用行数少的索引，会考虑是否回表
可以强制使用某个索引：force index

extra 额外信息，常见的重要值如下

- Using index：使用了覆盖索引，性能高
- NULL： 查询的列未被索引覆盖，需要回表
- Using temporary： 创建内部临时表来处理查询，需要考虑索引优化
- Using filesort： 无法利用索引完成的排序操作称为文件排序，性能差

-- 联合索引（name,age,birthday），在 B+树中三个值作为一个索引整体，需要对比每个索引整体大小，字段从左到右，再分叉查找。
SELECT name,age,birthday FROM user WHERE name = 'Tom'; -- 走 name, 不走 age 和 birthday
SELECT name,age,birthday FROM user WHERE name = 'Tom' and age = 18; -- 走 name 和 age, 不走 birthday
SELECT name,age,birthday FROM user WHERE name = 'Tom' and age = 18 and birthday = 1600328876857; -- 全走
-- 其它的排列组合则三个字段都不走索引

-- 另外，若当前字段用到了范围（> < like），导致分叉查找出现范围，则索引不走字段后面的列
SELECT name,age,birthday FROM user WHERE name like 'T%' and age = 18 -- 走 name, 不走 age 和 birthday

-- 聚簇索引：索引的叶子页直接存放数据行，一个表只有一个聚簇索引，主键索引为聚簇索引，反之不成立。
-- 回表：非聚簇索引（二级索引）的 B+ 树叶子节点不包含行数据，只包含聚簇索引字段数据。如果需要查询其它字段，则需要回表去查询聚簇索引的多叉树。
-- 覆盖索引：如果建立的索引上就已经有 SELECT 需要的字段，就不需要回表。

-- eg: 主键索引 id, 普通索引 name
SELECT age FROM user WHERE name = 'Tom'; -- 回表

-- 设置成联合索引（name, age）
SELECT age FROM user WHERE name = 'Tom'; -- 无需回表

-- 索引下推是 MySQL5.6 引入的优化，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返回聚簇索引多叉树进行查询，减少回表次数

-- 设置联合索引（name,age），若表中有（Tom, 18）（Tom, 19）两条记录
SELECT birthday FROM user WHERE name like 'T%' and age = 18 -- 走 name, 不走 age
-- MySQL5.6之前，两条记录都回表；有了索引下推后，只有（Tom, 18）回表

-- MySQL表连接查询算法是 Nest Loop Join
-- 通过循环遍历驱动表的数据，作为被驱动表数据查询的过滤条件，最后对结果合并
-- 驱动表不会走索引，被驱动表走索引
-- 可用 EXPLAIN 执行计划查询，第一行出现的表是驱动表

-- exists 外表是驱动表；内表是被驱表，字段可使用索引。外小内大
select A.id from A where exists (select 1 from B where B.id = A.id);

-- in 内表是驱动表；外表是被驱表，字段可使用索引。外大内小
select A.id from A where A.id in (select id from B);

-- 对于 join 查询
-- 当连接查询没有 where 条件时：
-- 1. left join 查询时，左表是驱动表，右表是被驱动表；
-- 2. right join 查询时，右表是驱动表，左表是被驱动表；
-- 3. inner join 查询时，能走索引的是被驱动表
-- 当连接查询带有 where 条件时：能走索引的是被驱动表

-- 由于 Nest Loop 算法，如果内嵌表不走索引，那每次外循环时，内嵌表都得遍历全表
-- eg: 小表a, 大表b
-- on a.id = b.a_id
-- 若 a.id有加索引，b.a_id没有加索引，则 a 是被驱动表
-- 若 a.id有加索引，b.a_id也有加索引，则 b 是被驱动表

FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <inner join, left join, right join ...>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

set global slow_query_log=ON -- 开启慢查询
set global long_query_time=2 -- 超过2s算作慢查询，记录到日志中
set global long_querise_not_using_indexex=ON -- 记录下没有使用索引的查询语句

# 慢查询相关配置
show variables like '%slow_query%';
# 查看正在执行的进程
show processlist;
# 查看当前事务信息
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G;
# 查看当前事务持有锁信息
SELECT * FROM performance_schema.data_locks\G;

Server层:
- 连接器：负责跟客户端建立连接、获取权限
- 查询缓存：执行SQL语句之前，先查缓存，没有再执行
- 分析器：SQL词法分析、SQL语法分析
- 优化器：索引选择，选择执行效率高的，生成执行计划
- 执行器：操作存储引擎，返回执行结果
存储引擎层：
有 InnoDB、MyISAM 等，负责数据的存储和提取

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all
得保证查询达到 range 级别，最好达到 ref
- const：在简单查询时，通过主键或唯一索引列与常数比较时，所以表最多有一个匹配行
- eq_ref：在连接查询时，被驱动表是通过主键或唯一索引列与常数比较时，被驱动表为 eq_ref
- ref：不使用唯一索引，而是使用普通索引或者唯一性索引部分前缀，可能会找到多个符合条件的行
- range：使用索引获取范围区间的记录，通常出现在 in, between, >, <, >= 等操作中
- index：扫描全表索引 eg select * from group 其中group表的字段都有索引
- all：扫描全表，没走索引（index是扫描索引块，而all是扫描数据块）

- char(n)：n字节长度 varchar(n)：2字节，如果是utf-8，则长度3n + 2
字符串存储时，若该索引列可以存储NULL值，则key_len多1个字节
- tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节　
- date：3字节 timestamp：4字节 datetime：8字节

- Using index：使用了覆盖索引，性能高
- NULL：查询的列未被索引覆盖，需要回表
- Using temporary：创建内部临时表来处理查询，需要考虑索引优化
- Using filesort：无法利用索引完成的排序操作称为文件排序，性能差

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

MySQL索引优化

存储引擎

索引结构

查询优化

Select 执行顺序

唯一索引和普通索引

count() 效率

慢查询

执行计划

MySQL索引优化

存储引擎

索引结构

查询优化

Select 执行顺序

唯一索引和普通索引

count() 效率

慢查询

执行计划

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

MySQL索引优化

存储引擎

索引结构

查询优化

Select 执行顺序

唯一索引和普通索引

count() 效率

慢查询

执行计划

MySQL索引优化

存储引擎

索引结构

查询优化

Select 执行顺序

唯一索引和普通索引

count() 效率

慢查询

执行计划