常用的sql优化
前面我们介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中，除了使用查询外，我 们还会使用一些其他的常用 SQL，比如 INSERT、GROUP BY 等。对于这些 SQL 语句，我们该 怎么样进行优化呢?
一、优化 insert 语句
当进行数据 insert 的时候,可以考虑采用以下几种优化方式
*如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语 句快(在一些情况中几倍)。下面是一次插入多值的一个例子:
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 倍。
二、优化 group by 语句
默认情况下，MySQL 对所有 GROUP BY col1，col2....的字段进行排序。这与在查询中指定 ORDER BY col1，col2...类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则 对 MySQL 的实际执行性能没有什么影响。
如果查询包括 group by 但用户想要避免排序结果的消耗，则可以指定 order by null 禁止排序，如下面例子：
mysql> explain select id,sum(moneys) from sales group by sex\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65743
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select id,sum(moneys) from sales group by sex order by null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65743
Extra: Using temporary
1 row in set (0.00 sec)
从上面的例子可以看出第一个 sql 语句需要进行 'filesort'，而第二个sql由于order by null 不需要进行 'filesort'，而 filesort 往往非常耗费时间。
三、优化 order by 语句
在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。 WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。
例如，下列 sql 可以使用索引
select * from t1 order by key_part1,key_part2,...;
select * from t1 where key_part1=1 order by key_part1 desc,key_part2 desc;
select * from t1 order by key_part1 desc, key_part2 desc;
但是在以下几种情况则不使用索引：
select * from t1 order by key_part1 desc, key_part2 asc; --order by 的字段混合 asc 和 desc
select * from t1 where key2=constant order by key1; --用于查询行的关键字与 order by 中使用的不同
select * from t1 order by key1,key2; --对不同关键字使用 order by
四、优化嵌套查询
MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起 来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)替代。
在下面的例子中，要从 sales 表中找到那些在 company 表中不存在的所有公司信息：
mysql> explain select * from sales where company_id not in (select id from company)\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65743
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: company
type: index
possible_keys: PRIMARY
key: ind_company_name
key_len: 15
ref: NULL
rows: 64
Extra: Using index
2 rows in set (0.00 sec)
如果使用 (join) 来完成这个查询工作，速度将会快很多。尤其是当company表中对id建有索引的话，性能将会更好，具体查询如下：
mysql> explain select * from sales left join company on sales.company_id = company.id where company.id is null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65744
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: company
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test1.sales.company_id
rows: 1
Extra: Using where; Not exists
2 rows in set (0.00 sec)
从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。
连接 (join) 之所以更有效率一些，是因为 mysql 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
五、mysql如何优化or条件
对于含有 or 的查询字句，如果要利用索引，则 or 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
例如，首先使用 show index 命令查看表 sales 的索引，可知它有3个索引，在id、age 两个字段上分别有1个独立的索引，在compay_id和moneys字段上有一个复合索引。
mysql> show index from sales\G;
*************************** 1. row ***************************
Table: sales
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 65744
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_companyid_monesy
Seq_in_index: 1
Column_name: company_id
Collation: A
Cardinality: 20
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_companyid_monesy
Seq_in_index: 2
Column_name: moneys
Collation: A
Cardinality: 20
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
4 rows in set (0.00 sec)
然后在两个独立索引上面做 or 操作，具体如下：
mysql> explain select * from sales where id = 1 or age = 22\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: index_merge
possible_keys: PRIMARY,ind_sales_age
key: PRIMARY,ind_sales_age
key_len: 4,2
ref: NULL
rows: 32873
Extra: Using union(PRIMARY,ind_sales_age); Using where
1 row in set (0.01 sec)
可以发现查询正确的用到了索引，并且从执行计划的描述中，发现 mysql 在处理含有 or 字句的查询时，实际是对 or 的各个字段分别查询后的结果进行了union。
但是当在建有复合索引的列 company_id 和 moneys 上面做 or 操作的时候，却不能用到索引，具体如下：
mysql> explain select * from sales where company_id = 3 or moneys = 0\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_companyid_monesy
key: NULL
key_len: NULL
ref: NULL
rows: 65744
Extra: Using where
1 row in set (0.00 sec)
六、使用sql提示
SQL 提示(SQL HINT)是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些 人为的提示来达到优化操作的目的。
下面是一个使用 SQL 提示的例子:
select SQL_BUFFER_RESULTS * from ...
这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁 定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为 可以尽快释放锁资源 。
下面是一些在 MySQL 中常用的 SQL 提示。
1、use index
在查询语句中表名的后面，添加 use index 来提供希望 mysql 去参考的索引列表，就可以让mysql不再考虑其他可用的索引。
mysql> explain select * from sales use index(PRIMARY) where id = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
2、ignore index
如果用户只是单纯地想让 mysql 忽略一个或者多个索引，则可以使用 ignore index 作为hint。同样是上面的例子，这次来看一下查询过程忽略索引 PRIMARY 的情况：
mysql> explain select * from sales ignore index(PRIMARY) where id = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 65744
Extra: Using where
1 row in set (0.00 sec)
从执行计划可以看出，系统忽略了指定的索引，而使用了全表扫描。
3、force index
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如， 当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描， 而不使用索引，如下所示:
mysql> explain select * from sales where age > 0\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_age
key: NULL
key_len: NULL
ref: NULL
rows: 65744
Extra: Using where
1 row in set (0.00 sec)
但是，当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使 用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示 后再次执行上面的 SQL:
mysql> explain select * from sales force index (ind_sales_age) where age > 0\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: range
possible_keys: ind_sales_age
key: ind_sales_age
key_len: 2
ref: NULL
rows: 32872
Extra: Using index condition
1 row in set (0.00 sec)
果然，执行计划中使用了 force index 后的索引。
SQL 优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问 题都是由不合适的 SQL 语句造成。本章通过实例描述了 SQL 优化的一般过程，从定位一个 有性能问题的 SQL 语句到分析产生性能问题的原因，最后到采取什么措施优化 SQL 语句的 性能。另外还介绍了优化 SQL 语句经常需要考虑的几个方面，比如索引、表分析、排序等。