mysql数据库笔记（《高性能MySQL》）——查询性能优化

1. 优化数据访问：

1.1 检查是否向数据库请求了不需要的数据：
- 1.1.1 例如是否查询了不需要的数据（LIMIT）；
- 1.1.2 多表关联时返回全部列；
- 1.1.3 总是取出全部列（SELECT * ）；
- 1.1.4 重复查询相同的数据（可对常用查询做缓存）
1.2 MySQL是否在扫描额外的记录
- 1.2.1 响应时间：响应时间包含服务时间和排队时间，服务时间是指数据库处理这个查询真正所花时间，排队时间是指服务器因为等待某些资源（I/O和行锁）而没有真正执行查询的时间；
- 1.2.2 扫描的行数和返回的行数：理想情况下扫描的行数和返回的行数应该是相同的，扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常大；
- 1.2.3 扫描的行数和访问类型:
- - 1.2.3.1 EXPLAIN语句中的type列反应了访问类型，访问类型有很多种，速度从慢到快是：全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用
  - - 在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎层完成的。
    - 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在MYSQL服务器层完成的，但无须再回表查询记录。
    - 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）这是在MYSQL服务器层完成，MYSQL需要先从数据表读出记录然后过滤。
  - 1.2.3.3 如果发现查询需要大量的数据，但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
    1. 使用索引覆盖扫描，把所有需要的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
    2. 改变库表结构。例如使用单独的汇总表
    3. 重写复杂的查询，让MYSQL优化器能够以更优化的方式执行这个查询

2. 重构查询方式

- 2.1 一个复杂查询还是多个简单查询：由于MYSQL从设计上让连接和断开连接都很轻量，返回小查询结果很高效，现代网络速度也更快（通用服务器上能够运行每秒超过10万的查询，千兆网卡也能轻松满足每秒超过2000次的查询），所以运行多个小查询已经不是大问题。实际中需要衡量大查询是否有必要分解为多个小查询
- 2.2 切分查询：查询结果数据量比较大时，可进行切分，每个查询功能完全一样，每次只返回部分数据。例如定期清除大量数据，一次性完成可能会一次锁住很多数据，占满整个事物日志，耗尽系统资源，阻塞很多小而重要的查询。

例如 DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

可以用以下替换：

rows_affected = 0

do {

rows_affected = do_query("DELETE FROM messages WHERE created < DATE_SUB (NOW(),INTERVAL 3 MONTH) LIMIT 100000")

} while rows_affected > 0

- 2.3 分解关联查询：对关联查询进行分解，例如：

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'

可分解为：

select * from tag where tag = 'mysql';

select * from tag_post where tag_id = 1234;

select * from post where post.id in (123,456,789);

此重构方式有以下优点：

1.让缓存的效率更高。

2.将查询分解后，执行单个查询可以减少锁的竞争。

3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

4.查询本身效率也可能会有所提升。

5.可以减少冗余记录的查询。

6. 相当于在应用中实现了哈希关联，而不是使用mysql的嵌套关联

3. 查询执行的基础

mysql执行一个查询的过程：

1.客户端发送一条查询给服务器。

2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划。

4.MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5.将结果返回给客户端。

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

mysql客户端和服务器之前的通信协议时“半双工”的，这意味着在任何一个时刻，要么是由服务器向客户端发送数据，要么时由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让mysql通信简单快速，但是也限制了mysql，比如没办法进行流量控制。一旦一端开始发消息，另一端要接收完整个消息才能响应它。客户端用一个单独的数据包将查询传给服务器（查询语句很长时，max_allowed_packet很重要），一般服务器响应给用户的数据通常很多，由多个数据包组成，当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不能简单地只取前面几条结果然后让服务器停止发送数据。客户端从服务器获取数据是一个被动接收的过程。

查询状态：对于一个mysql连接或者说一个线程，任何时刻都有一个状态，该状态表示了mysql当前正在做什么，show full processlist命令可以查看状态（为返回结果中的Command）

Sleep ：线程正在等待客户端发送新的请求。

Query：线程正在执行查询或者正在将结果发送给客户端。

Locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁并不会体现在线程状态中，没有行锁的引擎中会出现（对MyISAM来说是一个比较典型的状态）

Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。

Copying to tmp table [on disk]：线程正在执行查询，并将结果集复制到一个临时表中，这种状态一般是在做GROUP BY操作，文件排序操作或是UNION操作，如果后面有“on disk”标记，则表示mysql正在将一个内存临时表放到磁盘上。

Sorting result：线程正在对结果集进行排序。

Sending data：表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者向客户端返回数据。

3.2查询缓存

如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的，即使只有一个字节不同，也不会匹配缓存结果（Percona版本的mysql可以先将注释移除再计算哈希值），如果命中了查询缓存，会在查询结果之前检查一次用户权限。

3.3查询优化处理

查询的声明周期的下一步是将一个SQL转换成一个执行计划，MYSQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。过程中任何错误都会终止查询。

3.3.1语法解析器和预处理：语法解析->预处理->权限验证

MYSQL通过关键字将SQL语句进行解析，并生成“解析树”，这个过程会验证是否使用错误的关键字，或者关键字的顺序是否正确，引号是否前后匹配等；

预处理则根据MYSQL规则进一步检查解析树是否合法，检查数据表和列是否存在，解析名字和别名是否有歧义；

下一步进行权限严重，通常很快（除非服务器上有很多权限配置）

3.3.2查询优化器：语法树合法->转化为执行计划（优化器的作用就是找到最好的执行计划）

3.3.2.1优化器有时可能会选择错误的执行计划：

1.统计信息不准确：MYSQL依赖存储引擎提供的统计信息来评估成本，有的引擎提供的信息偏差会很大，例如InnoDB因为其MVCC的架构，并不能提供精确的统计信息。

2.执行计划中的成本估算不等同于实际执行的成本：例如有时候某个执行计划虽然需要读取更多页面，但是成本却更小，以后这些页面都是顺序读或者页面都已经在内存中的话，访问成本将很小。MYSQL不能知道哪些页面在内存中和磁盘上，所以无法估计实际查询的成本。

3.MYSQL的最优可能和我们希望的不一样：我们希望时间尽可能短，但是MYSQL只是基于成本模型选择。

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

5.MYSQL并不是任何时候都基于成本优化，有时会基于一些固定规则，例如存在全文搜索的MATCH（）子句，则在存在全文索引的时候就是用全文索引，即使使用别的索引更快。

6.MYSQL不会考虑不受控制的成本：例如存储过程或者用户自定义函数的成本。

3.3.2.2MYSQL的优化策略可以简单的分为两种：静态优化和动态优化。

静态优化是一种 “编译时优化”，在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。

动态优化是一种 “运行时优化”，和查询的上下文或者WHERE条件中的取值，索引中条目对应的数据行数等很多其他因素有关，需要在每次查询的时候重新评估。

3.3.2.3MYSQL能处理的优化类型：

1.重新定义关联表的顺序

2.将外连接转化成内连接

3.使用等价变换规则：例如 5=5 AND a>5将被改写为 a>5

4.优化COUNT()、MIN() 和MAX()：例如找到某一列最小值只需要查询对应B-Tree索引最左端的记录，没有WHERE条件的count(*)查询通常也可以使用存储引擎提供的一些优化（MyISAM维护了一个变量来存放数据表的行数）

5.预估并转化为常数表达式

6.覆盖索引扫描

7.子查询优化

8.提前终止查询

9.等值传播

10.IN() ：其他数据库系统中，IN()完全等同于多个OR条件的子句，在MYSQL中则不同，MYSQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，操作复杂度为O(log n)，等价的转换成OR查询的复杂度则为O(n)，IN()列表中有大量取值时，MYSQL的处理速度将会更快

4. MYSQL查询优化器的局限性

4.1.MYSQL的子查询实现的非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。（MYSQL会将相关的外层表压到子查询中，详细说明见->224页）

4.2.UNION的限制：MYSQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上，例如如果希望UNION的各个子句能够根据LIMIT只取部分结果集，需要在UNION的各个子句中分别使用这些子句。

4.3.索引合并优化：当WHRER子句中包含多个复杂条件的时候，MYSQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

4.4.等值传递：某些时候等值传递会带来一些意想不到的额外消耗。（MYSQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个表的某个列相关联，那么优化器会将IN()列表都复制应用到关联的各个表中，IN()列表非常大的话会导致优化和执行都会变慢。）

4.5.并行执行：MYSQL无法利用多核特性来并行执行查询。

4.6.哈希关联：MYSQL并不支持哈希关联，MYSQL所有的关联都是嵌套关联

（多表之间的关联有三种方式：Nested Loops（嵌套循环关联）、Hash Join（哈希关联）、Sort Merge Join（排序合并关联））

4.7.松散索引扫描：MYSQL不支持松散索引扫描，无法按照不连续的方式扫描一个索引。

4.8.最大值和最小值优化：对于MIN()和MAX()查询，MYSQL的优化做得并不好，例如：

select min(actor_id) from actor where first_name = 'aaa';

因为first_name字段上并没有索引，MYSQL会进行一次全表扫描。所以可以重写为：

select actor_id from actor use index(primary) where first_name = 'aaa' limit 1; 此策略可以让MYSQL扫描尽可能少的记录，但是可读性差。

4.9.在同一个表上查询和更新：MYSQL不允许对同一张表同时进行查询和更新。但是可通过使用生成表的形式绕过限制。

5. 查询优化器的提示

5.1.HIGH_PRIORITY和LOW_PRIORITY：

HIGH_PRIORITY用于SELECT语句，MYSQL将此SELECT语句放在表的队列的最前面；还可用于INSERT语句，效果只是简单抵消了全局LOW_PRIORITY设置对该语句的影响。

LOW_PRIORITY用于SELECT、INSERT、UPDATE和DELETE语句，会让语句一直处于等待状态，只要队列中还有需要访问同一个表的语句。

这两个提示只对使用表锁的存储引擎有效，千万不要再InnoDB或者其他有细粒度锁机制和并发控制的引擎中使用。

5.2.DELAYED：

用于INSERT和REPLACE语句，MYSQL会将语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量写入数据，适用于日志系统等；局限：不是所有引擎都支持，会导致函数LAST_INSERT_ID()无法正常工作。

5.3.STRAIGHT_JOIN:

用于SELECT语句，（可放置在SELECT关键字之后，也可放置在任何两个关联表的名字之间）作用：让查询中的所有表按照在语句中出现的顺序进行关联；固定前后两个表的关联顺序（MYSQL版本升级时，需要重新审视此类查询）。

5.4.SQL_SMALL_RESULT 和 SQL_BIG_RESULT：

用于SELECT语句，告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。

SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，避免排序操作。

SQL_BIG_RESULT告诉优化器结果集可能会很大，建议使用磁盘临时表做排序操作。

5.5.SQL_BUFFER_RESULT：

用于SELECT语句，告诉优化器将查询放入到一个临时表，尽快释放表锁。

5.6.SQL_CACHE和SQL_NO_CACHE：

告诉MYSQL这个结果集是否应该缓存在查询缓存中（MYSQL8 删除了缓存功能，此语句不起作用）

5.7.SQL_CALC_FOUND_ROWS：

严格来说这并不是一个优化器提示，它会让MYSQL返回的结果集包含更多的信息。加上该提示后MYSQL会计算除去LIMIT子句后这个查询要返回的结果集的总数

5.8.FOR_UPDATE和LOCK IN SHARE MODE：

也不算是真正的优化器提示，这两个提示主要控制SELECT语句的锁机制，只对实现了行级锁的存储引擎有效。（唯一内置支持这两个提示的引擎是InnoDB，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描）

5.9.USE INDEX、IGNORE INDEX 和 FORCE INDEX：

告诉优化器使用或者不使用那些索引来查询。

MYSQL升级后验证可以用：Percona Toolkit 中的pt-upgrade工具

6. 优化特定类型的查询

6.1.COUNT()：

MyISAM存储引擎会存储表行数，所以COUNT()无where条件时会比其他引擎更快，所以MyISAM的COUNT()总是很快是一个误解。

在一个查询中统计同一个列的不同值的数量：

SELECT COUNT(color = ‘blue’ OR NULL) AS blue，COUNT(color = 'red' OR NULL) AS red FROM items;

或者：SELECT SUM( IF(color = 'blue' ,1,0) ) AS blue, SUM( IF(color = 'red',1,0) ) AS red FROM items;

使用近似值：

业务场景并不要求完全精确的COUNT值时，执行EXPLAIN得到优化器的估算行数，来得到近似值

6.2.优化关联查询：

1.确保ON或者USING子句的列上有索引，当表A和B用列c关联的时候，如果优化器的关联顺序是B、A，那么不需要在B表的对应列上建上索引。

2.确保任何GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MYSQL才有可能使用索引来优化这个过程。

3.升级MYSQL的时候需要注意：关联语法、运算符优先级等其他可能发生变化的地方。

6.3.优化子查询：

子查询优化尽可能使用关联查询代替（使用MYSQL5.6或更新的版本或MariaDB可以直接忽略关于子查询的建议）

6.4.优化GROUP BY 和DISTINCT：

1.索引优化。

2.无法使用索引时，使用临时表或者文件排序来做分组，可以使用SQL_SMALL_RESULT 和 SQL_BIG_RESULT让优化器按照你希望的方式运行。

3.采用查找表的标识列分组的效率会比其他列更高

4.尽量不使用非分组列，结果通常不确定，索引或者优化器策略不同时可能导致结果不一样

5.对于GROUP BY WITH ROLLUP 如果可以，最好在应用程序中做超级聚合。

6.5.优化LIMIT分页：

1.偏移量非常大的时候，MYSQL查询大量记录被抛弃，代价非常高，可采用延迟关联来提升效率：

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

改写为：SELECT film.film_id,film.description FROM film INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) AS lim USING (film_id);

2.OFFSET问题：会导致MYSQL扫描大量不需要的行然后再抛弃掉，如果可以使用书签记录上次数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样可避免使用OFFSET。

3.其他办法：预先计算汇总表；关联到一个冗余表，冗余表只包含主键列和需要排序的数据列等。

6.6.优化SQL_CALC_FOUND_ROWS：注意！！！用可视化工具执行 SELECT SQL_CALC_FOUND_ROWS * FROM table_a limit 9;SELECT FOUND_ROWS();可能得不到准确的行数，用cmd命令符执行才准确！

加上此提示后，MYSQL会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描，所以该提示的代价可能非常高。所以可以不显示具体页数，用“下一页”按钮替换，另一种做法是先获取并缓存较多的数据，在设计上多加一个“获取更多数据”之类的按钮。

6.7.优化UNION查询：

MYSQL总是通过创建并填充临时表的方式来执行UNION查询，所以很多优化策略在UNION查询中都没办法很好地使用，经常需要将WHERE、LIMIT、ORDER BY等子句冗余地写一份到各个子查询。

除非确实需要服务器消除重复行，否则就一定要加上UNION ALL，如果没有ALL关键字，MYSQL会给临时表加上DISTINCT选项，会导致对整个临时表的数据做唯一性检查，代价非常高。

6.8.静态查询分析：Percona Toolkit 中的 pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在的问题的查询。

6.9.使用用户自定义变量：

SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

SELECT ... WHERE col <= @last_week;

不能使用用户自定义变量的场景：

使用自定义变量的查询，无法使用查询缓存。
不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
如果使用连接池或者持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
5.0版本之前是大小写敏感的
不能显式地声明自定义变量的类型。MYSQL的用户自定义变量是一个动态类型。
MYSQL优化器在某些场景下可能会将这些变量优化掉，可能导致代码不按预想的方式运行。
赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定，实际情况可能会很令人困惑。
赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。
使用未定义变量不会产生任何语法错误。

posted @ 2021-09-07 19:13 涂山倩倩阅读(548) 评论(0) 编辑收藏举报

刷新页面返回顶部

Hello

The very things that in the moment dampen our moods can later be sources of intense gratification and delight.

mysql数据库笔记（《高性能MySQL》）——查询性能优化

公告