MySQL增删改查SQL语句优化

MySQL增删改查SQL优化

插入数据

INSERT优化

1. 批量插入

• 每次插入最好不超过1000条。

2. 手动提交事务

• 防止事务频繁开启和提交。

3. 主键顺序插入

• 顺序插入性能高于乱序插入

大批量插入数据

• 使用LOAD指令插入（不是用语法）
  没装linux，详细load步骤可见此链接

主键优化

主键设计原则

设计原则
尽量降低主键长度（可减小二级索引占用的空间）
插入数据时，尽量顺序插入，选择自增主键
尽量不要使用UUID做主键或其他自然主键(如身份证号)
尽量避免对主键的修改

数据组织方式

• InnoDB中表数据是根据主键顺序组织存放的（索引组织表）
• 主键乱序插入时，需要通过页分裂组织数据（排序）

页分裂

• 页可以为空，也可以填充一半或全填充。
• 每个页包含2-N行数据（若数据过大，会行溢出）

页合并

• 删除数据时，并不是物理删除，仅设置删除标记，且可以被其他记录声明使用
• 当删除记录达到MERGE_THRESHOLD(阈值)，InnoDB会查找相邻的页，查看是否可以合并

ORDER BY优化

Using filesort

• 读取满足条件的数据行，在排序缓冲区sort buffer中完成排序操作。

Using index

• 通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高 。
• 可以根据排序需求创建索引（升降序都可以）（创建索引时字段名后＋ asc/desc）

注意事项

1. 根据排序字段建立合适的索引，多字段排序时也遵循做前缀法则。
2. 尽量使用覆盖索引。
3. 多字段排序，需注意联合索引在创建时的规则(ASSC/DESC)。
4. 大量数据排序时，若不可避免地出现filesort，可适当增大排序缓冲区大小sort_buffer_size。

• 查询排序缓冲区大小：

SHOW VARIABLES LIKE 'sort_buffer_size';

GROUP BY优化

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

LIMIT优化

• 例：
  执行limit 20000000,10时，需要MySQL排序前20000010条记录，查询排序代价大。
• 优化：（覆盖索引查询＋子查询）
  子查询不能嵌套limit，要建立临时表，通过多表联查实现。

COUNT优化

• MyISAM存储了表的总行数，执行COUNT(*)时会直接返回总数，但不能有条件（只记表长）
• InnoDB需要把数据一行一行地读出来再累积计数
• 优化：
  自己计数，创建计数表，插入数据时在计数表中+1。

不同COUNT()用法的遍历原理

count()	遍历原理
COUNT(主键)	遍历全表，取id值，直接按行累加
COUNT(字段名)	遍历全表，取字段值，只计算不为NULL的值
COUNT(1)	遍历全表，不取值，直接进行累加（放什么数字无所谓）
COUNT(*)	遍历全表，不取全部字段值，直接累加（已优化）

UPDATE优化

• 以索引字段为过滤条件时，UPDATE为行锁。
• 以非索引字段为过滤条件时，UPDATE为表锁。