《高性能MySQL》查询性能优化

前言

三驾马车：查询优化、索引优化、库表结构优化

为什么查询速度会慢

• 对应查询来说，真正重要的是响应时间
• 查询的大致生命周期
  • 客户端 -> 服务器（解析、生成执行计划、执行、返回结果）-> 客户端
  • 执行：包括了检索数据到存储引擎的调用以及调用后的数据处理（排序、分组等
• 查询需要在多个地方花费时间（网络、CPU计算、生成统计信息、执行计划、锁等待等
• 优化查询的目的就是减少和消除这些操作所花费的时间

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

• 查询低下最基本原因：访问的数据太多
• 分析步骤
  • 应用程序是否检索大量超过需要的数据（业务层面
  • MySQL服务器层是否分析大量超过需要的数据行（深度翻页 Limit 等

1. 是否向数据库请求了不需要的数据
   • 典型案例
     • 查询不需要的记录（请求1000条，只展示10条
     • 多表关联时返回全部列（通常是 SELECT * 导致
     • 总是取出全部列
       • 缺点：额外的 I/O 、内存和 CPU 消耗，同时无法使用索引覆盖扫描这类优化
       • 优点：能提高相同代码片段的复用性
     • 重复查询相同的数据
2. MySQL 是否在扫描额外的记录
   • 衡量查询开销的三个指标（已记录在慢日志中
     • 响应时间
     • 扫描的行数
     • 返回的行数
   • 响应时间 = 服务时间 + 排队时间
     • 服务时间：数据库处理这个查询真正花费的时间
     • 排队时间：等待某些资源而没有真正执行查询的时间（ I/O 操作、锁等待等
     • 受影响：存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等
   • 扫描的行数和返回的行数
     • 用于判断查询找到需要数据的效率
     • 理想情况下扫描的行数与返回的行数应该相同
   • 扫描的行数和访问类型
     • 访问类型：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用（慢 -> 快
     • MySQL 使用三种方式应用 WHERE 条件
       • 索引中使用 WHERE 条件过滤（存储引擎层
       • 索引覆盖扫描返回记录，无需回表查询（MySQL 服务层
       • 从数据表中返回数据，然后过滤不满足的条件（MySQL 服务器，先读出数据再过滤
     • 好的索引可以让查询使用合适的访问类型，尽可能只扫描需要的数据行
     • 当发现查询扫描大量行但只返回少数的行时，优化的技巧
       • 索引覆盖扫描
       • 改变库表结构（使用额外的汇总表
       • 重写复杂的查询

重构查询的方式

• 优化查询时，目标应该是找到一个更优的方法来获得实际需要的结果，而不是一定总数需要从 MySQL 获取一模一样的结果集

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

   • 需要考虑是否可以将一个复杂的查询分成多个简单的查询（类似于 MapReduce
   • MySQL 从设计上让 连接 和 断开连接 都很轻量级，返回小查询结果方面很高效

2. 切分查询

   • 大查询切分成多个小查询（分而治之
   • 大查询可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询
   • 小查询还可以减少 MySQL 复制延迟，将压力分散到一个很长的时间段中（削峰）

3. 分解关联查询

   • 含义：将原本需要在 MySQL 服务层的关联查询分解成多个单表查询，在应用层做关联

   • 分解优势

     • 缓存效率更高（单表缓存
     • 单个查询可以减少锁竞争
     • 更容易对数据库进行拆分，高性能和可扩展性
     • 查询本身效率也会提升（使用 IN ( ) 代替关联查询
     • 减少冗余记录的查询
     • 相当于在应用中实现了哈希关联，效率更高（MySQL 用的嵌套循环

查询执行的基础

MySQL 执行一个查询的过程