mysql存储引擎
mysql体系结构
连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于
TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程
池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务
器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部
分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解
析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，
最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，
这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通
信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库
中的索引是在存储引擎层实现的。
存储层
数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询
日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要
体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
存储引擎的介绍
**存储引擎是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。 **
**存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是 **
**基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果 **
没有指定将自动选择默认的存储引擎。
建表时指定存储引擎
# mysql5.5以后默认存储引擎是INNODB
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = 存储引擎 [ COMMENT 表注释 ] ;
查询当前数据库支持的存储引擎
# 查询当前数据库支持的存储引擎
SHOW ENGINES;
部分存储引擎分类
| 存储引擎 | 是否支持 | 注释 | 是否支持事务 | XA | Savepoints |
|---|---|---|---|---|---|
| MEMORY | YES | 基于哈希，存储在内存中，对临时表很有用 | NO | NO | NO |
| MRG_MYISAM | YES | 相同MyISAM表的集合 | NO | NO | NO |
| CSV | YES | CSV存储引擎 | NO | NO | NO |
| FEDERATED | NO | 联邦MySQL存储引擎 | |||
| PERFORMANCE_SCHEMA | YES | 性能模式 | NO | NO | NO |
| MyISAM | YES | MyISAM存储引擎 | NO | NO | NO |
| InnoDB | DEFAULT | 支持事务、行级锁定和外键 | YES | YES | YES |
| ndbinfo | NO | MySQL集群系统信息存储引擎 | |||
| BLACKHOLE | YES | /dev/null存储引擎(写入的内容将消失) | NO | NO | NO |
| ARCHIVE | YES | 归档存储引擎 | NO | NO | NO |
| ndbcluster | NO | 集群的容错表 | |||
InnoDB
介绍
**InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 **
MySQL 存储引擎。
特点
- DML操作遵循ACID模型，支持事务；
- 行级锁，提高并发访问性能；
- 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结
构（frm-早期的 、sdi-新版的）、数据和索引
参数：innodb_file_per_table
show variables like 'innodb_file_per_table';
- 当innodb_file_per_table设置为ON时（默认值），每个InnoDB表的数据和索引都会存储在单独的文件中。这意味着每个表都有一个独立的数据文件和一个独立的索引文件。这种设置有助于提高性能，因为每个表的数据和索引都可以独立地被访问和管理。此外，它还允许对单个表进行备份和恢复操作，而无需锁定整个数据库。
- 当innodb_file_per_table设置为OFF时，所有InnoDB表的数据和索引都存储在一个共享的文件（通常是名为ibdata1的文件）中。这种设置可以减少磁盘空间的使用，但可能会导致性能下降，因为访问不同表的数据和索引需要额外的查找操作。此外，在这种设置下，对单个表进行备份和恢复操作可能会影响其他表的性能。
- 总之，innodb_file_per_table选项决定了InnoDB存储引擎如何管理和存储表的数据和索引文件，从而影响了数据库的性能和可管理性。
查看sdi信息
ibd2sdi innodb引擎的表名.ibd
逻辑存储结构
- 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
- 段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
- 区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
- 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
- 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。
MyISAM
介绍
MyISAM是MySQL早期的默认存储引擎
特点
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
文件
- xxx.sdi：存储表结构信息
- xxx.MYD: 存储数据
- xxx.MYI: 存储索引
Memory
介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为
临时表或缓存使用
特点
- 内存存放
- hash索引（默认）
文件
- xxx.sdi：存储表结构信息
存储引擎特点
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 支持(5.6版本以后) | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据
实际情况选择多种存储引擎进行组合。
- InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
- MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
索引
索引概述
**介绍 **
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足
特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构
上实现高级查找算法，这种数据结构就是索引
特点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列也是要占用空间的 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如果对表进行行INSERT、UPDATE、DELETE时,效率降低 |
索引结构
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene,Solr,ES |
| 索引 | InnoDB | MyISAM | Menory |
|---|---|---|---|
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式，又可以分为以下几种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
- 聚集索引的叶子节点下挂的是这一行的数据。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
索引语法
创建索引
# 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX 索引名称 ON 表名 (列名,...);
查看索引
# 查看索引
SHOW INDEX FROM 表名;
删除索引
# 删除索引
DROP INDEX 索引名 ON 表名;
SQL性能分析
sql执行频率
# 查看增删改查访问频次
SHOW [SESSEION | GLOBAL] STATUS LIKE 'Com_______';
慢查询日志
说明
- 慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
- MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量slow_query_log
SHOW VARIABLES LIKE 'slow_query_log';
开启慢查询日志
在mysql的配置文件my.cnf当中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间 超过该时间就会被视为慢查询 并记录慢查询日志
long_query_time=10
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log。
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling
参数，能够看到当前MySQL是否支持profile操作：
# 是否支持profile
select @@have_profiling;
可以看到，当前MySQL是支持profile操作的，但是开关是关闭的。可以通过set语句在
session/global级别开启profiling：
查看profiling是否开启
# 查看profiling是否开启
select @@profiling;
开启profiling
# 开启profiling
SET [SESSION | GLOBAL] profiling = 1;
查看每一条SQL的耗时基本情况
show profiles;
查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
explain执行计划
说明
EXPLAIN 或者DESC命令获取MySQL 如何执行SELECT 语句的信息，包括在SELECT 语句执行过程中表如何连接和连接的顺序
语法
# 直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
Explain 执行计划中各个字段的含义
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。 |
| select_type | 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等 |
| type | 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。 |
| possible_key | 显示可能应用在这张表上的索引，一个或多个 |
| key | 实际使用的索引，如果为NULL，则没有使用索引 |
| key_len | 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好 |
| rows | MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。 |
| filtered | 表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好 |
索引的使用
最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)
范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
部分示例如下
# 创建一个联合索引
CREATE [UNIQUE | FULLTEXT] INDEX 表名 (列1,列2,列3);
# 走索引情况 全部条件都在则顺序无关都会走索引
select * from 表名 where 列1 = 值1 and 列2 = 值2 and 列3 = 值3;
select * from 表名 where 列2 = 值2 and 列1 = 值1 and 列3 = 值3;
select * from 表名 where 列3 = 值3 and 列1 = 值1 and 列2 = 值2;
# 若是最左列存在其中存在相邻列则列1和列2都走索引
select * from 表名 where 列1 = 值1 and 列2 = 值2;
# 最左列存在相邻列不存在则列1走索引列3不走索引与顺序无关
select * from 表名 where 列1 = 值1 and 列3 = 值3;
# 最左列不存在则不走索引
select * from 表名 where 列2 = 值2;
select * from 表名 where 列3 = 值3;
select * from 表名 where 列2 = 值2 and 列3 = 值3;
# 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效 列1走索引列2和列3不走索引
select * from 表名 where 列1 > 值1 and 列2 = 值2 and 列3 = 值3;
# 全部都不走索引 最左列做范围查询时<和<=都不走索引
select * from 表名 where 列1 < 值1 and 列2 = 值2 and 列3 = 值3;
# >=和<=时对于最左列<=无效不走索引，其他的列都能走索引但是符号得相同否则后面不同符号的不走索引
select * from 表名 where 列1 = 值1 and 列2 >= 值2 and 列3 >= 值3; # 全部走索引
select * from 表名 where 列1 = 值1 and 列2 >= 值2 and 列3 <= 值3; # 列3不走索引
# 走索引 BETWEEN and 会走索引
select * from 表名 where 列1 between 值1-1 and 值1-2 and 列2 = 值2 and 列3 = 值3;
索引失效情况
索引列运算
不要在索引列上进行运算操作，索引将失效
字符串不加引号
字符串类型字段使用时，不加引号，索引将失效
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会
被用到
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引,主要看数据分布情况
SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优
化操作的目的
- use index：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进
行评估）
# use index 语法
select * from 表名 use index(索引名) [ where 条件 ...];
- ignore index ：忽略指定的索引
# use index 语法
select * from 表名 ignore index(索引名) [ where 条件 ...];
- force index ：强制使用索引
# use index 语法
select * from 表名 force index(索引名) [ where 条件 ...];
覆盖索引
尽量使用覆盖索引，减少select *。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到
查询的所有列在索引或者主键当中已经存在的情况就不需要进行回表查询效率将会提高
索引当中包含查询列名情况如下
索引当中未包含查询列名情况如下
前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
语法
# 指定字符串前n位来建立索引
CREATE INDEX 索引名 on 表名(列名(n));
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，
索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的
# 比值
select count(distinct 列名)/count(*) from 表名;
select count(distinct substring(列名,开始位置,截取长度))/count(*) from 表名;
单列索引和联合索引
- 单列索引：即一个索引只包含单个列
- 联合索引：即一个索引包含了多个列
索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
sql优化
插入数据
insert优化
- 批量插入
insert into 表名(列1,列2,列3,...)values(值1-1,值2-1,值3-1,...),(值1-2,值2-2,值3-2,...),(值1-3,值2-3,值3-3,...);
- 手动控制事务
start transaction;
insert into 表名(列1,列2,列3,...)values(值1-1,值2-1,值3-1,...);
insert into 表名(列1,列2,列3,...)values(值1-2,值2-2,值3-2,...);
insert into 表名(列1,列2,列3,...)values(值1-3,值2-3,值3-3,...);
commit;
- 主键顺序插入
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使
用MySQL数据库提供的load指令进行插入。操作如下：
# 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
# 查看全局参数local_infile的值
SELECT @@local_infile;
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据，加载到表结构中 字段以','分割 行以 '\n' 分割
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
主键优化
数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。
页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行
溢出)，根据主键排列
页合并
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
索引设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
order by优化
MySQL的排序，有两种方式：
Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
创建索引时指定升序降序
# 默认索引排序为asc
create index 索引名称 on 表名(列1 [asc | desc] ,列2 [asc | desc]);
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
查看排序缓冲区的大小
# 查看排序缓存区大小
show variables like 'sort_buffer_size';
# 设置缓冲区的大小 大小是byte字节
SET [SESSION | GLOBAL] sort_buffer_size = 大小;
group by优化
在分组操作中，我们需要通过以下两点进行优化，以提升性能：
- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的。
limit 优化
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查
询形式进行优化
# 通过连表查询对limit进行优化
SELECT a.* from 表名 a , (select 主键 from 表名 order by 主键 LIMIT 起始数据,记录数) b where a.主键 = b.主键;
count优化
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了
| count用法 | 含义 |
|---|---|
| count(主键) | InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null) |
| count(字段) | 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加 |
| count(数字) | InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加 |
| count(*) | InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加 |
按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁
升级为表锁，所以一般数据更新的时候尽量根据索引字段进行更新数据操作
视图
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视
图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上
语法
创建
# 创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ];
查询
# 查询视图
SHOW CREATE VIEW 视图名称;
# 查看视图数据
SELECT * FROM 视图名称 ...... ;
修改
# 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];
# 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |LOCAL ] CHECK OPTION ];
删除
# 删除视图
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL，默认值为 CASCADED
CASCADED
级联：
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图
创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1
LOCAL
本地：
比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创
建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1
视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
- 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者 UNION ALL
作用
简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视
图，从而使得用户不必为以后的操作每次指定全部的条件
安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见
到的数据
数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响
存储过程
介绍
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用
特点
- 封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
- 可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
- 减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。
基本语法
创建
# 创建一个存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
--SQL语句
END;
调用
CALL 名称([参数]);
查看
# 查询指定数据库的存储过程及状态信息 xxx表示数据库名
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
# 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称 ;
删除
# 删除存储过程
DROP PROCEDURE [ IF EXISTS ] 存储过程名称;
在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符 默认结束符是";"。
变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量
系统变量
查看系统变量
# 查看所有系统变量
SHOW [SESSION | GLOBAL ] VARIABLES;
# 可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION | GLOBAL ] VARIABLES LIKE '......';
# 查看指定变量
SELECT @@[SESSION | GLOBAL ] 系统变量名;
设置系统变量
# 设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL]系统变量名 = 值;
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
- 全局变量(GLOBAL): 全局变量针对于所有的会话。
- 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。
用户定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接
赋值
# 方式一 直接赋值
SET @变量 = 值 [, @变量 = 值] ... ;
SET @变量 := 值 [, @变量 := 值] ... ;
# 方式二 将查询的结果赋值给变量
SELECT @变量 := 值 [, @变量 := 值] ... ;
SELECT 字段名 INTO @变量 FROM 表名;
使用
SELECT @变量 ;
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL
局部变量
局部变量 是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块
声明
# 声明局部变量
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
# 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
if
语法
# if语法 在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有
IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;
参数
类型
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
语法
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
-- SQL语句
END;
case
语法一
# 含义： 当case_value的值为when_value1时，执行statement_list1，当值为when_value2时，执行statement_list2，否则就执行statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;
语法二
# 含义：当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2，否则就执行statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;
while
while循环是有循环条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;
repeat
repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。具体语法为：
# 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;
loop
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：
- LEAVE ：配合循环使用，退出循环。
- ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
# 满足条件1则退出循环 满足条件2跳出本次循环
[begin_label:] LOOP
IF 条件1 THEN
LEAVE begin_label;
END IF;
IF 条件2 THEN
ITERATE begin_label;
END IF;
SQL逻辑...
END LOOP [end_label];
游标
游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进
行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下
声明游标
# 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
打开游标
# 打开游标
OPEN 游标名称 ;
获取游标记录
# 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
关闭游标
# 关闭游标
CLOSE 游标名称 ;
条件处理程序
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体
语法为
DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;
handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
游标示例如下
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
declare exit handler for SQLSTATE '02000' close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);
解释示例
/**
案例需求：
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
(id,name,profession)中
**/
create procedure p11(in uage int) //创建一个存储过程 参数为输入参数uage 类型为int
begin //begin 开始语法
declare uname varchar(100); //定义局部变量 uname 类型为varchar长度为100
declare upro varchar(100); //定义局部变量 upro 类型为varchar长度为100
declare u_cursor cursor for select name,profession from tb_user where age <= uage; //定义一个游标 游标名称为u_cursor接收select name,profession from tb_user where age <= uage这个sql的查询结果
declare exit handler for SQLSTATE '02000' close u_cursor;//定义一个条件处理程序 类型是exit退出 条件是 SQLSTATE的状态值为02000 退出前close u_cursor关闭游标
drop table if exists tb_user_pro; // 判断tb_user_pro是否存在存在就删除
create table if not exists tb_user_pro( //判断表不存在就创建表tb_user_pro
id int primary key auto_increment, //id为主键int类型自增长
name varchar(100), //name 为varchar长度100
profession varchar(100) //profession 为varchar长度100
);
open u_cursor; //打开游标
while true do //进行死循环
fetch u_cursor into uname,upro; //获取游标记录赋值给局部变量uname和upro按顺序赋值
insert into tb_user_pro values (null, uname, upro); //进行tb_user_pro的插入数据操作
end while;//结束循环 条件处理程序退出后将不执行下面内容
close u_cursor; //关闭游标
end; //存储过程结束语法
call p11(30); //调用
存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下
/**
characteristic说明：
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句。
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
**/
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
-- SQL语句
RETURN ...;
END ;
在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定 characteristic特性，否则就会报如下错误
示例解析
# 计算从1累加到n的值，n为传入的参数值
create function fun1(n int) //创建存储函数
returns int deterministic //返回类型为int 输入参数类型和返回类型一样
begin //开始语法
declare total int default 0; //定义局部变量
while n>0 do //开始循环 当中n<=0时退出循环
set total := total + n; //每次循环设置局部变量的值totla = total+n;
set n := n - 1; //设置输入参数n =n-1
end while; //结束循环
return total; //返回total
end; //结束语法
select fun1(50); //调用存储函数
触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发
| 触发器类型 | NEW和OLD |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
语法
创建
# 创建触发器
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW -- 行级触发器
BEGIN
具体逻辑
END;
查看
SHOW TRIGGER;
删除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库
示例
create trigger tb_user_insert_trigger //创建tb_user_insert_trigger触发器
after insert on tb_user for each row //tb_user新增数据之后触发
begin //开始语法
insert into user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
(null, 'insert', now(), new.id, concat('插入的数据内容为:
id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',
profession=', NEW.profession)); //往日志表当中插入数据
end; //结束语法
锁
概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂
MySQL中的锁，按照锁的粒度分，分为以下三类：
- 全局锁：锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁：每次操作锁住对应的行数据
全局锁
介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语
句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
语法
加全局锁
# 加全局锁
flush tables with read lock ;
数据备份
# 根据用户名和密码进行数据库备份 注意该语句不只是在sql当中执行而是在各系统的命令窗口执行
mysqldump -uroot –p1234 数据库 > 文件名.sql;
释放锁
# 释放锁
unlock tables;
特点
数据库中加全局锁，是一个比较重的操作，存在以下问题：
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致
性数据备份。
mysqldump --single-transaction -uroot –p1234 数据库 > 文件名.sql;
表级锁
介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁，主要分为以下三类：
- 表锁
- 元数据锁（meta data lock，MDL）
- 意向锁
表锁
对于表锁，分为两类：
表共享读锁（read lock）
加了读锁只能读取该表数据
表独占写锁（write lock）
加了写锁只能当前会话读写该表数据
结论
**读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞 **
其他客户端的写
语法
加锁：lock tables 表名... read/write。
释放锁：unlock tables / 客户端断开连接
元数据锁
介绍
meta data lock , 元数据锁，简写MDL。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。**为了避免DML与DDL冲突，保证读写的正确性。 **
这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)
常见的SQL操作时，所添加的元数据锁：
| 对于SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read / write | SHARED_READ_ONLY / SHARED_NO_READ_WRITE | |
| select 、select … lock in share mode | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| insert 、update、delete、select … for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| alter table … | EXCLUSIVE | 与其他的MDL都互斥 |
查看元数据锁
# 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.meta.data_locks;
意向锁
介绍
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
分类
- 意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
语法
# 查看意向锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
介绍
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类
- 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在 RC、RR隔离级别下都支持
- 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
- 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持
行锁
InnoDB实现了以下两种类型的行锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
- 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁
常见的SQL语句，在执行时，所加的行锁如下
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSET… | 排他锁 | 自动加锁 |
| UPDATE… | 排他锁 | 自动加锁 |
| DELETE… | 排他锁 | 自动加锁 |
| SELECT(正常) | 不加任何锁 | |
| SELECT…LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加上LOCK IN SHARE MODE |
| SELECT…FOR UPDATE | 排他锁 | 需要手动在SELECT之后加OR UPDATE |
查看意向锁及行锁的加锁情况
# 查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
- InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁
间隙锁&临键锁
默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
- 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
- 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会
阻止另一个事务在同一间隙上采用间隙锁。
一般产生间隙锁的条件如下
- 更新（UPDATE）操作：当事务执行UPDATE语句时，如果WHERE条件使用了普通索引或唯一索引，并且锁定了多行记录，那么会触发间隙锁。
- 删除（DELETE）操作：当事务执行DELETE语句时，如果WHERE条件使用了普通索引或唯一索引，并且锁定了多行记录，那么也会触发间隙锁。
- 范围查询（SELECT…FOR UPDATE）：当事务执行范围查询并使用FOR UPDATE子句时，如果WHERE条件中使用了普通索引或唯一索引，并且锁定了多行记录，那么同样会触发间隙锁。
- 插入（INSERT）操作：虽然插入操作本身不会直接产生间隙锁，但插入操作会检查是否存在冲突的间隙锁，如有，则会被阻塞。
- 某些特殊情况下的锁定：在某些特殊情况下，例如使用LOCK IN SHARE MODE或FOR UPDATE子句锁定非唯一索引列时，也可能会触发间隙锁。
InnoDB引擎
逻辑存储结构
表空间
表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
段
段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。段用来管理多个Extent（区）
区
区，表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有64个连续的页
页
页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区
行
行，InnoDB 存储引擎数据是按行进行存放的。
在行中，默认有两个隐藏字段：
Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
架构
概述
MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发 中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构
内存结构
在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer
Buffer Pool(缓冲池)
InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。
在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。
缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
• free page：空闲page，未被使用。
• clean page：被使用page，数据没有被修改过。
• dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。参数设置： show variables like ‘innodb_buffer_pool_size’;
Change Buffer(更改缓冲区)
Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中
二级索引的结构图
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO
Adaptive Hash Index(自适应hash索引)
自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引，但是给我们提供了一个功能就是这个自适应hash索引。因为前面我们讲到过，hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。
InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
**自适应哈希索引，无需人工干预，是系统根据情况自动完成。 **
参数： adaptive_hash_index
Log Buffer
Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
参数:
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
1: 日志在每次事务提交时写入并刷新到磁盘，默认值。
0: 每秒将日志写入并刷新到磁盘一次。2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次
2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次
磁盘结构
System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)
参数：innodb_data_file_path
系统表空间，默认的文件名叫 ibdata1
File-Per-Table Tablespaces
如果开启了innodb_file_per_table开关 ，则每个表的文件表空间包含单个InnoDB表的数据和索引 ，并存储在文件系统上的单个数据文件中
开关参数：innodb_file_per_table ，该参数默认开启
General Tablespaces
通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间
创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE 'file_name' ENGINE = 储存引擎名;
创建表时指定表空间
CREATE TABLE 表名 ... TABLESPACE 指定表空间名;
Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储
undo log日志
Temporary Tablespaces
InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据
Doublewrite Buffer Files
双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据
Redo Log
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用
以循环方式写入重做日志文件，涉及两个文件：
- ib_logfile0
- ib_logfile1
后台线程
在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收
IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read thread | 4 | 负责读操作 |
| Write thread | 4 | 负责写操作 |
| Log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息
show engine innodb status \G;
Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收
Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞
事务原理
事务基础
事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
特性
• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
而对于这四大特性，实际上分为两个部分。其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。而持久性是通过数据库的锁，加上MVCC来保证的
redo log
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。
有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。
那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢?
因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为WAL（Write-Ahead Logging）
undo log
回滚日志，用于记录数据被修改前的信息, 作用包含两个: 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制)
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的
update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚
Undo log销毁：undolog在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
Undo log存储：undolog采用段的方式进行管理和记录，存放在前面介绍的rollback segment 回滚段中，内部包含1024个undo log segment。
MVCC
基本概念
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select … lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读
快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
• Read Committed：每次select，都生成一个快照读
• Repeatable Read：开启事务后第一个select语句才是快照读的地方
• Serializable：快照读会退化为当前读
MVCC
全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView
隐藏字段
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 |
| DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段 |
undo log
介绍
- 回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
- 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
- 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除
最终我们发现，不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录
readview
ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。
ReadView中包含了四个核心字段：
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务ID集合 |
| min_trx_id | 最小活跃事务ID |
| max_trx_id | 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的） |
| creator_trx_id | ReadView创建者的事务ID |
不同的隔离级别，生成ReadView的时机不同：
- READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
- REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
原理分析
RC隔离级别(读已提交)
RC隔离级别下，在事务中每一次执行快照读时生成ReadView
RR隔离级别(可重复读)
RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的
MySQL数据库管理
系统数据库
Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下
| **数据库 ** | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便 DBA 和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图 |
常用工具
mysql
该mysql不是指mysql服务，而是指mysql的客户端工具
语法 ：
mysql [options] [database]
选项 ：
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
-e, --execute=name #执行SQL语句并退出
示例
mysql -uroot –p123456 db01 -e "select * from stu";
mysqladmin
mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并
删除数据库等
# 通过帮助文档查看选项：
mysqladmin --help
部分语法
语法:
mysqladmin [options] command ...
选项:
-u, --user=name #指定用户名
-p, --password[=name] #指定密码
-h, --host=name #指定服务器IP或域名
-P, --port=port #指定连接端口
mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具
语法 ：
mysqlbinlog [options] log-files1 log-files2 ...
选项 ：
-d, --database=name 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# 忽略掉日志中的前n行命令。
-r,--result-file=name 将输出的文本格式日志输出到指定文件。
-s, --short-form 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 指定位置间隔内的所有日志。
mysqlshow
mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引
语法 ：
mysqlshow [options] [db_name [table_name [col_name]]]
选项 ：
--count 显示数据库及表的统计信息（数据库，表 均可以不指定）
-i 显示指定数据库或者指定表的状态信息
示例：
#查询test库中每个表中的字段书，及行数
mysqlshow -uroot -p2143 test --count
#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
mysqldump
mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及
插入表的SQL语句
语法 ：
mysqldump [options] db_name [tables]
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文件，创建表结构的语句；一
个.txt文件，数据文件
mysqlimport/source
mysqlimport
mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件
语法 ：
mysqlimport [options] db_name textfile1 [textfile2...]
示例 ：
mysqlimport -uroot -p2143 test /tmp/city.txt
source
如果需要导入sql文件,可以使用mysql中的source 指令 :
语法 ：
source /root/xxxxx.sql
999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
