[MySQL] 常用SQL的优化--18.4

这里介绍下，Insert、Group By等SQL语句的优化方法：

1、大批量数据插入

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

1.1 对于MyISAM表，可以先禁用非唯一索引更新，再导入数据来快速导入大量的数据。

alter table table_name disable keys;

load date infile 'xxx.txt' into table_name;

alter table table_name enable keys;

1.2 对于InnoDB表：

1）因为InnoDB表必须有主键，表是按照主键的顺序保存的，所以将要导入的数据按照主键排序，

可以有效地提高导入数据的效率。

2）如果表中存在唯一性约束，则在导入数据前执行关闭唯一性校验，可以提高导入效率。

set unique_checks=0;

loading the data;

set unique_checks=1;

3) 如果应用使用自动提交的方式，建议在导入前执行set autocommit=0,关闭自动提交。

这样不用每次insert都去磁盘写日志，节省I/O。

set auto_commit=0;

loading the data;

set auto_commit=1;

2、优化Insert语句

1）如果是同一客户插入很多行，应尽量使用多个值表的Insert语句。（节省连接，关闭等消耗）

insert into test values(1,2),(1,3),(1,4);

2）如果从不通客户端出入多行，可以使用Insert Delayed语句得到更高的速度。Delayed含义是让

Insert 语句马上执行，不用等到其他用户对表的读写完成后才进行插入。LOW_PRIORITY刚好相反。

3）将数据文件和索引文件分别存在不同的磁盘上，加快访问速度（建表时可以指定，或者my.cnf里）。

4）如果是对MyISAM表进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提速。

5）当从一个文本文件装载一格表时，使用load data infile，比大量insert语句快20倍。

3、优化ORDER BY语句

3.1 MySQL的两种排序

第一种是有序索引通过顺序扫描直接返回有序数据，这种方式在使用explain分析查询的时候显示

为Using Index,不需要额外的排序，操作效率高，例如：

第二种是通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序

都叫做Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是

否使用了磁盘文件或者临时表等，则取决于MySQL服务器对排序参数的设置和需要排序的数据的大小。例如：

又如,对组合索引进行访问，并在索引idx_storeid_email上发生了一次操作排序，所以执行计划中仍然有Using Filesort。

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存

装载不下，他就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个快合并成有序的结果集。sort_buffer_size

设置的排序区是每个线程独占的，所以可以有多个sort buffer存在。

MySQL优化目标：尽量减少额外的排序，通过索引直接返回有序数据。

Where条件和order by使用相同的索引，并且order by的顺序和索引的顺序相同，并且order by的字段都是升序或者

都是降序。如果不满足，则肯定需要额外的排序操作，这样就会出现Filesort。

总结，下列SQL可以使用索引（符合组合索引的最左原则）：

但是在以下几种情况下则不能使用索引：

3.2 Filesort的优化

通过创建合适的索引就能减少Filesort的出现，但是某些情况下，不能避免，就要加快Filesort的操作。

在MySQL中，Filesort有两种排序算法：

• 两次扫描算法：先根据条件取出排序字段和行指针信息，之后在排序区中排序。再之后，通过排序后的
  行指针信息回表读取数据。
• 一次扫描算法：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。
  排序时内存开销比较大，但是排序效率比两次扫描算法高。

4. 优化group by 语句

默认情况下，MySQL对所有group by col1，col2，...的字段进行排序。与order by col1,col2...类似，如果

group by后显示的包括一个包含相同列的order by 子句，则对实际执行性能没什么影响。

如果查询包含group by但用户想要避免排序结果的消耗，则可以指定order by null禁止排序，如下面例子：

5.优化嵌套查询

使用子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，写起来容易。但是，有些情况下需要

用更有效率的连接（join）来替代。

使用连接（join）来替代子查询（in）

6. MySQL优化or条件

对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；

如果没有索引，要考虑增加索引。

例如：有三个索引，id,year,(company_id,moneys);show index from table_name;查看索引

由上看出，正确的使用了索引，并从执行计划中获得，MySQL处理含有OR字句的查询时，实际上是对OR的各个字段

分别查询后的结果进行了UNION操作。

但是当在组合索引的列company_id和moneys上面做OR操作时，却不能用到索引。

7.优化分页查询

一般分页查询时，通过创建覆盖索引能比较好的提高性能。一个常见又非常头疼的分页场景是“limit 1000，20”，

此时MySQL排序出前1020条记录后仅需要返回第1001到1020条记录，前1000条记录都被抛弃，查询和排序的代价非常高。

1）第一种优化思路：（使用子查询查出索引id limit m,n后联表查询）

在索引上完成排序分页的操作，最后根据主键关联取回原表查询所需要的其他列内容。

例如：处理效率不高的语句：

按照索引分页后回表方式改写SQL后：

2）第二种优化思路（将limit m,n 转变成limit n,过滤前m行后开始查询）

explain select film_id ,description from film where film_id >50 order by film_id asc limit 5\G

注意,这样把limit m,n转换成 limit n的查询，只适合在排序字段不会出现重复值的特定环境（唯一键或者

主键），能够减轻分页翻页的压力。如果排序字段出现大量重复值，则不适合这样优化方式。

8.使用SQL提示（SQL HINT，手动干预执行计划）

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

的目的。比如：

select sql_buffer_results * from ...

这个语句将强制MySQL生成一个临时结果集。生成临时结果集后，所有表上的锁定均被释放。这能在遇到表锁定问题时

或要花很长时间将结果传给客户端时很有帮助，因为可以尽快释放锁资源。

常见的SQL HINT：

1）USE INDEX（提供希望MySQL去参考的索引列表，但MySQL可能不会采纳）

explain select count(*) from rental use index (idx_rental_date)\G

2）IGNORE INDEX(让MySQL忽略一个或多个索引)

explain select count(*) from rental ignore index (idx_rental_date)\G

3）FORCE INDEX（强制MySQL使用一个特定的索引）

explain select* from rental force index (idx_fk_inventory_id) where inventory_id > 1\G