SQL优化（部分）

SQL优化

insert优化

• 批量插入：通过拼接成一条SQL执行（建议一次拼接500条左右）

• 手动提交事务：每次插入都会涉及到事务的开启和关闭

• 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入(100W数据大概就15s)

  

  # 客户端连接服务端的时候，加上参数 --local-infile
  mysql --local-infie -u root -p
  
  #设置全局参数local-infile为1，开启本地加载文件导入数据的开关
  set global local-infile = 1
  
  # 执行loal指令，将准备好的数据加载到表结构中
  load data local infile '/root/sql1.log' into table tb_user’ fields terminated by ',' lines terminated by '\n' ;
  

• 插入会有页分裂，删除会有页合并

order by优化（前提是使用了覆盖索引）

覆盖索引直通车----->点以下我呗

1. Using filesort :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所以不是通过索引直接返回排序结果的排序都叫FileSort排序。
2. Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
3. 一个升序一个降序，就会出现using filesort，可以通过建立联合索引的时候指定升序降序

# 使用的前提是使用了覆盖索引
# order中用a,b两个字段排序：这样就会出现using filesort
select id,a,b from table_name order by a ASC,b DESC;
# order中用a,b两个字段排序,创建联合索引时指定a为升序，b为降序:这样就是using index
create index ind_a_b on table_name(a ASC,b DESC)；

4. 如果不可避免出现using filesort，大量数据排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256K），如果超出这个大小，就会在磁盘中排序

# 查询sort_buffer_size
show variables like 'sort_buffer_size';

group by优化

建立合适的索引，遵循最左前缀法则，使用覆盖索引

limit优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

• 注意：在8版本中，in后面的数据不支持limit操作

  # 错误
  select * from table_name where id in (select id from table2_name limit 20)
  

  # 正确优化
  # 这里的子查询就是使用了覆盖索引，只查id，加入oder by是为了数据的正确性，如果
  # 不加order by 可能会导致结果不一致，具体原因我这章节就不展开说了
  select a.* from table_name a,(select id from table2_name order by id limit 900000,10) b where a.id = b.id
  

count优化

update优化

• 进行update的时候要使用索引去修改，因为只有走索引的update才是行锁，如果不走索引的update就会升级成表锁，在高并发下会影响性能
• innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

总结

1. 插入数据
2. 主键优化
3. order by优化
4. group by优化
5. limit优化
6. count优化
7. update优化