• • 连接的优化
    • 优化连接的一些要点
    • GROUP BY、DISTINCT、ORDER BY语句优化
    • 优化子查询
    • 优化limit
    • 优化IN列表
    • 优化 UNION
    • 优化带有 BLOB 、 TEXT 类型字段的查询
    • filesort 的优化
      • 相关参数的设置和优化
    • 优化 SQL_CALC_FOUND_ROWS
    • 优化临时表
    • OLAP 业务优化
    • 小结

连接的优化

由于连接的成本较高,因此对于高并发的应用,应该尽量减少使用连接的查询,连接表的个数建议控制在四个以内.以对于数据库应用的设计，最好在早期就确定未来可能会影响性能的一些查询，进行反范式设计减少连接的表，或者考虑在应用层进行连接

优化连接的一些要点

• 1）ON、USING子句中的列确认有索引。如果优化器选择了连接的顺序为B、A，那么我们只需要在A表的列上创建索引即可。例如，对于查询“SELECTB.*,A.*FROMBJOIN AON B.col1=A.col2;”语句MySQL会全表扫描B表，对B表的每一行记录探测A表的记录（利用A表col2列上的索引）。
• 2)最好是转换成INNER JOIN,LEFT JOIN的成本比INNER JOIN高很多.
• 3）使用EXPLAIN检查连接，留意EXPLAIN输出的rows列，如果rows列太高，比如几千，上万，那么就需要考虑是否索引不佳或连接表的顺序不当。
• 4）反范式设计，这样可以减少连接表的个数，加快存取数据的速度。
• 5）考虑在应用层实现连接。

对于一些复杂的连接查询,更值得推荐的做法是将它分解为几个简单的查询,可以先执行查询以获得一个较小的结果集,然后遍历此结果集,最后根据一定的条件去获取完整的数据,这样做往往是更加高效的,更加不容易发生变化,更加方便缓存数据,数据也可以按照设计的需求从缓存中进行获取.

示例:

SELECT a.* FROM a WHERE a.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17)

如果果id=1~15的记录已经被存储在缓存（如Memcached）中了，那么我们只需要到数据库查询“SELECT a.* FROM a WHEREa.id=16”和“SELECT a.* FROMa WHERE a.id=17”了。而且，把IN列表分解为等值查找，往往可以提高性能。

• 6）一些应用可能需要访问不同的数据库实例，这种情况下，在应用层实现连接将是更好的选择

GROUP BY、DISTINCT、ORDER BY语句优化

GROUP BY、DISTINCT、ORDERBY这几类子句比较类似，GROUP BY默认也是要进行ORDERBY排序的.可以考虑的优化方式如下.

• 尽量对较少的行进行排序.
• 如果连接了多张表,ORDERBY的列应该属于连接顺序的第一张表。
• 利用索引排序，如果不能利用索引排序，那么EXPLAIN查询语句将会看到有filesort。
• GROUP BY、ORDERBY语句参考的列应该尽量在一个表中，如果不在同一个表中，那么可以考虑冗余一些列，或者合并表。
• 需要保证索引列和ORDERBY的列相同，且各列均按相同的方向进行排序(有点类似与利用索引排序)。
• 增加sort_buffer_size

sort_buffer_size是为每个排序线程分配的缓冲区的大小。增加该值可以加快ORDERBY或GROUP BY操作。但是，这是为每个客户端分配的缓冲区，因此不要将全局变量设置为较大的值，因为每个需要排序的连接都会分配sortbuffersize大小的内存

• ·改变tmpdir变量指向基于内存的文件系统或其他更快的磁盘。

如果是MySQL服务器正作为复制从服务器被使用，那么不应将“--tmpdir”设置为指向基于内存的文件系统的目录，或者当服务器主机重启时将要被清空的目录。因为，对于复制从服务器，需要在机器重启时仍然保留一些临时文件，以便能够复制临时表或执行LOADDATAINFILE操作。如果在服务器重启时丢失了临时文件目录下的文件，那么复制将会失败。

• 指定ORDERBYNULL。

默认情况下 MySQL将排序所有GROUP BY的查询，如果想要避免排序结果所产生的消耗，可以指定ORDER BY NULL。

SELECT count(*) cnt, cluster_id FROM stat GROUP BY cluster_id ORDER BY NULL LIMIT 10;

• 优化GROUP BY WITH ROLLUP。

GROUP BY WITH ROLLUP可以方便地获得整体分组的聚合信息（superaggregation），但如果存在性能问题，可以考虑在应用层实现这个功能，这样往往会更高效，伸缩性也更佳。

• 使用非GROUP BY的列来代替GROUP BY的列。

原来是“GROUP BYxx_name,yy_name”，如果GROUP BYxx_id可以得到一样的结果，那么使用GROUP BYxx_id也是可行的.(多个的组合排序转化为单个排序)

优化子查询

对于数据库来说，在绝大部分情况下，连接会比子查询更快。使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，可以预先装载数据，更高效地处理查询。而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。

一些商业数据库已经可以智能地识别子查询，转化子查询为连接查询，或者转化连接为子查询。这种情况下，编写子查询也许是更好的方式，毕竟更符合人的思考方式，也能避免因为重复记录的匹配导致连接结果集的异常。但MySQL对于子查询的优化一直不佳，就目前的研发实践来说，子查询应尽量改写成JOIN的写法。如果我们不能确定是否要使用连接的方式，那么可以使用EXPLAIN语法查看语句具体的执行计划

Example1 如下是一个带子查询的语句

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;

Example2 如下的两个查询是等价的 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); 改写为LEFT JOIN形式 SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;Example3 把子句从子查询的外部转移到内部。 #感觉这句SQL有问题,如果s1不为空,效果和SELECT * FROM t1是一样的 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);Example4 SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2); 转化成如下的写法，使用行子查询来代替关联子查询 SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2); 对于只返回一行的无关联子查询，IN的速度慢于“＝”。

Web 应用经常需要对查询的结果进行分页，分页算法经常需要用到 “LIMIT offset,row_count ORDER BY col_id” 之类的语句。一旦 offset 的值很大，效率就会很差，因为 MySQL 必须检索大量的记录（ offset+row_count ），然后丢弃大部分记录。

可提供的优化方法有如下四种： 1) 限制页数，只显示前几页，超过了一定的页数后，直接显示 “ 更多（ more ） ” ，一般来说，对于 N 页之后的结果，用户一般不会关心。

limit offset用法演示

语句1：select * from student limit 9,4
语句2：slect * from student limit 4 offset 9

语句1和2均返回表student的第10、11、12、13行
语句2中的4表示返回4行，9表示从表的第十行开始

通过limit和offset 或只通过limit可以实现分页功能。

• 假设 numberperpage 表示每页要显示的条数，pagenumber表示页码，那么 返回第pagenumber页，每页条数为numberperpage的sql语句：

代码示例:

语句3：select * from studnet limit (pagenumber-1)*numberperpage,numberperpage
语句4：select * from student limit numberperpage offset (pagenumber-1)*numberperpage

2 ）要避免设置 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;

3 ）使用 Sphinx 。

一个软件,用来代替MySQL全文检索

4 ）使用 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);

优化IN列表

• 对于IN列表，MySQL会排序IN列表中的值，并使用二分查找 （ Binary Search ）的方式去定位数据。
• 把IN子句改写成OR的形式并不能提高性能。 IN 列表不宜过长，最好不要超过 200 。对于高并发的业 务，小于几十为佳。

如果能够将其转化为多个等于的查询，那么这种方式会更优。

SELECT * FROM table_a WHERE id IN (SELECT id FROM table_b);

们可以先查询 SELECT id FROM table_b ，然后把获取到的 id 值，逐个地和 “SELECT*FROM table_a” 进行拼接，转化为 “SELECT id FROM table_a WHERE id=?” 的形式。

优化 UNION

UNION 语句默认是移除重复记录的，需要用到排序操作，如果结果集很大，成本将会很高，所以，建议尽量使用 UNIONALL 语句。对于 UNION 多个分表的场景，应尽可能地在数据库分表的时候，就确定各个分表的数据是唯一的，这样就无须使用UNION 来去除重复的记录了。

另外，查询语句外层的 WHERE 条件，并不会应用到每个单独的 UNION 子句内，所以，应在每一个 UNION 子句中添加上WHERE 条件，从而尽可能地限制检索的记录数。

优化带有 BLOB 、 TEXT 类型字段的查询

由于Mysql的内存临时表不支持 BLOB 、 TEXT 类型，如果包含 BLOB 或 TEXT 类型列的查询需要用到临时表，就会使用基于磁盘的临时表，性能将会急剧降低。所以，编写查询语句时，如果没有必要包含 BLOB 、 TEXT 列，就不要写入查询条件。

规避 BLOB 、 TEXT 列的办法有如下两种。 1 ）使用 SUBSTRING() 函数。 2 ）设置 MySQL 变量 tmpdir ，把临时表存放在基于内存的文件系统中。如 Linux 下的 tmpfs 。可以设置多个临时表的路径（用分号分隔）， MySQL 将使用轮询的方式。

**优化的方法有如下3种：

• 1. 如果必须使用，可以考虑拆分表，把 BLOB 、 TEXT 字段分离到单独的表。
• 1. 如果有许多的大字段，可以考虑合并这些字段到一个字段，存储一个大的200KB比存储20个10KB更高效
• 1. 考虑使用 COMPRESS() ，或者在应用层进行压缩，再存储到 BLOB 字段中。

注意 如果 BLOB 列很大，可能需要增大 innodb_log_file_size （ MySQL 错误日志内可能会提示事务日志小了）。

filesort 的优化

有时我们使用 EXPLAIN 工具，可以看到查询计划的输出中的 Extra 列有 filesort 。 filesort 往往意味着你没有利用到索引进行排序。 filesort 的字面意思可能会导致混淆，它和文件排序没有任何关系，可以理解为不能利用索引实现排序。

排序一个带 JOIN （连接）的查询，如果 ORDER BY 子句参考的是 JOIN 顺序里的第一张表的列且不能利用索引进行排序，那么 MySQL 会对这个表进行文件排序（ filesort ）， EXPLAIN 输出中的 Extra 列就有 filesort 。如果排序的列来自于其他的表，且需要临时文件来帮助排序，那么 EXPLAIN 输出的 Extra 列就有 “Using temporary;Using filesort” 字样。对于 MySQL 5.1 ，如果有 LIMIT 子句，那么是在 filesort 之后执行 LIMIT 的，这样做效率可能会很差，因为需要排序过多的记录。

####　两种 filesort 算法

• （ 1 ） two-pass

这是旧的算法。列长度之和超过 max_length_for_sort_data 字节时就使用这个算法，其原理是：先按照 WHERE 筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲（ sort buffer ）。如果排序缓冲大小不够，就在内存中运行一个快速排序（ quick sort ）操作，把排序结果存储到一个临时文件里，用一个指针指向这个已经排序好了的块。然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录。

然后合并如上的临时文件，进行排序。

然后依据排序结果再去读取所需要的数据，读入行缓冲（ row buffer ，由 read_rnd_buffer_size 参数设定其大小）。这是第二次读取记录。

以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓冲里存储的行指针是有序的，但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率不佳。

优点： 排序的数据量较小，一般在内存中即可完成。 缺点： 需要读取记录两次，第二次读取时，可能会产生许多随机 I/O ，成本可能会比较高。

• （ 2 ） single-pass MySQL 一般使用这种算法。其原理是：按筛选条件，把 SQL 中涉及的字段全部读入排序缓冲中，然后依据排序字段进行排序，如果排序缓冲不够，则会将临时排序结果写入到一个临时文件中，最后合并临时排序文件，直接返回已经排序好的结果集。

优点： 不需要读取记录两次，相对于 two-pass ，可以减少 I/O 开销。 缺点： 由于要读入所有字段，排序缓冲可能不够，需要额外的临时文件协助进行排序，导致增加额外的 I/O成本。

相关参数的设置和优化

相关参数如下。 max_length_for_sort_data ：如果各列长度之和（包括选择列、排序列）超过了 max_length_for_sort_data 字节，那么就使用 two-pass 算法。如果排序 BLOB 、 TEXT 字段，使用的也是 two-pass 算法，那么这个值设置得太高会导致系统 I/O 上升， CPU 下降，建议不要将 max_length_for_sort_data 设置得太高。

max_sort_length ：如果排序 BLOB 、 TEXT 字段，则仅排序前 max_sort_length 个字节。

可以考虑的优化方向如下。

• 加大 sort_buffer_size 。

一般情况下使用默认的 single-pass 算法即可。可以考虑加大 sort_buffer_size 以减少 I/O 。 需要留意的是字段长度之和不要超过 max_length_for_sort_data ，只查询所需要的列，注意列的类型、长度。 MySQL 目前读取和计算列的长度是按照定义的最大的度进行的，所以在设计表结构的时候，不要将VARCHAR 类型的字段设置得过大，虽然对于 VARCHAR 类型来说，在物理磁盘中的实际存储可以做到紧凑，但在排序的时候，是会分配最大定义的长度的，有时排序阶段所产生的临时文件甚至比原始表还要大

• 对于 two-pass 算法，可以考虑增大 read_rnd_buffer_size ，但由于这个全局变量是对所有连接都生效的，因此建议只在会话级别进行设置，以加速一些特殊的大操作。
• 在操作系统层面，优化临时文件的读写。

优化 SQL_CALC_FOUND_ROWS

建议不要使用 SQL_CALC_FOUND_ROWS 这个提示，虽然它可以让开发过程变得简单一些，但并没有减少数据库所做的事情。例如以下这个查询。

SELECT SQL_CALC_FOUND_ROWS col_name FROM table_name where ... LIMIT N

这样只要执行一次较耗时的复杂查询可以同时得到与不带limit同样的记录条数第二个 SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时，FOUND_ROWS() 可能会返回不同的结果）。

优化临时表

如果不能利用索引排序，那么我们在MySQL中需要创建一个临时表用于排序。MySQL中的临时表分为“内存临时表”和“磁盘临时表”用于排序。其中内存临时表使用Mysql MEMORY 存储引擎。磁盘临时表使用 MySQL 的 MyISAM 存储引擎；一般情况下， MySQL 会先创建内存临时表，但当内存临时表超过配置参数指定的值后， MySQL 会将内存临时表导出到磁盘临时表。

触发以下条件，会创建临时表。

• ORDER BY 子句和 GROUP BY 子句引用的列不一样。
• 在连接查询中， ORDER BY 或 GROUP BY 使用的列不是连接顺序中的第一个表。
• ORDER BY 中使用了 DISTINCT 关键字。

通过EXPLAIN的Extra列可以查看是否使用到了临时表： “Using temporary” 表示使用了临时表。

如果查询创建了临时表（in-memory table)来排序或检索结果集，分配的内存大于 tmp_table_size 与 max_heap_table_size 参数之间的最小值，那么内存临时表就会转换为磁盘临时表（ on-disk table ）， MySQL 会在磁盘上创建磁盘临时表，这样会可能导致I/O 瓶颈，进而影响性能。

• tmp_table_size ：指定系统创建的内存临时表的最大大小。
• max_heap_table_size ：指定用户创建的内存表的最大大小。

SHOW FULL PROCESSLIST 命令输出的 state 列为 “Converting heap to MyISAM” 时表明临时表大于我们所设置的参数值，此时将会产生磁盘临时表，但是数据库执行查询往往很快， “Converting heap to MyISAM” 这个状态不一定能及时被看到，我们需要关注Created_tmp_tables 和Created_tmp_disk_tables 这两个变量的变化。由于 MySQL 慢查询日志里没有使用临时表的信息，这就给我们诊断性能问题带来了一些不便，第三方的版本如 Percona Server ，在慢查询里可以有更详细的信息，将会记录临时表使用的情况，从而有助于我们诊断和调优。

如下情况也可能会导致使用磁盘临时表：

• 表中有BLOB 或 TEXT 字段。
• 使用 UNION 或 UNION ALL 时， SELECT 子句中包含了大于 512 字节的列。

使用临时表一般意味着性能会比较底，特别是使用磁盘临时表的时，性能将会更慢，因此我们在实际应用当中应当尽可能的避免临时表的使用。

常见的避免临时表的方法有如下3点：

• 创建索引：在ORDER BY或者GROUP BY的列上创建索引
• 分拆长的列：一般情况下， TEXT 、 BLOB ，大于 512 字节的字符串，基本上都是为了显示信息，而不会用于查询条件，因此设计表的时候，可以考虑将这些列分离到另外一张表中。
• 不需要用 DISTINCT 时就没必要用 DISTINCT ，能用 UNION ALL 就不要用 UNION

OLAP 业务优化

由于 MySQL 对于复杂 SQL 的优化不佳，所以对于一些 OLAP （Online Analytical Processing）的应用需要格外小心，在前期就做好一些针对性的设计，以尽量避免数据量剧增后碰到性能问题。

• 使用冗余数据 冗余数据可以让查询的执行得更快。（大表之间的连接操作很耗时，增加冗余字段则可以有效地减少连接表的个数）
• 计算复用，使用缓存表 可以使用存储一些结果，这里这里所说的 “ 缓存表 ” ，意思是这些值在逻辑上是冗余的，可以从原始表中获取到，但显然从原始表中获取数据更慢。
• 预计算 预先对一些常用的大查询商城汇总表。当需要处理大量数据的时候，需要昂贵的计算成本。可以先把查询结果存储到单独的汇总表中，或者可以把相关联的表的一些字段放在一个独立的新表中，让这个新的汇总表做统计。

我们使用缓存表和汇总表时，我们要做出决定：是实时更新数据还是定期更新，这依赖于你的应用。

• 统计框架的改善 报表研发人员可以和用户沟通，限制一些查询的使用，引导用户培养一些能够更快查询数据的习惯，让用户能够自己综合分析一些报表而不是完全借助计算机系统。报表的复杂度和生成报表所花的时间是成正比的。

小结

一般SQL的优化有两个方向，一个是让SQL执行得更快，一个是蓝SQL语句做更少的事。

MySQL 的查询优化器比较简单，没有商业数据库那么强大和智能，我们应该理解 MySQL 的优化器限制，按优化器能理解的方式编写 SQL 。对于大流量的业务，应该尽量保持 MySQL 查询的简单性，以保证尽可能地支持更高的并发。现实中，对于数据库流量很大的业务，数据库往往已经退化为一个存储数据的容器，只利用它最高效的核心的特性。

整理自书籍《MYSQLDBA修炼之道》