- sql优化笔记
- 检测问题常用工具
- 检查问题常用语句
- 不常用但好用的工具
- hash索引
- 索引合并
- 联合索引设计技巧
- limit优化
- 执行策略
- 存在问题
- 优化
- 查询优化器的限制
- in (子查询)
- union
- in
- 最大值最小值
- 检测问题常用工具
- 查询优化处理的限制
- join算法
- Explain
- id
- select_type
- table
- type
- possible_key
- key
- key_len
- ref
- rows
- filtered
- Extra
检测问题常用工具
检查问题常用语句
msyqladmin mysql客户端，可进行管理操作
mysqlshow 功能强大的查看shell命令
show [SESSION | GLOBAL] variables 查看数据库参数信息
SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息
information_schema 获取元数据的方法
SHOW ENGINE INNODB STATUS Innodb引擎的所有状态
SHOW PROCESSLIST 查看当前所有连接session状态
explain 获取查询语句的执行计划
show index 查看表的索引信息
slow-log 记录慢查询语句
mysqldumpslow 分析slowlog文件的
show table status like '$表名' 查询表信息,数据/索引大小
show status like '%lock%' 查看锁状态
复制代码不常用但好用的工具
zabbix 监控主机、系统、数据库（部署zabbix监控平台）
pt-query-digest 分析慢日志
mysqlslap 分析慢日志
sysbench 压力测试工具
mysql profiling 统计数据库整体状态工具
Performance Schema mysql性能状态统计的数据
workbench 管理、备份、监控、分析、优化工具（比较费资源）
复制代码hash索引
采用hash算法对索引字段进行hash,类似hashmap的形式
优点:
- 速度快,理论上时间复杂度o(1)
- 磁盘io少,索引中不存放行数据(一个磁块能存放更多索引)
缺点:
- 排序不友好,索引数据顺序与索引值顺序不一致(索引值1,2,3,4,按4取余得索引数据,1,2,3,0)
- 只支持对全文索引,所以当查询条件不包含全部索引字段时无法使用hash索引(复合索引不支持左缀原则)
- 只支持等值匹配( a = *),不支持范围查询(<>)
- 出现严重的hash碰撞时,性能大幅下降
索引合并
5.0之后增加的特性,当查询条件满足多个索引采用合并索引方式进行查询
联合索引设计技巧
CREATE TABLE `user_item` (
`id` bigint(20) NOT NULL,
`age` smallint(6) NOT NULL,
`name` varchar(10) NOT NULL,
`gender` tinyint(4) DEFAULT NULL,#性别 0男 1女
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码-
原则上,联合索引中区分度高的字段放在前面.实际根据业务场景.(力求索引能满足所有业务查询)
例子:
age,gender都可视为枚举类型区分度很低,create_time,name区分高
根据基本原则创建联合索引(create_time,age,gender)
假设实际场景中,gender,age查询频率很高,大部分查询条件中都有gender和age
所以实际创建联合索引(gender,age,create_time)
-
当由于业务限制导致区分度低的字段在联合索引的前边时,可采用in(*,*,*)方式补全前缀以利用联合索引(如性别 类型等,注意:当in()中组合数量超过一定限制时,mysql会走全表扫描)
示例:
假设当前查询条件where age=18
不符合左缀原则,此时无法命中联合索引
由于gender是枚举类型,此时可以通过where age=18 and gender in(0,1) 来命中联合索引
反例:
假设当前查询条件where create_time>'2018-01-01 00:00:00'无法命中索引
故使用上述方法where create_time>'2018-01-01 00:00:00' and age in(1,2,3…100) and gender in(0,1)
由于age*gender产生100*2=200种组合,故sql优化器选择走全表扫描
-
范围查询字段最好在联合索引的后面,因为联合索引中范围查询字段后面的字段只能充当index_filter
limit优化
执行策略
#假设name字段能命中索引
select * from table where name='123' limit 10000,10
复制代码- 通过index_name索引得到所有符合条件行主键
- 由于要获取所有字段,故索引不覆盖,通过主键查询主键索引
- 通过主键索引获取前10010行数据,丢弃前10000行数据,得到10条数据
存在问题
当当前分页数很大时,由于不能覆盖索引,所以要回到主键索引获取行数据进行过滤,产生不必要的数据读取
优化
使用覆盖索引,减少主键索引的查询
select * from table join (select id from table where name='123' limit 10000,10) as x using(id)
复制代码- 通过index_name索引得到所有符合条件行主键
- 由于索引覆盖,故在index_name索引上获取前10010行数据,再丢弃前10000行数据,得到分页数据行主键
- 通过主键索引获取分页数据行
推测:对比原方式,减少了访问主键索引(随机io减少)
查询优化器的限制
in (子查询)
mysql不能很好的优化in (子查询)的方式,往往使用join的方式效率更高
但并不绝对,实际还是通过explain来判断优化结果
示例:
select * from table a where id in (select id from table where id =2)
这是原语句,通常我们理解为先执行子查询再执行外部查询,由子查询驱动外部查询
但实际优化结果可能不这样
select * from table a where exists(select id from table b where id =2 and a.id = b.id)
由于子查询依赖于外部查询结果(a.id=b.id),所以实际执行结果为先执行外部查询,然后对外部查询结果遍历,
将每条结果传入子查询进行查询,由外部查询驱动子查询
所以导致效率极低
复制代码mysql5.7中貌似对子查询进行了优化,不确定!
union
mysql不能很好的优化union
示例:
(select name from tableA ) union all (select name from tableB) limit 20;
对于上述语句的执行过程
1.查出tableA的所有数据
2.查出tableB的所有数据
3.执行union all(存在临时表中 数据总量tableA+tableB)
4.取前20条
但其实我们希望优化做的是直接查出前20条然后union all,再取前20条
因此要这么写
(select name from tableA limit 20) union all (select name from tableB limit 20) limit 20;
复制代码in
对于where id in (1,2,3) or id in (4,5,6)这种情况
sql优化器会试图将其转化为等值查询,以笛卡尔积的方式得到3*3=9中等值查询语句,此时可以通过索引进行快速查询
但是当笛卡尔积过大的时候,sql优化器会通过全表扫描的方式而不走索引来获取数据
最大值最小值
对min()和max()的优化,mysql做的不够好
#id为主键 name上无索引
select min(id) from table where name ='123'
复制代码由于name上无索引,所以会走全表扫描方式获取所有满足条件的数据,再得到min(id).
但其实id上有主键索引,所以通过走主键索引,满足name='123'的id就肯定是最小值了.
此时可以这么写
#其实这条语句并不能很好的表达我们的意图
#不过为了更高的效率我们也别无选择
select id from table force index(id) where name ='123' limit 1
复制代码查询优化处理的限制
基于查询成本的预测，选择成本最小的查询方式，但是预测可能出错，出错可能来源于：
- 统计信息不准确：比如因为MVCC对一条记录可能存在多份，所以InnoDB统计受影响行数并不准确
- 预估不准确：MySql的查询优化只是预测，所以对于IO操作，可能执行的时候存在读缓存、内存，顺序读，但MySql在预测的时候并不知道
- 基于规则的优化局限：某些情况下，MySql不会基于成本优化，而是基于规则，比如存在全文搜索Match()子句的时候，则在存在全文索引时一定是用全文索引，即便其他索引和where条件可能更快
- 对未知成本不考虑：如储存过程和用户自定义函数
- 不考虑其他并发执行的查询
join算法
- nest-join :嵌套循环 o(n^2)
- sort-merge-join :排序合并(两个指针对比) o(nlogn)
- hash-join :利用hash表,当发生碰撞是取出 o(n)(hash表查询是o(1),遍历表是o(n))
Explain
id
标记select所属的行
使用临时表进行union操作时为null
select_type
显示简单/复杂查询
simple:不包含子查询和union
其他:复杂查询
复制代码table
查询访问哪个表
type
关联方式(查询方式)
all:全表扫描,遍历全表
index:索引扫描,按照索引顺序遍历.
优点:有序(利于order排序)
缺点:当通过二级索引扫描,且为非覆盖索引时,会再访问主键索引(随机io),开销非常大.为覆盖索引时Extras列显示Using Index.
range:有范围的索引扫描
注意:In()和Or也会显示为range,但实际上优化器可能会转化其为等值查询
ref:索引查找,非唯一索引或唯一索引的非唯一前缀(联合索引)
eq_ref:索引查询,唯一索引
const,system:优化器可以将查询条件转为常量查询,性能非常好
NULL:性能最好,可以直接通过索引完成查询
复制代码possible_key
可以命中的索引
key
优化器实际选择的索引
key_len
使用的索引长度
ref
显示了在索引中用于查询的字段或常量
rows
优化器预估的需要扫描的行数
filtered
根据当前查询条件过滤剩余的行数(理论上越小越好)
Extra
额外信息
using index: 覆盖索引
using where: 存储引擎检索再进行过滤(联合索引只命中前缀的情况)
using temporary: 使用临时表
using filesort: 使用外部索引排序(无法按索引顺序直接读取)
复制代码