关于mysql索引分析和优化(一)

bangiao
448 阅读21分钟

1. 查看SQL执行频率

查询当前sql是读多还是写多

show status like 'Com_______';

show status like 'Innodb_rows_%'; 查看 innoDB引擎的读写情况

参数含义
Com_select执行 select 操作的次数，一次查询只累加 1。
Com_insert执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update执行 UPDATE 操作的次数。
Com_delete执行 DELETE 操作的次数。
Innodb_rows_readselect 查询返回的行数。
Innodb_rows_inserted执行 INSERT 操作插入的行数。
Innodb_rows_updated执行 UPDATE 操作更新的行数。
Innodb_rows_deleted执行 DELETE 操作删除的行数。
Connections试图连接 MySQL 服务器的次数。
Uptime服务器工作时间。
Slow_queries慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

2. 定位低效率执行SQL

可以通过下面几种方式了解到自己的 mysql 是否执行慢了

  • 需要数据库开启慢查询日志
  • 使用show porcesslist进行查看, 一般发现 mysql 在慢查询中的进程或者 mysql 查询死锁了

3. 大招 -- explain

这里有几个字段需要注意

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered

下面使用到的案例看看就好, 没必要自己也照着案例跑一边

explain id (了解)

id 需要注意:

  • 如果值相等则默认从上至下的顺序执行
select *
from t_role tr
         join user_role ur on ur.role_id = tr.id
         join t_user tu on tu.id = ur.user_id;

  • 如果值不相等, 则执行的顺序根据ID值的大小, 从大到小执行
explain select *
from t_role tr
where tr.id = (select role_id
               from user_role ur
               where user_id = (select id from t_user tu where username = 'stu1'));

  • id 值相同和不同同时存在

不同时按照从大到小的顺序执行, 相同时可以认为是同一组, 按照从上到下执行

explain
select *
from t_user tu
join user_role ur on ur.user_id = tu.id
where ur.role_id = (select id from t_role tr where tr.id = 5)

explain -- select_type (了解)

| select_type | 含义 | | | ---- | ---- | | SIMPLE | 简单的select查询，查询中不包含子查询或者UNION | | PRIMARY | 查询中若包含任何复杂的子查询，最外层查询标记为该标识 | | SUBQUERY | 在SELECT 或 WHERE 列表中包含了子查询 | | DERIVED | 在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中 | | UNION | 若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED | | UNION RESULT | 从UNION表获取结果的SELECT |

explain ---- table (了解)

展示这一行的数据是关于哪一张表的

explain ---- type (重点)

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type含义
NULLMySQL不访问任何表，索引，直接返回结果
system表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将"主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
indexindex 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all将遍历全表以找到匹配的行

执行的效率从上到下

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system > const > eq_ref > ref > range > index > ALL

**一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref **

explain ---- key (重要)

possible_keys : 显示可能应用在这张表的索引， 一个或多个。
key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。key_len 只指示了 WHERE 中用于条件过滤时被选中的索引列，不包含 ORDER BY/GROUP BY 这部分被选中的索引列

key_len 长度计算公式:

类型公式
varchar(10)变长字段且允许NULL10 * ( character set: utf8mb4=4,=3utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL10 *( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL10 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1)
tinyint允许NULL1 + 1(NULL)
tinyint不允许NULL1
smallint允许为NULL2+1(NULL)
smallint不允许为NULL2
int允许为NULL4+1(NULL)
int不允许为NULL4
long允许为NULL8+1(NULL)
long不允许为NULL8
datetime允许为NULL5 + 1(NULL)
datetime不允许为NULL5
timestamp允许为NULL4 + 1(NULL)
timestamp不允许为NULL4

explain ---- ref (了解)

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

explain ---- rows (重要)

扫描行的数量

explain ---- extra(略重要)

extra含义
using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by 和 group by； 效率低
using index表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

使用 show profiles 查看每个sql语句底层都做了啥?

SELECT * FROM city c WHERE c.city_id = 1;
show profiles;
show profile ALL for query 153;

可以在 show profile ** for query id; 中的 ** 中添加 all、cpu、block io 、context switch、page faults

这里建议使用 console 去执行上面几步, 使用 nvicat 是使用 show profiles 会出现很多多余的东西, 使用 DataGrip 执行上面的过程直接就查不出来了

如果你使用的是 navicat 其实不需要那么麻烦, 上到上面几张图片判断的概况了么? 直接点击就看到了

trace分析优化器执行计划 (了解)

使用这个可以查看 mysql 的优化器为什么执行 A计划 不执行 B计划 的问题

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
select *
from t_user tu
where id < 4;
select *
from information_schema.OPTIMIZER_TRACE OT;

索引的使用

了解了前面的大招 explain 之后再配合这个一般问题都能够解决

  • 索引是什么???

索引其实是使用者对表字段的某些字段以某种规律排在一些数据结构上, 而一般索引都是用 BTree 这种数据结构表现出来, 同时如果使用了索引对于该表的 insert udpate delete 都将变慢但对该表一某种规律的读取后能够命中索引则读取数据的效率将提高很多很多, 总体来说又是一种写少读多的场景

避免索引失效

全值匹配

存在一个这样字段的表

create table tb_seller
(
    sellerid   varchar(100) not null
        primary key,
    name       varchar(100) null,
    nickname   varchar(50)  null,
    password   varchar(60)  null,
    status     varchar(1)   null,
    address    varchar(100) null,
    createtime datetime     null
);

create index idx_seller_name_sta_addr
    on tb_seller (name, status, address);

select 查询

explain select *
from tb_seller ts
where address = '西安市'
  and ts.name = '小米科技'
  and status = '1'
;

他所创建的索引刚刚好 select 查询所需的字段相同, 数量相同, 但是顺序可以不同, mysql 优化器会自动全值匹配它

左前缀匹配

索引顺序 : (name, status, address)

但是如果是匹配的数量不同的话, name 必须匹配, 必须要有, 然后 status 必须要有的情况下, address 的索引才能够匹配, 这就是左前缀匹配原则, 不能跳过索引顺序中间的 status , 直接对 name 后面的 address 做索引命中, 会出现 name 命中了 , 但 address 无法命中的情况

比如:

explain select *
from tb_seller ts
where address = '西安市'
  and status = '1';

上面这样就不能使用索引了, 但是如果是这样

explain select *
from tb_seller ts
where address = '西安市'
  and ts.name = '小米科技';

或者

explain select *
from tb_seller ts
where ts.name = '小米科技';

或者

explain select *
from tb_seller ts
where status = '1' and ts.name = '小米科技';

都是可以匹配的

说白了, 索引开头是 name 起步, 后面 select 语句不管是什么, name 必须 必须 必须 要存在, 顺序和数量可以不同, mysql 会对其进行自动优化

那么上面那几个 sql 语句的差别是什么?

使用 explain 你会发现他们中间有两个属性会发生变化, ref 和 key_len

比如

  1. 索引匹配三个

where address = '西安市' and ts.name = '小米科技' and status = '1'

  1. 索引匹配两个

where status = '1' and ts.name = '小米科技'

但如果是

where address = '西安市' and ts.name = '小米科技'

这里实质上, 索引的匹配不能跳着走, 必须完全符合左前缀原则, 否则匹配几个就显而易见了

  1. 索引匹配一个

where name = '小米科技'

发现了没, 这里头有大文章

首先 ref 属性的功能很好猜, 索引匹配几个 const 就是几个, 应该没啥大问题, 前面的对 ref 的了解 "表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。" 能看的出来意思

现在的问题是 key_len 属性的长度为什么不一样, 它又有什么计算方法?

前面提到过, key_len 不损失精确性的情况下，长度越短越好

但为什么? 越短的话? b+tree树 占用的空间大小变小? 然后单次匹配一个 叶子 结点时, 匹配一个和同时匹配三个 (name, status, address) 效率上快?

等到后面研究 BTree 的时候再说, 先留着悬念, 不过这问题提的, 把答案都写出来了

现在进行 key_len 长度计算方案

在计算之前需要查看表结构编码格式

show create table tb_seller;

CREATE TABLE `tb_seller` (
  `sellerid` varchar(100) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `password` varchar(60) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`sellerid`),
  KEY `idx_seller_name_sta_addr` (`name`,`status`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

发现他是 utf8mb4 格式的编码, 现在根据前面的计算方法

安装上面的方法计算,

`name` varchar(100) DEFAULT NULL  
CHARSET=utf8mb4

根据下面的公式: varchr(10)变长字段且允许NULL = 10 * ( character set：utf8mb4=4,=3utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

100 * 4 + 1 + 2 == 403 刚刚好就是我们图中的数字

status = '1' and ts.name = '小米科技'
CHARSET=utf8mb4

`name` varchar(100) DEFAULT NULL,
`status` varchar(1) DEFAULT NULL,

计算方法: 403 + (1 * 4 + 1 + 3) = 410 一样

再计算

address = '西安市' and ts.name = '小米科技' and status = '1'

`name` varchar(100) DEFAULT NULL
`status` varchar(1) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
CHARSET=utf8mb4

计算方法: 410 + (100 * 4 + 1 + 2) = 410 + 403 = 813 完全正确

对了, 前文有说过, key_len 在不损失精确性的情况下，长度越短越好, 注意, 不损失精确性的情况下 不损失精确性的情况下 不损失精确性的情况下

不要在索引上运算, 索引将失效

explain select *
from tb_seller ts
where substring(name, 3, 2) = '科技';

但如果是调用的聚合函数是可以走索引的

explain select concat(name, 1, 2)
from tb_seller ts;
explain select count(*)
from tb_seller ts;

对于字符串类型不加单引号, 索引失效

create index idx_status on tb_seller(status);

explain select *
from tb_seller ts where status = 1;

尽量使用索引的覆盖功能, 减少使用 select * 情况的出现

在这之前我们的补补 ,前面 extra 的坑, 没讲完 补补

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据, 如果句子本身没有索引匹配则可以无视, 反正都要回表
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

现在呢, 看看我都有哪些字段建立了索引

explain select ts.status
from tb_seller ts where ts.status = '1';

看见了没? 使用了覆盖索引

当如果使用了

explain
select *
from tb_seller ts
where status = '1';

这段 sql 的话最后的 extra 就不会有内容了

在三个索引下使用覆盖索引

explain
select address, name, status
from tb_seller ts
where address = '西安市'
  and ts.name = '小米科技'
  and status = '1'
;

现在写个这样的 sql , 三个索引都覆盖了, 但是多了个字段看看 extra 会显示什么? 目测是

explain
select name, status, address, password
from tb_seller ts
where ts.name = '小米科技'
  and status = '1'
  and address = '西安市'
;

不过经过测试 extra 还是 null

如果一个 sql 中存在多重执行流程或者限定模糊不清时, 也可能不走索引

你在看一句 sql 时, 是否能够判断的出来 这一句 sql 走的流程可能不确定? 如果有这种想法的产生, 你就需要验证下, 你的想法, 因为它极有可能是不走索引的

做个例子吧,

explain select *
from tb_seller ts where status = '1' or password = 'e10adc3949ba59abbe56e057f20f883e';

在这段代码中, 我们创建了一个 关于 status 的索引, 但是 password 并没有, 这句话的结果是

看上面那句 sql 你会发现, or 的出现导致了索引直接的失效为什么会这样呢?

因为它出现的非限定的路径就很多, 这句 sql 的意思是我可能 走了 status = '1' 后面的 password 字段就不需要判断了, 但也有可能结果走了 password = 'e10adc3949ba59abbe56e057f20f883e' , 它是不确定的, 就其结果而言, 结果集很多

当如果后面的 password 字段也被上了索引, 也是可以走索引的

而 like 中的 % 位置也同样能够解决 sql 是否能走索引

select *
from tb_seller ts where ts.name like '%科技%';

或者

select *
from tb_seller ts where ts.name like '%科技';

都是不可以使用到索引的, 本身索引走的是 BTree , 他是每个节点每个节点的匹配, 按照从左往右的方式匹配, 但当遇到左边的 % 你会发现根本无法匹配 BTree 上的元素, 但是他可以从右边的 % 比如:

explain select *
from tb_seller ts where ts.name like '科技%';

就能够匹配到索引 而且 extra 显示的是 Using index condition

那像上面这样的 sql 我们就没有办法了么?

答: 覆盖索引

explain select ts.status, ts.password
        from tb_seller ts where status = '1' or password = 'e10adc3949ba59abbe56e057f20f883e';


explain select name
        from tb_seller ts where ts.name like '%科技%';

至少可以挽回一点速度吧

如果使用索引比全表扫描还慢, 那么不需要使用索引 ---- mysql 优化器会自动优化

mysql 的优化器已经很完善了, 它会分析数据的具体情况, 对一些数据判断是否加上索引或者不走索引, 比如某个字段的数据分布发现数据基本上都是某个同样的值, 那么 mysql 会考虑使用全表扫描代替索引扫描, 即使那个字段已经被上了索引, 它也会无视掉

数据分布不均的情况下, 比如 10 条数据, 的 name 字段, 9 条 name 字段都是 小明 , 此时 sql 查询 where = '小明' 的情况下, mysql 会替你选择使用 全表扫描 , 但如果你是那个 唯一的 小天 那么在 where name = '小天' 的请款下, 它会走索引, 当然在实际的业务中可能不会有 1:9 这么夸张的比例, 但不得不注意到这样一个点, mysql 会主动介入优化我们的 sql

is null 和 is not null 有时不走索引

首先呢, 我做个总结

is null 和 is not null 走不走索引, 看数据分布情况而定, 大体上因该是 7 3 开

详解请看上面一条: 如果使用索引比全表扫描还慢, 那么不需要使用索引 ---- mysql 优化器会自动优化

现在来详解下

首先创建索引(在这里我留下一个伏笔)

show index from t_user;
create index idx_name on t_user (name);

然后执行

explain select *
from t_user tu where tu.name is null;

发现不走索引

很多人就会说你这个骗子....明明说好的走索引的呢? 别急 看 extra 英语翻译下 Impossible WHERE, 为了防止某些人太懒, 谷歌翻译下

这是什么意思? 带入到编程世界的翻译就是 不可能的where 条件过滤

为什么?

伏笔在这里

走啥索引? mysql 足够智能估计在优化器的时候就给你拦截了, 看看下面那张图

整个MySQL Server由以下组成

  • Connection Pool : 连接池组件
  • Management Services & Utilities : 管理服务和工具组件
  • SQL Interface : SQL接口组件
  • Parser : 查询分析器组件
  • Optimizer : 优化器组件
  • Caches & Buffers : 缓冲池组件
  • Pluggable Storage Engines : 存储引擎
  • File System : 文件系统

mysql 内部查询流程

现在我们使用前面的 profiles 功能来看看这条 sql 它走了那几步

注意如果此时使用的是 jetbrains 公司的 dataGrip 的话可能存在问题, 需要切换到 nvicat 下做实验

现在我把 name 的 not null 功能关闭试试看

可以很直观的发现他们的不同

所以在 not null 下 mysql 根本就不需要判断 is null

总结, 就是在字段已经设置了 非空 情况下, mysql 不会再去考虑 is null 需不需要加锁了

现在演示下在 数据极端分布 情况下 mysql 优化器的抉择选择

先创建 address 字段的索引

show index from tb_seller;
create index idx_address on tb_seller(address);

之后开始表演

explain select *
from tb_seller ts where address = '北京市';

你会发现 what? 它不走索引, 然后试 西安市

explain select *
from tb_seller ts where address = '西安市';

看到没这是 不走索引的情况

在极端的数据分布下, 如果你的数据超过了某个比例, 那么即使添加了索引, mysql 也不会再去 btree 索引树里面找了, 直接主表

现在呢, 我在表演表演 is null 遇到极端"天气"的情况下, 数据库怎么处理的

在这之前做好准备工作

alter table  tb_seller add  column updatetime datetime;
create index idx_udpatetime on tb_seller (updatetime);

update tb_seller
set tb_seller.updatetime = now() where sellerid = 'alibaba';

使用下面这条 is null 的 sql 试试

explain select *
from tb_seller ts where updatetime is null ;

现在使用 is not null 试试 看看走不走索引

explain select *
        from tb_seller ts where updatetime is not null ;

看到没? 虽然他走的是 range 比较慢, 但还是匹配了索引, 这就是极端天气的好处,

update tb_seller
set tb_seller.updatetime = now();

这样之后, 所有的 updatetime 都不为 null 了, 此时 mysql 化身慈善家, 帮助 穷苦人民建立索引(说白了, 就是帮助少的那部分建立索引), 所以 is null 可以跑索引, 而 is not null 不可以

一样的情况在 is null 和 is not null 下出现了, 其实还是数据分布极端的情况下 mysql 的选择, 或 is null 使用索引, 或 is not null 使用索引 或 is null 和 is not null 同时都使用了索引, 当然 is not null 走索引的话效率可能没有 is null 高

mysql 秉承着一个原则, 如果你的数据已经很多了, 每次走索引也相当于全表扫描, 那还不如直接全表扫描来的快, 毕竟 你还要回表

在扫描全表的情况下, 使用覆盖索引, 然后成功

explain select *
        from tb_seller ts where updatetime is not null ;

上面这种无法获得索引的, 但是修改成这样就可以了

explain select updatetime
        from tb_seller ts where updatetime is not null ;

Using where; Using index 所以, 不用回表再次查询了

如果改成这样就不行了

explain select status
        from tb_seller ts where updatetime is not null ;

即使我对 status 和 update 各自创建了索引, 也不行, 试试 创建一个 idx_status_updatetime 看看

我估计是可以直接走索引的, 毕竟如果走了索引就不需要回去主表了, 直接在 btree 上扫描完毕直接就可以返回了

所以结果显而易见

这里需要做个总结: 如果能在 btree 上找到所有需要的数据, 那么 mysql 会主动去 btree 上找

in 走索引， not in 索引失效

记住这句话, not in 其实 可以使用 not exists 或者 join 进行代替的

现在我给案例(例子举得不是很好, 别见怪)

下面这段代码我从网上找了几十万行的表, 然后改造的, 链接在文章末尾给出

explain select sql_no_cache *
from emp e
where deptId not in (select deptId from dept d where d.id = e.deptId and d.ceo > 9999)
;

explain select sql_no_cache *
from emp e
where not exists(select * from dept d where d.id = e.deptId and d.ceo > 9999);

explain select sql_no_cache e.*
from emp e
left join dept d on d.id = e.deptId and d.ceo > 9999
where d.ceo is null
;

但我在实际测试中并未发现 join 有多大优势, 反而比 not in 方式还慢, 而 not exists 坚挺, 还能够上索引

create index idx_ceo on dept(ceo); , 效率在 600ms 左右, 其他两个 not in 效率在 1.4 s 左右, join 在1.6s 左右

复合索引

复合索引前面已经用过了, 这里专门开一个小目录是为了写上这么一句话

我记得很久以前有个人跟我说, 能用复合索引就用复合索引, 以前不懂, 直接听他的话, 现在回想起来, 索引不就是用来过滤数据的么? 能过滤越多的数据就代表着这个索引创建的很成功

在我的想法中, 一个或多个索引就类似于过滤器一样, 将一些自己不需要的数据过滤掉, 这样显示出来的都是我们所需要的, 但如果我们没有创建索引 btree 的话, 直接在单表中, 它是以主键的方式建立的树, 只要涉及主键的查找自然速度就很快, 但非主键的字段怎么办? 只能把一些我们需要过滤的字段的数据抽取出来创建出另一个以该字段排序的树, 这样我们可以现在自己的树上过滤掉一部分数据然后再去主键btree上速度自然就很快了

注意这里我主要说的是 btree 实际应用中 索引树 未必就是 btree

查看索引使用情况

查看索引的使用情况从中可以判断出索引在整个查询中的使用数量和为命中数量, 好用来判断什么时候需要加上索引, 我们加的索引质量如何

show status like 'Handler_read%';
show global status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低
越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的
性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，
该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。
你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应
该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说
明你的表索引不正确或写入的查询没有利用索引。

数据库sql下载, 下载后直接导入便可

提取码: 4fci
avatar
文章
阅读
粉丝