二、Mysql高级

MySQL / 2022-09-12

1、视图

视图是一种虚拟存在的数据表，简单来说就是就是将一条SELECT查询语句的结果封装到了一个虚拟表中

1）优点

  • 简单性

    • 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。

  • 安全性

    • 通过视图用户只能查询和修改他们所能见到的数据。但不能授权到数据库特定行和特定的列上。

  • 逻辑数据独立性

    • 视图可帮助用户屏蔽真实表结构变化带来的影响。
2）准备
-- 创建country表
CREATE TABLE country(
	id INT PRIMARY KEY AUTO_INCREMENT,
	country_name VARCHAR(30)
);
-- 添加数据
INSERT INTO country VALUES (NULL,'中国'),(NULL,'美国'),(NULL,'俄罗斯');

-- 创建city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,
	city_name VARCHAR(30),
	cid INT, -- 外键列。关联country表的主键列id
	CONSTRAINT cc_fk1 FOREIGN KEY (cid) REFERENCES country(id)
);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'纽约',2),(NULL,'莫斯科',3);
2）创建
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
	city_country
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
	
-- 创建一个视图，指定列名。将查询出来的结果保存到这张虚拟表中
CREATE
VIEW
	city_country2 (city_id,city_name,cid,country_name) 
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
3）查询
-- 标准语法
SELECT * FROM 视图名称;

-- 查询所有数据表，视图也会查询出来
SHOW TABLES;

-- 标准语法
SHOW CREATE VIEW 视图名称;

SHOW CREATE VIEW city_country;
4）修改
1.修改数据
-- 标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;

-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';
2.修改表结构
-- 标准语法
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 查询视图2
SELECT * FROM city_country2;

-- 修改视图2的列名city_id为id
ALTER
VIEW
	city_country2 (id,city_name,cid,country_name)
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
5）删除
-- 标准语法
DROP VIEW [IF EXISTS] 视图名称;

-- 删除视图
DROP VIEW city_country;

-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

2、备份与恢复

  • 使用SecureCRT工具连接到Linux系统，输入：mysqldump -u root -p 数据库名称 > 文件保存路径

  • 重新创建名称相同的数据库，导入文件执行：source 备份文件路径;

3、存储过程和函数

1）概念

存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合

2）优点
  • 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
  • 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
  • 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
  • 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理
3）区别
  • 函数必须有返回值
  • 存储过程没有返回值
4）创建与调用
1.准备
-- 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
	NAME VARCHAR(20),					-- 学生姓名
	age INT,							-- 学生年龄
	gender VARCHAR(5),					-- 学生性别
	score INT                           -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
2.语法
-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句; 
END$

-- 修改分隔符为分号
DELIMITER;
3.例子
-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE stu_group()
BEGIN
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符为分号
DELIMITER;

call stu_group();
5）查看与删除
-- 查询数据库中所有的存储过程 标准语法 mysql 5.*
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- mysql 8
SELECT * FROM information_schema.parameters WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA = '数据库名称'

-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称; 

DROP PROCEDURE IF EXISTS stu_group;
6）变量
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- 定义一个int类型变量、并赋默认值为10
delimiter $
CREATE PROCEDURE pro_test()
BEGIN 
	DECLARE num int DEFAULT 10;
	SELECT num;
END$

delimiter;

-- 调用pro_test存储过程
call pro_test();
-- 删除
DROP PROCEDURE if EXISTS pro_test;
1.set赋值
-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定义变量
	SET NAME = '存储过程';       -- 为变量赋值
	SELECT NAME;                -- 查询变量
END$

DELIMITER ;

-- 调用pro_test存储过程
CALL pro_test();

-- 删除
DROP PROCEDURE if EXISTS pro_test;
2.into
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定义变量
	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
	SELECT men,women;           -- 查询变量
END$

DELIMITER ;

-- 调用
call pro_test();
-- 创建
DROP PROCEDURE if EXISTS pro_test;
7）if
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;

/*
	定义一个int变量，用于存储班级总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上    学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/
delimiter $
CREATE PROCEDURE pro_test()
BEGIN 
	-- 总分数
	DECLARE total int;
	-- 叙述
	DECLARE description VARCHAR(10);
	-- 为总分数变量赋值
	SELECT SUM(score) INTO total FROM student;
	-- 判断
	IF total >=380 THEN 
		SET description = '学习优秀';
	ELSEIF total>=320 and total < 380 THEN
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END if;
	-- 查询总成绩和描述
	SELECT total,description;
END$

delimiter;

-- 调用pro_test存储过程
call pro_test();
-- 删除
DROP PROCEDURE if EXISTS pro_test;
8）参数的传递
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数，需要由调用者传递实际数据。默认的
	OUT:代表输出参数，该参数可以作为返回值
	INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;
1.输入参数
/*
	输入总成绩变量，代表学生总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test(IN total INT)
BEGIN
	-- 定义分数描述变量
	DECLARE description VARCHAR(10);
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = '学习优秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END IF;
	
	-- 查询总成绩和描述信息
	SELECT total,description;
END$

DELIMITER ;

-- 调用pro_test存储过程
CALL pro_test(390);
CALL pro_test((SELECT SUM(score) FROM student));
2.输出参数
/*
	输入总成绩变量，代表学生总成绩
	输出分数描述变量，代表学生总成绩的描述
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test(IN total INT,OUT description VARCHAR(10))
BEGIN
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = '学习优秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END IF;
END$

DELIMITER ;

-- 调用pro_test存储过程
CALL pro_test(310,@description);

-- 查询总成绩描述
SELECT @description;

  • tips

    • @变量名: 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

      @@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量

9）case
-- 标准语法
CASE 表达式
WHEN 值1/条件1 THEN 执行sql语句1;
[WHEN 值2/条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;

/*
	输入总成绩变量，代表学生总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test(IN total INT)
BEGIN
	-- 定义变量
	DECLARE description VARCHAR(10);
	-- 使用case判断
	CASE
	WHEN total >= 380 THEN
		SET description = '学习优秀';
	WHEN total >= 320 AND total < 380 THEN
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END CASE;
	
	-- 查询分数描述信息
	SELECT description;
END$

DELIMITER ;

-- 调用pro_test存储过程
CALL pro_test(390);
CALL pro_test((SELECT SUM(score) FROM student));

-- 删除
DROP PROCEDURE pro_test
10）while
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;

DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		-- 偶数判断
		IF num%2=0 THEN
			SET result = result + num; -- 累加
		END IF;
		
		-- 让num+1
		SET num = num + 1;         
	END WHILE;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test存储过程
CALL pro_test();
-- 删除
DROP PROCEDURE pro_test
11）repeat循环（未实践）
-- 标准语法
初始化语句;
REPEAT
	循环体语句;
	条件控制语句;
	UNTIL 条件判断语句
END REPEAT;

-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行

/*
	计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test9()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- repeat循环
	REPEAT
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
		
		-- 停止循环
		UNTIL num>10
	END REPEAT;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test9存储过程
CALL pro_test9();
12）loop循环（未实践）
-- 标准语法
初始化语句;
[循环名称:] LOOP
	条件判断语句
		[LEAVE 循环名称;]
	循环体语句;
	条件控制语句;
END LOOP 循环名称;

-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
--      如果不加退出循环的语句，那么就变成了死循环。

/*
	计算1~10之间的和
*/
DELIMITER $

CREATE PROCEDURE pro_test10()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- loop循环
	l:LOOP
		-- 条件成立，停止循环
		IF num > 10 THEN
			LEAVE l;
		END IF;
	
		-- 累加
		SET result = result + num;
		-- 让num+1
		SET num = num + 1;
	END LOOP l;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test10存储过程
CALL pro_test10();
13）游标（未实践）

  • 游标的概念

    • 游标可以遍历返回的多行结果，每次拿到一整行数据
    • 在存储过程和函数中可以使用游标对结果集进行循环的处理
    • 简单来说游标就类似于集合的迭代器遍历
    • MySQL中的游标只能用在存储过程和函数中

  • 游标的语法

    • 创建游标
    -- 标准语法
DECLARE 游标名称 CURSOR FOR 查询sql语句;
    • 打开游标
    -- 标准语法
OPEN 游标名称;
    • 使用游标获取数据
    -- 标准语法
FETCH 游标名称 INTO 变量名1,变量名2,...;
    • 关闭游标
    -- 标准语法
CLOSE 游标名称;

  • 游标的基本使用

-- 创建stu_score表
CREATE TABLE stu_score(
	id INT PRIMARY KEY AUTO_INCREMENT,
	score INT
);

/*
	将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 创建游标,查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 使用游标，遍历结果,拿到第1行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第2行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第3行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第4行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表
SELECT * FROM stu_score;


-- ===========================================================
/*
	出现的问题：
		student表中一共有4条数据，我们在游标遍历了4次，没有问题！
		但是在游标中多遍历几次呢？就会出现问题
*/
DELIMITER $

CREATE PROCEDURE pro_test11()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 创建游标,查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 使用游标，遍历结果,拿到第1行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第2行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第3行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第4行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 使用游标，遍历结果,拿到第5行数据
	FETCH stu_result INTO s_score;
	-- 将数据保存到stu_score表中
	INSERT INTO stu_score VALUES (NULL,s_score);
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test11存储过程
CALL pro_test11();

-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错
SELECT * FROM stu_score;
  • 游标的优化使用(配合循环使用)
/*
	当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
	加标记思想：
		1.定义一个变量，默认值为0(意味着有数据)
		2.当游标结束后，将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;

/*
	将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $

CREATE PROCEDURE pro_test12()
BEGIN
	-- 定义成绩变量
	DECLARE s_score INT;
	-- 定义标记变量
	DECLARE flag INT DEFAULT 0;
	-- 创建游标，查询所有学生成绩数据
	DECLARE stu_result CURSOR FOR SELECT score FROM student;
	-- 游标结束后，将标记变量改为1
	DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
	
	-- 开启游标
	OPEN stu_result;
	
	-- 循环使用游标
	REPEAT
		-- 使用游标，遍历结果,拿到数据
		FETCH stu_result INTO s_score;
		-- 将数据保存到stu_score表中
		INSERT INTO stu_score VALUES (NULL,s_score);
	UNTIL flag=1
	END REPEAT;
	
	-- 关闭游标
	CLOSE stu_result;
END$

DELIMITER ;

-- 调用pro_test12存储过程
CALL pro_test12();

-- 查询stu_score表
SELECT * FROM stu_score;

4、触发器

1）概念
  • 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
发器类型OLD的含义NEW的含义
INSERT 型触发器无 (因为插入前状态无数据)NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据无 (因为删除后状态无数据)
2）创建
DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;
1.准备
-- 创建账户表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),					-- 姓名
	money DOUBLE						-- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',2000);

-- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),				-- 操作类型 (insert update delete)
	operation_time DATETIME,			-- 操作时间
	operation_id INT,					-- 操作表的id
	operation_params VARCHAR(200)       -- 操作参数
);
2.INSERT触发器
-- 创建INSERT触发器
DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 向account表添加记录
INSERT INTO account VALUES (NULL,'王五',3000);

-- 查询account表
SELECT * FROM account;

-- 查询日志表
SELECT * FROM account_log;
3.UPDATE触发器
-- 创建UPDATE触发器
DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;

-- 修改account表
UPDATE account SET money=3500 WHERE id=3;

-- 查询account表
SELECT * FROM account;

-- 查询日志表
SELECT * FROM account_log;
4）DELETE触发器
-- 创建DELETE触发器
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;

-- 删除account表数据
DELETE FROM account WHERE id=3;

-- 查询account表
SELECT * FROM account;

-- 查询日志表
SELECT * FROM account_log;

5、事务

1）概念

事务一般指要做的事或者所做的事，但是这些事要么都做要么都不做

比如说 转账这件事，张三转给李四500，中间出现了错误，张三少了500，而李四却没有收到，这时候就应该恢复到初始状态

2）操作
  • 开启事务
    • start transaction
  • 回滚事务
    • rollback
  • 提交事务
    • commit
3）提交方式
  • 自动提交
    • MySQL默认
  • 手动提交
    • select @@autocommit 查看事务提交方式 0表示手动提交 1表示自动提交
  • 修改提交方式
    • set @@autocommit=数字

修改提交方式后，需要提交 sql语句才能生效

4）四大特征(ACID)
1.原子性（atomicity）

一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

2.一致性（consistency）

事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

3.隔离性（isolation）

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4.持久性（durability）

持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

5）四种隔离级别
  • 多个客户之间的事务应该是隔离的，互相独立，而不受影响
隔离级别级别字符串引发问题
读未提交read uncommitted脏读，不可重复读，幻读
读已提交read committed不可重复读，幻读
可重复读repeatable read幻读
串行化serializable无
  • 问题描述
问题现象
脏读是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功
  • 查询隔离级别
    • select @@TX_ISOLATION
  • 修改数据库隔离级别
    • set global transaction isolationlevel 级别字符串
6）总结
隔离级别名称出现脏读出现不可重复读出现幻读数据库默认隔离级别
read uncommitted读未提交是是是
read committed读已提交否是是Oracle / SQL Server
repeatable read可重复读否否是MySQL
**serializable **串行化否否否

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

6、索引

1）MySQL体系结构

22091101

  • 客户端连接
    • 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
  • 第一层：网络连接层
    • 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
    • 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。
  • 第二层：核心服务层
    • 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
    • SQL接口：接受SQL命令，并且返回查询结果。
    • 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。
    • 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
    • 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！
  • 第三层：存储引擎层
    • 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
  • 第四层：系统文件层
    • 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存
2）存储引擎

  • 引擎的概念

    • 生活中，引擎就是整个机器运行的核心，不同的引擎具备不同的功能。

  • MySQL存储引擎的概念

    • MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
    • 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
    • Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
    • 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。

  • MySQL支持的存储引擎

    • MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
    • 其中较为常用的有三种：InnoDB、MyISAM、MEMORY
3）常用引擎的特性对比
  • 常用的存储引擎
    • MyISAM存储引擎
      • 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
    • InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
      • 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。
    • MEMORY存储引擎
      • 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
  • 特性对比
特性MyISAMInnoDBMEMORY
存储限制有(平台对文件系统大小的限制)64TB有(平台的内存限制)
事务安全不支持支持不支持
锁机制表锁表锁/行锁表锁
B+Tree索引支持支持支持
哈希索引不支持不支持支持
全文索引支持支持不支持
集群索引不支持支持不支持
数据索引不支持支持支持
数据缓存不支持支持N/A
索引缓存支持支持N/A
数据可压缩支持不支持不支持
空间使用低高N/A
内存使用低高中等
批量插入速度高低高
外键不支持支持不支持
4）操作
-- 查询数据库支持的存储引擎
SHOW ENGINES;
-- 表含义:
  -- support : 指服务器是否支持该存储引擎
  -- transactions : 指存储引擎是否支持事务
  -- XA : 指存储引擎是否支持分布式事务处理
  -- Savepoints : 指存储引擎是否支持保存点

-- 查询某个数据库中所有数据表的引擎
SHOW TABLE STATUS FROM 数据库名称;

-- 查询某个数据库中某个数据表的引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';

-- 创建数据表，指定存储引擎
CREATE TABLE 表名(
	列名,数据类型,
    ...
)ENGINE = 引擎名称;

-- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;

7、索引

1）概念

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

2）分类
  • 功能分类
    • 普通索引： 最基本的索引，它没有任何限制。
    • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
    • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
    • 组合索引：顾名思义，就是将单列索引进行组合。
    • 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
    • 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
  • 结构分类
    • B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
    • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
3）操作
-- 创建索引
-- unique 唯一索引
-- fulltext 全文索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);

-- 查看索引
SHOW INDEX FROM 表名;

-- alter添加索引
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);


-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);

-- 查看student表中的索引
SHOW INDEX FROM student;

-- 删除索引
DROP INDEX 索引名称 ON 表名;
4）索引的实现原则

  • 索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。InnoDB引擎的实现的B+Tree索引。

  • B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。

1.磁盘存储
  • 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
  • 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
  • InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
  • InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
2.BTree
  • BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

22091202

  • 根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针。
  • 指针存储的是子节点所在磁盘块的地址。
  • 两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。
  • 以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找15的过程 :

1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
在磁盘块7中找到关键字15。

-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

3.B+Tree
  • B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
  • 从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
  • B+Tree相对于BTree区别：
    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个连接指针。
    • 数据记录都存放在叶子节点中。
  • 将上一节中的BTree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

22091201

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：

  • 【有范围】对于主键的范围查找和分页查找
  • 【有顺序】从根节点开始，进行随机查找

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

5）索引的设计原则
  • 对查询频次较高，且数据量比较大的表建立索引。
  • 使用唯一索引，区分度越高，使用索引的效率越高。
  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
6）联合索引
  • 最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引

ALTER TABLE user ADD INDEX index_three(name,address,phone);

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';

-- 这三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引
-- (name,address,phone)
-- (name,address)
-- (name)
-- Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。

-- 联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345';

8、锁

  • 线程

    • java中想保证数据的准确性，我们可以通过同步来实现，而同步就是上锁，只有一个线程操作完，释放锁后其他线程才能继续。

  • 事务

    • 事务的隔离性，可能会出现脏读、不可重复读、幻读的问题。可以通过修改事务的隔离级别来控制，锁的作用也可以解决掉之前的问题！

  • 锁机制

    • 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。

    在网络商城购物我们可能会出现这种问题 最后一件商品被几个人同时购买，谁能买到就是个问题 这时候就可以使用锁，让一位用户操作，其他用户等待，就解决了其中的并发问题

  • 在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

1）分类
  • 按操作分类：
    • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。
    • 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
  • 按粒度分类：
    • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
    • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
    • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
  • 按使用方式分类：
    • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
    • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
  • 不同存储引擎支持的锁
存储引擎表级锁行级锁页级锁
MyISAM支持不支持不支持
InnoDB支持支持不支持
MEMORY支持不支持不支持
BDB支持不支持支持
2）InnoDB锁
-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	age INT,
	score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95),
(NULL,'王五',25,98),(NULL,'赵六',26,97);


-- 共享锁
SELECT语句 LOCK IN SHARE MODE;

-- 窗口1
/*
	共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;

-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;

-- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;

-- 提交事务
COMMIT;

-- 排他锁
SELECT语句 FOR UPDATE;

-- 窗口1
/*
	排他锁：加锁的数据，不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 提交事务
COMMIT;


-- 窗口2
-- 开启事务
START TRANSACTION;

-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;
3）MyISAM锁
-- 数据准备
-- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price INT
)ENGINE = MYISAM;  -- 指定存储引擎为MyISAM

-- 添加数据
INSERT INTO product VALUES (NULL,'华为手机',4999),(NULL,'小米手机',2999),
(NULL,'苹果',8999),(NULL,'中兴',1999);

-- 读锁
-- 加锁
LOCK TABLE 表名 READ;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	读锁：所有连接只能读取数据，不能修改
*/
-- 为product表加入读锁
LOCK TABLE product READ;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(修改失败)
UPDATE product SET price=5999 WHERE id=1;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功)
UPDATE product SET price=5999 WHERE id=1;


-- 写锁
-- 加锁
LOCK TABLE 表名 WRITE;

-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

-- 窗口1
/*
	写锁：其他连接不能查询和修改数据
*/
-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询product表(查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;

-- 解锁
UNLOCK TABLES;

-- 窗口2
-- 查询product表(不能查询。只有窗口1解锁后才能查询成功)
SELECT * FROM product;

-- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功)
UPDATE product SET price=2999 WHERE id=2;
4）乐观锁和悲观锁

  • 悲观锁的概念

    • 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
    • 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
    • 我们之前所学的行锁，表锁不论是读写锁都是悲观锁。

  • 乐观锁的概念

    • 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
    • 但是在更新的时候会去判断在此期间数据有没有被修改。
    • 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

  • 悲观锁和乐观锁使用前提

    • 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
    • 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁
1.乐观锁的实现方式

  • 版本号

    • 给数据表中添加一个version列，每次更新后都将这个列的值加1。
    • 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
    • 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。
    -- 创建city表
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
	NAME VARCHAR(20),                   -- 城市名称
	VERSION INT                         -- 版本号
);

-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);

-- 修改北京为北京市
-- 1.查询北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京为北京市，版本号+1。并对比版本号
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

  • 时间戳

    • 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
    • 每次更新后都将最新时间插入到此列。
    • 读取数据时，将时间读取出来，在执行更新的时候，比较时间。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

9、MyCat中间件

环境

Mycat-server-1.6.7.1

VMware 15

MySQL 8

1）安装
  • 下载
  • 解压
    • tar -zxvf mycat.tar.gz
  • 授权
    • chmod -R 777 mycat
  • 配置环境
    • vi /etc/profile
    • export MYCAT_HOME=/root/mycat
    • source /etc/profile
  • 启动
    • cd /root/mycat/bin
    • ./mycat start
  • 检测端口监听状况，Mycat的端口号是8066
    • netstat -ant|grep 8066
  • Navicat连接mycat
    • 默认用户名：root
    • 默认密码：123456
    • 默认端口号：8066
2）克隆虚拟机
1.修改网卡信息

  • 可以改mac地址或者改配置文件

  • IPADDR

  • 重启网络

    • service network restart

  • 查看ip地址

    • ip addr
2.修改MySQL配置文件
  • uuid
// 编辑配置文件
vi /var/lib/mysql/auto.cnf
// 将server-uuid更改
  • 启动mySQL和MyCat
//将两台服务器的防火墙关闭
systemctl stop firewalld

//启动两台服务器的mysql
service mysqld restart

//启动两台服务器的mycat
cd /root/mycat/bin
./mycat restart

//查看监听端口
netstat -ant|grep 3306
netstat -ant|grep 8066

//使用sqlyog测试连接
3.主从复制

mysql8主从配置

vi /etc/my.cnf
主
log-bin=mysql-bin 
server-id=1

建立账号
create user repl identified with mysql_native_password by 'repl';
 
授权 
grant replication slave on *.* to repl;

刷新
flush privileges;
 
查看主库master
show master status;

vi /etc/my.cnf
从
server-id=2

change replication source to
source_host='192.168.15.100',
source_user='repl',
source_password='repl',
source_port=3306,
source_log_file='mysql-bin.000006',
source_log_pos=157;

开启主从服务
start slave;

查看是否成功、
show slave status \G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Got fatal error 1236 from master when reading data from binary log: 'Could not open log file' 报错

从机器停止slave

mysql> slave stop;

到master机器登陆mysql：

查看状态
mysql> show mster status;

刷新日志：
mysql> flush logs;

因为刷新日志file的位置会+1


马上到从机器执行

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=106;

mysql> slave start;

mysql> show slave status\G;

下面是MySQL5的操作 MySQL8操作一半无法进行

  • 主从复制的概念

    • 为了使用Mycat进行读写分离，我们先要配置MySQL数据库的主从复制。
    • 从服务器自动同步主服务器的数据，从而达到数据一致。
    • 进而，我们可以写操作时，只操作主服务器，而读操作，就可以操作从服务器了。
    • 原理：主服务器在处理数据时，生成binlog日志，通过对日志的备份，实现从服务器的数据同步。

  • 主服务器的配置

    • 在第一个服务器上，编辑mysql配置文件
    // 编辑mysql配置文件
vi /etc/my.cnf

//在[mysqld]下面加上：
log-bin=mysql-bin # 开启复制操作  8
server-id=1 # master is 1       8
innodb_flush_log_at_trx_commit=1
sync_binlog=1
    • 登录mysql，创建用户并授权
    // 登录mysql
mysql -u root -p

// 去除密码权限
SET GLOBAL validate_password_policy=0;
SET GLOBAL validate_password_length=1;

SET GLOBAL validate_password.policy=0;
SET GLOBAL validate_password.length=1;

// 创建用户
CREATE USER 'hm'@'%' IDENTIFIED BY 'itheima';

// 授权
GRANT ALL ON *.* TO 'hm'@'%';
    • 重启mysql服务，登录mysql服务
    // 重启mysql
service mysqld restart

// 登录mysql
mysql -u root -p
    • 查看主服务器的配置
    // 查看主服务器配置
show master status;

    27

  • 从服务器的配置

    • 在第二个服务器上，编辑mysql配置文件
    // 编辑mysql配置文件
vi /etc/my.cnf

// 在[mysqld]下面加上：
server-id=2
    • 登录mysql
    // 登录mysql
mysql -u root -p

// 执行 mysql 5
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;
    • 重启mysql，重新登录，配置从节点
    // 重启mysql
service mysqld restart

// 重新登录mysql
mysql -u root -p

// 执行 mysql 5
change master to master_host='192.168.15.100',master_port=3306,master_user='cool',master_password='codeui',master_log_file='mysql-bin.000001',master_log_pos=4642;
//mysql 8
change replication source to
source_host='127.0.0.1',
source_user='repl',
source_password='repl',
source_port=3300,
source_log_file='binlog.000003',
source_log_pos=827;
    • 重启mysql，重新登录，开启从节点
    // 重启mysql
service mysqld restart

// 重新登录mysql
mysql -u root -p

// 开启从节点
start slave;

// 查询结果
show slave status\G;
//Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。

    28

  • 测试

    • sqlyog连接主服务器
    -- 主服务器创建db1数据库,从服务器会自动同步
CREATE DATABASE db1;
    • sqlyog连接从服务器
    -- 从服务器创建db2数据库,主服务器不会自动同步
CREATE DATABASE db2;

  • 启动失败的解决方案

启动失败：Slave_IO_Running为 NO 
方法一:重置slave
slave stop;
reset slave;
start slave ;
方法二:重设同步日志文件及读取位置
slave stop;
change master to master_log_file=’mysql-bin.000001’, master_log_pos=1;
start slave ;
4.读写分离

  • 读写分离的概念

    • 写操作只写入主服务器，读操作读取从服务器。

  • 在主服务器上修改server.xml

    • user标签主要用于定义登录mycat的用户和权限。如上面定义用户名mycat和密码123456，该用户可以访问的schema的HEIMADB逻辑库。
<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">HEIMADB</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
</user>
  • 在主服务器上修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
	
	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- 主服务器进行写操作 -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="itheima">
		<!-- 从服务器负责读操作 -->
		<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
		</writeHost>
	</dataHost>
	
</mycat:schema>

  • 配置详解

    • schema标签逻辑库的概念和mysql数据库中Datebase的概念相同，我们在查询这两个逻辑库中的表的时候，需要切换到该逻辑库下才可以查到所需要的表。

    • dataNode属性：该属性用于绑定逻辑库到某个具体的database上。

    • dataNode标签： dataNode标签定义了mycat中的数据节点，也就是数据分片。一个dataNode标签就是一个独立的数据分片。

    • name属性：定义数据节点的名字，这个名字需要是唯一的，我们需要在table标签上应用这个名字，来建立表与分片对应的关系。

    • dataHost属性：该属性用于定义该分片属于那个数据库实例，属性值是引用datahost标签定义的name属性。

    • database属性：该属性用于定义该分片属于那个具体数据库实例上的具体库，因为这里使用两个纬度来定义分片，就是：实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。

    • dataHost标签：该标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句。

    • balance属性： 负载均衡类型
      ​ balance=0: 不开启读写分离，所有读操作都发送到当前可用的writeHost上。
      ​ balance=1: 全部的readHost与Stand by writeHost都参与select语句的负载均衡
      ​ balance=2: 所有的读操作都随机在writeHost，readHost上分发。
      ​ balance=3: 所有的读请求都随机分配到writeHost对应的readHost上执行，writeHost不负担读压力。

    • switchType属性：
      ​ -1：表示不自动切换。
      ​ 1 ：默认值，表示自动切换
      ​ 2：表示基于MySQL主从同步状态决定是否切换，心跳语句： show slave status.
      ​ 3:表示基于mysql galary cluster的切换机制，适合mycat1.4之上的版本，心跳语句show status like "%esrep%";

    • writeHost标签，readHost标签：这两个标签指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是，writeHost指定写实例、readHost指定读实例，组合这些读写实例来满足系统的要求。

      • host属性：用于标识不同的实例，对于writehost，一般使用M1；对于readhost一般使用S1.
      • url属性：后端实例连接地址，如果使用native的dbDriver，则一般为address:port这种形式，用JDBC或其他的dbDriver，则需要特殊指定。当使用JDBC时则可以这么写：jdbc:mysql://localhost:3306/。
      • user属性：后端存储实例的用户名。
      • password属性：后端存储实例的密码

  • 测试

    • 重启主服务器的mycat
    // 重启mycat
cd /root/mycat/bin

./mycat restart

// 查看端口监听
netstat -ant|grep 8066
    • sqlyog连接mycat
    -- 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 查询学生表
SELECT * FROM student;

-- 添加两条记录
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');

-- 停止主从复制后，添加的数据只会保存到主服务器上。
INSERT INTO student VALUES (NULL,'王五');
    • sqlyog连接主服务器
    -- 主服务器：查询学生表，可以看到数据
SELECT * FROM student;
    • sqlyog连接从服务器
    -- 从服务器：查询学生表，可以看到数据(因为有主从复制)
SELECT * FROM student;

-- 从服务器：删除一条记录。(主服务器并没有删除，mycat中间件查询的结果是从服务器的数据)
DELETE FROM student WHERE id=2;
5.分库分表

  • 分库分表的概念

    • 将庞大的数据进行拆分
    • 水平拆分：根据表的数据逻辑关系，将同一表中的数据按照某种条件，拆分到多台数据库服务器上，也叫做横向拆分。例如：一张1000万的大表，按照一模一样的结构，拆分成4个250万的小表，分别保存到4个数据库中。
    • 垂直拆分：根据业务的维度，将不同的表切分到不同的数据库之上，也叫做纵向拆分。例如：所有的订单都保存到订单库中，所有的用户都保存到用户库中，同类型的表保存在同一库，不同的表分散在不同的库中。

  • Mycat水平拆分

    • 修改主服务器的server.xml

      • 0：本地文件方式

        在mycat/conf/sequence_conf.properties文件中：
        GLOBAL.MINDI=10000最小值
        GLOBAL.MAXID=20000最大值，建议修改到9999999999

      • 1：数据库方式

        分库分表中保证全局主键自增唯一，但是需要执行mycat函数，配置sequence_db_conf.properties

      • 2：时间戳方式

        mycat实现的时间戳，建议varchar类型，要注意id的长度

    <!-- 修改主键的方式 -->
<property name="sequnceHandlerType">0</property>
    • 修改主服务器的sequence_conf.properties
    #default global sequence
GLOBAL.HISIDS=      # 可以自定义关键字
GLOBAL.MINID=10001  # 最小值
GLOBAL.MAXID=20000  # 最大值
GLOBAL.CURID=10000
    • 修改主服务器的schema.xml
      • table标签定义了逻辑表，所有需要拆分的表都需要在这个标签中定义。
      • rule属性：拆分规则。mod-long是拆分规则之一，主键根据服务器数量取模，在rule.xml中指定。如果是3个数据库，那么数据取模后，平均分配到三个库中。
      • name属性：定义逻辑表的表名，这个名字就如同在数据库中执行create table命令指定的名字一样，同一个schema标签中定义的表名必须是唯一的。
      • dataNode属性： 定义这个逻辑表所属的dataNode，该属性的值需要和dataNode标签中name属性的值相互对应。
    <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- write -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="itheima">
		<!-- read -->
		<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
		</writeHost>
	</dataHost>

</mycat:schema>
    • 修改主服务器的rule.xml
    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- 数据库的数量 -->
		<property name="count">3</property>
</function>

    • 测试

      • mycat操作
      -- 创建product表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price INT
);

-- 添加6条数据
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'苹果手机',6999);
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'华为手机',5999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'三星手机',4999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'小米手机',3999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'中兴手机',2999); 
INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'OOPO手机',1999); 

-- 查询product表
SELECT * FROM product;
      • 主服务器操作
      -- 在不同数据库中查询product表
SELECT * FROM product;
      • 从服务器操作
      -- 在不同数据库中查询product表
SELECT * FROM product;

  • Mycat垂直拆分

    • 修改主服务器的schema
    <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100">
		<table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/>

		<!-- 动物类数据表 -->
		<table name="dog" primaryKey="id" autoIncrement="true" dataNode="dn4" />
		<table name="cat" primaryKey="id" autoIncrement="true" dataNode="dn4" />

       <!-- 水果类数据表 -->
		<table name="apple" primaryKey="id" autoIncrement="true" dataNode="dn5" />
		<table name="banana" primaryKey="id" autoIncrement="true" dataNode="dn5" />
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />

	<dataNode name="dn4" dataHost="localhost1" database="db4" />
	<dataNode name="dn5" dataHost="localhost1" database="db5" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- write -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="itheima">
		<!-- read -->
		<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="itheima" />
		</writeHost>
	</dataHost>

</mycat:schema>

    • 测试

      • sqlyog连接mycat
      -- 创建dog表
CREATE TABLE dog(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO dog(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'哈士奇');
-- 查询dog表
SELECT * FROM dog;


-- 创建cat表
CREATE TABLE cat(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO cat(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'波斯猫');
-- 查询cat表
SELECT * FROM cat;



-- 创建apple表
CREATE TABLE apple(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO apple(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'红富士');
-- 查询apple表
SELECT * FROM apple;


-- 创建banana表
CREATE TABLE banana(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO banana(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'香蕉');
-- 查询banana表
SELECT * FROM banana;
      • sqlyog连接主服务器
      -- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;


-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;
      • sqlyog连接从服务器
      -- 查询dog表
SELECT * FROM dog;
-- 查询cat表
SELECT * FROM cat;


-- 查询apple表
SELECT * FROM apple;
-- 查询banana表
SELECT * FROM banana;

文章目录

最新评论

最新文章