1 数据库备份与恢复

命令行

• 备份
  登录到 MySQL 服务器，输入： mysqldump -u root -p 数据库名称 > 文件保存路径
• 恢复

1. 登录 MySQL 数据库
2. 删除已备份数据库
3. 重新创建名称相同的数据库
4. 使用该数据库
5. 导入文件执行：source 备份文件全路径

2 事务

事务介绍

• 事务：一台或者多条 SQL 语句组成的一个执行单元，要么同时成功要么同时失败
• 单元中的每条SQL语句都相互依赖，形成一个整体
• 如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会撤回到事务最初状态
• 如果单元中的所有 SQL 语句都执行成功，则事务就会顺利执行

事务的操作

• 开启事务
  START TRANSACTION;
• 回滚事务
  ROLLBACK;
• 提交事务
  COMMIT;

事务的提交方式

• 事务提交方式的分类
  自动提交（MySQL默认）
  手动提交
• 查看事务提交方式
  SELECT @@AUTOCOMMT;

0 代表手动提交，1 代表自动提交

• 修改事务提交方式
  SET @@AUTOCOMMIT = 数字;

事务四大特征（ACID）

• 原子性
  原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
• 一致性
  一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
  拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
• 隔离性
  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
• 持久性
  持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

事务的隔离级别

• 事务的隔离级别

  • 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
  • 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题

• 隔离级别分类

	隔离级别	名称	会引发问题
1	读未提交	read uncommitted	脏读、不可重复读、幻读
2	读已提交	read committed	不可重复读、幻读
3	可重复读	repeatable read	幻读
4	串行化	serializable	无

• 可能引发的问题

问题	现象
脏读	是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读	是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读	select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

• 查询数据库隔离级别
  SELECT @@transaction_isolation;
• 修改数据库隔离级别
• SET @@SESSION.TRANSACTION_ISOLATION = '级别字符串';;

修改结束后需要重新连接，才能查询到新的隔离级别

• 隔离级别问题解决
  • 脏读
    修改隔离级别为read committed
  • 不可重复读
    修改隔离级别为repeatable read
  • 幻读
    修改隔离级别为serializable

注意：隔离级别从小到大安全性越来越高，但是效率越来越低，所以不建议修改数据库默认的隔离级别

3 存储引擎

MySQL体系结构

存储引擎介绍

• 在生活中，引擎就是整个机器运行的核心（发动机），不同的引擎具备不同的功能，应用于不同的场景
• MySQL 数据库使用不同的机制存取表文件包括存储方式、索引技巧、锁定水平等不同的功能，这些不同的技术以及配套的功能称为存储引擎
• MySQL 支持的存储引擎有很多，常用的有三种：InnoDB、MyISAM、MEMORY.
• 特性对比
  • MyISAM：访问快，不支持事务和外键操作
  • InnoDB：支持事务和外键操作，支持并发控制，占用磁盘空间大（MySQL 5.5版本后默认）
  • MEMORY：内存存储，速度快，不安全。适合小量快速访问
    

存储引擎操作

• 查询数据库支持的存储引擎
  SHOW ENGINES;
• 查询某个数据库中所有表的存储引擎
  SHOW TABLE STATUS FROM 数据库名称;
• 查询某个数据库中某个数据表中的存储引擎
  SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
• 创建数据表，指定存储引擎

CREATE TABLE 表名(
	列名 数据类型,
	...
)ENGINE = 引擎名称

• 修改数据表的存储引擎
  ALTER TABLE 表名 ENGINE = 引擎名称

存储引擎的选择

• MyISAM
  • 特点：不支持事务和外键操作，读取速度快，节约资源
  • 使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高
• InnoDB
  • 特点：MySQL 的默认存储引擎，支持事务和外键操作
  • 使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作
• MEMORY
  • 特点：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问
  • 使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果
    总结：针对不同的需求场景，来选择最合适的存储引擎即可！如果不确定，则使用数据库默认的存储引擎

4 索引

索引介绍

• MySQL 索引：是帮助 MySQL 高效获取数据的一种数据结构，所以索引的本质是数据结构
• 在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引分类

• 按照功能分类
  • 普通索引：最基本的索引，没有任何限制
  • 唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值组合必须唯一
  • 主键索引：一种特殊的唯一索引，不允许有空值，在建表时有主键同时创建主键索引
  • 联合索引：顾名思义，就是将单列索引进行组合
  • 外键索引：只有InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作
  • 全文索引：快速匹配全部文档的方式。InnoDB 引擎 5.6 版本后才支持全文索引。MEMORY 引擎不支持
• 按照结构分类
  • BTree 索引：MySQL 使用最频繁的一个索引数据结构，是InnoDB 和 MyISAM 存储引擎默认的索引类型，底层基于 B + Tree 数据结构
  • Hash 索引：MySQL 中 MEMORY 存储引擎默认支持的索引类型

索引的操作

• 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] --默认是BTree
ON 表名(列名...)

• 查看索引
  SHOW INDEX FROM 表名

--创建普通索引
CREATE INDEX idx_name ON account(NAME);
--创建唯一索引
CREATE UNIQUE INDEX idx_money ON account(money);
--查看索引
SHOW INDEX FROM account;

• 添加索引
  • 普通索引：ALTER TABLE 表名 ADD INDEX 索引名称(列名);
  • 组合索引：ALTER TABLE 表名 ADD INDEX 索引名称(列名1，列名2，…);
  • 主键索引：ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
  • 外键索引：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFRENCES 主表名(主键列名);
  • 唯一索引：ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
  • 全文索引：ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
• 删除索引
  *DROP INDEX 索引名称 ON 表名;

索引原理

• 索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。
• B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。
• 需要理解的
  磁盘存储
  BTree
  B + Tree。

磁盘存储

• 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
• 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
• InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
• InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

BTree

B + Tree

• BTree 数据结构
  每个节点中不仅包含key值，还有数据，会增加查询数据时磁盘的IO次数
• B + Tree 数据结构
  非叶子节点只存储key值
  所有数据存储在叶子节点
  所有叶子节点之间都有连接指针
• B + Tree 好处
  提高查询速度
  减少磁盘的IO次数
  树形结构较小

索引的设计原则

• 创建索引遵循的原则
  1. 对查询频次较高，且数据量比较大的表建立索引
  2. 使用唯一索引，区分度越高，使用索引的频次越高
  3. 索引字段的选择，最佳候选列应当从 WHERE 子句的条件中提取
  4. 索引虽然可以有效的提升查询数据的效率，但并不是多多益善
• 最左匹配原则（只适合组合索引）
  在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
  对列name列、address和列phone列建一个联合索引

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

联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。

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)

进行数据匹配。
索引的字段可以是任意顺序的，如：

-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';

Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。
联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。

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

5 锁机制

锁的介绍

• 锁机制：数据库为了保证 数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则
• 锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性
• 按操作分类
  • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据
  • 排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入
• 按粒度分类：
  • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
  • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
  • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
• 按使用方式分类：
  • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
  • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
• 不同存储引擎支持的锁

存储引擎	表级锁	行级锁	页级锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

InnoDB 共享锁

• 特点
  数据可以被多个事务查询，但是不能修改
• 创建共享锁格式

SELECT语句 LOCK IN SHARE MODE;

• 举例

-- 窗口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;

InnoDB 排他锁

• 特点
  加锁的数据，不能被其他事务加锁查询或修改
• 创建排他锁格式

SELECT语句 FOR UPDATE;

• 举例

-- 窗口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;

注意：锁的兼容性
共享锁和共享锁 兼容
共享锁和排他锁 冲突
排他锁和排他锁 冲突
排他锁和共享锁 冲突

MyISAM 读锁

• 特点
  所有连接只能查询数据，不能修改
• 读锁语法格式

#加锁
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;

MyISAM 写锁

• 特点
  其他连接不能查询和修改数据
• 写锁语法格式

#加锁
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;

悲观锁和乐观锁

• 悲观锁
  就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
  整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
  我们之前所学的行锁，表锁不论是读写锁都是悲观锁。
• 乐观锁的概念
  就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
  但是在更新的时候会去判断在此期间数据有没有被修改。
  需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
• 悲观锁和乐观锁使用前提
  • 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
  • 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。
• 乐观锁的实现方式

  • 版本号

    • 给数据表中添加一个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
    • 每次更新后都将最新时间插入到此列。
    • 读取数据时，将时间读取出来，在执行更新的时候，比较时间。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

锁的总结

• 表锁和行锁
  • 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
  • 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！
• InnoDB锁优化建议
  • 尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。
  • 合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。
  • 尽可能减少基于范围的数据检索过滤条件。
  • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。