MySQL(进阶篇2.0)

原创

已于 2023-06-06 11:13:16 修改 · 682 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #数学建模

于 2023-06-06 11:01:47 首次发布

SQL优化

插入数据

insert

如果需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化

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、优化方案三

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

原因：

如果主键是顺序的，InnoDB会把每一次插入的记录存储在上一条记录的上面。当达到页的最大填充因子时，下一条记录会写入新的页中，一旦数据按照这种顺序的方式插入主键就会近似于被顺序的记录填满

随机插入的情况，InnoDB无法简单的总是把新的记录插入到索引的最后，也就是说插入的位置很可能是现有数据的中间，这会导致性能恶化

如果

大批量插入数据

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

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

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

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

示例：

1、创建表结构

CREATE TABLE `tb_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 ;

2、设置参数

-- 客户端连接服务器
mysql --local-infile -u root -p

-- 设置全局参数local_infile为1.开启从本地加载文件导入数据的开关
set global local_infile = 1;

3、load加载数据

load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';

在load时，主键顺序插入性能高于乱序插入

主键优化

主键顺序插入性能高于乱序插入原因：

数据组织方式

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

行数据，都是存储在聚集索引的叶子节点上的。

InnoDB的逻辑结构图：

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

页分裂

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

主键顺序插入效果

1、从磁盘中申请页，主键顺序插入

2、第一页没满，继续向第一页中插入

3、当第一个写满后，再写入第二页，也与也之间通过指针连接

4、当第二页写满了，再往第三页中写入

主键乱序插入效果

1、加入1#，2#页都已经写满，存放了如图所示的数据

2、此时再插入id为50的记录会怎么样？

此时索引结构的叶子节点是有顺序的。按照顺序，应该存储在47之后

但是47所在的页，已经写满了，存储不了50对应的数据，此时会开辟一个新的页3#

但是并不会直接将50存入3#页，而是会将1#页后的一半数据，移动到3#页，然后在3#页，插入50

移动数据，并插入id为50的数据之后，此时，这三个页之间的数据顺序是有问题的。1#的下一页应该是3#，3#的下一页是2#。所以此时需要重新设置链表指针

上述的这种现象，称之为“页分裂”，是比较耗费性能的操作

页合并

目前表中已有数据的索引结构（叶子节点）如下：

当对已有数据进行删除时，具体的效果如下：
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间不允许被其他记录声明使用

继续删除2#的数据记录

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

删除数据，并将页合并之后，再次插入新的数据21，则直接插入到3#页

这个里面发生的合并页的现象，就称为“页合并”

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或和创建索引时指定

索引设计原则

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

Order by优化

MySQL的排序，有两种方式：
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

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

对于以上两种排序方式，Using index的性能高，而Using filesort的性能低，在优化排序操作时，尽量优化为Using index

测试：

数据准备

把之前测试时，为tb_user表所建立的部分索引直接删除掉

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

1、执行排序SQL

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

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

由于age，phone都没有索引，所以此时再排序时，出现using filesort，排序性能较低

3、创建索引

create index idx_user_age_phone_aa on tb_user(age,phone);

4、创建索引后，根据age，phone进行升序排序

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

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

5、创建索引后，根据age，phone进行降序排序

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

注意：多个字段使用order by 时，按字段的顺序排优先级

也出现using index，但是此时Extra出现Backward index scan，这个代表反向扫描索引，因为在MySQL中创建的索引，默认索引的叶子节点是从小到大排序的，而此时查询排序时，是从大到小，所以，在扫描时就是反向扫描，就会出现Backward index scan。在MySQL8中支持降序索引，也可以创建降序索引

6、根据phone、age进行升序排序，phone在前，age在后

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

排序时，也需要满足最左前缀法则，否则也会出现filesort。因为在创建索引时，age是第一个字段，phone是第二个字段，所以排序时，也应该按照这个顺序来，否则就会出现using filesort

7、根据age，phone进行一个升序，一个降序排序

explain select id,age,phone from tb_user order by age asc, phone desc;

因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序。此时就会出现using filesort

8、创建一个联合索引（age升序排序，phone降序排序）

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

9、再次执行如下SQL：

explain select id,age,phone from tb_user order by age asc, phone desc;

升序/降序联合索引结构图示：

由上述的测试，得出order by优化原则：

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

group by优化

先将tb_user表的索引全部删除

drop index idx_user_pro_age_sta on tb_user;
drop index idx_email_5 on tb_user;
drop index idx_user_age_phone_aa on tb_user;
drop index idx_user_age_phone_ad on tb_user;