Mysql问题大全候选键(candidate key): 不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性

Mysql常问问题及解答

各种树

B树（B-，是一种多路搜索树（并非二叉的））可以在叶子节点和非叶子节点命中关键字
B+树（Innodb用的这个树）只能在叶子节点命中数据全部存储在叶子节点
B*树与b+树有区别，为非叶子结点也增加链表指针
搜索树（二叉，多叉）左边的节点小于树节点的值，右边的节点大于树节点的值
平衡二叉树（平衡多叉树）任何一个节点的左子树和右子树的差的绝对值只能<=1
二叉平衡搜索树综合搜索树和平衡树的特点
红黑树

各种键：

超键(super key): 在关系中能唯一标识元组的属性集称为关系模式的超键

候选键(candidate key): 不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！

主键(primary key): 用户选作元组标识的一个候选键程序主键

外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

日志：

1、redo log重做⽇志

作⽤：确保事务的持久性，防⽌在发⽣故障，脏⻚未写⼊磁盘。重启数据库会进⾏redo log执⾏重做，到达事务⼀致性

2、undo log回滚⽇志

作⽤：保证数据的原⼦性，记录事务发⽣之前的数据的⼀个版本，⽤于回滚。

innodb事务的可重复读和读取已提交隔离级别就是通过mvcc+undo实现

3、error log 错误⽇志

作⽤：Mysql本身启动、停⽌、运⾏期间发⽣的错误信息

4、slow query log 慢查询⽇志

作⽤：记录执⾏时间过⻓的sql，时间阈值可以配置，只记录执⾏成功

5、binlog ⼆进制⽇志

作⽤：⽤于主从复制，实现主从同步

6、relay log 中继⽇志

作⽤：⽤于数据库主从同步，将主库发送来的binlog先保存在本地，然后从库进⾏回放

7、general log 普通⽇志

作⽤：记录数据库操作明细，默认关闭，开启会降低数据库性能

一些查询技巧：

高频查询，可以建立联合索引来使用覆盖索引，不用回表。
非高频查询，再已有的联合索引基础上，使用最左前缀原则来快速查询。
对于MySQL 5.6 引入索引下推，减少回表次数

死锁检测和处理：

Mysql死锁处理方式

1、等待，直到超时，事务自动回滚。

2、发起死锁检测，回滚一个事务，让其他事务执行。

死锁检测，构建一个以事务为起点，锁为边的有向图，看是否存在环。

删除表中的数据：

truncate = drop + create

drop直接删掉表，释放表占用的空间

truncate删除表中数据，再插入时自增长id又从1开始（会把表占用的空间回复到最初），不会删除定义

delete删除表中数据，会把删除的操作给记录在日志中，以便回退，不会释放空间，不会删除定义

删除速度 drop > truncate >delete

3.如果索引值为null，走不走索引

索引值为null，具体看成本

Mysql常见问题

1、MySQL中一条SQL语句的执行过程

Mysql主要分为Server层和存储引擎层：

Server层：主要包括连接器，查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图、函数等，还有一通用的日志模块binlog
存储引擎：主要扶着数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中的Innodb引擎有自有的日志模块redo log模块。现在最常见的存储引擎是InnoDB，他从Mysql5.5.5版本开始就被当做默认存储引擎了。

上图各个组件的介绍：

连接器：身份认证（登录）和权限校验（查表）
查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）
分析器：没有命中缓存的话，Sql语句就会结果分析器。词法分析（分析语句的结构）、语法分析（分析语句语法是否正确）
优化器：按照Mysql认为最优（可能不是最优）的方案去执行（生成执行计划）
执行器：执行语句，然后从存储引擎返回数据

总结：

查询语句的执行过程：权限校验-->查询缓存-->分析器-->优化器-->权限校验-->执行器-->引擎
更新等语句的执行过程：权限校验-->查询缓存-->分析器-->优化器-->权限校验-->执行器-->引擎（redo log prepare--> binlog-->redo log commit 简称redolog的两阶段提交）

一条SQL语句的执行过程

2、数据库几大范式

数据库范式：

1NF:每个关系的属性都是原子的，不可能分割。每一个列只有一个值。

2NF:如果关系模式R是1NF，且每一个非主属性完全依赖(而不能部分依赖)于候选建，那么就称R是第二范式。

3NF：如果关系模式R是2NF，且关系模式R（U,F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式。

BCNF: BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性

3、事务4大特性，一致性具体指什么？

1、A原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

2、C 一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的

3、I 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

4、D 持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

4、事务隔离级别，4个隔离级别分别有什么并发问题？

隔离级别：

**READ-UNCOMMITTED(**读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读
**READ-COMMITTED(**读取已提交): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读 仍有可能发生
REPEATABLE-READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化): 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说该级别可以防止脏读、不可重复读以及幻读。

各个级别出现的问题：
脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改：指一个事务读取一个数据时，另外的一个事务也访问了该数据。那么在第一个事务中修改了这个数据后，第二个事务也修改了此数据，导致第一个事务的修改结果丢失，所以称为丢失修改
不可重复读：一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

不可重复读与幻读的区别：不可重复读的重点是修改，幻读的重点在于新增或者删除

5、Mysql默认隔离级别？如何保证并发安全？

MySQLInnoDB默认支持可重复读，但使用了Next-Key Lock算法避免了幻读的发生。因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。完全达到了保证事务的隔离要求，即达到了SQL标准的SERIALIZABLE隔离级别。但在分布式事务下，一般使用SERIALIZABLE（可串性化）隔离界别

三种并发控制机制：

1、悲观并发控制：悲观并发控制其实是最常见的并发控制机制，也就是锁

2、乐观并发控制：乐观并发控制其实也有另一个名字：乐观锁

3、多版本并发控制：MVCC多版本并发控制机制，可以与前两者中的任意一种机制结合使用，以提高数据库的读性能

6、介绍Innodb锁机制，行锁、表锁、记录锁、间隙锁、意向锁

乐观锁：在访问数据之前，默认不会有其他事务对此数据进行修改，所以先访问数据，然后再查找在此期间是否有事务修改数据。这不是数据库自带的，需要我们自己去实现，一般基于版本去实现。（使用版本号来实现）

即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据（版本号等）与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

悲观锁：

按照锁的粒度把数据库锁分为表级锁和行级锁。

表级锁: 对当前操作的整张表加锁,实现简单，加锁快，不死锁，但并发能力低。

行级锁: 只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

Record Lock记录锁：锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB会创建一个隐藏的聚簇索引加锁。

Gap Lock间隙锁**: 间隙锁是一种记录行与记录行之间存在空隙或在第一行记录之前或最后一行记录之后产生的锁。间隙锁可能占据的单行，多行或者是空记录。 对索引项之间的“间隙”加锁，锁定记录的范围,不包含索引项本身。**其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。

Next-key Lock： 锁定索引项本身和索引范围。Next-Key 是一种记录锁和间隙锁的组合锁。既锁住行也锁住间隙。即Record Lock和Gap Lock的结合。可解决幻读问题。

根据是否独占，锁又可以分为共享锁和排他锁。

共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁**，**直到已释放所有共享锁。

排它锁（(Exclusive lock,简记为X锁)）又称为写锁**，**若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。

Innodb同时支持行锁和表锁。但行锁和表锁的同时存在会发生冲突，如A申请了行共享锁，而B再申请表互斥锁。这时B不仅需要查看是否已经存在其他表锁，以及逐个查看是否存在行锁，效率太低。于是又引入了意向锁。意向锁是一种表级锁，用来指示接下来的一个事务将要获取的是什么类型的锁（共享还是独占）。意向锁分为意向共享锁（IS）和意向独占锁（IX），依次表示接下来一个事务将会获得共享锁或者独占锁。

意向共享锁（IS）：事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。而事务B发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。而且，申请意向锁的动作是数据库自动完成的，不需要我们手动申请。

7、介绍MVCC

MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式

MVCC多版本并发控制（Multiversion Concurrency Control），多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回；在这时，读写操作之间的冲突就不再需要被关注，而管理和快速挑选数据的版本就成了 MVCC 需要解决的主要问题。

各数据库中MVCC实现并不统一，MVCC只在读已提交(READ COMMITTED) 和可重复度（REPEATABLE READ）两个隔离级别下工作;

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：(trx_id->事务ID、roll_pointer->上个版本指针,其实还有一个row_id的隐藏列但这里用不着);

每次对记录进行改动，都会把对应的事务id赋值给trx_id隐藏列，也会把旧的版本写入到undo log日志中；

所以在并发情况下，一个记录可能存在多个版本，通过roll_pointer形成一个版本链。MVCC的核心任务就是：判断一下版本链中的哪个版本是当前事务可见的。这就有了ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids；根据ReadView的活跃事务ID列表和版本链事务ID进行比较找出可见的事务ID最大的版本:

1、如果版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。

2、如果版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

3、被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC只在读已提交和可重复读这两个隔离机制下运行。这两个隔离机制下MVCC实现方式的区别就在于：读已提交是每次读取数据前都生成一个ReadView；而可重复读，是在第一次读取数据后生成一个ReadView，后序的重复查询就不再生产eadView了。

总结：

多版本并发控制指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复这个ReadVie就好了。

在RR级别下，

快照读是通过MVCC和undo log来实现的

当前读是通过加next-key lock（record lock和gap lock）来实现的。

8、“读“与”读“的区别

MySQL中的读，和事务隔离级别中的读，是不一样的。

我们且看，在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题

对于这种读取历史数据的方式，我们叫它为快照读（snapshot read）,而读取数据库当前版本数据的方式，称为当前读（current read）。很显然，在MVCC中：

快照读：
- select * from table
当前读：特殊的读操作、插入、更新、删除操作，属于当前读，处理的都是当前的数据，需要加锁
- select * from table where？ lock in share model
- select * from table where ? for update
- insert
- update
- delete

事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些当前，就需要另外的模块来解决了。

MVCC 不能解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题

9、存储引擎Innodb和Myisam的区别使用场景

1、MyISAM不支持事务，而Innodb支持事务。

2、MyISAM支持表级锁，而Innodb支持行级锁和表级锁（默认是行级锁）。

3、外键支持：MyISAM表不支持外键，而InnoDB支持。

4、count运算：MyISAM缓存有表的行数，这种缓存只是表行的总数，where筛选无效。而Innodb没有。

各自的使用场景：

MyISAM适合：(1)做很多count 的计算；(2)读密集；(3)没有事务。

InnoDB适合：(1)要求事务；(2)写密集（3）高并发

10、聚簇索引和非聚簇索引区别

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序；

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关；

区别：

聚簇索引：
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
非聚簇索引：叶子节点的内容是数据的地址

11、创建索引原则、使用索引注意项、避免全表扫描

使用索引的情况：

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段(外键)上应该建立索引；

5、索引应该建在选择性高的字段上；

7、频繁进行数据操作的表，不要建立太多的索引；

2、经常作为查询条件在WHERE或者ORDER BY语句中出现的列要建立索引；

3、作为排序的列要建立索引；(单纯的order by不会用到索引，但如果在where中出现，就可以用索引了。)

5、高并发条件下倾向联合索引；

6、用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引。

7、使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。（有公式的，高性能mysql上有提）

什么时候不要使用索引？

1、经常增删改的列不要建立索引；

2、有大量重复的列不建立索引；

3、表记录太少不要建立索引。

4、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

索引使用注意项：

1、避免 where 子句中对字段施加函数，这会造成无法命中索引

2、在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键

3、将打算加索引的列建议设置为 NOT NULL ，因为 NULL 比空字符串需要更多的存储空间（不仅仅是索引列，普通的列如果业务允许都建议设置为 NOT NULL）

4、删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗（MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用）

索引优化：

1、根据联合索引的最左前缀原则，我们一般把排序分组频率最高的列放在最左边

2、模糊查询以%为开始的查询，只能使用全文索引来进行优化。

3、使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。

4、存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

避免全表扫描：

1、where字句别用or链接，可以union all

2、in和not in也慎用，可以between and

3、避免对字段进行null值判断

4、where子句别用!=和<>操作符

5、别在where子句对字段进行表达式操作和函数操作

6、别用以通配符开头的like的查询

7、任何地方都不要使用 select * from t

8、尽量使用数字型字段。

9、联合索引尽量满足最左前缀原则。

10、在查找唯一一条数据的时候，使用limit 1,10 类型不一致会导致失效，例如字符串不加单引号会导致索引失效。

Mysql高性能优化规范建议

书写高质量SQL的30条建议

12、索引的优缺点、索引类型、索引数据结构、哈希索引实现

索引优缺点：

(1) 优点：

(a) 大大加快数据的检索速度（主要原因）

(b) 创建唯一性索引，保证每一行的数据的唯一性等

(2) 缺点：

(a) 进行数据的增删改时候需要动态维护索引

(b) 索引需要占用空间

索引类型：主键索引，唯一索引，全文索引，普通索引，联合索引。

Mysql 索引主要使用的哪两种数据结构？

哈希索引（自适应Hash）：对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree 索引。
BTree 索引：Mysql 的 BTree 索引使用的是 B 树中的 B+Tree。但对于主要的两种存储引擎（MyISAM 和 InnoDB）的实现方式是不同的。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希索引的缺点：

1、不能使用范围查询。

2、无法利用索引的数据来避免任何排序运算；

3、不支持多列联合索引的最左匹配规则；

4、任何时候都不能避免表扫描。

5、存在所谓的哈希碰撞问题。

13、B树索引为什么使用B+树，相对于B树有什么优点？为什么不能红黑树？要提到磁盘预读

B树：可以在叶子节点和非叶子节点命中关键字

B+树，只有叶子节点存储数据，其他的节点只是起到索引的作用。平衡，性能稳定，每次查询的次数都是树的高度。非叶子节点只存储键值信息。数据记录都存放在叶子节点中。所有叶子节点之间都有一个链指针。

索引是一种数据结构。索引本身很大，不可能全部存储在内存中，因此索引以索引表的形式存储在磁盘中。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

B+树的优点：

1、B+树中间节点不存放数据，所以同样大小的磁盘页上可以容纳更多节点元素，IO次数更少。

2、B+树的查询必须最终找到叶子节点，而B-树只需要找到匹配的元素即可。B+树性能稳定。

3、范围查询方便。B-树只能依靠繁琐的中序遍历，而B+树只需要在链表上遍历即可。

因为普通的全表查询时间复杂度是O(n);如果是平衡二叉树，或者红黑树，查找时间变成O(logN)，但他们依然不适合做索引。因为索引通常比较大，存于磁盘中，无法一次将全部的索引加载到内存中，每次只能从磁盘中读取一个页到内存中，而平衡二叉树底层实现是数组，逻辑上相邻的节点在物理结构上可能相差很远，因此磁盘IO次数可能很大，平衡二叉树没能充分利用磁盘预读功能。磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存（局部性原理）。这样做的理论依据是计算机科学中著名的局部性原理。红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

B（B-）树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

B+树的关键字全部存放在非叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向下一个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

14、回表查询和覆盖索引

回表查询：B+树有主键索引和辅助索引两种

主键索引：按照表中主键的顺序构建一颗B+树，并在叶节点中存放表中的行记录数据，一个表只能有一个主键索引。

辅助索引：叶节点并不存储行记录数据，仅仅是主键的值。通过辅助索引查找到对应的主键，最后在聚集索引中使用主键的值获取对应的行记录。

覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次,这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

解释一：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）（

15、in与exists的区别

in与exists的区别。

使用上，in 后面的查询返回结果只能有一个字段。而exists没有限制。

本质上： A exists B;exists相当于遍历外面A,看A中数据是否存在于B。而in,相当于将结果集B分解开，用or相连，相当于做多次的查询。

exists相当于查询筛选，in则是多次查询

1、如果查询的两个表大小相当，那么用in和exists差别不大。

2、如果两个表中一个表大，另一个是表小，那IN适合于外表大而子查询表小的情况。

3、如果两个表中一个表大，另一个是表小，EXISTS适合于外表小而子查询表大的情况。

in不会使用索引搜索，会全表扫描。

16、慢查询原因及解决方法

慢查询分为2种：

1、大多数情况是正常的，知识偶尔会出现很慢

2、在数据量不变的情况下，这条Sql语句一直以来都执行的很慢

第一种：偶尔慢

1、数据库在刷脏页

当我们往数据库插入、更新、修改一条数据时，数据库先会在内存中把相应的字段的数据更新，但是更新后，这些更新的字段并不会马上同步持久化到磁盘中去。而是把这些更新的记录到redo log日志中去，等到必要的时候（空闲、内存不足），再通过redolog的日志把最新的记录刷到（持久化）磁盘上。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页分为以下4种情况：

1、redo log写满：redo log（默认4GB）里面的容量是有限的，如果数据库一直很忙，数据库更新很频繁，那么redo log很快就会被写满，这个时候没办法等到空闲的时候再去把数据同步到磁盘，必须停下手中的活，全身心的把数据同步到磁盘中去，所以这个时候，就会导致我们平时正常的SQL语句突然执行的很慢。所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。

2、内存不够：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页

3、**MySQL 认为系统“空闲”的时候：**这时系统没什么压力

4、MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

2、拿不到锁

我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了

第二种：一直慢

1、没用到索引(sql语法问题导致)

例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

1、查询字段没索引

查询字段没有索引，只有走全表扫描
2、查询字段有索引，但是没用上

注意自己的Sql语法，是不是遵守了一些运算的基本原则
3、函数操作导致没有用上索引

2、数据库选错了索引（优化器导致的）

系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大.

系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的

系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

强制走索引的方式来查询

select * from t force index(a) where c>100 and c<10000

查询索引的基数和实际是否符合

select index from t

重新来统计索引的基数，可以用这条命令

analyze table t

极客时间Mysql45讲-19讲

一条SQL语句执行很慢的原因有哪些

17、Explain语句各字段的意义

Explain:

1、ID: SELECT的查询序列号;

2、select_type:示查询中每个select子句的类型

1、SIMPLE(简单SELECT，不使用UNION或子查询等)

2、 PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

3、 UNION(UNION中的第二个或后面的SELECT语句)

4、DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

5、UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

6、 SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

7、 DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

8、 DERIVED(派生表的SELECT, FROM子句的子查询)

9、UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

3、Table:显示这一步所访问数据库中表名称

4、Type：对表访问方式，表示MySQL在表中找到所需行的方式，又称访问类型。

ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

1、All:全表扫描。

2、index: full index scan,遍历索引树。

3、range:只检索给定范围的行，使用一个索引来选择行

4、ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

5、eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

6、const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

7、NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

5、possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6、Key：显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

7、key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

8、ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

9、rows：估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

10、extra: 含MySQL*解决查询的详细信息,有以下几种情况：

1、Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

2、Using temporary ：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

3、Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

4、Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

5、Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

6、Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

7、no tables used：Query语句中使用from dual 或不含任何from子句

18、最左前缀、联合索引B+树？当where子句中出现>时，联合索引命中是如何的? 如 where a > 10 and b = “111”时，联合索引如何创建、mysql优化器会针对得做出优化吗？

最左前缀原则：Mysql索引可以引用多列，叫联合索引，如果查询条件精确匹配联合索引的左边连续一列或者多列，则查询命中索引。a,b,c的联合索引，(a,b)可以命中（a,c）不能命中。联合索引并不是全部不中，或者全部中。

MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，不过建议 where 后的字段顺序和联合索引保持一致，养成好习惯

联合索引(大于等于2小于等于3)也是建立一个B+树，只不过非叶子节点存储的是第一个列。叶子节点组合的列都有，中了第一列之后，然后安装其他的列索引搜查。

联合索引的好处：利用覆盖索引，避免回表操作。

19、left join,right join,inner join,outer join的含义

内连接inner join：内连接是一种一一映射关系，就是两张表都有的才能显示出来

左连接left join: 左连接是左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分只能补空显示

右连接right join:右连接，右边表的所有数据都会显示出来，左边的只会出现共同的那部分，其他的空。

全连接、外连接Outer Join :查询出左表和右表所有数据，但是去除两表的重复数据

[具体的有案例]([github.com/0voice/inte…](github.com/0voice/inte… 连接的种类.md))

20、mysql主从复制过程，binlog记录格式，复制的异步半同步同步模式区别

上图中，包含了我在上一篇文章中讲到的 binlog 和 redo log 的写入机制相关的内容，可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

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

从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position，从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在祝节点上实际执行过的操作，并在本数据库中执行。

MySQL主从复制默认是异步的模式：

1、异步复制：主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理；主节点不会主动push bin log到从节点；

2、半同步模式：这种模式下主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性。半同步模式不是mysql内置的，需要装插件开启半同步模式。

3、全同步模式：全同步模式是指主节点和从节点全部执行了commit并确认才会向客户端返回成功。

binlog记录格式：

1、基于SQL语句的复制：会将修改数据的sql语句记录到binlog中，减少了binlog日志量，节约IO,提高性能。某些情况：会导致主从节点中数据不一致。

2、基于行的复制：将SQL语句分解为基于Row更改的语句并记录在binlog中，也就是只记录哪条数据被修改了，修改成什么样。

优点：解决了特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点日志量太大。3、混合方式：能语句就语句，不能语句就切换行。

21、主从复制或读写分离等数据不一致性

读写分离存在的问题

在从库上会读到系统的一个过期状态的现象，暂且称之为“过期读”。

强制走主库方案其实就是，将查询请求做分类，对于必须要拿到最新结果的请求，强制将其发到主库上。

sleep 方案：主库更新后，读从库之前先sleep 一下。具体的方案就是，类似于执行select sleep(1)命令。

mysql主从复制存在的问题：

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

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

解决方法：

半同步复制：解决数据丢失的问题

并行复制—-解决从库复制延迟的问题（并行是指从库多线程apply binlog库级别并行应用binlog，同一个库数据更改还是串行的(5.7版并行复制基于事务组)设置）

22、分布式Id生成

1、UUID：不适合做主键，因为太长了，并且无需不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字

2、数据库自增id：: 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

3、利用redis生成id：性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本

4、Twitter的snowflake算法：Github 地址：snowflake算法Github。

5、美团的Leaf分布式ID生成系统：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。美团分布式ID技术文章

微信上-IT牧场-分布式Id的生成的补充与总结

学习链接

知乎关于索引的文章，写的是自己（平时用的很多的）关于阿里面试的

Mysql问题大全