SQL语句优化

1.大批量导入数据：使用load命令导入数据时，适当的设置能提高效率。

 

对于innodb类型的表操作如下：

        1）主键顺序插入：load data local infile '需导入文件本地路径' into table '需要加载到的表名' fields terminated by '每个之间使用什么分隔' lines terminated by '每行之间使用什么分隔';    (有序的数据导入效率高于无序数据)

mysql> load data local infile '/root/sqlsj.log' into table 'tb_test' fields terminated by ',' lines terminated by '\n';

        2）关闭唯一性校验：在导入数据前执行 SET UNIQUE_CHEKS=0，关闭唯一性校验；在导入结束后执行 SET UNIQUE_CHEKS=1,恢复唯一性校验，可提高导入的效率。

        3）手动提交事务：如果应用了自动提交事务，建议在导入数据前执行 SET AUTOCCOMMIT=0 来关闭自动提交；结束后再执行 SET AUTOCCOMMIT=1，打开自动提交，这样也能提高一定的效率。

2.优化insert语句：

-- 单条插入
insert into tb_test values(1,'zhangsan',20);

-- 批量插入  也可以搭配循环实现大批量插入
insert into tb_test values(1,'zhangsan',20),(2,'lisi',25),(3,'wangwu',22);

3.优化 order by 语句：

        了解：mysql中Order by的 using filesort排序 (通过对返回数据进行排序，即通过文件系统来排序) 和 using index排序 (通过有序索引顺序扫描直接返回有序数据，不需要额外排序) 的两种方式。

        在清楚此两种排序之后，便明确了需要尽量减少额外的排序，通过索引直接返回有序数据。where条件和 Order by 使用相同的索引，并且Order by的顺序和索引顺序相同，而且Order by的字段都是升序或者降序。

        1）Filesort优化：创建合适的索引能减少Filesort的出现。

                Filesort有两种排序算法：两次扫描算法和一次扫描算法。

                两次扫描即是根据条件取出排序字段和行指针信息，然后在 sort buffer排序区中排序，不够再在临时表中存储排序结果，完成排序后，再根据行指针回表读取记录，这样会导致大量的i/o操作。

                一次扫描算法则是一次性取出满足条件的字段，然后在sort buffer中排序后直接输出结果集。虽然这样内存消耗大，但排序效率要高与两次扫描。

        所以可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量来增加排序区的大小，从而提高排序的效率。

4.group by优化：与order by相比，group by主要是多了排序之后的分组操作，但在实现过程中一样可以利用到索引。

        1）若查询含group by且想避免排序结果的消耗，可以执行 order by null 禁止排序。

mysql> explain select age,count(*) from emp group by age;  (这需要进行filesort)

优化如下：
mysql> explain select age,count(*) from emp group by age order by null; (不需要进行filesort)

         2）创建索引：

mysql> create index idx_emp_age_salary on emp(age,salary);

5.子查询（嵌套查询）：使用多表连接查询代替子查询。

     嵌套查询：select * from  t_user where id in (select user_id from user_role);

     多表连接查询：select * from  t_user u，user_role ur where u.id =ur.user_id;

6.or条件优化：若要利用索引，则or之间的每个条件列都必须用到索引，且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

示例：select * from emp where id = 10 or age = 20;   （可以加explain查看执行）

union 替换 or：

示例：select * from emp where id = 10 union select * from emp where id = 20; 

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

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

7.优化分页查询：

例：select * from tb_item limit 1000010,10;   (需排序1000010条记录，却只返回后面10条记录，其他丢失，查询代价大)

        优化：

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

select * from tb_item t , (select id from tb_item order by id limit 1000010,10) a where t.id = a.id;

        2）可以把limit查询转换成某个位置的查询。 --适用于主键自增的表

select * from tb_item where id > 1000000 limit 10;