Mysql 面试总结看这一篇就够了

阅读量：

事务特性ACID

原子性A：要么成功，要么失败，不可分割。
一致性C：事务执行前后，数据库处于一致性状态，事务成功变化正确。事务失败返回原始阶段。
隔离性I：并发下，不同事务操作相同数据，并发事务所做的修改隔离，要么是另一个事务修改前没要么是另一个事务修改后；不存在中间状态。
持久性：事务结束后，对数据库的操作必须要永久保存下来（保存在磁盘中）。

事务的隔离级别

ISOLATION_DEFUALT ：后端数据库默认隔离级别。
ISOLATION_READ_UNCOMMITED ：最低级别，允许读尚未提交的数据变更，可能会出现脏读、幻读、不可重复读。
ISOLATION_READ_COMMITED ：RC，允许读取并发事务已提交的数据，可以阻止脏读，但可能会出现幻读、不可重复读。
ISOLATION_REPEATABLE_READ ：RR，同一字段，多次读取结果都是一致的，除非数据本身被修改，可阻止脏读、不可重复读，但仍有幻读。
ISOLATION_SERIALIZABLE ：最高隔离级别，完全服从ACID，没有脏读、幻读、不可重复读，但速度慢，完全锁定事务。

脏读、幻读、不可重复读

脏读： 一个事务读取了被另一个事务改写但尚未提交的数据，如果数据改变后被回滚，第一个事务读取的数据就会无效。
幻读： 当事务T1读取几行数据后，另外一个并发事务T2插入了一些记录，幻读就发生了，第一个事务T1发现了一些原来没有的额外数据记录（新增、或删除）。
不可重复读： 不可重复读发生在一个事务执行多次查询，但每次查询的结果都不同，通常由于另外一个事务在中途做了更新。

MySQL事务的实现

MySQL的事务的四个特性（ACID），是通过InnoDB日志和锁来保证的。

事务的隔离性是通过数据库锁的机制实现 。
事务的持久性是通过Redo Log来实现 。
事务的原子性和一致性是通过Undo Log实现的 。

实现过程：

在操作任务数据之前，首先将数据备份到Undo Log中，然后再进行数据的修改操作；
出现错误时执行Roll Back，系统可以利用Undo Log恢复到事务开始之前的状态。
Redo Log是记录新数据的备份，事务提交之前，只将Redo Log持久化即可。
系统崩溃时，数据库未持久化，但Redo Log已经持久化，系统可以根据Redo Log将数据恢复并提交。

页和脏页

页：InnoDB是B+树结构，树的每个节点是一个页，在MySQL中，页的大小是16kb，ORA中是8kb。

脏页：内存数据跟磁盘数据页不一致的时候称为这个内存页为“脏页” ，一致的称为“干净页”。

复制代码

脏页同步

脏页同步实际时机

1、redo log写满时 ，系统就会停止所有的更新操作，将更新的这部分日志对应的脏页同步到磁盘中，此时所有的更新全部停止，此时写的性能为0，必须等待刷盘一部分脏页后才能更新，这就导致了SQL执行慢，应避免此类情况 ；

2、系统内存不足时 ，需要将一部分数据页淘汰掉，如果淘汰的是脏页，则需要先将脏页同步到磁盘，空出来的给其他数据页使用。当淘汰的脏页过多时。会导致查询的响应时间边长 。

3、MySQL认为系统空闲时 ，则会同步一些数据到磁盘。无性能问题。

4、MySQL正常关闭时 ，会把内存脏页都同步到磁盘中。无性能问题。

刷脏页策略策略

innodb_io_capacity：redo log中的剩余空间。
innodb_max_dirty_pages_pct脏页比例上限，默认值是75% 通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的。

redo log（重做日志）

InnoDB存储引擎层的日志，redo log是用来实现事务的持久性，即当事务在提交时，必须先将该事务的所有操作日志写到磁盘上的 redo log file进行持久化，这也就是我们常说的 Write Ahead Log 策略（先日志后写数据）。有了redo log，在数据库发生宕机时，即使内存中的数据还没来得及持久化到磁盘上，我们也可以通过redo log完成数据的恢复，这样就避免了数据的丢失。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中。可以根据redo log日志进行恢复，也就达到了crash-safe。

即WAL即Write Ahead logging技术，是先写日志，再写磁盘。

redo log是循环写的，有write pos 和checkpoint 两个指针，当write pos追上checkpoint时，没有空间记录redo log，checkpoint就向前推进将脏页刷入磁盘。checkpoint之前表示擦除完了的，即可以进行写的，擦除之前会更新到磁盘中，write pos是指写的位置，当write pos和checkpoint相遇的时候表明redo log已经满了，这个时候数据库停止进行数据库更新语句的执行，转而进行redo log日志同步到磁盘中。

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

bin log（归档日志）

数据库的（和引擎无关）bin log记录了数据库系统所有的更新操作，主要是用来实现数据恢复和主从复制的。一方面，主从配置的MySQL集群可以利用bin log将主库中的更新操作传递到从库中，以此来实现主从数据的一致性；另一方面，数据库还可以利用bin log来进行数据的恢复。没有crash-safe能力。

作用：用于复制。在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

binlog是Server层自带 的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。binlog日志文件的格式：statement，row，mixed。

statement格式 的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确
row格式 的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
mixed格式 的binlog是前面两者的混合模式

业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

redo log和binlog的区别

redo log和bin log的产生方式不同。redo log是在物理存储引擎层产生 ，而bin log是在MySQL数据库的Server层产生的 ，并且bin log不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生bin log。
redo log和binlog的记录形式不同。MySQL Server层产生的bin log记录的是一种逻辑日志，即通过SQL语句的方式来记录数据库的修改；而InnoDB层产生的redo log是一种物理格式日志，其记录的是对于磁盘中每一个数据页的修改。
redo log和bin log记录的时间点不同。bin log只是在事务提交完成后进行一次写入 ，而redo log则是在事务进行中不断地被写入 ，redo log并不是随着事务提交的顺序进行写入的，这也就是说在redo log 中针对一个事务会有多个不连续的记录日志。
redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

注：数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer。

bin log和redo log的一致性问题

在MySQL内部，在事务提交时利用两阶段提交（内部XA的两阶段提交）解决了上面提到的bin log和redo log的一致性问题。

（redo log记录事务Prepare，bin log写入并持久化、redo log增加commit 标签）

（先写redo log再写bin log）

第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。
第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写bin log（先调用write()将bin log内存日志数据写入文件系统缓存，再调用fsync()将bin log文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。

在第一阶段并没有记录完整的redo log（不包含事务的commit标签） 。

在第二阶段记录完binlog后再写入redo log的commit 标签。

以第二阶段中bin log的写入与否作为事务是否成功提交的标志。

崩溃恢复过程

如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。

如果数据库在记录此事务的binlog之后发生crash。此时，即使是redo log中还没有记录此事务的commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog写入成功就认为事务成功提交了）。它会扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit 标签）的xid和bin log中提取的xid做比较，如果在binlog中存在，则提交该事务，否则回滚该事务。这也就是说，bin log中记录的事务，在恢复时都会被认为是已提交事务，会在redo log中重新写入commit标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在binlog中已经有了此事务的记录，所有从库也会有此事务的数据修改。

Checkpoint机制

InnoDB引擎通过LSN(Log Sequence Number)来标记版本，LSN是日志空间中每条日志的结束点，用字节偏移量来表示。每个page有LSN，redo log也有LSN，Checkpoint也有LSN。

Checkpoint机制每次刷新多少页，从哪里取脏页，什么时间触发刷新？这些都是很复杂的。有两种Checkpoint，分别为：

Sharp Checkpoint
Fuzzy Checkpoint

Sharp Checkpoint发生在关闭数据库时，将所有脏页刷回磁盘。在运行时使用Fuzzy Checkpoint进行部分脏页的刷新。部分脏页刷新有以下几种：

Master Thread Checkpoint ：Master Thread以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，不会阻塞查询线程。
FLUSH_LRU_LIST Checkpoint ：InnoDB要保证LRU列表中有100左右空闲页可使用。在InnoDB1.1.X版本前，要检查LRU中是否有足够的页用于用户查询操作线程，如果没有，会将LRU列表尾端的页淘汰，如果被淘汰的页中有脏页，会强制执行Checkpoint刷回脏页数据到磁盘，显然这会阻塞用户查询线程。从InnoDB1.2.X版本开始，这个检查放到单独的Page Cleaner Thread中进行，并且用户可以通过innodb_lru_scan_depth控制LRU列表中可用页的数量，默认值为1024。
Async/Sync Flush Checkpoint ：是指重做日志文件不可用时，需要强制将脏页列表中的一些页刷新回磁盘。这可以保证重做日志文件可循环使用。在InnoDB1.2.X版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，Sync Flush Checkpoint会阻塞所有查询线程。InnoDB1.2.X之后放到单独的Page Cleaner Thread。
Dirty Page too much Checkpoint ：脏页数量太多时，InnoDB引擎会强制进行Checkpoint。目的还是为了保证缓冲池中有足够可用的空闲页。

例子：更新语句的执行顺序

示例：update T set c=c+1 where ID=2;

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

InnoDB刷盘策略

MySQL的innodb_flush_method 参数控制着innodb数据文件及redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。
O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。

性能比较：

O_DSYNC对CPU的压力最大，datasync次之，O_DIRECT最小；整体SQL语句处理性能和响应时间看，O_DSYNC较差；O_DIRECT在SQL吞吐能力上较好（仅次于datasync模式），但响应时间却是最长的。

默认datasync模式，整体表现较好，因为充分利用了操作系统buffer和innodb_buffer_pool的处理性能，但带来的负面效果是free内存降低过快，最后导致页交换频繁，磁盘IO压力大，这会严重影响大并发量数据写入的稳定性。

注：redo log并没有打开O_DIRECT选项，所以redo log buffer只是先刷入redo log file，此时刷入的数据并没有落到磁盘上，而是放在文件系统的缓存中。之后为了确保redo log写入磁盘，就通过fsync操作将数据写入磁盘。

innodb_flush_log_at_trx_commit参数 ：

redo log日志持久化：每秒刷盘、事务提交强制刷盘、事务提交由系统每秒刷盘

0 ：由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
1 ：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
2 ：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

sync_binlog参数：

bin log日志持久化：系统控制、每次事务提交、日志组数量达到n

0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
1 ：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
n ：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

MySQL的索引有了解吗？（重点掌握）

答：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表 中的特定信息，就像一本书的目录一样，可以加快查询速度。InnoDB 存储引擎的索引模型底层实现数据结构为B+树 ，所有数据都是存储在 B+ 树中的。

关键字ki>ki-1，关键字个数永远比孩子个数少一个，某节点最左边关键字的值大于左子树关键字的值，某节点最右边孩子的关键字的值大于该节点任意值，其余节点关键字的值大小，在离该孩子借点指针最进的两个关键字之间，如图中p2->910在8和12之间

解析：

无论b还是b+树其目的都是然每个索引块尽可能存储多的信息。

b+树的磁盘读写代价更低 ：b+树的内部并没有指向关键字具体信息的指针，也就是不存放数据只存放索引信息，因此其内部节点先对b树更小，如果把所有同一内部节点的关键字存放在同一盘块中盘块所能容纳的关键字数量也就越多，一次行读入内存的数据就越多相应的就降低了io读写的次数

b+树查询效率更稳定 ：由于内部节点并不是最终指向文件内容的节点，而只是叶子节点关键字的索引，所有关键字查询必须从根节点到叶子节点。

b+树更有利于数据库的扫描 ：遍历叶子节点

为什么底层数据结构使用B+树，而不是B树？，hash索引？”

B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。
hash索引无法用于排序，无法用于组合索引，不能避免表扫描

聚簇索引和非聚簇索引？”

聚簇索引也称为主键索引 ，其索引树的叶子节点中存的是整行数据 ，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。

非聚簇索引（普通索引 ）的叶子节点内容是主键的值 。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

案例解析：

为了让大家更好的理解主键索引和普通索引的区别，我们给出一个具体的案例如下：

我们先来创建一张表User，主键为id，并且拥有字段uid和字段name，uid字段上有索引，建表语句如下所示：

1 2 3 4 5	`create table User(` id ``int `primary key,` uid ``int not ``null``, name varchar(``16``), `index (uid))engine=InnoDB;`

接着我们插入如下几条数据：

1	insert into User values(``1``,``21``,``'zhangsan'``),(``2``,``22``,lisi),(``3``,``23``,``'wangwu'``),(``5``,``25``,``'ywq'``),(``6``,``26``,``'dym'``);

我们知道，主键上自动创建了主键索引，并且我们手动在uid字段上创建的普通索引。接下来，我们一起看下主键索引树和普通索引树的形状吧~
图片说明

由上图可以看出，左边主键索引树的叶子节点存储的是完整的记录，而普通索引树上存储的是其对应的主键的值。那么主键索引和普通索引在查询方面的差距也就显而易见了吧~

MySQL回表：

如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索主键索引树。
如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为回表。

sql优化

慢查询数量：

根据slow_log_file 找到慢日志的位置打开文件可以产看sq执行时间定位到sql

这是explain结果的各个字段，分别解释下含义：

1. id

SQL查询中的序列号。

id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

2. select_type

查询的类型，可以是下表的任何一种类型：

select_type	类型说明
SIMPLE	简单SELECT(不使用UNION或子查询)
PRIMARY	最外层的SELECT
UNION	UNION中第二个或之后的SELECT语句
DEPENDENT UNION	UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表(FROM子句中的子查询)
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

DEPENDENT 意味着使用了关联子查询。

3. table

查询的表名。不一定是实际存在的表名。
可以为如下的值：

<unionM,N>: 引用id为M和N UNION后的结果。
: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。
: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

4. type（重要）

这是最重要的字段之一 ，显示查询使用了何种类型。从最好到最差的连接类型依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

1、system

表中只有一行数据或者是空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

2、const

最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

3、eq_ref

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

eq_ref可用于使用'='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。
eq_ref只能找到一行，而ref能找到多行。

4、ref

对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。

ref可用于使用'='或'<=>'操作符作比较的索引列。

5、 fulltext

使用全文索引的时候是这个类型。要注意，全文索引的优先级很高 ，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

6、ref_or_null

跟ref类型类似，只是增加了null值的比较。实际用的不多。

复制代码

 eg.

    
 SELECT * FROM ref_table
    
 WHERE key_column=expr OR key_column IS NULL;

7、index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range

8、unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：
value IN (SELECT primary_key FROM single_table WHERE some_expr)

9、index_subquery

该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。

10、range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

复制代码

 SELECT * FROM tbl_name

    
   WHERE key_column BETWEEN 10 and 20;
    
  
    
 SELECT * FROM tbl_name
    
   WHERE key_column IN (10,20,30);

11、index

索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：
一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。

复制代码

 # 此表见有一个name列索引。

    
 # 因为查询的列name上建有索引，所以如果这样type走的是index
    
 mysql> explain select name from testa;
    
 +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

    
 +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    
|1|SIMPLE|testa|index|NULL|idx_name|33|NULL|2|Using index|

    
 +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    
 1 row in set
    
  
    
 # 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走ALL扫描，如下：
    
 mysql> explain select cusno from testa;
    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
|1|SIMPLE|testa|ALL|NULL|NULL|NULL|NULL|2|NULL|

    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
 1 row in set
    
  
    
 # 包含有未见索引的列
    
 mysql> explain select * from testa;
    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|

    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
|1|SIMPLE|testa|ALL|NULL|NULL|NULL|NULL|2|NULL|

    
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    
 1 row in set

12、all

全表扫描，性能最差。

5. partitions

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

6. possible_keys

查询可能使用到的索引都会在这里列出来

7. key

查询真正使用到的索引。
select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

8. key_len

查询用到的索引长度（字节数）。
如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

key_len只计算where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

9. ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

10. rows（重要）

rows 也是一个重要的字段 。这是mysql估算的需要扫描的行数（不是精确值）。
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

11. filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

forec_index（）强制走索引

覆盖索引：

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

索引的最左前缀原则：

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

成因？

索引下推：

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

哪些列上适合创建索引？创建索引有哪些开销？”

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并 ），对性能造成影响。

自增主键：

为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序。

MySQL存储引擎InnoDB和MyISAM区别（重点掌握）？

答： MySQL中最常见的存储引擎有InnoDB和MyISAM，它们的主要区别如下：

MyISAM不支持事务；InnoDB是事务类型的存储引擎。
MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
MyISAM引擎不支持外键；InnoDB支持外键。
对于count(*)查询来说MyISAM更有优势，因为其保存了行数。
InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。（5.7之后支持）

总结：

最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。 InnoDB表支持事务、支持行级锁、支持外键。

解析：

对MySQL的存储引擎的考察也是一个几乎必考的知识点，为了让大家对存储引擎有一个更好的了解，我们先来看下MySQL的基本逻辑架构图，从整体上了解下MySQL包括哪些逻辑架构。
图片说明

由MySQL的逻辑架构图我们可以看出，逻辑架构包括Server层和存储引擎层 。其中Server层包括连接器，分析器，优化器以及执行器；存储引擎层包括多种支持的存储引擎。各个逻辑部件的作用如下：

连接器： 验证客户端权限，建立和断开MySQL连接
分析器： 进行SQL语句的语法分析
优化器： 选择索引，生成具体的SQL语句执行计划
执行器： 操作存储引擎，执行SQL，返回执行结果
存储引擎层： 各个不同的存储引擎都提供了一些读写接口来操作数据库

好了，我们接着说存储引擎的知识点，在MySQL5.5.5版本之后，InnoDB已经成为了其默认的存储引擎 ，也是大部分公司的不二选择，毕竟谁家公司会不要求数据库支持事务呢？谁家公司又可以忍受表级锁导致的读写冲突呢？

设计一个关系型数据库

MySQL中where、group by、having关键字？

答：这三个MySQL关键字作用可以总结如下：

where子句用来筛选from子句中指定的操作所产生的的行
group by 子句用来分组where子句的输出
having子句用来从分组的结果中筛选行

having和where的区别：

语法类似，where搜索条件在进行分组操作之前应用；having搜索条件在进行分组操作之后应用
having可以包含聚合函数sum、avg、max等
having子句限制的是组，而不是行

当同时含有where子句、group by 子句、having子句及聚集函数时，执行顺序如下：

执行where子句查找符合条件的数据
使用group by 子句对数据进行分组
对group by 子句形成的组运行聚集函数计算每一组的值
最后用having 子句去掉不符合条件的组

锁模块

意向锁

加意向锁的目的是为了表明某个事务正在锁定一行或者将要锁定一行。表名加锁的“意图”。
要读写，直接加S锁或者X锁就好啦，为什么还要表名这样一个意图呢？

意向锁有两种：

意向共享锁（IS）表示事务意图在表中的单个行上设置共享锁。
意向排他锁（IX）表明事务意图在表中的单个行上设置独占锁。

我们先了解一下意向锁是在什么时候使用的。

在一个事务对一张表的某行添加S锁之前，它必须对该表获取一个IS锁或者优先级更高的锁。
在一个事务对一张表的某行添加X锁之前，它必须对该表获取一个IX锁。

那么为什么要这么做呢？这么做有什么好处呢？

这里就需要介绍一下表锁了。

以前我们说X锁或者S锁，都是在说给一行加上X锁或者S锁，如果我们用select * from student for update,是会student表加上X锁的。

数据库里面是同时允许锁表，或者锁行的。如果事务A需要修改某一行数据，则他会给该行加X锁。

这时事务B想申请整个表的X锁做某些操作。他能否申请成功呢？不能，因为申请成功则代表事务B可以对任意行做读写。显然这与事务A冲突了。

那这时数据库应该怎么判断呢？

可行方案就是，逐行判断是否加了X锁，如果都没加，就代表可以锁表，如果其中某一行加上了X锁，那么就不能整表加锁，不过，显然这样效率实在是太低了......

这个时候来看意向锁，就可以看出他发挥了重要的重要：

事务A想对某行上X锁之前，必须要获得到表的IX锁，现在没有其他事务使用IX锁，所以事务A获取成功。

事务A——获得IX锁——对某行上X锁

这个时候事务B想对这个表上X锁，发现IX表已经被拿走了，证明目前有其他事务正在修改该表的某行或者多行，此时事务B被阻塞......

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突

意向锁之间是相互兼容的：

-	意向共享锁（IS）	意向排他锁（IX）
意向共享锁（IS）	兼容	兼容
意向排他锁（IX）	兼容	兼容

为什么都是兼容呢？

事务A加了表的IX锁，或者IS锁，只代表事务A已锁定一行或者将要锁定一行。事务B当然也可以锁定其他的行，所以事务B肯定也是可以获得表的IS锁或者IX锁的。

-	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突
排他锁（X）	冲突	冲突

再次重申：这里的S锁和X锁是表级别的，意向锁不会与行级别的S锁和X锁冲突

举个例子：

事务A已经获得了IS锁，想要读取某行数据，事务B想要获得表的S锁，可以获得成功，因为读是兼容的
事务B获得了表的IX锁，想要修改某行数据，事务B想要获得表的X锁，但是由于IX锁已被获取走，证明有其他事务正在修改某行数据，所以事务B获得失败，只能被组塞住...

总结：

InnoDB 支持多粒度锁，特定场景下，行级锁可以与表级锁共存。
意向锁之间互不排斥，但除了 IS 与 S 兼容外，意向锁会与共享锁 / 排他锁互斥。
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

对select上排它锁：在语句后边加lock in share mode。SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

对select上排它锁：在语句后边加for update。SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。

通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

实例

1. myisim 默认会给表加表级锁，不支持行级锁，会锁住整张表

显示给表加读或写锁