devbox
羊村懒王
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SQL优化笔记

作者：羊村懒王 | 2024-06-05 00:32:23

赞

踩

sql优化详细笔记

检测问题常用工具

检查问题常用语句

  1. msyqladmin                                 mysql客户端，可进行管理操作
  2. mysqlshow                                  功能强大的查看shell命令
  3. show [SESSION | GLOBAL] variables          查看数据库参数信息
  4. SHOW [SESSION | GLOBAL] STATUS             查看数据库的状态信息
  5. information_schema                         获取元数据的方法
  6. SHOW ENGINE INNODB STATUS                  Innodb引擎的所有状态
  7. SHOW PROCESSLIST                           查看当前所有连接session状态
  8. explain                                    获取查询语句的执行计划
  9. show index                                 查看表的索引信息
  10. slow-log                                   记录慢查询语句
  11. mysqldumpslow                              分析slowlog文件的
  12. show table status like '$表名' 			 查询表信息,数据/索引大小
  13. show status  like '%lock%'				  查看锁状态
  14. 复制代码

不常用但好用的工具

  1. zabbix                  监控主机、系统、数据库（部署zabbix监控平台）
  2. pt-query-digest         分析慢日志
  3. mysqlslap               分析慢日志
  4. sysbench                压力测试工具
  5. mysql profiling         统计数据库整体状态工具    
  6. Performance Schema      mysql性能状态统计的数据
  7. workbench               管理、备份、监控、分析、优化工具（比较费资源）
  8. 复制代码

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之后增加的特性,当查询条件满足多个索引采用合并索引方式进行查询

联合索引设计技巧

  1. CREATE TABLE `user_item` (
  2.   `id` bigint(20) NOT NULL,
  3.   `age` smallint(6) NOT NULL,
  4.   `name` varchar(10) NOT NULL,
  5.   `gender` tinyint(4) DEFAULT NULL,#性别 0男 1女
  6.   `create_time` datetime DEFAULT NULL,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  9. 复制代码

  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优化

执行策略

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

存在问题

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

优化

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

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

推测:对比原方式,减少了访问主键索引(随机io减少)

查询优化器的限制

in (子查询)

mysql不能很好的优化in (子查询)的方式,往往使用join的方式效率更高

但并不绝对,实际还是通过explain来判断优化结果

  1. 示例:
  2. select * from table a where id in (select id from table where id =2)
  3. 这是原语句,通常我们理解为先执行子查询再执行外部查询,由子查询驱动外部查询
  4. 但实际优化结果可能不这样
  5. select * from table a where exists(select id from table b where id =2 and a.id = b.id)
  6. 由于子查询依赖于外部查询结果(a.id=b.id),所以实际执行结果为先执行外部查询,然后对外部查询结果遍历,
  7. 将每条结果传入子查询进行查询,由外部查询驱动子查询
  8. 所以导致效率极低
  9. 复制代码

mysql5.7中貌似对子查询进行了优化,不确定!

union

mysql不能很好的优化union

  1. 示例:
  2. (select name from tableA ) union all (select name from tableB) limit 20;
  3. 对于上述语句的执行过程
  4. 1.查出tableA的所有数据
  5. 2.查出tableB的所有数据
  6. 3.执行union all(存在临时表中 数据总量tableA+tableB)
  7. 4.取前20条
  8. 但其实我们希望优化做的是直接查出前20条然后union all,再取前20条
  9. 因此要这么写
  10. (select name from tableA  limit 20) union all (select name from tableB  limit 20) limit 20;
  11. 复制代码

in

对于where id in (1,2,3) or id in (4,5,6)这种情况

sql优化器会试图将其转化为等值查询,以笛卡尔积的方式得到3*3=9中等值查询语句,此时可以通过索引进行快速查询

但是当笛卡尔积过大的时候,sql优化器会通过全表扫描的方式而不走索引来获取数据

最大值最小值

对min()和max()的优化,mysql做的不够好

  1. #id为主键 name上无索引
  2. select min(id) from table  where name ='123'
  3. 复制代码

由于name上无索引,所以会走全表扫描方式获取所有满足条件的数据,再得到min(id).

但其实id上有主键索引,所以通过走主键索引,满足name='123'的id就肯定是最小值了.

此时可以这么写

  1. #其实这条语句并不能很好的表达我们的意图
  2. #不过为了更高的效率我们也别无选择
  3. select id from table  force index(id) where name ='123' limit 1
  4. 复制代码

查询优化处理的限制

基于查询成本的预测，选择成本最小的查询方式，但是预测可能出错，出错可能来源于：

  • 统计信息不准确：比如因为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

显示简单/复杂查询

  1. simple:不包含子查询和union
  2. 其他:复杂查询
  3. 复制代码

table

查询访问哪个表

type

关联方式(查询方式)

  1. all:全表扫描,遍历全表
  2. index:索引扫描,按照索引顺序遍历.
  3. 	优点:有序(利于order排序)
  4. 	缺点:当通过二级索引扫描,且为非覆盖索引时,会再访问主键索引(随机io),开销非常大.为覆盖索引时Extras列显示Using Index.
  5. range:有范围的索引扫描
  6. 	注意:In()和Or也会显示为range,但实际上优化器可能会转化其为等值查询
  7. ref:索引查找,非唯一索引或唯一索引的非唯一前缀(联合索引)
  8. eq_ref:索引查询,唯一索引
  9. const,system:优化器可以将查询条件转为常量查询,性能非常好
  10. NULL:性能最好,可以直接通过索引完成查询
  11. 复制代码

possible_key

可以命中的索引

key

优化器实际选择的索引

key_len

使用的索引长度

ref

显示了在索引中用于查询的字段或常量

rows

优化器预估的需要扫描的行数

filtered

根据当前查询条件过滤剩余的行数(理论上越小越好)

Extra

额外信息

  1. using index: 覆盖索引
  2. using where: 存储引擎检索再进行过滤(联合索引只命中前缀的情况)
  3. using temporary: 使用临时表
  4. using filesort: 使用外部索引排序(无法按索引顺序直接读取)
  5. 复制代码
本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号