【MySQL 数据库】7、SQL 优化

目录

一、插入数据优化

(1) insert 语句

① 批量插入数据

insertinto tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

② 手动控制事务

starttransaction;insertinto tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');insertinto tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');insertinto tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');commit;

③ 主键顺序插入，性能要高于乱序插入

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89【☆】

(2) load 大批量插入数据【☆❀

】

① 如果需要一次性插入大批量数据（百万级别），使用 insert 语句插入性能很低
② 可使用 MySQL 数据库提供的

load

指令进行插入

可执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile-u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关setglobal local_infile =1;-- 执行load指令将准备好的数据，加载到表结构中loaddatalocalinfile'/root/sql1.log'intotable tb_user fieldsterminatedby','linesterminatedby'\n';

二、主键优化

主键顺序插入的性能是要高于乱序插入的, 为什么？

(1) 数据组织形式

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

在 InnoDB 存储引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

(2) 页分裂

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

(3) 页合并

(4) 主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

三、orber by 优化

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

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

四、group by 优化

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

五、limit 优化（分页）

• 在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
• 在进行分页查询时，如果执行limit 2000000,10 ，此时需要MySQL排序前2000010 条记录，仅仅返回 2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大

优化思路: 一般分页查询时，通过创建

覆盖索引

能够比较好地提高性能，可以通过覆盖索引加子查询（多表联查）形式进行优化

六、count 优化

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

用法：

• count（*）
• count（主键）
• count（字段）
• count（数字）

七、update 优化

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