从SQL Server到MySql(7) : 查询性能优化

1. 查询的过程

• 查询的生命周期: 从客户端, 到服务器, 然后在服务器上进行解析, 生成执行计划, 执行, 返回结果给客户端.
• 耗时的地方: 网络, CPU计算, 生成统计信息和执行计划, 锁互等(互斥等待).

2. 优化数据访问

• 访问的数据太多是低性能查询的根源.
  • 确认应用是否在检索大量超过需要的数据. 这可能是访问了太多的行或列.
  • 确认服务器是否在分析大量超过需求的数据行.

2.1 是否向数据库请求了不需要的数据

• 查询不需要的数据.
  • 典型情况: 先使用select 查询大量的结果,然后获取前面的N行后关闭结果集. 最好使用limit.
• 多表关联时返回全部列.
• 总是取出全部列.
  • 这会让优化器无法完成索引覆盖查询扫描优化, 并带来额外的I/O,CPU和内存消耗.
• 重复查询相同的数据.

2.2 Mysql 是否在扫描额外的记录

• 衡量查询开销的三个指标
  • 响应时间.
  • 扫描的行数.
  • 返回的行数.
• 响应时间
  • 等于服务时间和排队时间(等待I/O和锁)的和.
  • 在得到一个响应时间的值后, 先怀疑其�合理性.
• 扫描的行数和返回的行数
  • 它说明了查询找到需要的数据的效率.
• 扫描的行数和访问类型
  • 从表中找到某一行数据的成本(对扫描的需求).
  • explain 中的type 列反应了访问类型.
  • 应用where 条件的三种方式, 从好到坏:
    • 在索引中使用where 过滤. 这在存储引擎层完成.
    • 使用索引覆盖扫描来返回记录. 在服务器层完成, 无需回表查询.
    • 从数据表返回数据,然后进行过滤. 服务器层完成.

3. 查询的执行

3.1 客户端/服务器通信协议

• "半双工的": 同一时刻只能发生一个方向的数据发送.
  • 优势是简单快速
  • 限制: 无法进行流量控制.
    • 客户端用一个单独的数据包将查询传递给服务器, 接着就只能等待结果, 且必须完整的接收整个返回结果. 而不能在中途停止数据接收.
    • 所以, max_allowed_packet 参数, 以及查询语句中的limit 限制是特别重要的.
• Mysql 要等所有的数据都已经发送给客户端时才能释放这条查询锁占用的资源.
  • 接收全部结果并缓存
    • 能够让查询早点结束, 减少服务器压力.
    • 但当结果集很大时,会耗费更多的时间和内存.
  • 尽早开始处理结果集, 逐行获取需要的数据
    • 节省库函数处理查询所需的内存和时间.
    • 缺陷是会在和客户端交互的整个过程中占用服务器资源.
• Mysql 连接(线程) 的状态
  • sleep, query, locked, analyzing and statistics, coping to tem table, sorting table, sending data.

3.2 查询缓存

• 在解析查询语句之前, 如果查询缓存是打开的, Mysql 会优先检查该查询是否命中了查询缓存中的数据.
  • 检查是通过一个对大小写敏感的哈希查找实现的.
  • 若有命中, 再检查用户权限.
  • 如果都没问题, 跳过后续阶段, 直接从缓存中拿结果并返回给客户端.

3.3 查询优化处理

• 过程: 将SQL 转换成一个执行计划, 再依照该执行计划和存储引擎进行交互.
  • 子阶段: 解析SQL, 预处理, 优化SQL 执行计划.
  • 过程中任何错误都可能终止查询.
• 语法解析器和预处理
  • 解析器生成一颗"解析树".
  • 预处理器根据Mysql 规则进一步检查解析树是否合法.
• 查询优化器.

  • 一个查询有多种执行方式, 最后都返回相同的结果.

  • 优化器会从中找出最好的执行计划.

  • Mysql 使用基于成本的优化器. 评估成本时不考虑任何层面上的缓存.

    • 导致优化器选择错误的原因:
      • 统计信息不准确.
      • 成本估算不等于实际的执行成本(访问数据的成本不同).
      • 有时无法估算所有可能的执行计划.
      • 不会考虑不受其控制的操作的成本. 如执行存储过程和用户自定义函数的成本.
      • 有时是基于固定的规则而非成本进行的优化. 如存在Match()就一定会使用全文索引,即使别的索引会更快.
      • Mysql 的最优可能不是我们想要的最优. 我们想要的是最快的,而Mysql 只是基于成本上的最优.

  • Mysql 能够处理的优化类型

    • 重新定义关联表的顺序.
    • 将外连接转换为内连接.
    • 使用等价变化规则.
    • 优化Count, Min, Max 函数.
    • 预估并转换为常数表达式.
    • 覆盖索引扫描.
    • 子查询优化.
    • 提前终止查询.
    • 等值传播.
    • 列表IN() 的比较.
      • 多数数据库的In() 完全等同于多个OR 条件的子句, 其复杂度为O(n).
      • Mysql 会先排序,再二分查找确定列表值是否满足条件. 复杂度为O(log n).

  • �大多数情况下, 让优化器按自己的方式工作. 除非发现它进行了错误的优化, 并且知道原因时,再进行手工干预.

  • 数据和索引的统计信息.

    • 优化器存在于服务层, 而统计信息是由存储引擎构建并传递给优化器的.

  • Mysql 的关联查询.

    • Mysql 的概念中, 每个查询都是一次关联.
    • 会将一系列的单个查询结果放入一个临时表中,然后执行"嵌套循环关联" .

  • select t1.c1, t2.c2 from t1 
    inner join t2 Using (c3) where ...; 
    
    outer_iter = iterator over t1 where...
    outer_row = outer_iter.next
    while outer_row
        inner_iter = iterator oever t2 where ....
        inner_row = inner_iter.next
        while inner_row
          ....
          inner_row = inner_iter.next
        end
      outer_row = outer_iter.next
    end
    

    * 本质上, 所有类型的查询都以此类方式运行. 
    * `但全外连接是个例外, 因为它无法通过嵌套循环和回溯的方式完成. 所以Mysql 并不执行全外连接.`
      * full join :表中数据=内连接+左边缺失数据+右边缺失数据.
  * 执行计划
    * Mysql 并不会生成查询字节码来执行查询, 而是生成查询的一颗指令树, 然后通过存储引擎执行完成这颗指令数并返回结果. 
    * 最终的执行计划, 包含了重构查询的所有信息.
  * 关联查询优化器
    * 由于Mysql 嵌套循环方式的关联查询执行方式, 所以关联顺序变得非常重要.
    * 关联优化器会尝试在所有的关联顺序中选择一个成本最小的执行.
      * 但是, 当管理表过多时, 只能使用"贪婪" 搜索方式查找最优值.
      * 当10个表进行关联时,. 一共有3628800种不同的关联顺序.
  * 排序优化
    * 当无法使用索引进行排序时, Mysql 需要进行排序, 成为文件排序(可能在内存,硬盘中进行,根据数据量大小).
      * 内存不够时, 将数据分块, 对每个块使用"快速排序"并将结果存放在磁盘上, 最后进行merge.
    * 两种排序算法.
      * 两次传输排序(旧版本)
        * 读取行指针和需要排序的字段, 对其进行排序. 然后再更加排序结果读取所需要的数据行.
      * 单次传输排序(新版本)
        * 先读取查询需要的所有列, 然后再根据给定列进行排序, 最后直接返回排序结果.
    * `Mysql 进行文件排序时, 所需要使用的临时存储空间可能很大. 因�其对每个排序记录都会分配一个足够长的定长空间存放.`
  * 查询执行引擎
    * 经过了解析和优化阶段, 会生成执行计划(数据结构而非字节码). 查询执行引擎则根据这个执行计划来完成整个查询.
    * 过程: 简单地根据执行计划给出的指令逐步执行.
  * 返回结果给客户端
    * 即使没有结果数据, 也会返回一些查询信息,如影响的行数.
    * 增量, 逐步返回的过程. �开始生成第一条结果时,就逐步地进行返回.
    * 好处: 服务器无需存储太多结果. 让客户端尽快的得到了结果.

## 4. Mysql 查询优化器的局限性
### 4.1 关联子查询
* Mysql 的子查询实现的非常糟糕.
  * 其中, 最差的是where 条件中包含IN() 的子查询语句.

select * from people
where city_id IN(
select city_id from country where province = 'zhejiang')

  * 直觉上最优的执行方式:

Step1 : select city_id from country where name = 'shanghai'

Result : 4,5,6,7,8,9
Step2: select * from people where city_id IN (4,5,6,7,8,9).

  * Mysql 的做法: 将相关的外层表压到子查询中.

select * from people
where Exists(
select city_id from country where province = 'zhejiang'
Ande people.city_id = country.city_id)

    * 这会造成Mysql 无法先执行子查询. 而是先对people 执行全表扫描, 然后根据返回的city 行集合, 逐行执行子查询.
  * 改造

select * from people
Inner join country Using(city_id)
where province = 'zhejiang')

#### 4.2 Union 的限制
* Mysql 的限制: 无法将限制条件(如limit) 从外层"下推"到内层, 使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上.
` (select * from t1) Union All (select * from t2) limit 20 `
* 执行过程: 首先将t1 和t2 所有符合条件的结果存放在临时表中, 让其年后从临时表中取出前20条.
` (select * from t1 limit 20) Union All (select * from t2 limit 20) limit 20.`
* 直接在筛选t1,t2 的结果时, 只取前20 条到临时表中.

#### 4.3 在同一表上查询和更新
* Mysql 的限制: 不允许对同一张表同时进行查询和更新. 
` Update tbl as outer_tbl set 
cnt = (select count(*) from tbl as inner_tbl where outer_tbl.type = inner_tbl.type);` 
  * 该条Sql 是无法执行的, 适用生成表的形式来绕过上面的限制:
` Update tbl Inner join 
( select type, count(*) as cnt from tbl group by type) as der Using(type) 
set tbl.cnt =der.cnt;`

#### 4.4 最大值和最小值优化
* Mysql 对Min() 和Max() 查询�并没有做很好的优化.
` select Min(id) from people where name ='haha' `
  * 由于name 字段上并没有索引, 因此会进行一次全表扫描.
  * 其实, 进行一次主键扫描, 当找到第一个name为haha 的记录其实就是正确的最小值.
` select id from people use Index(primary) where name = 'haha' limit 1`
  * 缺点是通过SQL, 并不能看出其本意是取最小值.

#### 4.5 松散索引扫描
* 类似于Oracle 中的skip index scan.
* Mysql 并不支持松散索引扫描, 也就无法按照不连续的方式扫描一个索引.
  * 索引扫描需要先定义个起点和终点.
* 例如` where b between 2 and 3;` 如果索引的前导字段是列a. 那么无法被使用, 只能全表扫描.
* 而根据索引的存储特性, 其实可以使用跳跃的方式来进行查询
  * 先扫描a列的第一个值对应的b列的范围, 然后再跳到a列第二个不同值来扫描对应的b列的范围.
* 可以**给前列的加上可能的常数值**的方式来绕过该限制.
* 在5.6 版本后, 使用**索引条件下推**的方式, 可以解决松散索引扫描的一些限制.