mysql SQL语句优化

大批量数据导入

1.数据顺序id比乱序id的性能快

因为主键索引的原因，乱序id插入导致，索引树的结构在一直改变

2.关闭唯一性校验

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

3.手动提交事务

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

 

批量插入数据insert

多条SQL插入语句

insert into tb_test values(2,'李四');
insert into tb_test values(1,'张三');
insert into tb_test values(3,'王五');

1.优化为一条SQL语句，避免多次建立关闭数据库连接

insert into tb_test values(1,'张三'),(2,'李四'),(3,'王五');

2.在事务中进行数据插入，避免每次插入都开启关闭事务

start transaction;
insert into tb_test values(1,'张三');
insert into tb_test values(2,'李四');
insert into tb_test values(3,'王五');
commit;

3.数据有序插入

insert into tb_test values(1,'张三');
insert into tb_test values(2,'李四');
insert into tb_test values(3,'王五');

 

order by语句排序优化

1.两种排序

filesort排序：对返回结果排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

using index排序：通过有序顺序扫描直接返回有序数据，不需要额外排序。和覆盖索引同理。

尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by 使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现filesort。

2.filesort排序优化

对于filesort，mysql有两种排序算法：

1）两次扫描算法：mysql4.1之前，使用该方法排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据指针回表读记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

mysql通过比较系统变量max_length_for_sort_data 的大小和query语句取出的字段总大小，来判断使用哪种排序算法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

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

show variables like 'max_length_for_sort_data';
show variables like 'sort_buffer_size'

 

 

group by 语句优化

group by 底层会进行排序操作，如果不想要排序的话可以加上order by null

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

添加索引提高查询效率

 

 

子查询优化

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

 

or优化

or关联的条件必须每个字段都有索引，不然会导致索引失效。

复合索引用or连接会失效，比如复合索引 idx_age_name sql：select * from user where age=18 or name='张三' 这里or右边的name不满足最左匹配原则，索引失效。

优化方案使用union

 

分页查询优化

limit 200000,10 会取出满足条件的200010条数据，然后丢弃前200000条记录，然后返回10条记录

select * from user limit 200000,10

优化方案1：主键id无序

select * from user a left join (select id from user limit 200000,10) b on a.id = b.id;

优化方案2： 如果表id严格递增，且没有断层才能使用

select * from user where id >= 200000 limit 10

优化方案3：如果主键id有序

select * from user where id >= (select id from user limit 200000,1) limit 5;

 

使用sql提示

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

1.use indx 告诉数据库查询的时候使用这个idx_age索引去查询，仅仅是个参考

select * from user use index(idx_age);

2.ignore index 忽略这个索引

select * from user ignore index(idx_age);

3.force index 强制使用索引,有时数据库认为全表扫描比索引快，就会进行全表扫描，这个时候可以强制走索引

select * from user force index(idx_age);

 

前缀索引

使用前缀索引来减少空间损耗