存储引擎

MySQL体系结构

  • 连接层
  • 服务层
  • 引擎层
  • 存储层

存储引擎

简介

存储引擎就是存储数据、建立索引、更新／查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型

1
2
3
4
show create table 表名;
// 查看建表语句，可以查看存储引擎
show variables like '';
// 可以模糊查找字段

指定存储引擎

1
2
3
4
5
6
7
create table 表名(
 字段1 字段1类型 [COMMENT '字段1注释'],
 字段2 字段2类型 [COMMENT '字段2注释'],
 字段3 字段3类型 [COMMENT '字段3注释'],
 ······
 字段n 字段n类型 [COMMENT '字段n注释'],
) ENGINE = INNODB [COMMENT '表注释'];

查看当前数据库支持的存储引擎

1
show engines;

特点

InnoDB

介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎

特点

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键FOREIGNKEY约束，保证数据的完整性和正确性

文件

XXX.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi)、数据和索引。参数：innodb_file_per_table(默认打开，说明每张表都会对应这样一个表空间文件)

1
2
// 若想查看ibd文件，可在cmd中输入以下命令(注意文件路径)
ibd2sdi 表名.ibd

逻辑存储结构

MyISAM

介绍

MyISAM是MySQL早期的默认存储引擎

特点

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

Memory

介绍

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用

特点

内存存放
hash索引（默认）

文件

xoox.sdi：存储表结构信息

区别

选择

  1. InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择
  2. MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的
  3. MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

索引

概述

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

优缺点

结构

二叉树:

顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

红黑树:

大数据量情况下，层级较深，检索速度慢。

Btree(多路平衡查找树)

以一颗最大度数（max-degree）为5(5阶）的b-tree为例（每个节点最多存储4个key,5个指针）

演示网站

B+Tree

以一颗最大度数（max-degree）为4(4阶）b+tree为例

相对于B-Tree区别：

  1. 所有的数据都会出现在叶子节点
  2. 叶子节点形成一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表，红黑树来解决

特点：

  1. Hash索引只能用于对等比较（=,in)，不支持范围查询（between,>,<,）
  2. 无法利用索引完成排序操作
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持：
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的（这意味着当查询中的条件或者数据分布满足一定的要求时，InnoDB可能会选择使用Hash索引来提高查询效率。例如，对于某些特定的查询模式或者数据分布情况，Hash索引可能比B+Tree索引更适合，因为它可以在某些情况下提供更快的查找速度。）

为什么InnoDB存储引擎选择使用B+tree索引结构？

  1. 相对于二叉树，层级更少，搜索效率高
  2. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  3. 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：

  1. 如果存在主键，主键索引就是聚集索引
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

InnoDB主键索引的B+tree高度为多高呢？

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8
高度为2
8n+6(n+1)=16✖1024，算出n约为1170
1171✖16=18736
高度为3
1171 ✖1171✖16=21939856

语法

创建索引

1
create [UNIQUE|FULLTEXT] INDEX index_name on table_name (index_col_name,---);

查看索引

1
show index from table_name;

删除索引

1
drop index index_name on table_name;

性能分析

SQL执行频率

查看当前数据库的INSERT,UPDATE,DELETE,SELECT的访问频次(为后续优化提供依据)

1
show [global | session] status like 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
show variables like 'slow_query_log';
// 将以下两个配置添加到配置文件中
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
// 重新启动
systemctl restart mysqld;
// 查看
cd /var/lib/mysql;
ls;
cat localhost-slow.log; (不一定是这个名字，具体自己查看)
tail -f localhost-slow.log;

profile详情

帮助我们了解时间都耗费到哪里去了

1
2
3
4
5
6
select @@have_profiling;(查看是否支持)
select @@profiling;(默认关闭)
set profiling = 1;
show profiles;(查看每一条SQL的耗时基本情况)
show profile for query query_id;(查看指定query_id的SQL语句各个阶段的耗时情况)
show profile cpu for query query_id;(查看指定query_id的SQL语句CPU的使用情况)

explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL 如何执行SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

1
2
#直接在select语句之前加上关键字explain /desc
explain select 字段列表 from 表名 where 条件;

EXPLAIN 执行计划各字段含义

  1. id
    select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
  2. select_type
    表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION(UNION中的第二个或者后面的查询语句）、SUBQUERY(SELECT/WHERE之后包含了子查询）等
  3. type （）
    表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
  4. possible_key（）
    显示可能应用在这张表上的索引，一个或多个
  5. Key（）
    实际使用的索引，如果为NULL，则没有使用索引
  6. Key_len（）
    表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
  7. rows（）
    MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
  8. filtered
    表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
  9. Extra（）

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列

如果跳跃某一列，索引将部分失效（后面的字段索引失效）

最左边的字段必须存在，与顺序无关

范围查询

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效，如果业务允许，尽量使用>=或<=

索引列运算

不要进行索引列运算，否则会失效

字符串不加引号

会失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引会失效

or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引

SQL提示

在SQL语句中加入一些人为的提示来达到优化的目的

1
2
3
4
// use index:(只是一种建议，是否接收还需MySQL权衡)
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
// ignore index:(忽略这个索引)
// force index:(强制用这个索引)

覆盖索引&回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select * 的使用

在Extra出现

  • using index condition：查找使用了索引，但是需要回表查询数据
  • using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

前缀索引

当字段类型为字符串（varchar,text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

1
create index idx_xooxx on table_name(column(n)) ;

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

1
2
select count(distinct email)/ count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;

单列索引与联合索引

如果存在多个查询条件，推荐使用联合索引

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引
  2. 针对于常作为查询条件（where)、排序 (order by)、分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合素引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  6. 要控制素引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
  7. 如果素引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个
    索引最有效地用于查询

SQL优化

insert优化

  1. 批量插入（500到1000条）
  2. 手动事务提交
  3. 主键顺序插入

如果一次性需要插入大批量数据，可以使用MySQL提供的load指令

1
2
3
mysql --local-infile -u root -p
set global local_infile = 1;
load data local infile '/root/sql1.log' into 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含2-N行数据（如果一行数据过大，会行溢出），根据主键排列

主键乱序插入

页合并

当删除一行，实际上记录并没有被物理删除，只是被标记为删除并且它的空间变得允许被其他记录声明使用

当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%，可以自己设置)，InnoDB会开始寻找最靠近的页，看看是否可以将两个页合并以优化空间使用

主键设计原则

  1. 满足业务需求的情况下，尽量降低主键的长度
  2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
  3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
  4. 业务操作时。避免对主键的修改

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

Using index：通过有序素引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

1
create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
  1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
  2. 尽量使用覆盖索引
  3. 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC)
  4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k)

group by优化

在分组操作时，可以通过索引来提高效率

分组操作时，索引的使用也是满足最左前缀法则的

limit优化

覆盖索引+子查询

1
explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(“）的时候会直接返回这个数，效率很高
  • InnoDB引擎执行count(*）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

优化思路：自己计数

count的几种用法

  1. count（主键）：InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null)
  2. count（字段）：没有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
  3. count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字”1”进去，直接按行进行累加
  4. count (*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按照效率排序，count（字段）<count（主键）<count(1)<count (*)

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁（更新条件要有索引），并且该索引不能失效，否则行锁会升级为表锁

视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上

1
2
3
4
5
6
7
8
9
10
11
12
// 创建视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option];
// 查询
// 查看创建视图语句
show create view 视图名称;
// 查看视图数据
select * from 视图名称 ---;
// 修改
1. create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option];
2. alter view 视图名称[(列名列表)] as select语句 [with[cascaded | local] check option];
// 删除
drop view [if exists] 视图名称[(列名列表)] ---

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED（级联）—— 会给基于的另一个视图加上WITH CASCADED CHECK OPTION

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

  1. 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT(）等）
  2. DISTINCT
  3. GROUP BY
  4. .HAVING
  5. UNION 或者 UNION ALL

作用

  1. 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件
  2. 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
  3. 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响

存储过程

简介

存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用

特点

  • 封装，复用
  • 可以接受参数，也可以返回数据
  • 减少网络交互，效率提升

语法

创建

1
2
3
4
create procedure 存储过程名称([参数列表])
begin
 --SQL语句
end;

调用

1
call 名称([参数]);

查看

1
2
select * from information_schema.routines where routine_schema='数据库名'; --查询指定数据库的存储过程以及状态信息
show create procedure 存储过程名称; --查询某个存储过程的定义

删除

1
drop procedure [if exists] 存储过程名称;

设置SQL结束符

1
delimiter $$

变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL)、会话变量（SESSION)

查看系统变量

1
2
3
show [session|global] variables; --查看所有系统变量
show [session|global] variables like '';
select @@[session|global].系统变量名; --查看指定变量的值

设置系统变量

1
2
set [session|global] 系统变量名 = 值;
set @@[session|global].系统变量名 = 值;

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在／etc/my.cnf中配置

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用”＠变量名”使用就可以。其作用域为当前连接

赋值

1
2
3
4
set @var_name=expr [,@var_name=expr]---;
set @var_name:=expr [,@var_name:=expr]---;
select @var_name:=expr [,@var_name:=expr]---;
select 字段名 into @var_name from 表名;

使用

1
select @var_name;

用户定义的变量无需对其进行声明或初始化，只不过获取的值为NULL

局部变量

局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN.END块

声明

1
declare 变量名 变量类型 [default ---];

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

赋值

1
2
3
set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ---;

if

1
2
3
4
5
6
7
if 条件1 then
 执行语句;
elseif 条件2 then
 执行语句;
else
 执行语句;
end if;

参数

1
2
3
4
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin
 --SQL语句
end;

case

语法1

1
2
3
4
5
case case_value
 when when_value1 then statement_list1
 when when_value2 then statement_list2
 else statement_list
end case;

语法2

1
2
3
4
5
case 
 when search_condition1 then statement_list1
 when search_condition2 then statement_list2
 else statement_list
end case;

while

1
2
3
while 条件 do
 SQL逻辑
end while;

repeat

满足条件时退出循环

1
2
3
4
repeat
 SQL逻辑
 until 条件
end repeat;

loop

LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：

  • LEAVE：配合循环使用，退出循环
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
1
2
3
4
5
[begin_label:] loop
 SQL逻辑
end loop [end_label];
leave label; (break)
iterate label; (continue)

cursor(游标)

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和CLOSE，其语法分别如下

声明游标

1
declare 游标名称 cursor for 查询语句;

打开游标

1
open 游标名称;

获取游标记录

1
fetch 游标名称 into 变量 [,变量];

关闭游标

1
close 游标名称;

条件处理程序

可以用来定义在流程控制程序执行过程中遇到问题时相应的处理步骤

1
2
3
4
5
6
7
8
9
10
11
declare handler_action handler for condition_value [,condition_value] statement;
// declare exit handler for SQLSTATE '02000' close u_cursor;

handler_action
 CONTINUE：继续执行当前程序
 EXIT：终止执行当前程序
condition_value
 SQLSTATE sqlstate_value: 状态码，如 02000
 SQLWARNING：所有以01开头的SQLSTATE代码的简写
 NOT FOUND：所有以02开头的SQLSTATE代码的简写
 SQLEXCEPTION：所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create function 存储函数名称([参数列表])
returns type [characteristic..]
begin
 -SQL语句
 return---;
end;

characteristic说明：
- DETERMINISTIC：相同的输入参数总是产生相同的结果
- NO SQL：不包含SQL语句
- READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

// 调用
select 函数名称(参数);

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

语法

创建

1
2
3
4
5
6
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row --行级触发器
begin
 trigger_stmt;
end;

查看

1
show triggers;

删除

1
drop trigger [schema_name.]trigger_name; --如果没有指定schema_name，默认删除当前数据库的触发器

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

分类

MySQL中的锁，按照锁的粒度分，分为以下三类：

  1. 全局锁：锁定数据库中的所有表
  2. 表级锁：每次操作锁住整张表
  3. 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都
将被阻塞
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

1
2
3
flush tables with read lock; --加锁
mysqldump -h192.168.200.202(自己的主库) -uroot -p1234 demo > demo.sql; --全库逻辑备份(注意：此命令在cmd中执行)
unlock tables; --全局锁释放

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题:

  1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
  2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志 (binlog)，会导致主从延迟

在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份

1
mysqldump --single-transaction -h192.168.200.202(自己的主库) -uroot -p1234 demo > demo.sql; --全库逻辑备份(注意：此命令在cmd中执行)

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中
对于表级锁，主要分为以下三类:

  1. 表锁

    1. 表共享读锁 (read lock)

    2. 表独占写锁 (write lock)

      1
      2
      lock tables 表名--- read/write;
      unlock tables/客户端断开连接

  2. 元数据锁 (meta data lock，MDL)

    MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据(表结构)的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)

    1
    select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

  3. 意向锁

    为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

    1. 意向共享锁 (IS):由语句 select …lock in share mode添加。与表锁共享锁 (read)兼容，与表锁排它锁 (write) 互斥
    2. 意向排他锁 (IX): 由insert、update、delete、select …for update 添加。与表锁共享锁 (read) 及排它锁 (write) 都互斥。意向锁之间不会互斥
    1
    select obiect_schema,obiect_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

  1. 行锁(Record Lock): 锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
  2. 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
  3. 临键锁(Next-Key Lock): 行锁和间隙锁合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

行锁

  1. 共享锁 (S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
  2. 排他锁 (X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

间隙锁&临键锁

默认情况下，lnnoDB在 REPEATABLE READ事务隔离级别运行，innoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁
  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

1
2
select * from stu where age = 3 lock in share mode; --加共享锁
select obiect_schema,obiect_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data locks;

InnoDB引擎

逻辑存储结构

架构

MysQL5.5 版本开始，默认使用lnnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是lnnoDB架构图，左侧为内存结构，右侧为磁盘结构

内存架构

Buffer Pool: 缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型:

  1. free page: 空闲page，未被使用
  2. clean page:被使用page，数据没有被修改过
  3. dirty page:脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致

Change Buffer: 更改缓冲区(针对于非唯一二级索引页)，在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中

Change Buffer的意义是什么?
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更
新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了
ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO

Adaptive Hash index: 自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引警会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引
自适应哈希索引，无需人工干预，是系统根据情况自动完成
参数:

  • adaptive_hash_index

Log Buffer: 日志缓冲区，用来保存要写入到磁盘中的log日志数据 (redo log 、undo log)，默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O
参数:

  • innodb_log_buffer_size: 缓冲区大小

  • innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机

    1: 日志在每次事务提交时写入并刷新到磁盘
    0:每秒将日志写入并刷新到磁盘一次
    2:日志在每次事务提交后写入，并每秒刷新到磁盘一次。

磁盘结构

System Tablespace: 系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含lnnoDB数据字典、undolog等)
参数:

  • innodb_data file_path

File-Per-Table Tablespaces: 每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中
参数:

  • innodb_file_per_table

General Tablespaces:通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。\

1
2
create tablespace xxxx add datafile 'file_name' engine = engine_name;
create table xxx tablespace ts_name; --在创建表时就加上

Undo Tablespaces: 撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undo log日志

Temporary Tablespaces: nnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

Doublewrite Buffer Files: 双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

ib_16384_0.dblwr

ib_16384_1.dblwr

Redo Log: 重做日志，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复
使用。以循环方式写入重做日志文件

ib_logfile0

ib_logfile1

后台线程

  1. Master Thread：核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

  2. IO Thread：在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调

  3. Purge Thread：主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收

  4. Page Cleaner Thread：协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻Master Thread 的工作压力，减少阻塞

1
show engine innodb status;

事务原理

redo log(持久性)

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
该日志文件出两部分组成:重做日志缓冲(redo oa bufer)以及重做日志文件(redo oa fle),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用

undo log(原子性)

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和 MVCC(多版本并发控制)
undolog和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之
亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容
并进行回滚
Undo log销毁:undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC
Undo log存储: undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo og
segment.

MVCC

  • 当前读

    读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
    select … lockin share mode(共享锁)，select… for update、update、insert、delete(排他锁)都是一种当前读

  • 快照读

    简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

    • Read Committed: 每次select，都生成一个快照读
    • Repeatable Read:开启事务后第一个select语句才是快照读的地方
    • Serializable:快照读会退化为当前读

  • MVCC

    全称 Multi-Version Concurrency ontrol，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView

实现原理

记录中的隐藏字段

1
2
3
cd /var/lib/mysql
cd 库名称
ibd2sdi 表名称.ibd

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志

当insert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

undo log版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

readview

ReadView(读视图)是 快照读 SOL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id，ReadView中包含了四个核心字段:

不同的隔离级别，生成Readview的时机不同:

  • READ COMMITTED:在事务中每一次执行快照读时生成ReadView
  • REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

MySQL管理

系统数据库

常用工具

该mysql不是指mysql服务，而是指mysql的客户端工具

1
2
3
4
5
6
7
8
// 语法
 mysql [options] [database]
// 选项
 -u,--user=name   #指定用户名
 -p,--password[=name]  #指定密码
 -h,--host=name  #指定服务器IP或域名
 -P,--port=port  #指定连接端口
 -e,--execute=name  #执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MvSQL数据库再执行，对于一些批处理脚本，这种方式尤其方便

1
mysql -uroot -p123456 db01 -e "select * from stu";

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等

1
2
3
mysqladmin --help
mysqladmin -uroot -p123456 drop 'test01';
mysqladmin -uroot -p123456 version;

mysqlbinlog，由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具

1
2
3
4
5
6
7
8
9
10
11
语法:
 mysqlbinlog [loptions] log-files1 log-files2 ...
选项:
 -d,--database=name 指定数据库名称，只列出指定的数据库相关操作
 -o，--offset=# 忽略掉日志中的前n行命令
 -r,--result-file=name 将输出的文本格式日志输出到指定文件
 -s,--short-form  显示简单格式，省略掉一些信息
 -v        将行事件(数据变更)重构为SQL语句
 -w                   将行事件(数据变更)重构为SQL语句，并输出注释信息
 -start-datatime=date1 --stop-datetime=date2  指定日期间隔内的所有日志
 -start-position=pos1--stop-position=pos2  指定位置间隔内的所有日志

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引

1
2
3
4
5
6
7
8
9
10
11
12
语法:
 mysqlshow [options] [db_name [table_name [col_name]]]
选项:
 --count  显示数据库及表的统计信息(数据库，表均可以不指定)
 -i   显示指定数据库或者指定表的状态信息
示例:
 #查询每个数据库的表的数量及表中记录的数量
 mysqlshow -uroot -p2143 --count
 #查询test库中每个表中的字段书，及行数
 mysqlshow -uroot -p2143 test --count
 #查询test库中book表的详细情况
 mysqlshow -uroot -p2143 test book --count

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
语法:
 mysqldump [options] db _name [tables] > .sql
 mysqldump [options] --database/-B db1 [db2 db3...]
 mysqldump [options]--all-databases/-A
连接选项:
 -u,--user=name    #指定用户名
 -p,--password[=name]  #指定密码
 -h,--host=name    #指定服务器ip或域名
 -P,--port=#     #指定连接端口
输出选项:
 --add-drop-database  #在每个数据库创建语句前加上 drop database 语句
 --add-drop-table  #在每个表创建语句前加上 drop table 语句,默认开启;不开启(--skip-add-drop-table)
 -n,--no-create-db  #不包含数据库的创建语句不包含数据表的创建语句
 -t,--no-create-info  #不包含数据表的创建语句
 -d --no-data   #不包含数据
 -T,--tab=name   #自动生成两个文件:一个.sql文件，创建表结构的语句;一个.xt文件，数据文件
 (mysqldump -uroot -p1234 -T/root 数据库 表)

mysqlimport 是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件

1
2
3
4
5
6
7
语法:
 mysqlimport [options] db_name textfile1 [textfile2....]
示例:
 mysqlimport -uroot -p2143 test /tmp/city.txt

// 如果需要导入sql文件,可以使用mysql中的source 指令
 source /root/xxxxx.sql