MySQL SQL优化

派大星啦啦啦

已于 2023-03-15 12:48:32 修改

阅读量385

点赞数

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

于 2023-03-15 12:08:42 首次发布

本文链接：https://blog.csdn.net/qq_44544908/article/details/129493136

版权

Mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

开启慢查询日志（ SET GLOBAL long_query_time=阈值;超过阈值的sql就会记录到慢查询日志当中），或查看执行计划（explain+SQL）。慢查询优化如下：

（1）分析sql语句，是否加载了不需要的数据列

（2）分析sql执行计划，字段有没有索引，索引是否失效，是否用对索引

（3）使用复杂查询时，尽量使用关联查询来代替子查询，并且最好使用内连接

（4）orderby查找时使用索引进行排序，否则的话需要进行回表，然后在排序缓冲区中进行排序。

（5）groupby查询时，同样要建立联合索引，避免使用到临时表

（6）分页查询时，如果偏移量太大，比如要查询一百万条数据后的十条记录，可以使用主键+子查询的方式，避免进行全表扫描

（7）使用count函数时直接使用count的话count(*)的效率最高，也可以额外创建一张表去统计不同表中的数据行数，但维护麻烦count(*)或count(唯一索引)或count(数字):表中总记录数，count(字段)不会统计null

（8）在写update语句时，where条件要添加使用索引，否则会锁会从行锁升级为表锁

（9）表中数据是否太大，是不是要分库分表

一、插入数据

1.小数据插入(500~1000)

批量插入

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

手动提交事务

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;

主键顺序插入

2.大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的loadl指令进行插入。操作如下:

#客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u root p
#查询从本地加载文件导入数据的开关是否开启，0为关闭，1为开启
select @@local_infile;
#设置全局参数local_infile为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.数据组织方式

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

请添加图片描述

2.页分裂

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

请添加图片描述
在这里插入图片描述

3.页合并

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

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

请添加图片描述

4.主键设计原则

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

三、order by优化

Using filesort ：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。
```
#查询缓冲区大小语句
show variables like 'sort_buffer_size';
```