【MySQL 数据库】7、SQL 优化

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【MySQL 数据库】7、SQL 优化


一、插入数据优化

(1) insert 语句

① 批量插入数据

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;

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

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3

主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89【☆】

(2) load 大批量插入数据【☆❀

① 如果需要一次性插入大批量数据(百万级别),使用 insert 语句插入性能很低

② 可使用 MySQL 数据库提供的 load 指令进行插入


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

-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数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 IOT)

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

(2) 页分裂

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


(3) 页合并

(4) 主键设计原则

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

三、orber by 优化

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

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

四、group by 优化

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

五、limit 优化(分页)

  • 在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。
  • 在进行分页查询时,如果执行limit 2000000,10 ,此时需要MySQL排序前2000010 条记录,仅仅返回 2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大

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

六、count 优化

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

用法:

  • count(*)
  • count(主键)
  • count(字段)
  • count(数字)

七、update 优化

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
关系型数据库 MySQL 分布式数据库
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
《MySQL 简易速速上手小册》第6章:MySQL 复制和分布式数据库(2024 最新版)
24 2
|
JavaScript 关系型数据库 MySQL
❤Nodejs 第六章(操作本地数据库前置知识优化)
【4月更文挑战第6天】本文介绍了Node.js操作本地数据库的前置配置和优化,包括处理接口跨域的CORS中间件,以及解析请求数据的body-parser、cookie-parser和multer。还讲解了与MySQL数据库交互的两种方式:`createPool`(适用于高并发,通过连接池管理连接)和`createConnection`(适用于低负载)。
8 0
|
4天前
|
SQL 关系型数据库 MySQL
MySQL环境搭建——“MySQL数据库”
MySQL环境搭建——“MySQL数据库”
|
4天前
|
SQL NoSQL 关系型数据库
初识MySQL数据库——“MySQL数据库”
初识MySQL数据库——“MySQL数据库”
|
5天前
|
SQL 缓存 Java
Java数据库连接池:优化数据库访问性能
【4月更文挑战第16天】本文探讨了Java数据库连接池的重要性和优势,它能减少延迟、提高效率并增强系统的可伸缩性和稳定性。通过选择如Apache DBCP、C3P0或HikariCP等连接池技术,并进行正确配置和集成,开发者可以优化数据库访问性能。此外,批处理、缓存、索引优化和SQL调整也是提升性能的有效手段。掌握数据库连接池的使用是优化Java企业级应用的关键。
|
6天前
|
关系型数据库 MySQL 数据库
数据库基础(mysql)
数据库基础(mysql)
|
6天前
|
SQL 数据库
数据库SQL语言实战(二)
数据库SQL语言实战(二)
|
6天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
7天前
|
存储 关系型数据库 MySQL
【后端面经】【数据库与MySQL】为什么MySQL用B+树而不用B树?-02
【4月更文挑战第11天】数据库索引使用规则:`AND`用`OR`不用,正用反不用,范围中断。索引带来空间和内存代价,包括额外磁盘空间、内存占用和数据修改时的维护成本。面试中可能涉及B+树、聚簇索引、覆盖索引等知识点。MySQL采用B+树,因其利于范围查询和内存效率。数据库不使用索引可能因`!=`、`LIKE`、字段区分度低、特殊表达式或全表扫描更快。索引与NULL值处理在不同数据库中有差异,MySQL允许NULL在索引中的使用。
12 3
|
4月前
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
61 0