MySQL高级--你将会对MySQL有新的认识

序言

在简单的学习MySQL的增删改查语句后，对视图，存储过程，索引等的学习，能让我们的数据库更加高效。还有MySQL引擎特性，SQL优化等等，你将会对MySQL有新的认识。

视图

什么是视图

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

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

为什么使用视图

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

定义视图

CREATE VIEW 视图名 AS SELECT 列 1，列 2... FROM 表(查询语句); 

-- 创建视图
CREATE VIEW sel_news AS 
	SELECT
	  n.id,
	  n.title,
	  t.name
	FROM
	  news n
	  LEFT JOIN TYPE t
	    ON n.typeid = t.id

使用视图

SELECT * FROM 视图名

-- 使用视图   
SELECT * FROM sel_news;

删除视图

drop view 视图名

-- 删除视图
DROP VIEW sel_news;

存储过程

概述

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

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

存储过程类似于 JAVA 语言中的方法，需要先定义，使用时需要调用。存储

过程可以定义参数，参数分为 IN、OUT、INOUT 类型三种类型。IN 类型的参数表示接收调用者传入的数据，OUT 类型的参数表示向调用者返回数据，INOUT 类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。

MySQL存储过程的定义

语法格式

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

语法解析

1.存储过程的参数分为 in，out，inout 三种类型。

2.in 代表输入参数（默认情况下为 in 参数），表示该参数的值必须由调用程序指定。

3.out 代表输出参数，表示该参数的值经存储过程计算后，将 out 参数的计算结 果返回给调用程序。

4.inout 代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将 inout 参数的计算结果返回给调用程序。

5.存储过程中的语句必须包含在 begin 和 end 之间。

6.declare 中用来声明变量,变量默认赋值使用 default,语句块中改变变量值,使用 set 变量=值;

存储过程使用

定义第一个存储过程

DELIMITER $$
CREATE   PROCEDURE  demo1()
  -- 存储过程体
  BEGIN
     -- DECLARE 用来声明变量的
     DECLARE v_name VARCHAR(10) DEFAULT '';      
     
     SET v_name = 'jim'; 
     
     SELECT v_name; -- 测试输出语句
  END$$
DELIMITER ;

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

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

-- 查询新闻类型为1的有几条  演示入参和出参
DELIMITER $$
CREATE   PROCEDURE  type_count(IN p_typeid INT,OUT p_count INT)
  -- 存储过程体
  BEGIN
       SELECT COUNT(*) INTO p_count FROM news WHERE typeid = p_typeid;     
       SELECT p_count;
  END$$
DELIMITER ;


-- 在一个存储过程中调用另一个存储,
CALL type_count(1,@p_count);

流程控制语句

--  演示存储过程中逻辑判断
DELIMITER $$
CREATE   PROCEDURE  demo2(IN p_day INT,OUT p_name VARCHAR(10))
 
  BEGIN
         IF p_day = 1  THEN 
           SET p_name  = "星期一";
            SELECT p_name;     
         ELSEIF p_day = 2 THEN 
           SET p_name  = "星期二";
            SELECT p_name;
         ELSE   
           SET p_name = "无效日期";
           SELECT  p_name;  
       
         END IF; 
  END$$
DELIMITER ;

CALL demo2(1,@p_name);

使用存储过程插入信息

-- 存储过程保存管理员,在存储过程中判断账号是否已经存储,不存在存储,否则返回账号已存在
DELIMITER $$
CREATE   PROCEDURE  save_admin(IN p_account VARCHAR(10),IN p_password VARCHAR(10),OUT p_result VARCHAR(10))
  BEGIN
       -- 声明一个变量,接收查询到的结果
       DECLARE v_count INT DEFAULT 0;
       
       SELECT COUNT(*) INTO v_count FROM admin WHERE account = p_account;
          IF v_count = 0 THEN
             INSERT INTO admin(account,PASSWORD)VALUES(p_account,p_password);
              SET p_result = "保存成功";
          ELSE 
             SET p_result = "账号已存在";
             SELECT p_result;   
          END IF; 
  END$$
DELIMITER ;

mybatis调用存储过程

在映射文件里的

<parameterMap type="map" id=“usermap"> 
     <parameter property="addend1" jdbcType="VARCHAR" mode="IN"/> 
     <parameter property="result" jdbcType="VARCHAR" mode="OUT"/> 
</parameterMap>
    

<insert id="saveUserDemo" parameterMap="usermap" statementType="CALLABLE"> 
      {call saveuser(?, ?)} 
</ insert >

java代码

Map<String, Object> parms = new HashMap<String, Object>(); 
parms.put("addend1", 3); 
userDao.saveUserDemo(parms); 
parms.get(“result”);//获得输出参数

函数

函数类似于存储过程,但是函数主要用于查询

格式

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

注意：

1.参数列表包含两部分：参数名 参数类型

2.函数体：肯定会有 return 语句，如果没有会报错

3.函数体中仅有一句话，则可以省略 begin end

4.使用 delimter 语句设置结束标记

​ 设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE; 

删除函数

DROP FUNCTION 函数名; 

不带参数的函数

-- 函数
DELIMITER $$
CREATE  FUNCTION  demo()  RETURNS INT
   
    BEGIN
       DECLARE v_count INT DEFAULT 0;
       SELECT COUNT(*) INTO v_count FROM admin;
       RETURN v_count;      
    END$$

DELIMITER ;

 
SELECT demo();

带参数的

DELIMITER $$
CREATE  FUNCTION  findType(p_type INT)  RETURNS VARCHAR(10)
   
    BEGIN
       DECLARE v_type VARCHAR(10) DEFAULT '';
       IF p_type = 0 THEN
          SET v_type = '超级管理员';
       ELSE
         SET v_type = '管理员';   
       END IF;  
       RETURN v_type;      
    END$$

DELIMITER ;


SELECT account,id,findType(TYPE) FROM admin



DELIMITER $$
CREATE  FUNCTION  find_news_type(p_typeid INT)  RETURNS VARCHAR(10)
   
    BEGIN
       DECLARE v_type VARCHAR(10) DEFAULT '';
        SELECT NAME INTO v_type FROM TYPE WHERE  id = p_typeid;
       RETURN v_type;      
    END$$

DELIMITER ;

SELECT id,title,find_news_type(typeId) FROM news;

触发器

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

触发器具有以下特点：

1.与表相关联

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

2.自动激活触发器

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

3.不能直接调用

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

4.作为事务的一部分

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

定义触发器的语法规则

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

语法解析：

1.触发器名称：是用来标识触发器的，由用户自定义。

2.触发时机：其值是 before 或 after。

3.触发事件：其值是 insert，update 和 delete

4.表名称：标识建立触发器的表名，即在哪张表上建立触发器

5.语句：是触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束，中间包含多条语句；

-- 在删除管理员之前删除管理员角色关联信息
DELIMITER $$

CREATE
    TRIGGER delete_admin_role 
    BEFORE 
    DELETE
    ON admin
    FOR EACH ROW -- 行级触发器  操作多行时,每行都会触发触发器
    BEGIN
           DELETE FROM admin_role WHERE adminid = old.id;
    END$$

DELIMITER ;
 

DELETE FROM admin WHERE id = 2

-- 新增用户时，自动向其他表插入数据
DELIMITER $$ 
CREATE TRIGGER save_user_log AFTER INSERT 
ON user 
FOR EACH ROW 
BEGIN 
	INSERT INTO test(id,NAME)VALUES(new.id,new.account); 
END$$; 

INSERT INTO user(account)VALUES('jim')

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

MySQL架构

进入到 mysql 高级内容学习之前,先整体认识一下 MySQL 的完整架构图。

连接层

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

服务层

第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化,如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层

存储引擎层，存储引擎真正的负责了 MysQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

物理文件存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

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;

存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 .Mrg_Myisam

我们主要分析使用MyIsam 和InnoDB

InnoDB

InnoDB：默认的存储引擎

InnoDB 是一个事务型的存储引擎，有行级锁和外键约束,支持全文检索(全文索引)，它的设计目标是处理大容量数据库系统,MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引；支持主键自增.不存储表的总行数.

MyISAM

MyISAM 也是 MySQL 的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些;支持全文检索;存储表的总行数.

索引

数组索引 可以通过索引快速的找到某个位置的数据.

为什么有索引

不使用索引的话,查询从第一行开始,逐行向后查询,直到查询到我们需要的数据.

​ 如果数据量非常大的情况下,查询效率比较低.

什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。

排好序的快速查找的数据结构.

数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址.

为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉 查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引原理

索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就 能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。

索引优势

提高数据检索的效率,降低数据库的 IO 成本;

通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

索引劣势

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录, 所以索引列也是要占用磁盘空间的。

虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行 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 3 列,为 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=’’索引不生效

全文索引

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

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram; 

SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词') 

查看索引：

SHOW INDEX FROM 表名; 

索引创建原则

那些情况需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引(where 后面的语句)查询中与其它表关联的字段，外键关系建立索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

分组中的字段

那些情况不需要创建索引

表记录太少

经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据， 还要保存一下索引文件

Where 条件里用不到的字段不创建索引

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引数据结构

mysql Innodb引擎默认使用 B+树 作为数据结构存储索引.
​ 排好序的,一个节点可以存储多个数据. 横向扩展, 使得树的高度降低了.

​ 非叶子节点不存储数据,只存储索引,可以放更多的索引.

​ 数据记录都存放在叶子节点中. 找到了索引,也就找了数据.

​ 所有叶子节点之间都有一个链指针,非常适合区间查询 age>20 age<50

聚簇索引和非聚簇索引

聚簇索引

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

非聚簇索引

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

​ 根据学号查询所有信息是聚簇索引

​ 根据学号只查询学号,姓名 虽然学号加了索引,但是还需要查询姓名，需要根据学号,找到主键,通过主键回表查询 此种场景就是非聚簇的

​ 根据学号查询学号（查询学号是否存在）是聚簇索引

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

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

事务

概述

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

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

​ 事务用来管理 insert,update,delete 语句

特性

一般来说，事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性: 一次事务过程中的多个操作要么都成功,要么都失败.

持久性: 事务一旦提交,数据就不可改变.即使数据库服务出现问题.

隔离性: 数据库是允许同时有多个事务进行访问, 这时就需要对多个事务间的操作进行隔离,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

​ 隔离分为4个级别:

​ 读未提交（Read uncommitted） 问题 脏读

​ 读已提交（read committed） 解决 脏读 不可重复读

​ 可重复读（repeatable read） 解决 不可重读 幻读

​ 串行化（Serializable） 解决一切问题 加锁 效率低

**一致性: **在事务开始之前和事务结束以后，数据库的完整性没有被破坏.

事务设置

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

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

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

1、用 BEGIN, ROLLBACK, COMMIT 来实现

BEGIN; / START TRANSACTION; 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

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

SET SESSION / GLOBAL autocommit=0; 禁止自动提交

SET SESSION / GLOBAL autocommit=1;开启自动提交

查看 autocommit 模式

SHOW SESSION / GLOBAL VARIABLES LIKE 'autocommit';

并发事务处理带来的问题:

当两个或者多个事务选择同一行，然后基于最初选定的值进行更新操作时，由于每个事务都不知道其他事务的存在，则会发生丢失更新问题，即最后的更新并覆盖了前一个程序员所做的更改。

假设有两个事务 A 和 B，同时并发。

​ 1.脏读 读到了垃圾数据 A事务读到B事务 未提交的数据

​ 2.不可重复读 A事务开启后 读取两次数据,结果两次读到的内容不一样( 预期的效果是A在同一个事务中读取到数据应该是一样)

​ 3.幻读 A事务开启后, 读取到的两次数据数量不一致

事务隔离级别

只有 InnoDB 支持事务，所以这里说的事务隔离级别是指 InnoDB 下的事务隔离级别。

查看隔离级别

SELECT @@global.transaction_isolation,@@transaction_isolation; 

设置隔离级别

SET SESSION/GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

读未提交(read uncommitted)：

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

读已提交(read committed)：

​ 一个事务只能读取另一个事务已经提交的修改。 其避免了脏读，仍然存在不可以重复读和幻读问题。

可重复读(repeatable read MySQL 默认隔离级别)：

​ 同一个事务中多次读取相 同的数据返回的结果是一样的。其避免了脏读和不可重复读问题，但是幻读依然存在。

串行化(serializable)：

​ 事务串行执行,避免了以上所有问题。

MVCC

MVCC（多版本并发控制 Multi-Version Concurrent Control），是 MySQL 提高性能的一种方式，配合 Undo log 和版本链,替代锁,让不同事务的读-写、写-读操作可以并发执行，从而提升系统性能。一般在使用 读已提交 （READ COMMITTED）和 可重复读（REPEATABLE READ）隔离级别的事务中实现。

基本原理

版本链

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列。

​ **trx_id：**每次对某条聚簇索引记录进行改动时，都会把对应的事务 id 赋值给 trx_id 隐藏列。

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

基本特征

​ 每行数据都存在一个版本，每次数据更新时都更新该版本。

​ 修改时 Copy 出当前版本随意修改，各个事务之间无干扰。

​ 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃 copy （rollback）

​ 每次对记录进行改动，都会记录一条 Undo log，每条 Undo log 也都有一个 roll_pointer 属性（INSERT 操作对应的 Undo log 没有该属性，因为该记录并没有更早的版本），可以将这些 Undo log 都连起来，串成一个链表。

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

ReadView

​ 对于使用 READ UNCOMMITTED 隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用 SERIALIZABLE 隔离级别的事务来说，使用加锁的方式来访问记录。

​ 对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

​ 所以在 InnoDB 中设计了一个 ReadView 的概念，这个 ReadView 中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务 id 放到一个列表中，我们把这个列表命名为为 m_ids。所以我们在开启一次会话进行 SQL 读写时，开 始事务时生成 readview 时，会把当前系统中正在执行的写事务写入到 m_ids 列表中.

​ 在 MySQL 中，READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成 ReadView 的时机不同。

READ COMMITTED：每次读取数据前都生成一个 ReadView

REPEATABLE READ：在第一次读取数据时生成一个 ReadView

锁机制

概述

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB 通过锁机制来保证这一点。

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

行锁，间隙锁，表锁

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

MySQL中不同的存储引擎支持的锁是不一样的，例如 MyIsam 只支持表锁，而 InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

InnoDB 为例

行锁

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

特点

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

间隙锁

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

表锁

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

特点：

​ 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

锁的其他分类

**共享锁（S）：**又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这保证了其他事务可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

**排他锁（Ｘ）：**又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务 T 对数据对象 A 加上 X 锁，事务 T 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。

update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select …for update 语句，加共享锁可以使用 select … lock in share mode 语句。

还可以分为乐观锁,悲观锁：

1.乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的.

2.悲观锁：上面的行锁,间隙锁,表锁等都是悲观锁.

SQL优化

为什么进行SQL优化

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

SQL优化的方法

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免索引失效

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

select id from t where num is null

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num=0 

​ 2.2 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。2.3 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃 使用索引而进行全表扫描，如：

select id from t where num=10 or num=20 

​ 2.4 in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3 

​ 2.5 下面的查询也将导致全表扫描：

select id from t where name like '%abc%' 

​ 2.6 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100 

应改为:

select id from t where num=100*2 

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

select id from t where substring(name,1,3)='abc'--name 以 abc 开头的 id

3.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑， 视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

4.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

5.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间， 其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

6.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

7.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

8.尽量避免 NULL,平时开发过程中，MySQL 字段的一般都会设置为 NOT NULL，原因如下：

​ 空值(‘ ’)是不占用空间的 MySQL 中的 NULL 其实是占用空间的.

​ 如果查询中包含可为 NULL 的列，不走索引

​ count()统计某列的记录数的时候，如果采用的 NULL 值，会被系统自动忽略掉，但是空值是会进行统计到其中的。