常见的SQL优化（二）

（一）常见的SQL优化【针对InnoDB类型的表】

①大批量插入数据

（1)因为 InnoDB 类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效地提高导入数据的效率。

(2)在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率。eg：

SET UNIQUE_CHECKS=0;
#loading the data

load data infile '/home/mysql/film_test.txt' into table film_test2;

SET UNIQUE_CHECKS=1;
(3)如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,
导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。
eg：

SET AUTOCOMMIT=0;
#loading the data
load data infile '/home/mysql/film_test.txt' into table film_test2;
SET AUTOCOMMIT=1

②优化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禁止排序。

④优化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)替代。

　　连接(JOIN)之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

⑥MySQL如何优化OR条件

     对于含有 OR 的查询子句,如果要利用索引,则 OR 之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引。

MySQL 在处理含有 OR字句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION

⑦使用SQL提示

SQL 提示(SQL HINT)是优化数据库的一个重要手段,简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

1.USE INDEX

在查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ref possible_keys: ind_sales2_id key: ind_sales2_id key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec).

 

2.IGNORE INDEX

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引,则可以使用 IGNORE INDEX 作为 HINT。

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec).

 

3.FORCE INDEX

为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 HINT。

例如,
当不强制使用索引的时候,因为 id 的值都是大于 0 的,因此 MySQL 会默认进行全表扫描,而不使用索引,如下所示:

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from sales2 where id > 0 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: ALL possible_keys: ind_sales2_id key: NULL key_len: NULL ref: NULL rows: 1000 Extra: Using where 1 row in set (0.00 sec)

 
但是,当使用 FORCE INDEX 进行提示时,即便使用索引的效率不是最高,MySQL 还是选择使用了索引,这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示后再次执行上面的 SQL:

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> explain select * from sales2 force index (ind_sales2_id) where id > 0\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sales2 type: range possible_keys: ind_sales2_id key: ind_sales2_id key_len: 5 ref: NULL rows: 1000 223Extra: Using where 1 row in set (0.00 sec).

 

 执行计划中使用了 FORCE INDEX 后的索引