mysql进阶

Mysql

视图

什么是视图？

​ 视图是基于查询的虚拟表。通俗的理解，视图就是一条 SELECT 语句执行后返回的结果集。

​ SELECT 语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。

​ 使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句封装在视图内部，然后对视图进行查询，从而简化复杂的查询语句。

创建视图

#CREATE VIEW 视图名 AS SELECT 列 1，列 2... FROM 表(查询语句);
#例
create view admin_view AS select * from admin;

使用试图

#SELECT * FROM 视图名
#例
select * from admin_view;

删除视图

#drop view 视图名
#例
drop view admin_view;

存储过程

概述

​ 如果实现用户的某些需求时，需要编写一组复杂的 SQL 语句才能实现，那么可以将这组复杂的 SQL 语句集事先编写在数据库中，由 JDBC 调用来执行这组 SQL语句。把编写在数据库中的 SQL 语句集称为存储过程。

​ 存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

​ 存储过程类似于 JAVA 语言中的方法，需要先定义，使用时需要调用。存储 过程可以定义参数，参数分为 IN、OUT、INOUT 类型三种类型：

• IN 类型的参 数表示接收调用者传入的数据；
• OUT 类型的参数表示向调用者返回数据；
• INOUT 类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。

MySQL存储过程的定义

创建存储过程的语法格式

create procedure 存储过程名（in 变量名 类型，out 参数 2，…）
  begin
    declare 变量名 类型 DEFAULT 值;
    存储过程语句块;
  end$$

• 用 set 变量=值;

存储过程使用

定义一个存储过程

create procedure test()
begin
    declare var_num int default 0;
    set var_num = 1;
    select var_num;
end$$
-- 使用
call test();

定义一个有参数的存储过程

create procedure test1(out isExist int,in oldpwd varchar(16))
begin
	DECLARE cpwd VARCHAR(16) DEFAULT '';
	-- 把 sql 中查询的结果赋给变量cpwd
	SELECT PASSWORD INTO cpwd FROM qqacc WHERE id = 11;
	IF oldpwd=cpwd THEN
		SET isExist = 1;
	ELSE
		SET isExist = 0;
	END IF;
	SELECT isExist;
end$$
-- 使用
call test1(@isExist,'abcdef');

流程控制语句

case when

CREATE PROCEDURE test2(IN p_day INT)
BEGIN
	CASE WHEN p_day = 0 THEN
		SELECT "星期天";
	ELSE
		SELECT "星期一";
	END CASE;
END$$

循环

CREATE PROCEDURE test3()
BEGIN
	DECLARE v_num INT DEFAULT 0; 
	-- 循环开始
	addnum:LOOP
		SET v_num = v_num+1; -- 循环语句
		-- 循环结束条件
	IF v_num = 10 THEN
		LEAVE addnum;
	END IF;
	END LOOP; -- 循环结束
	SELECT v_num;
END$$

函数

函数语法

• 函数体：肯定会有 return 语句，如果没有会报错；
• 函数体中仅有一句话，则可以省略 begin end；
• 设置函数可以没有参数：SET GLOBAL log_bin_trust_function_creators=TRUE;
• 删除函数：DROP FUNCTION 函数名;
• 我们定义的函数都是单行函数。

create function 函数名([参数列表]) returns 数据类型
begin
	DECLARE 变量；
		sql 语句;
	return 值;
end;

不带参数

CREATE FUNCTION test() RETURNS INT
BEGIN
	DECLARE v_num INT;
	SELECT COUNT(*) INTO v_num FROM t_user;
	RETURN v_num;
END$$
-- 例
select test(); -- 相当于select count(*) from t_user;

带参数

-- 通过id查询账号
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10)
BEGIN
	DECLARE v_name VARCHAR(10);
	SELECT NAME INTO v_name FROM dept WHERE id = p_id;
	RETURN v_name;
END$$
-- 调用：
SELECT account,findDeptNameById(dept_id) FROM user

触发器

​ 触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。

触发器的特点

• 与表相关联

  触发器定义在特定的表上，这个表称为触发器表。

• 自动激活触发器

  当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

• 不能直接调用

  与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

• 作为事务的一部分

  触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

定义触发器的语法规则

CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW -- 行级触发
BEGIN
	语句
END;

语法解析

1. 触发器名称：是用来标识触发器的，由用户自定义。
2. 触发时机：其值是 before 或 after。
3. 触发事件：其值是 insert，update 和 delete
4. 表名称：标识建立触发器的表名，即在哪张表上建立触发器

案例：

​ 删除管理员时,自动触发删除管理员角色关系

CREATE
    TRIGGER `newsdb`.`deleteAdmin_deleteAdmin_role` BEFORE DELETE
    ON `newsdb`.`admin`
    FOR EACH ROW BEGIN
	DELETE FROM admin_role WHERE adminid = old.id;
    END$$
-- old表示你要操作的那一行的数据
-- 删除一条admin数据前，自动删除与admin表关联的admin_role表中的关联数据
-- 要删主键先删外键
delete from admin where id = 20;

​ 新增用户时，自动向其他表插入数据

CREATE
    TRIGGER `newsdb`.`save_user_log` AFTER INSERT
    ON `newsdb`.`user`
    FOR EACH ROW BEGIN
		INSERT INTO test(id,NAME)VALUES(new.id,new.account);
    END$$
-- new表示新增的那条数据
-- 向user表插入数据后，向test表插入数据
INSERT INTO user(account)VALUES('jim')

​ 在行级触发器代码中，可以使用 old 和 new 访问到该行的旧数据和新数据,old和 new 是对应表的行记录类型变量。

MySQL架构

连接层

​ 最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。

服务层

​ 接收sql，对sql语句进行解析、优化，缓存数据。

引擎层

​ 数据存储引擎是MySQL数据库的核心部分，负责存储和管理数据库中的数据，有多种不同的数据存储引擎可供选择。

物理文件存储层

​ 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，使用各种文件存储数据，以及各种日志文件，并完成与存储引擎的交互。

存储引擎

概述

​ MySQL引擎是MySQL数据库里的一个组件，它负责处理数据库的存储和检索。MySQL引擎是指数据库存储引擎，也叫存储引擎，它定义了如何存储、索引和读取数据，它负责处理所有的数据库查询。MySQL支持多种数据库引擎。

查看支持的引擎

SHOW ENGINES; 

查看表引擎

SHOW TABLE STATUS LIKE '表名' 

修改引擎方式：

1. 将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
2. 建表时指定 CREATE TABLE 表名(…)ENGINE=MYISAM;
3. 建表后修改 ALTER TABLE 表名 ENGINE = INNODB;

​ MySQL支持多种表引擎，主要有：MyISAM，InnoDB，MEMORY，CSV，ARCHIVE，BLACKHOLE，FEDERATED，MERGE，EXAMPLE，NDB Cluster。其中最常用的是MyISAM和InnoDB。MyISAM引擎是默认的存储引擎，它的优点是支持更快的查询和索引速度，但是它不支持事务处理和外键。InnoDB引擎支持事务处理和外键，但是它的查询和索引速度比MyISAM引擎慢。

SHOW ENGINES；

1. MyISAM：MyISAM 是 MySQL 最常使用的引擎，它拥有高性能，空间高效，可伸缩性强，而且有很强的可移植性，其中包括可以支持全文索引，支持表锁（在进行dml操作时会锁住整张表），但是不支持事务和行级锁定，不支持外键，不支持回滚，不支持自动恢复，可靠性较低，不适用于高并发的场合，主要用于查询多，增删改少的场景。

2. InnoDB：InnoDB 是 MySQL 支持事务处理的引擎，它支持行级锁定，可以提供 ACID 的事务处理能力，支持外键，支持全文索引，支持备份和恢复，有很多的安全特性，它的设计目标是处理大容量数据库系统,MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引，但是比 MyISAM 的空间效率低，查询性能相对 MyISAM 也有所损失，也不会存储表的总行数。

   对比项	MyISAM	InnoDB
   主外键	不支持	支持
   事务	不支持	支持
   行表锁	支持表锁，即使操作一条记录也会锁住整个表，不适合高并发操作	行级锁，操作时只锁某一行，不对其它行有影响，适合高并发操作
   缓存	只缓存索引，不缓存数据	不仅缓存索引也缓存真实数据，对内存要求高，而且内存大小对性能有决定性的影响
   表空间	小（只支持4GB的表）	大
   关注点	性能	事务
   备份和恢复	不支持	支持

3. MEMORY：MEMORY 引擎也叫做 HEAP，是一种暂时存储数据的引擎，它把数据存储在内存中，具有非常高的性能，但是并不支持事务，索引也不能直接存储在内存中，所以使用 MEMORY 引擎时需要特别注意数据的安全性。

4. CSV：CSV 引擎是一种特殊的文件存储引擎，它把数据存储在以逗号分隔的文本文件中，可以用于将 MySQL 数据导出，也可用于将数据从文件导入到 MySQL 中，但是 CSV 引擎不支持索引，也不支持事务处理。

5. ARCHIVE：ARCHIVE 引擎是一种特殊的存储引擎，它可以把数据以压缩的形式存储到磁盘上，而且 ARCHIVE 引擎不支持索引，也不支持事务处理，它的主要用途是对历史数据进行备份和存储。

6. BLACKHOLE：BLACKHOLE 引擎是一种特殊的存储引擎，它不会把数据存储到磁盘上，任何数据插入到 BLACKHOLE 引擎中都会被直接丢弃，它的主要用途是拆分主从服务器，把从服务器收到的数据丢弃，而不是真正的存储到磁盘上。

7. FEDERATED：FEDERATED 引擎是一种特殊的存储引擎，它可以让你在一个 MySQL 服务器上查询另外一个不同的 MySQL 服务器上的数据，它可以用于实现分布式的数据库系统，但是不支持索引和事务处理。

8. MERGE：MERGE 引擎是一种特殊的存储引擎，它可以将多个 MyISAM 表合并成一个表，可以把多个 MyISAM 表当作一个表来进行查询，但是不支持索引和事务处理。

9. EXAMPLE：EXAMPLE 引擎是一种特殊的存储引擎，它只是一个示例，主要用于演示 MySQL 如何开发存储引擎，它不支持事务和索引，也不能真正的存储数据。

10. NDB Cluster：NDB Cluster 是 MySQL 提供的一种高可用性的分布式存储引擎，它可以实现数据的自动备份，支持事务处理和索引，并且可以将数据存储到多个不同的服务器上，这样可以提高数据的可用性。

索引

为什么使用索引？

​ 假设有一张表，表中有 100 万条数据，这 100 万条数据在硬盘上是存储在数据页上的，一页数据大小为 16K，存储 100 万条数据需要很多数据页，假设其中有一条数据是 id=500000，如果要查询这条数据，其 SQL 是 SELECT * FROM 表名称 WHERE id = 500000。mysql需要扫描全表来查找 id=500000 的记录。全表扫描就是从“数据页 1”开始，向后逐页查询。对于少量的数据，查询 的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐页查询的时间是无法被用户接受的,所以就有了索引。

索引原理

​ 索引的存在是为了提高查询效率，类似于书的目录，我们可以通过查看目录快速定位我们需要看的内容的页数，还有查字典、查车次、航班。本质是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据，是排好序的快速查找的数据结构。

索引的优点

1. 提升查询性能：通过索引，可以快速找到记录，而不必检查每一条记录，从而提升查询的性能。
2. 更新更快：在更新表中的记录时，更新索引也会更快，因为更新过程中，对索引的修改会更少。
3. 减少磁盘I/O：索引会减少数据库表中需要读取的数据量，从而减少磁盘I/O操作，提高查询性能。
4. 提供高级查询：使用索引可以提供高级的查询功能，如范围查询、排序查询等，可以更有效地查询数据库表中的数据。

索引的缺点

1. 索引会占用大量的存储空间，因为它们需要存储数据库中的数据项。
2. 虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT,UPDATE 和 DELETE,因为更新表时,MySQL 不仅要保存数据,还要保存 一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变 化后的索引信息。

索引分类

主键索引(设定为主键后数据库会自动建立索引)

-- 设置主键索引：
 ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
-- 删除建主键索引：
 ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引(即一个索引只包含单个列，一个表可以有多个单列索引 )

-- 创建单值索引
CREATE INDEX 索引名 ON 表名(列名);
-- 删除索引：
DROP INDEX 索引名;

唯一索引（索引列的值必须唯一，允许为 null）

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
-- 删除索引
DROP INDEX 索引名 ON 表名;

组合索引(复合索引)

​ 即一个索引包含多个列,在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引.

-- 创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...);
-- 删除索引：
DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则

​ 即组合索引的第一个字段必须是最左边的字段，第二个字段是次左边的字段，依次类推。最左前缀原则的实质是指，索引列只能是查找条件中的最左侧的列，而其余列可以不出现在查找条件中，这样索引才可以生效。

假设表中有a,b,c三列，为a,b两列创建组合索引

select * from table where a=’’and b=’’;-- 索引生效
select * from table where b=’’and a=’’;-- 索引生效
select * from table where a=’’and c=’’;-- 索引生效
select * from table where b=’’and c=’’;-- 索引不生效

全文索引(FULLTEXT)

​ 当我们需要模糊查询时，一般索引就会无效，这时候就可以使用全文索引了。

-- 创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
-- 查询
SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

索引创建原则

哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引(where 后面的语句)
• 查询中与其它表关联的字段，外键关系建立索引
• 查询中排序的字段(ORDER BY)，排序字段若通过索引去访问将大大提高排序速度
• 在GROUP BY子句中使用的列

哪些情况不要创建索引

• 表记录太少
• Where 条件里用不到的字段不创建索引
• 经常增删改的表(提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件)
• 数据重复且分布平均的表字段，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引数据结构

​ B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。二叉查找树、平衡二叉树在数据库中高度较高，查询时I/O次数多，影响查询效率，所以直接pass掉，B树存储的数据遍布B树的每个节点，这意味着搜索时需要搜索多个节点，这会影响检索数据的效率，而B+树它可以将所有的键值对存储在叶子节点中，而不是在内部节点中，这样可以更有效地检索数据，因为只需要搜索一次叶子节点就可以获得所有的键值对，但是B+树中存在着冗余的索引。

​ Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

聚簇索引和非聚簇索引

聚簇索引

​ 找到了索引就找到了需要的数据，那么这个索引就是聚簇索引。

非聚簇索引

​ 索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

举例：

​ 下面我们创建了一个学生表，做三种查询，来说明什么情况下是聚簇索引，什么情况下不是。

CREATE TABLE student (
	id BIGINT, 
    NO VARCHAR (20), 
    NAME VARCHAR (20), 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `idx_no` (`no`)
)

​ 第一种，直接根据主键查询获取所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了 id=1 的所有字段的值。

SELECT * FROM student WHERE id = 1

​ 第二种，根据编号查询编号和名称，编号本身是一个唯一索引，但查询的列包含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主键 ID，需要根据主键 ID 重新查询一次，所以这种查询下 no 不是聚簇索引；

SELECT NO,NAME FROM student WHERE NO = 123

​ 第三种，我们根据编号查询编号(验证该编号在数据库中是否存在)，这种查询找到编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询，这种场景下 no 是聚簇索引。

SELECT NO FROM student WHERE NO = 123

​ MySQL 中 InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计.

​ 而 MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。

​ .ibd文件是InnoDB存储引擎生成的文件，而.MYD、.MYI、.sdi是MyISAM存储引擎生成的文件

事务

概述

​ 事务是数据库为了保证我们数据操作的（ACID）原子性、隔离性、持久性、一致性，数据库就提供一套机制，在同一事务中，如果需要执行多条sql，事务保证执行的可靠性。

​ 数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

​ 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。 事务用来管理 insert,update,delete 语句。

事务特性

原子性

​ 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态（要用到日志文件），就像这个事务从来没有执行过一样。

持久性

​ 一旦我们的事务提交，我们的数据就会持久保存在硬盘上，即使系统故障数据也不会丢失。

隔离性

​ 有好多个事务同时对数据库进行操作我们需要不同的隔离级别来进行控制，防止数据不完整

​ 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

一致性

​ 数据被操作后满足预设的结果

​ 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则。比如不能出现转了 100，对方收到了 50 的情况。前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。

事务设置

​ 默认情况下, MySQL 启用自动提交模式（变量 autocommit 为 ON）。这意味着, 只要你执行 DML 操作的语句,MySQL 会立即隐式提交事务。

​ 由于变量 autocommit 分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

MYSQL 事务处理主要有两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT 来实现

   BEGIN;-- 开启一个事务
   rollback;-- 事务回滚
   commit;-- 事务提交
   

2. 直接用 SET 来改变 MySQL 的自动提交模式:

   SET GLOBAL autocommit=0; -- 禁止自动提交
   SET GLOBAL autocommit=1; -- 开启自动提交
   

查看 autocommit 模式

SHOW GLOBAL VARIABLES LIKE 'autocommit';

事务隔离级别

为什么要有隔离级别？

​ MySQL 是一个服务器／客户端架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话。我们可以同时在不同的会话里输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。我们前边 说过事务有一个特性称之为隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，所以设计数据库的大叔提出了各种隔离级别，来最大限度的提升系统并发处理事务的能力。

查看隔离级别

--       全局隔离级别                        会话隔离级别
SELECT @@global.transaction_isolation,@@transaction_isolation;

​ Mysql 数据提供四种不同级别的隔离级别,实际开发中可以根据不同的需要场景选择不同的隔离级别,除了串行级别以外其他级别都会存在某种问题.

读未提交交(read uncommitted)：一个事务可以读取到另一个事务未提交的修改。这会带来脏读，幻读，不可重复读问题。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置全局隔离级别为读未提交
set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置会话隔离级别为读未提交

读已提交(read committed)：一个事务只能读取另一个事务已经提交的修改。其避免了脏读，仍然存在不可以重复读（同一事务中多次查询的结果不同）和幻读问题。

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置全局隔离级别为读已提交
set session TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置会话隔离级别为读已提交

可重复读(repeatable read ,MySQL 默认隔离级别):同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但是除InnoDB 外幻读依然存在。

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE-READ;-- 设置全局隔离级别为可重复读
SET session TRANSACTION ISOLATION LEVEL REPEATABLE-READ;-- 设置会话隔离级别为可重复读

串行化(serializable)：事务串行执行,即使只有一个事务进行操作，其他事务就得等待，即使其他事务进行读操作也不行，是最安全的隔离级别，避免了以上所有问题，但效率也是最低的。

SET GLOBAL TRANSACTION ISOLATION LEVEL serializable;-- 设置全局隔离级别为串行化
SET session TRANSACTION ISOLATION LEVEL serializable;-- 设置会话隔离级别为串行化

事务实现原理

原子性实现原理

​ 实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 sql 语句。InnoDB 实现回滚，靠的是 undo log：当事务对数据库进行修改时，InnoDB会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要 回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

​ undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时，InnoDB 会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个 delete，回滚时会执行 insert；对于每个 update，回滚时会执行一个相反的 update，把数据改回去。

持久性实现原理

​ mysql持久性的实现是通过redo log日志实现的，redo log 叫做重做日志，是保证事务持久性的重要机制。当 mysql 服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。

​ innodb 是以页为单位来管理存储空间的，任何的增删改查操作最终都会操作完整的一个页，会将整个页加载到 buffer pool 中，然后对需要修改的记录进行修改，修改完毕不会立即刷新到磁盘，而且仅仅修改了一条记录，刷新一个完整的数据页的话过于浪费了。但是如果不立即刷新的话，数据此时还在内存中， 如果此时发生系统崩溃最终数据会丢失的，因此权衡利弊，引入了 redo log， 也就是说，修改完后，不立即刷新，而是记录一条日志，日志内容就是记录哪个页面，多少偏移量，什么数据发生了什么变更。这样即使系统崩溃，再恢复后，也可以根据 redo 日志进行数据恢复。另外，redo log 是循环写入固定的文件，是顺序写入磁盘的。

隔离级别实现原理（MVCC）

MVCC是什么？

​ MVCC（多版本并发控制 Multi-Version Concurrent Control），是MySQL 提高性能的一种方式，配合 Undo log 和版本链,让不同事务的读-写、写-读操作可以并发执行，从而提升系统性能。

​ MVCC 使得数据库读不会对数据加锁，普通的 SELECT 请求不会加锁，提 高 了 数 据 库 的 并 发 处 理 能 力 ,借 助 MVCC ， 主要针对读已提交和可重复读两种隔离级别。

1. 在可重复读隔离级别时：select * from stu where id = 1088 第一次查询结果为张三，第二次查询结果也为张三
2. 在读已提交隔离级别时：select * from stu where id = 1088 第一次查询结果为张三，第二次查询结果也为张小三

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的事务 ID（TRX_ID），一个保存了行的回滚指针（ROLL_PT）。

• trx_id：每次对某记录进行改动时，都会把对应的事务 id 赋值给 trx_id隐藏列。

• roll_pt：每次对记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

​ 对该记录每次更新后，都会将旧值放到一条 undolog 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pt 属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务 id，这个信息很重要。

ReadView 是什么

​ ReadView 是“快照读”，是SQL 执行时 MVCC 提取数据的依据.

ReadView 是一个数据结构，包含 4 个字段

• m_ids：当前活跃的事务编号集合

• min_trx_id：最小活跃事务编号

• max_trx_id：预分配事务编号，当前最大事务编号+1

• creator_trx_id:ReadView 创建者的事务编号

读已提交: 称为当前读,当每个事物每次读取时,会生成一个 readVew,读取的是最新数据.

只要别的事务提交了,那么另一个事务就可以看到.

可重复读: 称为快照读,当一个事务第一次查询时,会生成一个 readView,第二次查询时仍会从当前 readView 中读数据.例如：B事务开始后,第一次读到的数据和之后读到的数据一致,这个过程中别的事务已经修改过了.第一次读的时候,会把版本链拍照,下次读时,从版本快照中读,所有第一次和第二次读到数据是一致的.

锁机制

​ 首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，

InnoDB 通过锁机制来保证这一点。

​ 锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁，间歇锁，表锁

​ 按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的间隙锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并 发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。

行锁

​ 行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行 加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

特点： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

间歇锁

​ 锁的是一个区间,当我们用范围条件而不是相等条件检索数据，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙",InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。例如id>5 and id<10,5-10这个范围内的几条数据都会被锁住。

表锁

​ 表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的MyISAM 与 innoDB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。

共享锁（S）：又称为读锁, 如果我们事物在读数据时,不想让其他事物写,还要让其他事物可以读, 那么查询语句可以加共享锁 select … lock in share mode.

排他锁（Ｘ）：就是独占锁., insert、update、delete操作默认加排他锁, 当我们读数据时,要求数据足够准确,可以给读操作加排他锁,select …for update

sql优化

为什么要进行sql优化

​ 项目上线初期，由于业务数据量相对较少，一些 SQL 的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断 SQL 对程序的运行效率 有多大，故很少针对 SQL 进行专门的优化，而随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化就很有必要。

SQL 优化的一些方法

1.查询 SQL 尽量不要使用 select *，而是具体字段

节省资源、减少内存开销。

可能用到覆盖索引（聚簇索引），减少回表，提高查询效率。

2.避免在 where 子句中使用 or 来连接条件

反例:SELECT * FROM user WHERE id=1 OR salary=5000

正例:使用 union all 把两个两个 SQL 结果合并

使用 or 可能会使索引失效，从而全表扫描；

对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得进行全表扫描；

3. 尽量使用数值替代字符串类型

正例 主键（id）：primary key 优先使用数值类型 int

性别（sex）：0 代表女，1 代表男；数据库没有布尔类型，mysql 推荐 使用 tinyint

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符； 而对于数字型而言只需要比较一次就够了，字符会降低查询和连接的性能，并会增加存储开销。

4. 使用 varchar 代替 char

• varchar 变长字段按数据内容实际长度存储，可以节省存储空间；
• char 按声明大小存储，不足补空格；
• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

5. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by

涉及的列上建立索引

6. 应尽量避免索引失效

1. 在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

   如：select id from t where num is null, 可以在 num 上设置默认值 0，确保表中 num 列没有 null 值， 然后这样查询：select id from t where num=0

2. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如:select id from t where num=10 or num=20

3. in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where numbetween 1 and 3

4. 模糊查询也将导致全表扫描，我们可以使用全文索引代替

   -- 创建全文索引
   CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
   -- 查询
   SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')
   

5. 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

   如: select id from t where substring(name,1,3)=‘abc’

7. inner join 、left join、right join，优先使用 inner join

​ 三种连接如果结果相同，优先使用 inner join

• inner join 内连接，只保留两张表中完全匹配的结果集；
• left join 会返回左表所有的行，即使在右表中没有匹配的记录；
• right join 会返回右表所有的行，即使在左表中没有匹配的记录；

8. 提高 group by 语句的效率

反例:先分组，再过滤

正例:先过滤，后分组

9. 清空表时优先使用 truncate

​ truncate table 比 delete 速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据,删除后主键自增的计数器也会还原。

10. 表连接不宜太多，索引不宜太多，一般 5 个以内

​ 联的表个数越多，编译的时间和开销也就越大，每次关联内存中都生成一个临时表，应该把连接表拆开成较小的几个执行，可读性更高。

11. 避免在索引列上使用内置函数

​ 使用索引列上内置函数，会导致索引失效。

12.使用explain分析你的sql执行计划

执行计划

EXPLAIN概述

​ 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

​ 在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息，而不是执行 SQL。

EXPLAIN作用

使用Explain你可以知道你的sql语句的一些信息

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

测试期间设置关闭对衍生表的合并优化,不关闭时,mysql 对简单查询进行优化, 不显示:

SET SESSION optimizer_switch='derived_merge=off';

EXPLAIN SELECT * FROM USER WHERE id = 1

expain 出来的信息有 12 列，分别是：

1. id:选择标识符
2. select_type:表示查询的类型。
3. table:输出结果集的表
4. partitions:匹配的分区
5. type:表示表的连接类型
6. possible_keys:表示查询时，可能使用的索引
7. key:表示实际使用的索引
8. key_len:索引字段的长度
9. ref:列与索引的比较
10. rows:扫描出的行数(估算的行数)
11. filtered:按表条件过滤的行百分比
12. Extra:执行情况的描述和说明

id

SELECT 识别符。这是 SELECT 的查询序列号

id 如果相同，可以认为是一组，从上往下顺序执行；

EXPLAIN SELECT * FROM employee e,dept d WHERE e.deptId = d.id

在所有组中，id 值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM employee e WHERE e.deptId = (SELECT id FROM dept d WHERE d.id = 1)

select_type

表示查询中每个 select 子句的类型

类型包括：

1. SIMPLE(简单 SELECT，不使用 UNION 或子查询等)
2. .PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select 被标记为 PRIMARY)
3. SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)
4. DERIVED(派生表的 SELECT, FROM 子句的子查询)
5. UNION(UNION 中的第二个或后面的 SELECT 语句)
6. UNION RESULT(UNION 的结果，union 语句中第二个 select 开始后面所有select)

type

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，性能逐渐降低）

• system:表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不计.
• const:表示通过索引一次就找到了，const 用于比较 primary key 或者 unique索引
• eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描.
• ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体.
• range:只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• index:Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）
• All: Full Table Scan，将遍历全表以找到匹配的行一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

possible_keys

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为 NULL，则没有使用索引,或者索引失效.

ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好.

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

EXPLAIN SELECT * FROM employee e,dept d,admin a WHERE e.deptId = d.id AND e.adminId=a.id
AND e.age=20 -- 使用了deptId adminId id age

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

额外的信息说明

• Using filesort: 当 Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候,Mysql 无法利用索引完成排序的操作称为”文件排序”.
• Using temporary: 使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
• Using index: 表示相应的 select 操作中使用了索引，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引用来读取数据而非执行查找动作。
  ll 是从硬盘中读的）