eleven26
0%

SQL 优化

Posted on

插入数据

插入多条数据的优化方式：

  1. 批量插入

一次插入数据不建议超过 1000 条，如果要插入的数据太多，可以分批插入

1
insert into tbl values (a, b), (c, d);
  1. 手动提交事务

MySQL 里面的事务提交方式默认是自动提交的，也就是执行一条语句会提交一次，这样效率很低。

1
2
3
4
start transaction;
insert into tbl values (a, b), (c, d);
insert into tbl values (a1, b1), (c1, d1);
commit;
  1. 主键顺序插入

主键顺序插入的性能要高于乱序插入的性能。见下一节。

  1. 大批量插入数据（如 100w 条）

如果一次性需要插入大批量数据，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入。

通过 load 指令，我们可以一次性将本地磁盘文件中的数据全部加载进数据库表结构当中。

操作如下：

1
2
1,a,b,2022-10-27
2,c,d,2022-10-28

磁盘文件为 csv 格式，通过 load 命令可以将这个文件的全部内容加载到数据库中。

1
2
3
4
5
6
7
8
# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

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

# 执行 load 指令将准备好的数据，加载到表结构中
load data local infile "/Users/ruby/a.csv" into table tbl fields terminated by "," lines terminated by "\n";

100w 的数据通过 load data 耗时十几秒，但是通过读取然后 insert 的方式需要 10 分钟。

主键顺序插入性能高于乱序插入。

主键优化

  • InnoDB 数据组织方式

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

  • 页分裂

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

在前后两个页满的时候，如果插入的主键也要插入这其中的一页，那么就会导致页分裂。

  • 页合并

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

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

MERGE_THRESHOLD 参考文档

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

  • 主键设计原则
  1. 满足业务需求的情况下，尽量降低主键的长度。（减少空间占用，除了主键索引会使用主键，二级索引的叶子结点存的也是主键的值）
  2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。（乱序插入可能会导致页分裂）
  3. 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。（因为这些数据插入的时候其实就等于是乱序插入，而且占用空间也会比整型自增主键，int 是固定的 4 字节，身份证号十几个字节了）
  4. 业务操作时，避免对主键的修改。（会导致主键索引的调整）

order by 优化

目的：通过建立合适的索引，优化去掉 filesort。

MySQL 里面的排序有哪些？

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

优化 order by 语句的时候，尽量优化为 using index。

如果知道 order by 使用了哪种方式？

使用 explain，extra 会显示排序使用了哪种方式。

如何优化？

  1. order by 的字段加索引。(多个字段的话，建立联合索引)
  2. 如果是多个字段的排序，则创建索引的时候，不同字段的顺序要跟排序时候的顺序一致。如 order by a asc, b desc 语句，则建立索引的时候就需要是 a 顺序，b 逆序。

show index from tbl; 结果里面的 collation 的 A 表示是升序，D 表示是降序。

示例

前提：覆盖索引。如果是 select * 则又是 using filesort 了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 没有创建索引时，根据 age，phone 进行排序。排序的方式显示是 using filesort
explain select id,age,phone from user order by age,phone;

-- 创建索引
create index idx_user_age_phone_aa on user(age,phone);

-- 创建索引后，根据 age,phone 进行升序排序。排序的方式显示是 using index
explain select id,age,phone from user order by age,phone;

-- 创建索引后，根据 age,phone 进行降序排序。排序的方式依然是 using index，这是因为索引是双向的链表结构。
explain select id,age,phone from user order by age desc, phone desc;

-- 根据 age,phone 一个升序，一个降序。排序的方式也出现了 using filesort
explain 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);

-- 根据 age,phone 进行一个字段升序，一个字段降序的排序。排序方式显示 using index。
explain select id,age,phone from user order by age asc, phone desc;

using index 意味着从索引返回的数据已经是有序的了，所以不需要再进行排序。

order by 优化总结

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  • 如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认 256K）。（如果要排序的数据太多可能会用到磁盘文件来排序）

group by 优化

索引对于分组操作的影响。

关键：联合索引、覆盖索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 执行分组操作，根据 profession 字段进行分组。显示没有用到索引，extra 显示 using temporary。
explain select profession, count(*) from user group by profession;

-- 创建索引。
create index idx_user_pro_age_sta on user(profession, age, status);

-- 执行分组操作，根据 profession 字段进行分组。用到了索引 idx_user_pro_age_sta，extra 显示 using index。
explain select profession, count(*) from user group by profession;

-- 执行分组操作，根据 profession,age 字段进行分组。用到了索引 idx_user_pro_age_sta，extra 显示 using index。
explain select profession, age, count(*) from user group by profession,age;

-- 用到了索引 idx_user_pro_age_sta，extra 显示 using index。
explain select age,count(*) from user where profession='软件工程' group by age;

总结

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

limit 优化

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

优化思路

覆盖索引 + 子查询。

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

实例

1
2
3
4
5
6
7
-- 5s
select * from sku limit 100000,10

-- 优化，0.133 秒
SELECT * from sku a, (SELECT id from sku LIMIT 100000,10) as b WHERE a.id = b.id

-- limit 500000,10 的时候，第一种方式 22s，第二种方式 0.35s

分析

  • 在上面第一个语句中，因为是 select * 所以这个查询没有用到索引，是全表扫描。
  • 而在第二个查询中，我们先是在子查询里面查询出了 id，而这个查询因为用到了索引，所以会快很多。
  • 然后拿到 id 后再去匹配 sku 表，这个过程也能用到索引，所以就会快很多。

count 优化

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高。（前提：没有 where）
  • InnoDB 执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数。如借助 redis，执行插入的时候 +1，删除的时候 -1。

count 的几种方法

  • count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累加值。

  • 用法：count(*)、count(主键)、count(字段)、count(1)。count(字段) 会判断 NULL。

  • count(主键): InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为 null）。

  • count(字段): 没有 not null 约束，InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加。有 not null 约束，InnoDB 引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。

  • count(*): InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

  • count(1): InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 1 进去，直接按行进行累加。

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

update 优化

事务不提交，锁不会释放。

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