MySQL深入理解学习笔记

一、MySQL架构体系

1.网络连接层 ：提供与Mysql服务器建立的支持。

2.服务层

• 连接池：负责存储和管理客户端与数据库的连接，一个县城负责管理一个连接。
• 系统管理和控制工具：备份恢复、安全管理、集群管理等。
• SQL接口：用于接受客户端发送的SQL命令，并返回用户需要查询的结果。如：DML、DDL、存储过程、视图、触发器。
• 解析器：负责将请求的SQL解析生成一个"解析树"，然后根据一些MySQL规则进一步检查解析树是否合法。
• 查询优化器：当"解析树"通过解析器语法检查后，将由优化器将其转化成执行计划，然后与存储引擎交互。
• 缓存：缓存机制是由一系列小缓存组成，如：表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以去查询缓存中取数据。

3.存储引擎层 ：负责MySQL中数据的存储与提取，与底层系统文件进行交互。服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽不同存储引擎之间的差远。比较常见的存储引擎如：MyISAM、InnoDB。

4.系统文件层 ：负责将数据库的数据和日志存储在文件系统上，并完成与存储引擎的交互，是文件的物理存储层。主要包括日志文件，数据文件，配置文件，pid文件，socket文件等。

• 日志文件：包含：错误日志、通用查询日志、二进制日志、慢查询日志。

• 配置文件：存放MySQL所有配置信息文件，比如my.cnf、my.ini。

• 数据文件：包含：

  • db.opt文件（记录这个库使用的字符集和校验规则）
  • frm文件（存储与表相关的元数据，包括表结构定义，每张表都会有一个frm文件）
  • MYD文件（MyISAM存储引擎专用，存放表数据，每张表都会有一个MYD文件）
  • MYI文件（MyISAM存储引擎专用，存放索引信息，每张表都会有一个MYI文件）
  • ibd文件和IBDATA文件（存放InnoDB数据文件包含索引）
  • ibdata1文件（系统表空间数据文件，存储表元数据、undo log日志）
  • ib_logfile0、ib_logfile1文件（redo log日志文件）

• pid文件：应用在Unix/Linux环境下的进程文件。

• socket文件：应用在Unix/Linux环境下，用户可通过socket连接MySQL。

二、MySQL运行机制

• ①建立连接：通过客户端/服务器通信协议与MySQL建立连接

  通讯机制：

  • 全双工：能同时发送和接收数据。
  • 半双工：某一时刻，要么发送数据，要么接收数据，不能同时。
  • 单工：只能发送数据或接收数据。

• ②查询缓存：这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

• ③解析器：将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

• ④查询优化器：根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最
  优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

• ⑤查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存中，以后若有相同的 SQL 语句执行则直接返回结果。

三、MySQL存储引擎及常用存储引擎对比

存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引擎。

可使用show engines命令进行查看

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

5.5之前默认采用MyISAM存储引擎，5.5之后默认采用InnoDB存储引擎。

3.1.InnoDB和MyISAM对比

InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，具体区别如下：

• 事务和外键

  InnoDB：支持事务和外键，具有安全性和完整性，适合大量insert或update操作
  MyISAM：不支持事务和外键，它提供高速存储和检索，适合大量select查询操作

• 锁机制

  InnoDB：支持行锁。基于索引来加锁实现。
  MyISAM：支持表锁。

• 索引结构

  InnoDB：聚集索引（聚簇索引）

  MyISAM：非聚集索引（非聚簇索引）

• 并发处理能力

  MyISAM：使用表锁，写操作并发率低，读写阻塞
  InnoDB：读写阻塞可以与隔离级别有关，采用多版本并发控制（MVCC）来支持高并发

• 存储文件

  InnoDB：一个.frm表结构文件，一个.ibd数据文件。最大支持64TB
  MyISAM：一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。最大支持256TB

• 适用场景

  InnoDB：需要事务支持、较高并发能力、数据更新频繁、数据一致性要求高

  MyISAM：不需要事务支持、并发较低、数据更新不频繁、数据一致性要求不高

3.2.InnoDB存储结构

• InnoDB内存结构：包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大组件

  • Buffer Pool：缓冲池，简称BP。以Page页为单位，默认16K，底层采用链表数据结构管理Page，在InnoDB访问表记录和索引时会在Page页中缓冲，减少IO操作，提升效率。

    • Page管理机制，根据状态分为三种类型：

      • free page ： 空闲page，未被使用
      • clean page：被使用page，数据没有被修改过
      • dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致

      针对上述三种page类型，InnoDB通过三种链表结构来维护和管理

      • free list ：表示空闲缓冲区，管理free page
      • flush list：表示需要刷新到磁盘的缓冲区，管理dirty page
      • lru list：表示正在使用的缓冲区，管理clean page和dirty page

    • 改进型LRU算法维护

      • 普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰

      • 改性LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

    • Buffer Pool配置参数

      • show variables like '%innodb_page_size%'; //查看page页大小

      • show variables like '%innodb_old%'; //查看lru list中old列表参数

      • show variables like '%innodb_buffer%'; //查看buffer pool参数

        建议：将innodb_buffer_pool_size设置为总内存大小的60%-80%

  • Change Buffer：写缓冲区，简称CB。在进行DML操作是，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再讲数据合并到BP中。

    • 占用BufferPool空间，默认占25%，最大允许占50%，可根据读写业务量来
      进行调整。参数innodb_change_buffer_max_size;

  • Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。

  • Log Buffer：日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。主要用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。

• InnoDB磁盘结构：包括Tablespaces，InnoDB Data Dictionary，Doublewrite Buffer、Redo Log和Undo Logs。

  • Tablespaces（表空间）：用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间等多种类型
    • 系统表空间：包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。
    • 独立表空间：独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。
    • 通用表空间：通过create tablespace语法创建的共享表空间。
    • 撤销表空间：撤销表空间由一个或多个包含Undo日志文件组成。
    • 临时表空间：分为session temporary tablespaces 和global temporary tablespace两种。
      • session temporary tablespaces：存储的是用户创建的临时表和磁盘内部的临时表。
      • global temporary tablespace储存用户临时表的回滚段。
  • InnoDB Data Dictionary（数据字典）：InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。
  • Doublewrite Buffer（双写缓冲区）：位于系统表空间，是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份。
  • Redo Log（重做日志）：重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。
  • Undo Logs（撤销日志）：撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。

3.3.InnoDB线程模型

• IO Thread：使用了大量的AIO（Async IO）来做读写处理，极大提高数据库的性能。

  有10个IO Thread，分别为：write（4个），read（4个），insert buffer（1个），log thread（1个）

  • read thread ： 负责读取操作
  • write thread：负责写操作
  • log thread：负责将日志缓冲区内容刷新到磁盘
  • insert buffer thread ：负责将写缓冲内容刷新到磁盘

• Purge Thread：事务提交之后，其使用的undo日志将不再需要，因此需要Purge Thread回收已经分配的undo页。

• Page Cleaner Thread：作用是将脏数据刷新到磁盘，脏数据刷盘后相应的redo log也就可以覆盖，即可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理。

• Master Thread：Master thread是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（page cleaner thread）、undo页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒处理。

  每1秒的操作：

  • 刷新日志缓冲区，刷到磁盘
  • 合并写缓冲区数据，根据IO读写压力来决定是否操作
  • 刷新脏页数据到磁盘，根据脏页比例达到75%才操作

  每10秒的操作：

  • 刷新脏页数据到磁盘
  • 合并写缓冲区数据
  • 刷新日志缓冲区
  • 删除无用的undo页

3.4.InnoDB数据文件

3.4.1 InnoDB文件存储结构

InnoDB数据文件存储结构：
分为一个ibd数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row（行）

• Tablesapce（表空间）：用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
• Segment（段）：用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
• Extent（区）：一个区固定包含64个连续的页，大小为1M。
• Page（页）：用于存储多个Row行记录，大小为16K。
• Row（行）：包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息。

Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和page body组成。如下图所示：

3.4.2.InnoDB文件存储格式

可通过 SHOW TABLE STATUS命令查看

mysql> show table status \G;
*************************** 1. row ***************************
           Name: position
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-01-20 09:17:59
    Update_time: 2021-01-20 09:24:52
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: position_detail
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-01-17 11:05:15
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果
row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

可通过 information_schema 查看指定表的文件格式

select * from information_schema.innodb_sys_tables;

3.4.3.File文件格式

目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。

• Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。
• Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。

3.4.4.Row行格式

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

• REDUNDANT 行格式：使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便能够在页外存储。
• COMPACT 行格式：与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。
• DYNAMIC 行格式：使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只
  包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制。
• COMPRESSED 行格式：COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令：

ALTER TABLE 表名 ROW_FORMAT=格式类型;

3.5.Undo Log

Undo Log介绍

Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。

Undo Log作用

• 实现事务的原子性
• 实现多版本并发控制（MVCC）

MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态，并且可以用来实现多版本并发控制。

MVCC实现原理：事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。

事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

3.6.Redo Log和Binlog

Redo Log和Binlog是MySQL日志系统中非常重要的两种机制，也有很多相似之处，下面介绍下两者细节和区别。

3.6.1.Redo Log日志

Redo Log介绍

Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。

Redo Log工作原理

Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

原理图如下：

Redo Log写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

如图所示：

• write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；
• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上
checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

Redo Log相关配置参数

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。可通过下面参数控制Redo Log存储：

show variables like '%innodb_log%';

Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

• 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
• 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
• 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

3.6.2.Binlog日志

• Binlog记录模式：

  Binary log，简称Binlog。是记录所有数据库表结构变更已经表数据修改的二进制文件（不记录SELECT和SHOW操作）。Binlog日志是以事件形式记录，包含语句所执行的消耗时间。开启Binlog日志有以下两个使用场景：

  • 主从复制：在主库中开启Binlog功能，主库就可以把Binlog传递到从库，从库拿到Binlog后实现数据恢复到达主从数据一致性。
  • 数据恢复：通过mysql binlog工具来恢复数据。

  Binlog文件名默认为"主机名_binlog-序列号"格式，例如:mysql_binlog-000001，可在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下：

  • ROW：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

    优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
    缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。

  • STATMENT：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

    优点：日志量小，减少磁盘IO，提升存储和恢复速度
    缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

  • MIXED：以上两种模式的混合使用，一般会使用
    STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

• Binlog文件结构

  MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。

  Binlog文件中Log event结构如下图所示：

  

• Binlog写入机制

  • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）

  • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓存区

    Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区：

    • stmt_cache：用于存放不支持事务的信息。

    • trx_cache：用于存放支持事务的信息。

  • 事务在提交阶段会将产生的log event写入外部binlog文件中。

    不同事务以串行方式将log event写入binlog中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

• Binlog文件操作

  • 查看Binlog状态

    show variables like 'log_bin';
    

  • 开启Binlog功能，需要修改my.cnf或my.ini配置文件，并重启服务

    #log-bin=ON
    #log-bin-basename=mysqlbinlog
    binlog-format=ROW
    log-bin=mysqlbinlog
    

  • 使用mysqlbinlog 命令

    mysqlbinlog "文件名"
    mysqlbinlog "文件名" > "test.sql"
    

  • 使用 binlog 恢复数据

    //按指定时间恢复
    mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop-
    datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
    //按事件位置号恢复
    mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234
    

    mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

  • 删除Binlog文件

    purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
    purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
    reset master; //清除所有文件
    

  可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

• Redo Log和Binlog区别

  • Redo Log是InnoDB引擎功能，Binlog是MySQL Server自带的二进制文件记录。
  • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
  • Redo Log日志是循环写，日志空间大小固定，Binlog是追加写入，写完一个继续写下一个，不会覆盖使用。
  • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。

四、MySQL索引原理

4.1.索引类型

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

• 存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 索引键值类型划分：主键索引、辅助索引（二级索引）
• 数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

4.1.1.普通索引：

基于普通字段建立的索引，创建普通索引的方法如下：

• CREATE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

4.1.2.唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的方法如下：

• CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

4.1.3.主键索引

一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

• CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
• ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4.1.4.复合索引

在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

创建组合索引的方法如下：

• CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
• CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

• 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。

• 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

4.1.5.全文索引

不做过多描述，自行百度查文档。

4.2.索引原理

定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

4.2.1.二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。

优点：等值查询、范围查询性能优秀，

缺点：更新数据、新增数据、删除数据维护成本高。

• 首先定位left和right两个指针
• 计算(left+right)/2
• 判断除2后索引位置值与目标值的大小比对
• 索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：

• 第一次查询

  

• 第二次查询

  

• 第三次查询

  

• 第四次查询

  

4.3.2.Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从上面结构可看出，Hash索引可以方便快速提供等着查询，但是对于范围查询则需要全表扫描。

Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

InnoDB 自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的B+Tree索引具备哈希索引的功能，即能快速定值访问频繁访问的索引页。

优点：等值查询效率要优于B+Tree。

缺点：范围查询效率比B+Tree低。

4.4.2.B+Tree

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

• B-Tree结构

  • 索引值和data数据分布在整棵树结构中
  • 每个节点可以存放多个索引值及对应的data数据
  • 树节点中的多个索引值从左到右升序排列

  

• B+Tree结构

  • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接，提高区间的访问性能

  

  相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有节点和数据，效率比B+树低。

4.4.3.聚簇索引和辅助索引

• 聚簇索引（聚集索引）

  InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

  • 聚簇索引：叶子节点存放主键索引值和行记录就属于聚簇索引
  • 非聚簇索引：索引值和行记录分开存放就属于非聚簇索引

  InnoDB的表要求必须要有聚簇索引：

  • 如果表定义了主键，则主键索引就是聚簇索引
  • 如果表没有定义主键，则第一个非空unique列作为聚簇索引
  • 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

• 辅助索引

  • 主键索引：叶子节点存放的是主键字段值就属于主键索引
  • 辅助索引：非主键值就属于辅助索引（二级索引）

4.3.索引优化与分析

4.3.1.EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

explain select * from position where id < 3

EXPLAIN 命令的输出内容大致如下：

mysql> explain select * from position where id < 3 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: position
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

• id：id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

• select_type：

  • simple：简单查询。查询不包含子查询和union
  • primary：复杂查询中最外层的查询
  • subquery：包含在 select 中的子查询（不在 from 子句中）
  • derived：包含在 from 子句中的子查询。

• type：

  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。 const 之外最好的联接类型。
  • const：表示使用主键或唯一索引做等值查询，常量查询。system是const的特例，表里只有一条元组匹配时为system
  • NULL：表示不用访问表，速度最快。

  依次从最优到最差分别为：NULL>system > const > eq_ref > ref > range > index > ALL

• possible_keys：这一列显示查询可能使用哪些索引来查找。

• key：表示查询时真正使用到的索引，显示的是索引名称。

• key_len：表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

  • 字符串类型

    字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
    char(n)：n*字符集长度
    varchar(n)：n * 字符集长度 + 2字节

  • 数值类型

    tinyint：1个字节
    smallint：2个字节
    mediumint：3个字节
    int、float：4个字节
    bigint、double：8个字节

  • 时间类型

    date：3字节
    timestamp：4字节
    datetime：8字节

  • 如果字段允许为 NULL，需要1字节记录是否为 NULL

• Extra：

  • Using index：使用覆盖索引
  • Using where：使用 where 语句来处理结果，查询的列未被索引覆盖
  • Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
  • Using temporary：mysql需要创建一张临时表来处理查询。（需要用索引优化的）
  • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。（需要用索引优化的）
  • Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引

4.3.2.回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫描两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。
总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

4.3.3.覆盖索引

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。

实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

4.4.4.最左前缀法则

复合索引使用时遵循最左前缀法则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

索引使用总结

4.4.查询优化

常见sql优化

准备

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5300004 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
	declare i int; 
	set i=1; 
	while(i<=100000)do
		insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
		set i=i+1;
	end while;
end;;
delimiter ;
call insert_emp();

• Order by与Group by优化

  Case1：

  

  分析：

  利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

  Case2：

  

  分析：从explain的执行结果来看，key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

  Case3：

  

  分析：查找只用到索引name，age和position用于排序，无Using filesort。

  Case4：

  

  分析：

  和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

  Case5：

  

  分析：与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

  Case6：

  

  分析：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

  Case7：

  

  分析：对于排序来说，多个相等条件也是范围查询

  Case8：

  

  可以使用覆盖索引优化

  

• 分页查询优化

  • 根据自增且连续的主键排序的分页查询

    mysql> select * from employees limit 90000,5;
    

    

    该 SQL 表示查询从第 90001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据，如下：

    select * from employees where id > 90000 limit 5;
    

    查询的结果是一致的。我们再对比一下执行计划：

    mysql> EXPLAIN select * from employees limit 90000,5;
    

    

    EXPLAIN select * from employees where id > 90000 limit 5;
    

    

    显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。

  ​ 注：如果主键不连续，不能使用上面的优化方法

  • 根据非主键字段排序的分页查询

    再看一个根据非主键字段排序的分页查询，SQL 如下：

    mysql>  select * from employees ORDER BY name limit 90000,5;
    

    

    发现并没有使用 name 字段的索引（key 字段对应的值为 null），具体原因：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

    知道不走索引的原因，那么怎么优化呢？

    其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下：

    mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed
    on e.id = ed.id;
    

    

    需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划：

    

    原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

• Join关联查询优化

  准备

  create table `t1` (
  	`id` int(11) NOT NULL AUTO_INCREMENT,
  	`a` int(11) DEFAULT NULL,
  	`b` int(11) DEFAULT NULL,
  	PRIMARY KEY (`id`),
  	KEY `idx_a` (`a`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
  
  create table t2 like t1;
  
  # 往t1表插入1万行记录
  CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_t1`()
  begin
    declare i int;                    
    set i=1;                          
    while(i<=10000)do                 
      insert into t1(a,b) values(i,i);  
      set i=i+1;                       
    end while;
  end
  
  # 往t2表插入100行记录
  insert into t2 select * from t1 limit 100 
  

  mysql的表关联常见有两种算法

  • 嵌套循环连接Nested-Loop Join 算法

    一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

    mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
    

    

    从执行计划中可以看到这些信息：

    • 驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
    • 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

    上面sql大致流程如下：

    • 从表 t2 中读取一行数据；

    • 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；

    • 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；

    • 重复上面 3 步。

    如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

  • 基于块的嵌套循环连接Block Nested-Loop Join 算法

    把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

    mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
    

    

    Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

    上面sql大致流程如下：

    • 把 t2 的所有数据放入到 join_buffer 中

    • 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

    • 回满足 join 条件的数据

    因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

    对于关联sql的优化

    • 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
    • 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

    straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

• in和exsits优化

  原则：小表驱动大表，即小的数据集驱动大的数据集

  • in：当B表的数据集小于A表的数据集时，in优于exists

    select * from A where id in (select id from B)
    

  • exists：当A表的数据集小于B表的数据集时，exists优于in

    将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

    select * from A where exists (select 1 from B where B.id = A.id)
    

优化总结

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
• order by满足两种情况会使用Using index。
  • order by语句使用索引最左前列。
  • 使用where子句与order by子句条件列组合满足索引最左前列。
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
• 如果order by的条件不在索引列上，就会产生Using filesort。
• 能用覆盖索引尽量用覆盖索引。
• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

五、MySQL事务和锁

5.1.ACID事务特性

ACID事务特性

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

• 原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。
• 隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。
• 一致性：指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。

5.2.事务控制的演进

事务并发处理就会带来一些问题，比如：更新丢失、脏读、不可重复读、幻读。

• 更新丢失：指两个或多个事务同时操作同一行记录，会产生数据丢失现象。可分为回滚覆盖和提交覆盖。
  • 回滚覆盖：一个事务回滚操作，覆盖其他事务已提交的数据。
  • 提交覆盖：一个事务提交操作，覆盖其他事务已提交的数据。
• 脏读：⼀个线程中的事务读到了另外⼀个线程中未提交的数据。
• 不可重复读：⼀个线程中的事务读到了另外⼀个线程中已经提交的update的数据。（前后内容不一致）
• 幻读：⼀个线程中的事务读到了另外⼀个线程中已经提交的insert或者delete的数据。（前后条数不一致）

5.3.锁的定义与分类

5.3.1.定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

5.3.2.锁分类

• 从性能上分为乐观锁（版本对比实现）和悲观锁
• 从对数据库操作类型分为读锁和写锁（都属于悲观锁）

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响

写锁（排它锁）：当前写操作没有完成前，会阻塞其他写锁和读锁

• 从对数据操作粒度分为表锁和行锁

表锁：每次操作锁住整张表。加锁快，开销小，锁定粒度大，发生锁冲突概率高，并发度最低，不会出现死锁。

行锁：每次操作锁住一行数据。加锁慢，开销大，锁定粒度最小，发生锁冲突概率最低，并发度最高，会出现死锁。

5.4.MVCC机制

MVCC概念

MVCC称为多版本控制，指再数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。

MVCC原理

MVCC最大的好处是读不加锁，读写不冲突。极大提升了系统的并发性能。MVCC只在Read Commited和Repeatable Read两种隔离级别下工作。

MVCC并发控制中，读操作分为：快照读和当前读。

快照读：读取的是记录的快照版本（有可能是历史版本），不加锁。（select）

当前读：读取的是记录的最新版本，并且当前读返回的记录都会加锁，保证其他事务不会再并发修改这条记录。（select for update或locks in share mode，insert/delete/update）

5.5.隔离级别

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交（Read uncommitted）、读已提交（Read committed）、可重复读（Repeatable read）、可串行化（Serializable）。

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

• 可串行化：可避免脏读、不可重复读、幻读的情况发生。
• 可重复读：可避免脏读、不可重复读的情况发生。（幻读有可能发生）
• 读已提交：可避免脏读情况发生。不可重复读和幻读一定会发生。
• 读未提交：级别最低，以上情况都会发生。

注意：隔离级别依次升高，效率依次降低。

MySQL的默认隔离级别是：REPEATABLE READ

查询当前使⽤的隔离级别： select @@tx_isolation;

设置MySQL事务的隔离级别： set session transaction isolation level xxx;(设置的是当前mysql连接会话的，并不是永久改变的)