mysql数据库之sql优化

快来打我* 2023-10-11 20:58 65阅读 0赞

一、插入数据时的优化。

1、批量插入。

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

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');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;

3、主键顺序插入。

主键乱序插入：8,1,9,21,88,2,4,15,89,5,7,3
    主键顺序插入：1,2,3,4,5,7,8,9,15,21,88,89

4、大批量数据插入。

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

![7c5dd2da512c4a25a7896336f7d1d2e1.png][]

操作过程如下

#客户端链接服务端时，加上参数--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 teminated by ',' lines teminated by '\n';

是否允许load插入数据，0不能，1可以。

![e96a49cae95c40d58eb2c74eddf0ebe0.png][]

耗时16秒

![c5b8bc01e7ee48cc9b40418cf97c54a6.png][]

查看所有：ll

![b2895809f96a44e0b9e0c493aeaef0c6.png][]

查看一共多少行：

![95389bcdb0ce44c7b463c4eefce22dcd.png][]

查看前十行：

![14dc5328e36641c6bff676b4711cc7be.png][]

二、主键优化。

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

![d977301d859040d6b027268829c2c14c.png][]

1、页分裂。

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

主键顺序插入

![b6a459ddd0fb4b42946ef45d9c69d1b1.png][]

主键乱序插入

![af7b69d341b4405f80d5204184694dc1.png][]

2、页合并。

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

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

![5607cb2736bf409cb726c0033bc3689a.png][]

![bf8a6aab7d274e658b6641ee2cc1be86.png][]

3、主键设计原则。

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO\_INCREMENT自增主键（页分裂）。

尽量不要使用UUID做主键或者是其他自然主键，如身份证号（页分裂）。

业务操作时，避免对主键的修改（修改主键需要修改对应的索引结构）。

三、order by优化。

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

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

![685a1db046974899839c804d3c83afa2.png][]

倒叙扫描索引![5fd324de2fb54f81a7d0f71519cce027.png][]

默认升序排列，如果倒叙，需要额外排序，性能降低。

![e0d900950ee34b0db1df2b657b2881f2.png][]

优化：单独创建升序和降序排列索引。

![73464177334b4c1b821dc2d01e3c791c.png][]

collection：升序排列还是降序排列

![ec06e1044d6943c69a717ef09acf3d3a.png][]

已经被优化

![3cd4ab1ede884ee5a16bcf370b2b839b.png][]

都是升序，性能最优

![dbdd950e83af411890759c0463334c84.png][]

都是升序排序

![363dcca1f2d441bf89b0f6e763eadaee.png][]

一个升序一个降序

![a9ffd9fcf7a743559c8f43da223013ce.png][]

order by 总结

1、根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

2、尽量使用覆盖索引。

3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ACC/DESC）。

4、如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort\_buffer\_size（默认256k）。

![60c5128a1ee046c69b3b6473c50718f5.png][]

四、group by优化。

优化前，性能较低。

![7a64d8a6af6442b7a6ee0ed0f934dd10.png][]

创建联合索引进行优化后，性能提升。

![1ea1bacb62f04d7e8020cb778221a169.png][]

最左前缀法则，性能提升。

![39004b6a795a40c1916ffb222461b8c6.png][]

满足最左前缀法则，查询专业为软件工程的用户，并对其根据年龄进行分组。

![d77f7966430844e7b70ba3aae8134185.png][]

总结：

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

五、limit优化。

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

优化前耗时19秒

![4303382a96ab426fa0a1c29e678b422c.png][]

![87b94b33b90d4fb38b27b85706025257.png][]

优化方法：覆盖索引＋子查询的方式进行优化

![b293406cf1ad4b03a8bd4e830468a0fb.png][]

多表联查优化后，耗时11.46

![aca86cfba15f40018bb8bd7ed2280d80.png][]

![13ea7ed7ba794fab8eb425b8707a1a2b.png][]

六、count优化。

explain select count（\*） from tb\_user;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（\*）的时候会直接返回这个数，效率很高。

InnoDB引擎就麻烦了，它执行count（\*）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数。

count的几种用法

1、count（）是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累积值就加1，否则不加，最后返回累计值。

2、count用法：count（\*）、count（主键）、count（字段）、count（1）。

count（主键）

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

count（字段，有值加1，没有值不加）

a.没有not null约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是都为null，不为null，计数累加。

b.有not null约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

![ae56310df1e541d7989b9a168b406fce.png][]

count（数字）

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

![992a07794ae14ea4bb2e28babcfc91d2.png][]

count（\*）

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

按照效率排序：count（字段）<count（主键）<count（1）=count（\*）,所以尽量使用count（\*）。

七、update优化。

行锁变成了表锁，因为没有索引，创建完索引后可以提交。

![4b72c747f2fe47d29bbec7a05d5982a4.png][]

![28b8def1922c471e9ce16d2303d4b630.png][]

需要根据索引字段进行更新，否则行锁就会变成表锁。

![030e0b9bb4fd4943a9ea058ffbf3c213.png][]

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

[7c5dd2da512c4a25a7896336f7d1d2e1.png]: https://img-blog.csdnimg.cn/7c5dd2da512c4a25a7896336f7d1d2e1.png
[e96a49cae95c40d58eb2c74eddf0ebe0.png]: https://img-blog.csdnimg.cn/e96a49cae95c40d58eb2c74eddf0ebe0.png
[c5b8bc01e7ee48cc9b40418cf97c54a6.png]: https://img-blog.csdnimg.cn/c5b8bc01e7ee48cc9b40418cf97c54a6.png
[b2895809f96a44e0b9e0c493aeaef0c6.png]: https://img-blog.csdnimg.cn/b2895809f96a44e0b9e0c493aeaef0c6.png
[95389bcdb0ce44c7b463c4eefce22dcd.png]: https://img-blog.csdnimg.cn/95389bcdb0ce44c7b463c4eefce22dcd.png
[14dc5328e36641c6bff676b4711cc7be.png]: https://img-blog.csdnimg.cn/14dc5328e36641c6bff676b4711cc7be.png
[d977301d859040d6b027268829c2c14c.png]: https://img-blog.csdnimg.cn/d977301d859040d6b027268829c2c14c.png
[b6a459ddd0fb4b42946ef45d9c69d1b1.png]: https://img-blog.csdnimg.cn/b6a459ddd0fb4b42946ef45d9c69d1b1.png
[af7b69d341b4405f80d5204184694dc1.png]: https://img-blog.csdnimg.cn/af7b69d341b4405f80d5204184694dc1.png
[5607cb2736bf409cb726c0033bc3689a.png]: https://img-blog.csdnimg.cn/5607cb2736bf409cb726c0033bc3689a.png
[bf8a6aab7d274e658b6641ee2cc1be86.png]: https://img-blog.csdnimg.cn/bf8a6aab7d274e658b6641ee2cc1be86.png
[685a1db046974899839c804d3c83afa2.png]: https://img-blog.csdnimg.cn/685a1db046974899839c804d3c83afa2.png
[5fd324de2fb54f81a7d0f71519cce027.png]: https://img-blog.csdnimg.cn/5fd324de2fb54f81a7d0f71519cce027.png
[e0d900950ee34b0db1df2b657b2881f2.png]: https://img-blog.csdnimg.cn/e0d900950ee34b0db1df2b657b2881f2.png
[73464177334b4c1b821dc2d01e3c791c.png]: https://img-blog.csdnimg.cn/73464177334b4c1b821dc2d01e3c791c.png
[ec06e1044d6943c69a717ef09acf3d3a.png]: https://img-blog.csdnimg.cn/ec06e1044d6943c69a717ef09acf3d3a.png
[3cd4ab1ede884ee5a16bcf370b2b839b.png]: https://img-blog.csdnimg.cn/3cd4ab1ede884ee5a16bcf370b2b839b.png
[dbdd950e83af411890759c0463334c84.png]: https://img-blog.csdnimg.cn/dbdd950e83af411890759c0463334c84.png
[363dcca1f2d441bf89b0f6e763eadaee.png]: https://img-blog.csdnimg.cn/363dcca1f2d441bf89b0f6e763eadaee.png
[a9ffd9fcf7a743559c8f43da223013ce.png]: https://img-blog.csdnimg.cn/a9ffd9fcf7a743559c8f43da223013ce.png
[60c5128a1ee046c69b3b6473c50718f5.png]: https://img-blog.csdnimg.cn/60c5128a1ee046c69b3b6473c50718f5.png
[7a64d8a6af6442b7a6ee0ed0f934dd10.png]: https://img-blog.csdnimg.cn/7a64d8a6af6442b7a6ee0ed0f934dd10.png
[1ea1bacb62f04d7e8020cb778221a169.png]: https://img-blog.csdnimg.cn/1ea1bacb62f04d7e8020cb778221a169.png
[39004b6a795a40c1916ffb222461b8c6.png]: https://img-blog.csdnimg.cn/39004b6a795a40c1916ffb222461b8c6.png
[d77f7966430844e7b70ba3aae8134185.png]: https://img-blog.csdnimg.cn/d77f7966430844e7b70ba3aae8134185.png
[4303382a96ab426fa0a1c29e678b422c.png]: https://img-blog.csdnimg.cn/4303382a96ab426fa0a1c29e678b422c.png
[87b94b33b90d4fb38b27b85706025257.png]: https://img-blog.csdnimg.cn/87b94b33b90d4fb38b27b85706025257.png
[b293406cf1ad4b03a8bd4e830468a0fb.png]: https://img-blog.csdnimg.cn/b293406cf1ad4b03a8bd4e830468a0fb.png
[aca86cfba15f40018bb8bd7ed2280d80.png]: https://img-blog.csdnimg.cn/aca86cfba15f40018bb8bd7ed2280d80.png
[13ea7ed7ba794fab8eb425b8707a1a2b.png]: https://img-blog.csdnimg.cn/13ea7ed7ba794fab8eb425b8707a1a2b.png
[ae56310df1e541d7989b9a168b406fce.png]: https://img-blog.csdnimg.cn/ae56310df1e541d7989b9a168b406fce.png
[992a07794ae14ea4bb2e28babcfc91d2.png]: https://img-blog.csdnimg.cn/992a07794ae14ea4bb2e28babcfc91d2.png
[4b72c747f2fe47d29bbec7a05d5982a4.png]: https://img-blog.csdnimg.cn/4b72c747f2fe47d29bbec7a05d5982a4.png
[28b8def1922c471e9ce16d2303d4b630.png]: https://img-blog.csdnimg.cn/28b8def1922c471e9ce16d2303d4b630.png
[030e0b9bb4fd4943a9ea058ffbf3c213.png]: https://img-blog.csdnimg.cn/030e0b9bb4fd4943a9ea058ffbf3c213.png