JAVA技术体系之分布式篇（六）——MySQL数据库

1、一条查询语句的执行流程

1.1 MySQL基本架构

  总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层。

1.2 连接层

  我们的客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。
  MySQL服务监听的端口默认是3306,客户端连接服务端的方式有很多，可以是同步的也可以是异步的，可以是长连接也可以是短连接，可以是TCP也可以是Unix Socket, MySQL有专门处理连接的模块，连接的时候需要验证权限。

  查看MySQL当前有多少个连接：

show global status like 'Thread%';

  客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。反过来，如果要杀死会话，就是Kill线程。MySQL会把那些长时间不活动的（SLEEP）连接自动断开。

  查看会话超时时间：

show global variables like 'wait timeout';--非交互式超时时间，如JDBC程序
show global variables like 'interactive timeout';--交互式超时时间，如数据库工具
--默认都是28800秒，8小时

  查看最大连接数：

show variables like 'max connections';--在5.7版本中默认是151个，最大可以设置成100000

1.3 服务层

  连接层会把SQL语句交给服务层，这里面又包含一系列的流程：比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）。然后就是优化器，MySQL底层会根据一定的规则对我们的SQL语句进行优化，最后再交给执行器去执行。

1.3.1 查询缓存

MySQL内部自带了一个缓存模块但默认是关闭的。

  查看mysql缓存是否启用

show variables like 'query_cache%';

  默认关闭的意思就是不推荐使用，主要是因为MySQL自带的缓存的应用场景有限，第一个是它要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL；第二个是当表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。
  MySQL 8.0中，查询缓存已经被移除了。

1.3.2 解析器（Parser）

词法解析

  词法分析就是把一个完整的SQL语句打碎成一个个的单词。

  比如一个简单的SQL语句：

select name from user where id = 1;

  它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。

语法解析

  第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合, 然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树(select lex)。

  词法语法分析是一个非常基础的功能，Java的编译器、百度搜索引擎如果要识别语句，必须也要有词法语法分析功能。任何数据库的中间件，要解析SQL完成路由功能，也必须要有词法和语法分析功能, 比如Mycat, Sharding-JDBC （用到了 Druid Parser）。在市面上也有很多的开源的词法解析的工具（比如LEX, Yacc）。

1.3.3 预处理器（Preprocessor）

  那如果我写了一个词法和语法都正确的SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如：

select * from xxxx;

  解析器可以分析语法，但它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检査生成的解析树，解决解析器无法解析的语义。比如，它会检査表和列名是否存在，检査名字和别名，保证没有歧义。

1.3.4 查询优化器（Query Optimizer）

  得到解析树之后，还是无法马上执行，因为一条SQL语句可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？这就是MySQL査询优化器的功能。
  査询优化器的目的就是根据解析树生成不同的执行计划(Execution Plan),然后选择一种最优的执行计划，MySQL里面使用的是基于开销(cost)的优化器，哪种执行计划开销最小，就用哪种。

  査看査询SQL的开销：

show status like 'Last_query_cost';

  优化器可以做许多事情，例如当我们对多张表进行关联查询的时候，确定以哪个表的数据作为基准表；当有多个索引可以使用的时候，选择使用哪个索引。实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，这就需要我们在写SQL语句的时候去思考和关注了。

  优化器最终会把解析树变成一个査询执行计划，我们在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息：

EXPLAIN select name from user where id=1;

  如果要得到详细的信息，还可以用FORMAT=JSON,或者开启optimizer trace。

EXPLAIN FORMAT=JSON select name from user where id=1;

1.3.5 执行引擎（Execution Engine）

  确定完最优的执行计划之后，执行引擎负责执行对应的执行计划，通过调用存储引擎提供的API完成操作，并返回结果。

1.4 存储引擎层

  存储引擎就是我们的数据真正存放的地方，在MySQL里面支持不同的存储引擎。再往下就是内存或者磁盘。
  常见存储引擎：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

1.4.1 MyISAM

  应用范围比较小。表级锁限制了读/写的性能，因此在Web和数据仓库配置中，它通常用于只读或以读为主的工作。

• 支持表级别的锁（插入和更新会锁表）。
• 不支持事务。
• 拥有较高的插入（insert）和查询（select）速度。
• 存储了表的行数（count速度更快）。

1.4.2 InnoDB

  MySQL 5.7中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致，非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。

• 支持事务，支持外键，因此数据的完整性、一致性更高。
• 支持行级别的锁和表级别的锁。
• 支持读写并发，写不阻塞读（MVCC）。
• 特殊的索引存放方式，可以减少I。，提升査询效率。

1.4.3 Memory

  将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbduster为大型分布式数据集提供了快速的键值查找。

• 读写的速度很快。
• 数据库重启或者崩溃，数据会全部消失。
• 只适合做临时表。

1.4.4 CSV

  它的表实际上是带有逗号分隔值的文本文件。CSV表允许以CSV格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入或导出阶段使用CSV表。

• 不允许空行，不支持索引。
• 格式通用，可以直接编辑，适合在不同数据库之间导入导出。

1.4.5 Archive

  这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。

• 不支持索引。
• 不支持update delete。

1.4.6 存储引擎的选择

  不同的存储引擎提供的特性都不一样，它们有不同的存储机制、索引方式、锁定水平等功能。我们在不同的业务场景中对数据操作的要求不同，就可以选择不同的存储引擎来满足我们的需求，这个就是MySQL支持这么多存储引擎的原因。

• 如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
• 如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
• 如果需要一个用于查询的临时表，可以选择Memory。
• 如果需要存储归档数据，可以选择Archive。

  如果所有的存储引擎都不能满足需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html。按照这个开发规范，实现相应的接口，给执行器操作。这些存储引擎用不同的方式管理数据文件，提供不同的特性，但是为上层提供相同的接口。

2、InnoDB总体架构

2.1 InnoDB内存结构

2.1.1 Buffer Pool

  对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据, 必须先把磁盘里面的数据加载到内存里面才可以操作。磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的I/O操作。
  所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，我们认为它附近的位置也很有可能会被马上读取到，这个就叫做局部性原理。
  InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。操作系统的页大小一般是4KB，而在InnoDB里面，这个最小的单位默认是16KB大小。如果要修改这个值的大小，需要清空数据重新初始化服务。
  InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取并操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。这个内存区域有个专属的名字，叫Buffer Pool。Buffer Pool缓存的是页面信息，包括数据页、索引页。
  修改数据的时候，也是先写入到buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

  查看Buffer Pool 内存大小（默认128M）：

SHOW VARIABLES like '%innodb_buffer_pool%';

LRU算法

  InnoDB用LRU算法来管理缓冲池,经过淘汰的数据就是热点数据。

  传统LRU，可以用Map+链表实现。value存的是在链表中的地址。

  InnoDB与传统LRU算法有一些差别

1）线性预读和随机预读

  首先，InnoDB的数据页并不是都是在访问的时候才缓存到buffer pool的。InnoDB有一个预读机制（read ahead）。也就是说，设计者认为访问某个page的数据的时候，相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool中缓存起来。
  这种预读的机制又分为两种类型，一种叫线性预读（异步的）（Linear read-ahead）。为了便于管理，InnoDB中把64个相邻的page叫做一个extent（区）。如果顺序地访问了一个extent的56个page，这个时候InnoDB就会把下一个extent （区）缓存到 buffer pool 中。

  顺序访问多少个page才缓存下一个extent,由一个参数控制：

show variables like 'innodb_read_ahead_threshold'

  第二种叫做随机预读（Random read-ahead）,如果buffer pool已经缓存了同一个extent （区）的数据页的个数超过13时，就会把这个extent剩余的所有page全部缓存到buffer pool。

  随机预读的功能默认关闭：

show variables like 'innodb_random_read_ahead'

2）冷热分离

  但是预读也带来了一些副作用，就是导致占用的内存空间更多，剩余的空闲页更少。如果说buffer pool size不是很大，而预读的数据很多，很有可能那些真正的需要被缓存的热点数据被预读的数据挤出buffer pool，淘汰掉了。下次访问的时候又要先去磁盘。
  InnoDB把LRU list分成两部分，靠近head的叫做new sublist，用来放热数据（我们把它叫做热区）。靠近tail的叫做old sublist，用来放冷数据，我们把它叫做冷区）。中间的分割线叫做midpoint。也就是对buffer pool做 一个冷热分离。

  所有新数据加入到buffer pool的时候，一律先放到冷数据区的head,不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在old sublist （冷区）直接被淘汰。放到LRU List以后，如果再次被访问，都把它移动到热区的head。如果热区的数据长时间没有被访问，会被先移动到冷区的head部，最后慢慢在tail被淘汰。
  在默认情况下，热区占了 5/8的大小，冷区占了 3/8，这个值由innodb_old_blocks_pct控制，它代表的是old区的大小，默认是37%也就是3/8。innodb_old_blocks_pct的值可以调整，在5%到95%之间，这个值越大，new区越小，这个LRU算法就接近传统LRU。如果这个值太小，old区没有被访问的数据淘汰会更快。

3）设置冷区锁定时间

  我们先把数据放到冷区，用来避免占用热数据的存储空间。但是如果刚加载到冷区的数据立即被访问了一次，按照原来的逻辑，这个时候我们会马上把它移动到热区。假设这一次加载然后被立即访问的冷区数据量非常大，比如我们查询了一张几千万数据的大表，没有使用索引，做了一个全表扫描。或者，dump全表备份数据，这种查询属于短时间内访问，后面再也不会用到了。这样就会导致它们全部被移动到热区的head，而很多热点数据被移动到冷区甚至被淘汰，造成了缓冲池的污染。
  InnoDB对于加载到冷区然后被访问的数据，设置一个时间窗口, 只有超过这个时间之后被访问，我们才认为它是有效的访问。由参数innodb_old_blocks_time参数控制，默认是1秒。这样就可以从很大程度上避免全表扫描或者预读的数据污染真正的热数据。

4）惰性数据移动

  为了避免并发的问题，对于LRU链表的操作是要加锁的。也就是说每一次链表的移动，都会带来资源的竞争和等待。从这个角度来说，如果要进一步提升InnoDB LRU的效率，就要尽量地减少LRU链表的移动。
  对此，InnoDB对于new区还有一个特殊的优化：如果一个缓存页处于热数据区域，且在热数据区域的前1/4区域，那么当访问这个缓存页的时候，就不用把它移动到热数据区域的头部；如果缓存页处于热区的后3/4区域，那么当访问这个缓存页的时候，会把它移动到热区的头部。

2.1.2 Change Buffer

  如果一个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert，Delete，Update）的执行速度。
  这一块区域就是Change Buffer。Change Buffer 是 Buffer Pool 的一部分。5.5之前叫Insert Buffer插入缓冲，现在也能支持 delete 和 update。
  最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge? 有几种情况：在访问这个数据页的时候、或者通过后台线程、或者数据库shut down、 redo log写满时触发。
  如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用Change Buffer （写缓冲）。

  可以通过调大这个值，来扩大Change的大小，以支持写多读少的业务场景：

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

  代表 Change Buffer 占 Buffer Pool 的比例，默认 25%。

2.1.3 Adaptive Hash Index

todo

2.1.4 Log Buffer（先了解2.2.6 Redo Log）

  Redo Log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域(Log
Buffer)专门用来保存即将写入日志文件的数据，它一样可以节省磁盘IO。

  查看Log Buffer大小

SHOW VARIABLES LIKE 'innodb_log_buffer_size';--默认16M

  需要注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件。
  在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。
  Log buffer写入磁盘的时机，由一个参数控制，默认是1:

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

• 0 （延退写） log buffer将每秒一次地写入log file中,并且log file的flush操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。
• 1 （默认，实时写，实时刷）每次事务提交时MySQL都会把log buffer的数据写入log file，并且刷到磁盘中去。
• 2 （实时写，延退刷）每次事务提交时MySQL都会把log buffer的数据写入log file。但是flush操作并不会同时进行。该模式下，MySQL会每秒执行一次flush操作。
  

2.2 InnoDB磁盘结构

2.2.1 系统表空间（System Tablespace）

  在默认情况下InnoDB存储引擎有一个共享表空间,，叫系统表空间。InnoDB系统表空间包含InnoDB数据字典和双写缓冲区、Change Buffer和Undo Logs（Change Buffer和Undo Logs由于可以独立于系统表空间，不在这里介绍），如果没有指定file-per-table，也包含用户创建的表和索引数据。

• 数据字典：由内部系统表组成，存储表和索引的元数据。
• 双写缓冲（InnoDB的一大特性）:
    InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。
  
    如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K就宕机了，这种情况叫做部分写失效(partial page write)，可能会导致数据丢失。
    虽然有redo log做崩溃恢复，但是如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write，InnoDB的双写技术。通过它实现了数据页的可靠性。因为double write是顺序写入的，不会带来很大的开销。

  在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。

2.2.2 独占表空间（File-Per-Table Tablespace）

  我们可以让每张表独占一个表空间。

  这个开关通过innodb_file_per_table设置，默认开启。

SHOW VARIABLES LIKE 'innodb_file_per_table';

  开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的ibd文件，存放表的索引和数据。但是其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。

2.2.3 通用表空间（General Tablespace）

  通用表空间也是一种共享的表空间。

• 我们可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。
• 在创建表的时候可以指定表空间，也可以用ALTER修改表空间。
• 删除表空间需要先删除里面的所有表：

2.2.4 临时表空间（Temporary Tablespace）

  存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。当数据服务器正常关闭时，该表空间被删除，下次重新产生。

2.2.5 undo表空间（Undo Tablespace）

  undo Log的数据默认在系统表空间中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。
  undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态，分为insert undo log和update undo log。如果修改数据时出现异常，可以用undo log来实现回滚操作 （保持原子性）。
  

show global variables like '%undo%';

2.2.6 Redo Log

  前面了解了Buffer Pool以及刷脏的操作。因为刷脏的操作不是实时的，如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。
  为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件。
  如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现崩溃恢复）。我们说的事务的ACID里面D （持久性），就是用它来实现的。这个日志文件就是磁盘的redo log （叫做重做日志）。
  那么同样是写磁盘，为什么不直接写到dbfile里面去？为什么先写日志再写磁盘？写日志文件和和写到数据文件有什么区别呢？这就涉及到磁盘顺序写和随机写了，通常情况下顺序写的效率要比随机写高出许多，因为随机写涉及不断寻址的一个过程（详见JAVA知识体系之分布式篇（四）——Kafka 4.3.3.1）。

show variables like 'innodb_log%';

1. redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性。
2. redo log不是记录数据页更新之后的状态，而是记录的是"在某个数据页上做了什么修改”。属于物理日志。
3. redo log的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool 到磁盘的同步，以便腾出空间记录后面的修改。

2.3 InnoDB后台线程

  后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread，IO thread，purge thread，page cleaner thread。

• master thread负责刷新缓存数据到磁盘并协调调度其它后台进程。
• IO thread 分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer、重做日志、读写请求的IO回调。
• purge thread 用来回收 undo 页。
• page cleaner thread用来刷新脏页。

3、一条更新语句的执行流程（binlog模式下）

3.1 binlog

  在了解更新语句的执行之前，需要先了解binlong，它是属于Server层的日志文件。binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。
  在开启了 binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。
  binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。

3.2 一条更新语句的执行流程

  例如一条语句：update teacher set name=‘盆鱼宴’ where id=1;

1. 先查询到这条数据，如果有缓存，也会用到缓存。
2. 把name改成盆鱼宴，然后调用引擎的API接口，写入这一行数据到内存， 同时记录redo logo这时redo log进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
3. 执行器收到通知后记录binlog，然后调用存储引擎接口，设置redo log为 commit 状态。
4. 更新完成。

需要注意：

• 先记录到内存，再写日志文件。
• 记录redo log分为两个阶段。
• 存储引擎和Server记录不同的日志。
• 先记录redo log，再记录binlog，最后再更新redo log。

在崩溃恢复时，判断事务是否需要提交：

• binlog无记录，redolog无记录：在redolog写之前crash,恢复操作：回滚事务。
• binlog无记录，redolog状态prepare:在binlog写完之前的crash,恢复操作：回滚事务。
• binlog有记录，redolog状态prepare:在binlog写完提交事务之前的crash, 恢复操作：提交事务。
• binlog有记录，redolog状态commit:正常完成的事务，不需要恢复。

4、索引

4.1 索引是什么

4.1.1 索引的定义

  数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、 更新数据库表中数据。

  数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据, 直到找到这条数据。但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。
  就像我们从一本500页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，我们只要确定内容对应的页码，就能很快地找到我们想要的内容。

4.1.2 索引的类型

  在InnoDB里面，索引类型有三种，普通索引、唯一索引(主键索引是特殊的唯一索引)、全文索引。

• 普通(Normal)：也叫非唯一索引，是最普通的索引，没有任何的限制。
• 唯一 (Unique)：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引。它还多了一个限制条件，要求键值不能为空。主键索引用primay key 创建。
• 全文(Fulltext)：针对比较大的数据，比如我们存放的是消息内容、一篇文章，有几KB的数据的这种情况，如果要解决like査询在全文匹配的时候效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

4.2 索引存储模型推演

4.2.1 二分查找

  我们可以利用二分查找的思想，每一次都把候选数据缩小一半。如果数据已经排过序的话，这种方式效率比较高。 所以第一个，我们可以考虑用有序数组作为索引的数据结构。

存在的问题

  有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题, 可能要挪动大量的数据(改变index)，所以只适合存储静态的数据。为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。
  为了解决这个问题，BST (Binary Search Tree)也就是我们所说的二叉査找树诞生了。

4.2.2 二叉查找树(BST Binary Search Tree)

  二叉查找树的特点是左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

存在的问题

  二叉查找树既能够实现快速查找，又能够实现快速插入。但是它有一个问题，就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)。
  例如，刚才的这一批数字，如果我们插入的数据刚好是有序的，2、6、11、13、17、22。它会变成链表（斜树），这种情况下就无法达到加快检索的目的，和顺序查找的效率是没有区别的。

  造成它倾斜的原因是因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。所以，我们需要一种左右子树深度相差不大，更加平衡的树。这个就是平衡二叉树，叫做Balanced binary search trees，或者AVL树。

4.2.3 平衡二叉树（AVL Tree）（左旋、右旋）

  平衡二叉树的定义：左右子树深度差绝对值不能超过1。
  比如左子树的深度是2，右子树的深度只能是1或者3。这个时候我们再按顺序插入1、2、3、4、5、6，—定是这样，不会变成一棵“斜树”。
  那它的平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过1呢？例如我们插入1、2、3。当我们插入了 1、2之后，如果按照二叉查找树的定义，3肯定是要在2的右边的，这个时候根节点1的右节点深度会变成2，但是左节点的深度是0,因为它没有子节点，所以就会违反平衡二叉树的定义。
  因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把2提上去，这个操作叫做左旋。

  同样的，如果我们插入7、6、5，这个时候会变成左-左型，就会发生右旋操作，把6提上去。

  所以为了保持平衡，AVL树在插入和更新数据的时候执行了一系列的计算和调整的操作。
  平衡的问题我们解决了，那么平衡二叉树作为索引怎么査询数据？在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？
  前面我们已经知道了，索引必须要存你建立索引的字段的值，叫做键值，比如id的值。还要存完整记录在磁盘上的地址。由于AVL树是二叉树，所以还要额外地存储左右子树的指针。
  第一个是索引的键值。比如我们在id上面创建了一个索引,我在用where id =1的 条件查询的时候就会找到索引里面的id的这个键值。
  第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。
  第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于26的时候，走右边，到下一个树的节点，继续判断。

存在的问题

  首先，索引的数据，是放在硬盘上的。查看数据和索引的大小：当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次IO操作。InnoDB操作磁盘的最小的单位是一页(或者叫一个磁盘块)，大小是16K(16384 字节)。
  那么，一个树的节点必须设计成16K的大小，不然就会出现读不完或者读不够的情况。如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到16K的容量。如果是机械硬盘时代，每次从磁盘读取数据需要10ms左右的寻址时间，交互次数越多，消耗的时间就越多。
  为了解决这个问题，我们会让每个节点存储更多的数据，一个是可以更好地利用资源，提升查询效率；另一个是当一个节点上的关键字的数量越多，指针数也就越多，树的分叉（路树）也就越多，树的深度就会减少。
  这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。这个就是我们的多路平衡查找树，叫做BTree （B代表平衡）。

4.2.4 多路平衡查找树（B Tree）（分裂、合并）

  跟AVL树一样，B树在枝节点和叶子节点存储键值、数据地址、节点引用。但它有一个特点：分叉数（路数）永远比关键字数多1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指钉指向三个子节点（当然肯定不只存3个这么少）。

  那B Tree又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟AVL树有什么区别？
  比如Max Degree (路数)是3的时候，我们插入数据1、2、3,在插入3的时候, 本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有4个指针, 子节点会变成4路，所以这个时候必须进行分裂。把中间的数据2提上去，把1和3变成2的子节点。
  如果删除节点，会有相反的合并的操作。注意这里是分裂和合并，跟AVL树的左旋和右旋是不一样的。我们继续插入4和5, B Tree又会出现分裂和合并的操作。

  从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不要更新主键。

4.2.5 B+树（加强版多路平衡查找树）

  B Tree的效率已经很高了,为什么MySQL还要对B Tree进行改良，最终使用了B+Tree 呢？
  总体上来说，这个B树的改良版本解决的问题比B Tree更全面。

  MySQL中的B+Tree有几个特点：

1. 它的关键字的数量是跟路数相等的。
2. B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。 目前我们的认知：这里要存放的数据是什么？是完整记录的地址。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜 索，一直到叶子节点。
3. B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据,形成了一个有序链表的结构。
     

  B+Tree的数据搜寻过程：

1. 比如我们要查找28,在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，28是［28,66）的左闭右开的区间的临界值，所以会走中间的子节点，然后继续搜索，它又是［28,34）的左闭右开的区间的临界值，所以会走左边的子节点，最后
   在叶子节点上找到了需要的数据。
2. 第二个，如果是范围查询，比如要查询从22到60的数据，当找到22之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。
     

  总结一下，InnoDB中的B+Tree特性带来的优势：

1. 它是BTree的变种，BTree能解决的问题，它都能解决。B Tree解决的两大问题是什么？（每个节点存储更多关键字，路数更多）
2. 扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵B+Tree拿到所有的数据）
3. B+Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存数据区, 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4. 排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5. 效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以I0次数是稳定的）

  我们举个例子：假设一条记录是16bytes, 一个叶子节点（一页）可以存储10条记录。非叶子节点可以存储多少个指针？
  假设索引字段+指针大小为16字节。非叶子节点（一页）可以存储1000个这样的单元（键值+指针），代表有1000个指针。树深度为2的时候，有1000^2个叶子节点，可以存储的数据为1000 * 1000 * 10=10000000 （千万级别）。在査找数据时一次页的査找代表一次IO，也就是说，一张千万级别的表，査询数据最多需要访问3次磁盘。
  树的深度是怎么来的？根据你的键值类型和数据量计算出来的。字段值越大、数据量越大，深度越大。所以在InnoDB中B+树深度一般为1-3层，它就能满足千万级的数据存储。

4.2.6 为什么不用红黑树?

  红黑树也是BST树，但是不是严格平衡的，通过变色和旋转来保持平衡。它有5个约束：

• 节点分为红色或者黑色。
• 根节点必须是黑色的。
• 叶子节点都是黑色的N U LL节点。
• 红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）。
• 从任意节点出发，至惧每个叶子节点的路径中包含相同数量的黑色节点。 插入：60、56、68、45、64、58、72、43、49。
  
    原因：它只有两路，且不够平衡。

4.3 B+Tree的实现

  MySQL的数据都是文件的形式存放在磁盘中的

show VARIABLES LIKE 'datadir';

  每个数据库有一个目录，我们新建了一个xxx数据库，那么这里就有一个文件夹xxx。这个数据库里面我们又建了5张表：archive，innodb，memory，myisam，csv。进入xxx的目录，发现这里面有一些跟我们创建的表名对应的文件。在这里我们能看到，每张InnoDB的表有两个文件（.frm和.ibd） , MylSAM的表有三个文件（.frm、.MYD、,MYI）。

  有一个是相同的文件：.frm。.frm是MySQL里面表结构定义的文件，不管建表的时候选用任何一个存储引擎都会生成。
  我们主要看一下其他两个文件是怎么实现MySQL不同的存储引擎的索引的。

4.3.1 MyISAM

  在MylSAM里面，另外有两个文件:

• 一个是.MYD文件，D代表Data，是MylSAM的数据文件，存放数据记录，比如我们的user myisam表的所有的表数据。
• 一个是.MYI文件，I代表Index，是MylSAM的索引文件，存放索引，比如我们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。一个索引就会有一棵B+Tree，所有的B+Tree都在这个myi文件里面。

  MylSAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索 引文件MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

  在MylSAM里面，非主键索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。

4.3.2 InnoDB

  在InnoDB的某个索引的叶子节点上，它直接存储了我们的数据。 所以，为什么说在InnoDB中索引即数据，数据即索引，就是这个原因。但是这里会有一个问题，一张InnoDB的表可能有很多个多索引，数据肯定是只有—份的，那数据在哪个索引的叶子节点上呢？

  这就涉及到做聚集索引（聚簇索引）的概念，就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。InnoDB组织数据的方式就是（聚集）索引组织表（clustered index organize table）。如果说一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。（比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引）
  那主键索引之外的索引，会不会也把完整记录在叶子节点放一份呢？并不会，因为这会带来额外的存储空间浪费和计算消耗。他们的叶子节点上没有数据怎么检索完整数据？比如我们在name字段上面建的普通索引。

  InnoDB中，主键索引和辅助索引是有一个主次之分的。刚才我们讲了，如果有主键索引，那么主键索引就是聚集索引。其他的索引统一叫做"二级索引”（secondary index）。二级索引存储的是二级索引的键值，例如在name上建立索引，节点上存的是name的值，qingshan mic tom等等（很明显，它的键值逻辑顺序跟物理行的顺序不一致）。而二级索引的叶子节点存的是这条记录对应的主键的值。
  当我们用name索引查询一条记录，它会在二级索引的叶子节点找到 name=qingshan,拿到主键值，也就是id = 1，然后再到主键索引的叶子节点拿到数据。
  从这个角度来说，因为主键索引比二级索引少扫描了一棵B+Tree （避免了回表）， 它的速度相对会快一些。

  聚合索引选择原则：

1. 如果定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。
2. 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
3. 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

4.4 索引使用原则

4.4.1 列的离散度

  列的离散度公式：count(distinct(column name)): count(1)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。我们不建议大家在离散度低的字段上建立索引。
  如果在B+Tree里面的重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。

4.4.2 联合索引最左匹配

  前面我们说的都是针对单列创建的索引，但有的时候我们的多条件査询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。
  比如我们在user表上面，给name和phone建立了一个联合索引。

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

  联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 （name在左边，phone在右边）。从这张图可以看岀来，name是有序的，phone是无序的。当name相等的时候， phone才是有序的。
  这个时候我们使用where name=‘青山’ and phone = '136xx’去査询数据的时候, B+Tree会优先比较name来确定下一步应该搜索的方向，往左还是往右。如果name 相同的时候再比较phone。但是如果查询条件没有name，就不知道第一步应该查哪个 节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

4.4.3 覆盖索引

  回表: 非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没
有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
  例如：select * from user innodb where name = ‘青山’；

  在二级索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

创建索引：

ALTER TABLE user_innodb DROP INDEX comixd_name_phone;
ALTER TABLE user_innodb add INDEX ' comixd_name_phone' ( name , phone );

这三个查询语句属于覆盖索引:

EXPLAIN SELECT name,phone FROM user_innodb WHERE name='青山' AND phone = '13666666666';
EXPLAIN SELECT name FROM user_innodb WHERE name='青山'AND phone = '13666666666';
EXPLAIN SELECT phone FROM user innodb WHERE name='青山'AND phone = '13666666666';

  如果改成只用where phone = 査询呢？按照我们之前的分析，它是用不到索引的。实际上可以用到覆盖索引！优化器觉得用索引更快，所以还是用到了索引。很明显，因为覆盖索引减少了IO次数，减少了数据的访问量，可以大大地提升查询效率。

4.4.4 索引条件下推（ICP）

  索引条件下推(Index Condition Pushdown) , 5.6以后完善的功能。只适用于二级索引。ICP的目标是减少访问表的完整行的读数量从而减少I/O操作。这里说的下推，其实是意思是把过滤的动作在存储引擎做完，而不需要到Server层过滤。
  举个例子，假设我们在last name和first name上面创建联合索引。现在要査询所有姓wang，并且名字最后一个字是zi的员工，比如王胖子，王瘦子。查询的SQL:

select * from employees where last_name='wang' and first_name LIKE '%zi' ；

  正常情况来说，因为字符是从左往右排序的，当你把％加在前面的时候，是不能基于索引去比较的，所以只有last_name （姓）这个字段能够用于索引比较和过滤。
所以查询过程是这样的：

1. 根据联合索引查出所有姓wang的二级索引数据（3个主键值：6、7、8）。
2. 回表，到主键索引上查询全部符合条件的数据（3条数据）。
3. 把这3条数据返回给Server层，在Server层过滤出名字以zi结尾的员工。

  注意，索引的比较是在存储引擎进行的，数据记录的比较，是在Server层进行的。 而当first_name的条件不能用于索引过滤时，Server层不会把first name的条件传递给存储引擎，所以读取了两条没有必要的记录。这时候，如果满足last_name='wang’的记录有10万条，就会有99999条没有必要读取的记录。
所以，根据first_name字段过滤的动作，能不能在存储引擎层完成呢？这是第二种查询方法：

1. 根据联合索引查出所有姓wang的二级索引数据（3个主键值：6、7、8）。
2. 然后从二级索引中筛选出first_name以zi结尾的索引（1个索引）。
3. 然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server 层。

  很明显，第二种方式到主键索引上査询的数据更少。ICP是默认开启的，也就是说针对于二级索引，只要能够把条件下推给存储引擎，它就会下推，不需要我们干预:

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划，Using index condition:

  把first name LIKE '%zi’下推给存储引擎后，只会从数据表读取所需的1条记录。

4.4.5 创建索引的原则

1. 在用于where判断order排序和join的(on)、group by的字段上创建索引
2. 索引的个数不要过多。
3. 过长的字段，建立前缀索引。
4. 区分度低的字段，例如性别，不要建索引。
5. 频繁更新的值，不要作为主键或者索引。
6. 随机无序的值，不建议作为索引，例如身份证、UUID。
7. 组合索引把散列性高(区分度高)的值放在前面
8. 创建复合索引，而不是修改单列索引

4.4.6 索引失效的场景

1. 索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式计算(+ -*/):

explain SELECT * FROM、t2' where id+1 = 4;

2. 字符串不加引号，出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user innodb add INDEX comidx_name_phone (name,phone);
explain SELECT * FROM user innodb where name = 136; 
explain SELECT * FROM user innodb where name = '136';

3. like条件右模糊。
4. 负向查询：NOT LIKE 不能；!= (<>)和NOT IN在某些情况下可以。

  是否使用索引跟数据库版本、数据量、数据选择度都有关系，最终都是优化器说了算。优化器是基于 cost 开销(Cost Base Optimizer)的，它不是基于规则(Rule-Based Optimizer)，也不是基于语义。

5、事务

5.1 事务的定义

  维基百科的定义：事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由 一个有限的数据库操作序列构成。
  这里面有两个关键点，第一个，所谓的逻辑单位，意味着它是数据库最小的工作单元，是不可以再分的。第二个，它可能包含了一个或者一系列的DML语句，包括insert delete update。(单条 DDL (create drop)和 DCL (grant revoke)也会有事务)

5.2 事务的四大特性（ACID）

  原子性（Atomicity） 也就是我们刚才说的不可再分，因为原子是化学上（参加化学反应）最小的单位。也就意味着我们对数据库的一系列的操作，要么都是成功，要么都是失败，不可能出现部分成功或者部分失败的情况。原子性，在InnoDB里面是通过undo log来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用undo log来实现回滚操作。
  隔离性（Isolation） 我们有了事务的定义以后，在数据库里面会有很多的事务同时去操作我们的同一张表或者同一行数据，必然会产生一些并发或者干扰的操作。 我们对隔离性的定义，就是这些很多个的事务，对表或者行的并发操作，应该是透明的，互相不干扰的。比如两个人给青山转账100，开启两个事务，都拿到了青山账户的余额 1000，然后各自基于1000加100，最后结果是1100，就出现了数据混乱的问题。
  持久性（Durability） 持久性的意思是我们对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为数据库掉电、 宕机、意外重启，又变成原来的状态。这个就是事务的持久性。持久性是通过redo log和double write buffer (双写缓冲)来实现的，我们操作数据的时候，会先写到内存的buffer pool里面，同时记录redo log，如果在刷盘之前出现异常，在重启后就可以读取redo log的内容，写入到磁盘，保证数据的持久性。当然，恢复成功的前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲保证。
  一致性（consistent） 一致性指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库自身提供了一些约束：比如主键必须是唯一的，字段长度符合要求。另外还有用户自定义的完整性。比如说转账的这个场景，A账户余额减少1000, B账户余额只增加了 500,两个操作都成功了，它是满足原子性的定义的，但是它不满足用户自定义的一致性，因为它导致了会计科目的不平衡。还有一种情况，A账户余额为0，如果这个时候转账成功了，A账户的余额会变成=-1000,虽然它也满足原子性，但是我们知道，借记卡的余额是不能够小于0的，所以也违反了一致性。用户自定义的完整性通常要在代码中控制。

5.3 事务的开启与结束

  当执行这样一条更新语句的时候，它有事务吗?

update student set sname ='李大彪111' where id=1;

  实际上，它不仅自动开启了一个事务，而且自动提交了，所以最终写入了磁盘。这个是开启事务的第一种方式，增删改的语句会自动开启事务，当然是一条SQL一个事务。注意每个事务都是有编号的，这个编号是一个整数，有递增的特性。如果要把多条SQL放在一个事务里面，就要手动开启事务。手动开启事务有两种方式： 一种是用 begin；—种是用 start transaction。
  结束也有两种方式：第一种是回滚事务rollback，事务结束；第二种就是提交一个事务，commit，事务结束。
  InnoDB里面有一个autocommit的参数（分为两个级别，session级别和global级别）。

show variables like 'autocommit';

  它的默认值是ON，我们在操作数据的时候，会自动提交事务。如果我们把autocommit设置成false/off，那么数据库的事务就需要我们手动地结束，用rollback或者commit。
  还有一种情况，客户端的连接断开的时候，事务也会结束。

5.4 事务并发带来的问题

5.4.1 脏读

  有两个事务，一个是事务编号2673，—个是事务编号2674。在第一个事务里面，首先通过一个where id = 1的条件査询一条数据，返回name=Ada，age=16的这条数据。然后第二个事务同样去操作id = 1的这行数据，它通过一个update的语句，把这行id = 1的数据的age改成了18，但是没有提交。这个时候，在第一个事务里面，它再次去执行相同的查询操作，发现数据发生了变化，获取到的数据age变成了18。
  这种在一个事务里面，由于其他的时候修改了数据（没有提交）而导致了前后两次读取数据不一致的情况，叫做脏读。

5.4.2 不可重复读

  同样是两个事务，第一个事务通过id=1査询到了一条数据。然后在第二个事务里面执行了一个update操作，并通过commit 提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况。这种事务并发带来的问题， 我们把它叫做不可重复读。

5.4.3 幻读

  在第一个事务里面执行了一个范围查询，这个时候满足条件的数据只有一条。在第二个事务里面，它插入了一行数据，并且提交了。在第一个事务里面再去查询的时候，它发现多了一行数据。这种情况就好像突然冒出来的一个幻影一样，这种情况我们把它叫做幻读。

  不可重复读和幻读最大的区别在于：修改或者删除造成的读不一致叫做不可重复读，插入造成的读不一致叫做幻读。

5.5 事务的隔离级别

5.5.1 SQL92标准事务隔离级别

  无论是脏读、不可重复读还是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。读一致性的问题，必须要由数据库提供一定的事务隔离机制来解决。
  所以，美国国家标准协会(ANSI)制定了一个SQL标准，也就是说建议数据库厂商都按照这个标准，提供一定的事务隔离级别，来解决事务并发的问题。这个SQL标准有很多的版本，大家最熟悉的是SQL92标准。它定义了事务的4种隔离级别：

1. Read Uncommitted （未提交读） 一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做RU，它没有解决任何问题。
2. Read Committed （已提交读） 一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题。
3. Repeatable Read （可重复读） 同一个事务里面多次读取同样的数据结果是一样的，它解决了不可重复读的问题。
4. Serializable （串行化） 在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有（脏读、不可重复读、幻读）问题。

  事务隔离级别设置：

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

5.5.2 InnoDb事务隔离级别

  InnoDB支持的四个隔离级别和SQL92定义的完全一致，隔离级别越高，事务的并发度就越低。唯一的区别就在于，InnoDB在RR的级别就解决了幻读的问题。
  也就是说，不需要使用串行化的隔离级别去解决所有问题，既保证了数据的一致性，又支持较高的并发度。这个就是InnoDB默认使用RR作为事务隔离级别的原因。

5.6 读一致性的解决方案

5.6.1 LBCC

  既然要保证前后两次读取数据一致，那么我读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案叫做基于锁的并发控制Lock Based Concurrency Control (LBCC)。

5.6.2 MVCC

5.6.2.1 MVCC原则

  如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。所以我们还有另一种解决方案，如果要让一个事务前后两次读取的数据保持一致， 那么我们可以在修改数据的之前给它建立一个备份或者叫快照，后面再来读取这个快照就行了。这种方案我们叫做多版本的并发控制Multi Version Concurrency Control （MVCC）。
  MVCC的原则：一个事务只能看到第一次查询之前已经提交的事务的修改和本事务的修改，不能看见本事务第一次查询之后创建的事务（事务ID比我的事务ID大）的修改以及未提交的事务的修改。
  MVCC的效果：我可以査到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。而在我这个事务之后新增的数据，我是查不到的。

5.6.2.2 MVCC实践演示

  首先，InnoDB的事务都是有编号的，而且会不断递增。其次，InnoDB为每行记录都实现了两个隐藏字段：

• DB_TRX_ID：事务ID,数据是在哪个事务插入或者修改为新数据的，就记录为当前事务ID。
• DB_ROLL_PTR：回滚指针（示例中把它理解为删除版本号，实际并不准确，正确理解应该是指向前一个版本的指针，不过不影响我们理解MVCC）。

  1）第一个事务，初始化数据（检查初始数据）：

  此时的数据，创建版本是当前事务ID （假设事务编号是1），删除版本为空：

  2）第二个事务，执行第1次查询，读取到两条原始数据，这个时候事务ID是2：

  3）第三个事务，插入数据：

  此时的数据，多了一条tom，它的创建版本号是当前事务编号，3：

  4）第二个事务，执行第2次查询:

  MVCC的査找规则：只能査找创建时间小于等于当前事务ID的数据，和删除时间大于当前事务ID的行（或未删除）。也就是不能查到在我的事务开始之后插入的数据，tom的创建ID大于2，所以还是只能査到两条数据。
  5）第四个事务，删除数据，删除了 id=2huihui这条记录：

  此时的数据，jack的删除版本被记录为当前事务ID, 4，其他数据不变:

  6）在第二个事务中，执行第3次査询：

  MVCC查找规则：只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事 务ID的行（或未删除）。也就是，在我事务开始之后删除的数据，所以huihui依然可以查出来。所以还是这两条数据。
  7）第五个事务，执行更新操作，这个事务事务ID是5:

  此时的数据，更新数据的时候，旧数据的删除版本被记录为当前事务ID 5 (undo)，产生了一条新数据，创建ID为当前事务ID5：

  8）第二个事务，执行第4次查询：

  MVCC查找规则：只能查找创建时间小于等于当前事务ID的数据，和删除时间大于当前事 务ID的行(或未删除)。因为更新后的数据penyuyan创建版本大于2，代表是在事务之后增加的，查不出来。而旧数据qingshan的删除版本大于2，代表是在事务之后删除的，可以查出来。

5.6.2.3 MVCC原理

  在InnoDB中，一条数据的旧版本，存放在undo log。因为修改了多次, 这些undo log会形成一个链条，叫做undo log链。所以前面我们说的DB_ROLL_PTR，它其实就是指向undo log链的指针。
  为了判断各个事务的可见性情况，我们必须要有一个数据结构，把本事务ID、活跃事务ID、当前系统最大事务ID存起来。这个数据结构就叫Read View （可见性视图），每个事务都维护一个自己的Read View。

• m_ids 表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
• min_trx_id 表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
• max_trx_id 表示生成ReadView时系统中应该分配给下一个事务的id值。
• creator_trx_id 表示生成该ReadView的事务的事务id。

  有了这个数据结构以后，事务判断可见性的规则是这样的：

1. 数据版本的trx_id = creator_trx_id，本事务修改，可以访问。
2. 数据版本的trx_id < min_trx_id （未提交事务的最小ID），说明这个版本在生成ReadView已经提交，可以访问。
3. 数据版本的trx_id > max_trx_id （下一个事务ID），这个版本是生成ReadView 之后才开启的事务建立的，不能访问。
4. 数据版本的trx_id 在min_trx_id 和max_trx_id之间，看看是否在m_ids中。 如果在，不可以。如果不在，可以。
5. 如果当前版本不可见，就找undo log链中的下一个版本。（上述示例中对DB_TRX_PTR的描述不够准确，真正的DB_TRX_PTR是由新版本指向旧版本）

6、InnoDB锁机制

6.1 锁的类型

6.1.1 共享锁

  共享锁是一种读锁，它允许其他事务添加共享锁或读取数据，不允许其他事务修改数据。注意不要在加上了读锁以后去写数据，不然的话可能会出现死锁的情况。
  我们可以用select … lock in share mode 的方式手工加上一把读锁。

6.1.2 排它锁

  排它锁又叫写锁，它不允许其他事务对数据加锁或修改数据。
  排它锁的加锁方式有两种，第一种是自动加排他锁，我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。第二种是手工加锁，我们用一个FOR UPDATE给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

6.1.3 意向锁

  意向锁是由数据库自己维护的一种表锁。当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。
  意向锁是为表锁而设计的，如果没有意向锁，当我们要加表锁时需要逐行判断里面的数据是否被加锁，只有所有数据都没加锁才可以加表锁，不仅效率低下，还存在原子性问题。有了意向锁之后，如果要加表锁，只需要判断这张表上有没有被加意向锁即可，大大提升了加表锁的效率。

6.2 锁的范围

6.2.1 记录锁

  记录锁指的是锁住某条记录。当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，使用的就是记录锁。

6.2.2 间隙锁

  间隙锁指的是锁住某个左开右开的区间。当我们査询的记录不存在，没有命中任何一个record，无论是用等值査询还是范围查询的时候，它使用的都是间隙锁。间隙锁主要是阻塞插入insert，相同的间隙锁之间不冲突。

6.2.3 临键锁

  临键锁指的是一个间隙锁加上最右边的记录，形成一个左开右闭的区间。当我们使用了范围査询，不仅仅命中了 Record记录，还包含了 Gap 间隙，在这种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。

  临键锁可以解决幻读的问题。

6.3 锁与事务隔离级别的实现

6.3.1 Read Uncommitted

  RU隔离级别不加锁。

6.3.2 Read Committed

  RC隔离级别下，普通的select都是快照读，使用MVCC实现。加锁的select都使用记录锁，因为没有间隙锁。（除了两种特殊情况——外键约束检査(foreign-key constraint checking)以及重复键检査(duplicate-key checking)时会使用间隙锁封锁区间）所以RC会出现幻读问题。

6.3.3 Repeatable Read

  RR隔离级别下，普通的select使用快照读(snapshot read)，底层使用MVCC来实现。加锁的 select(select … in share mode / select … for update)以及更新操作 update, delete等语句使用记录锁、或者间隙锁、 临键锁实现。

6.3.4 Serializable

  Serializable下所有的select语句都会被隐式的转化为select… in share mode，会和 updates、delete 互斥。

6.4 死锁

6.4.1 锁的释放与阻塞

  锁在事务结束或者客户端连接断开的时候释放。
  如果一个事务一直未释放锁，其他事务会被阻塞多久？会不会永远等待下去？如果是，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。
  MySQL有一个参数来控制获取锁的等待时间，默认是50秒：

show VARIABLES like 'innodb_lock_wait_timeout';

6.4.2 死锁的发生和检测

案例1

案例2

  我们看到：在第一个事务中，检测到了死锁，马上退岀了，第二个事务获得了锁， 不需要等待50秒。这个是MySQL的死锁检测机制，检测到死锁后会通过回滚其中一个事务来让另一个事务获得锁继续执行流程。
  死锁的产生条件：

1. 互斥：同一时刻只能有一个事务持有这把锁。
2. 不可剥夺：其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺。
3. 请求与保持：一个事务在请求锁资源时，对当前持有的资源不进行释放。
4. 循环等待：多个事务形成等待环路。

6.4.3 查看锁信息（日志）

  SHOW STATUS命令中，包括了一些行锁的信息：

show status like 'innodb_row_lock_%';

• lnnodb_row_lock_current_waits 当前正在等待锁定的数量。
• lnnodb_row_lock_time 从系统启动到现在锁定的总时间长度，单位ms。
• lnnodb_row_lock_time_avg 每次等待所花平均时间。
• lnnodb_row_lock_time_max 从系统启动到现在等待最长的一次所花的时间。
• lnnodb_row_lock_waits 从系统启动到现在总共等待的次数。

  InnoDB还提供了三张表来分析事务与锁的情况：

select * from information_schema.INNODB_TRX;—当前运行的所有事务，还有具体的语句

select * from information _schema.INNODB_LOCKS; --当前出现的锁

select * from information_schema.INNODB_LOCK_WAITS;--锁等待的对应关系

6.4.4 死锁的避免

1. 在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）。
2. 批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）。
3. 申请足够级别的锁，如果要操作数据，就申请排它锁。
4. 尽量使用索引访问数据，避免没有where条件的操作，避免锁表。
5. 如果可以，大事务化成小事务。
6. 使用等值査询而不是范围査询査询数据，命中记录，避免间隙锁对并发的影响。

7、MySQL性能优化

7.1 业务优化

  限流、降级、削峰。

7.2 架构优化

  引入缓存；主从、集群部署；读写分离；分库分表。
  详见JAVA知识体系之分布式篇（一）——漫谈分布式架构

7.3 数据库优化

7.3.1 数据库连接优化

  第一个环节是客户端连接到服务端，连接这一块有可能会出现服务端连接数不够导致应用程序获取不到连接。

解决方案

  从服务端来说，我们可以增加服务端的可用连接数:

show variables like 'max_connections';--修改最大连接数，当有多个应用连接的时候

  或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小：

show global variables like 'wait_timeout'; --及时释放不活动的连接

  从客户端来说，可以减少从服务端获取的连接数。如果我们想要不是每一次执行SQL都创建一个新的连接，可以引入连接池，实现连接的重用。常见的数据库连接池有老牌的DBCP和C3P0、阿里的Druid、Hikari (Spring Boot 2.x版本默认的连接池)。
  连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。
  在Hikari的github文档中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式。它的建议是机器核数乘以2加1。也就是说，4核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。
  我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括CPU、内存、磁盘、网络。在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。

7.3.2 存储引擎和表结构优化

7.3.2.1 存储引擎的选择

  为不同的业务表选择不同的存储引擎，例如:查询插入操作多的业务表，用MylSAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。

7.3.2.2 分表与分区

  交易历史表：在年底为下一年度建立12个分区，每个月一个分区。
  渠道交易表：分成：当日表、当月表、历史表，历史表再做分区。

7.3.2.3 字段定义

• 整数类型：INT有6种类型，不同的类型的最大存储范围是不一样的，占用的存储空间也是不一样的。
• 字符类型：变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长度。比如：联系地址。固定长度的用char，不要用varcharo比如：行政区划编码。
• 非空：非空字段尽量定义成NOT NULL，提供默认值，或者使用特殊值、空串代替null。NULL类型的存储、优化、使用都会存在问题。
• 不要用外键、触发器、视图：降低了可读性；影响数据库性能，应该把计算的事情交给程序，数据库专心做存储；数据的完整性应该在程序中检查。
• 大文件存储：不要用数据库存储图片（比如base64编码）或者大文件。把文件放在NAS上，数据库只需要存储URI （相对路径），在应用中配置NAS服务器地址。
• 表拆分：将不常用的字段拆分出去，避免列数过多和数据量过大。
• 字段冗余：某些场景下使用一定的冗余来减少联表查询。

7.3.3 慢查询分析

7.3.3.1 打开慢日志开关

  因为开启慢查询日志是有代价的（跟binlog、optimizer-trace —样），所以它默认是关闭的：

show variables like 'slow_query%';

  除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variables like 'long_query%';

7.3.3.2 慢日志分析

  MySQL提供了 mysqldumpslow的工具，在MySQL的bin目录下：

mysqldumpslow --help

  例如：查询用时最多的10条慢SQL：

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log

• Count 代表这个SQL执行了多少次。
• Time 代表执行的时间，括号里面是累计时间。
• Lock 表示锁定的时间，括号是累计。
• Rows 表示返回的记录数，括号是累计。

  show processlist 这是很重要的一个命令，用于显示用户运行线程。可以根据id号kill线程：

show full processlist;

  也可以查表，效果一样：

select * from infbrmation schema.processlist;

  show status用于查看MySQL服务器运行状态（重启后会清空）：

SHOW GLOBAL STATUS;

  show engine用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息。例如:

show engine innodb status;

  开启InnoDB标准监控和锁监控：

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

  很多开源的MySQL监控工具，其实他们的原理也都是读取的服务器、操作系统、MySQL服务的状态和变量。

7.3.4 Explain执行计划

  我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

DROP TABLE IF EXISTS course;
CREATE TABLE 'course' ( 
'cid' int(3) DEFAULT NULL, 
'cname' varchar(20) DEFAULT NULL, 
'tid' int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher;
CREATE TABLE 'teacher' ( 
'tid' int(3) DEFAULT NULL, 
'tname' varchar(20) DEFAULT NULL, 
'tcid' int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS teacher_contact;
CREATE TABLE 'teacher_contact' (
'tcid' int(3) DEFAULT NULL, 
'phone' varchar(200) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO course VALUES ('1', 'mysql'，'1');
INSERT INTO course VALUES ('2', 'jvm', '1');
INSERT INTO course VALUES ('3', 'juc', '1');
INSERT INTO course VALUES ('4', 'spring', '1');

INSERT INTO teacher VALUES ('1', 'qingshan', '1');
INSERT INTO teacher VALUES ('2', 'huihui', '2');
INSERT INTO teacher VALUES ('3', 'mic', '3');

INSERT INTO teacher contact VALUES ('1', '13688888888');
INSERT INTO teacher_contact VALUES ('2', '18166669999');
INSERT INTO teacher contact VALUES ('3', '17722225555');

  explain的结果有很多的字段，我们详细地分析一下。

7.3.4.1 id

  id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号。

  id值不同的时候，先査询id值大的（先大后小）。

-查询mysql课程的老师手机号
EXPLAIN SELECT tc.phone 
FROM teacher_contact tc
WHERE tcid =(
SELECT tcid
FROM teacher t
WHERE t.tid =(
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'
)
);

  查询顺序：course c——teacher t——teacher_contact tc。

  先查课程表，再査老师表，最后查老师联系方式表。子査询只能以这种方式进行, 只有拿到内层的结果之后才能进行外层的查询。

  id值相同时，表的查询顺序是从上往下顺序执行。

-查询课程ID为2,或者联系表ID为3的老师
EXPLAIN
SELECT t.tname, c.cname, tc.phone
FROM teacher t, course c, teacher_contact tc
WHERE t.tid = c.tid
AND t.tcid = tc.tcid
AND (c.cid = 2
OR tc.tcid = 3);

  例如这次查询的id都是1 （说明子査询被优化器转换成了连接査询），査询的顺序是teacher t （3条） course c （4条） teacher contact tc （3 条）。
  在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表。应该先查小表（得到结果少的表）因为它的中间结果最少（小标驱动大表的思想）。

7.3.4.2 select type

  下面列举了一些常见的查询类型（这里并没有列举全部，其它还有：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

SIMPLE

  简单査询，不包含子查询，不包含关联査询union。

EXPLAIN SELECT * FROM teacher;

PRIMARY

  子查询SQL语句中的主查询。

SUBQUERY

  子查询中所有的内层查询。

--查询mysql课程的老师手机号
EXPLAIN SELECT tc.phone
FROM teacher_contact tc
WHERE tcid =(
SELECT tcid
FROM teacher t
WHERE t.tid =(
SELECT c.tid
FROM course c
WHERE c.cname = 'mysql'));

DERIVED

  衍生查询，表示在得到最终查询结果之前会用到临时表。
  对于关联査询，先执行右边的table (UNION),再执行左边的table,类型是DERIVED。

UNION

  用到了 UNION查询。

UNION RESULT

  主要是显示哪些表之间存在UNION査询。＜union2,3＞代表id=2和id=3的査询存在UNION。

--查询ID为1或2的老师教授的课程
EXPLAIN SELECT cr.cname from (
select * from course where tid = 1
union
select * from course where tid = 2
) cr;

7.3.4.3 type

  所有的连接类型中：system > const > eq_ref > ref > range > index > all。
  这里并没有列举全部(其他：fulltext、ref_or_null、index_merger、unique_subquery、index_subquery) 。
  以上访问类型除了 all,都能用到索引。

const

  主键索引或者唯一索引，只能査到一条数据的SQL。

DROP TABLE IF EXISTS single_data; 
CREATE TABLE single_data(
id int(3) PRIMARY KEY, 
content varchar(20)
);
insert into single_data values(1,'a');
EXPLAIN SELECT * FROM single_data a where id = 1;

system

  system是const的一种特例，只有一行满足条件，对于MylSAM、Memory的表，只查询到一条记录，也是system。比如系统库的这张表（8.0的版本中系统表全部变成 InnoDB存储引擎了）

EXPLAIN SELECT * FROM mysql.proxies_priv;

eq_ref

  通常出现在多表的join査询，被驱动表通过唯一性索引（UNIQUE或PRIMARY KEY）进行访问，此时被驱动表的访问方式就是eq_ref。eq_ref是除const之外最好的访问类型。
  先删除teacher表中多余的数据，teacher contact有3条数据，teacher表有3条数据。
  teacher_contact表的tcid （第一个字段）创建主键索引。

ALTER TABLE teacher_contact DROP PRIMARY KEY; 
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

  执行以下SQL语句:

explain select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

  此时的执行计划（先大后小，从上往下，tc是被驱动表。tc表是eq_ref）: 被驱动表用主键索引进行访问。

ref

  查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
  为teacher表的tcid （第三个字段）创建普通索引。

ALTER TABLE teacher DROP INDEX idx_tcid; 
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);

  使用tcid上的普通索引査询:

explain SELECT * FROM teacher where tcid = 3;

range

  索引范围扫描。如果where后面是between and或〈或 > 或 > =或〈二或in这些，type类型
就为range。

ALTER TABLE teacher DROP INDEX idx_tid; 
ALTER TABLE teacher ADD INDEX idx_tid(tid);

  执行范围查询（字段上有普通索引）:

EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
--或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;

  IN查询也是range （字段有主键索引）：

EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);

index

  Full Index Scan，查询全部索引中的数据（例如覆盖索引，比不走索引要快）。

EXPLAIN SELECT tid FROM teacher;

all

  Full Table Scan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。

NULL

  不用访问表或者索引就能得到结果，例如：

EXPLAIN select 1 from dual where 1=1;

  一般来说，需要保证查询的type至少达到range级别，最好能达到ref。ALL （全表扫描）和index （查询全部索引）都是需要优化的。

7.3.4.4 possible_key和key

  可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。 possible_key可以有一个或者多个，可能用到索引不代表一定用到索引。 反过来，possible_key为空，key也有可能有值，示例如下：
  表上创建联合索引：

ALTER TABLE user innodb DROP INDEX comidx_namejDhone;
ALTER TABLE user innodb add INDEX comidx_name_phone (name.phone);

  执行计划（改成select name也能用到索引）:

explain select phone from user innodb where phone='126';

7.3.4.5 key_len

  索引的长度（使用的字节数）。跟索引字段的类型、长度有关。表上有联合索引 ： KEY ‘comidx_name_phone’ (‘name’,‘phone’)

explain select * from user_innodb where name ='青山';

key_len =1023,为什么不是 255 + 11=266 呢?
这里的索引只用到了 name字段，定义长度255。
utf8mb4编码1个字符4个字节。所以是255*4二1020。
使用变长字段varchar需要额外增加2个字节，允许NULL需要额外增加1个字节。
所以一共是1023。

7.3.4.6 rows

  MySQL认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

7.3.4.7 filtered

  这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例，它是一个百分比。如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。

7.3.4.8 ref

  使用哪个列或者常数和索引一起从表中筛选数据。

7.3.4.9 Extra

  执行计划给出的额外的信息说明。

using index

  用到了覆盖索引，不需要回表。

EXPLAIN SELECT tid FROM teacher ;

using where

  使用了 where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤（跟是否使用索引没有关系）。

EXPLAIN select * from user_innodb where phone='13866667777';

Using index condition （索引条件下推）
  使用到索引条件下推。

using filesort

  不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。

ALTER TABLE user innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

EXPLAIN select * from user_innodb where name ='青山'order by id;

using temporary

  用到了临时表。例如：

• distinct非索引列（确定tid字段是否有索引）

EXPLAIN select DISTINCT(tid) from teacher t;

• group by非索引列

EXPLAIN select tname from teacher group by tname;

• 使用join的时候，group任意列（t表的结果）

EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;