MySQL面经整理

1、数据库的常用引擎

• InnoDB（默认）

• Myisam

• Memory

2、InnoDB和MyISAM的区别

1. InnoDB和Myisam的默认索引都是B+tree，不同的是MyISAM的B+树叶子节点存放的是数据的磁盘地址，InnoDB的B+树叶子节点存放的是完整的数据记录

2. InnoDB支持事务、外键、2种锁（行锁（默认）、表锁），写入操作快，Mysql5.6版本以上才支持全文索引（全文索引不支持中文）

3. MyISAM支持表锁，不支持行锁

4. 如果没有显示在表定义时指定主键，InnoDB会选择第一个可以唯一标识数据记录的列作为主键索引，如果不存在这种列，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

5. Myisam不支持事务、外键，支持表锁、全文索引，写入、读取速度快

   每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列

6. InnoDB一张表两个本地文件（.frm、.idb）MyISAM一张表三个本地文件（.frm、.MYI、.MYD）

Memory的默认索引是hash

Memory所有的数据都保留在内存中，不需要进行磁盘的IO所以读取速度快，

但是一旦关机的话表的结构会保留但是数据会丢失，表支持Hash索引，因此查找速度很快

3、Mysql的事务隔离级别

事务具有四个特征（ACID）：

原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）、持久性（ Durability ）。

隔离级别

1. Read Uncommitted（读未提交）–脏读

   在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

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

   这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

3. Repeatable Read（可重复读）–幻读

   这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4. Serializable（串行化）

   这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

幻读和不可重复读的区别：

• 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
• 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

4、如何保证mysql的原子性和持久性？

• 利用redo_log日志保证mysql的持久性

• 利用undo_log保证系统的原子性

• 隔离性通过锁

• 一致性：是通过事务的原子性、隔离性、持久性共同保证的

  一致性是事务的根本追求

5、MySQL是怎么解决幻读的？

5.1、MVCC

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的· 创建版本号，一个保存的是行的删除版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID

SELECT (version)

• 读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。version >= createVersion and version < deleteVersion

INSERT (createVersion)

• 将当前事务的版本号保存至行的创建版本号。 createVersion = version

UPDATE (createVersion)

• 新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号。 新行createVersion = version，旧行deleteVersion = version

DELETE (deleteVersion)

• 将当前事务的版本号保存至行的删除版本号。 deleteVersion = version

5.2、间隙锁

6、MySql的锁分类🔐

所有的锁从概念上可以分为：

• 悲观锁：从数据库层面做并发控制

  实现方式：

  • 行锁
    • 共享锁（读锁）：加锁方式==（lock in share mode）==
    • 排它锁（写锁）：加锁方式==（for update）==
  • 表锁
    • 表独占读锁
    • 表独占写锁

  ==InnoDB存储引擎：==update、delete、insert都会自动给涉及到的数据加上排它锁，select语句默认不会加任何锁。

  InnoDB默认的是行锁（记录锁），行级锁都是基于索引的，如果一条sql语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。

  ==MyISAM存储引擎：==在执行select前会自动给涉及到的所有表加读锁，在执行update、delete、insert前，会自动给涉及到的表加写锁。

• 乐观锁：从应用层面做并发控制，适用于读多写少的场景

  实现方式：

  • 版本号version
  • 时间戳

6.1、MyISAM的锁

表级锁：开销小、加锁快，不会出现死锁，锁定力度大，发生所冲突的概率最高，并发量最低

表共享读锁：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

表独占写锁：会阻塞其他用户对同一表的读和写操作；

加锁方式：

• 在执行查询语句（select）前，会自动给涉及到的表加读锁
• 在执行更新操作（update、delete、insert）会自动给涉及到的表加写锁

在自动加锁的情况下，MyISAN总是一次获得sql语句所需要的全部锁，这也是MyISAM表不会出现死锁的原因

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

6.2、InnoDB的锁

行级锁：开销大，加锁慢，会出现死锁，锁定力度小，发生锁冲突的概率小，并发度高

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

排它锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

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

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

间隙锁（范围锁）：当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。间隙锁都是左开右闭原则。主要作用防止幻读

自增锁：

在innodb存储引擎中，针对每个自增长的字段都有一个自增长的计数器，在对还有自增长列的表进行插入操作的时候，这个计数器会被初始化，在mysql中，我们可以执行下面的语句来得到这个计数器的当前值：select max(自增长列) from table;

当我们进行插入操作的时候，该操作会根据这个自增长的计数器的值+1赋予自增长的列，这个操作我们称之为auto-inc Locking，也就是自增长锁

自增锁是MySQL(5.1.22之后)一种特殊的锁，主要为了解决自增主键锁表的问题，引入了参数innodb_autoinc_lock_mode,该实现方式是通过轻量级互斥量的增长机制完成的，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
Simple inserts：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。
Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
Mixed-mode inserts:指其中一部分是自增长的，有一部分是确定的。

• 0：“INSERT-like” 传统：所有的insert语句通过表级的自增锁的方式进行插入，也就是说所有类型的insert都使用自增锁，生成的自增列一定是连续的，由于语句结束才会释放这把锁，这就影响到了并发的插入，并发也是最低的。
• 1："Simple inserts" 连续：默认值，对于insert、replace等能够确定插入数据量的操作会使用一个更轻量级的互斥自增长机制去实现，官方称之为mutex（也就是说，一次性插入的值可以立马得到，所以mysql可以一次生成几个连续的值，语句得到了相应值后就会提前释放锁）。而对于load data，insert…select…这种未知数量的插入操作，使用普通的auto-inc Locking方法。这种方式下，不考虑回滚操作，生成的id是连续的。如果有回滚操作，则不一定连续。
• 2："Bulk inserts" 混合对所有的insert自增长值的产生使用互斥锁（立马获得并释放），并发性能最高，并发插入可能导致自增值不连续，可能会导致Statement 的 Replication 出现不一致，使用该模式，需要用 Row Replication的模式。

需要注意一下三点：

1. 自增列使用了auto-inc Locking锁，插入的时候仅仅需要等待上一条记录插入完成，不必等待上一个事务提交。

2. innodb中，自增列必须作为索引，如果是联合索引，必须放在第一列。

3. innodb_autoinc_lock_mode参数可以控制自增列的锁模式，数字越大，效率越高，安全性越差。

6.3、加锁方式：

• 意向锁是InnoDB自动加的，不需要用户干预
• 对于UPDATE、DELETE、INSERT，InnoDB会自动给涉及到的数据集加排它锁（X）
• 对于普通的select语句，InnoDB不会加任何锁
• 事务可以通过以下语句显示的加锁
  • 共享锁（S）：SELECT * FROM table_name WHERE …LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE …FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

6.4、InnoDB行锁的实现方式：

1. InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB行锁的这种实现特点意味着：只有通过索引条件检索数据InnoDB才会使用行级锁，否则将使用表锁
2. 只有执行计划真正使用到了索引，才会使用行锁，即便在条件中使用了索引，但是是否使用索引来检索数据是有MySQL通过判断不同的执行计划的代价来决定的，如果MySQL认为全表扫描效率更高（比如一些小表），他就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此在分析锁冲突时，应该检查执行计划，已确定是否真正使用到了索引
3. 由于MySQL的行锁,是针对索引项加的锁,不是针对某条记录加的锁，所以虽然多个session访问的是不同行的记录，但是如果使用的相同的索引键，还是会出现锁冲突（后使用加锁索引的session需要等待先使用session释放锁后才能获取锁）。

6.5、数据库悲观锁和乐观锁的原理和应用场景？

悲观锁和乐观锁是一种机制不是指具体的锁。

• 悲观锁：它指的是对数据被外界修改持悲观的态度，认为数据是不安全的,再操作数据时总有人在修改，所以在需要操作数据时，会先将数据锁住，然后再操作数据

  悲观锁通常用select…for update操作来实现悲观锁，悲观锁的流程（一锁二查三更新）

  需要注意的是： 当我执行select quantity from items where id=100 for update后。如果我是在第二个事务中执行select quantity from items where id=100（不带for update）仍能正常查询出数据，不会受第一个事务的影响。另外，MySQL还有个问题是select…for update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

  注意：使用悲观锁，需要关闭mysql的自动提交功能，将 set autocommit = 0;

  注意：mysql中的行级锁是基于索引的，如果sql没有走索引，那将使用表级锁把整张表锁住。

• 乐观锁：对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，访问、处理数据过程中不加锁，只有提交数据更新时，才会对数据是否冲突进行检测。如果发现冲突了，则返回错误信息给用户，让用户自已决定如何操作。

  乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本

  1. 版本号控制： 利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

  2. 时间戳控制 ： 还可以使用时间戳字段，该方法同样是在表中增加一个时间戳字段，和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

6.6、死锁

死锁是指两个或者多个事务在同一资源上相互占用，并求情锁定对方占用资源，从而造成互相等待的情况，表级锁不会产生死锁

7、MySQL的日志分类

7.1、redo log（重做日志）

mysql在更新数据的时候，使用的是WAL(write-Ahead logging)预写式技术，即先写入日志，再写磁盘，当有一条记录需要更新的时候，InnoDB会先把记录记到redo log日志中，等系统空闲时再把记录更新到磁盘。

redo log是InnoDB引擎层日志，是固定大小的，可以修改配置文件配置日志文件数和每个文件的大小，redo log采用循环写的方式记录，当写道结尾时会回到开头重新循环写，当数据库发生宕机，重启时可以通过redo log将未落盘的数据恢复

7.2、undo log（回滚日志）

用于存放数据被修改前的数据，主要辅助事务回滚，保证数据的一致性，还可以用于多版本并发控制（MVCC），想来也是，MVCC中不就保存了事务的版本号么！

7.3、bin log（归档日志）

也叫二进制日志，是mysql server层实现的,所有引擎都可以使用，记录了对Mysql数据库执行更改的所有操作，并且记录了语句发生时间，执行时长，操作数据等其他信息，主要用于主从复制,实现主从同步,二进制日志主要用于记录数据库变化情况,即DDL、DML，不包含DQL，开启后大概会有百分之一的性能损耗（官网数据）默认关闭

7.4、error log（错误日志）

错误日志会记录如下信息（默认开启）

• mysql执行过程中的错误信息
• mysql执行过程中的警告信息
• event scheduler 运行时所产生的信息
• mysql启动和停止过程中产生的信息
• 主从复制结构中，重启服务器IO复杂线程的启动信息

7.5、slow query log（慢查询日志）

默认关闭，记录执行成功并且执行时间超过指定的时间的sql语句都会被记录到日志中，默认为10秒

7.6、general log （通用日志）

查询日志记录数据库执行的命令，不管语法是否正确，都会被记录，由于数据库的命令多而且比较频繁，开启后查询日志可能需要不停地写入日志，增大服务器的IO压力，增加很多系统的开销，所以默认关闭，但是开启查询日志也能帮助我们分析哪些语句执行密集，执行密集的select语句是否可以优化、添加缓存，所以可以根据实际情况打开或者关闭。

通用日志包括以下三个参数：

• general_log 是否开启
• general_log_file 默认日志的输出文件
• log_output 以何种形式进行记录 可选值 TABLE, FILE, 或者 NONE，默认值是FILE，可以同时选择TABLE和FILE, 当我们选择TABLE的时候可以执行以下语句查看 select * from mysql.general_log;

8、MySQL索引

什么是索引？

索引是一种排好序的数据结构，可以帮助我们快速查找数据

mysql中使用比较多的B+树索引（InnoDB、MyISAM）,Hash索引（momory）

索引的优缺点？

• 优点

  1. 提高数据检索效率，降低数据库IO成本
  2. 降低数据排序的成本，降低CPU的消耗

• 缺点

  1. 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

  2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

索引的分类

逻辑分类：主键索引、唯一索引、普通索引（单列索引）、全文索引、组合索引（复合索引）

物理分类：聚簇索引（聚集索引）、非聚簇索引（非聚集索引）

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。 聚集索引（innodb）的叶节点就是数据节点，而非聚集索引(myisAM)的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

哪些情况需要创建索引？

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题，高并发下倾向创建组合索引
• 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
• 查询中统计或分组字段

哪些情况不要创建索引？

• 表记录太少
• 经常增删改的表
• 数据重复且分布不均匀的表字段，只应该为经常查询和经常排序的数据列建立索引（如果某个列包含太多重复数据，建立索引没有太大意义）
• 频繁更新的字段不适合创建索引（会加重IO负担）
• where条件里用不到的字段不创建索引

什么情况会导致索引失效？

1. 如果查询条件有or，即使天剑带索引也不会使用，要想使用or又想使用索引，只能将or中的每个列都加上索引
2. 对于组合索引，要遵循最左匹配原则
3. like查询以%开头，不走索引
4. 索引列参与运算、对索引列使用函数，不走索引
5. !=，不走索引
6. 全表扫描要比使用索引快，不走索引
7. where条件对字段进行了is null判断，不走索引
8. 字符串不加单引号索引失效

9、MySQL的索引覆盖、索引下推、回表、最左匹配

最左匹配原则：mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先

• 对于联合索引，MySQL会一直向右直到遇见范围查询（>,<,between,like）就停止匹配。比如a=3 and b=4 and c>5 and d=6,如果建立的是（a,b,c,d）这种顺序的联合索引，那么d就用不到索引，但是如果是（a,b,d,c）这种顺序，那就没问题，而且a,b,d的顺序可以随意调换
• =和in可以乱序，比如：a=3 and b=4 and c=5建立索引可以任意顺序（优化器会按照索引重新排列位置）
• 如果建立的索引顺序是（a,b）那么查询条件where b=5是无法利用索引的，这一条最能体现最左匹配原则

回表：普通索引的B+树索引结构存储的并不是全部数据，回表就是先通过普通索引扫描出数据所在行，在通过行主键取出索引中未提供的数据，即通过非主键索引的查询需要多扫描一个索引树🌲

索引覆盖：如果一个索引包含所有需要查询字段的值，就成为覆盖索引，即只需扫描索引无需回表

索引下推：简称ICP。MySQL5.6新添加，用于优化数据的查询。

• 当不使用ICP时：通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过检索数据，然后返回给MySQL服务器，服务器在判断是否符合条件

• 当使用ICP时：当存在索引的列作为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务传递的条件，只有当索引符合条件时，才会将数据检索出来返回给MySQL服务器

  示例：当我们创建一个用户表(userinfo),其中有字段：id,name,age,addr。我们将name,age建立联合索引。

  当我们执行：select * from userinfo where name like "ming%" and age=20;
  

  对于MySQL5.6之前：我们在索引内部首先通过name进行查找，在联合索引name,age树形查询结果可能存在多个，然后再拿着id值去回表查询，整个过程需要回表多次。

  对于MySQL5.6之后：我们是在索引内部就判断age是否等于20，对于不等于20跳过。因此在联合索引name,age索引树只匹配一个记录，此时拿着这个id去主键索引树种回表查询全部数据，整个过程就回一次表。

  固索引下推可以有效减少回表次数，从而提高效率

10、innodb为什么要用自增id作为主键？

如果表使用自增主键，那么每次插入新的数据就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页，形成一个紧凑的索引结构，如果使用非自增主键（uuid、身份证号等），每次插入的值近似于随机的，因此每次新数据都会插入到索引页的中间某个位置，就会造成频繁的数据移动、索引页的分裂，造成了大量碎片

11、drop、delete与truncate的区别？

delete语句执行删除是每次从表中删除一行数据，并将该删除记录记录到日志中，以便需要时进行回滚操作

truncate与不带where的delete都只是删除表的数据，而不删除表结构

drop语句将删除表结构被依赖的约束、触发器、索引（依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid）

12、主从复制流程

两个线程以及两个日志的关系

13、mysql的执行过程

客户端请求 —> 连接器（验证用户身份，给予权限） —> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） —> 分析器（对SQL进行词法分析和语法分析操作） —> 优化器（主要对执行的sql优化选择最优的执行方案方法） —> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） —> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）