mysql sql高级用法（MySQL学习八）

一、概述

• mysql的性能优化可以从机器硬件，如磁盘，内存等；MySQL服务器配置，如线程数，查询缓存等；MySQL的主从分离和分库分表等；SQL语句优化等。其中SQL语句优化是与日常开发密切相关的，而且也是MySQL优化中最重要的一个环节，因为MySQL服务器，机器等的资源是一定的，故当出现性能瓶颈时，首先需要排除是否为SQL执行问题，如通过开启MySQL慢日志统计执行慢的SQL，或者使用profile功能统计SQL执行涉及的CPU，内存，IO等资源开销。
• 定位到存在性能问题的SQL之后，则可以通过explain命令来分析该SQL的执行情况，如索引使用，排序等，然后是针对该SQL进行优化，优化主要从查询涉及的表，WHERE条件与是否使用和高效使用了索引，以及是否存在子查询等方面展开。
• 要进行SQL优化，首先需要理解SQL的执行过程，具体可以参考：SQL解析顺序与MySQL底层实现原理
• 以下分析以用户表t_user和用户订单表t_order来分析：在t_order表的user_id列是引用t_user的id列的外键。订单表和订单清单条目表t_order_item，通过order_id来建立外键约束。

二、拼表优化：FROM和JOIN

• 请参考：SQL优化（一）：MySQL多表查询FROM和JOIN的用法与性能优化

三、WHERE查询优化

• WHERE查询条件优化是SQL语句优化最重要的一个环节，WHERE子句的优化主要从索引的利用和条件的顺序两个方面。

1. 索引的利用

• 通过给查询列增加索引可以避免全表扫描加快数据检索速度，同时覆盖索引还可以避免回表查询，只通过索引即可返回所需要的数据，索引相关的内容可以参考：MySQL学习（七）：Innodb存储引擎索引的实现原理
• 如果WHERE查询条件中的索引列使用方法不当，则会导致索引失效，从而进行全表扫描，以下来分析索引失效的情况：SQL优化（二）：MySQL索引失效的六种场景与优化方法

2. 查询条件的顺序

• WHERE的查询条件的顺序主要是针对联合索引而言，即联合索引遵循最左前戳匹配规则，故需要保证在where中列从左到右，如联合索引(a,b,c)，则需要保证where a=xx and b=xx（注意，如果是where b=xx and a=xx，也可以继续使用该联合索引），而如果是where b=xx，则无法使用索引。如下对t_order_item表建立了联合索引idx_product_id_buy_date：当同时包含product_id和num或者只包含product_id时，可以使用该联合索引，如果只包含num则无法使用联合索引。

• 针对单列索引的情况，如果where的and条件中的列都包含索引或者某些没有索引，都是由MySQL自行选择使用其中一个MySQL优化器认为效率最高的索引，如下：user_id，order_id, buy_date均包含索引，则MySQL选择使用了order_id这个主键索引：

mysql> explain select * from t_order where user_id=1 and order_id>2 and buy_date=curdate();

explain的结果如下：主要关注possible_keys和key列，如下说明使用主键PRIMARY最高效。

四、子查询的使用和优化

• 请参考：SQL优化（三）：子查询和IN，EXISTS用法和优化方法

五、ORDER BY 排序优化

• ORDER BY的排序优化主要是利用索引的有序性来进行排序，从而减少MySQL服务器的排序操作，因为在服务端进行排序通常需要额外的内存空间，通常通过sort_buffer_size来控制每个MySQL服务器线程的sort buffer的大小，如果内存空间不够，则需要通过磁盘文件来辅助。所以如果能利用索引的有序性来完成排序而可以提高性能。
• 所以ORDER BY的优化就转变为避免索引失效的优化了，即尽可能使用主键进行排序；如果不能使用主键来排序，则对于order by的列加上索引，并且如果可以使用覆盖索引，则通过建立联合索引来实现直接从索引返回数据；对于联合索引需要注意最左前戳匹配规则。如下，查询某个用户的所有订单并且根据购买日期排序，由执行计划可知，使用了联合索引idx_user_id_buy_date。

• 如果存在联合索引，但是不遵循最左前戳规则，则无法使用索引来排序，如下将user_id和buy_date反过来则无法使用联合索引idx_user_id_buy_date了，由Using filesort可知需要在MySQL服务器进行排序。

mysql> explain select * from t_order where buy_date=curdate() order by user_id;

explain的结果如下：

六、LIMIT分页优化

• LIMIT分页查询优化主要是针对LIMIT index, count形式的SQL，即从index下标开始的count条记录，如 LIMIT 10000, 50，取出第10000到10050这50条记录，但是对于MySQL来说需要扫描前面的10000条记录。所以可以基于以下思路来优化：

1. 记录上一页的有序的最大ID

• 记录上一页的最大ID，通常为递增的主键值，或者递增的索引列，则可以利用索引来进行过滤，主要是基于B 树索引的特性来快速过滤掉大部分数据，如下：普通的limit index, count为全表扫描，使用order_id列则可以使用主键索引。

2. 子查询优化

• 可以通过子查询来对表的索引进行查找，获取这个范围的id，从而避免对数据表进行扫描，然后在数据表中取出匹配的数据行，如下：由于一页数据通常较少，故子查询

mysql> select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id; Empty set (0.02 sec) mysql> explain select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id;

explain的结果如下：

七、GROUP BY 分组优化

• GROUP BY操作通常会进行排序操作，而通过GROUP BY一般是与聚集函数，如SUM，COUNT，MAX等来结合使用从而完成统计任务，故一般不需要进行排序，如下：统计每个用户今天的订单总金额：

mysql> select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id; --------- ----------- | user_id | SUM(cost) | --------- ----------- | 2 | 1000 | --------- ----------- 1 row in set (0.01 sec)

• 执行计划如下：由extra的 Using filesort 可知，需要在MySQL服务器进行排序，但是此时并不需要该排序操作。

mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id;

explain的结果如下：

• 所以可以通过 ORDER BY NULL 来禁止排序操作，如下：extra不再包含Using filesort信息，故在MySQL服务器不再进行排序操作。

mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id order by null;

explain的结果如下：