常用SQL的优化

本文介绍insert、group by、order by等常用sql的优化。

一、大批量插入数据

对于MyISAM表，大批量插入数据时，可以先通过alter table tb_name disable keys;命令关闭非唯一索引的更新，然后用load data命令导入数据，最后alter table tb_name enable keys;打开非唯一索引的更新。
对于InnoDB表，大批量插入数据（load）的优化方法有以下几种：

1. 因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效提高导入数据的效率。
2. 在导入数据前，执行set unique checks=0，关闭唯一性校验，在导入数据结束后再执行set unique checks=1，恢复唯一性校验。
3. 如果应用使用的是自动提交的方式，可以在导入前执行set autocommit=0，关闭自动提交，导入结束后再执行set autocommit=1，打开自动提交。

二、优化insert语句

当进行数据insert的时候，有以下几种优化方式：

1. 如果同一客户要插入多行数据，应尽量使用多个值表的插入语句。避免多条insert语句。这样可以大大缩减客户端与数据库之间的连接。
2. 如果从不同客户插入很多行数据，可以通过insert delayed语句得到更高的速度。该语句的含义是让insert语句马上执行。
3. 将索引文件和数据文件分在不同的磁盘上存放。
4. 当从一个文本文件装在一个表时，使用load data infile 比使用insert语句快得多。

三、优化order by语句

MySQL的排序方式有两种：

• 通过索引顺序扫描直接返回有序数据，这种方式在explain分析查询的时候extra字段显示Using index
• 通过对返回数据进行排序，这种方式在explain分析查询的时候extra字段显示Using filesort

filesort是通过响应的算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序。Using index方式比Using filesort效率要高得多。所以对order by语句的优化就是尽量减少Filesort排序，通过索引直接返回有序数据，具体做法有是： where 条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，order by字段都是升序或者都是降序。
有时Filesort排序无法避免，此时可以尽量只使用必要的字段，select具体的字段名称，而不是select * 选择所有字段，这样可以减少排序区的使用，提高SQL性能。

四、优化group by 语句

MySQL默认对所有group by col1,col2,...字段进行排序。如果查询包括group by，但用户想要避免排序的性能消耗，可以在group by子句后面加上order by null子句禁止排序。

五、优化子查询

使用子查询的好处：使用子查询可以一次完成逻辑上需要很多步骤的sql操作，同时可以避免事务或者表锁死。
但是子查询的效率不如连接查询（join）快，原因是连接查询MySQL不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作。
所以当子查询和连接查询都能达到查询目地时，如果注重效率，则优先选择连接查询。

六、优化 or 条件

对于含有or的查询子句，如果使用索引，则or子句的每个条件都要使用索引，如果没有索引，则应该考虑增加索引。

七、优化limit子句

有些分页查询场景效率很差，比如limit 10000，20，此时MySQL对10020条记录做了排序，但只需要返回20条记录，前面10000条记录的排序没有价值。
对于limit子句的优化思路是：先对索引分页然后回表查询。
代码示例（updated列建了索引）：

mysql> explain select id,title from tb_item order by updated limit 1000,20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_item
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3083
        Extra: Using filesort
1 row in set (0.00 sec)

可以看出常规方式优化器做了全表扫描，效率不高，

mysql> explain select a.id,a.title from tb_item a inner join
    -> (select id from tb_item order by updated limit 1000,20) b on a.id=b.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1020
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: b.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: tb_item
         type: index
possible_keys: NULL
          key: updated
      key_len: 5
          ref: NULL
         rows: 3083
        Extra: Using index
3 rows in set (0.00 sec)

按照索引分页后回表查询方式改写SQL后，已经看不到全表扫描了 。

八、使用SQL提示

SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些提示来达到优化的目的。

1、use index

use index提示可以让MySQL参考用户提供的索引列表，不去考虑其他可用的索引。

mysql> explain select count(*) from demo use index (city) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: index
possible_keys: NULL
          key: city
      key_len: 153
          ref: NULL
         rows: 10
        Extra: Using index
1 row in set (0.00 sec)

可以看出使用了用户指定的索引city。

2、ignore index

ignore index提示让MySQL忽略一个或多个索引。

mysql> explain select count(*) from demo ignore index (city) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: index
possible_keys: NULL
          key: member
      key_len: 96
          ref: NULL
         rows: 10
        Extra: Using index
1 row in set (0.00 sec)

可以看出忽略了索引city，而使用了索引member。

3、force index

force index提示让MySQL强制使用指定的索引。

mysql> explain select count(*) from demo force index (city) \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: index
possible_keys: NULL
          key: city
      key_len: 153
          ref: NULL
         rows: 10
        Extra: Using index
1 row in set (0.00 sec)

可以看出使用了用户指定的索引city。