SQL优化笔记

254

检测问题常用工具

检查问题常用语句

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的形式

优点:

  1. 速度快,理论上时间复杂度o(1)
  2. 磁盘io少,索引中不存放行数据(一个磁块能存放更多索引)

缺点:

  1. 排序不友好,索引数据顺序与索引值顺序不一致(索引值1,2,3,4,按4取余得索引数据,1,2,3,0)
  2. 只支持对全文索引,所以当查询条件不包含全部索引字段时无法使用hash索引(复合索引不支持左缀原则)
  3. 只支持等值匹配( a = *),不支持范围查询(<>)
  4. 出现严重的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,#性别 01`create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. 原则上,联合索引中区分度高的字段放在前面.实际根据业务场景.(力求索引能满足所有业务查询)

    例子:

    age,gender都可视为枚举类型区分度很低,create_time,name区分高

    根据基本原则创建联合索引(create_time,age,gender)

    假设实际场景中,gender,age查询频率很高,大部分查询条件中都有gender和age

    所以实际创建联合索引(gender,age,create_time)

  2. 当由于业务限制导致区分度低的字段在联合索引的前边时,可采用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优化器选择走全表扫描

  3. 范围查询字段最好在联合索引的后面,因为联合索引中范围查询字段后面的字段只能充当index_filter

limit优化

执行策略

#假设name字段能命中索引
select * from table where name='123' limit 10000,10 
  1. 通过index_name索引得到所有符合条件行主键
  2. 由于要获取所有字段,故索引不覆盖,通过主键查询主键索引
  3. 通过主键索引获取前10010行数据,丢弃前10000行数据,得到10条数据

存在问题

当当前分页数很大时,由于不能覆盖索引,所以要回到主键索引获取行数据进行过滤,产生不必要的数据读取

优化

使用覆盖索引,减少主键索引的查询

select * from table join (select id from table where name='123' limit 10000,10) as x using(id)
  1. 通过index_name索引得到所有符合条件行主键
  2. 由于索引覆盖,故在index_name索引上获取前10010行数据,再丢弃前10000行数据,得到分页数据行主键
  3. 通过主键索引获取分页数据行

推测:对比原方式,减少了访问主键索引(随机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: 使用外部索引排序(无法按索引顺序直接读取)