SQL 优化

insert

• 批量插入，500-1000条一批次
• 手动提交事务
• 主键顺序插入
• 大批量数据插入使用MySQL提供的load指令

update

• where条件后字段需建立索引，避免将行锁升级为表锁
• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

order by

• Using filesort（使用文件排序）、Using index
• 根据排序字段建立合适的索引，多字段排序时也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则，默认都是ASC
• 如果不可避免地出现 filesort，大数据量排序时，可适当增大排序缓存区的大小sort_buffer_size（默认256K）

show variables like 'sort_buffer_size';

输出：

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

group by

• Using temporary（使用临时表）、Using index
• 建立索引
• 同样遵循最左前缀法则

limit

• 通过覆盖查询+子查询的方式

select t.* from test t, (select id from test order by id limit 10000000, 10) temp where t.id = temp.id;

• 改成前一页、后一页，携带每组记录最后一条的id请求下一次分页数据

select * from test where id > ? order by id limit 10;

count

• count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
• count(字段)：
  • 没有not null约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  • 有not null约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
• count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。
• count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按效率排序：count(主键)<count(字段)<count(1)≈count(*)，所以尽量使用count(*)。