SQL优化

插入数据时的SQL优化（insert优化，和大批量数据插入）

批量插入
在插入多条数据时，不要一句一句的SQL语句插入，而是一次性插入多条数据
```
INSERT INTO 表名  (字段名1，字段名2，...) VALUES (值一，值二，...),(值一，值二，...),(值一，值二，...);``
INSERT INTO 表名 VALUES (值一，值二，...),(值一，值二，...),(值一，值二，...);``
```
但是批量插入一次最多不要超过1000条，大概就是500到1000条，如果一次性要插入几万条数据，那么可以将其分为多条insert语句插入

手动提交事务
可以优化插入速度，在插入前手动开启事务，插入完成后手动结束事务

start transaction;
insert into 表名 values(具体数据1);
insert into 表名 values(具体数据2);
insert into 表名 values(具体数据3);
...
commit;

主键顺序插入
在插入主键时按照主键的顺序插入
大批量插入数据
如果一次性插入大批量数据（万级的），使用insert语句插入性能较低，此时可以使用MySQL提供的load指令插入，通过load指令可以将本地磁盘中的数据全部加载到数据库当中

使用load指令的步骤：
1.在客户端连接服务端的时候，加上参数 – local-infile
```
mysql--local-infile -u root -p
```
2.设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
```
set global local infile=1;
```
3.执行load指令将准备好的数据，加载到表结构中
```
-- 这里的'/root/sql1.log'是本地文件
-- fields teminated by ','：表示每一个字段之间使用 ， 分割
-- lines teminated by '\n';表示每一行数据之间使用 \n 分割
load data local infile '本地文件' into table '表名' fields teminated by ',' lines teminated by '\n';
```
可以看到，默认情况下local_infile这个全局变量是关闭的

注意这里插入数据时，由于是虚拟机，所以要现在finalshall中上传数据，把数据上传到虚拟机中

这里插入100万条数据只需要耗时16秒，很强

使用load插入时也需要主键顺序插入，顺序插入数据高于乱序插入

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

主键优化

InnoDB中数据的组织方式：

在InnoDB中，表的数据都是根据主键的顺序组织存放的，这种存储方式被称为索引组织表。（即每行数据在页中都是顺序存放）

page（页）是innoDB磁盘管理的最小单元，一个extent（区）中可以包含64个页
页分裂
页可以为空，也可以填充一半，也可以全部填满，但是一个页中最少包含2行数据，如果某行的数据较大，超出了页的阈值之后，就会出现行溢出的现象，

如果顺序插入，就不会出现页分裂，乱序插入就会出现页分裂的现象，导致插入时要多操作页，自然插入的时间就会变长
页删除
在InnoDB中当删除一行数据时，实际上数据并没有被物理删除，而是数据被标记为删除并且他的空间变的允许其他数据使用

上图的13，14，15，16就是打上删除的标记了

当页中删除的数据达到一个阈值（MERGE_THRESHOLD）时（默认为页的50%），innoDB会开始寻找最靠近的页（前或后面的页）看看是否可以将两个页进行合并以优化空间使用

达到阈值
然后合并

再插入数据时就会往下一个页中插入数据
这里阈值（MERGE_THRESHOLD）可以自己设置，默认为50%
主键的设计原则
满足业务需求的情况下，尽量降低主键的长度

插入数据时，尽量使用顺序插入，尽量使用auto_increment自增主键

尽量不要使用UUID做主键或者其他自然主键（如身份证号）

业务操作时，尽量避免对主键的修改

order by优化（排序操作的优化）

MySQL中的两种排序方式
在这里插入图片描述
using index的效率较高

-- 此时age和phone都没有索引，使用order by排序时都是Using filesort，效率较低
mysql> explain select id,age ,phone from tb_user_s1 order by age;
+----------------+| Extra          |
+----------------+
| Using filesort |
+----------------+
1 row in set, 1 warning (0.00 sec)mysql> explain select id,age ,phone from tb_user_s1 order by age,phone;
+----------------+| Extra          |
+----------------+
| Using filesort |
-+----------------+
1 row in set, 1 warning (0.00 sec)-- 为age和phone建立联合索引后，就会变为Using index-- 若在查询时，order by之后的联合索引的排序不同，例如，一个顺序，一个倒序，
-- 也会出现Using filesort的情况
-- 这里age升序排列，phone倒序排列
select id,age ,phone from tb_user_s1 order by age asc,phone desc;-- 这种情况的解决方式就是在创建联合索引时就把顺序定好
-- 下面就是在创建联合索引时就确定age和phone的排列方式
create index idx_user_age_phone_ad on tb_user_s1(age asc ,phone desc)

在这里插入图片描述

注意：上述的所有排序优化都有一个条件，就是覆盖索引，如果不是覆盖索引就不行

即order by优化主要就以下几点：

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

group by优化（分组优化）

在分组操作时，建立适当的索引来提升效率
在这里插入图片描述
没有索引时，直接分组效率是较低的，使用索引对分组进行优化的话，就尽量使用联合索引，注意联合索引的最左前缀法则

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

limit优化（分页查询优化）

limit一个常见的问题就是，在大数据量的情况下，越往后查询数据，limit的效率月底，例如limit 2000000，10，此时需要mysql排序前2000010记录，但是仅仅返回2000000和2000010之间的记录，其他的记录丢弃，查询排序的代价非常大

官方给出的优化方式是，通过覆盖索引和子查询的方式优化

-- 直接使用limit查询，效率很低，大概要19秒多
select *from tb_user_s2 limit 9000000,10;-- 使用覆盖索引和子查询的方式优化
-- 先在子查询中找到对应的主键，然后再使用主键进行查询数据
-- 但是这种方式好像在mysql8.0.26语法不支持
select * from tb_user_s2 where id in(select id from tb_user_s2 order by in limit 9000000,10);-- 那么可以使用另外的语法实现这个效果
-- 把select id from tb_user_s2 order by in limit 9000000,1
-- 返回的结果看成一张表，然后使用多表查询
select s.* from tb_user_s2 s, (select id from tb_user_s2 order by in limit 9000000,10) a where a.id=s.id;
-- 这样写大概查询是10秒，提高9秒的效率