MySQL进阶 —— 超详细操作演示!!!(中)
- 三、SQL 优化
- 3.1 插入数据
- 3.2 主键优化
- 3.3 order by 优化
- 3.4 group by 优化
- 3.5 limit 优化
- 3.6 count 优化
- 3.7 update 优化
- 四、视图/存储过程/触发器
- 4.1 视图
- 4.2 存储过程
- 4.3 存储函数
- 4.4 触发器
- 五、锁
- 六、InnoDB 引擎
- 七、MySQL 管理
MySQL— 基础语法大全及操作演示!!!
- 1、MySQL概述
- 2、SQL
- 3、函数
- 4、约束
- 5、多表查询
- 6、事务
MySQL进阶 —— 超详细操作演示!!!(持续更新)
- 1、存储引擎
- 2、索引
- 3、SQL 优化
- 4、视图 / 存储过程 / 触发器
- 5、锁
- 6、InnoDB 引擎
- 7、MySQL 管理
三、SQL 优化
3.1 插入数据
⭐️ 1) 、 insert
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
每执行一个
insert
都要与数据库建立连接进行网络传输,性能相对较低!
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....
- 1). 优化方案一 —— 批量插入数据
- (500条 ~ 1000 条)
insert into tb_test values(1,'Tom'), (2,'Cat'), (3,'Jerry');
- 2). 优化方案二 —— 手动控制事务
- (几万条,分割为多条
insert
) - mysql 中的事务提交方式默认为自动提交,意味着执行一条
insert
语句后,事务就提交了,会出现频繁的事务开启和提交!
- (几万条,分割为多条
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). 优化方案三 —— 主键顺序插入,性能要高于乱序插入。
- (取决于mysql 的数据组织结构)
主键乱序插入 : 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) 、 大批量插入数据
如果一次性需要插入 大批量数据 (比如: 几百万的记录 ),使用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' ;
-
fields terminated by ','
: 每个字段使用,
分隔;lines terminated by '\n'
: 每一行使用\n
分隔。
上传并查看 数据脚本文件 :load_user_100w_sort.sql
主键顺序插入性能高于乱序插入 !
示例演示:
- 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 '/data/sql/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;
我们看到,插入100w的记录,57s就完成了,性能很好;若使用 insert
插入,至少要十来分钟!
在
load
时,主键顺序插入 性能高于乱序插入
3.2 主键优化
主键顺序插入 的性能是要高于乱序插入的。 这一小节,就来介绍一下具体的原因,然后再分析一下主键又该如何设计。
1). 数据组织方式
- 在
InnoDB
存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为 索引组织表(index organized tableIOT
)。
- 行数据,都是存储在聚集索引的叶子节点上的。而我们之前也讲解过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
会开始寻找最靠近的页( 前或后)看看是否可以将两个页合并以优化空间使用。MERGE_THRESHOLD
:合并页的阈值,可以自己设置,在 创建表 或者 创建索引 时指定。
- 删除数据,并将页合并之后,再次插入新的数据
21
,则直接插入3#
页
这个里面所发生的合并页的这个现象,就称之为 “页合并”。
4). 索引设计原则
- 满足业务需求的情况下,尽量降低主键的长度。
- 插入数据时,尽量选择 顺序插入,选择使用
AUTO_INCREMENT
自增主键。 - 尽量 不要使用UUID 做主键或者是 其他自然主键,如
身份证号
。(无序且较长) - 业务操作时,避免对主键的修改。
3.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 on tb_user(age,phone);
D. 创建索引后,根据 age
, phone
进行升序排序
explain select id, age, phone from tb_user order by age;
explain select id, age, phone from tb_user order by age , phone;
- 建立索引之后,再次进行排序查询,就由原来的
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
。
G. 根据 age
, phone
进行降序一个升序,一个降序
explain select id,age,phone from tb_user order by age asc, phone desc ;
- 因为创建索引时,如果未指定顺序,默认都是按照升序排序的,而查询时,一个升序,一个降序,此时就会出现
Using filesort
。 - 为了解决上述的问题,我们可以创建一个索引,这个联合索引中
age
升序排序,phone
倒序排序。
H. 创建联合索引( age
升序排序,phone
倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc, phone desc);
I. 然后再次执行如下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
)。
3.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 on tb_user;
drop index idx_user_age_phone_ad on tb_user;
接下来,在没有索引的情况下,执行如下SQL,查询执行计划:
explain select profession, count(*) from tb_user group by profession ;
Using temporary
:临时表,这个性能也比较低。
然后,我们在针对于 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,查看执行计划:
explain select age, count(*) from tb_user group by age ;explain select profession, age, count(*) from tb_user group by profession, age ;
- 我们发现,如果仅仅根据
age
分组,就会出现Using temporary
; - 而如果是 根据
profession
,age
两个字段同时分组,则不会出现Using temporary
。 - 原因是因为对于分组操作,在联合索引中,也是符合最左前缀法则的。
所以,在分组操作中,我们需要通过以下两点进行优化,以提升性能:
- A. 在分组操作时,可以通过索引来提高效率。
- B. 分组操作时,索引的使用也是满足最左前缀法则的。
3.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;
3.6 count 优化
⭐️ 1) 、概述
select count(*) from tb_user ;
在之前的测试中,我们发现,如果数据量很大,在执行 count
操作时,是非常耗时的。
MyISAM
引擎把一个表的总行数存在了磁盘上,因此执行count(*)
的时候会直接返回这个数,效率很高; 但是如果是 带条件 的count
,MyISAM
也慢。InnoDB
引擎就麻烦了,它执行count(*)
的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
如果说要大幅度提升
InnoDB
表的count
效率,主要的优化思路:
- 自己计数(可以借助于
redis
这样的数据库进行, 但是如果是带条件的count
又比较麻烦了)。
⭐️ 2) 、count用法
count()
是一个聚合函数,对于返回的结果集,一行行地判断,如果 count
函数的参数不是 NULL
,累计值就加 1
,否则不加,最后返回累计值。
- 用法:
count(*)
、count(主键)
、count(字段)
、count(数字)
按照效率排序的话,
count(字段)
<count(主键 id)
<count(1)
≈count(*)
,所以 尽量使用count(*)
。
3.7 update 优化
我们主要需要注意一下 update
语句执行时的注意事项。
update course set name = 'javaEE' where id = 1 ;
- 当我们在执行删除的SQL语句时,会锁定
id
为1
这一行的数据,然后事务提交之后,行锁释放。
但是当我们在执行如下SQL时。
update course set name = 'SpringBoot' where name = 'PHP' ;
- 当我们开启多个事务,在执行上述的SQL时,我们发现行锁升级为了表锁。 导致该
update
语句的性能
大大降低。
InnoDB
的==行锁是针对索引加的锁==,不是针对记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。
🚀🚀🚀 SQL优化 快速食用:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
----------------------------------------
--大部分是根据索引优化
----------------------------------------## 1. 插入数据
insert : 批量插入、手动控制事务、主键顺序插入
大批量插入 : load data local infile## 2. 主键优化
主键长度尽量短、顺序插入 AUTO_INCREMENT(主键自增) ~~UUID~~(不要使用) ## 3. order by 优化
using index : 直接通过索引返回数据, 性能高
using filesort : 需要将返回的结果在排序缓冲区排序## 4. grop by 优化
索引,多字段分组满足最左前缀法则## 5. limit 优化
覆盖索引 + 子查询## 6. count 优化
性能 : count(字段) < count(主键 id ) < count(2) ~= count(*)## 7. update 优化
尽量根据主键/索引字段进行数据更新
四、视图/存储过程/触发器
MySQL 中的存储对象 :视图、存储过程、存储函数、触发器
4.1 视图
⭐️ 1) 介绍
- 视图(
View
)是一种 虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表(基表
),并且是在使用视图时动态生成的。 - 通俗的讲,视图 只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。
⭐️ 2) 语法
- a. 创建
create [OR replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
- b. 查询
查看创建视图语句:show create view 视图名称;
查看视图数据:select * from 视图名称 ...... ;
- c. 修改
方式一:create [OR replace] view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]
方式二:alter view 视图名称[(列名列表)] as select语句 [ with [cascaded | local] check option]
- d. 删除
drop view [if exists] 视图名称 [, 视图名称] ...
⭐️ 3) 检查选项
⭐️ 4) 视图的更新
⭐️ 5) 视图作用
⭐️ 6) 案例
4.2 存储过程
4.3 存储函数
4.4 触发器
五、锁
六、InnoDB 引擎
七、MySQL 管理
🚀🚀🚀 ** 快速食用:**---------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
注:仅供学习参考,如有不足,欢迎指正!!!