基础篇
2SQL
全称 Structured Query Language,结构化查询语言。操作关系型数据库的编程语言,定义了
一套操作关系型数据库统一标准 。
2.3 DDL
Data Definition Language,数据定义语言,用来定义数据库对象(数据库,表,字段) 。
2.3.1 数据库操作
1). 查询所有数据库
show databases ;
2). 查询当前数据库
select database() ;
3). 创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ] ;
4). 删除数据库
drop database [ if exists ] 数据库名 ;
5). 切换数据库
use 数据库名 ;
2.3.2 表操作
2.3.2.1 表操作-查询创建
1). 查询当前数据库所有表
show tables;
2). 查看指定表结构
desc 表名 ;
3). 查询指定表的建表语句
show create table 表名 ;
4). 创建表结构
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
注意: […] 内为可选参数,最后一个字段后面没有逗号
2.3.2.2 表操作-数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
1). 数值类型
2). 字符串类型
3). 日期时间类型
2.3.2.4 表操作-修改
1). 添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
2). 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
3). 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
4). 删除字段
ALTER TABLE 表名 DROP 字段名;
5). 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
2.3.2.5 表操作-删除
1). 删除表
DROP TABLE [ IF EXISTS ] 表名;
2). 删除指定表, 并重新创建表
TRUNCATE TABLE 表名;
注意: 在删除表的时候,表中的全部数据也都会被删除
2.5 DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进
行增、删、改操作。
添加数据(INSERT)
修改数据(UPDATE)
删除数据(DELETE)
2.5.1 添加数据
1). 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
2). 给全部字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...);
3). 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值
1, 值2, ...) ;
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
注意事项:
• 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小,应该在字段的规定范围内。
2.5.2 修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
注意事项:
修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
2.5.3 删除数据
DELETE FROM 表名 [ WHERE 条件 ] ;
注意事项:
• DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数
据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL即
可)。
• 当进行删除全部数据操作时,datagrip会提示我们,询问是否确认删除,我们直接点击
Execute即可。
2.6 DQL
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记
录。
查询关键字: SELECT
2.6.1 基本语法
DQL 查询语句,语法结构如下:
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
2.6.2 基础查询
在基本查询的DQL语句中,不带任何的查询条件,查询的语法如下:
1). 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)。
2). 字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
3). 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
2.6.3 条件查询
1). 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
2). 条件
常用的比较运算符如下:
常用的逻辑运算符如下:
2.6.4 聚合函数
1). 介绍
将一列数据作为一个整体,进行纵向计算 。
2). 常见的聚合函数
3). 语法
SELECT 聚合函数(字段列表) FROM 表名 ;
注意 : NULL值是不参与所有聚合函数运算的。
2.6.5 分组查询
1). 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];
2). where与having区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组
之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项:
• 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB
2.6.6 排序查询
排序在日常开发中是非常常见的一个操作,有升序排序,也有降序排序。
1). 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
2). 排序方式
ASC : 升序(默认值)
DESC: 降序
注意事项:
• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
2.6.7 分页查询
分页操作在业务系统开发时,也是非常常见的一个功能,我们在网站中看到的各种各样的分页条,后台都需要借助于数据库的分页操作。
1). 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
注意事项:
• 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
• 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
• 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
2.6.9 执行顺序
在讲解DQL语句的具体语法之前,我们已经讲解了DQL语句的完整语法,及编写顺序,接下来,我们要来说明的是DQL语句在执行时的执行顺序,也就是先执行那一部分,后执行那一部分。
2.7 DCL
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访
问权限。
2.7.1 管理用户
1). 查询用户
select * from mysql.user;
其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以
远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
2). 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3). 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
4). 删除用户
DROP USER '用户名'@'主机名' ; 1
注意事项:
• 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。
2.7.2 权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
上述只是简单罗列了常见的几种权限描述,其他权限描述及含义,可以直接参考官方文档。
1). 查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
2). 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3). 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; 1
注意事项:
• 多个权限之间,使用逗号分隔
• 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
3函数
MySQL中的函数主要分为以下四类: 字符串函数、数值函数、日期函数、流程函数
3.1 字符串函数
MySQL中内置了很多字符串函数,常用的几个如下:
3.2 数值函数
常见的数值函数如下:
3.3 日期函数
常见的日期函数如下:
3.4 流程函数
流程函数也是很常用的一类函数,可以在SQL语句中实现条件筛选,从而提高语句的效率。
4. 约束
4.1 概述
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
分类:
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
4.3 外键约束
4.3.1 介绍
外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
4.3.2 语法
1). 添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
2). 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
4.3.3 删除/更新行为
添加了外键之后,再删除父表数据时产生的约束行为,我们就称为删除/更新行为。具体的删除/更新行为有以下几种:
具体语法为:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
5. 多表查询
我们之前在讲解SQL语句的时候,讲解了DQL语句,也就是数据查询语句,但是之前讲解的查询都是单
表查询,而本章节我们要学习的则是多表查询操作,主要从以下几个方面进行讲解。
5.1 多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结
构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
一对多(多对一),多对多,一对一
5.2 多表查询概述
5.2.2 概述
多表查询就是指从多张表中查询数据。
执行多表查询,就只需要使用逗号分隔多张表即可;
而在多表查询中,我们是需要消除无效的笛卡尔积的,只保留两张表关联部分的数据。
在SQL语句中,如何来去除无效的笛卡尔积呢? 我们可以给多表查询加上连接查询的条件即可。
5.2.3 分类
连接查询
内连接:相当于查询A、B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
自连接:当前表与自身的连接查询,自连接必须使用表别名
子查询
5.3 内连接
内连接查询的是两张表交集部分的数据。
内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构
1). 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
2). 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
表的别名:
①. tablea as 别名1 , tableb as 别名2 ;
②. tablea 别名1 , tableb 别名2 ;
注意事项:
一旦为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字
段。
5.4 外连接
外连接分为两种,分别是:左外连接 和 右外连接。具体的语法结构为:
1). 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
2). 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺
序就可以了。而我们在日常开发使用时,更偏向于左外连接。
5.5 自连接
5.5.1 自连接查询
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。我们先来学习一下自连接
的查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
而对于自连接查询,可以是内连接查询,也可以是外连接查询。
注意事项:
在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底
是哪一张表的字段
5.5.2 联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
注意:
如果多条查询语句查询出来的结果,字段数量不一致,在进行union/union all联合查询时,将会报
错.
5.6 子查询
5.6.1 概述
1). 概念
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
2). 分类
根据子查询结果不同,分为:
A. 标量子查询(子查询结果为单个值)
B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)
根据子查询位置,分为:
A. WHERE之后
B. FROM之后
C. SELECT之后
5.6.2 标量子查询
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
5.6.3 列子查询
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
5.6.4 行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN
5.6.5 表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
6. 事务
6.1 事务简介
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
注意: 默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐式的提交事务。
6.2 事务操作
6.2.2 控制事务一
1). 查看/设置事务提交方式
SELECT @@autocommit ;
SET @@autocommit = 0 ;
2). 提交事务
COMMIT;
3). 回滚事务
ROLLBACK;
注意:上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提
交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
6.2.3 控制事务二
1). 开启事务
START TRANSACTION 或 BEGIN ;
2). 提交事务
COMMIT;
3). 回滚事务
ROLLBACK;
6.3 事务四大特性
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性,简称ACID。
6.4 并发事务问题
0)脏写:脏写是指当多个事务并发写同一数据时,先执行的事务所写的数据会被后写的数据覆盖。
1). 赃读:一个事务读到另外一个事务还没有提交的数据。
2). 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
3). 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了 “幻影”。
6.5 事务隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:
1). 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
2). 设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {
READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意:事务隔离级别越高,数据越安全,但是性能越低。
进阶
1. 存储引擎
1.1 MySQL体系结构
1). 连接层
2). 服务层
3). 引擎层
4). 存储层
1.2 存储引擎介绍
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。我们可以在创建表的时候,来指定选择的存储引擎,如果没有指定将自动选择默认的存储引擎。
1). 建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;
2). 查询当前数据库支持的存储引擎
show engines;
1.3 存储引擎特点
1.3.1 InnoDB
1). 介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5 之后,InnoDB是默认的MySQL 存储引擎。
2). 特点
DML操作遵循ACID模型,支持事务;
行级锁,提高并发访问性能;
支持外键FOREIGN KEY约束,保证数据的完整性和正确性;
支持崩溃恢复
支持mvcc(多版本并发控制)
3). 文件
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm-早期的 、sdi-新版的)、数据和索引。
参数:innodb_file_per_table
show variables like 'innodb_file_per_table';
4). 逻辑存储结构
表空间 : InnoDB存储引擎逻辑结构的最高层,ibd文件其实就是表空间文件,在表空间中可以包含多个Segment段。
段 : 表空间是由各个段组成的, 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理,都是引擎自身完成,不需要人为对其控制,一个段中包含多个区。
区 : 区是表空间的单元结构,每个区的大小为1M。 默认情况下, InnoDB存储引擎页大小为16K, 即一个区中一共有64个连续的页。
页 : 页是组成区的最小单元,页也是InnoDB 存储引擎磁盘管理的最小单元,每个页的大小默认为 16KB。为了保证页的连续性,InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行 : InnoDB 存储引擎是面向行的,也就是说数据是按行进行存放的,在每一行中除了定义表时所指定的字段以外,还包含两个隐藏字段(后面会详细介绍)。
1.3.2 MyISAM
1). 介绍
MyISAM是MySQL早期的默认存储引擎。
2). 特点
不支持事务,不支持外键
支持表锁,不支持行锁
访问速度快
支持全文索引(正排索引,倒排索引)
3). 文件
xxx.sdi:存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引
1.3.3 Memory
1). 介绍
Memory引擎的表数据时存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用。
2). 特点
数据存放内存中
支持hash索引(默认)和tree索引
不支持Blob(大的二进制信息)和Text(支持的是大文本)字段
3).文件
xxx.sdi:存储表结构信息
1.3.4 区别及特点
面试题:
InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的
存储引擎是memory表与临时表的区别
#临时表的创建
mysql> create temporary table test1 (id int, age int, name varchar(20),
primary key(id));
Query OK, 0 rows affected (0.04 sec)
#特点,创建完成之后,看不到表的名字;默认的存储引擎还是InnoDB;可以进行正常的SQL
(insert、update、delete、select);只会存在于当前会话,当会话关闭之后,临时表就消失
了。
#存储引擎是memory的表
mysql> create table test2 (id int, age int, name varchar(20), primary
key(id)) ENGINE=memory;
#存储引擎是memory的表,在创建之后,可以使用show tables看到表的名字;只是存储引擎被指定为
memory;也是可以进行正常的SQL语句(insert、update、delete、select)的;关闭当前会话,
存储引擎是memory可以进行正常的SQL操作;断电重启之后,存储引擎是memory中的数据丢失。
1.4 存储引擎选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
InnoDB: 是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
2. 索引
2.1 索引概述
2.1.1 介绍
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
2.1.2 演示
1). 无索引情况
在无索引情况下,就需要从第一行开始扫描,一直扫描到最后一行,我们称之为 全表扫描,性能很低。
2). 有索引情况
如果我们针对于这张表建立了索引,假设索引结构就是二叉树,那么也就意味着,会对age这个字段建立一个二叉树的索引结构。
备注: 这里我们只是假设索引的结构是二叉树,介绍一下索引的大概原理,只是一个示意图,并不是索引的真实结构,索引的真实结构,后面会详细介绍。
2.1.3 特点
2.2 索引结构
2.2.1 概述
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
上述是MySQL中所支持的所有的索引结构,接下来,我们再来看看不同的存储引擎对于索引结构的支持
情况。
注意: 我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
2.2.2 二叉树
如果选择二叉树作为索引结构,会存在以下缺点:
顺序插入时,会形成一个链表,查询性能大大降低。
大数据量情况下,层级较深,检索速度慢。
由于红黑树也是一颗二叉树,所以也会存在一个缺点:
大数据量情况下,层级较深,检索速度慢。
2.2.3 B-Tree
B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。
以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key,5个指针:
特点:
5阶的B树,每一个节点最多存储4个key,对应5个指针。
一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。
在B树中,非叶子节点和叶子节点都会存放数据。
2.2.4 B+Tree
B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为4(4阶)的b+tree为例,来看一下其结构示意图:
我们可以看到,两部分:
绿色框框起来的部分,是索引部分,仅仅起到索引数据的作用,不存储数据。
红色框框起来的部分,是数据存储部分,在其叶子节点中要存储具体的数据。
最终我们看到,B+Tree 与 B-Tree相比,主要有以下三点区别:
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
数据是存在磁盘上面了,如果建立的索引之后,会产生索引树(B+),也是存在磁盘上面。用索引进行查询的时候,需要将索引树加载到内存中,树的高度等同于磁盘IO的次数。
2.2.5 Hash
MySQL中除了支持B+Tree索引,还支持一种索引类型—Hash索引。
1). 结构
哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
2). 特点
A. Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)
B. 无法利用索引完成排序操作
C. 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
3). 存储引擎支持
在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
思考题: 为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树,层级更少,搜索效率高;
B. 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
C. 相对Hash索引,B+tree支持范围匹配及排序操作;
2.3 索引分类
2.3.1 索引分类
在MySQL数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。
2.3.2 聚集索引&二级索引
而在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
聚集索引选取规则:
如果存在主键,主键索引就是聚集索引。
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
聚集索引和二级索引的具体结构如下:
聚集索引的叶子节点下挂的是这一行的数据 。
二级索引的叶子节点下挂的是该字段值对应的主键值
如果存储引擎是InnoDB的表,并且是以主键创建的索引树,就会在叶子结点下面存放该条数据的完整信息。
如果是以非主键创建的索引树,就会在叶子结点下面存放对应主键索引,就不会将该条数据的完整信息全部存在辅助索引树上。
如果存储引擎是MyISAM的表,不管是以主键创建的主键索引树还是以非主键创建的索引树,在叶子结点里面存放的都是该条数据对应的地址值。
对于InnoDB存储引擎而言,如果在辅助索引树上进行查找某些列,并且在辅助索引树上找到了该列,那么就是称为索引覆盖;如果在辅助索引树上查找某些列A、B,这些列在辅助索引树上找不到,此时可以在辅助索引树上找到对应的主键,然后通过主键索引树找到那些列A、B,这称为回表。
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
思考题:
以下两条SQL语句,那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = ‘Arm’ ;
备注: id为主键,name字段创建的有索引;
解答:
A 语句的执行性能要高于B 语句。
因为A语句直接走聚集索引,直接返回数据。 而B语句需要先查询name字段的二级索引,然后再查询聚集索引,也就是需要进行回表查询。
思考题:
InnoDB主键索引的B+tree高度为多高呢?
假设:一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间,主键即使为bigint,占用字节数为8。
高度为2:
n * 8 + (n + 1) * 6 = 161024 , 算出n约为 1170
1171 16 = 18736
也就是说,如果树的高度为2,则可以存储 18000 多条记录。
高度为3:
1171 * 1171 * 16 = 21939856
也就是说,如果树的高度为3,则可以存储 2200w 左右的记录。
2.4 索引语法
1). 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;
2). 查看索引
SHOW INDEX FROM table_name ;
3). 删除索引
DROP INDEX index_name ON table_name ;
2.5 SQL性能分析
2.5.1 SQL执行频率
MySQL 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
通过上述指令,我们可以查看到当前数据库到底是以查询为主,还是以增删改为主,从而为数据库优化提供参考依据。 如果是以增删改为主,我们可以考虑不对其进行索引的优化。 如果是以查询为主,那么就要考虑对数据库的索引进行优化了。
那么通过查询SQL的执行频次,我们就能够知道当前数据库到底是增删改为主,还是查询为主。 那假如说是以查询为主,我们又该如何定位针对于那些查询语句进行优化呢? 次数我们可以借助于慢查询日志。
2.5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,我们可以查看一下系统变量 slow_query_log。
如果要开启慢查询日志,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log。
systemctl restart mysqld
2.5.3 profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling ;
可以通过set语句在session/global级别开启profiling
SET profiling = 1;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
2.5.4 explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
Explain 执行计划中各个字段的含义:
2.6 索引使用
2.6.1 验证索引效率
2.6.2 最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。
注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。
2.6.3 范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
所以,在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 <
2.6.4 索引失效情况
2.6.4.1 索引列运算
不要在索引列上进行运算操作, 索引将失效。
2.6.4.2 字符串不加引号
字符串类型字段使用时,不加引号,索引将失效
2.6.4.3 模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
3.6.4.4 or连接条件
用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
3.6.4.5 数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引。
就是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效。
is null 、is not null是否走索引,得具体情况具体分析,并不是固定的。
2.6.5 SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1). use index : 建议MySQL使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进行评估)。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
2). ignore index : 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
3). force index : 强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
2.6.6 覆盖索引
尽量使用覆盖索引,减少select *。 那么什么是覆盖索引呢? 覆盖索引是指 查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 。
SQL语句的执行计划前面所有的指标都是一样的,看不出来差异。但是此时,我们主要关注的是后面的Extra
因为,在tb_user表中有一个联合索引 idx_user_pro_age_sta,该索引关联了三个字段profession、age、status,而这个索引也是一个二级索引,所以叶子节点下面挂的是这一行的主键id。 所以当我们查询返回的数据在 id、profession、age、status 之中,则直接走二级索引直接返回数据了。 如果超出这个范围,就需要拿到主键id,再去扫描聚集索引,再获取额外的数据
了,这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值,很容易就会造成回表查询(除非是根据主键查询,此时只会扫描聚集索引)。
思考题:
一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:select id,username,password from tb_user where username =‘itcast’;
答案: 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句,在查询的过程中,出现回表查询。
2.6.7 前缀索引
当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
1). 语法
create index idx_xxxx on table_name(column(n)) ;
2). 前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
3). 前缀索引的查询流程
2.6.8 单列索引与联合索引
单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
2.7 索引设计原则
1). 针对于数据量较大,且查询比较频繁的表建立索引。
2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7). 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询
3. SQL优化
3.1 插入数据
3.1.1 insert
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
1). 优化方案一:批量插入数据
2). 优化方案二:手动控制事务
3). 优化方案三:主键顺序插入,性能要高于乱序插入。
3.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' ;
主键顺序插入性能高于乱序插入
在load时,主键顺序插入性能高于乱序插入
3.2 主键优化
在上一小节,我们提到,主键顺序插入的性能是要高于乱序插入的。 这一小节,就来介绍一下具体的原因,然后再分析一下主键又该如何设计。
1). 数据组织方式
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)。
在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.3 order by优化
MySQL的排序,有两种方式:
Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。
对于以上的两种排序方式,Using index的性能高,而Using filesort的性能低,我们在优化排序操作时,尽量要优化为 Using index
Extra中出现了 Backward index scan,这个代表反向扫描索引,因为在MySQL中我们创建的索引,默认索引的叶子节点是从小到大排序的,而此时我们查询排序时,是从大到小,所以,在扫描时,就是反向扫描,就会出现 Backward index scan。 在MySQL8版本中,支持降序索引,我们也可以创建降序索引
由上述的测试,我们得出order by优化原则:
A. 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
B. 尽量使用覆盖索引。
C. 多字段排序, 一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
D. 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
3.4 group by优化
分组操作,我们主要来看看索引对于分组操作的影响。
所以,在分组操作中,我们需要通过以下两点进行优化,以提升性能:
A. 在分组操作时,可以通过索引来提高效率。
B. 分组操作时,索引的使用也是满足最左前缀法则的。
3.5 limit优化
在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。
优化思路: 一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。
3.6 count优化
3.6.1 概述
在之前的测试中,我们发现,如果数据量很大,在执行count操作时,是非常耗时的。
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count() 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM也慢。
InnoDB 引擎就麻烦了,它执行 count() 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
如果说要大幅度提升InnoDB表的count效率,主要的优化思路:自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)
3.6.2 count用法
count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是NULL,累计值就加 1,否则不加,最后返回累计值。
用法:count()、count(主键)、count(字段)、count(数字)
按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(),所以尽量使用 count(*)。
3.7 update优化
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。
4. 视图/存储过程/触发器
4.1 视图
4.1.1 介绍
视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。
通俗的讲,视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上
4.1.2 语法
1). 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ]
2). 查询
查看创建视图语句:SHOW CREATE VIEW 视图名称;
查看视图数据:SELECT * FROM 视图名称 ...... ;
3). 修改
方式一:CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH[ CASCADED | LOCAL ] CHECK OPTION ]
方式二:ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ]
4). 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
如果我们定义视图时,如果指定了条件,然后我们在插入、修改、删除数据时,是否可以做到必须满足条件才能操作,否则不能够操作呢? 答案是可以的,这就需要借助于视图的检查选项了。
4.1.3 检查选项
当使用WITH CHECK OPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如 插入,更新,删除,以使其符合视图的定义。 MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项: CASCADED 和 LOCAL,默认值为 CASCADED 。
1). CASCADED级联。
比如,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为 cascaded,但是v1视图创建时未指定检查选项。 则在执行检查时,不仅会检查v2,还会级联检查v2的关联视图v1。
2). LOCAL本地。
比如,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为 local ,但是v1视图创建时未指定检查选项。 则在执行检查时,知会检查v2,不会检查v2的关联视图v1。
4.1.4 视图的更新
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,则该视图不可更新:
A. 聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等)
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL
4.1.5 视图作用
1). 简单
视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
2). 安全
数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
3). 数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
4.2 存储过程
4.2.1 介绍
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
特点:
封装,复用 -----------------------> 可以把某一业务SQL封装在存储过程中,需要用到的时候直接调用即可。
可以接收参数,也可以返回数据 --------> 再存储过程中,可以传递参数,也可以接收返回值。
减少网络交互,效率提升 -------------> 如果涉及到多条SQL,每执行一次都是一次网络传输。 而如果封装在存储过程中,我们只需要网络交互一次可能就可以了。
4.2.2 基本语法
1). 创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;
2). 调用
CALL 名称 ([ 参数 ]);
3). 查看
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义
4). 删除
DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;
注意:
在命令行中,执行创建存储过程的SQL时,需要通过关键字 delimiter 指定SQL语句的结束符。
4.2.3 变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
4.2.3.1 系统变量
系统变量 是MySQL服务器提供,不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
1). 查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
2). 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
注意:
如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话,在另外一个会话窗口就不生效了。
4.2.3.2 用户定义变量
用户定义变量 是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。
1). 赋值
方式一:
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
赋值时,可以使用 = ,也可以使用 := 。
方式二:
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
2). 使用
SELECT @var_name ;
注意: 用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL。
4.2.3.3 局部变量
局部变量 是根据需要定义的在局部生效的变量,访问之前,需要DECLARE声明。可用作存储过程内的局部变量和输入参数,局部变量的范围是在其内声明的BEGIN … END块。
1). 声明
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
2). 赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
4.2.4 if
1). 介绍
if 用于做条件判断,具体的语法结构为
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
在if条件判断的结构中,ELSE IF 结构可以有多个,也可以没有。 ELSE结构可以有,也可以没有。
4.2.5 参数
1). 介绍
参数的类型,主要分为以下三种:IN、OUT、INOUT。 具体的含义如下:
用法:
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;
4.2.6 case
1). 介绍
case结构及作用,和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式:
语法1:
-- 含义: 当case_value的值为 when_value1时,执行statement_list1,当值为 when_value2时,
执行statement_list2, 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
语法2:
-- 含义: 当条件search_condition1成立时,执行statement_list1,当条件search_condition2成
立时,执行statement_list2, 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;
注意:如果判定条件有多个,多个条件之间,可以使用 and 或 or 进行连接。
4.2.7 while
1). 介绍
while 循环是有条件的循环控制语句。满足条件后,再执行循环体中的SQL语句。具体语法为:
-- 先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;
4.2.8 repeat
1). 介绍
repeat是有条件的循环控制语句, 当满足until声明的条件的时候,则退出循环 。具体语法为:
-- 先执行一次逻辑,然后判定UNTIL条件是否满足,如果满足,则退出。如果不满足,则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
4.2.9 loop
1). 介绍
LOOP 实现简单的循环,如果不在SQL逻辑中增加退出循环的条件,可以用其来实现简单的死循环。
LOOP可以配合一下两个语句使用:
LEAVE :配合循环使用,退出循环。
ITERATE:必须用在循环中,作用是跳过当前循环剩下的语句,直接进入下一次循环。
[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
上述语法中出现的 begin_label,end_label,label 指的都是我们所自定义的标记。
4.2.10 游标
1). 介绍
游标(CURSOR)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE,其语法分别如下。
A. 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
B. 打开游标
OPEN 游标名称 ;
C. 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
D. 关闭游标
CLOSE 游标名称 ;
4.2.11 条件处理程序
1). 介绍
条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:
DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;
handler_action 的取值:
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值:
SQLSTATE sqlstate_value: 状态码,如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
4.3 存储函数
1). 介绍
存储函数是有返回值的存储过程,存储函数的参数只能是IN类型的。具体语法如下:
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;
characteristic说明:
DETERMINISTIC:相同的输入参数总是产生相同的结果
NO SQL :不包含 SQL 语句。
READS SQL DATA:包含读取数据的语句,但不包含写入数据的语句。
4.4 触发器
4.4.1 介绍
触发器是与表有关的数据库对象,指在insert/update/delete之前(BEFORE)或之后(AFTER),触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发
4.4.2 语法
1). 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
trigger_stmt ;
END;
2). 查看
SHOW TRIGGERS ;
3). 删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name,默认为当前数据库 。
5. 锁
5.1 概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
MySQL中的锁,按照锁的粒度分,分为以下三类:
全局锁:锁定数据库中的所有表。
表级锁:每次操作锁住整张表。
行级锁:每次操作锁住对应的行数据。
5.2 全局锁
5.2.1 介绍
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
为什么全库逻辑备份,就需要加全就锁呢?
对数据库进行进行逻辑备份之前,先对整个数据库加上全局锁,一旦加了全局锁之后,其他的DDL、DML全部都处于阻塞状态,但是可以执行DQL语句,也就是处于只读状态,而数据备份就是查询操作。那么数据在进行逻辑备份的过程中,数据库中的数据就是不会发生变化的,这样就保证了数据的一致性和完整性。
5.2.2 语法
1). 加全局锁
flush tables with read lock ;
2). 数据备份
mysqldump -uroot –p1234 itcast > itcast.sql
数据备份的相关指令, 在后面MySQL管理章节, 还会详细讲解.
3). 释放锁
unlock tables ;
5.2.3 特点
数据库中加全局锁,是一个比较重的操作,存在以下问题:
如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。
在InnoDB引擎中,我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql
5.3 表级锁
5.3.1 介绍
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁,主要分为以下三类:
表锁,元数据锁(meta data lock,MDL),意向锁
5.3.2 表锁
对于表锁,分为两类:
表共享读锁(read lock)
表独占写锁(write lock)
语法:
加锁:lock tables 表名… read/write。
释放锁:unlock tables / 客户端断开连接 。
特点:
A. 读锁:左侧为客户端一,对指定表加了读锁,不会影响右侧客户端二的读,但是会阻塞右侧客户端的写
B. 写锁:左侧为客户端一,对指定表加了写锁,会阻塞右侧客户端的读和写。
结论: 读锁不会阻塞其他客户端的读,但是会阻塞写。写锁既会阻塞其他客户端的读,又会阻塞其他客户端的写。
5.3.3 元数据锁
meta data lock , 元数据锁,简写MDL。
MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML与DDL冲突,保证读写的正确性。
这里的元数据,大家可以简单理解为就是一张表的表结构。 也就是说,某一张表涉及到未提交的事务时,是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)。
常见的SQL操作时,所添加的元数据锁:
我们可以通过下面的SQL,来查看数据库中的元数据锁的情况:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
5.3.4 意向锁
1). 介绍
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查。
2). 分类
意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容,与表锁排他锁(write)互斥。
意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。
一旦事务提交了,意向共享锁、意向排他锁,都会自动释放。
可以通过以下SQL,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
5.4 行级锁
5.4.1 介绍
行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:
行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
5.4.2 行锁
1). 介绍
InnoDB实现了以下两种类型的行锁:
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
两种行锁的兼容情况如下:
常见的SQL语句,在执行时,所加的行锁如下:
2). 演示
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时 就会升级为表锁。
可以通过以下SQL,查看意向锁及行锁的加锁情况:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
5.4.3 间隙锁&临键锁
默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止幻读。
索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁 。
索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock 退化为间隙锁。
索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
6. InnoDB引擎
6.1 逻辑存储结构
InnoDB的逻辑存储结构如下图所示:
1). 表空间
表空间是InnoDB存储引擎逻辑结构的最高层, 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ,则每张表都会有一个表空间(xxx.ibd),一个mysql实例可以对应多个表空间,用于存储记录、索引等数据。
2). 段
段,分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment),InnoDB是索引组织表,数据段就是B+树的叶子节点, 索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。
3). 区
区,表空间的单元结构,每个区的大小为1M。 默认情况下, InnoDB存储引擎页大小为16K, 即一个区中一共有64个连续的页。
4). 页
页,是InnoDB 存储引擎磁盘管理的最小单元,每个页的大小默认为 16KB。为了保证页的连续性,InnoDB 存储引擎每次从磁盘申请 4-5 个区。
5). 行
行,InnoDB 存储引擎数据是按行进行存放的。
在行中,默认有两个隐藏字段:
Trx_id:每次对某条记录进行改动时,都会把对应的事务id赋值给trx_id隐藏列。
Roll_pointer:每次对某条引记录进行改动时,都会把旧的版本写入到undo日志中,然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。
6.2 架构(略)
6.3 事务原理(略)
6.4 MVCC(略)
7. MySQL管理
7.1 系统数据库
Mysql数据库安装完成后,自带了一下四个数据库,具体作用如下:
7.2 常用工具
7.2.1 mysql
该mysql不是指mysql服务,而是指mysql的客户端工具。
语法 :
mysql [options] [database]
选项 :
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出
-e选项可以在Mysql客户端执行SQL语句,而不用连接到MySQL数据库再执行,对于一些批处理脚本,这种方式尤其方便
7.2.2 mysqladmin
mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
通过帮助文档查看选项:
mysqladmin --help
语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
7.2.3 mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存,所以如果想要检查这些文本的文本格式,就会使用到mysqlbinlog 日志管理工具。
语法 :
mysqlbinlog [options] log-files1 log-files2 ...
选项 :
-d, --database=name 指定数据库名称,只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,--result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式, 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。
7.2.4 mysqlshow
mysqlshow 客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
语法 :
mysqlshow [options] [db_name [table_name [col_name]]]
选项 :
--count 显示数据库及表的统计信息(数据库,表 均可以不指定)
-i 显示指定数据库或者指定表的状态信息
示例:
#查询test库中每个表中的字段书,及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
7.2.5 mysqldump
mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表,及插入表的SQL语句。
语法 :
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
连接选项 :
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=# 指定连接端口
输出选项:
--add-drop-database 在每个数据库创建语句前加上 drop database 语句
--add-drop-table 在每个表创建语句前加上 drop table 语句 , 默认开启 ; 不
开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件:一个.sql文件,创建表结构的语句;一
个.txt文件,数据文件
7.2.6 mysqlimport/source
1). mysqlimport
mysqlimport 是客户端数据导入工具,用来导入mysqldump 加 -T 参数后导出的文本文件。
语法 :
mysqlimport [options] db_name textfile1 [textfile2...]
示例 :
mysqlimport -uroot -p2143 test /tmp/city.txt
2). source
如果需要导入sql文件,可以使用mysql中的source 指令 :
语法 :
source /root/xxxxx.sql
运维篇
1. 日志
1.1 错误日志
错误日志是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,建议首先查看此日志。
该日志是默认开启的,默认存放目录 /var/log/,默认的日志文件名为 mysqld.log 。查看日志位置
show variables like '%log_error%';
1.2 二进制日志
1.2.1 介绍
二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,但不包括数据查询(SELECT、SHOW)语句。
作用:①. 灾难时的数据恢复;②. MySQL的主从复制。在MySQL8版本中,默认二进制日志是开启着的,涉及到的参数如下
show variables like '%log_bin%';
参数说明:
log_bin_basename:当前数据库服务器的binlog日志的基础名称(前缀),具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。
log_bin_index:binlog的索引文件,里面记录了当前服务器关联的binlog文件有哪些。
1.2.2 格式
MySQL服务器中提供了多种格式来记录二进制日志,具体格式及特点如下:
show variables like '%binlog_format%';
如果我们需要配置二进制日志的格式,只需要在 /etc/my.cnf 中配置 binlog_format 参数即可。
1.2.3 查看
由于日志是以二进制方式存储的,不能直接读取,需要通过二进制日志查询工具 mysqlbinlog 来查看,具体语法:
mysqlbinlog [ 参数选项 ] logfilename
参数选项:
-d 指定数据库名称,只列出指定的数据库相关操作。
-o 忽略掉日志中的前n行命令。
-v 将行事件(数据变更)重构为SQL语句
-vv 将行事件(数据变更)重构为SQL语句,并输出注释信息
1.2.4 删除
对于比较繁忙的业务系统,每天生成的binlog数据巨大,如果长时间不清除,将会占用大量磁盘空间。可以通过以下几种方式清理日志:
也可以在mysql的配置文件中配置二进制日志的过期时间,设置了之后,二进制日志过期会自动删除。
show variables like '%binlog_expire_logs_seconds%'; 1
1.3 查询日志
查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。默认情况下,查询日志是未开启的。
如果需要开启查询日志,可以修改MySQL的配置文件 /etc/my.cnf 文件,添加如下内容:
#该选项用来开启查询日志 , 可选值 : 0 或者 1 ; 0 代表关闭, 1 代表开启
general_log=1
#设置日志的文件名 , 如果没有指定, 默认的文件名为 host_name.log
general_log_file=mysql_query.log
开启了查询日志之后,在MySQL的数据存放目录,也就是 /var/lib/mysql/ 目录下就会出现mysql_query.log 文件。之后所有的客户端的增删改查操作都会记录在该日志文件之中,长时间运行后,该日志文件将会非常大。
1.4 慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志,默认未开启。long_query_time 默认为10 秒,最小为 0, 精度可以到微秒。
如果需要开启慢查询日志,需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数:
#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2
默认情况下,不会记录管理语句,也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes,如下所述。
#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
上述所有的参数配置完成之后,都需要重新启动MySQL服务器才可以生效。
2. 主从复制
2.1 概述
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。
MySQL 复制的优点主要包含以下三个方面:
主库出现问题,可以快速切换到从库提供服务。
实现读写分离,降低主库的访问压力。
可以在从库中执行备份,以避免备份期间影响主库服务。
2.2 原理
MySQL主从复制的核心就是 二进制日志,具体的过程如下:
从上图来看,复制分成三步:
- Master 主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。
- 从库读取主库的二进制日志文件 Binlog ,写入到从库的中继日志 Relay Log 。
- slave重做中继日志中的事件,将改变反映它自己的数据。
2.3 搭建
2.3.1 准备
准备好两台服务器之后,在上述的两台服务器中分别安装好MySQL,并完成基础的初始化准备(安装、密码配置等操作)工作。 其中:
192.168.200.200 作为主服务器master 192.168.200.201 作为从服务器slave
2.3.2 主库配置
- 修改配置文件 /etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 232-1,默认为1
server-id=1
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01
- 重启MySQL服务器
systemctl restart mysqld
- 登录mysql,创建远程连接的账号,并授予主从复制权限
#创建itcast用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456'
;
#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
- 通过指令,查看二进制日志坐标
show master status ;
字段含义说明:
file : 从哪个日志文件开始推送日志文件
position : 从哪个位置开始推送日志
binlog_ignore_db : 指定不需要同步的数据库
2.3.3 从库配置
- 修改配置文件 /etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,和主库不一样即可
server-id=2
#是否只读,1 代表只读, 0 代表读写
read-only=1
- 重新启动MySQL服务
systemctl restart mysqld
- 登录mysql,设置主库配置
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.200', SOURCE_USER='itcast',
SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004',
SOURCE_LOG_POS=663;
上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本,执行如下SQL:
CHANGE MASTER TO MASTER_HOST='192.168.200.200', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=663;
4. 开启同步操作
start replica ; #8.0.22之后
start slave ; #8.0.22之前
- 查看主从同步状态
show replica status ; #8.0.22之后
show slave status ; #8.0.22之前
2.3.4 测试
- 在主库 192.168.200.200 上创建数据库、表,并插入数据
- 在从库 192.168.200.201 中查询数据,验证主从是否同步
3. 分库分表(略)
4. 读写分离
4.1 介绍
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力。通过MyCat即可轻易实现上述功能,不仅可以支持MySQL,也可以支持Oracle和SQL Server。
文章评论