MySQL开发03-查询优化的常用策略

豪杰笑开怀

已于 2023-04-11 12:13:06 修改

阅读量327

点赞数

分类专栏： MySQL开发文章标签： mysql 数据库运维 sql 服务器

于 2023-04-10 12:49:28 首次发布

本文链接：https://blog.csdn.net/oddrock/article/details/130057343

版权

MySQL开发专栏收录该内容

5 篇文章 0 订阅

订阅专栏

0、概述

MySQL优化的两个方向：我们可以升级硬件让SQL跑得更快。或者，我们可以把小批量数据的排序交由应用程序去执行，MySQL不做排序计算。类似的方法有很多，但基本不外乎这两个方向。

让SQL语句执行得更快
让SQL语句做更少的事

对于MySQL的查询优化一般有如下几种思路：

优化数据访问：数据缓存、使用索引、增加筛选条件等
重写SQL：复杂查询改多个简单查询、大数据量分批操作、减少表连接
重新设计库表：增加冗余表、增加冗余列

1、优化数据访问

1.1、原理

优化数据访问的两个思路：

减少应用程序对数据库的数据访问：减少应用程序对数据库的访问，可以通过数据缓存到应用程序内存或者专用的缓存数据库例如Redis来实现，也可以让应用程序只取获取它需要的字段而不是获取全量字段来实现。
减少数据库应实际扫描的记录数：如果在慢查询日志里看到Rows_examined的值很高、Rows_sent的值很低，而实际上并不需要扫描大量的数据，一般可以通过添加索引或增加筛选条件来减少记录扫描的行数。

1.2、方法

数据做应用级别缓存；
新建/使用索引；
增加筛选条件；

2、重新设计库表

2.1、原理

有些情况下即使是重写SQL或添加索引也是解决不了问题的，这时可能要考虑更改表结构。比如，可以增加一个结果表，把统计数据缓存在结果表；或者可以增加冗余列，以减少连接。优化的主要方向是进行反范式设计。

2.2、方法

增加中间结果表或冗余表；
增加冗余列；

3、重写SQL

3.1、重写原则

复杂查询分解为多个简单查询：一般多个简单查询的总成本小于一个复杂查询的成本。
大量数据操作分批执行：对于需要进行大量数据的操作，可以分批执行，以减少对生产系统产生的影响。
减少表连接：由于MySQL连接（JOIN）严重降低了并发性，对于高并发，高性能的服务，应该尽量避免连接太多表。很多连接可以在应用层面实现。
减少排序

3.2、连接(JOIN)的优化

减少连接表的数量：连接的表建议控制在4个以内。
ON、USING子句中的列确认有索引。
优先使用INNER JOIN：LEFT JOIN的成本比INNER JOIN高很多。
使用EXPLAIN检查连接：留意EXPLAIN输出的rows列，如果rows列太高，就需要考虑是否索引不佳或连接表的顺序不当。
反范式设计：这样可以减少连接表的个数，加快存取数据的速度。
在应用层实现连接。
复杂查询分解为几个简单查询

3.3、GROUP BY、DISTINCT、ORDER BY语句优化

GROUP BY、DISTINCT、ORDER BY这几类子句比较类似，GROUP BY默认也是要进行ORDER BY排序的，因此优化的思路也是类似的。可以考虑的优化方式如下：

尽量对较少的行进行排序。
如果连接了多张表，ORDER BY的列应该属于连接顺序的第一张表。
利用索引排序，避免filesort
GROUP BY、ORDER BY的列尽量在一个表中：如果不在同一个表中，那么可以考虑冗余一些列，或者合并表。
保证索引中的列和ORDER BY的列相同，且各列均按相同的方向进行排序。
如果不想对GROUP BY排序可以指定ORDER BY NULL：默认情况下，MySQL将排序所有GROUP BY的查询，如果想要避免排序结果所产生的消耗，可以指定ORDER BY NULL。例如：SELECT count(*) cnt, cluster_id FROM stat GROUP BY cluster_id ORDER BY NULL LIMIT 10;
增加sort_buf fer_size：sort_buf fer_size是为每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作。但是，这是为每个客户端分配的缓冲区，因此不要将全局变量设置为较大的值，因为每个需要排序的连接都会分配sort_buf fer_size大小的内存。
增加read_rnd_buffer_size：当按照排序后的顺序读取行时，通过该缓冲区读取行，从而避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDERBY的性能。但这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只用为需要运行大查询的客户端更改会话变量即可。
改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。
可以考虑使用Sphinx等产品来优化GROUP BY语句，一般来说，它可以有更好的可扩展性和更佳的性能。

3.4、子查询优化

子查询往往是性能杀手：对于数据库来说，在绝大部分情况下，连接会比子查询更快。使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，可以预先装载数据，更高效地处理查询。而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。如果我们不能确定是否要使用连接的方式，那么可以使用EXPLAIN语法查看语句具体的执行计划。
如下是一些子查询和连接的等价场景，可以将子查询改写成连接
如下两个查询等价：

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);
SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

如下三个查询等价：

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);
SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id  WHERE table2.id IS NULL;

如下两个查询等价：

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

如下两个查询等价：

SELECT (SELECT column1 FROM t1) + 5 FROM t2;
SELECT (SELECT column1 + 5 FROM t1) FROM t2;

如下两个查询等价，第二句使用行子查询来代替关联子查询

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2);
SELECT * FROM t1WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

对于只返回一行的无关联子查询，IN的速度慢于“＝”，因此下面第一句可改为第二句：

SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);
SELECT * FROM t1 WHERE t1.col_name= (SELECT a FROM t2 WHERE b = some_const);

MySQL优化器这些年来一直都在改进，MySQL后续版本对于子查询也有了更多改进，可以参考如下链接：
http: //dev.MySQL.com/doc/refman/5.6/en/subquery-optimization.html
http: //dev.MySQL.com/doc/refman/5.7/en/subquery-optimization.html

3.5、LIMIT子句优化

分页算法经常需要用到“LIMIT of f set,row_count ORDER BY col_id”之类的语句。一旦off set的值很大，效率就会很差，因为MySQL必须检索大量的记录（offset+row_count），然后丢弃大部分记录。可供考虑的优化办法有如下4点：

限制页数，只显示前几页，超过了一定的页数后，直接显示“更多（more）”，一般来说，对于N页之后的结果，用户一般不会关心。
要避免设置offset值，也就是避免丢弃记录。

例如以下的例子，按照id排序（id列上有索引），通过增加一个定位的列“id>990”，可以避免设置offset的值。

SELECT id, name, address, phone FROM customers WHERE id > 990 ORDER BY id LIMIT 10;

也可以使用条件限制要排序的结果集，如可以这样使用。

WHERE date_time BETWEEN  ‘2014-04-01 00:00:00’  AND ‘2014-04-02 00:00:00’  ORDER BY id

对条件值可以进行估算，对于几百上千页的检索，往往不需要很精确。也可以专门增加冗余的列来定位记录，比如如下的查询，有一个page列，指定记录所在的页，代价是在修改数据的时候需要维护这个列的数据，如下面的查询。

SELECT id, name, address, phone FROM customers WHERE page = 100 ORDER BY name;

使用Sphinx。
使用INNER JOIN：以下的例子中，先按照索引排序获取到id值，然后再使用JOIN补充其他列的数据。customers表的主键列是id列，name列上有索引，由于“SELECT id FROM customers…”可以用到覆盖索引，所以效率尚可。

SELECT id, name, address, phone FROM customers INNER JOIN ( SELECT id FROM customers ORDER BY name LIMIT 999,10) AS my_results USING(id);

3.6、IN列表优化

IN列表原理：对于IN列表，MySQL会排序IN列表里的值，并使用二分查找（Binary Search）的方式去定位数据。IN列表不宜过长，最好不要超过200。对于高并发的业务，小于几十为佳。
改写OR并不能优化：把IN子句改写成OR的形式并不能提高性能。
优化建议-转为等值连接：如果能够将其转化为多个等于的查询，那么这种方式会更优。例如如下这个查询：SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);我们可以先查询SELECT id FROM table_b，然后把获取到的id值，转化为“SELECT id FROM table_a WHERE id=?”的形式。

3.7、UNION优化

UNION语句默认是移除重复记录的，所以需要用到排序操作，如果结果集很大，成本将会很高，建议尽量使用UNION ALL语句。对于UNION多个分表的场景，应尽可能地在数据库分表的时候，就确定各个分表的数据是唯一的，这样就无须使用UNION来去除重复的记录了。
查询语句外层的WHERE条件，并不会应用到每个单独的UNION子句内，所以，应在每一个UNION子句中添加上WHERE条件，从而尽可能地限制检索的记录数。

3.8、BLOB/TEXT类型字段的查询优化

由于MySQL的内存临时表不支持BLOB、TEXT类型，如果包含BLOB或TEXT类型列的查询需要用到临时表，就会使用基于磁盘的临时表，性能将会急剧降低。所以，编写查询语句时，如果没有必要包含BLOB、TEXT列，就不要写入查询条件。
如果必须使用，可以考虑拆分表，把BLOB、TEXT字段分离到单独的表。
如果有许多大字段，可以考虑合并这些字段到一个字段，存储一个大的200KB比存储20个10KB更高效。
考虑使用COMPRESS()，或者在应用层进行压缩，再存储到BLOB字段中。

4、其他一些优化

4.1、filesort优化

如果查询计划Extra列有filesort(一般是Using temporary; Using filesort字样)，往往意味着没有用索引进行排序。优化措施是加大sort_buffer_size，以减少I/O。
需要留意的是字段长度之和不要超过max_length_for_sort_data，只查询所需要的列，注意列的类型、长度。MySQL目前读取和计算列的长度是按照定义的最大的度进行的，所以在设计表结构的时候，不要将VARCHAR类型的字段设置得过大，虽然对于VARCHAR类型来说，在物理磁盘中的实际存储可以做到紧凑，但在排序的时候，是会分配最大定义的长度的，有时排序阶段所产生的临时文件甚至比原始表还要大。

4.2、临时表优化

MySQL的临时表分为“内存临时表”和“磁盘临时表”，使用临时表一般意味着性能会比较低，特别是使用磁盘临时表时，性能将会更慢，因此应尽量避免临时表的使用。常见的避免临时表的方法有如下3点：
- 创建索引：在ORDER BY或GROUP BY的列上创建索引。
- 分拆长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件，因此设计表的时候，可以考虑将这些列分离到另外一张表中。
- 不需要用DISTINCT时就没必要用DISTINCT，能用UNION ALL就不要用UNION

4.3、OLAP优化

冗余数据：有时最好的办法是在表中保存冗余的数据，虽然这些冗余数据有时也可以由其他的列推断得出。冗余数据可以让查询执行得更快。比如，我们可以增加一个专门的计数表或计数字段，实时更新计数信息。比如，大表之间的连接操作很耗时，增加冗余字段则可以有效地减少连接的表的个数。
数据缓存：用缓存表存储一些结果，这里所说的“缓存表”，意思是这些值在逻辑上是冗余的，可以从原始表中获取到，但显然从原始表中获取数据更慢。
预计算：如果你需要处理大量数据，一般需要昂贵的计算成本。所以预计算往往是值得考虑的好方法。我们可以把查询结果存储到独立的汇总表中，或者可以把相关联的表的一些字段存放在一个独立的新表中，基于这个新的汇总表去做统计。
改善统计框架：需要将一个复杂的查询任务放在一个SQL查询中来完成，往往会导致性能问题。组件或报表工具通常假设单个查询仅仅只用来完成一个简单的任务。如果报表需求太复杂，不能用单个SQL查询来完成，那么更好的方案可能就是生成多个报表、增加一些限制条件。

5、总结

MySQL优化的两个方向：让SQL语句执行得更快，让SQL语句做更少的事；’
对于MySQL的查询优化一般有如下几种思路：
- 优化数据访问：数据缓存、预计算、使用索引、增加筛选条件
- 重新设计库表：增加冗余表、增加冗余列
- 重写SQL：复杂查询改多个简单查询、大数据量分批操作、减少表连接、减少排序
  - 尽量使用连接替代子查询；
  - GROUP BY、ORDER BY尽量使用索引，特别是覆盖索引；
  - 分页尽量不要让用户指定offset，避免丢弃大量数据导致性能差；
  - IN列表的值不要超过200个，最好用等值连接替代IN，因为IN列表的值是会被排序的；’
  - 尽量用UNION ALL而不是UNION，因为UNION会做排序。应在UNION子句中加上WHERE条件，尽可能限制记录数。
  - 尽量不要使用BLOB/TEXT字段，因为内存临时表不支持，必须用磁盘临时表。
  - VARCAHR字段尽量不要设置过大，因为MySQL读取列时按最大长度进行的。