「推荐收藏！」【MySQL 技术之旅】（4）总结和盘点优化方案系列之常用 SQL 的优化
概述
前面我们介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中，除了使用查询外，我们还会使用一些其他的常用 SQL，比如 INSERT、GROUP BY 等。对于这些 SQL 语句，我们该怎么样进行优化呢？本节将针对这些 SQL 语句介绍一些优化的方法。
优化 INSERT 语句
当进行数据 INSERT 的时候，可以考虑采用以下几种优化方式：
如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快（在一些情况中几倍）。下面是一次插入多值的一个例子：
如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度。但是，这只能对 MyISAM 表使用；
当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。
优化 GROUP BY 语句
默认情况下，MySQL 对所有 GROUP BY col1，col2....的字段进行排序。这与在查询中指定 ORDER BY col1，col2...类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。如果查询包括 GROUP BY，但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序，如下面的例子：
从上面的例子可以看出第一个 SQL 语句需要进行“filesort”，而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”，而 filesort 往往非常耗费时间。
优化 ORDER BY 语句
MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。
例如下列 SQL 可以使用索引：
但是在以下几种情况下则不使用索引：
1.order by 后面跟的排序方式不一致
2.where 条件后面跟着的查询条件和 order by 排序的条件不一致
3.单纯加入 order by 不加入 where 条件做过滤
优化嵌套查询
MySQL 支持 SQL 子查询。可以使用 SELECT 语句来创建单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。在下面的例子中，要从 goods_stock 表中找到那些在 goods_stock_price 表中不存在阶梯价格的库存：
从上面执行计划可以看到 goods_stock 表是走了全表扫描的，goods_stock、goods_stock_price 表查询结果是在内存上创建临时表存储的，如果使用连接（JOIN）来完成这个查询工作，速度将会快很多。尤其是当 goods_stock_price 表中对 goods_stock.StockGUID 建有索引的话，性能将会更好，具体查询如下：
（此部分可以走内连接，在这不做重复说明了！）
从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
MySQL 如何优化 OR 条件
对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。例如，首先使用 show index 命令查看 goods_stock 表的索引，可知它有 3 个非聚集索引，在 StockGUID、LotNO 两个字段上分别有 1 个独立的索引，在 Model 和 Brand 字段上有 1 个复合索引。
然后在两个独立索引上面做 OR 操作，具体如下：
使用 SQL 提示
SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。下面是一个使用 SQL 提示的例子：
这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。下面是一些在 MySQL 中常用的 SQL 提示。
USE INDEX
在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引：
IGNORE INDEX
如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。同样是上面的例子，这次来看一下查询过程忽略索引 idx_stock_3 的情况：
从执行计划可以看出，系统忽略了指定的索引，而使用了全表扫描。
FORCE INDEX
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为 goods_stock_price.GoodsStockID（已加索引）的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引，如下所示：
但是，当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示后再次执行上面的 SQL：
果然，执行计划中使用了 FORCE INDEX 后的索引。
总结
SQL 优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问题都是由不合适的 SQL 语句造成。本章通过实例描述了 SQL 优化的一般过程，从定位一个有性能问题的 SQL 语句到分析产生性能问题的原因，最后到采取什么措施优化 SQL 语句的性能。
🏆 InfoQ写作平台-签约作者 🏆 2020-03-25 加入
【个人简介】酷爱计算机科学、醉心编程技术、喜爱健身运动、热衷悬疑推理的“极客达人” 【技术格言】任何足够先进的技术都与魔法无异 【技术范畴】Java领域、Spring生态、MySQL专项、微服务/分布式体系和算法设计等
评论