mysql详解

一、文件
配置文件
用于存放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 文件(表名.ibd)。使用共享表空间 .ibdata 所有表格共同使用一个(或多个，自行配置， indata001 002这 种）.ibdata 文件。 ibdata1 文件：系统表空间数据文件，存储表元数据Undo日志等 。
ib_logfifile0、ib_logfifile1 文件：Redo log 日志文件。 二、运行机制
1、连接机制
建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议MySQL建立连接(TCP连 握手挥手的概念)。MySQL 客户端和服务端的通信方式是 “ 半双工 对每一个 MySQL 的连 连接，总有一个线程状态来识别连接在做什么。
通讯机制：
全双工:可以同时发送和接收数据，比如平时打电话。相应的网络意味着客户端和服务端可以同时收集，所以 任何一方都可以随意收发，两个方向都是开放的。
半双工：指某一时刻，要么发送数据，要么同时接收数据。例如，早期对讲机。mysql就是这 在一种模式下，只有在客户端发送服务端后才能返回，而不是收发
单工:只能发送或接收数据。比如单行道，区分任务，一方只能接受，一方只能发送。
线程状态：
每个连接都是一个线程状态，所以我们可以通过监控这个线程状态来分析哪些连接在做什么能看到 当前的。
show processlist; ///查看用户正在运行的线程信息，root用户可以查看所有线程，其他用户只能查看自己 的。

以下是这些参数的含义：
id：线程ID，可以使用kill xx；您当前连接的线程id。 user：用户启动这个线程，你用什么用户启动，正轨？mysql设置用户和分组使用。 Host：客户端发送请求IP和端口号，注意客户端。 db：哪个库执行当前命令，没连库就是null Command：该线程正在执行的操作命令 Create DB：库操作正在创建中 Drop DB：删除库操作 Execute：执行一个PreparedStatement Close Stmt：一个正在关闭PreparedStatement Query：执行一个语句 Sleep：正在等待客户端发送 Quit：正在退出 Shutdown：服务器正在关闭 Time：表示线程处于当前状态的时间，单位是秒，注意你现在执行的距离。show processlist的时间。最 新的就是0 State：线程状态 Updating：正在搜索匹配记录，进行修改 Sleeping：正在等待客户端发送新请求 Starting：正在执行请求处理 Checking table：检查数据表 Closing table : 将表中数据刷新到磁盘中 Locked：记录被其他查询锁定 Sending Data：正在处理Select查询，同时，将结果发送给客户端
查询缓存（Cache&Buffffer） 这是MySQL如果打开查询缓存并在查询缓存过程中查询完全相同(参数 也必须完全一致)SQL查询结果直接返回客户端；如果没有打开查询
缓存或未查询完全相同 SQL 句子将由分析器进行语法语义分析，并产生分析树。然后走具 体查询。
解析器（Parser） 发送客户端SQL语法分析，生成"解析树"。基于一些预处理器MySQL规则进一步检查解析树 是否合法，例如，这里将检查数据表和数据列是否存在，并分析名称和其他名称，它们是否有歧义，然后 产生新的解析树。
查询优化器（Optimizer） 根据分析树生成优秀的执行计划。MySQL使用许多优化策略生成优秀的执行计划可分为两类：静态 动态优化(编译时优化)和动态优化(运行时优化)。
查询执行引擎
查询执行引擎负责执行 SQL 此时，查询执行引擎将基于语句 SQL 语句中表的存储引擎类型及相应的 的API接口与底层存储引擎缓存或物理文件交互，获取查询结果并返回客户端。若开启用查询缓 存，这时会将SQL 句子和结果完全保存在查询缓存中（Cache&Buffffer）如果以后有相同的 SQL 语 句子执行直接返回结果。
如果打开查询缓存，请先将查询结果作为缓存操作。默认情况是关闭的。这个东西不好，8.0直接废除。 返回结果太多，mysq底层采用增量模式返回。也就是说，它不会一次全部返回，而是。
Info：默认情况下，记录线程执行的语句显示前100个字符。查看完整的使用命令：show full processlist;
缓存Select查询的结果和SQL语句 执行Select查询时，先查询缓存，判断是否有可用的记录集，要求是否完全相同(包括参数值) 匹配缓存数据命中。 即使打开以下查询缓存，SQL也不能缓存 使用查询语句SQL_NO_CACHE 查询结果大于query_cache_limit设置是指缓存最大的大小 查询中有一些不确定的参数，比如now() show variables like '%query_cache%'; ///检查缓存是否启用、空间大小、限制等 show status like 'Qcache%'; ///查看更详细的缓存参数，可使用缓存空间、缓存块、缓存量等
等价变换策略 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)
三、存储引擎 1、Innodb存储结构
从MySQL 5.5版本开始默认使用InnoDB作为一种发动机，它擅长处理事务，具有自动崩溃和恢复的特点 广泛应用于开发。以下是官方的InnoDB引擎架构图主要分为内存结构和磁盘结构。
1.1、InnoDB内存结构
内存结构主要包括Buffffer Pool、Change Buffffer、Adaptive Hash Index和Log Buffffer四大组件。
Buffer Pool：缓冲池，简称BP。BP以Page页面为单位，默认尺寸16K，BP底层采用链表数据结构管理 Page。在InnoDB在内存中记录和索引访问表Page在页面中缓存缓存可以减少磁盘IO操作，提高效率 率。 Page管理机制 Page根据状态可分为三种类型： free page ： 空闲page，未被使用，还没有存储数据 clean page：被使用page，数据没有修改，这意味着数据没有修改page页面中的数据与磁盘数据一致 是的，刷盘，一致，不一定完全没有改变。 dirty page：使用脏页page，数据已修改，尚未刷盘，页面数据与磁盘数据不同 致 针对以上三种page类型，InnoDB维护和管理三个链表结构 free list ：表示空闲缓冲区，管理free page flush list：表示需要刷新磁盘缓冲区，管理dirty page，内部page按修改时间排序，修 时间越早，刷盘越先，链表尾部先改，头部后改，用的 头插法，每次刷盘的时候是 从尾部刷。脏页存在于flush链表，也在LRU链表中，但两者不相互影响，LRU链表负责管理page的可 和释放flush链 表负责管理脏页的刷盘操作。 lru list：表示正在使用的缓冲区，管理clean page和dirty page，这个链表有两部分 midpoint前链表称为基点new列表区，存经常访问的数据(热        数据)， 占63%；后面的链 表称为old列表区(冷数据区)，存放使用较少数据，占37%。这里就是冷热分区，冷数据以后容易被淘汰，要 是需要淘汰，先淘汰他们里的。        当你lru list里面有页要淘汰的时候，说明不够用了，需要淘汰， 就会触发刷页操作，把脏页刷进磁盘，腾出空的页，这时候要是操作了dirty page的部分那么管理        他的flush list里面的也会刷盘，腾出空闲位置，保持这三个链表的同步，不能这里的脏页没了，被处理 了，你那里面还在。            改进型LRU算法维护        普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
        改性LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间midpoint位 置(new的尾部old的头部)插入，如果数据被访问，那么page就会向        new列表头部移动，如果数据 没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffer pool时，InnoDb引擎 会判断是否有空闲页，是否足        够，如果有就将free page从free list列表删除，放入到LRU列表 中。没有空闲页，就会根据LRU算法淘汰LRU链表末尾的页(就是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%，        innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。块的话大一点就能 多放几个页，这个调不调好像没多大用。 --------------------------------------------------------------------------------------------------------------------------Change Buffer：写缓冲区，简称CB。在进行DML(增删改)操作时，如果BP没有其相应的Page数据，并不会 立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更(就是把你的变动操作记录在这里)，等未来数据被读取 时，再将数据合并恢复到BP中。5.5版本之前叫做insert buffer，就是只对插入操作做缓冲，5.5之后才对 所有的DML语句做缓冲。而且你看上图能看出来其实他是属于Buffer poll的一部分，他使用的就是BP的空 间。
ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%，可以根据读写业务量来进行调整。通 过调整参数innodb_change_buffer_max_size; 当更新一条记录时，该记录在BufferPool存在，直接在BufferPool修改，一次内存操作。如果该记录在 BufferPool不存在（没有命中），会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避 免一次磁盘IO。当下次查询这条修改的记录时，会先进性磁盘读取，然后再从ChangeBuffer中读取信息合并 完成修改，最终载入BufferPool中。后续BufferPool里面的数据会和磁盘再做同步。
写缓冲区，仅适用于非唯一普通索引页(主键和唯一性索引都不行)，为什么？ 如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，那么怎么做唯一性校验呢，就是去磁盘 读取数据去做，因此必须查询磁盘，做一次IO操作，所以索性就不用这个change buffer了，反正你怎么地 都要磁盘io。会直接将记录查询到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作。
-----------------------------------------------------------------------------------------------------------------------------Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控对表索引的 查找，如果mysql观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存 储引擎会自动根据访问的频率和模式来为某些page页建立哈希索引。以后查询的时候根据hash的O(1)的时间 复杂度有优势。用的是BP的空间。
-----------------------------------------------------------------------------------------------------------------------------Log Buffer：日志缓冲区，用来保存要写入磁盘上log文件（Redo/Undo）的数据，这些日志记录不是立即 会保存到日志文件中，而是先记录在日志缓冲区中，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲 区满时会自动将其刷新到磁盘，当然了没满也会定时刷，当遇到BLOB或多行更新的大事务操作时，适当增加日 志缓冲区大小可以节省磁盘I/O，把多次操作统一到缓冲区一起刷入，不用每次都刷入，减少磁盘IO次数。当 然也不能一味的设置过大，不然你重启恢复也慢。一般就是默认16M，当然你看实际情况。 LogBuffer主要是用于记录InnoDB引擎日志，在DML操作时会产生Redo和Undo日志。 可以通过SHOW VARIABLES LIKE '%innodb_log%'查看log_buffer的一些信息。 LogBuffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频 率，没满也有写入策略，下面看。 innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1    0：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache，再由系统操作刷盘 OScache-->磁盘文件），最多丢失1秒数据    1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作，安全性好，但是性能上...
命令操作
使用SHOW ENGINE INNODB STATUS;可以看到innodb里面的一些配置信息。输出结果有一部分是 buffer pool的显示，我们来看看。
Buffer poll是缓存池。
里面有两块地方是change buffer和自适应哈希索引。change buffer先不说。 自适应哈希索引是根据你访问缓存中的命中次数，为缓存中的数据页建立索引映射的，类型是hash类型 的，hash的速度是1，所以就很快的就能找到缓存中的页进一步提高速度。key是页，值就是地址指针映 射。
查看Buffer Poll中的数据配置数据
    2：事务提交，立刻写日志文件进os cache但是不是立即刷盘，每隔1秒钟进行刷盘操作，相当于上面两 个的折中，一般可以设置为这个        这里注意一个东西，就是我们说的日志刷盘一般指的是把日志数据从日志缓冲区刷盘到OS cache这个区 域，然后具体再由操作系统把数据从os cache刷盘到实际的磁盘文件中。但是你要是一旦进了系统缓冲，那 么即便你Mysql宕机了数据也不丢，因为数据已经脱离mysql了。    但是上面的BUffer poll的刷盘，则会穿过os cache（上面的图有体现），直接到达磁盘文件中。这 是他们的不同之处，注意一下，至于为什么这么设计，我理解是日志没什么命中一说，就是往进写，所以经常 要进磁盘，所以就要多经过一次缓冲区，减少io之类的。
BUFFER POOL AND MEMORY  // BP ---------------------Total large memory allocated 8585216 Dictionary memory allocated 1156667 Buffer pool size   512  这里是bp的大小是512，表示的是512个页，也就是bp大小有512个页之大 Free buffers       254  这里是free list的大小是254个页 Database pages     256  这里是lru list的大小是256个页  你会发现Free buffers 和 Database pages加起来小于Buffer pool size，这是因为这个bp里面的空间可能会分配给自适应索引， 所以有时候会小于。 Old database pages 0    这里我们说lru list是有new 和 old两个热冷分区，这里就是old部分， 现在我表里没数据，就是空的 Modified db pages  0    这里就是脏页数量，就是dirty page的个数 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 431, created 57, written 253 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 256, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0]
show variables like '%innodb_buffer%';
来解释一下这几个配置：
chunk_size:就是块大小。
instance:是实例个数 pool_size:就是bp的整个缓冲区大小， 是这么个关系，pool里面可以设置多个实例个数，避免你就一个实例的时候，访问缓存的时候出现争 抢，多个实例就是可以减缓这种争抢。让各个客户端去不同的实例去查找。
每个实例里面又有多个块，每个块里面就有多个页，是这么个关系。
所以实例个数 * 块大小就是pool的大小。
pool-size默认是8388608,但是你可以调大，一般大可以调为总内存的百分之六十到八十左右，当然了 看环境，你内存不够就别作死。这个大了，实例个数也可以多几个，减少争抢。
看一个这个命令：查看lru list中old列表参数
查看old列表参数，上面的pct就是占百分比，是37，就是old区占37.然后下面的时间是1000，就是1 秒。他的意思是，一旦数据页被访问了，那么old区的数据被访问就应该移动去new区么，他不是立刻移 动的，而是要一秒后，这个就是这个配置。是为了不立刻移动影响操作。缓和一秒
查看log_buffer的信息
show variables like '%innodb_old%';
SHOW VARIABLES LIKE '%innodb_log%'
解释一下上面的参数：
innodb_log_buffer_size：就是日志缓冲区的总大小 innodb_log_file_size：单个日志文件的大小 innodb_log_files_in_group：每组日志文件的个数，可以看出来，日志文件是按组划分的，默认每组两 个
上面参数都能根据实际情况调整。来优化数据库。 1.2、InnoDB磁盘结构 
InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary，Doublewrite Buffffer、Redo Log和 Undo Logs。 1.2.1、表空间（Tablespaces） 
系统表空间（The System Tablespace） 包含InnoDB数据字典，Doublewrite Buffffer，Change Buffffer，Undo Logs的存储区域。系统表空间 也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是 被多个表共享的。该空间的数据文件通过参数innodb_data_fifile_path控制，默认值是 ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)。可以用SHOW VARIABLES LIKE去查 看。
独立表空间（File-Per-Table Tablespaces）
默认开启，独立表空间是一个单表表空间，以表为单位，该表创建于自己的数据文件中，而非创建于系 统表空间中。当innodb_fifile_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建 于系统表空间中。每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。表 空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。5.7默认是开启的独立表空间， 所以官方其实是推荐这个的。
通用表空间（General Tablespaces）
通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外 的其他表空间，其可以容纳多张表，且其支持所有的行格式。其文件也是ibd文件。具体理解可以去看上 面那张整体结构图。
# 用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo表空间 等多种类型；
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间
撤销表空间（Undo Tablespaces） 撤销表空间由一个或多个包含Undo日志文件组成。在MySQL 5.7版本之前Undo占用的是System Tablespace共享区，从5.7开始将Undo从System Tablespace分离了出来。InnoDB使用的undo表空间 由innodb_undo_tablespaces配置选项控制，默认为0。参数值为0表示使用系统表空间ibdata1;大于0 表示使用undo表空间undo_001、undo_002等。
临时表空间（Temporary Tablespaces） 分为session temporary tablespaces 和global temporary tablespace两种。session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace储存用户 临时表的回滚段（rollback segments ）。mysql服务器正常关闭或异常终止时，临时表空间将被移除， 每次启动时会被重新创建。
frm文件和上面的这些文件有所重叠。 1.2.2、数据字典（InnoDB Data Dictionary） 
InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物 理上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件 （.frm文件）中存储的信息重叠。 1.2.3、双写缓冲区（Doublewrite Buffffer） 
位于系统表空间，是一个存储区域。在BufffferPage的page页刷新到磁盘真正的位置前，会先将数据存 在Doublewrite 缓冲区。如果在page页写入过程中出现操作系统、存储子系统或mysqld进程崩溃， InnoDB可以在崩溃恢复期间从Doublewrite 缓冲区中找到页面的一个好备份(这个已经在系统里面了， mysql宕机不影响，但是系统崩了，那就。。。而且往这里面写也快一些，我们可以看到mysql设置了大 量的多级缓冲)。在大多数情况下，默认情况下启用双写缓冲区，要禁用Doublewrite 缓冲区，可以将 innodb_doublewrite设置为0。如果使用Doublewrite 缓冲区时建议将innodb_flflush_method设置为 O_DIRECT，可以提高销量表，缓冲区可以合并多次io操作为一次。
1.2.4、重做日志（Redo Log） 
重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。MySQL以循环 方式写入重做日志文件，记录InnoDB中所有对Buffffer Pool修改的日志。当出现实例故障（像断电）， 导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。读写事务在执行 的过程中，都会不断的产生redo log。默认情况下，重做日志在磁盘上由两个名为ib_logfifile0和 ib_logfifile1的文件物理表示。 1.2.5、撤销日志（Undo Logs） 
撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志属于逻辑日 志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。
MySQL的innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。有三个 值：fdatasync(默认)，O_DSYNC，O_DIRECT。设置O_DIRECT表示数据文件写入操作会通知操作系统不要 缓存数据，也不要用预读，直接从InnodbBuffer写到磁盘文件。 默认的fdatasync意思是先写入操作系统缓存，然后再调用fsync()函数去异步刷数据文件与redo log的缓 存信息。
1.3、新版本结构演变 
上图是8.0了，开始是5.7的，你可以对比看看，确实有的地方不一样了。 1.3.1、MySQL 5.7 版本 
将 Undo日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件 大小和数量。
增加了 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。 Buffffer Pool 大小可以动态修改，无需重启数据库实例。 1.3.2、MySQL 8.0 版本 
将InnoDB表的数据字典和Undo都从共享表空间ibdata中彻底分离出来了，以前需要ibdata中数据字典 与独立表空间ibd文件中数据字典一致才行，8.0版本就不需要了。 temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快 了处理的速度。
用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个 表使用，但一个表只能存储在一个表空间中。
将Doublewrite Buffffer从共享表空间ibdata中也分离出来了，也就有了双写的缓冲区的文件了。 2、InnoDB线程模型 
mysql有一些后台线程，是用来维护缓冲数据中的更新和刷盘操作。
2.1、IO Thread 
在InnoDB中使用了大量的AIO（Async IO 异步io读写）来做读写处理，这样可以极大提高数据库的性 能。在InnoDB1.0版本之前共有4个IO Thread，分别是write，read，insert buffffer和log thread，后 来版本将read thread和write thread分别增大到了4个，一共有10个了。
可以通过SHOW ENGINE INNODB STATUS;语句去查看这些个数信息。 read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。一共有4个读线程 write thread：负责写操作，将缓存脏页刷新到磁盘。4个 log thread：负责将日志缓冲区内容刷新到磁盘。1个 insert buffffer thread ：负责将写缓冲内容刷新到磁盘，就是你磁盘和change buffer合并之后， 由这个线程刷入磁盘。1个 2.2、Purge Thread 
事务提交之后，其使用的undo日志将不再需要，不需要用来做回滚了，因此需要Purge Thread回收已 经分配的undo页，减少空间占用。 show variables like '%innodb_purge_threads%';
2.3、Page Cleaner Thread 
作用是将脏数据刷新到磁盘，脏数据刷盘后相应的redo log也就可以覆盖，就能清除这些redo log之类 的一些释放，即可以同步数据，又能达到redo log循环使用的目的。其内部是会调用write thread线程处 理操作的。
show variables like '%innodb_page_cleaners%';
2.4、Master Thread 
Master thread是InnoDB的主线程，负责调度其他各线程，优先级高。作用是将缓冲池中的数据异步 刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（page cleaner thread）、undo页回收（purge thread）、redo日志刷新（log thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒 处理。这两个主处理都是存在的，一起作用。
因为都在主线程里面做会效率低，就成了redis，所以分出多个线程。当然了你启用这些线程才能做，要 是都没启用，那就还是在主线程做。
每1秒的操作： 刷新日志缓冲区，刷到磁盘 合并写缓冲区数据，根据IO读写压力来决定是否操作，不是真的每秒都做，每秒判断一次是 否操作 刷新脏页数据到磁盘，根据脏页比例达到75%才操作（innodb_max_dirty_pages_pct， innodb_io_capacity） 这个75%可以通过show variables like '%innodb_max_dirty_pages_pct%';查看，也可 以修改。
当然了还有一个是一次刷多少页的配置;
每10秒的操作： 刷新脏页数据到磁盘 合并写缓冲区数据 刷新日志缓冲区 删除无用的undo页,这个无用的一次是删除300个page页show variables like '%innodb_purge_batch_size%';
3、InnoDB数据文件 3.1、InnoDB文件存储结构 
InnoDB数据文件存储结构： 分为一个ibd(默认是独立表空间)数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row （行）
可以这么说;每个表有自己的独立表空间，对应就是一个表名.ibd文件 每个表空间内部有多个segment段，有什么数据段，索引段，事务回滚段
每个段又由多个区Extent组成 每个区有多个page组成，具体就是64个连续的page页组成一个区 每个page就是有多个数据行记录Row组成，每个行有各个部分组成，什么事务id，指针，然后就是属性 值(具体的数据)
Tablesapce 表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。 Segment 段，用于管理多个Extent，分为数据段（Leaf node segment）叶子上是数据、索引段 （Non-leaf nodesegment）、回滚段（Rollback segment）。一个表至少会有两个 segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment(肯定会多 一个数据段和索引段，叶子和非叶子)。 Extent 区，一个区固定包含64个连续的页，大小为1M(每个页是16K)。当表空间不足，需要分配新的 页资源，不会一页一页分，直接分配一个区。 Page 页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系 统页，事务数据页，大的BLOB对象页。 Row 行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）和事务有关，后面 说、字段指针（Fieldpointers）便于获取字段值等信息。
Page是文件基本的单位，无论何种类型的page，都是由page header，page trailer和pagebody组 成。如下图所示，
3.2、InnoDB文件存储格式 
通过 SHOW TABLE STATUS 命令
一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format 为DYNAMIC和COMPRESSED，文件格式为Barracuda。
通过 information_schema 查看指定表的文件格式
select * from information_schema.innodb_sys_tables;
3.3、File文件格式（File-Format） 
在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于支持 新的功能。目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。 Antelope: 先前未命名的，原始的InnoDB文件格式，它支持两种行格式：COMPACT和 REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。 Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。
通过innodb_fifile_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本开始 改为Barracuda。 3.4、Row行格式（Row_format） 
表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个 page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的 I/O更少。 InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。
DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前 缀。
每个表的数据分成若干页来存储，每个页中采用B树结构存储；
如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页， REDUNDANT 行格式 使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余的存储在溢 出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便能够在页外存储。
COMPACT 行格式 与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了 某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式 可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。 DYNAMIC 行格式 使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包 含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。 DYNAMIC行格式支持大索引前缀，多可以为3072字节，可通过innodb_large_prefifix参数 控制。 COMPRESSED 行格式 COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数 据压缩的支持。该字段被称为页外列。
在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件格式 的方法是重新创建表及其索引，简单方法是对要修改的每个表使用以下命令：
3.5、Undo Log 3.5.1、 Undo Log介绍 
Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。 Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩 溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，即便数据确认提交了，也并不会 立刻删除undo log，因为可能涉及到MVCC的操作还会用，innodb会将该事务对应的undo log放入到删 除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过 程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的 update，因为是用作回滚用的，所以记录一个相反的操作。
逻辑日志的意思就是记录一个变化过程。
Undo Log存储：undo log采用段的方式管理存储和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment，早期只有一个。可以通过下面一组参数来控制 Undo log存储。
第一个是文件目录：
第二个是
第三个是每个undolog的文件大小，默认是128K
第四个是文件位于的表空间号，0表示系统表空间。你要是改为大于0的数字，比如2，表示就使用undo tablespace，而且undolog文件的个数为2，undo_001,undo_002 3.5.2、Undo Log作用 
实现事务的原子性 Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用 户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前 的状态。 实现多版本并发控制（MVCC） Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事 务进行快照读。
ALTER TABLE 表名 ROW_FORMAT=格式类型;
show variables like '%innodb_undo%';
事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffffer 中。 事务B手动开 启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读
3.6、Redo Log和Binlog 
Redo Log和Binlog是MySQL日志系统中非常重要的两种机制，也有很多相似之处，下面介绍下两者细节 和区别。 3.6.1、 Redo Log日志 
Redo Log介绍 Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。你可以理解为，操作失 败了，重新来一次，就用这玩意。
Redo Log：指事务中修改的任何数据，将新的数据备份存储的位置（Redo Log），被称为重做日 志。顺序写磁盘的。
Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log 写入Log Buffffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后， Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。
# 这里第一次出现mvcc，我解释一下，重点理解，当然后面还会讲，这里先打一下基础 首先两个事务AB， 1、A事务在开启前先把id=1的数据备份到undo buffer中。存一个相反的操作数据，这个还会异步持久化到 Undo Log中，具体看服务操作。 2、A事务在BP或者CP中操作user表，修改id为1的age列。当然后面的持久化和加载都是后台线程做，这里只 说流程，不细分析了。 3、此时事务B开启做了id =1的数据的快照读，但是因为A此时还没提交事务呢，所以你要是从A正操作的那块 的缓冲区(BP)里面读，就读到了当前数据了，读到了没提交的数据，取到的是被改过的了，这就不是快照读 了。所以此时事务B可以去undo buffer中去拿到前面A备份的。这里没有还可以去Undo Log中拿。这就能拿 到之前的了。就不会读到事务A没提交的了。你看是不是像解决了那种脏读问题呢？这个后面事务的时候说。 4、要是你事务A执行了rollback 回滚操作，还能通过这个undo log回滚。
# 那么他为什么被提出呢？ 我们前面知道，mysql写操作，是操作的buffer poll（当然也有cp的操作，这里就拿bp举例子）。bp写完 了不是立即就写回磁盘的。 那么问题来了，此时要是突然宕机，你的写操作不就丢了么，因为你提交了事务之后就写入了bp，此时丢了那 就麻了。客户端认为我都提交了事务，为啥还能丢呢？ 于是引出redo log，我们在每次修改完bp的数据之后，同时把操作写入undo log然后提交事务(二阶段)， 此时才算提交完成，要是此时宕机了，在恢复服务的时候就能通过这个文件记录的来恢复期间还没写入磁盘的 数据。 那你可能问了，为啥不写入磁盘之后再算提交呢？这样不就不用redo log了吗。这个存疑，后面看看TODO。
Redo Log工作原理 Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持 久化这一特性。
Redo Log写入机制 Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。这个可以 结合45讲那里一起理解。
# 这里解释三个问题： 1、为什么不直接写进ibd磁盘，而是先走了redo buffer（下面我就写为rb），然后持久化到redolog磁盘 文件，恢复的时候用redolog呢？你是不是脱了裤子放屁。 答：你要是直接写入ibd磁盘文件，那么你每次提交事务都得io一次磁盘，还得是操作线程做，这样很慢。所 以他先是写入到rb中，写内存缓冲肯定是要快的，等到攒够数了，一次写入redolog。 2、当数据写入redolog的时候，就安全了，宕机恢复就能用redolog恢复到user.ibd文件了。要是你还在 rb中，此时宕机，数据会丢。谁告诉你mysql不丢数据了？ 3、为什么要写进redolog呢？而不是从rb中直接进ibd磁盘？ 答：假如我们一次操作了两条数据id=1 id=999两条数据，你要是直接写进ibd磁盘，可能这两个数据不在一 个页，不连续。你就要找到两个数据的位置一起改了，这样就不是所谓的顺序io了，需要磁头转动去找到对应 位置去修改，很耗时间。但是你要是写入redolog不需要找数据位置，就顺序写进去就好了。等宕机恢复的时 候再根据这个redolog写入ibd就行了，当然此时还是可能随机io，不过宕机可能性低，重启恢复时间久一点 也还好，总比比平时操作久强的多。
如图所示：
 write pos 是当前记录的位置，一边写一边后移，写到后一个文件末尾后就回到 0 号文件开头；
 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件； write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一 下。
Redo Log相关配置参数
每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认 为ib_logfifile0和ib_logfifile1。可以通过下面一组参数控制Redo Log存储：
Redo Buffffer 持久化到 Redo Log 的策略，可通过 Innodb_flflush_log_at_trx_commit 设置：
0：每秒提交 Redo buffffer ->OS cache -> flflush cache to disk，可能丢失一秒内的事务数据。由后台 Master线程每隔 1秒执行一次操作。 1（默认值）：每次事务提交执行 Redo Buffffer -> OS cache -> flflush cache to disk，安全，性能 差的方式。
2：每次事务提交执行 Redo Buffffer -> OS cache，然后由后台Master线程再每隔1秒执行OScache -> flflush cache to disk 的操作。
一般建议选择取值2，因为 MySQL 挂了数据没有损失(因为数据已经进OS CACHE了，这个是服务器的空 间了，不是Mysql的)，整个服务器挂了才会损失1秒的事务提交数据。但是你一旦写进系统缓存，系统就 立即启动异步线程同步，丢数据很难。
show variables like '%innodb_log%';
3.6.2、Binlog日志 
Binlog记录模式
Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binarylog（二进制 日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录 SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。binlog默认 是关闭的，会消耗系统资源的百分之一，所以默认关闭，开启Binlog日志有以下两个重要的使用场 景。(就是顺序把你的sql写进去了，可以参考redis的AOF机制.) 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实 现数据恢复达到主从数据一致性。 数据恢复：通过mysqlbinlog工具来恢复数据,后面会介绍。
Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在在开启Binlog的 时候在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。 ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，把每一行的修改 细节，原来是啥，修改成啥了，然后在slave端对相同的数据进行修改。 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。要记录每一行的具体改了 啥，要是你空间海量那其实可以用这个好，因为他主从完全一致，至于为啥会有不一致往下看。 STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master 的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。 简称SQL语句复制。 优点：日志量小，减少磁盘IO，提升存储和恢复速度 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。因为你在主库的 时间，同步到从库去执行，时间必然不一致了。还有就是id可能不一样，要是你是随机id。或者自 增，万一有一个不一致id就不一致了，这就是上面说的主从不一致的情况。 MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT 模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog(比如上面说的那 种now()时间函数)，MySQL会根据执行的SQL语句选择写入模式。 Binlog文件结构，(可以参考青蛙哥的公众号)
MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Logevent。 不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。 Binlog文件中Log event结构如下图所示：
Binlog写入机制
根据记录模式和操作触发event事件封装生成log event（事件触发执行机制） 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个自己的独立缓冲区Log Event 保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于 存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。有的引擎不支持事务就 去不支持的，innodb就去trx_cache。 事务在提交阶段会将产生的log event写入到外部binlog文件中。 不同事务以串行方式(控制并发了)将log event写入binlog文件中，所以一个事务包含的log event信 息在binlog文件中是连续的，中间不会插入其他事务的log event。
Binlog文件操作 Binlog状态查看
开启Binlog功能
需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启MySQL服 务。
show variables like 'log_bin'; 会有一个sqllogbin的配置，这个开启是记录你使用Binlog恢复数据的时候的执行操作，这个其实不应 该记，你恢复数据的操作记啥，造成冗余，还没用。所以这个建议关闭。
mysql> set global log_bin=mysqllogbin; ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
使用show binlog events命令
使用mysqlbinlog 命令，这个是Mysql服务端的，你得进去mysql服务里面去执行。
使用 binlog 恢复数据
mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。 删除Binlog文件
可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超 出1天binlog文件会自动删除掉。 Redo Log和Binlog区别，这个建议结合45讲理解 Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记 录。 Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。 Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖 使用。 Redo Log作为服务宕机后事务数据的自动恢复，binlog可以作为主从复制和数据恢复。binlog没有 事务这一说，没有状态，他不知道哪些数据是提交了，哪些数据是还没提交这个。
[][] [Mysql事务数据恢复][https://zhuanlan.zhihu.com/p/142491549] 四、MySql索引原理 
#log-bin=ON #log-bin-basename=mysqlbinlog binlog-format=ROW log-bin=mysqlbinlog
show binary logs; //等价于show master logs; 查看有哪些binglog日志。 show master status;// 查看当前正用的那个binlog show binlog events;// 查看目前为止有哪些事件 show binlog events in 'mysqlbinlog.000001';// 查看具体的哪个binlog文件
mysqlbinlog "文件名" mysqlbinlog "文件名" > "test.sql"
//按指定时间恢复 ，可以先去Binlog文件里面看看执行语句的时间。要是大体知道，可以不看，直接 指定范围内的恢复。 mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="202004-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 //按事件位置号恢复 mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234
purge binary logs to 'mysqlbinlog.000001'; //删除指定文件 purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 reset master; //清除所有文件
1、索引类型 
索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下： 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引(多维度B tree。 不常用) 从应用层次划分：普通索引、唯一索引、主键索引、复合索引 从索引键值类型划分：主键索引、辅助索引（二级索引） 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）数据和索引在一起，也就是主键索 引、非聚集索引（非聚簇索引），数据和索引不在一起，也就是普通的二级索引。 1.1、普通索引 
这是基本的索引类型，基于普通字段建立的索引，没有任何限制。
创建普通索引的方法如下：
CREATE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);//修改表的时候创建 CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );//建表的时候创建
查看索引的方法如下：
1.2、唯一索引 
与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值(不允许的就是主键了) 。在创 建或修改表时追加唯一约束，就会自动创建对应的唯一索引。多个为空的时候不冲突唯一性。
创建唯一索引的方法如下：
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名); CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ; 1.3、主键索引 
它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主 键。
创建主键索引的方法如下：
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); ALTER TABLE tablename ADD PRIMARY KEY (字段名); 1.4、复合索引 
单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索 引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合 索引所需的开销更小。
索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过 2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
创建复合索引的方法如下：
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
SHOW INDEX FROM 表名;
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...); CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );
复合索引使用注意事项：
何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效 率有很大影响。 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查 询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。至于原理下面会 说。 1.5、全文索引(TODO) 
查询操作在数据量比较少时，可以使用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 [索引的名字] (字段名) ; 和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
全文索引使用注意事项：
全文索引必须在字符串、文本字段上建立。在别的类型上没效果，不会生成索引。 全文索引字段值必须在小字符和大字符之间的才会有效。（innodb：3-84；myisam：4-84） 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa,要是你没 有+切符，你的查询就不会模糊。 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布 尔模式下搜索a*
2、索引原理 
MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。因为需要额外开辟空间和数据 维护工作。
索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。innodb的 理念就是索引即数据，数据即索引。你要调大页大小是可以存更多的索引的每个页。 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价，因为你插入数据 要重整索引树。
索引涉及的理论知识：二分查找法、Hash和B+Tree。 2.1、二分查找法 
二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范 围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高(因为要维护有序)。
select * from user where match(name) against('aaa');
select * from user where match(name) against('a*' in boolean mode);
举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下： 1、第一次查找
2、第二次查找
3、第三次查找
首先定位left和right两个指针 计算(left+right)/2 判断除2后索引位置值与目标值的大小比对 索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动
4、第四次查找
2.2、Hash结构 
Hash底层实现是由Hash表来实现的，是根据键值 存储数据的结构。非常适合根据key查找 value值，也就是单个key查询，或者说等值查询。其结构如下所示：
从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了，因为 哈希是根据hash函数映射的，所以没有顺序，无法范围查询只能全表扫。 Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。
InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。 InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当 InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内 存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。
InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于 B+Tree。 自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建 立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行 人工干涉。
2.3、B+Tree结构 
MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。 B-Tree结构
索引值和data数据分布在整棵树结构中,叶子和非叶子都存值 每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中 会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
B+Tree结构 非叶子节点不存储data数据，只存储索引值(理解为就是主键值，要是二级索引就是你索引的列值，参考 青蛙哥的书)，这样便于存储更多的索引值 叶子节点包含了所有的索引值和data数据
叶子节点用双向指针连接(下图没标明)，提高区间的访问性能
show engine innodb status \G; 查看当前状态，里面有hash索引使用前后的性能表现 show variables like '%innodb_adaptive%';
相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍 历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。 2.4、聚簇索引和辅助索引 
聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行 记录分开存放就属于非聚簇索引。
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值 就属于辅助索引（二级索引）。
在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。
聚簇索引（聚集索引）
聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子 节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本 身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是 聚集索引。
InnoDB的表要求必须要有聚簇索引：
如果表定义了主键，则主键索引就是聚簇索引 如果表没有定义主键，则第一个非空unique列作为聚簇索引 再没有非空唯一列，InnoDB会从建一个隐藏的row-id作为聚簇索引
辅助索引
InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存 了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多(没有其他数据)， 通常创建辅助索引 就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
非主键索引检索的时候要回表取全部数据。
非聚簇索引
与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。
不管是主键索引还是辅助索引数据和索引都是分开的，你在索引查完都要去数据文件去取数据。 3、索引分析与优化 3.1、 EXPLAIN 
MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信 息，供开发人员有针对性的优化。例如：
EXPLAIN 命令的输出内容大致如下：\G表示纵向显示。
下面就对这几个属性一一分析意思，看懂意思才能知道怎么分析执行计划。 3.1.1、select_type 
表示查询的类型。常用的值如下：
例子
EXPLAIN SELECT * from user WHERE id < 3;
SIMPLE：表示查询语句不包含子查询或union,就是一个简单的查询。
PRIMARY：表示此查询是最外层的查询，就是要是有嵌套，子查询那种，最外层的就是这个。
UNION：表示此查询是UNION的第二个或后续的查询，嵌套或者联合的时候内层的。
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用到了外面查询结果。
UNION RESULT：UNION的结果，联合的结果。
SUBQUERY：SELECT子查询语句就属于这个类型。
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。
最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。
而且前面的id就是执行顺序，id值小的先执行，大的后执行，相等的谁在前面谁先执行。这就是一个sql 的执行过程，mysql后面就这么执行。注意这个只是Mysql的执行计划，不一定就真的这么搞，只是作为 参考。
3.1.2、type(参考青蛙哥) 
表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基 于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
index 现在我的user表只有id是主键有索引，其他字段没有索引。
这个我们直接查user查询条件就是username，我们看到他会去扫全表，找到这个username = lwq的数 据，你可能会问，找到lwq就返回不就行了，为啥要走完全表呢？因为username这不是唯一索引，不知 道有几个，所以要扫完。结合扫描区间来看。
我们条件和上面还是一致，只不过加了一个order by id。id是索引字段(主键)，我们按照索引去排序了， 他就是先按索引排序，在排序的索引树上面找username=lwq的，因为name还不是唯一，所以还要遍历 索引全表，只不过此时是有序的。其实就是走索引了，但是走的是全部索引树。在索引树上找 username。All也是在索引树上找username，但是不是基于索引的前提下的。
ALL：表示全表扫描，性能最差。这种一般就要优化了。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。可能要问了，你这个弄了半天还是扫全表， 而且还多了一个扫索引，你怎么就比上面的ALL效率高了，因为上面那个ALL，是直接全表扫，要是碰上排序操 作，你这个最后还得排序，无序扫再排序就很伤。而这里index先按照索引扫，索引是有序的，最后按照有序 的索引去找全表数据，排序方面能优化不少。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等，在索引上使用范围缩小了扫描区间，少扫描 了一些行数。
ref：表示使用非唯一索引(普通索引)进行单值查询(单值查询就是只有一行，要是有多个就是ALL)。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果，其实 就是你表设计的时候就是一对一的关系，最后连表走索引就是这样。
const：表示使用主键或唯一索引做等值查询(等值查询唯一索引出来也就是一行了)，常量查询。
NULL：表示不用访问表，速度最快，就像那种select 1直接返回1，不走表。
3.1.3、possible_keys 
表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。 3.1.4、key 
表示查询时真正使用到的索引，显示的是使用到的索引名称。 3.1.5、rows 
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效 率越高，可以直观的了解到SQL效率高低。 3.1.6、key_len 
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。就是看你组合索引有多长，这个 key_len有多长，要是等于，说明都用上了，小于就是没全走。 key_len的计算规则如下：
3.1.7、Extra 
Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
# 字符串类型    字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4    char(n)：n*字符集长度    varchar(n)：n * 字符集长度 + 2字节(前两个字节不存东西，设计如此) # 数值类型    TINYINT：1个字节    SMALLINT：2个字节    MEDIUMINT：3个字节    INT、FLOAT：4个字节 &nbs