MySQL面试题

数据库的三范式是什么

1. 第一范式：列不可再分
2. 第二范式：行可以唯一区分，主键约束
3. 第三范式：表的非主属性不能依赖于其他表的非主属性，外键约束

• 三级范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立在第一、第二范式上

MySQL数据库引擎有哪些

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

• MySQL常用引擎包括：MyISAM、InnoDB、MEMORY
  1. InnoDB：InnoDB是MySQL的默认存储引擎。它支持事务、行级锁和外键约束等特性，适用于需要高并发、高可靠性、高可用性和数据安全性的应用场景。
  2. MyISAM：MyISAM是MySQL的另一个常见的存储引擎，它不支持事务和行级锁，但是支持全文检索和压缩包等特性，适用于读密集、写少的应用场景。
  3. MEMORY：MEMORY引擎是一种基于内存的存储引擎，适用于对读写速度要求非常高的应用场景，但是数据不能持久化。

说说InnoDB和MyISAM的区别

• InnoDB和MyISAM是MySQL最常用的两种存储引擎，它们有以下几个区别
  1. 事务和锁：InnoDB支持事务和行级锁，而MyISAM不支持事务，只支持表级锁。因此在高并发的场景下，InnoDB可以提供更好的并发性能和数据一致性，而MyISAM的锁机制可能会导致读写冲突和性能问题。
  2. 索引：InnoDB的索引是B+树索引，而MyISAM的索引是B树索引。B+树索引对于范围查询和排序等操作的性能更好，而B树索引对于等值查询的性能更好。
  3. 全文检索：MyISAM支持全文检索，而InnoDB不支持全文检索。如果需要进行全文检索，可以使用MySQL的全文检索引擎(如Sphinx)，或者使用其他支持全文检索的数据库
  4. 外键约束：InnoDB支持外键约束，而MyISAM不支持外键约束。使用外键约束可以保证数据的完整性和一致性，避免数据异常和错误。
  5. 数据安全性：InnoDB支持崩溃恢复和数据恢复，可以保证数据的安全性和完整性。而MyISAM不支持崩溃恢复和数据恢复，一旦发生崩溃，可能会导致数据丢失或不一致。
  6. InnoDB不保存表的具体行数，执行SELECT COUNT(*) FROM table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

数据库事务

• 数据库事务是指一系列数据库操作被视为一个单独的逻辑单元，要么全部成功执行，要么全部失败回滚。事务的目的是确保数据的完整性和一致性，即使在多个并发访问的情况下也保证数据的正确性。
• 在数据库中，事务通常由以下四个属性组成，即ACID:
  1. 原子性(Atomicity)：事务是一个原子操作，要么全部执行，要么全部回滚，不存在部分执行的情况。
  2. 一致性(Consistency)：事务执行前后，数据库中的数据必须满足约束条件和完整性规则，即数据库中的数据必须处于一致的状态。
  3. 隔离性(Isolation)：多个事务并发执行时，每个事务都应该感觉不到其他事务的存在，每个事务的执行应该独立于其他事务的执行。
  4. 持久性(Durability)：一旦事务执行成功，它对数据库的修改应该是永久性的，即使发生系统故障或重启，数据库也应该能够恢复到事务执行成功的状态。
• 通过使用事务，可以确保数据库操作的原子性和一致性，避免数据异常和错误。

索引是什么

• 索引是一种特殊的数据结构，用于加快数据库中数据查询的速度。它类似于书中的目录，可以帮助快速查找特定的数据。
• 在数据库中，索引通常是在表的一列或多列上创建的。创建索引后，数据库会使用类似于二分查找的算法来查找数据，从而大大提高了数据查询的速度。
• 索引可以大大提高数据查询的速度，但也会增加数据修改的时间和磁盘空间的使用。因此，在选择索引时，需要根据实际情况进行权衡。一般来说，应该对经常用于查询的列或用于表之间连接的列创建索引，同时避免创建过多的索引和不必要的索引，以免对数据库性能产生负面影响。
• 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织的索引。

SQL优化手段有哪些

• SQL优化是指对数据库中的SQL语句进行调整和优化，以提高查询和修改数据的效率，下面是一些常用的SQL优化手段
  1. 查询语句中不要使用SELECT *：使用SELECT *会查询出所有的列，包括可能不需要的列，这样会增加查询的数据量，影响查询的效率。在实际应用中，应该尽量指定需要查询的列。

  ## 优化前
  SELECT * FROM user;
  
  ## 优化后
  SELECT id, name, age FROM user;

  2. 尽量减少子查询，使用关联查询(LEFT JOIN、RIGHT JOIN、INNER JOIN)替代：子查询是指在一个查询语句中嵌套另一个查询语句。由于子查询需要进行多次查询，因此会降低查询的效率。可以使用关联查询来替代子查询，以减少查询次数。

  ## 优化前
  SELECT name, age, (SELECT MAX(salary) FROM salary WHERE salary.user_id = user.id) AS max_salary FROM user;
  
  ## 优化后
  SELECT user.name user.age MAX(salary.salary) AS max_salary
  FROM user
  LEFT JOIN salary ON user.id = salary.user_id
  GROUP BY user.id, user.name, user.age;

  3. 减少使用IN或NOT IN，使用EXISTS、NOT EXISTS或关联查询替代：IN和NOT IN查询会对列表中的每个值进行匹配，因此查询效率较低。可以使用EXISTS或NOT EXISTS查询语句或关联查询语句替代

  ## 优化前
  SELECT name FROM user WHERE id IN (SELECT user_id FROM order WHERE status = 'completed')
  ## 优化后
  SELECT name FROM user
  WHERE EXISTS (SELECT 1 FROM order WHERE order.user_id - user.id AND order.status = 'completed')

  4. OR的查询尽量使用UNION和UNION ALL代替（在确认没有重复数据或不用去重数据时，UNION ALL会更好）：OR查询语句会对多个条件进行匹配，而UNION和UNION ALL查询语句会将多个查询语句的结果合并。在确定没有重复数据或不需要剔除重复数据时，使用UNION ALL会比UNION更高效。

  ## 优化前
  
  ## 优化后

  5. 尽量避免在WHERE子句中使用!=、<>操作符，否则引擎将放弃使用索引而进行全表扫描，可以使用=操作符或NOT操作符来替代

  ## 优化前
  SELECT name FROM user WHERE age <> 18;
  ## 优化后
  SELECT name FROM user WHERE age < 18 OR age > 18;

  6. 尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描，如

  SELECT id FROM tb WHERE id IS NULL
  ## 改为如下形式，前提是num设置默认值为0，确保num列不会出现NULL值
  SELECT id FROM tb WHERE id = 0

简单说说DROP、DELETE与TRUNCATE的区别

• DROP、DELETE、TRUNCATE都是MySQL中用于删除数据或对象的语句，他们的区别如下
  1. DROP：用于删除整个数据库、表或视图等对象，同时删除对象机器相关的约束、触发器和索引等。DROP语句是无法恢复的，一旦执行成功，对象将永久删除。
  2. DELETE：用于删除表中的数据行，但不删除表本身或表结构。DELETE语句可以使用WHERE子句指定要删除的数据行，如果没有指定WHERE子句，将删除表中的所有数据。DELETE语句是可逆的，使用ROLLBACK语句可以撤销删除操作。
  3. TRUNCATE：也是用于删除表中的数据行，与DELETE不同的是，TRUNCATE语句删除表中所有数据（不支持WHERE子句）。TURNCATE比DELETE更快且更有效率，同时也是无法恢复的。
• DELETE语句是DML，这个操作会放到ROLLBACK SEGEMENT中，事务提交后才生效，如果有相应的TRIGGER，执行时会被触发
• DROP和TRUNCATE是DDL，操作立即生效，无法回滚，操作不触发TRIGGER

什么是视图

• 视图(View)是一种虚拟的表，是从一个或多个基本表(或视图)导出的表。视图并不存储数据，而是通过查询基本表得到数据的结果集，因此视图中的数据是动态的，会根据基本表的数据变化而自动更新。
• 视图可以看做是一种数据过滤器或数据抽象层，可以隐藏底层表的细节，提供更加简单、直观的数据访问接口，方便用户进行查询和分析。通过视图，用户可以只看到他们需要的数据，而不需要了解底层表的具体结构和关系。
• 例如下面的视图从员工表中过滤出工资大于5000的员工的信息

CREATE VIEW high_salary_employee AS
SELECT emp_id, emp_name, salary
FROM employee
WHERE salary > 5000;

• 通过创建这个视图，用户可以直接查询工资大于5000的员工信息，而不用每次都写出SELECT查询语句

什么是内连接、左外连接、右外链接

• 内连接(Inner Join)：匹配两张表中相关联的记录
• 左外连接(Left Outer Join)：除了匹配两张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示
• 右外链接(Right Outer Join)：除了匹配两张表中相关联的记录歪，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示

并发事务带来哪些问题

• 在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一条数据进行操作）。并发虽然是必须的，但是同样会导致以下几个问题
  1. 脏读(Dirty read)：当事务A正在访问数据并且对数据进行了修改，而这种修改还没有被提交到数据库中，此时事务B也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么事务B读到的数据就是脏数据，依据脏数据所做的操作可能是不正确的。
  2. 丢失修改(Lost to modify)：是指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务修改了这个数据后，第二个事务也修改了这个事务，那么就会导致第一个事务修改的结果被丢失，因此成为丢失修改。
     • 例如事务A读取表中的数据age=10，事务B也读取age=10，事务A修改age=age+1，事务B修改age=age+2，最终结果age=12，事务A的修改被丢失
  3. 不可重复读(Unrepeatable read)：是指在一个事务内多次读取同一事务。在这个事务还没有结束时，另一个事务也访问该数据，那么在第一个事务中的两次读数据之间，由于第二个失误的修改导致第一个事务两次读取的数据可能不太一样，这就发生了在一个食物内两次读到的数据是不一样的情况，因此被称为不可重复读。
  4. 幻读(Phantonm read)：幻读与不可重复读类似，它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，因此被称为幻读。
• 不可重复读和幻读的区别：
  • 不可重复读的重点是修改：例如多次读取一条记录，发现其中某些列的值被修改了。
  • 幻读的重点在于新增或删除：例如多次按照固定的查询语句查询记录，发现读取到的记录数增多了或是减少了

事务隔离级别有哪些？MySQL的默认隔离级别是什么

• 事务隔离级别是指多个事务之间互相隔离的程度，目的是保证事务的一致性和隔离性，MySQL支持四种隔离级别，分别是
  1. 读未提交(Read Uncommitted)：最低的隔离级别，一个事务可以读取另一个事务未提交的数据，容易出现脏读、不可重复读、幻读的问题。
  2. 读已提交(Read Committed)：一个事务只能读取另一个事务已经提交的数据，可以避免脏读问题，但是可能出现不可重复读和幻读的问题。
  3. 可重复度(Repeatable Read)：一个事务开始后，不允许其他事务修改该事物使用的数据，可以避免脏读和不可重复读的问题，但是可能出现幻读问题。
  4. 串行化(Serializable)：最高的隔离级别，完全的隔离事务，每个事务都必须等待前一个事务提交后才能执行，避免了所有并发问题。

大表如何优化

• 优化大表的性能是数据库优化的重要部分，下面是一些常用的优化方案
  1. 垂直分隔(Vrtical Partitioning)：将大表按照业务逻辑拆分为多个表，每个表只包含特定的字段，可以减少每个表的行数和列数，提高查询效率。
  2. 水平分隔(Horizontal Partitioning)：将大表按照某个字段(如时间、地域等)进行分区，将不同的数据存储在不同的分区中，可以减少单个表的行数，提高查询效率。
  3. 数据库分库分表(Sharding)：将大表拆分为多个小表，分散在多个数据库或者表空间中，可以提高并发访问能力和数据处理能力，但是需要考虑数据的一致性和跨分区查询等问题。
  4. 索引优化：对于大表，需要特别注意索引的设计和优化，尽量避免全表扫描和索引失效等问题。
  5. 数据库优化参数调整：对于大表，需要根据实际情况适当调整数据库的一些参数，例如缓存大小、并发连接数、线程池大小等。
  6. 定期清理无用数据：对于大表，需要定期清理无用数据，以减少表的行数和提高查询效率。
• 详细内容可以参考：MySQL大表优化方案

分库分表后，id主键如何处理

• 将大表拆分为多个表后，如果每个表的主键都是从1开始累加的，这样是不对的，我们需要一个全局唯一的ID来标识
• 生成全局ID有下面几种方式
  1. UUID：不适合作为主键，太长了，并且无序不可读，查询效率低。比较适合用于生成卫衣的名字标识，例如文件名。
  2. 数据库自增id：两台数据库分别设置不同的自增补偿，生成不重复ID的策略来实现高可用。这种方式生成的ID有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  3. 利用Redis生成ID：性能比较好，灵活方便，不依赖于数据库，但是引入了新组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本
  4. Twitter的snowflake算法（雪花算法）

说说在MySQL中，一条查询SQL是如何执行的

• 大致分为以下几个步骤
  1. 获取连接：使用MySQL连接器连接到数据库。
  2. 查询缓存：key为SQL语句，value为查询结果，如果查到就直接返回。但是不建议使用此缓存，在MySQL 8.0版本中已经将查询缓存删除掉了
  3. 分析器：将SQL语句进行分词和语法分析，判断语法是否正确，如果语法不正确，会在这个阶段发现并报错。
  4. 优化器：对SQL语句进行优化，选择最优的执行计划。执行计划是指在表中查询数据的方法，包括使用哪些索引、连接表的顺序等。优化器会根据表中的索引、表大小、统计信息等因素，选择最优的执行计划。
  5. 执行器：根据优化器选择的执行计划执行SQL语句。执行器负责打开表，根据表的引擎定义，使用引擎提供的接口获取数据，进行筛选和计算，最终返回查询结果。

索引有什么缺点

• 虽然索引可以加速查询，但是也存在以下缺点
  1. 索引会增加数据的存储空间：索引需要占用一定的存储空间，尤其是对于大表来说，索引可能会占用相当大的空间。
  2. 索引会降低数据的修改速度：当数据表中的数据发生修改时，索引也需要进行相应的更新操作。因此，对于经常需要修改的表，索引可能会降低数据的修改速度。
  3. 索引不适用于所有类型的查询：虽然索引可以加速许多类型的查询，但不是所有的查询都可以使用索引进行优化。例如对于使用LIKE或%操作符的模糊查询，索引可能会失效。
  4. 索引可能会降低数据库的整体性能：虽然索引可以加速查询，但是过多的索引可能会降低数据库的整体性能。因为索引也会占用一定的系统资源，例如CPU、内存等，如果索引太多，会导致系统资源不足，从而影响数据库的整体性能。

MySQL中varchar和char的区别？varchar(30)中的30代表的含义

• varchar和char是两种不同的数据类型，它们的主要区别在于存储方式和存储空间的使用。具体来说，char是一种固定长度的字符类型，而varchar是一种可变长度的字符类型。
• 在char类型中，如果指定了一个长度为N的char字段，那么该字段总是占用N个字符的存储空间，即使实际存储的字符数小于N，也不会释放多余的存储空间。
• 在varchar类型中，如果指定了一个长度为N的varchar字段，那么改组段可以存储最多N个字符的数据，但实际存储的字符数可能会小于N，此时该字段将只占用实际存储的字符数的存储空间，而不是固定长度的存储空间
• 在varchar(30)中，30表示该字段可以存储的最大字符数为30，但实际存储的字符数可能小于30。如果实际使用中存储的自付超过了30，那么MySQL将自动抛出错误或者截断数据。

int(11)中11代表的意义

• 在MySQL中，int(11)中的11表示字段的显示宽度，而不是字段的最大值或者存储空间。

为什么SELECT COUNT(*) FROM table在InnoDB比MyISAM慢

• 在MySQL中，执行SELECT COUNT(*) FROM table语句时，会对表中的每一行进行一次计数操作，因此需要遍历整个表。
• 对于MyISAM表来说，表中保存了行数的计数器，因此在执行SELECT COUNT(*) FROM table时，只需要读取计数器的值，而不需要遍历整个表，因此执行速度非常快。
• 但是在InnoDB中，由于数据存储的特殊性质和实现方式，没有类似于MyISAM的计数器来保存表中的行数，因此MySQL需要遍历整个表来进行计数操作。

MySQL索引类型有哪些

• MySQL支持多种类型的索引，包括以下几种
  1. B树索引：这是MySQL中最基本的索引类型，采用B树结构存储数据，适用于查找单个值的场景。
  2. B+树索引：B+树是B树的一种变体，在B树的基础上增加了叶子结点的指针。形成一个链表，适用于范围查找和排序的场景。
  3. 哈希索引：这种索引采用哈希算法来快速定位数据，适用于等值查询场景，但不支持范围查询和排序。
  4. 全文索引：这种索引用于全文搜索，适用于对文本进行模糊匹配和搜索的场景。

什么时候不要使用索引

• 索引虽然可以加快查询速度，但是在某些情况下也会带来额外的性能消耗，甚至会降低查询效率。以下是不适合使用索引的情况
  1. 数据量非常小：对于非常小的数据集，索引可能不会带来显著的性能提升，反而会增加额外的开销。
  2. 经常进行插入、删除或修改操作：对表进行更新操作时，索引也需要更新和维护，会导致额外的性能开销。如果对表的更新操作比较频繁，那么使用索引可能会影响性能。
  3. 有大量重复的列：如果某个列存在大量重复的值，就算这个列没有唯一性约束，也不建议对其建立索引。因为索引的目的是帮助数据库加速数据的查找和匹配，而如果一个列存在大量重复的值，建立索引反而会降低查询的效率。

说说什么是MVCC

• MVCC(Multi-Version Concurrency Control, 多版本并发控制)是一种用于保证数据库并发性的技术。
• 在传统的数据库并发控制机制中，锁定是控制并发访问的主要手段。当多个事务同时访问同一行数据时，系统会对该行数据进行锁定，直到其中一个事务完成操作并释放锁之后，其他事务才能访问该行数据。这种方式虽然可以确保数据的一致性和安全性，但是会引入许多问题，如死锁、性能瓶颈等。
• MVCC采用了一种不同的方式来控制并发访问。在MVCC中，每个事务都可以看到一个版本（或者说是快照）的数据，不同的事务之间使用不同的版本来避免冲突。当一个事务更新某个数据时，系统会创建一个新的版本，并在事务提交时将其写入磁盘。其他事务继续使用旧版本的数据，直到它们提交或回滚事务，然后再使用新版本的数据。这样可以避免锁定和死锁等问题，并提高并发访问的效率。

MVCC可以为数据库解决什么问题

• MVCC可以解决数据库中并发性和一致性的问题。数据库中的并发性问题包括了读写冲突、死锁等，而MVCC通过采用乐观锁的方式，允许多个事务同时读取一个数据，同时又能够保证数据的一致性。
• 具体来说，MVCC实现了以下特性
  1. 可重复度：通过使用每个事物的独立版本快照来避免数据被其他并发事务修改而导致的读取不一致的问题。
  2. 非阻塞读：读操作不会阻塞写操作，写操作也不会阻塞读操作。
  3. 无锁写：MVCC使用CAS(Compare-And-Swap)操作来实现乐观锁，避免了传统锁机制的瓶颈和缺陷。
  4. 避免死锁：MVCC不使用传统锁机制，避免了传统锁机制中可能发生的死锁的问题。
  5. 高并发：MVCC通过提供并发事务的独立快照来保证读取的一致性，从而提高了数据库的并发性能。
• 总之，MVCC能够使数据库在高并发的情况下保持一致性和可靠性，使得多个事务可以同时访问同一个数据库，从而提高了数据库的并发性能。

说说MVCC的实现原理

• MVCC的实现原理主要包括版本号、快照、回滚段、多版本索引等技术。
  1. 版本号：每个数据行都有一个版本号，用来标识该行数据的版本。在MySQL中，InnoDB存储引擎使用一个6字节的事务ID和一个7字节的递增计数器来表示版本号。
  2. 快照：每个事务在开始执行时，会建立一个快照(Snapshot)，用来记录当前时间点的数据库状态。这个快照可以看做是一个事务的视图，反映了它所能看到的数据状态。在查询时，系统会根据事务的快照，返回相应版本的数据。
  3. 回滚段：在MVCC中，每个事务都会创建一个回滚段(Rollback Segment)，用来保存该事物所做的修改。如果事务需要回滚，系统就可以利用回滚段中的信息来回复数据的原始状态。
  4. 多版本索引：为了支持MVCC，数据库通常会使用多版本索引(Multi-Version Index)来记录数据的版本信息。多版本索引包含多个版本的数据，每个版本都有自己的版本号和快照信息。当事务执行查询时，系统会根据事务的快照，返回相应版本的数据。
• MVCC的实现原理可以概括为：每个事务都有自己的快照，用来记录当前时间点的数据状态。在查询时，系统会根据事务的快照，返回相应版本的数据。当事务需要回滚时，系统会创建一个新的版本，并将旧版本标记为已删除。如果事务需要回滚，系统就可以利用回滚段中的信息，恢复数据的原始状态。多版本索引用于记录数据的版本信息，以便系统能够快速地找到相应版本的数据。这些技术相互配合，实现了MVCC的高效并发控制。

说说MySQL数据库的锁

• MySQL数据库中的锁分为共享锁(Shared Lock)和排它锁(Exclusive Lock)两种。
  1. 共享锁：也被称为读锁，允许多个事务同时读取同一份数据，但不允许任何事物对数据进行修改，找到所有共享锁都被释放。
  2. 排他锁：也被称为写锁，只允许一个事务对数据进行修改，其他事务无法读取或修改数据，直到排他锁被释放。
• MySQL还支持表级锁行级锁两种粒度的锁
  1. 表级锁：是对整张表的锁，可以锁定整个表，避免其他事务对该表进行任何操作。表级锁的优点是简单，系统开销小，适用于对表进行全局性操作；缺点是粒度太大，会导致并发性能下降。MyISAM使用表级锁。
  2. 行级锁：是针对单个数据行的锁，只锁定需要修改的数据行，避免其他事务对该数据进行操作。行级锁的优点是粒度小，发生冲突概率低，可以提高并发性能；缺点是实现复杂，需要维护锁定的数据行和事务的状态，容易出现死锁。InnoDB支持行锁（必须有索引才能实现，否则会自动锁全表，变成表级锁）

什么是锁升级

• 锁升级是指将行级锁升级为表级锁的过程。MySQL中默认使用的是行级锁，在一些特殊情况下，需要使用表级锁，但是如果表上已经存在行级锁，就需要将行级锁升级为表级锁，从而保证数据的一致性和完整性。
• 锁升级一般会在以下情况发生：
  1. 当前会话需要更新的行数太多，导致获取行级锁的时间过长，从而影响系统的并发性能，此时可以将行级锁升级为表级锁。
  2. 当前会话需要执行的操作已经涉及到整张表的数据，比如对表进行TRUNCATE、DROP等操作，此时需要将行级锁升级为表级锁。
• 锁升级的过程是自动的，由MySQL内部的锁管理器来控制，具体实现细节可以参考MySQL的官方文档。需要注意的是，锁升级会对系统的性能产生一定的影响，因此在实际的应用中需要谨慎使用，尽量避免锁升级的情况。

说说悲观锁和乐观锁

• 悲观锁和乐观锁是两种常见的并发控制方式。
  1. 悲观锁的思想是：假设并发访问的情况下，数据很可能会被其他用户修改，因此在访问数据前就会加锁，防止其他用户对数据进行修改。悲观锁的实现方式一般是通过数据库的锁机制来实现的，例如行级锁、表级锁等。
  2. 乐观锁的思想是：假设并发访问的情况下，数据很少会发生冲突，因此在访问数据前不会加锁，而是通过在数据上增加版本号等机制来判断数据是否被修改。乐观锁的实现方式一般是通过在数据表中增加一个版本号或时间戳等字段，每次修改数据时都会更新这个版本号，如果读取数据是发现版本号与预期的不一致，则说明数据已经被其他用户修改，需要重新读取数据。

怎样尽量避免死锁的出现

• 死锁是指多个事务在互相等待对方释放资源而无法继续执行的情况，这种情况下需要DBMS的干预才能解除死锁。下面是一些尽量避免死锁的方法：
  1. 尽量减少事务持有资源的时间，比如避免在事务中进行大量计算和逻辑处理。
  2. 尽量在事务开始时就确定需要访问的数据，避免在事务中根据条件动态检索数据。
  3. 避免长时间占用多个资源，比如对多个表进行更新操作。
  4. 尽量使用较短的事务，这样锁的等待时间会减少。
  5. 优化SQL语句，尽量避免全表扫描或索引失效等情况。
• 如果死锁无法避免，那么可以使用以下方式作为保底手段解除死锁：
  1. 设置等待超时机制：当一个事务在一定时间内无法获得锁，就主动放弃等待并结束事务，然后再重试。
  2. 死锁检测：DBMS定时检测系统中的死锁情况，并将其解除。这种方法会消耗一定的系统资源，所以不适合在高并发的环境中使用。

使用MySQL的索引时需要注意什么

• 使用MySQL的索引时需要注意以下几点
  1. 应尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数，因此需要查询该表的所有行。
  2. 应尽量避免在WHERE子句中用OR来连接条件，否则将导致引擎放弃使用索引而进行全表扫猫，如: SELECT id FROM t WHERE num = 10 OR num = 20
  3. 应尽量避免在WHERE子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描.
  4. 应尽量避免在WHERE子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描.
  5. 不要在WHERE子句中的=左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  6. 复合索引遵循最左前缀原则。
  7. 如果MySQL评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。
  8. 列类型是字符串类型，查询时一定要给值加引号，否则索引失效.
  9. LIKE 的模糊查询可能会导致无法使用索引。原因是 LIKE 语句中的通配符（% 或 _）使得索引失效。如果需要模糊匹配，可以使用全文索引引擎（例如ElasticSearch）
  10. 表字段为NULL，也不可以使用索引的

主键和候选键有什么区别

• 主键和候选键都是用来唯一标识一张表中每个行的键，但是它们之间有一些区别
  1. 主键必须是非空、唯一的。
  2. 候选键也是唯一标识一张表中每个行的键，但不是必须非空的，在一个表中可以有多个候选键，也可以没有候选键。
  3. 主键是一种特殊的候选键，它是表的主要标识，用于保证表中的每个行的唯一性。如果没有指定主键，则可以选择一个合适的候选键作为主键。候选键可以是多个，但只有一个可以成为主键。

主键和索引有什么区别

• 主键和索引都可以用来唯一标识一张表中的记录，但它们有一些区别
  1. 主键是一种唯一性约束，保证表中每一行数据的唯一性。而索引知识一种辅助数据结构，用于提高查询效率。
  2. 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键。
  3. 主键不允许为空值，唯一索引列允许为空值。
  4. 一个表只能有一个主键，但是可以有多个唯一索引。
  5. 主键可以被其他表引用为外键，唯一索引列不可以。

MySQL如何做到高可用方案

• MySQL的高可用方案可以通过以下几种方式实现：
  1. 主从复制：将一个MySQL实例作为主节点，其余MySQL实例作为从节点，主节点上的操作会被自动同步到从节点上，从而达到高可用的目的。当主节点出现故障时，可以将其中一个从节点提升为新的主节点，从而保证系统的可用性。
  2. 数据库集群：将多个MySQL实例组成一个集群，每个示例都可以处理请求，当其中一个实例出现故障时，其他实例会自动接管请求，从而保证系统的可用性。
  3. 数据库代理：通过引入数据库代理层，将多个MySQL实例进行统一管理，提供负载均衡、故障转移、自动切换等功能，从而达到高可用的目的。
  4. 数据库备份和恢复：通过定期备份数据库，将备份数据存储到其他位置，当出现故障时，可以通过备份数据进行快速恢复，从而达到高可用的目的。

面试模拟

• 面试官：了解过索引吗？
• 候选人：

• 索引在项目中还是比较常见的，它是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低CPU的消耗

• 面试官：索引底层的数据结构了解过吗？
• 候选人：

• MySQL现在默认的存储引擎是InnoDB，它采用的是B+树的数据结构来存储索引，选择B+树的主要原因是
  1. 阶数（树杈）更多，路径更短
  2. 磁盘读写代价更低，飞叶子节点只存储指针，叶子结点存储数据
  3. B+树便于扫库和去检查询，因为叶子结点是一个双向链表
     
• 使用二叉树或者红黑树效率都不高，二叉树在不平衡的情况下，会退化为链表。红黑树虽然能保证平衡性来降低树的高度，但是红黑树只是二叉树，阶数低，如果遇到庞大的数据量，二叉树也会很高，效率也是不如B+树
  
• B树的非叶子结点和叶子结点都会存放数据，范围查询效率低，并且不太稳定
  

• 面试官：什么是聚簇索引？什么是非聚簇索引？
• 候选人：

• 聚簇索引有且只有一个，主要是指数据与索引放到一块，B+树的叶子结点保存了整行数据，一般情况下都是主键作为聚簇索引的
• 非聚簇索引指的是数据与索引分开存储，B+树的叶子结点保存对应的主键，可以有多个，一般我们自定义的索引都是非聚簇索引
  

• 面试官：知道什么是回表查询吗？
• 候选人：

• 这个和刚刚介绍的聚簇索引和非聚簇索引有很大关系的，回表的意思是，通过非聚簇索引找到数据对应的主键值，然后通过主键值去聚簇索引中查找出整行的数据，这个过程就是回表。
  

• 面试官：知道什么叫覆盖索引吗？
• 候选人：

• 覆盖索引值的是select查询语句使用了索引，返回的列必须能在索引中全部找到，如果我们使用id查询，它会直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果按照非聚集索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，所以尽量避免使用SELECT *，尽量在返回的列中都包含添加索引的字段

• 面试官：MySQL超大分页怎么处理？
• 候选人：

• 超大分页一般都是在数据量比较大的时候，我们使用limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

-- 执行该命令，会先排序前900010条数据，仅返回后10条记录，查询的代价非常大
SELECT * FROM tb_stu limit 9000000, 10;

• 先分页查询数据的id字段，确定了id之后，再使用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的是覆盖索引，这样效率就会提升很多

SELECT * 
FROM tb_stu t, (SELECT id FROM tb_stu ORDER BY id LIMIT 9000000. 10) a
WHERE t.id = a.id

• 面试官：索引创建原则有哪些？
• 候选人：

• 嗯，这个情况有很多，不过都有一个大前提，首先表中的数据要超过10万条以上，我们才会去创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是作为查询条件、排序字段或者分组字段这些。其次就是，我们通常创建索引的时候都是使用复合索引来创建，一条SQL的返回值，尽量使用覆盖索引。

• 面试官：什么情况下索引会失效？
• 候选人：

• 嗯，这个情况比较多
  • 比如索引在使用的时候没有遵循最左匹配原则，举例说明：假设有一个复合索引idx_name_age，包含两列：name和age。下面是一些查询条件和它们是否符合最左匹配原则的判断：
    1. 查询条件：WHERE name = ‘John’。
       • 符合最左匹配原则，因为从复合索引的最左侧列name开始进行匹配。
    2. 查询条件：WHERE age > 30。
       • 不符合最左匹配原则，因为复合索引的第一列是name，而age不是从最左侧开始进行匹配的。
    3. 查询条件：WHERE name = ‘John’ AND age > 30。
       • 符合最左匹配原则，因为从复合索引的最左侧列name开始进行匹配，然后紧接着匹配age。
    4. 查询条件：WHERE age > 30 AND name = ‘John’。
       • 不符合最左匹配原则，虽然涵盖了复合索引的所有列，但是列的顺序并没有从最左侧开始连续匹配。
  • 模糊查询的时候，如果%在前面也会导致索引失效。
    • 例如 LIKE '%John' 会失效，而 LIKE 'John%'则不会
  • 在添加索引的字段上进行了运算操作或者类型转换也会导致索引失效

  -- 进行了运算操作
  select * from tb_seller where substring(name,3,2) ='科技'
  -- 字符串不加单引号，也会造成索引失效，因为此时要进行类型转换

• 面试官：有SQL的优化经验吗？
• 候选人：

• 嗯，这个还是比较常见的，可以从这几方面来考虑
  1. 建表的时候选择合适的类型
  2. 使用索引
  3. SQL语句的编写
  4. 主从复制
  5. 读写分离

• 面试官：那你在建表的时候，是如何进行优化的呢？
• 候选人：

• 这个主要参考阿里开发手册，就比如再定义字段的时候，需要结合字段的内容来选择合适的类型，如果是字符串，需要结合存储的内容来选择是使用char还是varchar或者text类型。用户名邮箱之类的，可以用varchar，文章内容这种可以用text，邮编之类的定长字符串可以用char

• 面试官：那你在使用索引的时候，是如何优化的呢？
• 候选人：

• 首先表中的数据要超过10万条以上，我们才会去创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是作为查询条件、排序字段或者分组字段这些。其次就是，我们通常创建索引的时候都是使用复合索引来创建，一条SQL的返回值，尽量使用覆盖索引。

• 面试官：你平时对SQL语句做了哪些优化呢？
• 候选人：

• 嗯，这个也有很多，比如SELECT语句务必指明字段名称，不要直接使用SELECT *，还有就是需要注意避免索引失效的写法，如果是聚合查询，尽量使用UNION ALL来替代UNION，因为UNION会多一次过滤，效率相对较低。如果是表关联的话，尽量使用INNER JOIN，必要时用LEFT JOIN或RIGHT JOIN，如果非要用的话，一定要以小表作为驱动。

• 面试官：事务的特性是什么？可以详细说一下吗？
• 候选人：

• 事务的特性指的是ACID属性，分别是原子性、一致性、隔离性、持久性。我举个例子吧，就比如转账这种场景
  1. A向B转账500，转账成功，A扣除500元，B增加500元。原子性体现在要么都成功，要么都失败。
  2. 在转账过程中，A扣除了500元，那么B必须增加500元，需要保证数据的一致性。
  3. 隔离性体现在，A向B转账，不能受到其他事务的干扰
  4. 持久性体现在，转账成功后，需要将数据持久化。

• 面试官：并发事务带来哪些问题？
• 候选人：

• 第一是脏读，当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，此时另一个事务也来访问了这个数据，由于这个数据还没有提交，那么另一个事务读取到的数据就是脏数据，根据脏数据所做的操作可能是不正确的。
• 第二个是不可重复读，比如在一个事务内多次读同一数据，在这个事务还没有结束时，另一个事务也来访问该数据，那么在第一个事务中的两次读取数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样，因此称为不可重复读。
• 第三个是幻读：幻读跟不可重复读类似，它是发生在一个事务读取了几行数据，接着另一个事务插入了一些数据，在随后的查询中，第一个事务就会发现多了一些原本不存在的记录。

• 面试官：那么怎么解决这些问题呢？
• 候选人：

• 解决方案是对事务进行隔离，MySQL支持四种隔离级别
  1. 读未提交：它解决不了刚刚的所有问题，一般项目里也不用这个
  2. 读已提交：它能解决脏读问题，但是解决不了不可重复读和幻读
  3. 可重复读：他能解决脏读和不可重复读，但是解决不了幻读，同时它也是MySQL的默认隔离级别
  4. 串行化：它可以解决刚刚提出的所有问题，但是由于是让事务串行执行的，性能比较低。所以项目中一般用的都是MySQL的默认隔离级别：可重复读。

• 面试官：undo log和redo log的区别
• 候选人：

• redo log日志记录的是数据页的物理变化，服务宕机可用来同步数据，而undo log 不同，它主要记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据，比如我们删除一条数据的时候，就会在undo log日志文件中新增一条delete语句，如果发生回滚就执行逆操作；
• redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

• 面试官：事务中的隔离性是如何保证的？
• 候选人：

• 隔离性是由锁和MVCC实现的。
• MVCC是多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView视图
• 隐藏字段是指在MySQL中给每个表都设置了隐藏字段，有一个是trx_id（事务id），记录每一次操作的事务id，是递增的，另一个是roll_pointer（回滚指针），指向上一个版本的事务地址
• undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
• readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问哪个版本的数据，不同的隔离级别快照读是不一样的，最终的访问结果也不一样。如果是读已提交隔离级别，每一次执行快照读时生成ReadView，如果是可重复度隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用

• 面试官：MySQL主从同步原理知道吗？
• 候选人：

• MySQL主从复制的核心就是二进制日志(DDL（数据定义语言）语句和 DML（数据操纵语言）语句)，它的步骤是这样的：
  1. 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
  2. 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。
  3. 从库重做中继日志中的事件，将改变反映它自己的数据