最全面的MySQL知识点总结

前言

最近在回顾之前学的知识点，mysql部分涉及的东西很多，所以想写写文章记录一些重要的知识点，方便以后回顾，同时也分享给大家，如果文章中有描述的不对或不足的地方，欢迎指出和交流。

一、架构原理

1、基础架构

1.1、架构概览

1.2、架构说明

1.2.1、server层

• 连接器： 负责跟客户端建立连接、获取权限、维持和管理连接，一个用户成功建立连接后，即使用管理员的账号对这个用户的权限进行修改也不会影响已存在的连接的权限。客户端如果太长时间没动静，连接器会自动断开连接，由参数wait_timeout控制（默认为8小时）
• 查询缓存： 查询出的结果会暂时缓存在这里，不过建议尽量不用查询缓存，因为更新表记录后，缓存会失效，对更新比较频繁的表来说命中率很低，除非是很长时间才更新一次的表（mysql8.0之后已将查询缓存功能删除）
• 分析器： 先做词法分析，识别出关键字。再做语法分析，判断是否满足mysql的语法。
• 优化器： 选择索引、确定执行方案。
• 执行器： 操作引擎，返回执行结果。

1.2.2、存储引擎层

存储引擎层有MyISAM、Innodb等。

1.2.3、系统文件层

• 日志文件： 包括：错误日志（Error log）、通用查询日志（General query log）、通用查询日志（General query log）、通用查询日志（General query log）。
• 配置文件： 用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
• 数据文件:
  • db.opt 文件: 记录这个库的默认使用的字符集和校验规则。
  • frm 文件： 存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
  • MYD 文件： MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。
  • MYI 文件： MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。
  • ibd文件和 ibdata文件： 存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种
    表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
  • ibdata1 文件: 系统表空间数据文件，存储表元数据、Undo日志等。
  • ib_logfile0、ib_logfile1 文件： Redo log 日志文件。

1.3、运行机制

1.3.1、一次sql查询经过的过程

• ***执行过程图：***
• 过程详解
  • ①建立连接： 通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。
  • ②查询缓存： 这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句（包括参数值），则将查询结果直接返回给客户端。不过有些情况下即使开启查询缓存，以下SQL也不能缓存（查询语句使用SQL_NO_CACHE、查询的结果大于query_cache_limit设置、查询中有一些不确定的参数，比如now()）
  • ③解析器： 将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。
  • ④查询优化器： 根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。
    • 等价变换策略： 例如：5=5 and a>5 改成 a > 5，a < b and a=5 改成b>5 and a=5；基于联合索引，调整条件位置等）。
    • 优化count、min、max等函数： InnoDB引擎min函数只需要找索引最左边、InnoDB引擎max函数只需要找索引最右边、MyISAM引擎count(*)，不需要计算，直接返回。
    • 提前终止查询： 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据。
    • in的优化： MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)。
  • ⑤查询执行引擎： 负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。注：如果开启了查询缓存，先将查询结果做缓存操作，如果返回结果过多，采用增量模式返回

二、存储引擎

关于存储引擎这边只接受InnoDB引擎，其他引擎用的不多，所以了解的也不是很深。

1、存储结构

1.1、总体存储结构

1.2、内存结构

1.2.1、Buffer Pool

缓冲池，简称BP。BP以Page页为单位，默认大小16K，BP的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率，其中page有三种状态类型：

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

三种链表结构：

1、free list： 表示空闲缓冲区，管理free page
2、lru list： 表示正在使用的缓冲区，管理clean page和dirty page，缓冲区以midpoint为基点，前面链表称为new列表区，存放经常访问的数据，占63%；后面的链表称为old列表区，存放使用较少据据，占37%。
3、flush list： 表示需要刷新到磁盘的缓冲区，管理dirty page，内部page按修改时间排序。脏页即存在于flush链表，也在LRU链表中，但是两种互不影响，LRU链表负责管理page的可用性和释放，而flush链表负责管理脏页的刷盘操作

1.2.2、Change Buffer

写缓冲区，简称CB。在进行DML操作时，如果BP没有其相应的Page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，会先进行磁盘读取，然后再从ChangeBuffer中读取信息合并，最终载入BufferPool中。不过，仅适用于非唯一普通索引页，如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。

• change buffer 读过程：
  
• change buffer 更新过程：
  

1.2.3、Adaptive Hash Index（自适应哈希索引）

用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引

1.2.4、Log Buffer

日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘。

其中可以优化的参数：

• innodb_log_buffer_size： 将这个参数调大，可以减少磁盘IO频率
• innodb_flush_log_at_trx_commit： 控制日志刷盘行为，默认为1。
  • 0 ： 每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer–>OS cache，刷盘OS cache–>磁盘文件），最多丢失1秒数据；
  • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作；
  • 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

1.3、磁盘结构

1.3.1、表空间（Tablespaces）

• 系统表空间（The System Tablespace）
  包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数
  innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)
• 独立表空间（File-Per-Table Tablespaces）
  默认开启，每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。
• 通用表空间（General Tablespaces）
  通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间
• 撤销表空间（Undo Tablespaces）
  InnoDB使用的undo表空间由innodb_undo_tablespaces配置选项控制，默认为0（0表示使用系统表空间ibdata1，大于0表示使用undo表空间）
• 临时表空间（Temporary Tablespaces）

1.3.2、数据字典（InnoDB Data Dictionary）

由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据

1.3.3、双写缓冲区（Doublewrite Buffer）

在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite 缓冲区，使用Doublewrite 缓冲区时建议将innodb_flush_method设置为O_DIRECT

innodb_flush_method有三个值可以配置：

• fdatasync(默认)：fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文件与redo log的缓存信息
• O_DIRECT：O_DIRECT表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从Innodb Buffer写到磁盘文件
• O_DSYNC：表示写日志时，数据都要写到磁盘，并且元数据也需要更新，才返回成功。

1.3.4、重做日志（Redo Log）

重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据，默认情况下，重做日志在磁盘上由两个名为ib_logfile0和ib_logfile1的文件物理表示

1.3.5、撤销日志（Undo Logs）

用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中

1.4、线程模型

1.4.1、IO Thread

• read thread：负责读取操作，将数据从磁盘加载到缓存page页（4个）
• write thread：负责写操作，将缓存脏页刷新到磁盘（4个）
• log thread：负责将日志缓冲区内容刷新到磁盘（1个）
• insert buffer thread：负责将写缓冲内容刷新到磁盘（1个）

1.4.2、purge Thread

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

1.4.3、page Cleaner Thread

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

刷脏页的机制：

• 用到innodb_io_capacity这个参数，它会告诉InnoDB你的磁盘能力。这个值建议设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试
• InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度，InnoDB会根据这两个因素先单独算出两个数字
  • 参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字
  • InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，
    我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字
    
• innodb_flush_neighbors 参数：是用来控制将邻居脏页也刷盘的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了

1.4.5、Master Thread

主线程，负责调度其他各线程，优先级最高，它会在每隔一段时间执行一些操作，比如：

• 每1秒的操作：
  • 1、刷新日志缓冲区，刷到磁盘
  • 2、合并写缓冲区数据，根据IO读写压力来决定是否操作
  • 3、刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct，innodb_io_capacity）
• 每10秒的操作：
  • 1.刷新脏页数据到磁盘
  • 2.合并写缓冲区数据
  • 3.刷新日志缓冲区
  • 4.删除无用的undo页

1.5、数据文件

1.5.1、文件存储结构

• Tablesapce： 表空间
• segment： 段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment
• extent： 区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会一页一页分，直接分配一个区
• page： 页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页，page header，page trailer和page body组成
• row： 行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointers）等信息

1.5.2、File文件格式

• Antelope： 最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT
• Barracuda： 新的文件格式。它支持InnoDB的所有行格式

1.5.3、Row行格式（Row_format）

• REDUNDANT： Redundant行格式是MySQL 5.0之前使用的一种行格式，这里不做讨论
• COMPACT： Compact行记录是在MySQL5.0中引入的，为了高效的存储数据，简单的说，就是为了让一个页（Page）存放的行数据越多，这样性能就越高
• DYNAMIC： 使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制
• COMPRESSED： COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

三、日志系统

1、undo log

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

1.1、产生与销毁

Undo Log在事务开始前产生，事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理

1.2、存放的内容

Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update

1.3、存储方式

undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment，由参数innodb_undo控制，在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128

1.4、作用

• 实现事务的原子性: 事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态
• 实现多版本并发控制（MVCC）: 事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读
  

2、redo log(InnoDB引擎特有的）

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)

2.1、工作原理

2.1.1、写入机制

• 新增或更新记录时先记录到redo log中，并更新内存，空闲时再写入磁盘，如果内存页中的数据与磁盘的数据不一致则称这个内存页为脏页，mysql偶尔会抖一下，是在刷脏页（flush），什么情况下会进行刷脏页？
  • 1、redo log满了，此时会将checkpoint指针往前推，将脏页刷入磁盘，这种情况会导致这个系统不再接受更新，影响性能
  • 2、系统内存不足时，当需要新的内存页而内存不够用时，会进行刷入磁盘，其中，mysql使用buffer pool缓存池来管理内存，缓存池中的内存页有三种状态：

  1、还没使用的页
  2、使用了并且是干净页
  3、使用了并且是脏页

  • 3、系统空闲时
  • 4、mysql正常关闭时
• 写入时是采用两阶段提交
  
• 写入策略

  • 先写入redo log buffer再写入page cache 最后刷到磁盘

  • 由参数innodb_flush_log_at_trx_commit控制

    1、设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中
    2. 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；与sync_binlog组成“双一”配置，一个事务提交会执行两次刷盘
    3. 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache

  • 其他策略

    • 后台有个线程每隔1秒会把redo log buffer中的日志写到page cache中然后在持久化到磁盘中

2.2、存储结构

是环形结构，固定大小

3、binlog

是mysql server层自己的日志，是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 “

3.1、使用场景

• 1、主从复制
  在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性
• 2、数据恢复：
  通过mysqlbinlog工具来恢复数据

3.2、写入机制

• 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
• 系统binlog cache分配一片内存，每个线程一个，由参数binlog_cache_size来控制单个线程binlog cache的大小，如果超过这个大小，就要暂存到磁盘中
• 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息
• 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息
• 事务在提交阶段会将产生的log event写入到外部binlog文件中。不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event
• 事务执行时，把日志写入binlog cache中，事务提交时把binlog cache中完整的事务写入到binlog中，并清空binlog cache
  
  • wirte是指把日志写入到文件系统的page cache，fsync才会将数据写入磁盘
  • wirte和fsync的时机是由参数sync_binlog控制的

1. sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
2. sync_binlog=1的时候，表示每次提交事务都会执行fsync；
3. sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

3.4、三种模式

3.4.1、Row Level 行模式

日志中会记录每一行数据被修改的形式，然后在slave端再对相同的数据进行修改，由参数binlog_format='row’控制
优点： 在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题
缺点： row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

3.4.2、Statement Level（默认）

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行，由参数binlog_format='statement’控制
优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上所执行的语句的细节，以及执行语句的上下文的信息。
缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现，比如now()。

3.4.3、 Mixed 混合模式

在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3.5、清除

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

二、索引

1、索引类型

索引类型可以按照不同的划分方式进行划分，主要的划分方式有以下几点

1.1、按索引存储结构划分

可分为：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引

1.2、按应用层次划分

1.2.1、普通索引

最基本的索引类型，基于普通字段建立的索引，没有任何限制，创建方式：

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

1.2.2、唯一索引

索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引，创建方式：

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

1.2.3、主键索引

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

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

1.2.4、复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效

创建方式：

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

1.2.5、前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

• 通过计算不同的区分度来决定使用多长的前缀

select count(distinct email)as l from user;
select count(distinct left(email,5))as l1 from user;

注意事项： 使用前缀索引就用不上覆盖索引的优化了

1.2.6、全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持，创建方式：

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

使用方法：全文索引有自己的语法格式，使用 match 和 against 关键字

select * from user where match(name) against(‘aaa’);
select * from user where match(name) against(‘a*’ in boolean mode);

注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

1.3、按索引键值类型划分

主键索引、辅助索引（二级索引）

1.4、按数据存储和索引键值逻辑关系划分

1.4.1、聚集索引（聚簇索引）

InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引

1.4.2、非聚集索引（非聚簇索引）

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构

2、索引原理

2.1、定义

是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作，索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价

2.2、B+Tree结构

• 结构： 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。叶子节点包含了所有的索引值和data数据。叶子节点用指针连接，提高区间的访问性能
• 自适应哈希索引： InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页

2.3、索引分析与优化

2.3.1、EXPLAIN命令重要参数解析

• select_type（查询的类型）

SIMPLE ： 表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询

• type（表示存储引擎查询数据时采用的方式）

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

• possible_keys: 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称
• key: 表示查询时真正使用到的索引，显示的是索引名称
• rows: 估算SQL要查询到结果需要扫描多少行记录
• key_len: 表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引
• Extra:

Using where
表示查询需要通过索引回表查询数据。
Using index
表示查询需要通过索引，索引就可以满足所需数据。
Using filesort
表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
Using temprorary
查询使用到了临时表，一般出现于去重、分组等操作

2.3.2、覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做覆盖索引也叫索引覆盖。（回表查询：通过二级索引查询主键值，然后再去聚簇索引查询记录信息）

2.3.3、最左匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

2.3.4、 LIKE查询优化

MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引

2.3.5、NULL查询优化

NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等

2.3.6、索引与排序优化

• filesort排序
  • 原理： 先把结果查出，然后在缓存或磁盘进行排序操作，效率较低
  • 算法：
    • 双路排序： 需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据
    • 单路排序： 从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量
  • 会走filesort排序的场景

1、WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等），explain select id from user where age>10 order by name; //对应(age,name)索引
2、对索引列同时使用了ASC和DESC，explain select id from user order by age asc,name desc; //对应(age,name)索引
3、使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引，explain select id from user order by name,age; //对应(name)、(age)两个索引
4、WHERE子句与ORDER BY子句，使用了不同的索引。explain select id from user where name=‘tom’ order by age; //对应（name)、(age)索引
5、WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式。explain select id from user order by abs(age); //对应(age)索引

• index排序
  • 原理： 是指利用索引自动实现排序，不需另做排序操作，效率会比较高
  • 会走index排序的场景：

1、 ORDER BY 子句索引列组合满足索引最左前列。explain select id from user order by id; //对应(id)、(id,name)索引有效
2、WHERE子句+ORDER BY子句索引列组合满足索引最左前列。explain select id from user where age=18 order by name; //对应(age,name)索引

2.4、查询优化

2.4.1、慢查询优化

• 全表扫描：explain分析type属性all
• 全索引扫描：explain分析type属性index
• 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
• 频繁的回表查询开销：尽量少用select *，使用覆盖索引

2.4.2、 分页查询优化

• 利用覆盖索引优化

select * from user limit 10000,100;
select id from user limit 10000,100;

• 利用子查询优化

select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;
使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

三、事务和锁

1、事务控制

1.1、隔离级别

• read uncommit (RU 读未提交) ：一个事务还没提交时，它的变更就能被其他事务看到（会产生脏读）
• read commit（RC 读已提交） ：一个事务提交后，它的变更才能被其他事务看到（产出不可重复读问题，在事务内两次读取数据，第二次有其他事务提交了数据，则两次数据不一致）
• repeatable read （RR 可重复读)：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据一致，在该隔离级别下，未提交的变更对其他事务不可见
• serializable (串行化）：对于同一行记录会加锁，串行化操作

1.2、事务隔离的实现(MVCC)

1.2.1、概念

MVCC（Multi-Version Concurrency Control）多版本并发控制，是用来在数据库中控制并发的方法，实现对数据库的并发访问用的。在MySQL中，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的。MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。利用了Copy on Write的思想

1.2.2、读取数据的方式

• 当前读： 读取的都是最新版本，读取时加行锁不允许其他事物修改当前记录，像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读；
• 快照读： 通过mvcc实现，通过不同版本来解决读写并发的问题

1.2.3、实现原理

• 三个隐式字段

DB_TRX_ID：6byte,最近修改（修改、插入）事务ID
DB_ROLL_PTR：7byte,回滚指针，指向这条记录的上一个版本（存储在rollback segment中）
DB_ROW_ID：隐藏主键

• undo log

insert undo log：代表事务在insert新记录时产生的undo log，只有在事务回滚时需要，事务提交后删除
update undo log：事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

• read view

Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)

• RC和RR read view 的差别：
  • RC：在每个语句执行前都会重新算出一个read view
  • RR：在事务开始是创建read view

1.2.3、执行原理

2、数据库锁

2.1、锁分类

2.1.1、全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句；
应用场景是全库逻辑备份，全局锁不太好，可以使用官方的mysqldump，当使用-single-transaction时，导出数据之前会启动一个事物，通过mvcc来保证一致性视图

2.1.2、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)

• 表锁： 表锁的语法是 lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁， 也可以在客户端断开的时候自动释放。
• 元数据锁（meta data lock,MDL)： 当要对表做结构变更操作的时候，加MDL写锁，事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

2.1.3、行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。在在InnoDBInnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

• 死锁：

  • 产生的原因：两个事物互相等待对方释放锁

    • 

  • 死锁排查

    • 查看死锁日志：通过show engine innodb status\G命令查看近期死锁日志信息。使用方法：1、查看近期死锁日志信息；2、使用explain查看下SQL执行计划
    • 查看锁状态变量：通过show status like’innodb_row_lock%‘命令检查状态变量，分析系统中的行锁的争夺情况

    Innodb_row_lock_current_waits：当前正在等待锁的数量
    Innodb_row_lock_time：从系统启动到现在锁定总时间长度
    Innodb_row_lock_time_avg： 每次等待锁的平均时间
    Innodb_row_lock_time_max：从系统启动到现在等待最长的一次锁的时间
    Innodb_row_lock_waits：系统启动后到现在总共等待的次数

  • 如何解决死锁

    • 1、设置超时时间innodb_lock_wait_timeout（默认50S)
    • 2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

2.1.4、间隙锁（Gap Lock)

隙锁（Gap Lock）是Innodb在可重复读（RR）提交下为了解决幻读问题时引入的锁机制，幻读在“当前读”才会出现，幻读产生的原因是行锁只能锁住行，对于新插入的记录这个动作，要更新的是记录之间的间隙。不使用间隙锁的话，可以使用RC级别加上binlog_format=row。优化：在删除时可以加上limit，来减少间隙锁的范围。

2.1.5、next-key lock

每个next-key lock是前开后闭区间

• 加锁规则
  • 两个原则

    1、加锁的基本单位是next-key-lock(前开后闭区间）
    2、查找过程中访问到的对象才会加锁

  • 两个优化

    1、索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
    2、索引上等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁(开区间）

四、sql语句优化

1、order by 优化

1.1、执行过程

• 全字段排序
  

• rowId排序
  

• 由max_length_for_sort_data的值决定，如果字段总长度大于这个值则执行rowid排序

1.2、排序机制

排序默认会在sort_buffer内存中进行，如果大小超过sort_buffer_size值，就会借助外部排序生成多个临时文件（归并排序）

1.3、优化

通过覆盖索引可以不用进行排序，索引本身就是有序的

2、join查询优化

2.1、判断要不要使用join

看explain看 extra字段有没有出现“Block Nested Loop”，如果没有出现则可以使用join，但是要以小表作为驱动表

2.2、优化

尽量在被驱动的表上加索引，使其使用BKA（batched key acess)算法，BKA是基于MRR（Multi-RangeRead)优化实现，在t1中取出索引a的数据，在join buffer中对数据进行id的排序，在t2进行顺序查找，使用set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;开启BKA，如果被驱动表是大表的话，可以使用临时表来优化，创建要查询范围数据的临时表，再设置关联字段的索引即可。

五、集群架构

1、主从模式

1.1、主从同步

主库和备库之间维持了一个长连接，主库An内部有个线程，专门服务备库b的这个长连接，完整过程：

1、在备库B上通过change master命令，设置主库A的ip、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志的偏移量
2、在备库B上执行start slave命令，这时候备库会启动两个线程，io_thread和sql_thread，其中io_tread负责与主库建立连接
3、主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B
4、备库接收到binlog后，写到本地文件，称为中转日志（relay log)
5、sql_thread读取中转日志，解析出日志里的命令，并执行

1.2、存在的问题以及解决办法

• 1、主库宕机后，数据可能丢失

  • 解决办法：半同步复制,，即主库等待从库写入 relay log 并返回 ACK 后才进行Engine Commit
  • 

• 2、从库只有一个SQL Thread，主库写压力大，复制很可能延时

  • 解决办法：并行复制，sql_thread变成多线程来加快备库的执行，减少延迟，遵循两个规则：

  1、同一个事务必须在同一个work线程中执行
  2、操作同一行数据的多个事务必须在同一个work线程中执行

2、读写分离

读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过主从复制机制进行数据的同步

2.1、读写分配机制

• 基于编程和配置实现（应用端）：优点是实现简单，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手，如果其中一个数据库宕机了，就需要修改配置重启项目
• 基于服务器端代理实现（服务器端）：常用的有MySQL Proxy、MyCat以及Shardingsphere等

2.2、存在的问题及解决办法

• 主从同步延迟
• 解决办法：
  • 写后立刻读：在写入数据库后，某个时间段内读操作就去主库，之后读操作访问从库
  • 二次查询：先去从库读取数据，找不到时就去主库进行数据读取。该操作容易将读压力返还给主库，为了避免恶意攻击，建议对数据库访问API操作进行封装，有利于安全和低耦合
  • 根据业务特殊处理：根据业务特点和重要程度进行调整，比如重要的，实时性要求高的业务数据读写可以放在主库。对于次要的业务，实时性要求不高可以进行读写分离，查询时去从库查询

3、双主模式

3.1、双主单写

其中一个Master提供线上服务，另一个Master作为备胎供高可用切换，Master下游挂载Slave承担读请求

3.2、问题及解决办法

• 双“M"互为主备，怎么避免循环复制？
• 解决办法：会在binlog中记录第一次执行时所在的实例serverId,备库在收到binlog并重放过程中生成与原binlog相同serverid的binlog，A在收到时判断serverid如果是自己的就不做处理

4、主备切换

4.1、可靠性优先策略

主从同步是都是只读，存在一段时间不可用

4.2、可用性优先策略

会存在数据不一致的情况

5、MHA架构

是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA还支持在线快速将Master切换到其他主机，通常只需0.5－2秒

5.1、故障处理机制

1、把宕机master的binlog保存下来
2、根据binlog位置点找到最新的slave
3、用最新slave的relay log修复其它slave
4、将保存下来的binlog在最新的slave上恢复
5、将最新的slave提升为master
6、将其它slave重新指向新提升的master，并开启主从复制

5.2、优点

1、自动故障转移快
2、主库崩溃不存在数据一致性问题
3、性能优秀，支持半同步复制和异步复制
4、一个Manager监控节点可以监控多个集群

6、分库分表

6.1、拆分方式

日常工作中，我们通常会同时使用两种拆分方式，垂直拆分更偏向于产品/业务/功能拆分的过程，在技术上我们更关注水平拆分的方案

6.1.1、垂直拆分（解决表过多或者是表字段过多问题）

垂直拆分是将表按库进行分离，或者修改表结构按照访问的差异将某些列拆分出去。应用时有垂直分库和垂直分表两种方式，一般谈到的垂直拆分主要指的是垂直分库；

• 垂直分库：采用垂直分库，比如将用户表和订单表拆分到不同的数据库中
• 垂直分表：垂直分表就是将一张表中不常用的字段拆分到另一张表中，从而保证第一张表中的字段较少，避免出现数据库跨页存储的问题，从而提升查询效率
• 优点：

1、拆分后业务清晰，拆分规则明确；
2、易于数据的维护和扩展；
3、可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次数；
4、可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起；
5、便于实现冷热分离的数据表设计模式

• 缺点：

1、主键出现冗余，需要管理冗余列；
2、会引起表连接 JOIN 操作，可以通过在业务服务器上进行 join 来减少数据库压力，提高了系统的复杂度；
3、依然存在单表数据量过大的问题；
4、事务处理复杂

6.2、水平拆分（解决表中记录过多问题）

根据某种规则将数据分散至多个库或表中，每个表仅包含数据的一部分

• 优点：

1、拆分规则设计好，join 操作基本可以数据库做；
2、不存在单库大数据，高并发的性能瓶颈；
3、切分的表的结构相同，应用层改造较少，只需要增加路由规则即可；
4、提高了系统的稳定性和负载能力

• 缺点：

1、拆分规则难以抽象；
2、跨库Join性能较差；
3、分片事务的一致性难以解决；
4、数据扩容的难度和维护量极大；

6.2、主键策略

6.2.1、UUID

• 优点： 可以在本地生成，没有网络消耗，所以生成性能高；
• 缺点： UUID比较长，没有规律性，耗费存储空间， 如果UUID作为数据库主键，在InnoDB引擎下，UUID的无序性可能会引起数据位置频繁变动，影响性能

6.2.2、COMB（UUID变种）

保留UUID的前10个字节，用后6个字节表示GUID生成的时间（DateTime），这样我们将时间信息与UUID组合起来，在保留UUID的唯一性的同时增加了有序性，以此来提高索引效率。解决UUID无序的问题，性能优于UUID

6.2.3、SNOWFLAKE

SnowFlake生成的ID整体上按照时间自增排序，并且整个分布式系统内不会产生ID重复，并且效率较高。经测试SnowFlake每秒能够产生26万个ID。缺点是强依赖机器时钟，如果多台机器环境时钟没同步，或时钟回拨，会导致发号重复或者服务会处于不可用状态

6.2.4、Redis生成ID

假如一个集群中有5台Redis。可以初始化每台Redis的值分别是1,2,3,4,5，然后步长都是5。各个Redis生成的ID为：

A：1,6,11,16,21
B：2,7,12,17,22
C：3,8,13,18,23
D：4,9,14,19,24
E：5,10,15,20,25

6.3、分片策略

6.3.1、基于范围分片

根据特定字段的范围进行拆分，比如用户ID、订单时间、产品价格等

• 优点： 新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。
• 缺点： 数据热点分布不均，数据冷热不均匀，导致节点负荷不均

6.3.2、哈希取模分片

通过Hash(Key) % n就可以确定数据所在的设备编号

• 优点： 实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况；
• 缺点： 扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和迁移

6.3.3、一致性哈希分片

一致性Hash是将数据按照特征值映射到一个首尾相接的Hash环上，同时也将节点（按照IP地址或者机器名Hash）映射到这个环上。对于数据，从数据在环上的位置开始，顺时针找到的第一个节点即为数据的存储节点

一致性Hash在增加或者删除节点的时候，受到影响的数据是比较有限的，只会影响到Hash环相邻的节点，不会发生大规模的数据迁移，为了让节点数据分配均匀，可以使用虚拟节点；