面试之Mysql

一、序言

上次整理了一篇面试之http与https,根据面试常问的方向，今天总结一下关于mysql的面试点。

二、关系型数据和非关系型数据库

1. 关系型数据库

优点

易于维护：都是使用表结构，格式一致；
使用方便：通用SQL语句，不需要额外学习；
支持复杂操作：可以一个表和以及多个表之间非常复杂的查询。

缺点

固定的表结构,导致灵活性差；
关系型数据库存储于磁盘，则读写性能会比较低。

2. 非关系型数据库

优点

格式灵活：不固定存储数据的格式。可以是文档，kv，图片等等。
速度快：nosql不仅可以使用硬盘存储，也可以使用随机存储器存储。
成本低：nosql部署简单，都是开源的。

缺点

不支持sql，学习成本较高；
无事务的加持；
数据结构的复杂性，所以复杂查询不好操作。

三、mysql的事务特性 ACID

• 原子性： 事务是一个不可分割的单位，要么同时成功，要么同时失败。
• 一致性： 事务执行前后，数据库状态保持一致。事务将数据库从一个状态转移到另外一个状态。
• 隔离性： 并发事务和事务之间互相不影响。
• 持久性： 一旦事务提交，其对数据库的修改将永久保存，即使系统崩溃或出现故障。

四、mysql的事务隔离级别

1. 脏读（读未提交）

事务a读取一条数据，事务b修改这条数据，但是并没有提交，这时事务a再次读取，读取到还没有提交的数据。

2. 不可重复读（读已提交）

事务a读取一条数据，事务b修改这条数据，但是并没有提交，这时事务a再次读取，读到的还是之前的数据，此时事务b提交修改，事务a再次读取，读到修改后的数据，虽然解决了脏读，但是却出现了两次读取的数据不一致，也就是不可重复读。

3. 可重复读 （幻读）

事务a： select * from table where id>=3;
此时，事务b：insert into table（name）values (‘zjx’), 假设这条语句插入id为4。
事务a： select * from table where id>=3;
则两次范围查询出来的数量不一样，出现了幻读

4. 串行化

它强制事务按照顺序执行，从而可以避免前面提到的幻读问题，但它会在读取的每一行数据上都加锁，或者每次读取都在一个单独的事务中，总之，这种隔离级别会产生大量的加锁情况，效率很低

五、mysql事务日志

在上面我们提到了事务的四大特性（acid），那事务的这四大特性是怎么实现的呢？

事务的隔离性是由锁机制实现的。
事务的原子性，一致性，持久性，则是由事务的日志redo和undo来保证的。

• REDO LOG 称为重做日志，提高再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
• REDO LOG：是存储引擎层（innodb）生成的日志，记录的是物理级别上的页面修改操作，比如页号xxx、偏移量yyy，再写入了‘zzz’数据。主要为了保证数据的可靠性。
• UNDO LOG称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。
• UNDO LOG ：是存储引擎层（innodb）生成的日志，记录的是逻辑操作日志，比如对某一行数据进行了INSERT语句的操作，那么UNDO LOG 就记录一条与之相反的DELETE操作。主要用于事务的回滚（UNDO LOG 记录的是每个修改操作的逆操作）和一致性非锁定读（UNDO LOG 回滚行记录到某种特定的版本 — MVCC，即多版本并发控制）。

redolog

INNODB存储引擎是以页为单位来存储管理空间的。正真访问页面之前，需要把磁盘上的页缓存到内存中的BufferPool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定频率被刷进磁盘（checkpoint机制）。

为什么使用BufferPool缓冲池？
为了减少cpu和磁盘之间的速度差距，保证性能。
为什么需要redolog呢？
我们知道缓冲池可以帮助加快速度，checkpoint机制可以把数据刷进磁盘，但是checkpoint并不是每次变更的时候就触发的，而是master线程没隔一段时间就去处理的。就有可能出现下面的情况：
事务提交之后，刚写进缓冲池，然后数据库就宕机了，那我们的这段数据就丢失了，无法恢复。
redolog怎么保证数据的持久性呢？
我们只是想让已经提交的事务对数据库中所做的修改永远生效，就算后来系统崩溃，再重启之后，也能把这种修改修复出来，所以我们只用把修改了那些东西记录一下就好。
在INNODB引擎中，采用的就是wal技术，也就是先写日志，再写磁盘，只有日志写成功，才能算事务提交成功，当发生宕机且数据未刷入磁盘的时候，就可以通过日志来恢复。这个日志就是redolog的作用。保证了事务的持久化。

优点

REDO日志降低了刷盘频率。
REDO日志占用的空间非常小。
存储表空间ID、页号、偏移量以及需要更新的值，所需要的存储空间是很小的，刷盘快。

特点

REDO日志是顺序写入磁盘的
在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志就是按照产生的顺序写入磁盘的，也就是使用顺序IO，效率比随机IO快。
事务执行过程中，REDO LOG 不断记录
REDO LOG跟BIN LOG的区别，REDO LOG是存储引擎层产生的，而BIN LOG 是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往REDO LOG顺序记录，而BIN LOG不会记录，直到这个事务提交，才会一次写入到BIN LOG文件中。
如何将redo log buffer中的日志刷新到redo log file中，它支持三种策略
设置为0：系统没隔1秒做一次

为0时，master thread 中每1秒进行一次重做日志的fsync操作，因为实例crash最多丢失1秒钟内的事务。（master thread是负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性）
数值0的话，是一种折中的做法，它的IO效率理论是高于1的，低于2的，这种策略也有丢失数据的风险，也无法保证D。

设置为1：表示每次事务提交成功都进行同步 （默认值）

为1时，只要事务提交成功，redo log 记录就一定在磁盘里，不会有任何数据丢失。
如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。可以保证ACID的D，数据不会丢失，但是效率最差的。
建议使用默认值，虽然操作系统宕机的概率理论小于数据库宕机的概率，但是既然使用了事务，那么数据的安全相对来说要更重要些。

设置为2: 表示每次事务提交时都只把redo log buffer 内容写入 page cache，不进行同步。由OS自己决定什么时候同步到磁盘文件。

为2时，只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。
如果仅仅只是MySQL挂了不会有任何数据丢失，但是操作系统宕机可能会有1秒数据的丢失，这种情况下无法满足ACID中的D，但是数值2肯定是效率最高的。

undolog

undolog是事务原子性的保证。在事务中更新数据的前置操作起始就是要先写入一个undolog。

为什么需要undolog？

事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做。但是也有可能在事务执行中会出现一些情况，例如：

• 事务执行中遇到错误，服务器，操作系统，或者断点等中断事务的操作。
• 可以手动输入rollback语句结束当前事务的执行。
  出现上面青睐的时候，我们需要把数据修改为原来的样子，这个过程称之为回滚，这样就可以造成一个假象，这个事务看起来什么都没有做，符合原子性要求。
  每当要对一条记录做改动时（这里的改动可以指INSERT、DELETE、UPDATE），都需要“留一手” — 把回滚时所需的东西记录下来，比如：
• 当插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。（对于每个INSERT，InnoDB存储引擎会完成一个DELETE）。
• 当删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。（对于每个DELETE，InnoDB存储引擎会执行一个INSERT）。
• 当修改了一条记录，至少要把修改这条记录前的旧值记录下来，这样后续回滚时再把这条记录更新为旧值就可以了。（对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去）。
  MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志（即undo log）。注意，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。
  此外，undo log 会产生redo log，也就是undo log的产生伴随着redo log的产生，这是因为undo log也需要持久性的保护。

undolog的作用

• 作用1：回滚数据

用户对undo日志可能有误解：undo用于将数据库物理地恢复到执行语句或事物之前的样子。但事实并非如此，undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有的修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页面中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

• 作用2：MVCC

undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前版本信息，以此实现非锁定读取。

undolog页的重用

指路 http://t.csdnimg.cn/Fb5M2

六、MVCC 多版本并发控制

主要是为了实现读写冲突不加锁的快照读
这里的多版本指的是数据库中同时存在多个版本的数据，是一条记录多个版本同时存在。

1. 当前读和快照读

当前读

当前读是一种读取数据的操作方式，它可以直接读取最新的数据版本，读取时还需要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
SELECT … LOCK IN SHARE MODE (共享读锁)：

• 使用共享读锁时，其他事务可以同时读取相同的数据，但不能修改它。
• 适用于需要读取数据的场景，但不希望其他事务修改该数据。
  SELECT … FOR UPDATE：
• 使用排他锁时，其他事务无法同时读取或修改相同的数据。
• 适用于需要读取数据并且在之后修改该数据的场景，例如更新或删除操作。
  UPDATE / DELETE / INSERT：
• 当前读确保读取的是最新版本的数据，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。
• 例如，假设要更新一条记录，但另一个事务已经删除了这条数据并且提交了，如果不加锁就会产生冲突。因此，update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

当前读的语法：

快照读

快照读是在读取数据时读取一个一致性视图中的数据，MySQL使用 MVCC 机制来支持快照读。
具体而言，每个事务在开始时会创建一个一致性视图（Consistent View），该视图反映了事务开始时刻数据库的快照。这个一致性视图会记录当前事务开始时已经提交的数据版本。
当执行查询操作时，MySQL会根据事务的一致性视图来决定可见的数据版本。只有那些在事务开始之前已经提交的数据版本才是可见的，未提交的数据或在事务开始后修改的数据则对当前事务不可见。
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读。
快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
注意：快照读的前提是隔离级别不是串行级别，在串行级别下，事务之间完全串行执行，快照读会退化为当前读
MVCC主要就是为了实现读-写冲突不加锁，而这个读指的就是快照读，是乐观锁的实现。

2. MVCC

之前我们有提到undolog是mvcc实现的核心，
在InnoDB存储引擎中，Undo日志分为两种：插入（insert）Undo日志 和 更新（update）Undo日志

1. insert undolog：插入Undo日志是指在插入操作中生成的Undo日志。由于插入操作的记录只对当前事务可见，对其他事务不可见，因此在事务提交后可以直接删除，无需进行purge操作。
2. update undolog：更新Undo日志是指在更新或删除操作中生成的Undo日志。更新Undo日志可能需要提供MVCC机制，因此不能在事务提交时就立即删除。相反，它们会在提交时放入Undo日志链表中，并等待purge线程进行最终的删除。删除操作只是设置一下老记录的DELETED_BIT，并不真正将过时的记录删除，为了节省磁盘空间，InnoDB有专门的purge线程来清理 DELETED_BIT为true的记录。

**注意：**由于查询操作（SELECT）并不会修改任何记录，所以在查询操作执行时，并不需要记录相应的 undo log 。
在mysql中，MVCC只在读取已提交和可重复读两个事务级别下有效。是通过undolog中的版本链和readview一致性视图来实现。MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。

版本链

不同的事务或者相同的事务对同一行记录的修改，会使该记录行的undolog成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。
每个数据行都有一个版本链，包含了该数据行的所有历史版本，每次更新操作都会生成一个新的数据版本，而不是覆盖旧版本，这些版本按照时间顺序链接在一起，就形成了版本链

http://t.csdnimg.cn/tAI5h

3. 隐藏字mysql的每行数据，除了我们自定义的字段之外，还包含了三个隐藏字段，在内部使用。

DB_ROW_ID : 隐含的自增ID（隐藏主键），用于唯一标识表中的每一行数据，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。
DB_TRX_ID：每个事务都有一个唯一的事务ID，用于标识该事务的开始和结束。事务ID通常是递增的，并且是全局唯一的。通过 DB_TRX_ID 字段，可以追踪行数据和事务的所属关系。
DB_ROLL_PTR：该字段存储了回滚指针（Roll Pointer），它指向用于回滚事务的Undo日志记录。

版本链和隐藏字段，undolog之间的使用

一张原始数据表

事务A：执行update语句修改id=1的name 为 李四

• 获得一个事物编号 104
• 把这张表修改前的数据拷贝到undolog
• 修改id=1的这条数据
• 把修改后的事务版本号改成当前事务版本号，并把DB_ROLL_PTR地址指向undo log数据地址。
  如图所示：
  

Read View （读视图）

在innodb 中每个事务开启后都会得到一个read_view。副本主要保存了当前数据库系统中正处于活跃（没有commit）的事务的ID号，其实简单的说这个副本中保存的是系统中当前不应该被本事务看到的其他事务id列表。
ReadView一致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最大事务ID组成（实际上ReadView还有其他的字段，但不影响这里对MVCC的讲解）。
一个事务启动的时候，innodb会为这个事务构造一个数组，用来保存这个事务的启动瞬间正在活跃的所有事务id。“活跃”指的是启动了，但是没提交。
数组里面id最小的值即为低水位，最大的值+1记为高水位，这便是一致性视图。
每个事务在做查询的时候会根据一致性视图的可见性规则去undo log版本链中推导对应的数据。
比如：[100,200],300。事务100和200是当前未提交的事务，而事务300是当前创建的最大事务（已经提交了）。当执行SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，生成ReadView的策略是不一样的：读取已提交级别是每执行一次SELECT语句就会重新生成一份ReadView，而可重复读级别是只会在第一次SELECT语句执行的时候会生成一份，后续的SELECT语句会沿用之前生成的ReadView（即使后面有更新语句的话，也会继续沿用）。

读视图包括以下信息：

• m_ids: 当前系统活跃(未提交)事务版本号集合。
• max_limit_id: 创建当前read view时“当前系统最大事务版本号+1(下一个应该分配的事务id值)。
• min_id: 创建当前read view 时“系统正处于活跃事务最小版本号（未提交）”
• creator_trx_id: 创建当前read view的事务版本号；
  如何根据某个读事务生成的ReadView快照，判断版本链上的某个版本对该查询事务是否可见呢？遵循以下步骤：
• 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前读事务在访问它自己修改过的记录，所以该版本对当前事务可见；
• 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本对当前事务可见；
• 如果被访问的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，该版本对当前事务不可见；
• 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本对当前事务不可见；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本对当前事务可见。

案例

原数据

事务a 查询数据 select * from user where id = 1；事务id = 104

事务b 修改数据 update user set name = tyq where id = 1；事务id = 105

事务b 查询数据 select * from user where id = 1；事务id = 105

事务a 查询数据 select * from user where id = 1；事务id = 104
这是为什么呢？ 为这是为什么呢？ 为什么事务a和事务b查询出来的数据不一样？
这是因为事务b 再查询的时候事务id为105，此时105虽然是活跃的事务id，但是是他自己的id，所以可以查看，当事务a查询数据的时候 事务id为104，[此时要看现在的readview的数据（并且根据读已提交和可重复读的情况来看是否创建新的readview），105是活跃的，根据版本链记录下一个是103]103是已经提交的，所以可以返回。
事务b 提交事务
事务a 查询数据 select * from user where id = 1；（事务d的事务id为104）

这个案例可以指路 http://t.csdnimg.cn/bXkZU

可重复读的时候是每次select都产生一个select，但是事务id并不需要改变

七、Mysql索引

Mysql的索引使用的数据结构是B+Tree。

为什么需要索引？

索引的出现起始就是为了提高数据查询效率，就像书的目录一样。事实上，索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。
索引王五是存在磁盘上的文件中（INNODB是数据索引在一起，一个文件；MYISAM是数据和索引分成两个文件）。

索引的数据结构

INNODB采用的是B+树来实现索引的，B+树是一颗变种的B树，变种的主要目的是减少树的深度和大小，以减少IO的次数，提升效率。
例如：

如果我们要加载id=55的数据，那么

• 1.把磁盘块1的数据由磁盘加载到内存，发生一次IO，在内存中用二分查找确定55在50-100，锁定磁盘块1的P2指针，确定了磁盘块3的位置。
• 2.把磁盘块3的数据由磁盘加载到内存，发生第二次IO，确定55在50-60之间，锁定磁盘块3的P指针，确定了磁盘块7的位置。
• 3.加载磁盘块7的数据到内存，发生第三次IO，在内存中做二分查找确定数据55，结束查询，总计三次IO。
• 真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B树和B+树的区别

B+树（B+Tree）是一种类似于B-Tree的自平衡搜索树结构，被广泛应用于数据库和文件系统等领域。它是B-Tree的一种变体，相较于B-Tree，在存储和查询性能上有一些优化。
B+树与B-Tree相似，也具有多路平衡性、有序性和磁盘友好性的特点。但B+树在某些方面具有不同的设计：
只有叶子节点存储数据：B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。
叶子节点之间通过指针连接：B+树的叶子节点使用指针进行连接，形成一个有序链表，便于范围查询和顺序遍历。
顺序访问性能更好：由于叶子节点之间的指针连接和有序链表的形式，B+树在顺序访问时具有更好的性能。例如，对于范围查询或者按照关键字顺序遍历数据，B+树比B-Tree更适合。
叶子节点之间没有互相连接：B+树的叶子节点之间并没有直接的连接，需要通过内部节点进行导航，这样可以减少内部节点的空间占用。
B+树通常被用作数据库系统的索引结构，特别适用于支持范围查询和按顺序访问数据的场景。它的平衡性和磁盘友好性使得在大规模数据集的存储和检索过程中具有良好的性能表现。

几种索引数据结构类型的指路：[http://t.csdnimg.cn/29LVP] (http://t.csdnimg.cn/29LVP)

考点：

1. 为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

• 相比于二叉树：二叉树顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。B+树，能解决顺序插入的问题且层级更少，搜索效率高。
• 相比于红黑树：红黑树虽然解决了顺序插入形成链表的问题，但是本质上二叉树，大数据量情况下，层级较深，检索速度慢。B+树，能解决顺序插入的问题且层级更少，搜索效率高。
• 相比于B-Tree：对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。而B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。B+树的叶子节点会形成一个有序链表，便于范围查询和顺序遍历。
• 相对于 Hash 索引：Hash索引只支持等值匹配，不支持范围查询和排序。B+Tree 支持范围匹配及排序操作。

2. 索引有什么缺点?

• 空间代价

索引就是一棵B+数，每创建一个索引都需要创建一棵B+树，每一棵B+树的节点都是一个数据页，每一个数据页默认会占用16KB的磁盘空间，每一棵B+树又会包含许许多多的数据页。所以，大量创建索引，你的磁盘空间会被迅速消耗。

• 时间代价

链表的维护
主键索引的B+树的每一个节点内的记录都是按照主键值由小到大的顺序，采用单向链表的方式进行连接的

如果我现在要删除主键id为1的记录，会破坏3个数据页内的记录排序，需要对这3个数据页内的记录进行重排列，插入和修改操作也是同理。
假如这张表有12个字段，我们为这张表的12个字段都设置了索引，我们删除1条记录，需要涉及到12棵B+树的N个数据页内记录的排序维护。
更糟糕的是，你增删改记录的时候，还可能会触发数据页的回收和分裂。还是以上图为例，假如我删除了id为13的记录，那么数据页124就没有存在的必要了，会被InnoDB存储引擎回收；我插入一条id为12的记录，如果数据页32的空间不足以存储该记录，InnoDB又需要进行页面分裂。我们不需要知道页面回收和页面分裂的细节，但是能够想象到这个操作会有多复杂。
如果每个字段都创建索引，所有这些索引的维护操作带来的性能损耗，
查询计划
执行查询语句之前，MySQL查询优化器会基于cost成本对一条查询语句进行优化，并生成一个执行计划。如果创建的索引太多，优化器会计算每个索引的搜索成本，导致在分析过程中耗时太多，最终影响查询语句的执行效率。

• 回表的代价

ps: 只有聚集索引也就是主键id的索引 是id下面一行的数据都在叶子结点，其他索引的叶子结点都是只有主键id。拿到主键id再去聚集索引下面获取索引的就叫做会标。
1、额外磁盘IO：如果回表的目标数据页恰好在内存中的话效果倒也不会太差，但如果不在，则需要额外的磁盘IO操作，增加响应时间，特别是大型表上；
2、增加CPU开销：回表过程需要消耗额外的 CPU 资源，因为数据库引擎需要执行额外的操作来定位并返回所需的数据行。
3、增加网络传输开销：如果数据库引擎和数据存储不在同一台服务器上，回表可能导致额外的网络传输开销，因为需要将数据从存储服务器传输到查询引擎所在的服务器。
4、增加锁的竞争：回表操作可能导致额外的锁的竞争，尤其是在高并发的环境中。如果多个查询需要回表，并试图访问相同的数据行，可能会引发锁的争夺，影响系统的并发性能。
所以，最好不要回表，或者减少会标次数

• 优化回表策略：

1. 覆盖索引
想一下，如果非聚簇索引的叶子节点上有你想要的所有数据，是不是就不需要回表了呢？比如我为 name 和 phone 字段创建了一个联合索引，如果我们恰好只想搜索 name、phone、主键字段：
select id,name,phone from t_user where name=‘张三’;
可以直接从叶子节点获取所有数据，根本不需要回表操作。
我们把索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。
2. 索引下推
还是拿name和phone的联合索引为例，我们要查询所有name为「蝉沐风」，并且手机尾号为6606的记录，查询SQL如下：
SELECT * FROM user_innodb WHERE name = “蝉沐风” AND phone LIKE “%6606”;
由于联合索引的叶子节点的记录是先按照name字段排序，name字段相同的情况下再按照phone字段排序，因此把%加在phone字段前面的时候，是无法利用索引的顺序性来进行快速比较的，也就是说这条查询语句中只有name字段可以使用索引进行快速比较和过滤。正常情况下查询过程是这个样子的：
InnoDB使用联合索引查出所有name为蝉沐风的二级索引数据，得到3个主键值：3485，78921，423476；
拿到主键索引进行回表，到聚簇索引中拿到这三条完整的用户记录；
InnoDB把这3条完整的用户记录返回给MySQL的Server层，在Server层过滤出尾号为6606的用户。
如下面两幅图所示，第一幅图表示InnoDB通过3次回表拿到3条完整的用户记录，交给Server层；第二幅图表示Server层经过phone LIKE "%6606"条件的过滤之后找到符合搜索条件的记录，返给客户端。

值得我们关注的是，索引的使用是在存储引擎中进行的，而数据记录的比较是在Server层中进行的。
现在我们把上述搜索考虑地极端一点，假如数据表中10万条记录都符合name='蝉沐风’的条件，而只有1条符合phone LIKE "%6606"条件，这就意味着，InnoDB需要将99999条无效的记录传输给Server层让其自己筛选，更严重的是，这99999条数据都是通过回表搜索出来的啊！关于回表的代价你已经知道了。
现在引入索引下推。准确来说，应该叫做索引条件下推（Index Condition Pushdown，ICP），就是过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。ICP是在MySQL5.6之后完善的功能。
索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
再回顾一下，我们第一步已经通过name = "蝉沐风"在联合索引的叶子节点中找到了符合条件的3条记录，而且phone字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历，筛选出尾号为6606的记录，找到主键值为78921的记录，最后只需要进行1次回表操作即可找到符合全部条件的1条记录，返回给Server层。
很明显，使用ICP的方式能有效减少回表的次数。
另外，ICP是默认开启的，对于二级索引，只要能把条件甩给下面的存储引擎，存储引擎就会进行过滤，不需要我们干预。
指路这里使用的文章

索引失效

**索引的选择，用不用索引都是优化器说了算，

那么MySQL优化器是什么呢？**

MySQL内部优化器是MySQL中很重要的一个部分，它主要用于在执行查询时获取最合适的执行计划，以使得查询能够以最短的时间内得到结果。
MySQL内部优化器的工作原理是在接收到一条查询语句之后，它会根据一系列的算法和规oSi则来确定哪个执行计划是最优的。
通常情况下，MySQL优化器会依赖于表的统计信息和索引信息来进行优化决策。例如，在执行select语句时，优化器会尝试使用索引来避免全表扫描。同时，优化器还会对各种查询操作的代价进行估算，以便找到最优的执行计划。
通常我们执行计划explain，来查看索引是否失效，以及这条sql语句的好坏
下面情况会造成索引失效：

• 违反了最左前缀法则

在联合索引中的索引顺序为 name，age，habby。如果我们在sql语句的条件中是age，habby没有满足最左边，就会导致索引失效。

• 如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效。
• 进行运算 或者函数操作也会导致索引失效
• %开头的模糊查询
• 隐式转换 比如需要int却给了char
• or两边字段都是索引字段才会走索引。
• 数据量太少了，没必要走索引

sql优化

• 表设计的优化

比如设置合适的数值（tinyint int bigint），要根据实际情况选择
比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低。

• sql语句的优化

• 避免索引的失效
• 避免使用select可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。 此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。 还有一个最重要的问题是：select不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。
• 用小表驱动大表 也就是先执行小表 再根据小表的条件去大表中查询
• 多用limit 限制查的数量
• 使用inner join比left或者right好，因为inner引擎会选择最小的那张表计算
• 索引优化
• 用链接查询去替代子查询，因为子查询需要创建临时表
• 不要用过多的join
  性能问题：每个 join 操作都需要对两个或多个表进行连接操作，这个操作需要消耗大量的计算资源和时间，如果 join 操作过多，会导致 SQL 的执行效率降低，从而影响整个系统的性能。
  可读性和维护性问题：join 操作会使 SQL 语句变得复杂，难以理解和维护，特别是当 join 操作涉及到多个表的时候，SQL 语句的复杂度会呈现指数级增长，给代码的可读性和可维护性带来挑战。

• 分库分表
• 读写分离
  指路15种优化方式http://t.csdnimg.cn/lbqWD

八、INNODB和MYISAM

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB支持行锁，而 MyISAM 支持表级锁
6. 性能上存在差异：MyISAM 的读取速度比 InnoDB 快，但是在高并发环境下，InnoDB 的性能更好。这是因为 InnoDB 支持行级锁和事务处理，而 MyISAM 不支持。
7. 数据安全不同：InnoDB 支持崩溃恢复和数据恢复，而 MyISAM 不支持。如果 MySQL 崩溃了或者发生意外故障，InnoDB 可以通过恢复日志来恢复数据。
8. https://www.jb51.net/article/282784.htm

mysql的缓存

最近在准备redis的时候，突然看到了mysql的缓存，感觉面试会提到这个，而且也是一个亮点，所以把它补上。

一个示例

在使用mysql查询的时候，大量数据下（这里我刚好最近有一个3000万的数据表）
第一次查询 花费了三分半

第二次查询时 发现竟然只花费了3秒多

后面还增加了几百万数据，但是第一次的查询时应该是我的老师，我再查询时已经不是第一次查询了，所以上面使用的是我增加数据之前的截图，但是这里并不影响我们想要体现的效果。

为什么变快？

这是因为数据库引入了查询缓存(Query Cache)的功能。
mysql query cache 在mysql中是默认打开的。
ps：在mysql8.0版本中，查询缓存已经被移除，在mysql之前的版本缓存查询是存在的。大家肯定会疑惑，缓存提高查询效率不是很好吗？为什么要移除查询缓存呢？在后面我们会说到查询缓存的缺点。

为什么第一次查询的是别人的会话，但是我们的会话查询时也可以使用缓存？

这是因为mysql的缓存不是在会话里，而是在mysql的服务端。

为什么保存在服务端呢？

mysql的缓存机制并非与每个单独的客户端连接会话相关联，而是由mysql服务端同意管理和维护。
这就意味着无论有多少个客户端连接到mysql服务器执行查询操作，缓存的管理和存储都是集中在服务端进行的。
这样设计的好处有以下几点：

• 资源共享和效率：将缓存集中在服务端可以使多个会话能够共享相同的缓存结果。如果每个会话都有自己独立的缓存，肯呢个会导致相同的查询在不同的会话中被重读执行和计算，浪费系统资源。集中式的换粗可以让一次查询的结果被所有会话复用，提高了缓存的利用率和系统的整体性能。
• 一致性和准确性：由服务端统一管理缓存能够更好的保证缓存数据的一致性和缓存性。服务端可以更有效的处理缓存的更新，失效，驱逐等操作，确保缓存中的数据始终都是最新的有效的。
  例如，假设多个客户端都执行了相同的复杂查询语句获取用户信息。缓存是在服务端统一管理的，当第一个客户端执行该查询并将结果缓存后，后续的客户端执行相同查询时，服务端可以直接从缓存中返回结果，无需再次执行查询操作，从而提高了响应速度和系统效率。

什么是Mysql查询缓存

查询缓存是为了提高完全相同的query语句的响应速度，mysql server会对查询语句进行hash计算后，把得到的值与query查询的结果集对应存放在query cache中。
当mysql开启query cache之后，mysql会对每一个select语句通过特定的select语句通过特定的hash算法算出该query的hash值，然后通过这个hash值去querycache去匹配。

• 如果没有匹配，将这个hash值存放在一个hash链表中，并将Query的结果集存放到cache中，存放hash值链表的每个hash节点存放了相应Quey结果集在cache中的地址，以及该query所涉及到一些table相关信息；
• 如果通过hash值匹配到了一样的Query，则直接将cache中相应的Query结果集返回给客户端。

缓存流程

服务器接收Sql，以Sql和一些其他条件作为key查询缓存表
如果缓存命中，直接返回缓存
如果缓存没有命中，则执行sql查询，包括sql解析，优化等
执行完sql查询结果之后，将sql查询结果写入缓存表

缓存规则 什么时候不会缓存？

• 通过将sql文本进行hash计算，hash值作为key，结果集作为value，进行存储。当运行相同的sql，服务端直接从缓存中取到结果，而不需要再去解析和执行SQL。进而提升查询性能。
• 查询必须是完全相同(逐字节相同)才能够被认为是相同的（大小写也要区分）。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同
• 默认字符集的查询被认为是不同的查询并且分别进行缓存。 查询语句中有一些不确定数据时，不会缓存，如now()，current_time()等
• 若查询中包含用户自定义函数，存储函数，用户变量，临时表，mysql库中系统表，或者任何包含权限的表，一般都不会缓存

缓存失效

在表结构或者数据发生改变时，查询缓存中的数据不再有效，查询缓存的相关条目也被清空
Insert Update Delete Truncate alter drop table或者drop database将导致缓存失效

缓存的缺点

缓存会带来额外开销，因为：

• 读查询在开始之前必须先检查是否命中缓存；
• 若某个读查询可以被缓存且未被缓存，那么当完成执行后，MySQL会将其结果存入查询缓存；
• 对写操作也有影响，因为当写入数据时，MySQL必须将对应表的所有缓存都设置失效，这在缓存内存较大时将导致很大的系统消耗；
• 故查询缓存并非必需，其效率取决于全部查询中开销较大的查询是否能被缓存命中；

杂

Mysql索引的优点和缺点？

Mysql里面的索引的优点有很多
通过B+树的结构来存储数据，可以大大减少数据检索时的磁盘IO次数，从而提升数据查询的性能
B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。
通过唯一索引约束，可以保证数据表中每一行数据的唯一性
当然，索引的不合理使用，也会有带来很多的缺点。
数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。
一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。
创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低。

binlog和redolog有什么区别？

binlog和redolog都是Mysql里面用来记录数据库数据变更操作的日志。
其中binlog主要用来做数据备份、数据恢复和数据同步，大家初步接触这个概念 ，应该是在Mysql的主从数据同步的场景中，master节点的数据变更，会写入到binlog中，然后再把binlog中的数据通过网络传输给slave节点，实现数据同步。

问题答案
binlog和redolog的区别有很多，我可以简单总结三个点

使用场景不同，binlog主要用来做数据备份、数据恢复、以及主从集群的数据同步； Redo Log主要用来实现Mysql数据库的事务恢复，保证事务的ACID特性。当数据库出现崩溃的时候，Redo Log可以把未提交的事务回滚，把已提交的事务进行持久化，从而保证数据的一致性和持久性。
记录的信息不同，binlog是记录数据库的逻辑变化，它提供了三种日志格式分别是statement，row以及mixed；
redo log记录的是物理变化，也就是数据页的变化结果。

记录的时机不同， binlog是在执行SQL语句的时候，在主线程中生成逻辑变化写入到磁盘中，所以它是语句级别的记录方式； RedoLog是在InnoDB存储引擎层面的操作，它是在Mysql后台线程中生成并写入到磁盘中的，所以它是事务级别的记录方式，一个事务操作完成以后才会被写入到redo log中。
https://blog.csdn.net/weixin_46781683/article/details/130992651