MySQL数据库之SQL优化

最新推荐文章于 2025-08-28 11:36:07 发布

大草原的小灰灰

最新推荐文章于 2025-08-28 11:36:07 发布

阅读量668

点赞数 9

CC 4.0 BY-SA版权

分类专栏：数据库文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/new9232/article/details/146242199

数据库专栏收录该内容

8 篇文章

订阅专栏

1 插入数据优化

1.1 insert优化

批量插入

  # 批量插入时建议一次插入数据 500 - 1000 条
  insert into 表名(字段1, 字段2, ...) values(值11, 值21, 值31, ...),(值12, 值22, 值32, ...),...

手动提交事务

MySQL默认自动提交事务，可改为手动提交

  start transaction;
  insert into 表名(字段1, 字段2, ...) values(值11, 值21, 值31, ...),(值12, 值22, 值32, ...),...
  commit;

主键顺序插入

1.2 大批量插入数据

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

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

示例

root目录下有一份sql1.txt文件，内容如下
执行命令创建一张表

  create table infile_st(id int, name varchar(20), age int, course varchar(20));

将本地文件导入

  load data local infile '/root/sql1.txt' into table infile_st fields terminated by ',' lines terminated by '\n';

2 主键优化

数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
页分裂
页可以为空，也可以填充一半，也可以填充满。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
主键顺序插入
主键乱序插入
主键乱序插入时，page1发生分裂，这种现象称为页分裂。
页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页看看是否可以将两个页合并以优化空间使用。
主键设计原则
满足业务需要的情况下，尽量降低主键的长度
- 一张表主键索引只能有一个，但二级索引可以有很多。二级索引数据部分是数据主键，如果主键过长，会占用较多磁盘空间。搜索时性能也会降低。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 主键乱序插入时会发生页分裂。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
业务操作时，避免对主键的修改。

3 order by优化

对于MySQL中的排序，有两种方式：Using filesort和Using index
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
有这样一张表
看下表的索引，只有id有索引。
进行数据查询，并按照age排序，年龄相同时，按照phone排序。可以看到执行计划中，排序方式为Using filesort。
接下来为age和phone字段创建联合索引。再查询，可以看到，排序方式变为了Using index。
如果进行倒序排列，出现了反向扫描索引。
如果先按phone排序，再按age排序，违背了最左前缀法则，需要使用Using filesort进行额外排序。
如果age升序排列，phone降序排列，也要使用Using filesort进行额外排序。
如果想用age升序排列，phone降序排列。但是排序方式只用Using index，可以再创建一个联合索引，指定age为升序，phone为降序。查看索引结构，可以看到Collation这一列，phone的idx_age_phone_ad索引为D(desc)，表示排序方式为降序，其他都为A(asc)，表示降序。
再去查询，可以看到排序方式为Using index。
如果多查询一个字段name，可以看到排序方式只有Using filesort。因为没有使用到覆盖索引，需要回表查询。
优化规则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- 如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认为256k）。

4 group by优化

有这样一张表，只有一个主键索引。
根据课程分组并统计对应人数，执行计划中没有用到索引，使用了Using temporary（临时表），性能较低。
创建联合索引，再分组查询。执行计划中用到了索引，没有用到临时表，性能提高。
如果直接用age分组，会用到临时表，因为违背了最左前缀法则。同时使用course和age分组，没有违背最左前缀法则，就不会用到临时表，性能较高。
如果只用age分组，但是用course过滤，也不会违背最左前缀法则，不会用到临时表。

5 limit优化

分页查询时，通过创建覆盖索引能够较好的提高性能，可以通过覆盖索引加子查询形式进行优化。
有一张person表，有10万条数据。执行分页查询，分别从第10条数据的位置和第90000条数据的位置查询。
可以看到，从第90000条数据的位置开始查询，耗时要远远大于从第10条数据的位置查询。
进行优化后，效率提升了一倍。

6 count优化

MyISAM引擎把一个表的总行数存在了磁盘上，执行count(*)的时候会直接返回这个数，效率很高。
InnoDB引擎执行count(*)的时候，需要把数据一行一行读出来，然后累计计数。
count()是一个聚合函数，对于返回的结果集，一行行判断，如果count函数的返回不是NULL，累计值就加1，最后返回累计值。
如果某个字段值为NULL，count()不计数。
用法count(*)、count(主键)、count(字段)、count(1)
- count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（不会判断返回是否为NULL，因为主键不能为NULL）。
- count(字段)：没有not null约束，要额外判断是否为NULL，不是NULL再累加。有not null约束，则直接进行累加。
- count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接进行累加。
- count(*)：InnoDB引擎并不会把全部字段取出来，二是专门做了优化，不取值，服务层直接按行进行累加。
效率：count(字段)<count(主键)<count(1) $≈\approx$ count(*)，所以尽量使用count(*)。
看下实际的执行效率

7 update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
有一张课程表
左边和右边开启事务，左边更新id=1的数据，右边更新id=3的数据，都是没有问题的。
重新开启事务，左边用teacher字段去更新，右边再更新数据，就阻塞了。由于teacher没有索引，会导致整张表被加锁，因此右边不能操作。
左边提交事务后，右边才能更新成功。

8 总结

插入数据优化
- insert：使用insert插入数据时尽量选择批量插入、手动控制事务、主键顺序插入。
- 大批量插入：大批量插入数据时，通过加载本地文件的方式插入数据，load data local infile。
主键优化
- 主键长度尽量短，插入时顺序插入。
order by优化
- MySQL排序，有两种方式
- using index：排序时直接通过索引返回数据，性能高。
- using filesort：排序时需要将返回的结果在排序缓冲区排序。
- 尽量使用覆盖索引，涉及到的排序字段尽量创建索引。
group by优化
- 多字段分组时要遵循最左前缀法则。
limit优化
- 使用覆盖索引+子查询的方式提高效率。
count优化
- 效率：count(字段)<count(主键)<count(1) $≈\approx$ count(*)，尽量使用count(*)。
update优化
- 尽量根据有索引的字段更新，防止行锁升级为表锁，降低性能。