6查询性能优化

查询优化,索引优化,表结构优化是一起进行的,不能只靠一个

“快速,精准和实现简单"三者永远只能满足其二,必须舍弃其中一个

查询性能低下的最基本的原因是访问的数据太多了

1. 查询不需要的记录(加上limit限制)

2. 多表关联返回所有列(只返回需要的列)

3. select *避免

4. 重复查询相同数据(单条数据还好)

扫描的行数与访问类型

扫描表,扫描索引,范围访问,单值访问

    慢——————————>快

使用where的方式

    快  1索引完整的where  ->  存储引擎完成

     |  2覆盖索引扫描  ->  MySQL服务器完成

    慢  3从数据库中过滤where(回表)  ->  MySQL服务器完成

重构查询方式

1. 将复杂查询分解成单个简单查询

2. 切分查询(分段查,用limit,id,ctime限制范围)

3. 分解关联查询(不分页的都可以分解)

查询执行基础

查询流程概述

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时可以获取具体的更新与插入的行数

总结

1. 在数据库层面尽量不做,少做,让应用程序多做 —> 转换思想,比如提前存储相关计算值,在应用程序转换,避免函数,走索引等
2. 尽可能快的完成需要做的事 —> 事务update…select 而不是 select…for update
3. 某些无法优化的查询,尝试改变策略
4. 一些没法走索引的查询,可以先建立近似值索引,通过索引过滤到一个小范围内,再通过精准匹配数据