mysql 优化 mysql优化表语句

#使用索引合并优化的案例
SELECT * FROM t WHERE a = 10 OR b = 20;

SELECT * FROM t WHERE (a = 10 OR b = 20) AND non_key = 30;

SELECT * FROM t1, t2 WHERE (t1.a IN (1,2) OR t1.b LIKE 'value%') AND t2.a = t1.some_col;

SELECT * FROM t1, t2 WHERE t1.a = 1 AND (t2.a = t1.some_col OR t2.b = t1.some_col2);

Index Merge的限制

如果在WHERE语句中，存在多层嵌套的AND/OR，MySQL可能不会选择最优的方案，可以尝试通过拆分WHERE子句的条件来进行转换：

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

Index Merge的Explain输出

查看执行计划，索引合并的EXPLAIN的输出如下：

索引合并方法在type列中会显示为index_merge。在这种情况下，key列包含一个所使用的索引的列表，key_len包含这些索引最长的关键部分的列表。

type：显示为index_merge
key：显示使用的索引列表，key_len列显示这些索引的最大长度（列表）。
Extra：显示Index Merge算法，有Using intersect/Using union/Using sort_union几种。

Index Merge算法

索引合并的使用受制于optimizer_switch系统变量的几个标识：

index_merge
index_merge_intersection：
index_merge_union
index_merge_sort_union

默认情况下，所有这些标志都是打开的。如果只想启用某些算法，可以将index_merge设置为关闭，并只启用其他应该被允许的算法。

①Index Merge Intersection(索引合并交集访问算法)

这种方法适用于WHERE子句中的条件是通过AND组合的不同索引的范围条件时，每个条件都需要满足下列条件之一：

如果其中的索引是多列索引，条件中需要包括索引的所有列

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

在Innodb表的主键上的范围条件

例如：

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

索引合并交集算法在所有使用的索引上同时进行扫描，并从扫描结果中生成行的交集。
如果查询中的所有列都被使用的索引覆盖，不需要检索所有表行（EXPLAIN输出中的Extra列中包括Using index）。例如这个语句：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引没有覆盖查询中所有的行，只有当所有使用的索引的范围条件满足时才检索整个行。
如果合并条件中包括Innodb表主键索引条件，主键并不用来检索数据，而是用来筛选使用其他条件检索出的行。(先通过其他的范围条件筛选出一部分数据，在从这部分数据中，通过主键来筛选出最终的结果)

②Index Merge Union(索引合并并集访问算法)

这个算法的标准与索引合并相交算法的标准相似。当表的WHERE子句被转换为不同键上的几个范围条件与OR组合时，该算法就适用，每个条件是以下之一。

如果其中的索引是多列索引，条件中需要包括索引的所有列

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

在Innodb表的主键上的范围条件
适用于Index Merger intersection算法的条件

例子：

SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

③Index Merge Sort_Union(索引合并排序-并集访问算法)

这种方法适用于WHERE子句中的条件是通过OR组合的不同索引的范围条件，但Index Merge Union算法不适用。

例子：

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

sort-union算法和union算法的区别在于，sort-union算法必须首先获取所有行的行ID，并在返回任何行之前对其进行排序。

索引合并优化的实际意义

索引合并策略有时是一种优化的结果，但实际上更多时候说明表上的索引建的很糟糕：

当出现对多个索引做相交操作时(通常是多个AND条件)，通常意味着需要一个包含了所有列的多列索引，而不是为多多个列都创建单列索引。
当出现对多个索引左Union操作时(通常是多个OR条件)，通常需要消耗大量CPU和内存在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据时
更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。除了会消耗更多CPU和内存，还可能影响查询的并发性。通常来说，还不如将查询改写为Union的方式。

如果在Explain中看到有索引合并，应该检查一下查询和表结构，看是否已经最优。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNOE INDEX提示让优化器忽略掉某些索引。

4.ICP优化

官方文档：Index Condition Pushdown Optimization

ICP是在mysql5.6中新增的，在之前的博客中总结过，参考：索引下推和Mysql优化之索引生效和失效的典型场景

5.Outer join优化

官方文档：

Nested Join Optimization

Outer Join Optimization

Outer Join Simplification

6.Multi-Range Read优化

官方文档：Multi-Range Read Optimization

7.Is Null优化

官方文档：IS NULL Optimization

参考：MySQL 优化 —— IS NULL 优化

8.ORDER BY优化

官方文档：ORDER BY Optimization

Mysql中有两种排序方式

1.通过有序的索引顺序扫描直接返回有序数据。不需要额外的排序，操作效率高。
2.通过返回数据进行排序(称为Filesort排序)

Filesort排序是否使用磁盘文件或临时表，取决于Mysql服务器对排序参数的设置和需要排序数据的大小。

尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就出现了Filesort。

9.GROUP BY优化

官方文档：GROUP BY Optimization 和《高性能mysql》6.7.4节

①禁用隐式的排序

MySQL默认会对所有GROUP BY col，cl2…的字段进行排序(分组后排序)。如果查询仅需要GROUP BY，不需要对结果进行排序，则可以指定ORDER BY NULL来禁用排序。

GROUP BY xxx ORDER BY NULL

②松散索引扫描和紧凑索引扫描

GROUP BY通常需要先扫描整张表，然后创建一张临时表，其中每个分组的所有行都是连续的，并使用该临时表来检测分组和应用聚集函数(如果有)。某些情况下，MySQL可以使用索引访问来避免创建临时表。

在GROUP BY中使用索引的最重要的前提条件是，所有GROUP BY列都引用同一索引的属性，并且索引按顺序存储其键。

临时表能否被索引替代，取决于查询使用了索引的哪些部分、为这些部分指定的条件，以及选择的聚合函数。

通过索引访问来执行 GROUP BY 查询有两种方法：

第一种方法：将分组操作与所有范围谓词（如果有）一起应用。
第二种方法：首先执行范围扫描，然后对结果元组进行分组。

在MySQL中，GROUP BY用于排序，所以也可以将ORDER BY优化应用到分组中。然而，依靠隐式的GROUP BY排序已经被废弃了(deprecated)。

Loose Index Scan(松散索引扫描)

处理 GROUP BY 最有效的方法是使用索引直接检索分组列。通过这种访问方法，MySQL使用一些索引类型的属性，即键是有序的（例如，BTREE）。该属性使得可以直接在索引中查找组，而无需考虑索引中满足所有WHERE条件的所有键。这种访问方法只考虑索引中的一部分键，所以它被称为松散索引扫描。

当没有WHERE子句时，松散索引扫描读取的键数与组的数量一样多，而组的数量可能比所有键的数量小得多。
如果WHERE子句包含范围谓词，松散索引扫描会查找每个组中满足范围条件的第一个键，并再次读取尽可能少的键。这在以下条件下是可能的

该查询是针对单个表的。
GROUP BY只命名构成索引最左边前缀的列，而没有其他列。(如果查询中没有GROUP BY，而是有一个DISTINCT子句，那么所有的独立属性都是指构成索引最左边前缀的列）。例如，如果表t1有一个关于(c1,c2,c3)的索引，如果查询有GROUP BY c1, c2，那么松散索引扫描就适用。如果查询有GROUP BY c2, c3（这些列不是最左边的前缀）或GROUP BY c1, c2, c4（c4不在索引中），则不适用。
在选择列表中使用的唯一聚合函数（如果有的话）是MIN()和MAX()，而且都是指同一列。该列必须在索引中，并且必须紧随GROUP BY中的列。
除了 MIN() 或 MAX() 函数的参数之外，查询中引用的 GROUP BY 之外的索引的任何其他部分都必须是常量（即，它们必须以与常量相等的方式引用）。
对于索引中的列，必须索引完整的列值，而不仅仅是前缀。例如，对于 c1 VARCHAR(20)、INDEX (c1(10))，索引仅使用 c1 值的前缀，不能用于松散索引扫描。

如果查询能用上Loose Index Scan，则EXPLAIN输出在Extra列中会显示 Using index for group-by。

假设表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面的方法不能使用松散索引扫描，原因如注释所示。

#除了MIN()或MAX()之外，还有其他的聚合函数。    
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

#GROUP BY子句中的列不构成索引的最左侧前缀。
SELECT c1, c2 FROM t1 GROUP BY c2, c3;

#该查询指的是一个键的一部分，它在GROUP BY部分之后，对于它来说，没有与常数相等。
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
如果查询包含 WHERE c3 = const，则可以使用松散索引扫描。

除了已经支持的MIN()和MAX()引用之外，松散索引扫描访问方法还可以应用于选择列表中其他形式的聚合函数引用

支持 AVG(DISTINCT)、SUM(DISTINCT) 和 COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)采用单个参数。COUNT(DISTINCT)可以有多个列参数。
查询中不能有 GROUP BY 或 DISTINCT 子句。
前面描述的松散索引扫描限制仍然适用。

假设表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)，松散索引扫描访问方法可用于以下查询

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

Tight Index Scan(紧凑索引扫描)

紧密索引扫描可以是全索引扫描，也可以是范围索引扫描，这取决于查询条件。

当不满足松散索引扫描的条件时，仍然有可能避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，则此方法仅读取满足这些条件的键。否则，它执行索引扫描。因为这种方法读取WHERE子句定义的每个范围内的所有键，或者如果没有范围条件则扫描整个索引，所以称为紧密索引扫描。使用紧密索引扫描，只有在找到所有满足范围条件的键后才执行分组操作。

要使此方法起作用，查询中的所有列都有一个恒定的相等条件就足够了，这些列引用了 GROUP BY 键部分之前或之间的键部分。来自等式条件的常量填充了搜索键中的任何“空白”，以便可以形成索引的完整前缀。这些索引前缀然后可用于索引查找。如果 GROUP BY 结果需要排序，并且有可能形成作为索引前缀的搜索键，MySQL 也会避免额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

假设表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)。以下查询不适用于前面描述的松散索引扫描访问方法，但仍适用于紧密索引扫描访问方法。

GROUP BY 中有一个缺口，但它被条件 c2 = 'a' 覆盖

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY不是从键的第一部分开始的，但有一个条件为该部分提供了一个常数

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

10.DISTINCT优化

官方文档：DISTINCT Optimization

DISTINCT与ORDER BY组合在很多情况会需要使用临时表，因为DISTINCT可能会使用到ORDER BY。

在大多数情况下，DISTINCT子句可以视为GROUP BY的特例。因此，适用于GROUP BY查询的优化也可以应用于带有DISTINCT子句的查询。

#下面两者是等价的
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

当把LIMIT与DISTINCT组合使用时，MySQL一旦发现行数唯一的行就会停止。

如果不使用查询中命名的所有表的列，MySQL在找到第一个匹配时就会停止扫描任何未使用的表。在下面的例子中，假设t1在t2之前被使用，当MySQL发现t2中的第一条记录时，就停止从t2中读取（对于t1中的任何特定记录）

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

11.limit查询优化

官方文档：LIMIT Query Optimization

limit 1000,20

mysql在处理这条语句会排序取出前1020条记录后返回最后20条记录，而将前1000条记录全抛弃，这样的代价是非常高的。

优化思路1：使用索引覆盖扫描

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是所有的列，然后再做一次关联操作再返回所需的列。

// 优化前
select film_id,description from film order by title limit 1000,20
// 优化后
select a.film_id, a.description from film a inner join (select film_id from film order by title limit 1000,20) b on a.film_id = b.film_id

优化思路2：把limit m,n转换成limit n的查询

把limit查询转换成某个位置的查询，也就是把limit m,n转换成limit n的查询。
这只适合在排序字段不会出现重复值的特定情况。如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。

#需要记录上一页的最后一条记录的id

12.优化UNION

UNION会消除重复的行，而UNION ALL不会消除重复行。

Mysql总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将where,limit,order by等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化(例如，直接将这些字句冗余地下一份到各个子查询)

如果没有ALL，Mysql会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价是非常高的。即使有ALL关键字，Mysql仍然会使用临时表存储结果。事实上，Mysql总是将结果放入临时表，然后再取出返回给客户端。

除非确实需要服务器消除重复的行，否则一定要使用UNION ALL，这点很重要。

二、优化子查询、导出表

官方文档：Optimizing Subqueries, Derived Tables, and View References

1.优化子查询

在MySQL5.5及以下版本中，子查询的实现目前还比较差，很难得到一个很好的执行计划，很多时候明明有索引可以利用，可QueryOptimizer就是不用。从MySQL官方给出的信息说，这一问题将在MySQL6.0中得到较好的解决，将会引入SemiJoin的执行计划，可MySQL6.0离我们投入生产环境使用恐怕还有很遥远的一段时间。所以，能不用子查询的时候就尽量不要使用子查询。

在Mysql5.6中，子查询已经有了很大的改善，"能不用子查询的时候就尽量不要使用子查询"这个建议将可以被忽略了。

(我还暂未发现mysql5.6子查询相关的权威说明)

2.优化关联查询

需要特别提到的几点：

①确保on或者USING子句上的列有索引。创建索引的时候要考虑到关联的顺序。如果表A和表B用列c做关联，如果优化器的关联顺序是B,A，那么就不需要在B表的对应列上建索引。一般来说，除非有其它理由，否则只需要在关联顺序中的第二个表的相应列上创建索引

我的解析：

如果有这样的关联：on A.c=B.c ，假设优化器的关联顺序是B,A，即用B表去关联A表，则应该在第二个表即A表的c列上建立索引，而不是B表的c列上建立索引。

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

我的解析：

可能是下面这个意思吧，我也不太确定？？？？？？？？

(假设a为t1和t2共有字段)

GROUP BY t2.a,t1.b (不建议)
GROUP BY t1.a,t1.b (建议)

ORDER BY t2.a,t1.b (不建议)
ORDER BY t1.a,t1.b (建议)

三、优化数据改变语句

1.优化INSERT语句

官方文档：Optimizing INSERT Statements

①如果从同一客户端批量插入多条记录，使用以下的插入。

insert into test values(1,2),(1,3),(1,4)……

②如果从不同客户端批量插入多条记录，可以使用INSERT DELAYED语句得到更高的速度。
DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多。LOW_PRIORITY刚好相反，在所有其他用户对表的读写完成后才进行插入。
③将索引文件和数据文件存放在不同磁盘上。(利用建表中的选项)
④如果批量插入，可以通过增加bulk_insert_buffer_size变量值来提高速度(仅对MyISAM表有效)
⑤当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。(仅适合文本文件)

2.插入大批量数据

对于MyISAM表，当用load命令导入数据时，可以有以下几种方式提高导入效率。

ALTER TABLE 表名 DISABLE KEYS;//关闭非唯一索引的更新
……
导入数据
……
ALTER TABLE 表名  ENABLE KEYS;//打开非唯一索引的更新

在导入大量数据到一个非空的MyISAM表时，通过设置这两个命令可提高导入效率。

在导入大量数据到一个空的MyISAM表时，默认就是先导入数据然后才创建索引，所以不用进行设置。

对于Innodb表，有以下几种方式提高导入效率
①因为Innodb类型的表是按照主键的顺序来保存的，所以将导入的数据按照主键的顺序排序，可以有效的提高导入数据的效率。

②关闭唯一性校验

SET UNIQUE_CHECKS=0;(关闭唯一性校验) 
SET UNIQUE_CHECKS=1;(开启唯一性校验)
show variables like 'UNIQUE_CHECKS';(查看唯一性校验的状态)

③关闭自动提交

导入数据前，关闭自动提交。导入完成后，再开启。

SET AUTOCOMMIT=0;(关闭自动提交) 
SET AUTOCOMMIT=1;(开启自动提交)
show variables like 'AUTOCOMMIT';(查看自动提交的状态)

④关闭外键约束

SET foreign_key_checks=0;(关闭外键约束)
SET foreign_key_checks=1;(打开外键约束)
show variables like 'foreign_key_checks';(查看外键约束的状态)

⑤另外可以同时对mysql进行参数调优

不同版本的mysql参数的默认值可能不同，可以参考mysql官方文档。

常用的几个mysql参数项：(mysql5.6)

max_allowed_packet：默认4M (我设置为64M)
innodb_buffer_pool_size：默认128M，(我8g电脑设置为1g)，配置多大合适请参考这篇文章

我设置了上面两个参数后，导入速度大大加快了。

我的补充：
在开发中我们经常需要将导出的sql文件再导入到mysql数据库中，直接用mysql命令就比普通的source命令导入sql效率高的多。
主要区别就是source是一条条执行的效率当然低的多，而mysql命令则是批量提交，处理效率高。

// 需要在mysql安装目录/bin下执行
mysql -h localhost -uroot -p --default-character-set=utf8  [数据库名] < D:\xx.sql

3.优化Update语句

官方文档：Optimizing UPDATE Statements

An update statement is optimized like a SELECT query with the additional overhead of a write. The speed of the write depends on the amount of data being updated and the number of indexes that are updated. Indexes that are not changed do not get updated.

Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.

4.优化Delete语句

官方文档：Optimizing DELETE Statements

The time required to delete individual rows in a MyISAM table is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable. See Section 5.1.1, “Configuring the Server”.

To delete all rows from a MyISAM table, TRUNCATE TABLE tbl_name is faster than DELETE FROM tbl_name. Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock. See Section 13.1.34, “TRUNCATE TABLE Statement”.

参考资料：

官方文档：Optimizing SQL Statements《深入浅出MySQL：数据库开发、优化与管理维护》第2版
《高性能Mysql》第3版

不积跬步，无以至千里。不积小流，无以成江海！

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。