深圳网站建设制作设计_阿里云建站论坛网站_搜索引擎优化规则_网络推广引流方式

逻辑架构图

MySQL日志文件

MySQL是通过文件系统对数据索引后进行存储的，MySQL从物理结构上可以分为日志文件和数据及索引 文件。MySQL在Linux中的数据索引文件和日志文件通常放在/var/lib/mysql目录下。MySQL通过日志记 录了数据库操作信息和错误信息。

常用日志文件如下

1. 错误日志：/var/log/mysql-error.log
2. 二进制日志：/var/lib/mysql/mysql-bin
3. 查询日志：general_query.log
4. 慢查询日志：slow_query_log.log
5. 事务重做日志：redo log
6. 中继日志：relay log

可以通过命令查看当前数据库中的日志使用信息：

mysql> show variables like 'log_%';

错误日志：error log

默认开启，错误日志记录了运行过程中遇到的所有严重的错误信息，以及 MySQL每次启动和关闭的详细 信息。错误日志所记录的信息是可以通过log_error和log_warnings配置来定义的。从5.5.7以后不能关 闭错误日志

log_error：指定错误日志存储位置
log-warnings：是否将警告信息输出到错误日志中。
log_warnings 为0， 表示不记录告警信息。
log_warnings 为1， 表示告警信息写入错误日志。
log_warnings 大于1， 表示各类告警信息，例如：有关网络故障的信息和重新连接信息写入 错误日志。

log_error=/var/log/mysql-error.log
log_warnings=2

二进制日志：bin log

默认关闭，需要通过以下配置进行开启。binlog记录了数据库所有的ddl语句和dml语句，但不包括 select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行 时间信息。如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到 binlog日志中。
binlog主要用于实现mysql主从复制、数据备份、数据恢复。

配置中mysql-bin是binlog日志文件的basename，binlog日志文件的完整名称：mysql-bin.000001。

server_id=42
log-bin=mysql-bin

通用查询日志：general query log

默认关闭，由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环 境下会产生大量的信息从而导致不必要的磁盘IO，会影响MySQL的性能的。
如果不是为了调试数据库，不建议开启查询日志。

# 查询通用查询日志变量信息
mysql> show global variables like '%general_log%';

开启方式

#启动开关
general_log={ON|OFF}
#日志文件变量，而general_log_file如果没有指定，默认名是host_name.log
general_log_file=/var/lib/mysql/host_name.log

慢查询日志：slow query log

默认关闭，通过以下设置开启。记录执行时间超过long_query_time秒的所有查询，便于收集查询时间 比较长的SQL语句。

查看阈值

show global status like '%Slow_queries%';
show variables like '%slow_query%';
show variables like 'long_query_time%';

配置慢查询开启

# 开启慢查询日志
slow_query_log=ON
# 慢查询的阈值，单位秒
long_query_time=10
# 日志记录文件
# 如果没有给出file_name值， 默认为主机名，后缀为-slow.log。
# 如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
slow_query_log_file=slow_query_log.log

MySQL数据文件

查看MySQL数据文件：

show variables like '%datadir%';

ibdata文件：使用系统表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

InnoDB存储引擎的数据文件：

• .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息
• .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。

MyISAM存储引擎的数据文件：

• .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息
• .myd文件：主要用来存储表数据信息。
• .myi文件：主要用来存储表数据文件中任何索引的数据树。

Sql语句执行流程

select c_id,first_name,last_name from customer where c_id=14; 

大体来说，MySQL 可以分为 Server层和存储引擎层两部分：

1. Server层
   包括：连接器、查询缓存、分析器、优化器、执行器等
   涵盖 MySQL的大多数核心服务功能
   所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实 现
   比如：存储过程、触发器、视图等
2. 存储引擎层：
   负责数据的存储和提取
   可插拔式存储引擎：InnoDB、MyISAM、Memory 等
   最常用存储引擎是InnoDB
   从MySQL 5.5版本开始，默认存储引擎是InnoDB

第一步：连接到数据库
首先会连接到这个数据库上，这时候接待你的就是连接器

-- 连接命令
mysql -h127.0.0.1 -P3306 -uroot -p

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器 就会自动将它断开。这个时间是由参数 wait_timeout 控制的默认值是 8 小时。

mysql> show processlist;
# 其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

第二步：查缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及 其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句hash之后的值，value 是查询的结果。

• 如果你的查询语句在缓存中，会被直接返回给客户端。
• 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存 中。
  如果查询命中缓存，MySQL 不需要执行后面的复杂操作就可以直接返回结果，效率会很高！但是不建议 使用MySQL的内置缓存功能

查询缓存默认是关闭的状态

# 1）查看是否开启缓存
mysql> show variables like 'query_cache_type';
# 2）查看缓存的命中次数：
mysql> show status like 'qcache_hits';
# 3）开启缓存
在/etc/my.cnf文件中修改“query_cache_type”参数
值为`0或OFF`会禁止使用缓存。
值为`1或ON`将启用缓存，但以`SELECT SQL_NO_CACHE`开头的语句除外。
值为`2或DEMAND`时，只缓存以`SELECT SQL_CACHE`开头的语句。

清空查询缓存
可以使用下面三个SQL来清理查询缓存：

FLUSH QUERY CACHE; # 清理查询缓存内存碎片。
RESET QUERY CACHE; # 从查询缓存中移出所有查询。
FLUSH TABLES; # 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

为什么不建议使用MySQL的查询缓存？

因为查询缓存往往弊大于利

• 成本高：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清 空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。
• 命中率不高：对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一 张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓 存。
• 功能并不如专业的缓存工具更好：redis、memcache、ehcache...

注意：MySQL 8.0 版本直接将查询缓存的整块功能删掉了

第三步：分析SQL语句

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。客户端程序发送过来的请求，实际上只 是一个字符串而已，所以MySQL服务器程序首先需要对这个字符串做分析，判断请求的语法是否正确， 然后从字符串中将要查询的表、列和各种查询条件都提取出来，本质上是对一个SQL语句编译的过程， 涉及词法解析、语法分析、预处理器等。

➢ 词法分析：词法分析就是把一个完整的 SQL 语句分割成一个个的字符串
➢ 语法分析：语法分析器根据词法分析的结果做语法检查，判断你输入的SQL 语句是否满足 MySQL 语法。
➢ 预处理器：预处理器则会进一步去检查解析树是否合法，比如表名是否存在，语句中表的列是否存 在等等，在这一步MySQL会检验用户是否有表的操作权限。

1）词法分析：

比如：这条简单的SQL语句，会被分割成10个字符串

# 分隔前
select c_id,first_name,last_name from customer where c_id=14;
# 分隔后
select，c_id,first_name,last_name，from，customer，where，c_id，=，14

MySQL 同时需要识别出这个SQL语句中的字符串分别是什么，代表什么。
➢ 把"select"这个关键字识别出来，这是一个查询语句
➢ 把“customer”识别成“表名 customer”
➢ 把“c_id识别成“列 c_id”。

2）语法分析：

如果语法正确就会根据 MySQL语法规则与SQL 语句生成一个数据结构，这个数据结构我们把它叫做解 析树。

3）预处理器：

预处理器则会进一步去检查解析树是否合法，比如表名是否存在，语句中表的列是否存在等等，在这一 步MySQL会检验用户是否有表的操作权限。
预处理之后会得到一个新的解析树，然后调用对应执行模块

第四步：优化SQL语句

优化器顾名思义就是对查询进行优化。作用是根据解析树生成不同的执行计划，然后选择最优的执行计 划。

MySQL 里面使用的是基于成本模型的优化器，哪种执行计划Explain执行时成本最小就用哪种。而且它 是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。

查看上次查询成本开销，默认值是0

show status like 'Last_query_cost';

优化器可以做哪些优化呢？

1. 当有多个索引可用的时候，决定使用哪个索引
2. 在一个语句有多表关联（join）的时候，决定各个表的连接顺序，以哪个表为基准表

第五步：执行SQL语句

1）判断执行权限 开始执行的时候，要先判断一下你对这个表customer有没有执行查询的权限，如果没有，就会返回没有 权限的错误。

2）调用存储引擎接口查询

MySQL的存储引擎

存储引擎种类

引擎怎么选择

归纳为一句话：除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该选 择InnoDB引擎。也就是说，大部分情况下都选择InnoDB。

InnoDB和MyISAM存储引擎区别

存储引擎查看与设置

# 查看支持的存储引擎
> show engines# 使用其他引擎，在mysql中默认使用InnoDB引擎，一个数据库中不同的表可以使用不同的引擎。
create table t_myisam(a int primary key, b int) engine=myisam;

InnoDB架构图

上图详细展示了InnoDB存储引擎的体系架构，从图中可见，InnoDB存储引擎由内存结构、磁盘结构两 部分组成。

内存结构

InnoDB 内存结构主要分为如下四个区域：

1. Buffer Pool 缓冲池
2. Change Buffer 修改缓冲
3. Adaptive Hash Index 自适应索引
4. Log Buffer 日志缓冲

缓冲池(Buffer Pool)

缓冲池Buffer Pool 用于加速数据的访问和修改，通过将热点数据缓存在内存的方法，最大限度地减少磁 盘 IO，加速热点数据读写

# 查看innodb存储引擎状态，包含缓冲池、修改缓冲、自适应哈希状态信息、日志缓冲等信息...
mysql> show engine innodb status;
# 查看InnoDB的Buffer Pool大小
mysql> show variables like 'innodb_buffer_pool_size';

修改缓冲(Change Buffer)

Change Buffer（在 MySQL 5.6 之前叫 insert buffer，简称 ibuf ）是 InnoDB 5.5 引入的一种优化策 略。Change Buffer 用于加速非热点数据中二级索引的写入操作。由于二级索引数据的不连续性，导致 修改二级索引时需要进行频繁的磁盘 IO 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同 时将写操作录入 redo log 中，在缓冲到一定量或系统较空闲时进行 merge 操作将修改写入磁盘中。 Change Buffer 在系统表空间中有相应的持久化区域。

Change Buffer 大小默认占 Buffer Pool 的 25%，最大50%，在引擎启动时便初始化完成。其物理结构 为一棵名为 ibuf 的 B Tree。

二级索引就是辅助索引，除了聚簇索引之外的所有索引都是二级索引。
聚簇索引也叫聚集索引，索引组织表，指的是一种数据存储方式，指数据与索引的数据结构存储在 一起。如 InnoDB 的主键索引中所有叶子节点都存储了对应行的数据。因为数据肯定只是存储在 一个地方，所以一个表只能有一个聚集索引。

自适应哈希索引(AHI)

自适应哈希索引（Adaptive Hash Index，AHI）用于实现对于热数据页的一次查询。是建立在索引之上 的索引！使用聚簇索引进行数据页定位的时候需要根据索引树的高度从根节点走到叶子节点，通常需要 3 到 4 次查询才能定位到数据。InnoDB 根据对索引使用情况的分析和索引字段的分析，通过自调优 Self-tuning的方式为索引页建立或者删除哈希索引。

AHI 的大小为 Buffer Pool 的 1/64，在 MySQL 5.7 之后支持分区，以减少对于全局 AHI 锁的竞争，默认 分区数为 8。

AHI 所作用的目标是频繁查询的数据页和索引页，而由于数据页是聚簇索引的一部分，因此 AHI 是建立 在索引之上的索引，对于二级索引，若命中 AHI，则将直接从 AHI 获取二级索引页的记录指针，再根据 主键沿着聚簇索引查找数据；若聚簇索引查询同样命中 AHI，则直接返回目标数据页的记录指针，此时 就可以根据记录指针直接定位数据页。

# 查看innodb存储引擎状态，包含自适应哈希状态信息
mysql> show engine innodb status;
# 查看是否开启自适应哈希配置，默认是开启的
mysql> show variables like 'innodb_adaptive_hash_index';

日志缓冲(Log Buffer)

InnoDB 使用 Log Buffer 来缓冲日志文件的写入操作。内存写入加上日志文件顺序写的特点，使得 InnoDB 日志写入性能极高。

对于任何修改操作，都将录入诸如 redo log 与 undo log 这样的日志文件中，因此日志文件的写入操作 非常频繁，却又十分零散。这些文件都存储在磁盘中，因此日志记录将引发大量的磁盘 IO。Log Buffer 将分散的写入操作放在内存中，通过定期批量写入磁盘的方式提高日志写入效率和减少磁盘 IO。

磁盘结构之表空间

在磁盘中，InnoDB 将所有数据都逻辑地存放在一个空间中，称为表空间（Tablespace）。表空间由段 （Segment）、区（extent）、页（Page）组成。

• 开启独立表空间innodb_file_per_table=1，每张表的数据都会存储到一个独立表空间，即 表 名.ibd 文件
• 关闭独占表空间innodb_file_per_table=0，则所有基于InnoDB存储引擎的表数据都会记录到系统 表空间，即 ibdata1 文件

表空间是 InnoDB 物理存储中的最高层，目前的表空间类别包括：

• 系统表空间（System Tablespace）
• 独立表空间（File-per-table Tablespace）
• 通用表空间（General Tablespace）
• 回滚表空间（Undo Tablespace）
• 临时表空间（The Temporary Tablespace）

系统表空间

独立表空间

独立表空间用于存放每个表的数据和索引。其他类型的信息，如：回滚日志、双写缓冲区、系统事务信 息、修改缓冲等仍存放于系统表空间内。因此即使用了独立表空间，系统表空间也会不断增长。在5.7版 本中默认开启

开启独立表空间（File-per-table TableSpace）（ innodb_file_per_table=ON ）之后，InnoDB 会为 每个数据库单独创建子文件夹，数据库文件夹内为每个数据表单独建立一个表空间文件 table.ibd 。 同时创建一个 table.frm 文件用于保存表结构信息。
每个独立表空间的初始大小是 96KB。

通用表空间

通用表空间（General Tablespace）是一个由 CREATE TABLESPACE 命令创建的共享表空间，创建时必 须指定该表空间名称和 ibd 文件位置，ibd 文件可以放置于任何 MySQL 有权限的地方。该表空间内可以 容纳多张数据表，同时在创建时可以指定该表空间所使用的默认引擎。

通用表空间存在的目的是为了在系统表空间与独立表空间之间作出平衡。系统表空间与独立表空间中的 表可以向通用表空间移动，反之亦可，但系统表空间中的表无法直接与独立表空间中的表相互转化

 Undo 表空间

Undo TableSpace 用于存放一个或多个 undo log 文件。默认 undo log 存储在系统表空间中，MySql 5.7中支持自定义 Undo log 表空间并存储所有 undo log。一旦用户定义了 Undo Tablespace，则系统 表空间中的 Undo log 区域将失效。对于 Undo Tablespace 的启用必须在 MySQL 初始化前设置， Undo Tablespace 默认大小为 10MB。Undo Tablespace 中的 Undo log 表可以进行 truncate 操作。

临时表空间

MySQL 5.7 之前临时表存储在系统表空间中，这样会导致 ibdata 在使用临时表的场景下疯狂增长。5.7 版本之后 InnoDB 引擎从系统表空间中抽离出临时表空间（Temporary Tablespace），用于独立保存 临时表数据及其回滚信息。该表空间文件路径由 innodb_temp_data_file_path 指定，但必须继承 innodb_data_home_dir 。

磁盘结构之存储

段【Segment】

表空间由各个段（Segment）组成，创建的段类型分为数据段、索引段、回滚段等。由于 InnoDB 采用 聚簇索引与 B+ 树的结构存储数据，所以事实上数据页和二级索引页仅仅只是 B+ 树的叶子节点，因此数 据段称为 Leaf node segment，索引段其实指的是 B+ 树的非叶子节点，称为 Non-Leaf node segment。一个段会包含多个区，至少会有一个区，段扩展的最小单位是区。

• 数据段称为 Leaf node segment
• 索引段称为 Non-Leaf node segment

区【Extent】

区（Extend）是由连续的页组成的空间，大小固定为 1MB，由于默认页大小为 16K，因此一个区默认 存储 64 个连续的页。如果页大小调整为 4K，则 256 个连续页组成一个区。为了保证页的连续性， InnoDB 存储引擎会一次从磁盘申请 4 ~ 5 个区。

页【Page】

页（Page）是 InnoDB 的基本存储单位，每个页大小默认为 16K，从 InnoDB1.2.x 版本开始，可通过 设置 innodb_page_size 修改为 4K、8K、16K。InnoDB 首次加载后便无法更改。

# 查看MySQL页大小
show variables like 'innodb_page_size';

MySQL规定一个页上最少存储2个数据项。如果向一个页插入数据时，这个页已经满了，就会从区中分 配一个新页。如果向索引树叶子节点中间的一个页中插入数据，如果这个页是满的，就会发生页分裂

操作系统读写磁盘最小单位也是页，当然此页非毕页！Linux的页一般是4K，通过命令查看：

# 默认 4096 4K
getconf PAGE_SIZE

由此可知，InnoDB从磁盘中读取一个数据页，操作系统会分4次从磁盘文件中读取数据到内存。写入也 是一样的，需要分4次从内存写入到磁盘中。

行【Row】

InnoDB的数据是以行为单位存储的，1个页中包含多个行。在MySQL5.7中，InnoDB提供了4种行格 式：Compact、Redundant、Dynamic和Compressed行格式，Dynamic为MySQL5.7默认的行格式。

创建表时可以指定行格式：

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;
#修改表的行格式
ALTER TABLE tablename ROW_FORMAT=行格式名称;
#修改默认行格式
SET GLOBAL innodb_default_row_format=DYNAMIC;
#查看表行格式
SHOW TABLE STATUS LIKE 't1';

内存数据落盘

内存数据落盘要考虑的核心问题：高性能写入数据，同 时保证数据的绝对安全性！

脏页落盘

什么是脏页？
对于数据库中页的修改操作，则首先修改在缓冲区中的页，缓冲区中的页与磁盘中的页数据不一致，所 以称缓冲区中的页为脏页。然后再以一定的频率将脏页刷新到磁盘上。页从缓冲区刷新回磁盘的操作并 不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

怎么确保日志就能安全的写入系统呢？

• 为了确保每次日志都写入到redo日志文件，在每次将redo日志缓冲写入redo日志后，调用一次 fsync操作，将缓冲文件从文件系统缓存中真正写入磁盘。

这样做不就等同于数据直接写入磁盘吗？

• redo日志不会记录完整的一页数据，因为这样日志太大，它只会记录那次（sequence）如何操作 了（update,insert）哪页(page)的哪行(row) 日志是顺序写入，而数据是随机写入。顺序写入效率更高 日志也不是改一条写一条，而是采用redo 日志落盘策略来兼顾安全性与性能！ 可以通过 innodb_flush_log_at_trx_commit 来控制redo日志刷新到磁盘的策略。

Redo日志落盘

Log Buffer写入磁盘的时机由参数 innodb_flush_log_at_trx_commit 控制，此属性控制每次事务提交时 InnoDB的行为。是InnoDB性能调优的一个基础参数，涉及InnoDB的写入性能和数据安全性。默认1， 表示事务提交后立即落盘。

# 查看写入时机参数配置
show VARIABLES like 'innodb_flush_log_at_trx_commit';

设置为1时，安全性最好但写入效率比较低。如果没有设置为1，是无法满足ACID的D持久性由于MySQL 执行刷新操作fsync() 是阻塞的，所以磁盘刷新速度比较慢，如果开启了1MySQL性能会下降。0是性能 最好的模式，但是会存在丢失数据的风险。2是介于1和0之间的一个选择，数据的安全性会依赖于操作 系统是否稳定。在配置这一项的时候需要慎重考虑。

CheckPoint检查点机制

Checkpoint要做的事情是将缓冲池中的脏页数据刷到磁盘上。CheckPoint决定了脏页落盘的时机、条件 及脏页的选择，不同的CheckPoint做法并不相同

Double Write双写

脏页落盘出现的问题：写失效

Doublewrite其实就是写两次，解决写失效问题，需要用到Doublewrite机制，简单来说就是在redo日 志前，对需要写入的页的做个副本，当写失效发生时，通过页的副本来还原该页再重做，这就是所谓的 double write。写失效后redo日志也是无法进行恢复的，因为redo日志记录的是对页的物理修改。

事务底层原理

丢失更新问题

两个事务针对同一数据进行修改操作时会丢失更新，这个现象称之为丢失更新问题

解决方案一：基于锁并发控制LBCC

使用基于锁的并发控制LBCC（Lock Based Concurrency Control）可以解决上述问题。

查询总额事务会对读取的行加锁，等到操作结束后再释放所有行上的锁。因为用户A的存款被锁，导致 转账操作被阻塞，直到查询总额事务提交并将所有锁都释放

 这种方案比较简单粗暴，就是一个事务去读取一条数据的时候，就上锁，不允许其他事务来操作。假如 当前事务只是加读锁，那么其他事务就不能有写锁，也就是不能修改数据；而假如当前事务需要加写 锁，那么其他事务就不能持有任何锁。总而言之，能加锁成功，就确保了除了当前事务之外，其他事务 不会对当前数据产生影响，所以自然而然的，当前事务读取到的数据就只能是最新的，而不会是快照数 据

解决方案二：基于版本并发控制MVCC

当然使用版本的并发控制MVCC（Multi Version Concurrency Control）机制也可以解决这个问题。

查询总额事务先读取了用户A的账户存款，然后转账事务会修改用户A和用户B账户存款，查询总额事务 读取用户B存款时不会读取转账事务修改后的数据，而是读取本事务开始时的副本数据【快照数据】。

MVCC实现原理【InnoDB】

MVCC全称叫多版本并发控制，是RDBMS常用的一种并发控制方法，用来对数据库数据进行并发访问， 实现事务。核心思想是读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突非常重要，极大 的增加了系统的并发性能，这也是为什么几乎所有的RDBMS，都支持MVCC的原因。

MVCC 实现原理关键在于数据快照，不同的事务访问不同版本的数据快照，从而实现事务下对数据的隔 离级别。虽然说具有多个版本的数据快照，但这并不意味着必须拷贝数据，保存多份数据文件（这样会 浪费存储空间），InnoDB通过事务的Undo日志巧妙地实现了多版本的数据快照。

MVCC 的实现依赖与Undo日志 与 Read View 。

InnoDB下的表有默认字段和可见字段，默认字段是实现MVCC的关键，默认字段是隐藏的列。默认字段 最关键的两个列，一个保存了行的事务ID，一个保存了行的回滚指针。每开始新的事务，都会自动递增 产生一个新的事务id。事务开始后，生成当前事务影响行的ReadView。当查询时，需要用当前查询的事 务id与ReadView确定要查询的数据版本

Undo日志

Redo日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操 作，这时就需要undo。因此在对数据库进行修改时，InnoDB存储引擎不但会产生Redo，还会产生一定 量的Undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条Rollback语句请求 回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。在多事务读取数据时，有了Undo日志 可以做到读不加锁，读写不冲突。

Undo存放在数据库内部的一个特殊段（segment）中，这个段称为Undo段（undo segment）。Undo 段位于系统表空间内，也可以设置为Undo表空间。

Undo日志保存了记录修改前的快照。所以，对于更新和删除操作，InnoDB并不是真正的删除原来的记 录，而是设置记录的delete mark为1。因此为了解决数据Page和Undo日志膨胀问题，则需要回收机制 进行清理Undo日志。

根据行为的不同Undo日志分为两种： Insert Undo Log 和 Update Undo Log

1）Insert Undo日志：是在Insert操作中产生的Undo日志

Insert 操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以 Insert Undo Log 可以在 事务提交后直接删除而不需要进行回收操作。

2）Update Undo日志 ：是Update或Delete 操作中产生的Undo日志

Update操作会对已经存在的行记录产生影响，为了实现MVCC多版本并发控制机制，因此Update Undo 日志不能在事务提交时就删除，而是在事务提交时将日志放入指定区域，等待 Purge 线程进行最后的删 除操作。

ReadView

MVCC的核心问题就是：判断一下版本链中的哪个版本是当前事务可见的！

• 对于使用 RU 隔离级别的事务来说，直接读取记录的最新版本就好了，不需要Undo log。
• 对于使用 串行化 隔离级别的事务来说，使用加锁的方式来访问记录，不需要Undo log。
• 对于使用 RC 和 RR 隔离级别的事务来说，需要用到undo 日志的版本链。

1）什么是ReadView？

ReadView是张存储事务id的表，主要包含当前系统中有哪些活跃的读写事务，把它们的事务id放到一个 列表中。结合Undo日志的默认字段【事务trx_id】来控制那个版本的Undo日志可被其他事务看见。

四个列：
m_ids：表示在生成ReadView时，当前系统中活跃的读写事务id列表
m_low_limit_id：事务id下限，表示当前系统中活跃的读写事务中最小的事务id，m_ids事务列表 中的最小事务id
m_up_limit_id：事务id上限，表示生成ReadView时，系统中应该分配给下一个事务的id值

m_creator_trx_id：表示生成该ReadView的事务的事务id

2）ReadView怎么产生，什么时候生成？

• 开启事务之后，在第一次查询(select)时，生成ReadView
• RC 和 RR 隔离级别的差异本质是因为MVCC中ReadView的生成时机不同，详细生成时机在案例中 分析

3）如何判断可见性？

开启事务执行第一次查询时，首先生成ReadView，然后依据Undo日志和ReadView按照判断可见性， 按照下边步骤判断记录的版本链的某个版本是否可见。

MVCC下的读操作

在MVCC并发控制中，读操作可以分成两类：快照读 (Snapshot Read)与当前读 (Current Read)

• 快照读：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。刚才案例中都是快照读。
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并 发修改这条记录。

当前读与快照读

快照读也就是一致性非锁定读(Consistent Nonlocking Read)是指InnoDB存储引擎通过多版本控制 (MVCC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作，这时读取操 作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个最新可见快照。ReadView的读取 操作就是快照读；

快照读：简单的select操作，属于快照读，不加锁。

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode; # 加读锁
select * from table where ? for update;# 加写锁
insert into table values (…);# 加写锁
update table set ? where ?;# 加写锁
delete from table where ?;# 加写锁
# 所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发
事务不能修改当前记录，对读取记录加锁。
# 其中，除了第一条语句，对读取记录加读锁外，其他的操作都加的是写锁。

Mysql索引

简介

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目 录，能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能 存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚簇索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明， 默认都是使用B+树结构组织的索引。

优势和劣势

索引的类型

按照索引列的数量分类：

• 单列索引：索引中只有一个列。
• 组合索引：使用2个以上的字段创建的索引。

单列索引

• 主键索引：索引列中的值必须是唯一的不允许有空值。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

ALTER TABLE table_name ADD INDEX index_name (column_name);

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。

CREATE UNIQUE INDEX index_name ON table(column_name);

• 全文索引：只能在文本类型CHAR，VARCHAR，TEXT类型字段上创建全文索引。字段长度比较大 时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和 InnoDB中都可以使用全文索引

全文搜索时候，全文索引一般很少使用，数据量比较少或者并发度低的时候可以用。但是数据 量大或者并发度高的时候一般是用专业的工具Lucene，ES，Solr

#创建表时，创建全文索引
CREATE TABLE `t_fulltext` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#创建全文索引
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX `idx_content`(`content`);
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('1','Mention');
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('2','Vincent hero man');
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('3','Benson');
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('4','Carol');
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('5','yilia');
INSERT INTO `t_fulltext` (`id`,`content`) VALUES ('6','lock and lock');

可以使用MATCH() ... AGAINST语法执行全文搜索

SELECT * FROM t_fulltext WHERE MATCH(content) AGAINST('Vincent');

• 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在 空间索引这方面遵循OpenGIS几何数据模型规则。

• 前缀索引：在文本类型如CHAR，VARCHAR，TEXT类列上创建索引时，可以指定索引列的长度， 但是数值类型不能指定。

ALTER TABLE table_name ADD INDEX index_name (column1(length));

组合索引

• 组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。
• 一般情况下，建议使用组合索引代替单列索引（主键索引除外)

ALTER TABLE table_name ADD INDEX index_name (column1,column2);

删除索引

DROP INDEX index_name ON table

查看索引

SHOW INDEX FROM table_name

索引基本需求

索引的数据结构，至少需要支持两种最常用的查询需求：

1. 等值查询：根据某个值查找数据，比如： select * from t_user where age=76;
2. 范围查询：根据某个范围区间查找数据，比如： select * from t_user where age>=76 and age<=86;
3. 排序
4. 分组

同时需要考虑时间和空间因素：性价比高
在执行时间方面，我们希望通过索引，查询数据的时间尽可能小；
在存储空间方面，我们希望索引不要消耗太多的内存空间和磁盘空间。

索引的数据结构

Hash表

二叉查找树

红黑树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个 子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很 高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。

B树：改进二叉树，为多叉树

B+树：改进B树，非叶子节点不存储数据

索引使用口诀
全值匹配我最爱，最左前缀要遵守。
带头大哥不能死，中间兄弟不能断。
索引列上不计算，范围之后全失效。
Like百分写最右，覆盖索引不写星。
不等空值还有OR，索引失效要少用。

组合索引创建原则

1. 频繁出现在where条件中的列，建议创建组合索引。
2. 频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。 order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的。
3. 常出现在select语句中的列，也建议创建组合索引（与覆盖索引有关系！）。

回表

回表，顾名思义就是回到表中，也就是先通过普通索引扫描出数据所在的行，再通过行主键ID 取出索引中未包含的数据。所以回表的产生也是需要一定条件的，如果一次索引查询就能获得所有的select 记录就不需要回表，如果select 所需获得列中有其他的非索引列，就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树。

索引创建原则

1. 频繁出现在where 条件字段，order排序，group by分组字段
2. select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
3. 多表join关联查询，on字段两边的字段都要创建索引

索引优化建议

创建原则：组合索引应该把频繁用到的列、区分度高的值放在前面。频繁使用代表索引的利用率 高，区分度高代表筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围

索引失效

• 最左前缀匹配原则
• 不在索引列上做任何操作【计算、函数、类型转换】，会导致索引失效，转而使用全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引【只访问索引的查询，索引列和查询列一致】，减少使用select *
• 不等于【!= 或 <>】，索引会失效
• is null，is not null，索引会失效
• like以通配符开头，索引会失效
• 字符串不加单引号，索引会失效
• 少用or，用它来连接时，索引会失效