1.用户管理
创建用户
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 % 表示任何地址都可以连接
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户 可以直接登录。
修改用户名
UPDATE mysql.user SET USER='li4' WHERE USER='wang5';
FLUSH PRIVILEGES;
删除用户
#方式1：使用DROP方式删除（推荐）
#使用DROP USER语句来删除用户时，必须用于DROP USER权限。
DROP USER user[,user]…;
DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
#方式2：使用DELETE方式删除
DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;
不推荐通过 DELETE FROM USER u WHERE USER=‘li4’ 进行删除，系统会有残留信息保 留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表 的相应记录都消失了。
修改密码
#修改当前用户密码
#1
ALTER USER USER() IDENTIFIED BY 'new_password';
#2
SET PASSWORD='new_password';
#修改其他用户密码
#1
ALTER USER user [IDENTIFIED BY '新密码']
[,user[IDENTIFIED BY '新密码']]…;
#2
SET PASSWORD FOR 'username'@'hostname'='new_password';
#3使用UPDATE语句修改普通用户的密码（不推荐）
UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";
2.权限管理
- CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
- SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
- SELECT权限 只有在它们真正从一个表中检索行时才被用到。
- INDEX权限 允许创建或删除索引，INDEX适用于已 有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
- ALTER权 限 可以使用ALTER TABLE来更改表的结构和重新命名表。
- CREATE ROUTINE权限 用来创建保存的 程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限 用来执行保存的 程序。
- GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
- FILE权限 使用 户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权 限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务 器可以访问这些文件）。
授权
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
#该权限如果发现没有该用户，则会直接新建一个用户
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;
#授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
查看权限
#查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();
#查看其他用户权限
SHOW GRANTS FOR 'user'@'主机地址' ;
回收权限
在将用户账户从user表删除之前，应该收回相应用户的所有权限。
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
#注意： 须用户重新登录后才能生效
3.用户管理
引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关 重要的。
创建
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
#角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空
CREATE ROLE 'manager'@'localhost;
授权
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
#查看权限
SHOW GRANTS FOR 'manager';
回收权限
REVOKE privileges ON tablename FROM 'rolename';
删除角色
DROP ROLE role [,role2]...
# 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。
赋予角色
GRANT role [,role2,...] TO user [,user2,...];
GRANT 'school_read' TO 'kangshifu'@'localhost';
SHOW GRANTS FOR 'kangshifu'@'localhost';
激活角色
SELECT CURRENT_ROLE()
#使用kangshifu用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。
#1
SET DEFAULT ROLE ALL TO 'kangshifu'@'localhost';
#2
show variables like 'activate_all_roles_on_login';
SET GLOBAL activate_all_roles_on_login=ON;
#这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。
撤销角色
REVOKE role FROM user;
4.sql逻辑架构
- 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
- SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
- 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
SQL执行流程
-
查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没 有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃 了这个功能。
-
解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
-
优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索 等。
-
执行器:在执行之前需要判断该用户是否 具备权限 。如果没有，就会返回权限错误。如果具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
select @@profiling;
show variables like 'profiling';
set profiling=1;
select * from employees;#执行任意一个查询
show profile;
#可以查看sql执行步骤
缓冲池
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲 池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会 优先对使用频次高 的热数据进行加载 。
查询缓存是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。
show variables like 'innodb_buffer_pool_size';
set global innodb_buffer_pool_size = 268435456;
5.存储引擎
show engines;
#查看默认的存储引擎
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;
#修改默认的存储引擎
SET DEFAULT_STORAGE_ENGINE=MyISAM;
#或者修改 my.cnf 文件：
default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service
设置表存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是 说不同的表可以有不同的物理存储结构，不同的提取和写入方式。
#创建表的语句没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
#修改表引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
引擎介绍
InnoDB 引擎：具备外键支持功能的事务存储引擎
-
InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务 的完整提交(Commit)和回滚(Rollback)。
-
除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
-
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
-
InnoDB是 为处理巨大数据量的最大性能设计 。
-
对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和 索引。
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。
MyISAM 引擎：主要的非事务处理存储引擎
- MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
- 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
- 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
- 应用场景：只读应用或者以读为主的业务
Archive 引擎：用于数据存档
- 仅支持插入和查询，行插入后无法修改
- 具有很好的压缩机制，使用zlib压缩库。
- 数据文件扩展名为ARZ
- 适合存储大量独立的作为历史记录的数据，拥有很高的插入速度，但对查询支持较差
Blackhole 引擎：丢弃写操作，读操作会返回空内容
- 没有实现任何存储机制，丢弃所有插入数据，不做保存
- 服务器会记录Blackhole表的日志，所以可用于复制数据到备库，或者简单记录到日志。但有很多问题，不推荐。
- 基本没啥用
CSV 引擎：存储数据时，以逗号分隔各个数据项
- 将普通csv文件作为mysql的表处理，但不支持索引
- 对于数据的快速导入，导出是有明显优势的
- 创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相 同，后缀为 CSM 。
CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
INSERT INTO test VALUES(1,'record one'),(2,'record two');
Memory 引擎：置于内存的表
Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另 外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。
- Memory同时 支持哈希（HASH）索引 和 B+树索引 。
- Memory表至少比MyISAM表要 快一个数量级 。
- MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。
- 数据文件与索引文件分开存储。
- 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。
使用Memory存储引擎的场景：
- 目标数据比较小 ，而且非常 频繁的进行访问 ，在内存中存放数据，如果太大的数据会造成 内存溢 出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大 小。
- 如果 数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中。
- 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。
Federated 引擎：：访问远程表
Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务 器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。
Merge引擎：管理多个MyISAM表构成的表集合
NDB引擎：MySQL集群专用存储引擎
也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集 群。
6.索引
索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集 索引称为二级索引或者辅助索引。
CREATE TABLE index_demo(
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1)
) ROW_FORMAT = Compact;
#record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1 暂时还没用过，下面讲。
InnoDB中索引
聚簇索引
不是一种单独的索引类型，而是一种数据存储方式，所有用户记录都存储在了叶子结点，也就是所谓的索引即数据，数据即索引。
特点
- 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
- 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
- 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。
- B+树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。
优点
- 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快
- 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
- 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。
缺点
- 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新
- 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据
二级索引（辅助索引、非聚簇索引）
概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
一张表只能有一个聚簇索引，但可以有多个非聚簇索引
联合索引
属于非聚簇索引
我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序这个包含两层含义：
- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下，采用c3列进行排序
MyISAM中索引
是MyISAM索引的原理图
在Col2上建立一个二级索引
MyISAM 与 InnoDB对比
MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。
两种引擎中索引的区别：
- 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
- InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数 据记录的地址
- InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。
- MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
- InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。
索引的声明与使用
索引的分类
- 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
- 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
- 按照 作用字段个数 进行划分，分成单列索引和联合索引。
创建索引
创建约束的时候相当于创建了一个索引
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |DESC]
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
- UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
- INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
- index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
- col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
- length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
- ASC 或 DESC 指定升序或者降序的索引值存储。
删除索引
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;
#删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
哪些情况适合创建索引
-
字段的数值有唯一性的限制(业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba） 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。)
-
频繁作为 WHERE 查询条件的字段
-
经常 GROUP BY 和 ORDER BY 的列
-
UPDATE、DELETE 的 WHERE 条件列
-
DISTINCT 字段需要创建索引
-
多表 JOIN 连接操作时，创建索引注意事项
- 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
- 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。
- 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
-
使用列的类型小的创建索引(数据类型的数据范围大小)
-
使用字符串前缀创建索引
create table shop(address varchar(120) not null); alter table shop add index(address(12)); # count(distinct left(列名, 索引长度))/count(*) select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度 count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度 count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度 count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度 from shop; #如果使用了索引前缀，无法支持使用索引排序，只能用文件排序
-
区分度高(散列性高)的列适合作为索引(基数越大)
-
使用最频繁的列放到联合索引的左侧 这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
-
在多个字段都要创建索引的情况下，联合索引优于单值索引
不适合创建索引的情况
- 在where中使用不到的字段，不要设置索引
- 数据量小的表最好不要使用索引(在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。)
- 有大量重复数据的列上不要建立索引
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 不要定义冗余或重复的索引
7.数据库优化
EXPLAIN
EXPLAIN SELECT select_options
#或者
DESCRIBE SELECT select_options
EXPLAIN 语句输出的各个列的作用如下：
演示表：
#s1
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
#s2
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
table
不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）。
id
- id如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
- 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
select_type
#SIMPLE
EXPLAIN SELECT * FROM s1;
#PRIMARY s1
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
#UNION s2
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
#UNION RESULT 表示使用临时表完成UNION查询去重工作，针对临时表的查询
#SUBQUERY 子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
#DEPENDENT SUBQUERY 依赖外部表的子查询
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 =s2.key2) OR key3 = 'a';
#DEPENDENT UNION
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 ='a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
#DERIVED 使用派生出来的表进行查询 derived_s1
EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
#MATERIALIZED 物化表，根据key1 IN ...所生成的一个结果集
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
#剩下不常用
partitions (可略)
匹配分区信息
type ☆
针对表的访问方法
完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。
#system 当表中只有一条记录，并且该表使用的存储引擎的统计数据是精确的，比如MyISAM,Memory,
CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;
#const 根据主键或者唯一二级索引与常数进行等值匹配
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
#eq_ref 连接查询时，被驱动表是通过主键或者唯一二级索引列等值匹配访问的 s2
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
#ref 通过普通二级索引列与常量进行等值匹配
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
#ref_or_null 通过普通二级索引列进行等值匹配，且列值可以是null
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
#index_merge 索引合并 key1,key3都是
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
#unique_subquery 针对包含IN子查询,如果查询优化器决定将IN子查询转为EXISTS子查询，且子查询可以使用到主键进行等值匹配
EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 =s2.key1) OR key3 = 'a';
#index_subquery
> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 wheres1.key1 = s2.key1) OR key3 = 'a';
#range 获取范围区间
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
#index 当时用索引覆盖，但需要扫描全部索引记录
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
#ALL 整表扫描
#fulltext 全文索引
结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）
possible_keys和key
表示可能用到的索引和实际用到的索引
key_len ☆
实际使用到的索引长度(字节数)，越大越好 主要针对联合索引
varchar(10)变长字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
ref
当使用索引列等值查询时，与其进行等值匹配的对象信息
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
#ref = content
rows ☆
预估需要读取的记录条数，越小越好
filtered
结果记录条数与被搜索表记录条数的百分比,越大越好
Extra ☆
#No tables used
EXPLAIN SELECT 1;
#Impossible WHERE
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
#Using where 全表扫描/有索引时还有额外搜索条件
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
#No matching min/max row 查询列表有MIN/MAX聚合函数，但没有复合WHERE子句中的搜索记录
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
#Using index 只包含索引
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
#Using index condition 有索引，但索引失效,LIKE导致索引失效
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
#Using join buffer (Block Nested Loop) 被驱动表s2不能利用索引加快访问,mysql会分配join buffer内存块加快速度
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field =
s2.common_field;
#Not exists 使用外连接时，如果WHERE中包含被驱动表某个列为NULL，而那个列又不允许存储NULL
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
#Using intersect(...) 、 Using union(...) 和 Using sort_union(...) 合并索引
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
#Zero limit
EXPLAIN SELECT * FROM s1 LIMIT 0;
#Using filesort 文件排序
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
#Using temporary 使用临时表
EXPLAIN SELECT DISTINCT common_field FROM s1;
#
8.索引优化
索引失效
#全值匹配
#使用SELECT *并且不带where
#最佳左前缀法则
#根据索引顺序写where索引才有效
#主键插入顺序
#建议主键自增，不然手动插入到中间会导致分页使性能损耗
#计算、函数、类型转换(自动或手动)导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
#类型转换导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 范围条件右边的列索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
create index idx_age_name_classid on student(age,name,classid);#将范围写在索引的最右边
#不等于(!= 或者<>)索引失效
# is null可以使用索引，is not null无法使用索引
# like以通配符%开头索引失效
#【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
#OR 前后存在非索引的列，索引失效
#数据库和表的字符集统一使用utf8mb4
#统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。
关联查询优化
#左外连接
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;
ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描
ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描
#采用内连接 mysql自动选择驱动表
#两个表都有索引时，小表驱动大表
#如果使用join语句的话，需要让小表做驱动表。
子查询优化
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代
排序优化
- SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫 描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。
- 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。
- 无法使用 Index 时，需要对 FileSort 方式进行调优。
INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/
GROUP BY优化
group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接 使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的
优化分页查询
#1
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;
#2 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;
覆盖索引
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数 据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。
- 好处：
- 避免Innodb表进行索引的二次查询（回表）
- 可以把随机IO变成顺序IO加快查询效率
- 弊端：
- 索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA，或者称为业务数据架构师的工作。
前缀索引
alter table teacher add index index1(email);
#或
alter table teacher add index index2(email(6));
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考 虑的一个因素。
索引下推(ICP)
先进行索引在进行回表，减少回表次数
zipcode和lastname在索引中，而address不在
没有ICP：对zipcode进行索引，然后回表，在对lastname进行判断查找，再对adress进行判断查找。如果匹配zipcode的有100条数据则需要回表100次
有ICP：对zipcode和lastname进行索引，然后回表，对adress进行判断查找。如果匹配zipcode和lastnmae的只有10条，则只需要回表10次
使用条件
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录 到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。
普通索引 vs 唯一索引
假设，执行查询的语句是 select id from test where k=5。 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一 个不满足k=5条件的记录。 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检 索。 那么，这个不同带来的性能差距会有多少呢？答案是， 微乎其微 。
change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁 盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。 将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了 访问这个数据页 会触 发merge外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行merge 操作。 如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存 ，提高内存利用率。 唯一索引的更新就不能使用change buffer ，实际上也只有普通索引可以使用。
change buffer的使用场景
- 普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是 对 更新性能 的影响。所以，建议你 尽量选择普通索引 。
- 如果所有的更新后面，都马上 伴随着对这个记录的查询 ，那么你应该 关闭change buffer 。而在 其他情况下，change buffer都能提升更新性能。
- 在实际使用中会发现， 普通索引 和 change buffer 的配合使用，对于 数据量大 的表的更新优化 还是很明显的。
9.其他优化
EXISTS 和 IN 的区分
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc)
#A小B大用EXISTS
#B小A大用IN
多使用COMMIT
只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放 的资源而减少。
COMMIT 所释放的资源：
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述 3 种资源中的内部花费
LIMIT 1 对优化的影响
针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找 到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加 上 LIMIT 1 了。
10.设计规范
第一范式
确保字段具有原子性，即每个字段不可再次拆分
第二范式
满足数据表里每一条记录都是可唯一标识的，所有非主键字段，必须完全依赖主键，不能只依赖一部分。
成绩表 （学号，课程号，成绩）关系中，（学号，课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以“（学号，课程号）→成绩”就是 完全依赖关系 。
1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。
第三范式
要求数据表中所有非主键字段不能依赖其他非主键字段
BCNF(巴斯范式)
在第三范式上进行了改进，若一个关系达到第三范式且只有一个候选键，或者每个候选键都是单属性，则满足BC范式
一般情况数据库满足BC范式或三范式就行
第四范式
在满足巴斯范式上，消除同一表内的多对多关系删除
职工表(职工编号，职工孩子姓名，职工选修课程)。
在这个表中，同一个职工可能会有多个职工孩子姓名。同样，同一个职工也可能会有多个职工选修课程，即这里存在着多值事实，不符合第四范式。
如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如： 职工表一 (职工编号，职工孩子姓名)， 职工表二 (职工编号，职工选修课程)，两个表都只有一个多值事实，所以符合第四范式。
第五范式、域键范式
在满足第四范式（4NF）的基础上，消除不是由候选键所蕴含的连接依赖。如果关系模式R中的每一个连 接依赖均由R的候选键所隐含，则称此关系模式符合第五范式。
第五范式处理的是 无损连接问题 ，这个范式基本 没有实际意义 ，因为无损连接很少出现，而且难以察 觉。而域键范式试图定义一个 终极范式 ，该范式考虑所有的依赖和约束类型，但是实用价值也是最小 的，只存在理论研究中。
11.ER模型
ER 模型中有三个要素，分别是实体、属性和关系。
- 实体：矩形
- 属性：椭圆
- 关系：菱形
实体和属性不容易区分。这里提供一个原则：我们要从系统整体的角度出发去看，可以独立存在 的是实体，不可再分的是属性。也就是说，属性不能包含其他属性。
ER 模型图转换成数据表
（1）一个 实体 通常转换成一个 数据表 ；
（2）一个 多对多的关系 ，通常也转换成一个 数据表 ；
（3）一个 1 对 1 ，或者 1 对多 的关系，往往通过表的 外键 来表达，而不是设计一个新的数据表；
（4） 属性 转换成表的 字段 。
数据表的设计原则
- 数据表的个数越少越好
- 数据表中的字段个数越少越好
- 数据表中联合主键的字段个数越少越好
- 使用主键和外键越多越好
注意：这个原则并不是绝对的，有时候我们需要牺牲数据的冗余度来换取数据处理的效率。
12.事务
在 MySQL 中，只有InnoDB 是支持事务的。
事务处理的原则：保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方 式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保 存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。
ACID
- 原子性（atomicity）:原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
- 一致性（consistency）:根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态 是 语义上 的而不是语法上的，跟具体的业务有关。比如账户有200，但是转出300，余额为-100，此时数据就不一致，应为规定余额>=0。
- 隔离型（isolation）：事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对 并发 的 其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库 故障不应该对其有任何影响。
事务的隔离性由 锁机制 实现
而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。 REDO LOG 称为 重做日志 ，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持 久性。 UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性
显式事务
步骤1： START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。
BEGIN;
#或者
START TRANSACTION;
#START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个 修饰符 ：
#READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
#READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
#WITH CONSISTENT SNAPSHOT ：启动一致性读。
步骤2：一系列事务中的操作（主要是DML，不含DDL）
步骤3：提交事务 或 中止事务（即回滚事务）
# 提交事务。当提交事务后，对数据库的修改是永久性的。
mysql> COMMIT;
# 回滚事务。即撤销正在进行的所有没有提交的修改
mysql> ROLLBACK;
# 将事务回滚到某个保存点。
mysql> ROLLBACK TO [SAVEPOINT]
#事务中保存
SAVEPOINT 保存点名称
#删除保存点
RELEASE SAVEPOINT 保存点名称；
隐式事务
- 数据定义语言（Data definition language，缩写为：DDL）
- 隐式使用或修改mysql数据库中的表
- 事务控制或关于锁定的语句
- 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了 另一个事务时，会 隐式的提交 上一个事务。
- 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会 隐式的提交 前边语 句所属的事务。
- 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会 隐式的提交 前边语句所属的事 务。
- 加载数据的语句 LOAD DATA
- 关于MySQL复制的一些语句 主从复制等等
- 其它的一些语句 分析表，优化等等
事务隔离性
- 脏写（ Dirty Write ）：对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数 据，那就意味着发生了 脏写
- 脏读（ Dirty Read ）：对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。 之后若 Session B 回滚 ，Session A 读取 的内容就是 临时且无效 的。
- 不可重复读：对于两个事务Session A、Session B，Session A 读取 了一个字段，然后 Session B 更新 了该字段。 之后 Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读。
- 幻读（ Phantom ）：对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插 入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。那就意味着发生了幻读。
脏写 > 脏读 > 不可重复读 > 幻读
事务日志
REDO日志
保证持久性
- 好处
- redo日志降低了刷盘频率
- redo日志占用的空间非常小
- 特点
- redo日志是顺序写入磁盘的
- 事务执行过程中，redo log不断记录
Redo log可以简单分为以下两个部分：
- 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。
- 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。
innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务 时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
- 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日 志的同步）
- 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
- 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自 己决定什么时候同步到磁盘文件。
Undo日志
保证原子性，在事务中 更新数据 的 前置操作 其实是要 先写入一个undo log
作用1：回滚数据 作用2：MVCC
回滚段：
InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请。
- 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。
- 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数 据会被复制到回滚段。
- 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够 用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘 区或者在回滚段允许的情况下扩展新的盘区来使用。
- 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个 undo表空间。
- 当事务提交时，InnoDB存储引擎会做以下两件事情： 将undo log放入列表中，以供之后的purge操作 判断undo log所在的页是否可以重用，若可以分配给下个事务使用
在InnoDB存储引擎中，undo log分为： insert undo log和update undo log
13.锁
读锁、写锁
- 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会 互相影响，相互不阻塞的。
- 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样 就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。
- 需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。
表级锁、页级锁、行锁
表锁（Table Lock）
表级别的S锁、X锁
在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级 别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其 他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务 中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会 发生阻塞。这个过程其实是通过在 server层 使用一种称之为 元数据锁 （英文名： Metadata Locks ， 简称 MDL ）结构来实现的。
一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩 溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取 InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：
- LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。
- LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
不过尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供 什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的 行锁
意向锁 （intention lock）
InnoDB 支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向 锁就是其中的一种 表锁 。
- 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
- 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
即：意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前， InooDB 会先获取该数据行 所在数据表的对应意向锁 。
- InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。
- 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
- IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
- 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。
自增锁（AUTO-INC锁）
#有插入数据的方式总共分为三类，分别是“ Simple inserts ”，“ Bulk inserts ”和“ Mixed-mode inserts ”。
#1.“Simple inserts” （简单插入）
可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT...VALUES() 和 REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行数。
#2. “Bulk inserts” （批量插入）
事先不知道要插入的行数 （和所需自动递增值的数量）的语句。比如 INSERT ... SELECT ， REPLACE... SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列分配一个新值。
#3. “Mixed-mode inserts” （混合模式插入）
这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 只是指定了部分id的值。另一种类型的“混合模式插入”是 INSERT ... ON DUPLICATE KEY UPDATE 。
#innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：
#（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)
在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有 AUTO_INCREMENT列的表。每当执行 insert的时候，都会得到一个表级锁(AUTO-INC锁)，使得语句中生成的 auto_increment为顺序，且在binlog中重放的时候，可以保证 master与 slave中数据的 auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行 insert的时候，对于AUTO-INC锁的争夺会 限制并发 能力。
#（2）innodb_autoinc_lock_mode = 1(“连续”锁定模式)
在MySQL 8.0 之前，连续锁定模式是 默认的
在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT ...SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。
对于“Simple inserts”（要插入的行数事先已知），则通过在 mutex（轻量锁） 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts”等待AUTO-INC锁，如同它是一个“bulk inserts”。
#（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)
从 MySQL 8.0 开始，交错锁模式是 默认 设置。
在此锁定模式下，自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一 且 单调递增 的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。
元数据锁（MDL锁）
MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。
MDL 的作用是，保证读写的正确性。比 如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一 列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写 锁。
行锁
会死锁
记录锁（Record Locks）
记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。比如我们把id值为8的 那条记录加一个记录锁的示意图如图所示。仅仅是锁住了id值为8的记录，对周围的数据没有影响。
记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。
- 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可 以继续获取X型记录锁；
- 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不 可以继续获取X型记录锁。
间隙锁（Gap Locks）
MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方 案解决，也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁
比如，把id值为8的那条 记录加一个gap锁的示意图如下。
图中id值为8的记录加了gap锁，意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ，其实就是 id列的值(3, 8)这个区间的新记录是不允许立即插入的。比如，有另外一个事务再想插入一条id值为4的新 记录，它定位到该条新记录的下一条记录的id值为8，而这条记录上又有一个gap锁，所以就会阻塞插入 操作，直到拥有这个gap锁的事务提交了之后，id列的值在区间(3, 8)中的新记录才可以被插入。
gap锁的提出仅仅是为了防止插入幻影记录而提出的。
临键锁（Next-Key Locks）
有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提 出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为 next-key锁 。Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁， innodb默认的锁就是Next-Key locks。
插入意向锁（Insert Intention Locks）
一个事务在 插入 一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁 （ next-key锁 也包含 gap锁 ），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是InnoDB规 定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是 现在在等待。InnoDB就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为： LOCK_INSERT_INTENTION ，我们称为 插入意向锁 。插入意向锁是一种 Gap锁 ，不是意向锁，在insert 操作时产生。
插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁 。
事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。
页锁
会死锁
页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我 们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量 超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。
乐观锁、悲观锁
从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待 数据并发的思维方式 。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的 设计思想 。
乐观锁
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新 的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过 程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量。
#1.乐观锁的版本号机制
在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE ... SET version=version+1 WHERE version=version 。此时如果已经有事务对这条数据进行了更改，修改就不会成功。
#2. 乐观锁的时间戳机制
时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行比较，如果两者一致则更新成功，否则就是版本冲突。
你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或 者时间戳），从而证明当前拿到的数据是否最新。
悲观锁
悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上 锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当 其他线程想要访问数据时，都需要阻塞挂起。
两种锁的适用场景
- 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁 问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
- 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。
显式锁、隐式锁
隐式锁
情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务 id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的的就是 当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X 锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态 （也就是为自己也创建一个锁结构， is_waiting 属性是 true ）。
情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如 果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已 经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记 录，然后再重复 情景一 的做法。
逻辑过程
A. InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。
B. 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活 动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)。
C. 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。
D. 等待加锁成功，被唤醒，或者超时。
E. 写数据，并将自己的trx_id写入trx_id字段。
显式锁
通过特定的语句进行加锁，我们一般称之为显示加锁，例如：
#显示加共享锁：
select .... lock in share mode
#显示加排它锁：
select .... for update
其他锁
全局锁
全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后 其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结 构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份 。
Flush tables with read lock
14.多版本并发控制
MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版 本管理来实现数据库的 并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保 证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到 即使有读写冲突时，也能做到 不加锁 ， 非阻塞并发读 ，而这个读指的就是 快照读 , 而非 当前读 。当前 读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。
快照读
快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞 读
当前读
当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务 不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前 读。
隐藏字段、Undo Log版本链
- trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id 隐藏列。
- roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然 后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo Log Segment也会被系统回收（也就是该undo日志占用的Undo页面链表要么被重用，要么被释 放）。
MVCC实现原理之ReadView
MVCC 的实现依赖于：隐藏字段、Undo Log、Read View。
- 使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录 的最新版本就好了。
- 使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。
- 使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到 已经提交了的 事务修改 过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问 题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。
#creator_trx_id ，创建这个 Read View 的事务 ID。
说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。
#trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表。
#up_limit_id ，活跃的事务中最小的事务 ID。
#low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。
注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。
ReadView的规则
- 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问 它自己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前 事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事 务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判 断一下trx_id属性值是不是在 trx_ids 列表中。
- 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
- 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
MVCC整体操作流程
- 首先获取事务自己的版本号，也就是事务 ID；
- 获取 ReadView；
- 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
- 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
- 最后返回符合规则的数据。
15.日志
- 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
- 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令， 对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
- 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的 状态，从而对服务器进行维护。
- 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故 障时数据的无损失恢复。
- 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。 从服务器通过读取中继日志的内容，来同步主服务器上的操作。
- 数据定义语句日志：记录数据定义语句执行的元数据操作。
除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录中。
弊端：
- 日志功能会 降低MySQL数据库的性能 。
- 日志会占用大量的磁盘空间 。
二进制日志
使用日志恢复数据
mysqlbinlog [option] filename|mysql –uuser -ppass;
- filename ：是日志文件名。
- option ：可选项，比较重要的两对option参数是–start-date、–stop-date 和 --start-position、-- stop-position。
- –start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
- –start-position和–stop-position ：可以指定恢复数据的开始位置和结束位置。
使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必 须在atguigu-bin.000002之前恢复。
写入机制
binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再 把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一 次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。
为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。 最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
binlog与redolog对比
- redo log 它是 物理日志 ，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生 的。
- 而 binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程 中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。
binlog与redolog逻辑不一致
redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？
导致数据不一致
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。
16.主从复制
主从复制的原理
Slave 会从 Master 读取 binlog 来进行数据同步。
基本原则
- 每个 Slave 只有一个 Master
- 每个 Slave 只能有一个唯一的服务器ID
- 每个 Master 可以有多个 Slave
17.数据备份与恢复
物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比 较大，MySQL中可以用 xtrabackup 工具来进行物理备份。
逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空 间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现。
逻辑备份
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql
说明： 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。
#备份全部数据库
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql
#备份部分数据库
mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
#备份部分表
mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql
#备份多张表 book和account
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql
#备份单表的部分数据
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql
#排除某些表的备份
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
#只备份结构或只备份数据
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
#只备份数据
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备 份存储过程及函数，使用 --events 或 -E 参数来备份事件。
#备份整个atguigu库，包含存储过程及事件
mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql
命令恢复
mysql –u root –p [dbname] < backup.sql
#单库备份中恢复单库
#如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示
mysql -uroot -p < atguigu.sql
#否则需要指定数据库名称，如下所示
mysql -uroot -p atguigu4< atguigu.sql
#全量备份恢复
mysql –u root –p < all.sql
#从全量备份中恢复单库 需要先进行分离
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql> atguigu.sql
#分离完成后我们再导入atguigu.sql即可恢复单个库
#从单库备份中恢复单表
cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use atguigu;
mysql> source class_structure.sql;
mysql> source class_data.sql;
物理备份
直接复制整个数据库
#表的导出 在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
#使用mysqldump命令导出文本文件
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
#mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。
#使用mysql命令导出文本文件
mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"
#将atguigu数据库account表中的记录导出到xml文件，使用--xml参数，具体语句如下。
mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"
#如果要将表数据导出到html文件中，可以使用 --html 选项。然后可以使用浏览器打开。
#表的导入
#使用LOAD DATA INFILE方式导入文本文件
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;
#使用mysqlimport方式导入文本文件
mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'
3171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
