SQL优化

SQL优化¹

1 插入数据

1.1. INSERT 优化

1. 批量插入

   -- 每个 insert 控制在 1000个以内
   INSERT INTO tb_test VALUES(1,'Tom'),(2,'Cat');
   

2. 使用手动事务提交

   start transaction；
   INSERT INTO tb_test VALUES(1,'Tom'),(2,'Cat'),(3,'Jerry');
   INSERT INTO tb_test VALUES(4,'Tom'),(5,'Cat'),(6,'Jerry');
   ...
   commit;
   

3. 主键顺序插入

   主键顺序插入性能优于乱序插入

4. 大批量插入数据（LOAD）

   大批量插入数据,如果使用 INSERT 性能较低，可以使用 LOAD 指令进行插入

   • 示例

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

     /root/sql.log

     

2 主键优化

2.1. 数据的组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序存放的，这种存储方式的表称为 索引组织表（IOT，*index organized table*）

2.2. 页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含 2~N 行数据（如果一行数据较大，会行溢出），根据主键排列

主键顺序插入

主键 乱 序插入 & 页分裂

2.3. 页合并

当删除一行记录时，实际记录没有被物理删除，只是被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的 50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化使用空间

提示：

• MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或在创建索引的时候指定

2.4. 主键设计原则

1. 尽量降低主键长度
2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT自增主键
3. 尽量不要使用UUID 或 其他自然数据（如：身份证号）做主键
4. 避免对主键的修改

3 ORDER BY 优化

3.1. Using filesort

通过过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

3.2. Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index ，不需要额外排序，操作效率高

示例:

-- 创建索引后，order by 全部升序或降序 则会 using index
explain select id,age,phone from tb_user order by age,phone
explain select id,age,phone from tb_user order by age desc,phone desc

-- order by 一个升序一个降序，则需要创建对应的升序降序索引
create index idx_age_phone_ad on tb_user(age asc,phone desc);
explain select id,age,phone from tb_user order by age asc,phone desc

3.3. order by 优化小结

1. 多字段排序，也要遵循最左前缀法则
2. 尽量使用索引覆盖
3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建是的规则（ASC/DESC）
4. 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k），若超出则在磁盘文件中排序

4 GROUP BY 优化

4.1. 优化操作

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

示例：

未创建索引：using temporary （使用临时表）

-- 创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
-- 执行分组操作，会使用索引 using index
explain select profession,count(*) from tb_user group by profession
-- where 和 group by 组合 满足最左法则也会使用索引
explain select profession,count(*) from tb_user where profession = '电气工程' group by age

5 LIMIT 优化

5.1. 优化前

当 limit 9000000,10 时，需要MySQL排序前 9000010 条记录 ，返回 9000000-9000010条数据，其他的丢弃，查询排序的代价很大

-- tb_sku 10,000,000 条数据
select * from tb_sku limit 9000000,10;
-- 10 rows in set (19.39 sec)

5.2. 优化方案

1. 优化思路

   通过覆盖索引加子查询的方式实现

2. 示例

   explain select t.* from tb_sku t,(select id from tb_sku order by id limit 9000000,10) a where t.id=a.id;
   

6 COUNT 优化

6.1. 优化前

• MyISAM 引擎把一个表的总行数存在了磁盘上，当查全表的count(*) 效率很高，但有WHERE 条件时 很慢
• InnoDB 引擎，当count(*) 时，要把数据一行一行拿出来累加，很慢

select count(*) from tb_sku;
-- 10000000
-- 1 rows in set (11.02 sec)

6.3. count 的几种用法

• count() 是一个聚合函数，对于返回的结果，一行一行进行判断，如果 count 函数的餐数 不是 NULL 值就加1 ，否则不加，在最后返回累计值
• 用法：count(*)、count(主键)、count(字段)、count(1)
  • count(主键)：InnoDB 引擎会遍历整张表，把每行的id取出来，返回给服务层。服务层拿到主键后，直接按行累加（因为主键不可能为Null）
  • count(字段)
    • 没有 not null 约束：InnoDB引擎会遍历整张表把每行的字段都取出来，返回给服务层，服务层判断后，不为null执行累加操作
    • 有 not null 约束：InnoDB引擎会遍历整张表把每行的字段都取出来，返回给服务层，直接按行进行累加
  • count(1)：InnoDB引擎会遍历整张表，但不取值，服务层对于返回的每一行，放一个数字 “1” 进去，直接按行进行累加
  • count(*)：InnoDB引擎不会把全部字段取出来，而是做了专门的优化，不取值，服务层直接按行进行累加。
• 按效率排序：count(字段) <count(主键id)<count(1) ≈ count(*) 

6.2. 优化思路

1. 自己计数

   采用 redis 等 当insert 时 +1

2. 尽量使用 `count(*)`

7 UPDATE 优化

7.1. 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

事务操作 update 会加锁，update的WHERE条件尽量使用索引

1. 参考黑马程序员 ↩