查询优化,索引优化,表结构优化是一起进行的,不能只靠一个
“快速,精准和实现简单"三者永远只能满足其二,必须舍弃其中一个
查询性能低下的最基本的原因是访问的数据太多了
查询不需要的记录(加上limit限制)
多表关联返回所有列(只返回需要的列)
select *避免
重复查询相同数据(单条数据还好)
扫描的行数与访问类型
扫描表,扫描索引,范围访问,单值访问
慢——————————>快
使用where的方式
快 1索引完整的where -> 存储引擎完成
| 2覆盖索引扫描 -> MySQL服务器完成
慢 3从数据库中过滤where(回表) -> MySQL服务器完成
重构查询方式
将复杂查询分解成单个简单查询
切分查询(分段查,用limit,id,ctime限制范围)分解关联查询(不分页的都可以分解)
查询执行基础
查询流程概述
1MySQL客户端/服务器通信协议
"半双工”:任何时刻只能是服务器向客户端发数据,或客户端向服务器发数据,不能同时发
优点:简单快速
缺点:无法流量控制.就像是从"消防管道喝水”(因此大查询一般都是将结果存到内存,优先释放连接资源)
2查询缓存
- 大小写敏感的哈希查找实现
- 权限判断
3查询优化处理
语法解析与预处理
验证语法正确性
验证权限查询优化器
一条查询可以有很多种执行方式都返回相同的结果,优化器的作用就是找到其中最好的
MySQL使用基于成本的优化器,采用统计信息预测
静态优化:直接对解析树分析,完成优化(只进行一次)
动态优化:查询上下文有关(每次查询都需要优化)
部分优化举例:关联表顺序,等价交换规则,预估与转化常数表达式,优化count(),min()等函数,子查询优化,提前终止查询,等值传播,列表in()比较
总结 ==>不要自以为比优化器更聪明关联查询 —> 嵌套循环关联
执行计划 —> 左侧深度优先树
关联查询优化器 —> 决定多个表的关联顺序
排序优化 —> 尽量避免排序,尽量走索引排序
4查询执行引擎
将生成的执行计划(一种数据结构)逐步执行
5返回客户端
- 返回数据影响的行
- 缓存尝试
- 返回数据是一个增量的,逐步返回的过程,避免服务器内存占用
查询优化器的局限性
1关联子查询 —> 尤其是in()加子查询
如:select * from film where film_id in (select film_id from actor)
2union限制 —> 无法将限制条件从外层”下推”到内层
3索引合并优化
4等值传递 —> 大in()变关联查询
5哈希关联 —> MySQL不支持
6并行执行 —> 无法利用多核特性
7松散索引扫描 —> 跳跃索引不支持
8最大值和最小值优化 —> max()与min()转为limit 1走索引
9在同一个表上的查询和更新 —> 不支持同一张表同时进行查询和更新
查询优化器的提示(hint)
DELAYED —> 延迟插入数据(对于last_insert_id()失效)
STRAIGHT_JOIN —> 固定关联顺序
FOR UPDATE 和 LOCK IN SHARE MODE —> 显示锁
USE INDEX —> 索引提示
==> 最好不使用任何提示,优化器会不断升级的,伴随着升级,提示反而变成的阻碍
优化特定类型查询
1优化count()查询
count(*)忽略列信息,直接统计行信息
2优化关联查询
确保ON或者USING子句中的列上有索引,只需要在关联顺序的第二张表上创建索引
确保任何group by和order by表达式,只涉及到一个表中的列
3优化子查询
尽可能用关联查询替换
4优化group by和distinct
对关联查询分组通常使用查找表的标识列分组(就是B表的关联列,一般是id)
关联查询的子查询没有索引
5优化limit分页
走二级索引只查id,避免回表的无效扫描
6优化SQL_CALC_FOUND_ROWS
加hint,不推荐
7优化union查询
优先使用union all
8使用自定义变量
避免重复查询刚刚更新的数据
如:update t set lastUpdate = NOW() where id=1 and @now :=NOW();
select @now
统计更新和插入的数量
如:insert on duplicate key update时可以获取具体的更新与插入的行数
总结
- 在数据库层面尽量不做,少做,让应用程序多做 —> 转换思想,比如提前存储相关计算值,在应用程序转换,避免函数,走索引等
- 尽可能快的完成需要做的事 —> 事务update…select 而不是 select…for update
- 某些无法优化的查询,尝试改变策略
- 一些没法走索引的查询,可以先建立近似值索引,通过索引过滤到一个小范围内,再通过精准匹配数据
