SQL优化

SQL优化

SQL优化需要先熟悉explain的用法
可以看一下上一篇文章explain详解

1 优化数据插入

大批量插入数据（load）

对于大量的数据插入（文件形式）往往采用load指令，相对于insert会快很多。
但是对于innodb类型的表，还可以进一步提高load插入的效率。

 load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' ENCLOSED BY '"' lines terminated by '\n';

• 主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。

• 关闭唯一性校验

关闭唯一性校验可以适当提高导入效率。
关闭唯一性校验：SET UNIQUE_CHECKS=0;

• 手动提交事务

Mysql使用自动提交需要先执行 SET AUTOCOMMIT=0关闭自动提交设置。然后手动提交事务。待导入完成后，不要忘了再打开自动提交。

少量插入数据（insert）

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。

  示例， 原始方式为：

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  

  优化后的方案为 ：

  insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
  

• 在事务中进行数据插入。

  start transaction;
  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  commit;
  

• 数据有序插入

  insert into tb_test values(4,'Tim');
  insert into tb_test values(1,'Tom');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(5,'Rose');
  insert into tb_test values(2,'Cat');
  

  优化后

  insert into tb_test values(1,'Tom');
  insert into tb_test values(2,'Cat');
  insert into tb_test values(3,'Jerry');
  insert into tb_test values(4,'Tim');
  insert into tb_test values(5,'Rose');
  

2 适当使用索引

mysql索引的使用与注意事项

3 优化order by语句

• 单字段排序

select * from emp order by age desc;

所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。效率低。

优化后
利用覆盖索引优化。
id是主键索引
age ，salary是复合索引

 select age ,id from emp order by age desc;

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

• 多字段排序

因为age ，salary是复合索引，对于这条语句也是使用的索引进行查找排序，效率高。

这里不满足符合索引的条件，salary没有自己单独的索引，因此age使用了索引排序，salary 使用filesort。

这里使用了不同的排序规则，age使用了索引排序，salary 使用filesort。

• filesort优化
  通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，这就需要优化FileSort。mysql4.1之后，Filesort 有两种排序算法，经过适当的调整可以提高查询的性能。

1）双路排序算法：MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取行指针和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

但是双路排序会扫描两次磁盘，磁盘IO是非常消耗性能的，所以后面被单路排序取代。

2）单路排序算法 从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

但是单路排序算法可能会导致一个问题：如果数据量过大，一次读取不完，就会导致读取的次数比双路排序多。

因为读取操作是在 sort_buffer 中，如果数据量过大，超出了 sort_buffer 的容量，导致每次只能读取 sort_buffer 容量大小的数据进行排序，排完再取，导致多次IO。

优化FileSort，就是尽可能的让mysql使用单路排序算法。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定采用那种排序算法，如果max_length_for_sort_data 更大，那么使用单路排序算法；否则使用双路排序算法。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，使mysql使用单路排序算法，从而提高排序的效率。

总结

1. 尽量不使用select * ，使用覆盖索引优化。
2. 通过创建合适的索引，能够减少 Filesort 的出现
3. 对于多字段排序，尽量排序顺序相同，且满足索引左前缀原则。
4. Filesort优化。

4 优化group by 语句

GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

优化后

explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 “filesort”， 而上文提过Filesort往往非常耗费时间。

进一步优化，创建索引 ：

create index idx_emp_age_salary on emp(age,salary);

5 优化嵌套查询

mysql4.1之后可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。也就是子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，由于要使用临时表，子查询的性能往往比较差，而有些情况下，子查询是可以被更高效的连接（JOIN）替代的。

 explain select * from t_user where id in (select user_id from user_role );

执行计划为 :

优化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;

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

6 优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。

获取 emp 表中的所有的索引 ：

explain select * from emp where id = 1 or age = 30;

建议使用 union 替换 or：

explain select * from emp where id = 1 union select * from emp where age = 30;

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL

由此就说明了 UNION 要优于 OR 。

7 优化分页查询

当数据量很大时，我们常遇到以下问题：
获取第100000000-100000010的记录信息，正常使用limit进行分页查询，mysql会排序前100000010条数据，然后取最后十条。性能消耗很大。因此需要进一步优化。

• 优化思路一（覆盖索引，子查询）

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

• 优化思路二 （不推荐使用）

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。(局限性：主键不能断层)

8 使用SQL提示

SQL提示，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

此方法的使用的前提是：必须对数据库和索引信息非常熟悉，不然得不偿失

• USE INDEX

在查询语句中表名的后面，添加 use index 来提供**希望**MySQL去参考的索引列表。

推荐sql使用，但sql不一定使用

explain select * from emp use index(idx_emp_age) where age =25;

• IGNORE INDEX

使用 ignore index 作为 提示，mysql会忽略掉此索引，也就是一定不会使用此索引 （也可以是多个）。

 explain select * from emp ignore index(idx_emp_age) where age =25;

• FORCE INDEX

可在查询中使用 force index 作为提示。强制mysql使用此索引。

强制数据库使用此索引

 explain select * from emp force index(idx_emp_age) where age =25;