SQL语句的优化步骤（二）---mysql语句优化

       mysql数据库时使用较为广泛一种数据库语言，为了提高sql语句的执行效率，在我们平时的设计过程中，可以借鉴下面几个方面进行设计，可以有效的提高sql语句执行效率。

1、大批量插入数据 （sql文件插入）

        通过 load 命令导入数据的时候，适当的设置可以提高导入的效率

        对于innoDB类型的表是按照主键的顺序保存的，所以将需要导入的数据按主键的顺序导入，可以有效的提高数据的效率

   1）主键顺利插入

补充： head sql文件名 查看文件数据内存

数据导入格式

load data local infile '路径 文件名' into table '表名'
            fields terminabled by ',' 
            lines terminated by '\n'

nto table :                 文件数据需要插到那个表中

feilds terminabled ： 字段以什么符号分割

lines terminabled:      行以什么符号分割

注意：有主键时导入速度会更快，所以建议使用使用主键

   2）关闭唯一性校验

       关闭唯一性校验，等数据导入完成后再开始唯一性校验，也可以提高效率

关闭唯一性校验 set unique_checks=0
开启唯一性校验 set unique_checks=1

注意：   开启和关闭必须成对存在

    3）手动关闭提交事务

             手动关闭自动提交，然后等数据导入完成后再打开自动提交，也会提高效率

set auto_committed=0; 
set auto_committed=1;

2、优化insert语句

   1).尽量使用单个insert 语句块

       insert into student values ( , ) ,( , );

   2).使用事务的手动提交

set auto_committed=0; //关闭自动提交
start transaction;   //开启事务
insert into...... //select执行语句
commit(); //手动提交
set auto_committed=1;  //开启自动提交

3.数据有序插入（主键有序插入）

//按主键有序插入

insert into student values(1,);

insert into student values(2,);

3、order by 优化

           优化方式，

                    使用索引排序（using index）

                    使用一次扫描排序（filesort）

  3.1 两种排序方式

         filesort(文件系统排序) ：不是直接通过索引返回结果

         using index(索引排序) ：直接通过索引返回结果（返回字段都是索引字段），操作效率高

1、单字段排序

2、多字段排序：都按asc或都按desc排序，不一致时会出现filesort排序

3、按order by后是多字段排序的时候，要按照索引的顺序进行排序

      如 idx_age_salary ，order by age,salary

   3.2 Filesort 优化

        对于filesort ，mysql中有两种算法

1.两次扫描算法（mysql4.1之前）

           首先根据条件取出排序字段和行指针信息，在sort buffer中排序，其次在根据行指针回表读取记录，该操作可能会造成大量随机IO的操作，效率低

2.一次扫描算法

      一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集，内存开销较大，但是效率较高

       mysql中怎么选择的？

 比较系统变量max_length_for_sort_data和Query语句中取出的字段总大小

 max_length_for_sort_data大的话，会采用第二种算法

优化策略

          提高max_length_for_sort_data和sort_buffer_size的值,增大排序区的大小，让其强制使用第二种（一次扫描算法），提高排序的效率

设置max_length_for_sort_data 
show variables like 'max_length_for_sort_data'; 
set max_length_for_sort_data=值; 

设置 sort_buffer_size 
show variables like 'sort_buffer_size';
 set sort_buffer_size=值；

4、group by 分组排序

 优化：索引字段分组排序，需要分组字段设置为索引提高效率

           因group by在运行的时候，也使用order by排序 ，继而可以采用索引字段进行分组排序。

换句话说：就是将分组的字段设置为索引字段，这样在分组的时候，就是按照索引分组排序，效率更高

5、优化嵌套查询（子查询）

      优化：尽量少使用子查询，改为表连接join 查询

6、OR的优化

       优化：OR关联条件都是索引字段

       优化：使用union 连表优化（推荐）

使用OR 提高效率时，应该注意的是：  

 1、 OR所关联的条件必须都是索引字段，否则将不是无法使用索引查询

  2、 不能是复合索引，必须用单列索引

7、limit 分页查询

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

//优化前
select * from student limit 20000,10;

//优化后

select * from student a,(select id from student limit 20000,10) b where a.id=b.id;

优化二、适用于主键自增的表(自增的主键不能出现断层)，可以把limit查询转换成某个位置的查询

select * from student where id >20000 limit 10;

8、使用SQL提示（索引提示）

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

1、USE INDEX 推荐使用那个索引(在from 表名后使用)

      在查询语句中表名的后面，添加use index来提供希望mysql去参考的索引列表，就可以让mysql不再考虑其他可用的索引

    select * from 表名 use index(索引) where ..... 案例

select * from student use index(name_index) where name='张三';

2、IGNORE INDEX 忽略索引

   select * from 表名 ignore index(索引) where ..... 忽略那个索引

3、FORCE INDEX 强制使用哪个索引

    select * from 表名 force index(索引) where ..... 忽略那个索引

  注意：use和ignore 只是推荐，不是强制，force是强制性使用