八、MySQL SQL优化

最新推荐文章于 2024-08-29 18:58:08 发布

_半桶水

最新推荐文章于 2024-08-29 18:58:08 发布

阅读量97

点赞数

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

本文链接：https://blog.csdn.net/weixin_45794310/article/details/126844650

版权

学习MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SQL优化

插入数据

批量插入数据

不要一条一条的数据插入数据，但不建议超过1000条，500-1000条最合适。如果更多的数据，则分开批量插入，而尽量减少mysql的连接关闭，从而提高效率

INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);

大批量插入数据

如果一次性需要插入大批量数据，使用INSERT语句插入行能较低，此时可以使用MySQL数据库提供的load指令进行插入，一行一行的分开，列与列用,或;分隔开

# 在客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

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

#执行load指令将准备好的数据，加载到表结构中
load data local infile '文件路径' into table tableName fields terminated by '分隔号' lines terminated by '\n';

手动提交事务

MySQL是自动帮我们提交事务的，我们可以手动的开启提交事务，从而避免多次的开启和提交事务

START TRANSACTION;
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
INSERT INTO tableName(xx) VALUES(xx),(xx),(xx);
COMMIT;

主键顺序插入

因为有关数据结构的问题

# 主键乱序插入：2 4 1 3 8 6
# 主键顺序插入：1 2 3 4 6 8 -- 推荐
# 主键顺序插入性能高于乱序插入

主键优化

数据组织方式

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

聚集索引的存储方式(叶子节点下面存的是行的数据)
在这里插入图片描述

逻辑存储结构
在这里插入图片描述

所以，如果是顺序的主键插入的话，就可以直接插入到数据的最后面，如果是乱序的插入，后面的page就需要页分裂，重新整理顺序分配页结构

页分裂

页是最小的存储单元

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

页合并

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

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

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

主键设计原则

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

ORDER BY 优化

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

根据 WHERE 条件查询差不多，ORDER BY 也需要创建安装排序条件给条件字段创建索引，也满足最左前缀法，创建索引时，可以指定字段的顺逆序

CREATE INDEX idx_user_age_phon_ad ON tableName(cloName1 asc, cloName2 desc);

规则：

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

GROUP BY 优化

一般的优化都是在创建索引来完成的

SELECT xx, count(*) FROM tableName GROUP BY xx; # 一般分组查询是按分组的条件返回字段的

规则：

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

LIMIT 优化

在大数据量情况下，越往后越耗时

SELECT * FROM tableName limit 0, 10;	# 0： 起始行，10： 从起始行开始查询多少条

一个常见有非常头疼的问题就是 limit 200000, 10 此时需要MySql排序前 20000010 记录，仅仅返回200000-200010的记录，其它记录丢弃，查询排序的代价非常大

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

# where in 之后不能使用 limit
# 所以我们可以使用自查询获得全部数据，先按主键排序查询出来，再查询所有字段
SELECT * FROM tableName t, (SELECT id FROM tableName ORDER BY id LIMIT 200000, 10) a WHERE t.id = a.id;

COUNT 优化

MyISAM 引擎吧一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率非常高

InnoDB 引擎执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

InnoDB 优化思路：自己计数（自己维护）

count的几种用法

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

用法：count(*)、count(主键)、count(字段)、count(1)

count(*): 表中的总记录数

InnoDB引擎不会吧全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
count(主键)：表中的总记录数

InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为NULL）
count(字段): 表中字段的值不为NULL的总记录数
- 没有NOT NULL约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为NULL，不为NULL，计数累加
- 有NOT NULL约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加（不为NULL，不用再判断）
count(1): xx?

InnoDB 引擎整张表遍历，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加