目录
一、复习查询优化器
二、MySQL常见瓶颈
三、Explain
四、各字段解释
五、单表查询优化(索引失效)
一、复习查询优化器
MySQL有专门负责优化select语句的优化器模块
通过计算系统中收集到的统计信息,为请求的query提供他(mysql)认为最优的执行计划,但不见得是DBA认为最优的,这过程最耗时
过程:
(1)客户端向MySQL请求一条Query发给解析器
(2)命令解析器完成请求分类,区别是select并转发给MySQL Query Optimizer
(3)MySQL Query Optimizer对整条Query进行优化,处理一些常量表达式的预算,直接换算成常量值,并对Query中查询条件简化,根据Hint信息以确定执行计划,进行计算分析,最后得出执行计划
二、MySQL常见瓶颈
CPU饱和:发生在磁盘读取数据 || 数据装入内存
磁盘IO瓶颈:装入的数据量 大于 内存容量
查看系统性能状态:top,free,iostat,vmstat等命令查看系统的性能状态
性能下降SQL(慢执行程序||等待长数据库)
1. 查询语句写的慢
- 各种连接,子查询导致没用索引
- 没建索引
2. 索引失效(建了索引,没用上)
- 单值
- 复合
3. 关联查询太多join(设计缺陷或者不得已需求)
- 分布式开发的库表分离,同一张表数据过大会分到不同的库
- 那么join关联太多也会影响效率
4. 服务器调优及各个参数设置(缓冲,线程数)
三、Explain
定义
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的
- 目的:分析你的查询语句或者表结构是否存在性能瓶颈
作用
- 表table的读取顺序------id,table
- 数据读取器操作的操作类型------select_type
- 哪些索引可以使用----possible_keys
- 哪些索引被实际使用----key(索引名)
- 表之间的引用----ref(索引具体的列)
- 每张表有多少行被优化器查询---rows(被查询的行数)
EXPLAIN+SQL 效果图
四、各字段解释
目录
- id:select选择标识符
- select_type:表示查询的类型
- table:输出结果集的表
- type:表示表的连接类型
- possible_keys:表示查询时可能使用的索引
- key:表示实际使用的索引
- 覆盖索引
- key_len:索引字段的长度
- ref:列与索引的比较
- rows:扫描出的行数(估算的行数)
- Extra:执行情况的描述和说明
1.id:表的读取顺序
id是select查询的序列号(一组数字),表示查询中执行select子句或操作表的顺序
id相同:执行顺序为 从上至下执行(图中红色箭头)
id不同:执行顺序为 id大的先执行
id相同又不同： 执行顺序为
- id不同时，值较大的先执行
- id相同时，从上至下执行(可以认为是同一组)
image-20200903160343340.png
表的加载顺序为t3, t2, 虚表dervied2 ** 其中s1中的dervied2** 的 2，为 id = 2,父亲是t3
总结:由t3衍生s1,s1和t2在里面关联查询,故t3 > s1 > t2
2. select_type:表示查询的类型
| elect_type 属性 | 含义 |
|---|---|
| SIMPLE | 简单的 select 查询,查询中不包含子查询SUBQUERY或者 UNION |
| PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary (最后加载的鸡蛋壳🥚) |
| DERIVED | 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会<u>递归执行</u>这些子查询, 把结果放在<u>临时表里</u> |
| SUBQUERY | 在SELECT或WHERE列表中包含了子查询(带括号那些) |
| DEPEDENT SUBQUERY | 在SELECT或WHERE列表中包含了子查询,子查询基于外层 |
| UNCACHEABLE SUBQUERY | 无法使用缓存的子查询 |
| UNION | 若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED |
| UNION RESULT | 从UNION表获取结果的SELECT |
UNION 和 UNION RESULT
3. table:输出结果集的表
显示这一行数据是关于哪张表的
4. type:表示表的连接类型
#是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery > range > index > all
#常见的顺序
--一般来说，得保证查询至少达到 range 级别，最好能达到 ref
--查的少/细/精准 ----------> 查的多/广泛
system > const > eq_ref > ref > range > index > all
| 类型名 | 含义 |
|---|---|
| SYSTEM | 表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计 |
| CONST | 表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量 |
| EQ_REF | 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
| REF | 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 |
| RANGE | 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点不用扫描全部索引 |
| INDEX | 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组 |
| ALL | Full Table Scan，将遍历全表以找到匹配的行 |
5. possible_keys:表示查询时可能使用的索引
显示可能应用在这张表中的索引,一个或多个
查询设计到的字段上如果存在索引,则会被列出,但是不一定会被查询实际使用
6. key:表示实际使用的索引
实际使用的索引。如果为NULL，则没有使用索引
EXPLAIN SELECT * FROM tbl_emp WHERE id = 1 AND deptId = 1;
MySQL推测出可能使用到主键索引和fk_table_id外键部门编号索引,但是最后实际只用了主键索引
7. 覆盖索引(数据在索引那,不要通过主键去回表)
mysql会判断你只要查找索引里面有的字段,那么就全往索引里面去查了
当查找的字段与建立索引的匹配(查找的字段都是索引,但是不需要整表的所有字段都有索引)
这个时候,就会发生覆盖索引,MySQL推测使用的索引为NULL,而实际上会使用索引
解释:
- select的数据列只要能从索引中获取,就不必从数据表中读取,查询列要被所使用的的索引覆盖
-
注意:要使用覆盖索引,**只取出需要的列(有创建索引),不要使用select ***
image-20200903200703723.png
8.key_len:索引字段的长度
表示索引中使用的字节总数,key_len能够帮你检查是否充分利用上了索引
key_len越长,说明索引使用的越充分
| 索引字段类型 | 索引占多少字节 | 备注 |
|---|---|---|
| 允许为null | +1字节 | MySQL需要1个字节标识NULL |
| char(20) | 20*3 | utf8字符集占用3个字节 |
| varchar(20) | 20*3 + 2 | utf8字符集占用3个字节,变长字段需要+2。 |
| timestamp | 4 | timestamp占用4字节 |
9. ref:列与索引的比较
ref显示索引的哪一列被使用了,如果可能的话,可以是一个常量
10. rows:扫描出的行数(估算的行数)
rows列显示MySQL认为它执行查询时必须检查的行数,越少越好
--查询下索引名
show index from tbl_emp;
--先删除索引
DROP INDEX fk_table_id ON tbl_emp;
--查找
EXPLAIN SELECT * FROM tbl_emp,tbl_dept WHERE tbl_emp.deptId = tbl_dept.id;
--再创建索引
CREATE INDEX fk_table_id ON tbl_emp(deptId);
--查找
EXPLAIN SELECT * FROM tbl_emp,tbl_dept WHERE tbl_emp.deptId = tbl_dept.id;
11. Extra:执行情况的描述和说明
using filesort:使用外部索引排序(未使用用户创建的索引)----九死一生
- mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql无法利用索引完成的排序称为"文件排序"
- 出现Using filesort说明SQL语句设计不好,没有按照创建的索引排序, 或者未按照索引指定的顺序进行排序
- idx_emp_empno_age,想要利用索引进行排序,中间不能断,
ORDER BY age就会出问题
image-20200904084314101.png
using-temporary:排序时使用临时表---------无一生还
- 使用了临时表保存中间结果,MySQL对查询结果排序时使用临时表,常见于排序order by或者group by
-
出现using temporary说明SQL语句设计的非常不好+1,可能是没有按照顺序使用索引排序
image-20200904144111650.png
using index:使用了覆盖索引(Covering Index),效率不错
- 表示在select操作中使用的覆盖索引,避免访问了表的数据行,效率得到大幅提升
- 如果同时出现using where,表明索引被用来执行索引键值的查找(还行)
- 如果只出现using index,表明索引只是用来读取数据,而非利用索引来执行查找(效率更猛,索引上数据直接拿来用了!!!)(等于覆盖索引)
- Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
- Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
- Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
- No tables used：Query语句中使用from dual 或不含任何from子句
五、单表查询优化(索引失效)
1.全值匹配很快捷
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
2.最佳左前缀
建立了个复合索引(name,age,pos)
按照顺序,索引没问题
--查询
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
不按顺序,索引出问题
--条件跳过了name
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
总结
- 以上得出,查询的字段与索引字段的顺序不同,导致索引失效
- 原因:使用复合索引,如果索引了多列,要遵守最左前缀法则,指的是查询从索引最左前列开始并且不跳过索引的列
- 结论:过滤条件where使用索引必须按照建立索引的顺序,依次满足,一旦跳过某个字段,该字段后面的索引都无法使用
3.索引列上不计算
不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），可能会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July';
4.范围之后全失效
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age >25 AND pos = 'dev';
总结
- 建议:可以尽量将范围查找的字段的索引顺序放在最后面
- 结论:使用范围查找后,范围内的记录过多,可能会导致全表扫描,因为自定义索引映射到主键索引需要消耗的时间太多,反而不如直接全表扫描来得快
5.覆盖索引多使用:不用select *
- 尽量使用覆盖索引,只按顺序查询复合索引的各个字段(查询列和索引列一致),减少出现select *
-
覆盖索引直接从索引里面取值,出现了using index,索引可以用来读取数据,不用利用索引进行查找
image-20200905072819820.png
6.使用<>会失效:致扫全表
在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描
7.使用NULL值要小心
IS NULL或者IS NOT NULL时，可能会导致索引失效
- IS NULL 不会导致索引失效
-
IS NOT NULL 会导致索引失效
image-20200905082808660.png
8.模糊查询加右边
要使用模糊查询时，百分号最好加在右边，而且进行模糊查询的字段必须是单值索引
--百分号最好加在右边
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
- 可以看出，索引为单值索引且模糊查询语句在最右边时，索引才会生效
- 其他情况均失效了(%string || %string%),但是有时必须使用%string%，那么就需要用覆盖索引来解决问题了
9.字符串加单引号
-
当where字段为字符串varchar时，查询时必须带上<u>单引号</u>。否则底层会发生自动的类型转换,索引失效从而触发全表扫描
整型转为varchar是严重错误的
底层原理,varchar类型的name = 2000,为了使用索引来快速查找2000,那么原本是varchar索引树就会重构,但是索引树类型重构varchar -> int的代价是非常大的,mysql会认为直接全表扫描快点
varchar转为整型规则(mysql自动识别)
除数字的字符类保留,其他通通转成0
int a = 'a',则mysql会自动优化成a = 0
int a = '1000' a=1000 -
不加单引号'',导致发生自动的类型转换致使索引失效
image-20200905094804587.png
10.尽量不用or查询
- 如果使用or，可能导致索引失效。所以要减少or的使用，可以使用 union all 或者 union 来替代：
-
or导致索引失效
image-20200905095513543.png
11.总结补充
巧记(select 字段 where a = 1 and b = 1)
- 覆盖不用select *
- 最左前缀顺序-头不死--中不断
- 索引列少计算和强转(left() || int->varchar)
-
范围不要,范围后都失效
- or,exist,in,<>,!=
- not null
- like
