深入学习理解MySQL高级特性系列（四） -- 深入浅出查询性能优化_如何区分简单查询 复杂查询的响应时间 性能

一、为什么查询速度会慢

• 查询：一个任务，由一系列子任务组成
• 优化查询：优化子任务
  • 消除其中一些子任务
  • 减少子任务的执行次数
  • 让子任务运行得更快

二、慢查询基础：优化数据访问

1. 是否向数据库请求了不需要的数据（人为：SQL语法层面）

• 查询不需要的记录：MySQL先返回全部结果集再进行计算
• 多表关联时返回全部列：只取需要的列
• 总是取出全部列：SELECT *
• 重复查询相同的数据：初次查询后，将数据缓存

2. MySQL 是否在扫描额外的记录（MySQL层面）

• 衡量查询开销的指标：
  • 响应时间：服务时间 + 排队时间
    • 服务时间：数据库处理查询真正花了多长时间
    • 排队时间：服务器因为等待某些资源（IO操作、行锁）而没有执行查询的时间
  • 扫描的行数和返回的行数（理想情况下相等）
  • 扫描的行数和访问类型：EXPLAIN语句中的type列（从慢到快）
    • 全表扫描（ALL）
    • 索引扫描（index）
    • 范围扫描（range）
    • 唯一索引扫描
    • 常数引用（ref）
• 能够应用WHERE条件：从好到坏
  • 在索引中使用WHERE条件过滤不匹配的记录（存储引擎层）
  • 使用索引覆盖扫描（Using index）返回记录，无须回表（服务器层）
  • 从数据表中返回数据，然后过滤不满足条件的记录（Using Where）（服务器层）

三、重构查询的方式

1. 一个复杂查询还是多个简单查询

2. 切分查询

• 含义：将大查询切分成小查询，每个查询功能完全一样
• 举例：删除数据时（定期清除大量数据），将一个大的DELETE语句（需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞其他查询）切分成多个较小的查询（一次删除一万行数据）

3. 分解关联查询

• 对每一个表进行一次单表查询，然后将结果在应用程序中进行关联
• 优势：
  • 让缓存的效率更高（许多应用程序可以方便地缓存单表查询的结果对象）
  • 执行单个查询可以减少锁的竞争
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可拓展
  • 查询本身效率也可能会有所提升
  • 可以减少冗余记录的查询（关联查询可能需要重复地访问一部分数据）
  • 相当于在应用中实现了哈希关联（关联查询相当于嵌套循环关联）

mysql> SELECT * FROM tag ->  JOIN tag_post ON tag_post.tag_id=tag.id ->  JOIN post ON tag_post.post_id=post.id -> WHERE tag.tag='mysql'; 
1

可以分解成：

mysql> SELECT * FROM tag_post WHERE tag_id=1'; mysql> SELECT * FROM tag_post WHERE tag_id=1234; mysql> SELECT * FROM post WHERE post.id in (123,456,567,9098)  
1

四、查询执行的基础

MySQL执行一个查询的过程：

• 客户端发送一条查询请求给MySQL服务器
• 服务器先检查查询缓存（5.7版本之前），若命中缓存（完全一样），则立刻返回缓存中的结果，否则进入下一阶段
• 服务器端进行：
  • SQL解析，生成解析树