SQL优化

一、优化 SQL 语句的一般步骤

1、通过 show status 命令了解各种 SQL 的执行频率

show status like 'Com%';

例如：Com_xxx 表示每个 xxx 语句执行的次数,我们通常比较关心的是以下几个统计参数。
    Com_select:执行 select 操作的次数,一次查询只累加 1。
    Com_insert:执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。
    Com_update:执行 UPDATE 操作的次数。
    Com_delete:执行 DELETE 操作的次数。

2、定位执行效率较低的SQL语句

通过慢查询日志定位那些执行效率较低的 SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。

慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 MySQL 在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操
作进行优化。

3、通过 EXPLAIN 分析低效 SQL 的执行计划

通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。例如：

 

select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。
table:输出结果集的表。
type:表示表的连接类型,性能由好到差的连接类型为 system(表中仅有一行,即常量表)、const(单表中最多有一个匹配行,例如 primary key 或者 unique index)、eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用 primary key 或者 unique index)、
ref(与 eq_ref 类似,区别在于不是使用 primarykey 或者 unique index,而是使用普通的索引)、ref_or_null(与 ref 类似,区别在于条件中包含对 NULL 的查询)、index_merge(索引合并优化)、unique_subquery(in的后面是一个查询主键字段的子查询)、 index_subquery (与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)、 range (单表中的范围查询)、index (对于前面的每一行,都通过查询索引来得到数据)、 all (对于前面的每一行,都通过全表扫描来得到数据)。
possible_keys:表示查询时,可能使用的索引。
key:表示实际使用的索引。
key_len:索引字段的长度。
rows:扫描行的数量。
Extra:执行情况的说明和描述。

二、使用索引

三、常用SQL的优化

1、大批量插入数据

(1)当用 load 命令导入数据的时候,适当的设置可以提高导入的速度。
对于 MyISAM 存储引擎的表,可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时,通过设置这两个命令,可以提高导入的效率。

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

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

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

2、优化INSERT语句

当进行数据 INSERT 的时候,可以考虑采用以下几种优化方式。
(1)如果同时从同一客户插入很多行,尽量使用多个值表的 INSERT 语句,这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗,使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。下面是一次插入多值的一个例子:insert into test values(1,2),(1,3),(1,4)...
(2)如果从不同客户插入很多行,能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘,这比每条语句分别插入要快的多;LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入;
(3)将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);
(4)如果进行批量插入,可以增加 bulk_insert_buffer_size 变量值的方法来提高速度,但是,这只能对 MyISAM 表使用;
(5)当从一个文本文件装载一个表时,使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

3、优化 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 sales2 group by id order by null\G;

4、优化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:

5、优化嵌套查询

子查询技术可以使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代。

mysql> explain select * from sales2 where company_id not in ( select id from
company2 )\G;

如果使用连接(JOIN)来完成这个查询工作,速度将会快很多。尤其是当 company2 表中对 id 建有索引的话,性能将会更好。

mysql> explain select * from sales2 left join company2 on sales2.company_id =
company2.id where sales2.company_id is null\G;

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

6、如何优化OR条件

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

从执行计划的描述中,可以发现 MySQL 在处理含有 OR字句的查询时,实际是对 OR 的各个字段分别查询后的结果进行了 UNION。

但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候,却不能用到索引。

mysql> explain select * from sales2 where company_id = 3 or moneys = 100\G;

7、使用 SQL 提示

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

USE INDEX 

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

mysql> explain select * from sales2 use index (ind_sales2_id) where id = 3\G;

IGNORE INDEX

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引,则可以使用 IGNORE INDEX 作为 HINT。同样是上面的例子,这次来看一下查询过程忽略索引 ind_sales2_id 的情况:

mysql> explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;

FORCE INDEX

为强制 MySQL 使用一个特定的索引,可在查询中使用 FORCE INDEX 作为 HINT。例如,当不强制使用索引的时候,因为 id 的值都是大于 0 的,因此 MySQL 会默认进行全表扫描,而不使用索引

mysql> explain select * from sales2 where id > 0 \G;