MySQL学习笔记-05(SQL优化)

南风在学习

于 2024-09-01 00:36:29 发布

阅读量495

点赞数 21

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_41016739/article/details/141761481

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL学习笔记-05(SQL优化)

SQL优化

SQL优化工具

SQL执行频率

作用：查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次；
语法

# 可以提供服务器状态信息

SHOW [SESSION | GLOBAL] STATUS [LIKE 'Com______'];

运行结果

在这里插入图片描述

慢查询日志

# Linux--在my.cnf设置，在mysql/localhost-slow.log中查看慢日志

# 开启MySOL慢日志查询开关
slow query log=1

# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志，默认10秒

# windows--在my.ini设置

slow_query_log=1
slow_query_log_file=C:/ProgramData/Microsoft/Windows/Start Menu/Programs/MySQL/MySQL Server 8.4/temp/mysql_slow.log

profile详情

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时；
查看并开启profiling

select @@profiling;

set profiling = 1;

在这里插入图片描述

示例

# 查看每一条SQL的耗时基本情况
show profiles;

# 查看指定query id的SOL语句各个阶段的耗时情况
show profile for query query_id;

# 查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;

在这里插入图片描述

explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

explain select * from user_large limit 800000,1000;

在这里插入图片描述

EXPLAIN 执行计划各字段含义:

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)；
select_type：表示SELECT的类型，常见的取值有：SIMPLE(简单表，即不使用表连接或者子査询)、PRIMARY(主查询，即外层的査询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等；
type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all；
possible_key：显示可能应用在这张表上的索引，一个或多个；
Key：实际使用的索引，如果为NULL，则没有使用索引；
Key len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows：MySOL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的;
filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

SQL优化内容

insert 优化

批量插入

INSERT INTO dept VALUES (1001,'总裁部'),(1002,'保洁部');

手动提交事务

START TRANSACTION;

INSERT INTO dept VALUES (1001,'总裁部'),(1002,'保洁部');
INSERT INTO dept VALUES (1003,'总裁部'),(1004,'保洁部');
INSERT INTO dept VALUES (1005,'总裁部'),(1006,'保洁部');

COMMIT;

主键顺序插入

主键乱序插入：12, 45, 32, 76, 1, 5, 87, 23, 6

主键顺序插入：1, 5, 6, 12, 23, 32, 45, 76, 87

大批量插入数据：如果一次性需要插入大批量的数据，INSERT语句性能较低，可使用MySQL数据库提供的load执行进行插入，操作步骤如下：

1、通过nodejs生成一个large_user.sql文件

const fs = require('fs');

let str = ''

for(let i=0;i<1000000;i++){
    str += `${i},测试名称${i},18,2022-02-02,1`+'\n' 
}

fs.writeFile('large_user.sql', str, 'utf8', (err) => {
    console.log('保存成功')
});

在这里插入图片描述

2、客户端连接服务器时,加上参数–local_infile

mysql  --local-infile -u root -p

在这里插入图片描述

3、新建一个user_large表

create table user_large(
    id int primary key auto_increment comment 'ID',
    name varchar(50) not null comment '姓名',
    age int check ( age > 0&& age <=120 ) comment '年龄',
    birthday date default null comment '生日',
    dep_id int comment '部门ID'
) comment '超大用户表';

3、查询全局参数local-infile

select @@local_infile;

在这里插入图片描述

4、设置全局参数local-infile，开启本地加载文件导入数据的开关

set global local_infile = 1;

在这里插入图片描述

5、执行load指令，将准备好的数据加载到user_large中

load data local infile 'E:/studySpace/MySQL/large_user.sql' into table user_large fields terminated by ',' lines terminated  by '\n';

在这里插入图片描述

5、查询load进去的数据

select count(*) from user_large;

在这里插入图片描述

主键优化

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

在这里插入图片描述

页分裂：页可以为空，也可以填一半，也可以填充100%，每个页包含了2~N行数据(如果一行数据太大，会行溢出)，根据主键排列。

1、主键顺序插入

在这里插入图片描述

2、主键乱序序插入

在这里插入图片描述

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flag)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

在这里插入图片描述

主键的涉及原则

满足需求的情况下，尽量降低主键的长度（二级索引叶子结点挂的就是数据主键）；
Insert数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键;
尽量不要使用UUID或其他自然主键，比如身份证号；
避免对主键进行修改。

ORDER BY优化

Using filesort 与 Using index

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

示例

# 没有创建索引时，根据age,phone进行排序
explain select id,age,phone from user_yh order by age,phone;

# 创建索引
create index inx_user_age_phone on user_yh(age, phone);

# 创建索引后，根据age,phone进行升序排序
explain select id,age,phone from user_yh order by age,phone;

# 创建索引后，根据age，phone进行降序排序
explain select id,age,phone from user_yh order by age desc,phone desc;

# 根据age,phone进行降序一个升序，一个降序
explain select id,age,phone from user_yh order by age asc,phone desc;

# 创建索引
create index inx_user_age_phone_ad on user_yh(age asc, phone desc)

# 根据aqe,phone进行降序一个升序，一个降序
explain select id,age,phone from user_yh order by age asc,phone desc ;

运行结果

在这里插入图片描述

优化

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

GROUP BY优化

示例

# 执行分组操作，根据age字段分组
explain select age,count(*) from user_yh group by age;

# 创建索引
create index inx_user_age_phone on user_yh(age, phone);

# 执行分组操作，根据age字段分组
explain select id,age,phone from user_yh order by age,phone;

运行结果

在这里插入图片描述

优化

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

imit优化

问题: limit 5000000,10，此时需要MySQL排序前5000010记录，仅仅返回5000000-5000010的记录，其他记录丢弃，查询排序的代价非常大。
思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
示例


select * from user_large limit 980000,10;

select * from user_large u,(select id from user_large order by id limit 980000,10) a where u.id=a.id;

explain select * from user_large limit 980000,10;

explain select * from user_large u,(select id from user_large order by id limit 980000,10) a where u.id=a.id;

运行结果

在这里插入图片描述

count优化

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

count(*)：InnoDB引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。
count(主键)：没有not nul约束:InnoD8引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为nul，计数累加。
有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(字段)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(1)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

效率：count(字段) < count(主键id) < count(1) < count(*)

update优化

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

未给name添加索引前

# 客户端1：
START TRANSACTION;

update user_yh set name='老六' where name = '王五';

# 客户端2：
START TRANSACTION;

# 报Lock wait timeout exceeded; try restarting transaction;
update user_yh set name='万久' where id =3;

给name添加索引后

# 客户端1：
START TRANSACTION;

update user_yh set name='老六2' where name = '老六';

# 客户端2：
START TRANSACTION;

update user_yh set name='万久' where id =3;

# 客户端1：
COMMIT;

# 客户端2：
COMMIT;

运行结果

未给name添加索引前

在这里插入图片描述

给name添加索引后

ait timeout exceeded; try restarting transaction;
update user_yh set name=‘万久’ where id =3;


给name添加索引后

```SQL
# 客户端1：
START TRANSACTION;

update user_yh set name='老六2' where name = '老六';

# 客户端2：
START TRANSACTION;

update user_yh set name='万久' where id =3;

# 客户端1：
COMMIT;

# 客户端2：
COMMIT;