mysql常用sql语句优化

转载自：http://www.cnblogs.com/gomysql/p/3632209.html

在数据库日常维护中，最常做的事情就是SQL语句优化，因为这个才是影响性能的最主要因素。当然还有其他方面的，比如OS优化，硬件优化，MySQL Server优化，数据类型优化，应用层优化，但是这些都没有SQL语句优化来的重要。下面将介绍INSERT,GROUP BY,LIMIT等的优化方法。

1. 优化大批量插入数据

当用load命令导入数据的时候，适当的设置可以提高导入的速度。

对于MyISAM存储引擎的表

对于MyISAM存储引擎的表，可以通过如下方式快速导入大量的数据：

ALTER TABLE tablename DISABLE KEYS；
loading the data；
ALTER TABLE tablename ENABLE KEYS；

DISABLE KEYS和ENABLE KEYS用来关闭或者打开MyISAM表非唯一索引的更新。
在导入大量的数据到一个非空的MyISAM表示，通过设置这两个命令，可以提高导入的效率。
对于导入大量数据到一个空的MyISAM表时，默认就是先导入数据然后才创建索引的，所以不用设置。

对于InnoDB存储引擎表

对于InnoDB存储引擎表，上面的方式并不能提高导入数据的效率。可以有以下几种方式提高Innodb表的导入效率:

1. 因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
2. 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性效验，在导入数据结束以后执行SET UNIQUE_CHECKS=1，恢复唯一性效验，可以提高导入效率。
3. 如果使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
4. 对于有外键约束的表，我们在导入数据之前也可以忽略外键检查，因为innodb的外键是即时检查的，所以对导入的每一行都会进行外键的检查。

set foreign_key_checks = 0;
load data ............
set foreign_key_checks = 1;

2. 优化INSERT语句

• 如果同时从同一客户端插入大量数据，应该尽量使用多个值的表的INSERT 语句，这种方式将大大减少客户端与数据库服务器之间的连接，关闭等消耗，使得效率比分开执行的单个INSERT语句快(大部分情况下，使用多个值表的INSERT语句能比单个INSERT语句快上好几倍），比如下面一次插入多行：

INSERT INTO user(name,age) VALUES ('yayun',23),('tom',26),('atlas',32),('david',25).......

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

无法使用索引的情况

1. 以%开头的like查询
2. 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符串常量值用引号引起来，否则即便这个列上有索引，MySQL也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索
3. 复合索引的情况下，如果查询条件不包含索引列的最左边部分，即不满足最左前缀原则，则不会使用索引
4. 如果mysql估计使用索引扫描比全表扫描更慢，则不使用索引。(扫描数据超过30%，都会走全表)
5. 用or分割开的条件，如果 or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
6. 字段使用函数，将无法使用索引
7. Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引

3. 优化ORDER BY语句

通过索引排序是性能最好的，通常如果SQL语句不合理，就无法使用索引排序，以下几种情况是无法使用索引排序的。

1. 查询使用了两种不同的排序方向，但是索引列都是正序排序的;
2. 查询的where和order by中的列无法组合成索引的最左前缀;
3. 查询在索引列的第一列上是范围条件;
4. 查询条件上有多个等于条件。对排序来说，这也是一种范围查询

在优化ORDER BY语句之前，先来看看MySQL中排序的方式。先看看MySQL官方提供的示例数据库sakila中customer表上的索引情况。

1.MySQL中有两种排序方式

第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询时显示为Using Index，不需要额外的排序，性能是最优的。

因为查询主键，然后store_id列是辅助索引(二级索引)，辅助索引上存放了索引键值+对应行的主键，所以直接扫描辅助索引返回有序数据。

这种排序方式直接使用了主键，也可以说成是使用了聚集索引。因为innodb是索引组织表（index-organized table），通过主键聚集数据，数据都是按照主键排序存放。而聚集索引就是按照没张表的主键构造一颗B+树，同时叶子节点中存放的即为正张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

第二种是通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或者临时表，取决于mysql服务器对排序参数的设置和需要排序数据的大小。

那么这里优化器为什么不使用store_id列上的辅助索引进行排序呢？

当通过辅助索引来查找数据时，innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那么需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

使用mysql5.6 的trace功能来查看一下强制使用辅助索引和全表扫描的开销。（mysql5.6的trace真心不错，给个赞^_^）

{
   "rows_estimation": [
     {
       "table": "`customer` FORCE INDEX (`idx_fk_store_id`)",
       "table_scan": {
         "rows": 599,
         "cost": 5
       } /* table_scan */
     }
   ] /* rows_estimation */
 },
 {
   "considered_execution_plans": [
     {
       "plan_prefix": [
       ] /* plan_prefix */,
       "table": "`customer` FORCE INDEX (`idx_fk_store_id`)",
       "best_access_path": {
         "considered_access_paths": [
           {
             "access_type": "scan",
             "rows": 599,
             "cost": 719.8,
          "chosen": true,
             "use_tmp_table": true
           }

可以清楚的看见优化器使用全表扫描开销更小。

再来看一种情况

这里为什么又是filesort呢？不是使用了using index吗？虽然使用了覆盖索引(只访问索引的查询，即查询只需要访问索引，而无须访问数据行，最简单的理解，比如翻开一本书，从目录页查找某些内容，但是目录就写的比较详细，我们在目录就找到了自己想看的内容)。但是请别忘记了，idx_stored_email是复合索引，必须遵循最左前缀的原则。

我们改成如下SQL，就可以看见效果了：

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以在同一个时刻，mysql中存在多个sort buffer排序区。该值不要设置的太大，避免耗尽服务器内存。

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

针对Filesort优化MySQL Server

通过创建合适的索引当然能够减少Filesort的出现。但是在某些特殊情况下，条件限制不能让Filesort消失，那就需要想办法加快Filesort的操作。对于Filesort，mysql有两种排序算法。

1.两次扫描算法(Two Passes)

首先 根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。如果sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL 4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针记录获取记录，尤其是第二次读取操作可能导致大量随机I/O操作，优点是排序的时候内存开销比较小。

2.一次扫描算法(Single passes)

一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用哪种排序算法。如果max_length_for_sort_data更大，那么使用第二种排序算法，否则使用第一种。

适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的排序算法，即第二种算法。当然设置max_length_for_sort_data 过大，会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。

适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行，当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_szie参数是每个线程独占，设置过大，会导致服务器SWAP严重。

尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT * 选择所有字段，这样可以减少排序区的使用。提高SQL性能。

4.优化GROUP BY 语句

默认情况下，mysql对所有GROUP BY col1，col2，的字段进行排序。这与在查询中指定ORDER BY col1，col2类似。因此，如果显式包括一个 包含相同列的ORDER BY子句，则对mysql的实际性能没有什么影响。如果查询包括GROUP BY,但我们想要避免排序带来的性能损耗，则可以指定ORDER BY NULL禁止排序，示例如下：

可以看见使用了Filesort，还使用了内存临时表，这条SQL严重影响性能，所以需要优化：

首先禁止排序，ORDER BY NULL

可以看见已经没有使用Filesort，但是还是使用了内存临时表，这是我们可以创建一个复合索引来优化性能

5.优化子查询

MySQL 4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另外一个SELECT语句中。使用子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也非常easy，but，在有些情况下，子查询效率非常低下，我们可以使用比较高大上的写法，那就是连接（JOIN）取而代之.^_^,下面是一个列子：

我解释一下这里的执行计划：

第二行，id为2，说明优先级最高，最先执行，DEPENDENT SUBQUERY子查询中的第一个SELECT(意味着select依赖于外层查询中的数据)，type为index_subquery,与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询,using index使用了覆盖索引。

第一行，id为1，说明优先级最低，可以看见select_type列是PRIMARY，意思是最外层的SELECT查询，可以看见使用了全表扫描。

如果使用连接(join)来完成这个查询，速度将会快很多。尤其是连接条件有索引的情况下：

从执行计划看出查询关联类型从index_subquery调整为了ref，在mysql5.5(包含mysql5.5)，子查询效率还是不如关联查询(join),连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

6.优化OR条件

对于含有OR的查询语句，则无法使用单列索引，但是可以使用复合索引

下面看一个较简单明了的例子：

可以看见表tt有两个单列索引，我们使用如下SQL查询，看是否会使用索引

可以看见虽然显示有id，age索引可用，但是没有使用，即全表扫描。我们可以这样优化：

可以看见已经使用了索引，至于这里的执行计划，我就不再说明。有机会我会写一篇mysql执行计划的文章。

看看使用复合索引查询的情况：

7.优化分页查询(LIMIT)

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。比较常见的一个分页查询是limit 1000,20，这种最蛋碎了，此时mysql排序出前1020条记录后仅仅返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

在索引上完成排序分页操作，最后根据主键关联回表查询所需要的其他列内容。（使用到了自连接）例如下面的SQL语句：

可以看见实际上使用了全表扫描，如果表有上百万记录，那么这将是一条致命SQL

我们改写成按照索引分页后回表读取行的方式，从执行计划中看不到全表扫描了

这里我大概解释一下执行计划：

第三行：

id为2，优先级最高，最先执行
select_type为DERIVED 用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为“派生表”，因为该临时表是从子查询中派生出来的。
type列为index表示索引树全扫描，mysql遍历整个索引来查询匹配的行，这里就把film_id查询出来了。
Extra列为using index 表示使用覆盖索引

第二行：
select_type为PRIMARY，即复杂查询的最外层，当然这里还不算是最最外层。
table列为a，即film表的别名a，
type列为eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

第一行：
select_type列的primary表示该查询为外层查询
table列被标记为<derived2>，表示查询结果来自一个衍生表，其中2代表该查询衍生自第2个select查询，即id为2的select

7.其他优化手段

当然还有其他的优化手段，比如索引提示，我这里简单列举一下就行了，因为大部分的时候mysql 优化器都工作的很好。

USE INDEX
提供给优化器参考的索引列表(优化器不一定给你面子哦)

IGNORE INDEX
提示优化器忽略一个或者多个索引

FORCE INDEX
强制优化器使用某个特定索引

总结一下：
其实SQL语句优化的过程中，无非就是对mysql的执行计划理解，以及B+树索引的理解，其实只要我们理解执行计划和B+树以后，优化SQL语句还是比较简单的，当然还有特别复杂的SQL，我这里只是一些简单例子，当然再复杂的SQL，还是逃脱不了原理性的东西。呵呵。^_^