最全MySQL面试题整理

最全MySQL面试题整理

一、事务

1. 说一说事务

事务指的是满足ACID特性的一组操作，可以使用commit提交一个事务，也可以使用Rollback进行回滚

2. 什么是事务的ACID属性

• 原子性(Atomicity)：事务被士为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚
  回滚可以通过回滚日志实现，回滚日志记录着事务所执行的修改操作，在回滚的时候反向执行这些操作即可
• 一致性(Consistency)：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的
• 隔离性(Isolation)：一个事务所做的修改在最终提交之前，对其他事务是不可见的
• 持久性(Durability)：一旦事务提交，它所做的修改将会永久保存到数据库中，即使系统发生崩溃，事务执行的结果也不会丢失
  可以使用重做日志来保证持久性

事务的ACID属性不是一种平级关系：

• 只有满足一致性，事务的执行结果才是正确的
• 在无并发的情况下，事务串行执行，隔离性一定能够满足，此时只要满足原子性，就一定能满足一致性
• 在并发的情况下，事务并发执行，事务不仅要满足原子性，还要满足隔离性，才能满足一致性
• 事务满足持久性是为了应对数据库崩溃的情况

AUTOCOMMIT：MySQL默认采用自动提交模式，也就是说，如果不显式使用START TRANSACTION语句来开始一个事务，那么每个查询都会被当作一个事务自动提交

3. 说一说并发一致性问题

在并发环境下，事务的隔离性很难保证，就会出现很多并发一致性问题

• 丢失修改：T1和T2两个事务都对一个数据进行了修改，T1先修改，T2后修改，T2的修改覆盖了T1的修改
• 读脏数据：T1修改一个数据，T2随后又读取这个数据，如果T1撤销了这次修改，那么T2读取的就是脏数据
• 不可重复读：T2读取了一个数据，T1对该数据做了修改，如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同
• 幻影读：T1读取某个范围的数据，T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和第一次读取的结果不同

产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题

二、封锁

1. 封锁粒度

• MySQL中提供了两种封锁粒度：行级锁和表级锁
• 应该尽量只锁定需要修改的那部分数据，而不是所有的资源，锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、检查锁状态）都会增加系统开销，因此封锁粒度越小，系统开销就越大。在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡

2. 封锁类型

• 读写锁
  • 排他锁（Exclusive），简写为X锁，又称写锁
  • 共享锁（Shared），简写为S锁，又称读锁
  • 一个事务对数据对象A加了X锁，就可以对A进行读取和更新，加锁期间其他事务不能对A加任何锁
  • 一个事务对数据对象A加了S锁，就可以对A就行读取操作，但是不饿能进行更新操作，加锁期间其他事务能对A加S锁，不能加X锁

	X	S
X	×	×
S	×	✓

• 意向锁
  使用意向锁可以更容易地支持多粒度封锁
  在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检测是否有其他事务对表A或者表A中的任意一行加了锁，那么就需要对表A的每一行都检测一次，这是非常耗时的
  意向锁在原来的X/S锁上引入了IX/IS锁，IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁，有以下两个规定：
  • 一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
  • 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁

通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其他事务对表A加了X/IX/S/IS锁，如果加了就表示有其他事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败

	X	IX	S	IS
X	×	×	×	×
IX	×	✓	×	✓
S	×	×	✓	✓
IS	×	✓	✓	✓

3. 封锁协议

• 三级封锁协议，隔离级别与锁的关系

  • 一级封锁协议:事务T要修改数据A时必须加X锁,直到T结束才释放锁
  • 二级封锁协议:在一级封锁协议的基础上,要求读取数据A时必须加S锁,读取完马上释放S锁
  • 三级封锁协议:在二级封锁协议的基础上,要求读取数据A时必须加S锁,直到事务结束才释放S锁

• 两段锁协议

加锁和解锁分为两个阶段进行,事务遵循两段锁协议是保证可串行化调度的充分条件,可串行化调度是指通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同

• MySQL隐式与显式锁定

MySQL的InnoDB数据库引擎采用两段锁协议,会根据隔离级别在需要的时候自动加锁,并且所有的锁都是在同一时间被释放,这被称为隐式锁定,InnoDB也可以使用特定的语句进行显示锁定

三、隔离级别

1. 事务的隔离级别有哪些

• 读未提交(READ UNCOMMITTED)：事务所做的修改，即使没有提交，对其他事务也是可见的
• 读已提交(READ COMMITTED)：事务所做的修改，在提交之前对其他事务是不可见的
• 可重复读(REPEATABLE READ)：保证在同一个事务中多次读取同样数据的结果是一样的。MySQL的默认隔离级别
• 可串行化(SERIALIZABLE)：事务串行执行

隔离级别	丢失修改	读脏数据	不可重复读	幻读
读未提交	×	✓	✓	✓
读已提交	×	×	✓	✓
可重复读	×	×	×	✓
可串行化	×	×	×	×

四、多版本并发控制

1. 说一说多版本并发控制

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL的InnoDB数据库引擎实现事务隔离级别的一种方式，可以用于实现读已提交和可重复读两种隔离级别。在早期的数据库中，只有读读操作可以并发执行，读写、写读、写写都要阻塞，在引入MVCC之后，只有写写需要阻塞，其他三种操作都可以并发执行，提高了并发度。InnoDB通过undo log保存每条数据的多个版本，并且能找回历史版本给用户读，每个数据读到的数据版本可能是不一样的。MVCC主要依靠隐藏字段、快照、回滚日志实现

2. 多版本并发控制的原理

• 隐藏字段：InnoDB数据库引擎在每行数据后面添加三个隐藏字段，并不是所谓的创建版本、删除版本，他们分别是最近一次对数据行做修改的事务ID、指向当前记录行undo log信息的回滚指针、随着新行插入而单调递增的行ID。当表没有主键或唯一非空索引的时候，InnoDB使用这个行ID自动产生聚簇索引

• 快照：快照主要用来做可见性判断，里面保存了对当前事务不可见的其他活跃事务。源码里面有几个重要参数，分别是low_limit_id、up_limit_id、trx_ids、creator_trx_id。trx_ids快照创建时其他未提交的活跃事务的ID列表，up_limit_id是trx_ids中最小的事务ID、low_limit_id是下一个被分配的事务ID、cretator_trx_id是当前事务的ID

• 回滚日志：Undo log保存的是过去版本的数据，当事务读取数据行的时候，如果当前数据行不可见，事务会顺着Undo log链找到满足可见性条件的数据行版本。回滚日志分为两类：insert undo log、update undo log。insert undo log在进行insert的时候产生，只在回滚的时候需要，在事务提交后就可以丢弃；update undo log在进行delete和update的时候产生，在回滚和快照读的时候都需要，只有当数据库使用的快照中不涉及该日志记录，才会被purge线程删除

• 可见性比较算法：在InnoDB中，创建一个事务后，执行第一个普通select语句的时候，InnoDB会创建一个快照，快照用来记录当前事务不可见的事务ID列表，当读取数据行的时候，会使用可见性比较算法判断是否满足可见性

3. 当前读和快照读的区别

• 快照读：普通的select语句，快照读读取的是满足可见性的数据，不需要加锁
• 当前读：select … lock in share mode, select … for update, insert, delete, update，当前读读取的是最新的数据，需要加锁，除了select … lock in share mode使用的是S锁，select … for update，insert，delete，update都使用的是X锁

4. MVCC怎么实现RC、RR隔离级别，为什么不能解决修饰修改和幻读问题

• MVCC为每个修改保存一个版本，版本与事务相关联，每个事务都只能看到该事务创建快照之前已提交的修改和该事务本身所作的修改。这样在并发读写数据库的时候，读操作不会阻塞写操作，写操作不会阻塞读操作，提高了性能
• MVCC实现RC和RR两种隔离级别的区别在于，在在RC隔离级别下，每条select语句都会创建一个新的快照；而RR隔离级别下，第一条select语句会创建一个快照，事务之后都是使用这个快照，直到事务结束，这样保证了可重复读
• 因为事务所作的修改在提交后才会在MVCC中被记录，因此在提交之前发生的丢失修改问题MVCC依然不能被解决。MVCC在RR隔离级别下只能保证可重复读和防止部分幻读，不能完全避免幻读，在使用快照读时，可以防止幻读，但是使用当前读时，读取的是最新数据，就会产生幻读
• 因此，InnoDB在实现RR隔离级别的时候，不仅使用了MVCC，还会对当前读语句读取的记录行加临键锁，防止幻读

五、MySQL中各种锁

1. 表级锁和行级锁

• 表级锁：锁定整张表，开销小，加锁快，不会出现死锁，并发度低
• 行级锁：锁定某一行或某几行数据或者是间隙，开销小，加锁慢，会出现死锁，并发度高
• 表锁由MySQL Server实现，行锁由存储引擎实现，InnoDB支持行锁和表锁，默认使用行锁，MyISAM和MEMORY都使用表锁

2. 读写锁与意向锁

略

3. 记录锁、间隙锁、Next-key锁

InnoDB实现了三种行锁的算法：记录锁(Record Lock)、间隙锁(Gap Lock)、Next-key锁(Next-key Lock)。只有在RR隔离级别下才有间隙锁和Next-key锁

• 记录锁：记录锁是对索引的锁定，锁定某个索引后，会阻止其他事务对该索引的插入、删除和更新操作。如果没有设置索引，InnoDB会自动创建隐藏的聚簇索引
• 间隙锁：间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前或最后一个索引之后的间隙。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，间隙可以包含单个或多个记录，甚至没有记录，它能保证两次读取这个范围内的记录不会改变，从而不会出现幻读现象。间隙锁和间隙锁是互不冲突的，间隙锁的唯一作用就是防止其他事务的插入，所以间隙X锁和间隙S锁没有任何区别
• Next-key Lock锁：由记录锁和间隙锁组成，它指的是锁定某个索引以及该索引之前的间隙

4. 插入意向锁

插入意向锁(Insert Intention Lock)：是一种特殊的间隙锁，在insert的时候表示插入的意向。插入意向锁不影响其他事务加其他任何锁，但是插入意向锁会与间隙锁或Next-key锁冲突

5. 自增锁

自增锁(Auto-inc Lock)是一种特殊的表级别锁，专门针对事务插入AUTO_INCREMENT类型的列。事务往表中插入记录时，所有其他事务的插入必须等待，以便第一个事务插入的行是连续的主键值

6. 锁的兼容关系

7. 乐观锁悲观锁

• 乐观锁：不加锁，假定不会出现冲突直接进行操作，一般使用版本号和CAS操作来实现。乐观锁适用于多读场景，写操作比较少的情况
• 悲观锁：加锁，假定会出现冲突，操作时会进行加锁，一般使用封锁机制来实现。悲观锁适用于多写场景，写操作比较多的情况

8. InnoDB中实现了哪些锁

读写锁、意向锁、记录锁、间隙锁、临键锁、插入意向锁、自增锁

9. InnoDB中锁的相关概念

• 普通的select是快照读，不会加锁，使用MVCC，而当前读会加锁，只有通过索引条件检索数据，才会使用行锁，否则会使用表锁，如果使用主键索引，则锁主键索引；如果使用辅助索引，则先锁辅助索引，再锁主键索引
• 在可重复读隔离级别下，InnoDB默认使用next-key锁，当查询的索引是主键或者唯一索引时，才会退化为记录锁，当范围查询或等值查询的记录不存在的时候，临键锁会退化成间隙锁；如果是辅助索引，对辅助索引加Next-key锁，对主键索引加记录锁
• 行锁是对索引加的锁，不是对记录加的锁，所以虽然是访问不同行的记录，如果使用的是相同的索引键，依然会出现锁冲突
• https://www.jianshu.com/p/1bf925f728aa
  https://www.cnblogs.com/huangfuyuan/p/9510022.html
  https://www.cnblogs.com/snow-man/p/10515193.html

10. 什么是死锁，怎么避免死锁

• 死锁指的是两个或两个以上事务在执行过程中，因争夺资源而造成的一种相互等待的现象
• InnoDB有专门的死锁检测机制，当InnoDB发现产生死锁后，会选择两个事务中较小的事务，也就是修改记录条数较少的事务回滚，来解决死锁。当死锁涉及到不止InnoDB存储引擎的时候，InnoDB是无法u检测到死锁的，此时就只能通过指定锁等待超时参数InnoDB_lock_wait_timeout来解决

避免死锁方法：

• 如果不同程序并发存取多个表，尽量约定以相同的顺序访问表，减少产生死锁的概率
• 在同一个事务中，尽可能一次锁定所有需要的资源，减少产生死锁的概率
• 对于非常容易产生死锁的部分，可以尝试使用表级锁，通过表级锁减少产生死锁的概率

11. 什么时候使用表锁

InnoDB中，大部分情况下都应该使用行锁，在某些情况下才应该使用表锁

• 事务需要更新大部分或全部数据，表又比较大，此时使用行锁事务的执行效率低，容易出现锁冲突，可以考虑使用表锁
• 事务涉及多个表的时候，可能引起死锁，造成大量事务回滚，此时可以考虑使用表锁，减少事务回滚带来的开销

12. InnoDB行锁优化

• 尽量让所有的数据检索都通过索引来完成，避免InnoDB因为无法通过索引键加锁而升级为表级锁
• 合理设计索引，尽可能减小锁定范围，避免造成不必要的锁定
• 尽量控制事务的大小，减少锁定的资源量和锁定的时间
• 在业务环境允许的情况下，尽量使用较低级别的事务隔离

六、索引

1. 什么是索引

索引是帮助数据库高效获取数据的数据结构，它包含了一个表中某些列的值以及记录对应的地址，并把这些值存储在一个数据结构中，常见的是Hash索引，BTree索引

2. 索引的优缺点

• 优点：可以减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序速度
• 缺点：创建和维护索引都要消耗时间，对数据进行增删改的时候也要动态维护索引，降低效率

3. 说一说索引的使用场景

• 使用where关键字的时候，可以为某些字段建立索引，提高查询速度
• 使用order by进行排序的时候，数据量很大会使用外部排序，效率很低，此时如果对相应字段建立索引，能够提高效率
• 如果要查询的字段都建立了索引，那么存储引擎会查询索引表，提高效率，这也被称为索引覆盖

4. 说一说索引有哪些类型，索引的分类

• 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键索引
• 唯一索引：数据列不允许重复，允许为NULL，一个表可以有多个唯一索引，ALTER TABLE table_name ADD UNIQUE (column);
• 普通索引：数据列允许重复，允许为NULL，ALTER TABLE table_name ADD INDEX index_name (column);
• 全文索引：用大文本对象的列构建的索引，ALTER TABLE table_name ADD FULLTEXT (column);

5. 索引的数据结构有哪些，Hash索引和B树索引的优劣或区别

二叉查找树与平衡二叉树
BTree与B+Tree
索引原理

MySQL中，主要有四种类型的索引，分别是：BTree索引、Hash索引、Fulltext索引和RTree索引。InnoDB存储引擎支持Hash索引和BTree索引，默认是BTree索引，BTree索引是基于B+树实现的

• Hash索引：在MySQL中，Memory存储引擎默认使用Hash索引。Hash索引将数据保存在哈希表中，查询速度很快，但是Hash索引不支持范围查找和排序等功能
• BTree索引：在MySQL中，InnoDB存储引擎默认使用BTree索引。BTree索引使用B+树实现，B+树所有索引数据都在叶子节点上，每个叶子节点都有指向相邻叶子节点的指针，在查找单条记录的速度上比不上Hash索引，但是更适合于范围查找和排序等操作
• Hash索引在任何时候都避免不了回表查询数据，而B+树在符合某些条件的时候可以只通过索引（聚簇索引、覆盖索引）完成查询
• Hash索引在等值查询上很快，但是性能不稳定，当某个键值大量重复的时候，发生哈希碰撞，效率就会变差

6. B树和B+树

B树又称为平衡多路查找树，它的特点是：

• 每个节点最多有m个分支，而最少的分支数要看是否为根节点，如果是根节点且不是叶子节点，则至少有两个分支，非根非叶节点至少有m/2向上取整个分支
• 有n个分支的节点有n-1个关键字，他们按递增的顺序排列
• 节点内各个关键字互不相等且从小到大排列
• 叶节点都处于同一层，可以用空指针表示，是查找失败的位置

B树可以在内部节点同时存储键和值，因此将频繁访问的数据放在靠近根节点的地方能够提高热点数据的查询效率

B+树是B树的变形，它的特点是：

• 具有n个关键字的节点含有n个分支
• 每个节点的关键字个数n的取值范围是m/2向上取整到m个
• 叶子节点包含信息，并且包含了全部关键字以及指向记录的指针，叶子节点中相邻关键字按大小顺序排列
• 所有非叶子节点仅起到一个索引的作用，只含有对应子树的最大关键字和指向子树的指针，不含有关键字对应记录的存储地址
• 有一个指针指向关键字最小的叶子节点，所有叶子节点连接成一个线性链表

7. 为什么使用B+树而不是B树作为数据库索引，B树和B+树的区别

• B+树更适合外部存储，其非叶节点节点不存放真正的数据，因此一个节点可以存储更多的关键字，每个节点能索引的范围更大更精确，这样I/O的次数就更少，性能更高
• B+树将叶子节点使用链表连接了起来，这样就支持随机检索和顺序检索，也支持区间访问，而B树只支持随机检索
• B+树查询效率更加稳定，任何关键字必须走到叶子节点才能查询到，而在B树中，越靠近根节点查询时间越短
• B+树增删效率更高，因为其叶子节点包含了所有的关键字，并用链表连接起来，提高了增删效率

8. 为什么不使用平衡二叉树作为索引

索引是存放在磁盘中的，且索引通常是很大的，因此无法将全部索引一次加载到内存中，每次只能读取一个磁盘页的数据到内存中，而磁盘读取时间相比于内存读取时间是很长的。平衡二叉树的物理实现是数组，在逻辑结构上相近的节点在物理结构上可能相差很远，因此每次读取的磁盘页上的数据有很多是用不上的，需要进行多次I/O，平衡二叉树不适合作为索引

9. 说一说索引设计的原则

• 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
• 基数较小的列索引效果差，没有必要建立索引
• 使用短索引，如果对长字符串进行索引，应该指定一个前缀长度，这样能节省大量索引空间
• 不要过度使用索引，索引要占用额外的磁盘空间，并降低写操作的性能

10. 说一说创建索引的原则

• 最左前缀匹配原则，使用组合索引时，mysql会一直向右匹配直到遇到范围查询就停止匹配，范围查询后的索引是用不到的，因此索引顺序非常重要，一般将使用最频繁的列放在最左边
• 频繁用作查询条件的字段才去创建索引
• 频繁更新的字段不适合创建索引
• 查询很少用到的列，重复值较多的列不适合创建索引
• 不能有效区分数据的列不适合作为索引
• 尽量扩展索引，不要新建索引
• 定义外键的列要创建索引
• 定义为text、image、和bit类型的列不要创建索引

11. 创建索引需要注意什么

• 除非想要使用NULL，否则应指定列为NOT NULL。因为含有空值的列很难进行查询优化，一般使用默认值代替空值
• 将离散程度大的列放在联合索引的前面，这样能提高效率
• 索引字段越小越好，这样磁盘页存储的数据越多效率越高

12. 百万级或以上的数据如何删除

进行增删改的时候都需要维护索引，直接进行删除效率很低，如果删除中断就会进行回滚，消耗的时间更长。因此可以先删除索引，然后再删除无用数据，删除完成后再重新创建索引

13. 什么是聚簇索引和非聚簇索引

• 聚簇索引：聚簇索引指的使用主键创建B+树，B+树叶子节点中存放的是整张表的行记录数据，每张表只能有一个聚簇索引
• 非聚簇索引：建立在聚簇索引之上的索引，也称为辅助索引。辅助索引访问数据总是需要二次查找，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

14. 什么是联合索引，为什么需要注意联合索引中的顺序

MySQL可以使用多个字段同时建立一个索引，这就是联合索引，联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序依次使用，否则无法命中索引。因为MySQL是按字段顺序来进行索引排序的，先对第一个字段排序，如果字段相同，则按下一个字段排序，依次类推，所以建立联合索引的时候一般将频繁使用的字段放在前面

15. 什么是覆盖索引，为什么覆盖索引非常高效

覆盖索引是指只用通过检索索引就能够读取到想要的数据，不需要再到数据表中读取行，因此非常高效。如果一个索引覆盖了查询语句正在查找的所有数据，那么这就是覆盖索引。不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引的列，哈希索引、全文索引都不存储索引列的值，所以只有BTree索引能实现覆盖索引

七、存储引擎

1. InnoDB

InnoDB是MySQL默认的事务型存储引擎，支持行级锁和外键约束。实现了四种事务的隔离级别，在可重复读隔离级别下，通过MVCC和Next-key锁来防止幻读，主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大提升
InnoDB的数据文件本身就是索引文件，其B+树的叶子节点保存了所有的数据行，索引文件需要按主键聚集，所以InnoDB要求表必须有主键，而MyISAM则不要求。如果没有显示指定主键，InnoDB会自动选择一个可以唯一标识数据行的列作为主键，如果没有这种列，那么InnoDB会自动生成一个隐式字段作为主键
内部做了很多优化，包括插入缓冲、二次写、自适应哈希索引、预读等，这也是InnoDB存储引擎的四大特性

2. MyISAM

MyISAM是MySQL的上一代默认存储引擎，不支持事务，不支持行级锁和外键约束。只能对整张表加锁，读取时对表加共享锁，写入时对表加排他锁，但在表有读取操作的同时，也可以向表中插入新的记录，这被称为并发插入
如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成的时候，不会立即将修改的索引数据写入磁盘，而是会写入到内存的缓冲区中，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘，这种方式可以提高写入性能，但在数据库崩溃的时候会造成索引损坏，需要执行修复操作

3. MEMORY

所有的数据都在内存中，数据的处理速度很快，但是安全性不高

4. MyISAM与InnoDB的区别

	MyISAM	InnoDB
存储结构	每张表被存放在三个文件中：frm-表格定义，MYD-数据文件，MYI-索引文件，数据和索引分开存储	每张表都被保存在同一个数据文件中，数据和索引集中存储
记录存储顺序	按记录插入顺序存储	按主键大小有序存储
外键	不支持	支持
事务	不支持	支持
锁支持	表锁	表锁和行锁
哈希索引	不支持	支持
全文索引	支持	不支持

5. MyISAM与InnoDB索引的区别

• InnoDB索引是聚簇索引，而MyISAM是非聚簇索引
• InnoDB的主键索引的叶子节点存储着数据行，因此主键索引非常高效，而MyISAM索引的叶子节点存储的是数据行地址，需要再寻址一次才能得到数据
• InnoDB辅助索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

八、MySQL相关

1. MySQL数据类型

• 整数类型：tinyInt(8)、smallInt(16)、mediumInt(24)、int(32)
• 实数类型：float、double、decimal
• 日期和时间类型：year、time、date…
• 文本、二进制类型：CHAR(M)：M为0_{255之间的整数、VARCHAR(M)：M为0}65535之间的整数、TEXT、BLOB

2. 数据库的三大范式

• 第一范式：每个列都不可再拆分
• 第二范式：在第一范式的基础上，非主键列必须完全依赖于主键，不能部分依赖
• 第三范式：在第二范式的基础上，非主键列只能依赖于主键，不能依赖于其他主键

3. SQL语句主要分为哪几类

• 数据定义语言DDL(Data Definition Language)：CREATE,DROP,ALERT
• 数据查询语言DQL(Data Query Language)：SELECT
• 数据操纵语言DML(Data Manipulation Language)：INSERT,UPDATE,DELETE
• 数据控制语言DCL(Data Control Language)：GRANT,REVOKE,COMMIT,ROLLBACK

4. 超键、候选键、主键、外键

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键
• 候选键：没有冗余元素的最小超键
• 主键：数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合
• 外键：在一个表中存在的另一个表的主键称为此表的外键

5. SQL约束有哪些

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK：用于控制字段值的范围

6. MySQL关联查询

• 笛卡尔积：相当于全连接
• 内部连接：目前为止内部连接都是等值连接，自连接也是一种内部连接
• 外部连接：分为左外连接和右外连接两种
• 组合查询：UNION必须由多条SELECT语句组成，使用UNION分隔，每个查询必须包含相同的列、表达式或聚集函数，且列数据类型必须兼容。UNION会自动取出重复的行，如果想保留所有行，可以使用UNION

7. varchar和char的区别

• char表示定长字符串，长度固定，如果数据长度小于char的固定长度，则用空格填充。char长度固定，所以存取速度更快，char最多存放255个字符
• varchar表示变长字符串，长度不是固定的，插入的数据多长，就按照多长来存储，空格也算一个字符。varchar长度不固定，所以存取速度比char慢，varchar最多存放65535个字符

8. int(20)含义

int()中的数字仅表示显示字符的长度，不影响内部存储，之影响前面补多少个0，易于展示

9. float和double的区别

• float类型最多可以存储8位十进制数，占4个字节
• double类型最多可以存储18位十进制数，占8个字节

10. drop、delete和truncate区别

九、SQL优化

1. 如何定位及优化SQL语句的性能问题，查看创建的索引是否使用到，或者如何找到SQL语句运行慢的原因

• 对于低性能SQL语句的定位，可以使用执行计划，MySQL提供了explain命令来查看语句的执行计划。其中有select_type表示查询类型，table表示查询的数据表，type表示访问类型，通过查看type可以知道有没有走索引，比如ALL表示扫描全表，index表示遍历索引，ref表示普通索引；possible_keys表示可能使用到的索引，不一定会使用，查询涉及到的字段上若存在索引，就会被列出来，该列为NULL的时候就要考虑是否需要优化了，key表示实际使用的索引,rows表示扫描的行数

2. SQL的生命周期

• 应用服务器与数据库服务器建立连接
• 数据库进程拿到请求
• 解析并生成执行计划，执行
• 读取数据到内存并进行逻辑处理
• 将结果返回到客户端
• 关闭连接，释放资源

3. 大表查询优化方法有哪些

• 首先考虑优化sql语句，使用相关的索引
• 尝试使用缓存，将热点数据缓存起来
• 主从复制，读写分离
• 垂直切分
• 水平切分

4. 超大分页如何处理

• 在数据库层面，比如说像select * from table where age > 20 limit 1000000,10 这种查询，我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10) ，这样就使用了覆盖索引，速度会很快

5. 说一说MySQL分页

MySQL分页使用LIMIT关键字，用来返回指定的记录数，可以接收一个或两个数字参数。第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目，初始记录行偏移量为0，-1表示直到记录结束

6. 慢查询日志

用来记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，一旦SQL语句执行时间超过了阈值就会被记录到慢查询日志中

• 开启慢查询日志：slow_query_log参数
• 设置阈值：long_query_time参数

7. 对慢查询是怎么优化的

慢查询首先要明白慢的原因是什么，是没有命中索引，还是加载了不必要的数据，还是数据量太大

• 首先分析语句，看看是否加载额外的数据，如果有的话就对语句进行重写
• 分析语句的执行计划，看看使用索引的情况，之后修改语句或修改索引，使语句能命中索引
• 进行了前两个步骤后，可以考虑数据量是否太大，如果太大的话可以考虑进行横线或纵向分表

十、数据库优化

1. 数据库结构优化有哪些方法

• 将字段很多的表分解成多个表：对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表，因为当表的数据量很大的时候，这些使用频率低的字段会降低性能
• 增加中间表：对于需要经常联合查询的表，可以建立中间表，这样就能节省关联查询的开销
• 增加冗余字段：通过增加冗余字段，可以减少连接的次数，提高效率

2. 大表如何优化，分库分表原理是什么

MySQL单表记录数过大的时候，性能会明显下降，此时就需要进行优化：

• 限定数据的范围：在查询语句中限定数据范围，避免加载不必要的数据
• 读写分离：将数据库拆分开来，主库负责写，从库负责读
• 使用缓存：将重量级、更新少的数据保存在缓存中，提高查询效率
• 分库分表：垂直分表、水平分表

3. 垂直切分和水平切分

• 垂直切分：将一张表按列切分为多个表，通常按照列的关系密集程度进行切分
  • 优点：减少查询时的数据量，提高效率；简化表结构，易于维护
  • 缺点：主键会出现冗余，需要管理冗余列，查询所有数据的时候需要联合查询
• 水平切分：水平切分又称为sharding，它是指将表中的记录拆分到多个相同结构的表中。水平切分解决了单表数据过大的问题，如果切分后依旧保存在同一台机器上，并不能提高并发度，因此水平切分最好分库。当表中的数据具有独立性，比如分表记录各地区的数据或不同时期的数据时水平切分比较适用
  • 优点：能够解决单表数据过大的问题，提高效率，如果分库的话能提高并发度
  • 缺点：查询需要多个表明，查询所有数据需要UNION操作

分库分表常见方案是客户端代理和中间件代理

4. 分库分表后面临的问题

• 事务问题：使用分布式事务来解决。如果依赖数据库本身的分布式事务管理功能会造成性能开销，如果由程序逻辑来实现，会造成编程方面的负担
• 跨库join问题：分两次查询实现，第一次查询找出关联数据的id，第二次查询根据id得到数据
• 跨库group by、order by、聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并
• 跨分片的排序分页：当排序字段就是分片字段的时候，比较容易定位到指定的分片，当排序字段不是分片字段的时候，就需要在不同的分片节点中将数据排序，然后将不同分片的排序结果进行汇总再次进行排序
• ID问题：可以使用UUID作为主键，UUID能保证ID在所有机器中的唯一性。UUID非常长，占16个字节，会占用大量的存储空间，关键是在建立索引和使用索引的时候都存在性能问题

5. MySQL如何实现主从复制，主从复制原理

主从复制常见问题
主从复制常见问题2

MySQL主从复制主要涉及三个线程：master的binlog dump线程、slave的I/O线程、slave的SQL线程

• binlog dump线程：当主库有数据更新时，就会将数据更改保存到二进制日志binary log中，然后binlog dump线程会通知slave有数据更新，当I/O线程请求日志内容时，就会将binary log和更新的位置传给slave的I/O线程
• I/O线程：I/O线程会连接到master，向binlog dump线程请求一份指定二进制日志的副本，保存到本地的中继日志relay log中，中继日志按递增后缀名的方式产生多个文件，slave使用一个index文件来追踪当前使用的中继日志
• SQL线程：SQL线程检测到中继日志有更新后，就会replay中继日志，将主库中发生的事情重新执行一遍，保证同步。同时，如果一个中继日志中记录的所有操作都被全部执行了以便，那么SQL线程会自动删除这个中继日志

6. 说一说读写分离

读写分离是基于主从复制来实现的，主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离能提高性能的原因是：

• 主服务器主要负责写操作，缓解锁争用
• 多个从服务器只进行读操作，保证负载均衡，提升查询性能
• 增加冗余，实现数据备份

读写分离常用代理的方式来实现，代理服务器接收应用层传来的读写请求，然后代理服务器决定使用哪个服务器

查漏补缺

1. 不走索引的几种情况
2. 什么情况下临键锁会退化成记录锁
3. 重做日志和回滚日志，日志有哪些
   elay log中，中继日志按递增后缀名的方式产生多个文件，slave使用一个index文件来追踪当前使用的中继日志

• SQL线程：SQL线程检测到中继日志有更新后，就会replay中继日志，将主库中发生的事情重新执行一遍，保证同步。同时，如果一个中继日志中记录的所有操作都被全部执行了以便，那么SQL线程会自动删除这个中继日志

!](https://img-blog.csdnimg.cn/20200813102902882.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L051amFiZXM=,size_16,color_FFFFFF,t_70#pic_center)

6. 说一说读写分离

读写分离是基于主从复制来实现的，主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作

读写分离能提高性能的原因是：

• 主服务器主要负责写操作，缓解锁争用
• 多个从服务器只进行读操作，保证负载均衡，提升查询性能
• 增加冗余，实现数据备份

读写分离常用代理的方式来实现，代理服务器接收应用层传来的读写请求，然后代理服务器决定使用哪个服务器