高性能MYSQL读书笔记（六-2）

wuxiren
235 阅读13分钟

高性能查询的三个主要因素：查询优化、索引优化、库表结构优化

6.2  慢查询源头

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

2.是否扫描了额外的记录行


如果发现扫描大量的数据但是只需要返回少量行

1.使用索引覆盖扫描

2.修改库表结构，例如使用单独的汇总表

3.重写这个复杂的查询，让mysql的优化器能够以更优化的方式执行语句


6.3  重构查询的方式

1.一个复杂查询是否能拆分成多个简单查询（在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总认为网络通信、查询解析和优化是代价很高的事情，其实对于当前的mysql来说并不适用）

2.切分查询（对于一个大的复杂的查询，可以考虑分而治之 - 例如删除分步进行）

3.分解关联查询（在应用中进行对数据进行逻辑处理）


6.4  查询执行的基础


1.客户端发出一条查询命令给服务器

2.服务器检查查询缓存，如果命中则直接返回，否则继续

3.服务器进行sql解析，预处理，再由优化器生成执行计划

4.mysql根据执行计划，调用存储引擎的API执行查询

5.将结果返回


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

mysql的服务器与客户端之间的通讯协议是半双工的，这意味着在任何一个时刻，数据传输是单向的，由服务器到客户端或者客户端到服务器，两个动作不能同时发生。这样确实会简单快速，但很明显不能进行消息切分，一段数据开始传输必须传输完成才行。

一旦客户端进行了请求，它能做的事情只有等待。而服务器一旦开始传输数据时，客户端则必须接收全部响应。


查询状态

  • Sleep：线程正在等待客户端发送新的请求
  • Query：线程正在执行查询或者正在将结果返回给客户端
  • Locked：在服务器层，该线程正在等待表锁。在存储引擎级别的锁，不会体现在线程状态中
  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Copying to tmp table ：线程正在执行查询，并将其结果集复制到一个临时表中，这种状态一般是在group by，或者文件排序，或者union操作。
  • Sorting result：线程正在对结果集进行排序
  • Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回

6.4.3  查询处理优化

  • 语法解析器和预处理，mysql通过关键字对语句进行解析生成一个对应的“解析树”。解析器将会对其语法进行校验和解析查询（主要验证语句的合理性）
  • 查询优化器：从多种执行方式中找到最合理的执行计划。它将尝试预测一个查询使用执行计划的成本，并选择其中代价最小的一个。

有多种因素导致优化器选择错误的执行计划

1.统计信息不准确，（因其mvcc架构不能维护数据表的行数准确信息）

2.执行计划中的成本估算不等同于实际执行的成本。即使估计信息追缺，也无法保证执行计划是最优的（有时候某个执行计划虽然读取更多的页面，但是它的成本却更小，因为这些页面都是顺序读或者都在内存中，它的访问成本就很小）

3.mysql的最优并不代表时间最短，mysql是基于成本模型选择最优的查询优化

4.mysql不考虑其他并发执行的查询，可能会影响当前的查询速度

5.mysql不会考虑不受其控制的操作的成本，例如执行存储过程或者用户自定义函数的成本

6.优化器有时候无法估算所有的执行计划，它可能会错过某些最优的计划


mysql能够处理的优化类型

  • 重新定义关联表的顺序
  • 将外连接转换成内连接
  • 使用等价变换原则
  • 优化count、min、max函数
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询（使用limit时）
  • 等值传播
  • 列表in的比较

mysql的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意，union也是如此

执行计划

和很多关系型数据库不同，mysql并不会生成查询字节码来执行查询。mysql生成查询的一颗指令树，然后通过存储引擎完成这颗指令树并返回结果

关联查询优化器（可使用STRAIGHT_JOIN强制使用自定义关联顺序，当表关联数量过大时，可能的关联计划太多，会导致成本过高，优化器会选择使用"贪婪"搜索方式，当表关联数量超过一个参数配置时）

Mysql优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序，在多表关联查询时，他会有多种关联顺序来得到结果，但是在优化器进行评测后他会选择成本最小的一个关联顺序来执行。

排序优化

当排序的数据无法使用索引时，数量量小则使用内存进行快速排序，数据量大则使用磁盘排序。且进行分割划分，每个独立的模块进行快速排序，最后进行汇总合并。

有如下两种排序算法：两种算法有各自的适用场景，当查询的所需要列的总长度不超过参数时，mysql使用单次传输

  • 两次传输排序（旧版本）：读取行指针和需要的排序字段，对其进行排序，然后再根据排序结果读取所需要的数据行，这需要进行两次数据传输。也就是排序数据量小，方便排序，但是排序完之后要进行回表操作，会产生大量的随机io读取。
  • 单次传输排序（新版本，4.1版本以后）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。这样会导致排序的数据量太大，排序缓慢。

mysql在进行排序时所需要的临时存储空间其实会很大，因为他对每行记录会使用“定长空间”来存储，也就是记录里的最大的那条记录空间，对空间有很大的浪费。

关联查询时有两种方案排序

  • 一种是order by的列都在第一张表中，在查询时直接进行排序处理
  • 除此之外，mysql都会将关联的结果放到一个临时表中，然后等所有关联查询结束再进行排序操作

mysql5.6做了一些改进，当只需要返回部分排序结果时，例如limit的使用，mysql不会对所有结果进行排序，而是根据实际情况，抛弃不满足条件的结果再进行排序

6.4.4  查询执行引擎

存储引擎实现接口化，在生成执行计划后，吧执行计划交给存储引擎就能进行处理操作。所以存储引擎的功能非常丰富，但是底层接口只有几十个，但是也会带来一些限制。

6.4.5  返回结果给客户端

mysql结果的返回是增量的，当产生第一条数据时就可以开始传输。这样有两点好处

  • 服务器无需存储太多数据结果，也节约了内存
  • 客户端也能更早的得到部分结果，而不是等待全部一起传递

6.5  查询优化器的局限性

6.5.1  关联子查询

IN加子查询，性能效果经常（并不绝对）非常糟糕，通常建议使用exist来代替

6.5.2  UNION的限制

第一种与第二种写法区别，在临时表取的数据量完全不一致，比如test表有300条记录，test2表有500条记录，第一种写法会取800条记录到临时表，在进行排序取记录，而第二种只有四十条

(select a.id form test1
order by a.id )
union all
(select a.id form test2
order by a.id )
limit 20
(select a.id form test1
order by a.id 
limit 20)
union all
(select a.id form test2
order by a.id 
limit 20)
limit 20

6.5.3  索引合并优化

在5.0版本之后，当where自居中包含多个复杂条件时，mysql能访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行

6.5.4  等值传递

某些时候，等值传递会产生意想不到的额外消耗。

6.5.5  并行执行

mysql无法利用多核优势来并行查询

6.5.8  最大值最小值优化

select MIN(actor_id) from sakila.actor where first_name = 'pony'

因为在first_name字段上没有索引，因此mysql会进行一次全表扫描，可以考虑移除min使用limit实现

select actor_id from sakila.actor use INDEX(primary)

where first_name = 'pony' limit 1;


6.6  查询优化器的提示（hint）

如果对优化器的执行计划不满意，可以使用优化器提供的几个提示来控制最终的执行计划

    1. high_priority和low_priority:当多个语句同时访问一个表的时候，那些语句的优先级相对高一些，那些语句的优先级相对低一些。当high_priority作用域select语句时，mysql会将这个语句重新调度到所有正在等待表锁以便修改数据的语句之前。这两个参数只对使用表锁的存储引擎有效。不能再InnoDB或者其他有细粒度的锁机制和并发机制的引擎中使用
    2. delayed：只对insert和replace有效，mysql会将使用该提示的语句立即返回给客户端，并将插入的行数放入到缓冲区，然后在表空闲时期将数据写入。适用日志系统及需要写入大量数据但客户端无需等待单条语句完成IO的应用
    3. straight_join：放在select关键字后（让查询中所有表按照语句中标的顺序关联执行），也可以放在任何两个关联表的名字之间（固定其前后两个表的关联顺序）
    4. sql_small_result和sql_big_result：只对select有效，他们告诉优化器针对grop by或者distinct如何使用临时表及排序。sql_small_result提示数据小，可以将结果集放在内存中直接进行排序操作，而sql_big_result则提示数据量大，要在磁盘继续排序操作。
    5. sql_buffer_result:提示告诉优化器将结果放入到临时表，然后尽快释放表锁。也就是将查询结果进行服务器缓存，会占用服务器的内存
    6. sql_cache和sql_no_cache：这个提示告诉mysql这个结果集是否应该缓存在查询缓存中
    7. use index、ignore index、force index：提示优化器使用或者不使用那些索引来查询记录，force index与use index基本相同，force index会告诉优化器选择全表扫描的成本会远高于索引扫描。

私自使用关键字来修改优化器对执行计划的选择很多时候收效甚微，且给维护带来很多麻烦，当mysql升级时问题尤为明显，我们设置的”优化器提示“会让新版本的优化策略失效

6.7  优化特定类型的查询

6.7.1  优化count类型查询

  • count有两种用法，可以统计列出现的总数，也可以统计行的总数，而conut(*)就是统计行数
  • 有一种看法MyISAM的count函数总是很快，其实是有前提条件，只有没有任何where条件的count才是很快，带上where的条件子句后与其他的存储引擎无差别
  • 使用近似值：有时候业务场景并不是需要精确的count值，此时可以用近似值代替。explain出来的优化器估算就是一个不错的近似值，执行explain并不是真正的语句执行，成本低，如每三十分钟统计一次在线数量

6.7.2  优化关联查询

  • 确保on或者using子句上的列都有索引
  • 确保任何group by和order by只涉及一个表中的列，这样mysql才有可能使用索引来优化这个过程
  • 当升级mysql的时候需要注意：关联语法，运算符优先级等其他可能发生变化的地方

6.7.3  优化子查询

尽量使用关联查询代替子查询（5.6版本后优化器会转化）

6.7.4  优化group by和distinct

在mysql中，当无法使用索引时，group by会使用两种策略来完成：使用临时表或者文件排序来做分组

如果实在无法优化，可以考虑在客户端中进行业务排序及分组

6.7.5  优化limit分页

在系统需要进行分页操作时，通常是使用limit加上偏移量的办法实现，同时加上order by子句，如果没有索引，要进行文件排序。

当偏移量很大时，如limt 10000,20则是需要查询10020条记录却只需要20条记录，这样代价很高，要优化这样的查询，要么在页面限制分页的数量，要么优化大偏移量的性能。即尽可能使用覆盖索引扫描，而不是查询所有的列。或者修改实现方式,增加判断

select * from test
order by id desc limit 10000,20

select * from test
where id > 9999
order by id desc limit 10000,20


6.7.7  优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，除非确实需要服务器消除重复的行记录，否则就一定使用union all，如果没有all 关键字，mysql会给临时表加上distinct限制，这样代价很高

6.7.9  使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都在。可以使用下面的set和select来定义

set @one :=1
set @min_actor :=(select min(actor_id) from test.actor)
set @last_week :=current_date-interval 1 week;

使用：select ···where col < @last_week
  • 使用自定义变量的查询不能查询缓存
  • 不能在使用常量、或者标识符的的地方使用自定义变量，如表名，列名
  • 生命周期只在一个连接中有效，不能做连接中通信
  • 5.0版本前，大小写不转化
  • 不能显示声明自定义变量类型，


avatar
文章
阅读
粉丝