MySQL--SQL优化详解-CSDN博客

本文链接：https://blog.csdn.net/m0_73856804/article/details/147871960

插入数据优化

insert优化

 insert into test values(1,'tom');
 insert into test values(2,'cat');
 insert into test values(3,'jerry');
 ...

优化策略

批量插入(批量插入数据的数量最好在500到1000条之间)

 insert into test values(1,'tom'),(2,'cat'),(3,'jerry');

过多数据插入可以使用多条批量插入语句

手动提交事务

 begin；
 insert into test values(1,'tom'),(2,'cat'),(3,'jerry');
 insert into test values(4,'tom'),(5,'cat'),(6,'jerry');
 insert into 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 9 15 21 88 89

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。通过load指令可以一次性把本地文件的数据全部加载到数据库

操作如下：

前置操作：生成user表

 create table `user`(
     `id` int(11) not null auto_increment,
     `username` varchar(50) not null ,
     `password` varchar(50) not null,
     `name` varchar(20) not null ,
     `birthday` date default null,
     `sex` char(1) default null,
     primary key (`id`),
     unique key `unique_user_username` (`username`)
 )engine = innodb default charset = utf8

根据python脚本生成100万的数据

import csv
 import random
 from datetime import datetime, timedelta
 
 
 def generate_csv_file(num_users, output_file='users_data.csv'):
     first_names = ["张", "李", "王", "赵", "刘", "陈", "杨", "黄", "周", "吴"]
     last_names = ["伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军"]
     sexes = ["M", "F"]
 
     with open(output_file, 'w', encoding='utf-8', newline='') as csvfile:
         writer = csv.writer(csvfile)
 
         for i in range(1, num_users + 1):
             username = f"user{i}"
             password = f"pwd{i % 1000}"
             name = random.choice(first_names) + random.choice(last_names)
 
             # 生成随机生日
             start_date = datetime.now() - timedelta(days=60 * 365)
             end_date = datetime.now() - timedelta(days=18 * 365)
             random_days = random.randint(0, (end_date - start_date).days)
             birthday = (start_date + timedelta(days=random_days)).strftime('%Y-%m-%d')
 
             sex = random.choice(sexes)
 
             writer.writerow([username, password, name, birthday, sex])
 
             if i % 100000 == 0:
                 print(f"已生成 {i} 条记录")
 
     print(f"\nCSV文件已生成: {output_file}")
 
 
 generate_csv_file(1000000)

在开启本地加载文件导入数据的开关

 -- 客户端连接服务端时，加上参数 --local infile
 -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
 set global local_infile = 1;
 show variables like 'local_infile';
 -- 执行load指令将准备好的数据，加载进表结构中
 LOAD DATA LOCAL INFILE 'D:/pythonProject/users_data.csv'
 INTO TABLE `user`
 FIELDS TERMINATED BY ','
 OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 (username, password, name, birthday, sex);
 -- 没有执行成功，只能在命令行中执行

如果使用insert语句插入的话，可能需要十几分钟，将近10倍的差距。

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

主键优化

数据组织方式

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

在索引分类中，有聚集索引和二级索引，聚集索引默认是表中的主键索引，而聚集索引的叶子节点存放的是该主键下的行数据，而Innodb存储引擎用的索引结构是B+树，而B+树索引叶子节点是顺序排列的双向有序链表。所以，表数据都是根据主键顺序组织存放的

页分裂

page中存放的就是具体的行数据，页可以为空，也可以填充一半，也可以全部填充，每个页包含了2-N行数据（如果一行数据过大，行溢出），根据主键排列。在InnoDB规定中，一页至少包含两行数据。

主键顺序插入

主键乱序插入

乱序插入时，再插入行数据时，需要插入第一个页的后面，但第一个页的后面空间不够，因此，会开启一个新的数据页，找到第一个数据页50%的位置，将50%以后的行数据移到开启的新数据页里面，再将需要插入的行数据插入到新的数据页中。

这时，第一个数据页后面的数据页不应该是第二个数据页了，应该是第三个数据页，我们需要对链表指针进行一个重新的设置，将第一个数据页的后置指针设置为第三个数据页，将第三个数据页的后置指针设置为第二个数据页。

这种现象就被称为页分裂，主键乱序插入的情况下，可能会出现页分裂。

因此，主键顺序插入的性能高于主键乱序插入

页合并

执行删除操作，在InnoDB当中，如果要删除某一行记录时，实际上记录并没有被物理删除，只是该记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

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

在插入数据时就可以往第三个数据页插入。

注意事项：merge_threshold：合并页的阈值，可以自己设置，在创建表或创建索引时指定。

主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
  
  原因：在索引结构中，聚集索引只有一个，但二级索引有多个，在二级索引的叶子节点中就是数据表的主键，如果主键长度较长，二级索引较多，就会占用大量的磁盘空间，而且在搜索的时候会浪费大量的磁盘IO，因此要尽量降低主键的长度
- 插入数据是，尽量选择顺序插入，选择使用auto_increment自增主键。
原因：主键顺序插入的性能高于乱序插入的性能，原因如上：（页分裂）。
- 尽量不要使用UUID做主键或者其他自然主键，如身份证号。
原因：uuid生成的数据是无序的，这就会导致主键乱序插入，，身份证号长度较长，检索浪费磁盘IO。
- 业务操作时，避免对主键的修改。

order by优化（排序优化）

在MySQL排序有两种方式，

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

代码展示：

查询解析（按照age升序排序）

explain select id,age,phone from user order by age;

Extra中显示Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”（排序时没有使用索引，需要优化）

原因：age没有索引。

查询解析（先按照age升序排序，如果age相同，则按照电话号码升序排序）

explain select id,age,phone from user order by age,phone;

Extra中显示Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”（排序时没有使用索引，需要优化）

原因：age和phone没有索引。

create index `idx_user_np` on user(`name`,`phone`);

创建联合索引，再次进行测试

查询解析（按照age升序排序，age有联合索引，且左边第一个字段为age）
```
explain select id,age,phone from user order by age;
```

Extra中显示Using index：表示索引被用来执行行索引键值的查找，避免访问了表的数据行，效率不错。

原因：创建了联合索引，索引中包含age

查询解析（先按照age升序排序，如果age相同，则按照电话号码升序排序）

explain select id,age,phone from user order by age,phone;

Extra中显示Using index：表示索引被用来执行行索引键值的查找，避免访问了表的数据行，效率不错。

原因：创建了联合索引，索引中包含age，phone

查询解析（先按照age倒序排序，如果age相同，则按照电话号码倒序排序）

explain select id,age,phone from user order by age desc,phone desc;

Extra显示 Backward index scan （反向扫描索引）；Using index

反响扫描索引的原因：在InnoDB中的索引结构为B+树，而B+树的叶子节点是双向有序链表，里面的行数据也是有序的，而select语句要求倒序，所以需要反向扫描索引。

查询解析（先按照phone升序排序，如果phone相同，则按照age升序排序）

explain select id,age,phone from user order by phone,age;

Extra显示Using index，Using filesort。

原因：在创建联合索引时，age为左边第一个，而这次排序是先排phone，违背了最左前缀原则，因此在查询phone排序时，没有使用索引，在查询age时使用了索引。

查询解析（先按照age升序排序，如果age相同，则按照电话号码倒序排序）

explain select id,age,phone from user order by age ,phone desc;

Extra显示Using index，Using filesort。

原因：我们在创建索引的时候，如果没有指定顺序，默认是按照升序往后走的，先按照age升序排列，在按照phone倒序排序，但在select语句中，先是默认查询age升序排序，这时候使用了索引，接下来要查询phone倒序排序，此时就需要额外的排序。没有使用索引。

show index from user;

如何优化这种情况？

针对该字段，在创建一个索引，排列顺序为查询语句的排序。

create index `idx_user_ap_ad` on user(`age` asc ,`phone` desc);

查询解析（先按照age升序排序，如果age相同，则按照电话号码倒序排序）

explain select id,age,phone from user order by age ,phone desc;

Extra显示Using index，

原因：创建了新的联合索引，里面的phone默认排序为倒序。

查询解析（先按照age升序排序，如果age相同，则按照电话号码升序排序）

 explain select id,age,phone from user order by age,phone;

Extra显示Using index，

原因：原来的联合索引，里面的age，phone默认排序为正序。

联合索引内部结构

注意事项：这些所有的排序规则都有一个前提：

使用覆盖索引：使用索引查询数据时，所以的数据都是可以通过索引列查询到的，不需要再回表查询。

总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀原则，且与where查询条件不同，where是并立的，最左前缀主要在条件中就可以用，而在order by的条件是讲先后顺序的。
尽量使用覆盖索引
多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区的大小（sort_buffer_size）（默认256k）

 show variables like 'sort_buffer_size';

如果此时排序缓冲区被占满了，这时就会涉及到磁盘文件，他会再磁盘文件当中进行排序，性能较低。

where子句中如果出现索引范围查询会导致order by索引失效。
关联查询优化：内连接时，MySQL会自动把小结果集的选为驱动表，所以大表的字段最好加上索引，左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理，我们最好保证被驱动表上的字段建立了索引。

group by优化（分组优化）

查询分析（根据profession进行分组查询）

 explain select profession,count(*) from user group by profession;

Extra显示Using temporary：MySQL在对查询结果排序时使用临时表，性能较低

针对该字段创建索引

 create index `idx_user_pas` on user(profession,age,status);

再次测试

查询分析（根据profession进行分组查询）

 explain select user.profession,count(*) from user group by profession;

Extra显示Using index；

原因：以profession为第一个字段创建的索引。

查询分析（根据age进行分组查询）

 explain select age,count(*) from user group by age;

Extra显示Using index，Using temporary。

原因：索引结构中pro全局有序而age局部有序：age的值可以直接通过index获得不需要回表查询。所以Using index ；而获得的age不是索引的第一列，无法直接利用索引来执行完全的分组操作，需要在临时表存储中间结果，等待聚合计算（Count（*））以分组，所以Using temporary

查询分析（）

 explain select profession,age,count(*) from user group by profession, age;

Extra显示Using index

原因：遵循最左前缀原则。

查询分析（）

 explain select profession,age,count(*) from user where user.profession = '软件工程' group by  age;

Extra显示Using index。

原因：索引(profession, age)中，若 WHERE 固定了 profession，则age在索引中已经是局部有序的，GROUP BY age 可直接利用索引。不需要在建立临时表。

总结：

在分组操作中，可以通过索引来提高效率
分组操作时，索引的使用也需要满足最左前缀原则

Limit优化（分页优化）

在大数据量的表分页查询时，有一个常见又很烦的问题：就是limit 300000,10，此时需要MySQL排序前300010条记录，但仅仅返回300000 - 300010的记录，其他记录丢弃，查询排序的代价很大

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

代码展示：

 select * from user limit 0,10; -- 1 ms
 select * from user limit 100000,10; --  139 ms (execution: 47 ms, fetching: 92 ms)
 select * from user limit 500000,10; --  308 ms (execution: 245 ms, fetching: 63 ms)
 select * from user limit 900000,10; -- 553 ms (execution: 344 ms, fetching: 209 ms)
  select id from user order by id limit 900000,10; -- 266 ms (execution: 149 ms, fetching: 117 ms)
  select * from user where id in ( select id from user order by id limit 900000,10); -- error: [42000][1235] This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
  select u.* from user u,(select id from user order by id limit 900000,10) a where u.id = a.id;-- 243 ms (execution: 164 ms, fetching: 79 ms)

注意事项：子查询是覆盖索引，主查询是通过聚集索引，避免了回表查询

count优化

 select count(*) from user;-- 733 ms (execution: 390 ms, fetching: 343 ms)

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

优化思路：自己计数

比如：可以借助一些键值对数据库（Redis），当我们执行插入数据时，直接将某个计数加一，当我们往某张表中删除一条数据，将计数减一，自己维护计数。比较繁琐。

count的几种方法

count（）是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值。
用法：
- count（*）：
  
  InnoDB引擎并不会将全部字段取出，而是专门做了优化，不取值，服务层直接按行进行累加
- count（主键）：InnoDB引擎会遍历整张表，把每一行的主键值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键一定不为空），所以也是查询表的总行数。
- count（字段）：
  
  没有not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  
  有not null约束: InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。
- count（n）：InnoDB引擎会遍历整张表，但不会取数值。都会放一个数字“n“进去,直接按行进行累加
按照效率排序的话：count（字段）< count（主键） < count（n）≈ count(*) ，所以尽量使用count（ * ）。

代码展示：

 select count(*) from user; -- 314 ms (execution: 16 ms, fetching: 298 ms)
 select count(1) from user;-- 182 ms (execution: 6 ms, fetching: 176 ms)
 select count(id) from user; -- 96 ms (execution: 4 ms, fetching: 92 ms)
 select count(age) from user; -- 272 ms (execution: 6 ms, fetching: 266 ms)

update优化

避免行锁升级成表锁

代码展示：

事务1：

set autocommit = false;
 begin ;
 update course set name = 'javaEE' where id = 1; -- 执行时为主键，有主键索引，此时加的是行锁，只锁住了ID = 1的行
 commit ;
 -- 进行测试
 select * from course;
 begin ;
 update course set name = 'SpringBoot' where name = 'PHP'; -- 执行时name字段没有索引，此时加的就不再是行锁，而是表锁
 commit ;
 -- 再次进行测试，在name字段上添加索引
 create index `idx_course_name` on course(name); -- 建立索引
  begin ;
 update course set name = 'Spring' where name = 'SpringBoot'; -- 执行时为name，有索引，此时加的是行锁，只锁住了name = springBoot的行
 commit ;

事务二：

 set autocommit = false;
 begin ;
 select name from course where id = 1;
 update course set name = 'Kafka' where id = 4; -- 执行成功，上一个事务为行锁。
 commit ;
 -- 再次测试
 begin ;
 select name from course where id = 1;
 update course set name = 'Kafka2' where id = 4; -- 执行失败，锁和锁之间互斥，事务进入堵塞状态，只能等待表锁的释放，所以在执行update语句时，会一直等待上一个事务，因为上一个事物的锁是表锁。 26 s 34 ms
 commit ;
 -- 再次进行测试
 begin ;
 update course set name = 'Mybatis' where id = 4;-- 执行成功
 commit ;

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。一旦为表锁，并发性能就会降低（锁与锁之间互斥，造成堵塞）

注意事项：我们在更新数据时，一定要根据索引字段进行更新