【MySQL】七种SQL优化方式 你知道几条

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 【MySQL】七种SQL优化方式 你知道几条

1.插入数据

1.1insert

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

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');

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

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

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

1.2大批量插入数据

如果一次性需要插入大批量数据 ( 比如 : 几百万的记录 ) ,使用 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' ;

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

示例演示 :

A. 创建表结构

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 ;

B. 设置参数

-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;

C. load 加载数据

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

我们看到,插入 100w 的记录, 17s 就完成了,性能很好。

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

2.主键优化

在上一小节,我们提到,主键顺序插入的性能是要高于乱序插入的。 这一小节,就来介绍一下具

体的原因,然后再分析一下主键又该如何设计。

1). 数据组织方式

InnoDB 存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表

(index organized table IOT)

行数据,都是存储在聚集索引的叶子节点上的。而我们之前也讲解过 InnoDB 的逻辑结构图:

InnoDB 引擎中,数据行是记录在逻辑结构 page 页中的,而每一个页的大小是固定的,默认

16K

那也就意味着, 一个页中所存储的行也是有限的,如果插入的数据行 row 在该页存储不小,将会存

储到下一个页中,页与页之间会通过指针连接。

2). 页分裂

页可以为空,也可以填充一半,也可以填充 100% 。每个页包含了 2-N 行数据 ( 如果一行数据过大,

会行溢出) ,根据主键排列。

A. 主键顺序插入效果

. 从磁盘中申请页, 主键顺序插入

. 第一个页没有满,继续往第一页插入

. 当第一个也写满之后,再写入第二个页,页与页之间会通过指针连接

. 当第二页写满了,再往第三页写入

B. 主键乱序插入效果

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

. 此时再插入 id 50 的记录,我们来看看会发生什么现象

会再次开启一个页,写入新的页中吗?

不会。因为,索引结构的叶子节点是有顺序的。按照顺序,应该存储在 47 之后。

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

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

50

移动数据,并插入 id 50 的数据之后,那么此时,这三个页之间的数据顺序是有问题的。 1# 的下

一个页,应该是3# 3# 的下一个页是 2# 。 所以,此时,需要重新设置链表指针。

上述的这种现象,称之为 " 页分裂 " ,是比较耗费性能的操作。

3). 页合并

目前表中已有数据的索引结构 ( 叶子节点 ) 如下:

当我们对已有数据进行删除时,具体的效果如下 :

当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记( flaged )为删除并且它的空

间变得允许被其他记录声明使用。

当我们继续删除2#的数据记录

当页中删除的记录达到 MERGE_THRESHOLD (默认为页的 50% ), InnoDB 会开始寻找最靠近的

页(前或后)看看是否可以将两个页合并以优化空间使用。

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

这个里面所发生的合并页的这个现象,就称之为 "页合并"

知识小贴士:

MERGE_THRESHOLD :合并页的阈值,可以自己设置,在创建表或者创建索引时指定。

4). 索引设计原则

满足业务需求的情况下,尽量降低主键的长度。

插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键。 尽量不要使用 UUID

主键或者是其他自然主键,如身份证号。

业务操作时,避免对主键的修改。

3.order by优化

MySQL 的排序,有两种方式:

Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort

buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index ,不需要

额外排序,操作效率高。

对于以上的两种排序方式, Using index 的性能高,而 Using filesort 的性能低,我们在优化排序

操作时,尽量要优化为 Using index

接下来,我们来做一个测试:

A. 数据准备

把之前测试时,为 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;

B. 执行排序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 , 排序性能较低。

C. 创建索引

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

D. 创建索引后,根据 age, phone 进行升序排序

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

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

建立索引之后,再次进行排序查询,就由原来的 Using filesort , 变为了 Using index ,性能

就是比较高的了。

E. 创建索引后,根据 age, phone 进行降序排序

 

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

也出现 Using index , 但是此时 Extra 中出现了 Backward index scan ,这个代表反向扫描索

引,因为在 MySQL 中我们创建的索引,默认索引的叶子节点是从小到大排序的,而此时我们查询

排序时,是从大到小,所以,在扫描时,就是反向扫描,就会出现 Backward index scan 。 在

MySQL8 版本中,支持降序索引,我们也可以创建降序索引。

F. 根据 phone age 进行升序排序, phone 在前, age 在后。

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

排序时 , 也需要满足最左前缀法则 , 否则也会出现 filesort 。因为在创建索引的时候, age 是第一个

字段, phone 是第二个字段,所以排序时,也就该按照这个顺序来,否则就会出现 Using filesort。

F. 根据 age, phone 进行降序一个升序,一个降序

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

因为创建索引时,如果未指定顺序,默认都是按照升序排序的,而查询时,一个升序,一个降序,

此时就会出现Using filesort

为了解决上述的问题,我们可以创建一个索引,这个联合索引中 age 升序排序, phone 倒序排

序。

G. 创建联合索引 (age 升序排序, phone 倒序排序 )

 

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

H. 然后再次执行如下SQL

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

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

由上述的测试 , 我们得出 order by 优化原则 :

A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。

B. 尽量使用覆盖索引。

C. 多字段排序 , 一个升序一个降序,此时需要注意联合索引在创建时的规则( ASC/DESC )。

D. 如果不可避免的出现 filesort ,大数据量排序时,可以适当增大排序缓冲区大小

sort_buffer_size( 默认 256k)

4.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;

接下来,在没有索引的情况下,执行如下 SQL ,查询执行计划:

explain select profession , count(*) from tb_user group by profession ;

然后,我们在针对于 profession age status 创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession , age , status);

紧接着,再执行前面相同的 SQL 查看执行计划。

explain select profession , count(*) from tb_user group by profession ;

再执行如下的分组查询 SQL ,查看执行计划:

我们发现,如果仅仅根据 age 分组,就会出现 Using temporary ;而如果是 根据

profession,age 两个字段同时分组,则不会出现 Using temporary 。原因是因为对于分组操作,

在联合索引中,也是符合最左前缀法则的。

所以,在分组操作中,我们需要通过以下两点进行优化,以提升性能:

A. 在分组操作时,可以通过索引来提高效率。

B. 分组操作时,索引的使用也是满足最左前缀法则的。

5.limit优化

在数据量比较大时,如果进行 limit 分页查询,在查询时,越往后,分页查询效率越低。

我们一起来看看执行limit 分页查询耗时对比:

通过测试我们会看到,越往后,分页查询效率越低,这就是分页查询的问题所在。

因为,当在进行分页查询时,如果执行 limit 2000000,10 ,此时需要 MySQL 排序前 2000010

录,仅仅返回 2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大 。

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

询形式进行优化。

 

explain select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;

6.count优化

6.1概述

select count(*) from tb_user ;

在之前的测试中,我们发现,如果数据量很大,在执行 count 操作时,是非常耗时的。

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个

数,效率很高; 但是如果是带条件的 count MyISAM 也慢。

InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出

来,然后累积计数。

如果说要大幅度提升 InnoDB 表的 count 效率,主要的优化思路:自己计数 ( 可以借助于 redis 这样的

数据库进行, 但是如果是带条件的 count 又比较麻烦了 )

 

6.2count用法

count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是

NULL ,累计值就加 1 ,否则不加,最后返回累计值。

用法: count * )、 count (主键)、 count (字段)、 count (数字)

按照效率排序的话, count( 字段 ) < count( 主键 id) < count(1) ≈ count(*) ,所以尽

量使用 count(*)

7.update优化

我们主要需要注意一下 update 语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ; 1

当我们在执行删除的 SQL 语句时,会锁定 id 1 这一行的数据,然后事务提交之后,行锁释放。

但是当我们在执行如下 SQL 时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务,在执行上述的 SQL 时,我们发现行锁升级为了表锁。 导致该 update 语句的

性能大大降低

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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
14天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
12 0
|
20天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
20天前
|
存储 SQL 关系型数据库
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
|
20天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
|
14天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
79 1
|
20天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
20天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
1天前
|
SQL 分布式计算 资源调度
一文解析 ODPS SQL 任务优化方法原理
本文重点尝试从ODPS SQL的逻辑执行计划和Logview中的执行计划出发,分析日常数据研发过程中各种优化方法背后的原理,覆盖了部分调优方法的分析,从知道怎么优化,到为什么这样优化,以及还能怎样优化。
|
8天前
|
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进行限制。
33 3
|
15天前
|
SQL 存储 关系型数据库
【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
【4月更文挑战第5天】两阶段提交是为确保`redo log`和`binlog`逻辑一致,避免数据不一致。若先写`redo log`, crash后数据可能丢失,导致恢复后状态错误;若先写`binlog`,crash则可能导致重复事务,影响数据库一致性。一天一备相较于一周一备,能缩短“最长恢复时间”,但需权衡额外的存储成本。
16 1