Sql优化

一、insert 优化

1. 批量插入

 insert into table values（1,'tom'）,(2,'jerry');

2. 手动提交事务

3. 主键顺序提交

4. load大批量插入数据

#客户端连接服务端时，加上参数 --local-infile
mysql --local-file -u root -p

#设置全局参数local_infile为1，开启本地加载文件导入数据的开关

set global local_infile = 1

#执行load指令将准备好的数据 加载到数据库的表中

load data local infile 'data.sql' into table 'user' fields terminated by '\n';

二、主键优化

• 满足业务需求的情况下，尽量降低主键的长度。

• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCEEMENR自增主键。

• 尽量不要使用uuid做主键或者是其他自然主键，如身份证号。

三、order by优化

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫
• FileSort 排序。 Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

• 尽量使用覆盖索引。

  create index idx_user_age_phone on user(age,phone);
  select id,age,phone from user where age = 20 order by phone;
  

• 多字段排序， 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC / DESC）。

  # 以下sql出现Using filesort
  select id, age, phone from user order by age asc, phone desc;
  # 创建适合的索引
  create index idx_user_age_phone_ad on user (age asc, phone desc);
  

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

  # 查看缓冲区的大小
  show variables like 'sort_buffer_size';
  # 设置缓冲区的大小
  set global sort_buffer_size = 缓冲区大小数值;
  

四、group by 优化

• 在分组操作时，可以通过索引来提高效率。
• 分组操作时，索引的使用也是满足最左前缀法则的。

select age,count(*) from user group by age;

五、limit 优化

在数据量比较大时，如果进行limit分页查询，越往后，效率越低。
因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。
一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

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

六、count 优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
但是如果是带条件的count，MyISAM也慢。 InnoDB 引擎执行 count(*)的时候，需要把每一行数据从引擎读出来，然后累积计数。

优化思路：自己计数(可以借助于redis，但如果是带条件的count又比较麻烦了，新增数据时count值加1，删除数据时count加1)。

count的几种用法

• count(主键)：InnoDB引擎会遍历整张表，把每一行的主键都取出来，返回给服务层，然后由服务层直接进行累加
• count(字段)：若有not null 约束，与count(键)类似，直接进行累计；若没有，则还要在服务层判断是否为null。
• count( 1 )：InnoDB引擎遍历全表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，然后直接按行进行累加。
• count(*)：InnoDB引擎不会取出全部字段，而是做了特殊优化，服务层直接按行进行累加。

按照效率排序：count(字段) < count(主键) < count( 1 ) < count(*)

七、update 优化

在执行update的sql语句时，需注意使用的where条件，对其建立相应的索引，此时只会对当前行进行锁定。
若不在索引列或索引失效，整个表将被锁定。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。