Mysql面试题总结

SQL基础知识总结

候选码、主码、全码、外码、主属性、主键、主关键字、非主属性

• 码=超键：能够唯一标识一条记录的属性或属性集。
• 候选键=候选码：能够唯一标识一条记录的最小属性集
• 主键=主码：某个能够唯一标识一条记录的最小属性集（是从候选码里人为挑选的一条）
• 主属性 包含在任一候选码中的属性称主属性。简单来说，主属性是候选码所有属性的并集
• 非主属性 不包含在候选码中的属性称为非主属性。 非主属性是相对于主属性来定义的。
• 外键（foreign key）：子数据表中出现的父数据表的主键，称为子数据表的外键。
• 全码：当所有的属性共同构成一个候选码时，这时该候选码为全码。
• 代理键：当不适合用任何一个候选键作为主键时（如数据太长等），添加一个没有实际意义的键作为主键，这个键就是代理键。
• 自然键：自然生活中唯一能够标识一条记录的键

三大范式和BC范式

• 第一范式1NF
  确保数据库表字段的原子性。即一个属性不能有几个值
• 第二范式
  首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
• 第三范式
  首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
• BC范式
  在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合第三范式。
  （1）所有非主属性对每一个码都是完全函数依赖；
  （2）所有的主属性对于每一个不包含它的码，也是完全函数依赖；
  （3）没有任何属性完全函数依赖于非码的任意一个组合。

Mysql八股文总结

引擎和存储

InnoDB和MyISAM的区别

项目	MyISAM	InnoDB
构成	每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。	基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
事务处理上	MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持	InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能
Select,Update,Insert,delete操作	如果执行大量的SELECT，MyISAM是更好的选择	1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表 2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
Auto_Increment的操作	每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置。 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。 更好和更快的auto_increment处理	如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上
表的具体行数	select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的	InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁	表锁	提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”
索引	不支持聚簇索引	支持聚簇索引

myisam和innodb应该如何选择

一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
三、InnoDB支持外键，MyISAM不支持
四、MyISAM是默认引擎，InnoDB需要指定
五、InnoDB不支持FULLTEXT类型的索引
六、InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’

参考资料：https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html

InnoDB的四大特性

1. 插入缓存
   索引数据存储在磁盘上，主键索引由于天然自增，无须磁盘的随机 I/O，只需不断追加即可。但普通索引大概率无序，默认情况下需要进行随机磁盘 I/O 操作，效率极差。
   为了解决普通索引插入效率低下的问题，InnoDB 存储引擎引入 Insert Buffer 的概念，对于普通索引（非聚集索引）不是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓存池中，如果在直接插入，否则先放入 Insert buffer 对象中，然后以一定频率和辅助索引页子节点进行合并操作，此时通常能将多个插入合并到一个操作中，提高插入性能
2. 二次写
   InnoDB 索引页一般 16KB 大小，而操作系统写文件以 4KB 为单位，这就导致同一页需要分四块分别写入。此时就存在写完一块系统崩溃或者断电等特殊情况，此时就导致写入数据不完整的问题
   二次写就是为了解决该问题，double write 分为两部分，一部分 doublewrite buffer，其大小 2MB，另一部分是磁盘上共享表空间中连续的 128 个页
   每次刷盘时这样处理：先将脏数据写入 doublewrite buffer，doublewrite buffer 每次 1MB 写入共享表空间的磁盘上，完成以上两步后调用 fsync 函数，将数据同步到各个表空间
   如果操作系统在将页写入磁盘的过程中崩溃，InnoDB 重启发现页数据损坏后，可以从共享表的 doublewrite 中找到副本，用于数据恢复
3. 自适应哈希索引
   如果发现某个索引频繁被访问，则建立哈希索引。InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。
4. 预读
   当 InnoDB 预计某些页很快就要被访问时，会异步加载对应页数据到缓冲池。该思路就类似空间局部性：如果某块内存被访问，那么它周围的内存大概率也会被访问。
   InnoDB 采用两种预读算法提高 I/O 性能：线性预读 和 随机预读
   线性预读：以块为单位，一块等于64页。如果某一块中的被顺序读取的页数超过预定值，则 InnoDB 将会异步的将下一块读取到 buffer pool 中
   随机预读：以页为单位，当某一个块中的一些页在 buffer pool 中被发现时，InnoDB 会将该块中的剩余页一并读到 buffer pool 中，目前已废弃。
   参考资料：https://blog.csdn.net/m0_57015193/article/details/119877915

InnoDb为何推荐使用自增主键

自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

存储结构

什么是InnoDb的页、段、区

1. 页
   首先，InnoDB 将物理磁盘划分为页（page），每页的大小默认为 16 KB，页是最小的存储单位。页根据上层应用的需要，如索引、日志等，分为很多的格式。我们主要说数据页，也就是存储实际数据的页。
2. 区
   为了避免过多页对存储空间分配和回收产生的不良影响，引入了区（Extent）的概念。一个区默认是64个连续的页组成（也就是1MB）。通过Extent对存储空间的分配和回收就比较容易了。
3. 段
   B+ 树的叶子节点存放的是我们的具体数据，非叶子结点是索引页。所以 B+ 树将数据分为了两部分，叶子节点部分和非叶子节点部分，也就我们要介绍的段 Segment，也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。Segment 是一种逻辑上的组织。

页的构成

• File Header
  用于描述数据页的外部信息，比如属于哪一个表空间、前后页的页号等。
• Page Header
  用来描述数据页中的具体信息，比如存在多少条纪录，第一条纪录的位置等。
• infimum 和 supremum 纪录
  infimum 和 supremum 是系统生成的纪录，分别为最小和最大纪录值，infimum 的下一条是用户纪录中键值最小的纪录，supremum 的上一条是用户纪录中键值最大的纪录，通过 next_record 字段来相连。
• User Records
  用户纪录，也就是数据库表中对应的数据，这里我们说常用的 Compact 格式。
  
• Free Space
  页中目前空闲的存储，可以插入纪录。
• Page Dictionary
  类似于字典的目录结构，根据主键大小，每隔 4-8 个纪录设置一个槽，用来纪录其位置，当根据主键查找数据时，首先一步到位找到数据所在的槽，然后在槽中线性搜素。这种方法比从前到后遍历页的链表的效率更快。
• Page Tailer
  File Header存储刷盘前内存的校验和，Page Tailer储存刷盘后的校验和。当刷盘的时候，出现异常，Page Tailer和File Header中的校验和不一致，则说明出现刷盘错误。

页中插入记录的过程

1. 如果剩余空间足够，直接分配空间。并将插入前最后一条纪录的 next_record 指向当前插入的纪录，将当前插入纪录的 next_record 指向 supremum 纪录。
2. 如果剩余空间不够，则将之前删除的碎片重新整理之后，按照上述步骤插入记录。
3. 如果还不够，则重新申请一个页，插入记录。

事务

ACID

• 原子性 atomicity： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
• 一致性 consistency： 一致性是指事务在开始之前和执行结束之后，数据库的状态都是合法的，保证了数据库约束不变(WIKI原文是：Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct. Referential integrity guarantees the primary key – foreign key relationship.）
• 隔离性 isolation： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
• 持久性 durability： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

ACID实现原理

• 原子性：实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。通过undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
• 持久性：Mysql使用了Buffer pool的缓存机制，为了避免由于各种原因，Buffer Pool的数据没有刷新到磁盘（刷脏），引入了redo机制：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
  既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
  （1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
  （2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少
• 隔离性：隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明)，那么隔离性的探讨，主要可以分为两个方面：1. (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性；2.(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性。
• 一致性：保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证

参考资料：https://www.cnblogs.com/kismetv/p/10331633.html

什么是脏读、幻读和不可重复度

脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

幻读：一个事务中两次读取的数据量不一致。 例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务的隔离级别

• 未提交读
  事务中的修改，即使没有提交，其他事务也可以看得到，比如说上面的两步这种现象就叫做脏读，这种隔离级别会引起很多问题，如无必要，不要随便使用
• 提交读
  这种隔离级别就是一个事务的开始，只能看到已经完成的事务的结果，正在执行的，是无法被其他事务看到的。这种级别会出现读取旧数据的现象，而这个旧数据可能是无效的。
• 可重复读
  REPEATABLE READ解决了脏读的问题，该级别保证了每行的记录的结果是一致的，也就是上面说的读了旧数据的问题，但是却无法解决另一个问题，幻行，顾名思义就是突然蹦出来的行数据。指的就是某个事务在读取某个范围的数据，但是另一个事务又向这个范围的数据去插入数据，导致多次读取的时候，数据的行数不一致。
• 可串行化
  SERIALIZABLE是最高的隔离级别，它通过强制事务串行执行（注意是串行），避免了前面的幻读情况，由于他大量加上锁，导致大量的请求超时，因此性能会比较底下，再特别需要数据一致性且并发量不需要那么大的时候才可能考虑这个隔离级别

串行化的隔离级别最高，读未提交的级别最低，级别越高，则执行效率就越低，所以在选择隔离级别时应该结合实际情况。
MySQL 支持以上四种隔离级别，默认为 Repeatable read (可重复读)；而 Oracle 只支持 Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。

参考资料：https://www.cnblogs.com/s-b-b/p/5845096.html

锁

锁分类

从锁粒度来分，分为：行锁，页锁和表锁。
从使用性质来分：共享锁，排他锁，更新锁。

• 共享锁（Share Lock）：S 锁，又称读锁，用于所有的只读数据操作。
  S 锁并非独占，允许多个并发事务对同一资源加锁，但加 S 锁的同时不允许加 X 锁，即资源不能被修改。S 锁通常读取结束后立即释放，无需等待事务结束。
• 排他锁（Exclusive Lock）：X 锁，又称写锁，表示对数据进行写操作。
  X 锁仅允许一个事务对同一资源加锁，且直到事务结束才释放，其他任何事务必须等到 X 锁被释放才能对该页进行访问。
  使用 select * from table_name for update; 语句产生 X 锁。
• 更新锁：U 锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。
  当被读取的页将要被更新时，则升级为 X 锁，U 锁一直到事务结束时才能被释放。故 U 锁用来避免使用共享锁造成的死锁现象。
  从主观上划分：乐观锁（版本号）和悲观锁（每次操作都加锁）。

锁和隔离级别的关系

• 读未提交：读数据不需要加共享锁
• 读已提交：读操作需要加共享锁，但是在语句执行完以后释放共享锁
• 可重复读：读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；
• 可重复读：限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

InnoDB中的行锁算法

• Record Lock（记录锁）: 单个记录上的锁
• Gap Lock（间隙锁) : 锁定一个范围，但不包括记录本上，左开右开区间
• Next-Key Lock（临键锁）: 锁定一个范围，并且锁定记录本身 ，左开右闭区间
  
• Record Lock 记录锁，基于唯一索引
  记录锁就是为某行记录加锁，事实上，它封锁的是该行的索引记录。如果表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用 “隐式的主键” 来进行锁定。
  所谓隐式的主键就是指：如果在建表的时候没有指定主键，InnoDB 存储引擎会将第一列非空的列作为主键;如果没有的话会自动生成一列为 6 字节的主键。
  那么，既然 Record Lock 是基于索引的，那如果我们的 SQL 语句中的条件导致索引失效(比如使用 or) 或者说条件根本就不涉及索引或者主键，行级锁就将退化为表锁。
• Gap Lock，基于非唯一索引

select * from test where id between 1 and 10 for update; 

对于上述 SQL 语句，所有在(1，10)区间内(左开右开)的记录行都会被 Gap Lock 锁住，所有 id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条被操作的索引记录并不会被锁住。
注意!这里指的是锁住所有的(1，10)区间内的 id，也就是说即使某个 id 目前并不在我们的表中比如 id = 6 ，如果你想插入一条 id = 6 的新纪录，那对不起，不行。

• Next-key Lock，基于非唯一索引
  Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法，其主要目的是为了解决幻读问题。
  在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的算法就是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。
  由于主键也是一种唯一索引，所以我们可以这么说：Record Lock 是基于唯一索引的，而 Next-Key Lock 是基于非唯一索引的。
  需要注意的，当操作的索引为非唯一索引时，InnoDB 会先用 Record Lock 锁住对应的唯一索引，再用 Next-Key Lock 和 Gap Lock 对这个非唯一索引进行处理，而不仅仅是锁住这个非唯一索引。

参考资料：https://blog.csdn.net/bbj12345678/article/details/120797923
https://www.51cto.com/article/676123.html

什么是当前读和快照读

什么是MVCC

MVCC（Multi-Version Concurrency Control）：多版本并发控制，是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MVCC的工作原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

• 三个隐藏字段
  DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID 是当前操作该记录的事务 ID ,而 DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本
• undo log
  不同事物或者相同事务对同一个记录的修改，会导致该记录的 undo log 成为一条版本记录链。undo log 的链首就是最新的旧记录，尾部就是最旧的记录。
• read view
  Read View 就是事务进行快照读操作的时候生产的读视图，在当前事务执行快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID（当每个事务开启时，都会被分配一个ID，这个ID是默认递增的，所以事务越新，ID越大）。可以通过Read View进行可见性判断，即当我们某个事物执行快照读的时候，对读取的该记录创建一个 Read View 视图，把它当作条件，用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据（也就是该快照），也可能是该行记录的 undo log 日志里的某个版本的数据。

参考资料：https://baijiahao.baidu.com/s?id=1719749867568105953&wfr=spider&for=pc
https://blog.csdn.net/SIESTA030/article/details/123113437

索引

索引结构

为什么使用树，而不是哈希表作为索引

Hash不支持区间快速搜索。

B+树和B树的区别

• B+树的叶子节点用链表来串联。 查找某个区间的数据，只需要用区间的起始值，在树中进行查找，当定位到有序链表中的某个节点之后，再从这个节点开始顺着有序链表往后遍历，直到有序链表中的节点数据值大于区间终止值为止。

• B+树中的任何节点都不存储真实数据，只是用来索引。 B树直接通过叶子节点获取到数据；而B+树每个叶子节点存储数据行的键值和地址信息，当查询到某个叶子节点时，通过叶子节点的地址找到真实的数据信息。

为什么使用B+树，而不是B树

• B+ 树减少了 IO 次数。
  由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
• B+ 树查询效率更稳定
  由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。
• B+ 树更加适合范围查找
  B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

为什么使用B+树，而不是二叉搜索树

• 二叉搜索树深度高
• B+更适合范围查找

索引的分类

从物理结构上可以分为聚集索引和非聚集索引两类：

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。聚簇索引由于数据是紧密相连的，数据库不用从多个数据块中提取数据，从而节省了大量的io操作。为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。
• 非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。
  如图：
  

从应用上可以划分为以下几类：

• 普通索引（单值索引）：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
• 主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；
• 组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；
• 全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

什么是最左前缀原则

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。
如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。
再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。

最左匹配的原理

上图可以看出 a 是有顺序的（1、1、2、2、3、3），而 b 的值是没有顺序的（1、2、1、4、1、2）。所以 b = 2 这种查询条件无法利用索引。
同时我们还可以发现在 a 值相等的情况下（a = 1），b 又是顺序排列的，所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

创建索引的优点和缺点

优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

• 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

参考资料：https://www.jb51.net/article/239235.htm

什么是覆盖索引

• select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
• 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
• 是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

什么是索引下推

索引下推（Index condition pushdown） 简称 ICP，在 Mysql 5.6 版本上推出的一项用于优化查询的技术。
在不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器判断数据是否符合条件。
而有了索引下推之后，如果存在某些被索引列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

索引失效的情况

1. 查询条件中带有or,除非所有的查询条件都建有索引，否则索引失效
2. like查询是以%开头
3. 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引
4. 索引列上参与计算会导致索引失效
5. 违背最左匹配原则
6. 如果mysql估计全表扫描要比使用索引要快，会不适用索引
   参考资料：https://blog.csdn.net/sy_white/article/details/122112440

什么是回表

回表主要是指，在非主键索引中，找到记录后先得到主键的值，在根据主键的值来找到记录，访问了两棵B+，这个过程就是回表。
当使用覆盖索引时，即使非主键值，也不会产生回表（所以尽量避免使用select *）

存储过程

什么是存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

日志

什么是物理日志和逻辑日志

物理日志和逻辑日志在存储内容上有很大区别，存储内容是区分它们的最重要手段。

• 物理日志：
  存储数据库中特定记录的变更，通常是 page oriented，即描述具体某一个 page 的修改操作；
• 逻辑日志：
  存储事务中的一个操作；

mysql中的常见日志

1. redo log： 物理日志
   作用是确保事务的持久性。 redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已提交事务数据。
2. undo log：逻辑日志
   作用是保证数据的原子性。 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
3. binlog：逻辑日志
   常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。
4. errorlog：错误日志
   记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
5. general query log：普通查询日志
   记录了服务器接收到的每一个命令，无论命令语句是否正确，因此会带来不小开销，所以也是默认关闭的。
6. slow query log：慢查询日志
   记录执行时间过长和没有使用索引的查询语句（默认 10s），同时只会记录执行成功的语句。
7. relay log：中继日志
   在从节点中存储接收到的 binlog 日志内容，用于主从同步。

集群

主从复制

什么是主从复制

主从复制是用来建立一个与主数据库完全一样的数据库环境，即从数据库。主数据库一般是准实时的业务数据库。

主从复制的作用

读写分离，使数据库能支撑更大的并发。
高可用，做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

主从复制的架构

• 一主一从或一主多从
  在主库的请求压力非常大时，可通过配置一主多从复制架构实现读写分离，把大量对实时性要求不是很高的请求通过负载均衡分发到多个从库上去读取数据，降低主库的读取压力。而且在主库出现宕机时，可将一个从库切换为主库继续提供服务。
  

• 主主复制
  双主复制架构适用于需要进行主从切换的场景。 两个数据库互为主从，当主库宕机恢复后，由于它还是原来从库（现在主库）的从机，所以它还是会复制新的主库上的数据。那么无论主库的角色怎么切换，原来的主库都不会脱离复制环境。
  
  通过双主复制架构能够大大减轻一主多从架构下对主库进行维护带来的额外搭建从库的工作。
  当然双主架构还能和主从复制联合起来使用：在Master2库下配置从库Slave1、Slave2等，这样既可通过从库Slave1等来分担读取压力，同时在DBA做维护的同时，避免了重建从库的额外工作，但需要注意从库的复制延迟。MySQL双主多级复制架构如下所示。
  

• 多主一从（5.7 开始支持）

• 联级复制
  因为每个从库在主库上都会有一个独立的 Binlog Dump 线程来推送 binlog 日志，所以随着从库数量的增加，主库的 IO 压力和网络压力也会随之增加，这时，联级复制架构应运而生。
  联级复制架构只是在一主多从的基础上，再主库和各个从库之间增加了一个二级主库 Master2，这个二级主库仅仅用来将一级主库推送给它的 Binlog 日志再推送给各个从库，以此来减轻一级主库的推送压力。
  
  多级复制解决了一主多从场景下，主库的I/O负载和网络压力，当然也有缺点：MySQL的传统复制是异步的，多级复制场景下主库的数据是经历两次复制才到达从库Slave1、Slave2、Slave3的，期间的延迟要比一主多从复制场景下只经历一次复制的还大。
  参考资料：https://blog.csdn.net/m0_67698950/article/details/124042358

主从复制的原理

数据库有个 binlog 二进制文件，记录了数据可执行的所有 SQL 语句。主从同步的目标就是把主数据库的 binlog 文件中的 SQL 语句复制到从数据库，让其在从数据的 relaylog 文件中再执行一次这些 SQL 语句即可。
具体实现需要三个线程：

• binlog 输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送 binlog内容到从库。
  在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
• 从库 IO 线程：当 START SLAVE 语句在从库开始执行之后，从库创建一个 IO 线程，该线程连接到主库并请求主库发送 binlog 里面的更新记录到从库上。从库 IO 线程读取主库的 binlog 输出线程发送的更新并拷贝这些更新到本地文件，其中包括 relaylog 文件。
• 从库 SQL 线程：从库创建一个 SQL 线程，这个线程读取从库 IO 线程写到 relaylog 的更新事件并执行。

什么是异步复制和半复制

MySQL 的主从复制有两种复制方式，分别是异步复制和半同步复制：

• 异步复制
  MySQL 默认的主从复制方式就是异步复制，因为 Master 根本不考虑数据是否达到了 Slave，或 Slave 是否成功执行。
  如过需要实现完全同步方式，即 Master 需要等待一个或所有 Slave 执行成功后才响应成功，那集群效率可想而知。故 MySQL 5.6 之后出现了一种折中的方式——半同步。
• 半同步复制
  一主一从，一主多从情况下，Master 节点只要确认至少有一个 Slave 接受到了事务，即可向发起请求的客户端返回执行成功的操作。同时 Master 是不需要等待 Slave 成功执行完这个事务，Slave 节点接受到这个事务，并成功写入到本地 relay 日志中就算成功。
  另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那 MySQL 会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。
  半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。

JDBC八股文总结

数据库连接池

什么是数据库连接池

数据库连接池(connection pool)是程序启动时建立多个数据库连接，并将这些连接组成一个连接池，统一进行管理，由程序动态地对池中的链接进行申请，使用，释放。

为什么使用数据库连接池

1. 资源复用
   数据库连接得到复用，避免了频繁的创建、释放连接带来的性能开销，减少内存碎片。
2. 更快的系统相应速度
   数据库连接池初始化时，已经创建多个数据库连接置于池中备用。此时连接的初始化工作已经完成。对于业务请求而言，直接利用连接池中的可用连接，避免了数据库连接的创建和释放，从而缩减了系统整体响应时间。
3. 统一的连接管理，避免数据库连接泄露
   数据库连接由连接池统一管理，最后由连接池统一对连接进行析构，防止有连接没有析构，导致资源泄露
4. 避免多个线程同时使用一个连接
   数据库连接并不是线程安全的，连接池可以防止多个线程同时使用一个连接。

数据库连接池的使用机制

• 从连接池获取或创建可用连接；
• 使用完毕后，把连接返回给连接池；
• 在系统关闭前，断开所有连接并释放连接占用的系统资源。
  

常见数据库连接池

常用的主流开源数据库连接池有C3P0、DBCP、Tomcat Jdbc Pool、BoneCP、Druid等

C3p0: 开源的JDBC连接池，实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate、Spring等。单线程，性能较差，适用于小型系统，代码600KB左右。
DBCP (Database Connection Pool):由Apache开发的一个Java数据库连接池项目， Jakarta commons-pool对象池机制，Tomcat使用的连接池组件就是DBCP。单独使用dbcp需要3个包：common-dbcp.jar,common-pool.jar,common-collections.jar，预先将数据库连接放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完再放回。单线程，并发量低，性能不好，适用于小型系统。
Tomcat Jdbc Pool：Tomcat在7.0以前都是使用common-dbcp做为连接池组件，但是dbcp是单线程，为保证线程安全会锁整个连接池，性能较差，dbcp有超过60个类，也相对复杂。Tomcat从7.0开始引入了新增连接池模块叫做Tomcat jdbc pool，基于Tomcat JULI，使用Tomcat日志框架，完全兼容dbcp，通过异步方式获取连接，支持高并发应用环境，超级简单核心文件只有8个，支持JMX，支持XA Connection。
BoneCP：官方说法BoneCP是一个高效、免费、开源的Java数据库连接池实现库。设计初衷就是为了提高数据库连接池性能，根据某些测试数据显示，BoneCP的速度是最快的，要比当时第二快速的连接池快25倍左右，完美集成到一些持久化产品如Hibernate和DataNucleus中。BoneCP特色：高度可扩展，快速；连接状态切换的回调机制；允许直接访问连接；自动化重置能力；JMX支持；懒加载能力；支持XML和属性文件配置方式；较好的Java代码组织，100%单元测试分支代码覆盖率；代码40KB左右。
Druid：Druid是Java语言中最好的数据库连接池，Druid能够提供强大的监控和扩展功能，是一个可用于大数据实时查询和分析的高容错、高性能的开源分布式系统，尤其是当发生代码部署、机器故障以及其他产品系统遇到宕机等情况时，Druid仍能够保持100%正常运行。主要特色：为分析监控设计；快速的交互式查询；高可用；可扩展；Druid是一个开源项目，源码托管在github上。

参考资料：
https://blog.csdn.net/weixin_45325628/article/details/122930369
https://blog.csdn.net/adminpd/article/details/122910606