枫叶🍁 枫叶🍁
/ 面经 / 33浏览

Mysql 面试题

💾 db-tutorial 是一个数据库教程。https://github.com/dunwu/db-tutorial

排他锁

原理是：如果一个事务需要一条数据它就把数据锁住
如果另一个事务也需要这条数据
它就必须要等第一个事务释放这条数据
这个锁叫排他锁。

但是对一个仅仅读取数据的事务使用排他锁非常昂贵，因为这会迫使其它只需要读取相同数据的事务等待。因此就有了另一种锁，共享锁。

共享锁

如果一个事务只需要读取数据A
它会给数据A加上『共享锁』并读取
如果第二个事务也需要仅仅读取数据A
它会给数据A加上『共享锁』并读取

如果第三个事务需要修改数据A
它会给数据A加上『排他锁』，但是必须等待另外两个事务释放它们的共享锁。

同样的，如果一块数据被加上排他锁，一个只需要读取该数据的事务必须等待排他锁释放才能给该数据加上共享锁。

数据库 update操作是获取的是行级锁还是表级锁 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

哪些操作会获取表锁 如果是一次的插入操作呢

如何显式地对一个行加锁

事务可以通过以下语句显示给记录集加共享锁或排锁。 事务commit或者rollback后锁才会被释放

共享锁（Ｓ）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE
用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT … FOR UPDATE方式获取排他锁。

数据库里索引有什么价值，为什么能快速地检索到我们需要的数据？检索的流程逻辑是什么样的？

事物的4种隔离级别

读未提交(RU)
读已提交(RC)
可重复读(RR)
串行

1、MySQL 中有哪几种锁？

2、MySQL 中有哪些不同的表格？

3、简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别?

4、MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

5、CHAR 和 VARCHAR 的区别？

6、主键和候选键有什么区别？

7、myisamchk 是用来做什么的？

8、如果一个表有一列定义为 TIMESTAMP，将发生什么？

9、你怎么看到为表格定义的所有索引？

10、LIKE 声明中的％和_是什么意思？

11、列对比运算符是什么？

12、BLOB 和 TEXT 有什么区别？

13、MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么？
答；以下是 MySQL_fetch_array 和 MySQL_fetch_object 的区别：
（1）MySQL_fetch_array（） – 将结果行作为关联数组或来自数据库的常规数组返回。
（2）MySQL_fetch_object – 从数据库返回结果行作为对象。

14、MyISAM 表格将在哪里存储，并且还提供其存储格式？

15、MySQL 如何优化 DISTINCT？

16、如何显示前 50 行？

17、可以使用多少列创建索引？

18、NOW（）和 CURRENT_DATE（）有什么区别？

19、什么是非标准字符串类型？

20、什么是通用 SQL 函数？

21、MySQL 支持事务吗？

22、MySQL 里记录货币用什么字段类型好

23、MySQL 有关权限的表都有哪几个？

24、列的字符串类型可以是什么？

25、MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化?

26、锁的优化策略

27、索引的底层实现原理和优化

28、什么情况下设置了索引但无法使用
（1）以“%”开头的 LIKE 语句，模糊匹配。
（2）OR 语句前后没有同时使用索引。
（3）数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int 型）。

MySQL的锁🔒

概述

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。本文没有说明的情况下默认使用的是Innodb引擎。

Innodb原理(简单说一下🎐)：
  • innodb一定存在聚簇索引，默认以主键作为聚簇索引
  • 有几个索引，就有几棵B+树(不考虑hash索引的情形)
  • 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引B+树。
锁的分类🎏
  • 共享锁(S锁): 假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。
  • 排他锁(X锁): 假设事务T1对数据A加上排他锁，那么事务T2不能读数据A，不能修改数据A。
  • 我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。
  • 意向共享锁(IS锁): 一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。
  • 意向排他锁(IX锁): 一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。
意向锁存在的目的🎑

这里说一下意向锁存在的目的。假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

加锁算法🔏
  • Record Locks： 简单翻译为行锁。注意了，该锁是对索引记录进行加锁！锁是加在索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！
  • Gap Locks： 简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。这里我对官网补充一下，隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。
  • Next-Key Locks： 这个理解为Record Lock+索引前面的Gap Lock。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，间隙锁的范围如下
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

还有不懂的地方可以看一下MySQL的官方文档👉：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

快照读和当前读

在mysql中select分为快照读和当前读，执行下面的语句
select * from table where id = ?;
执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立，后面会再补充。）
那么，执行
select * from table where id = ? lock in share mode;
会对读取记录加S锁 (共享锁)，执行
select * from table where id = ? for update
会对读取记录加X锁 (排他锁)，那么加的是表锁还是行锁呢？

表锁or行锁 🎭

针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:

  • Read Uncommited(RU)： 读未提交，一个事务可以读到另一个事务未提交的数据！
  • Read Committed (RC)： 读已提交，一个事务可以读到另一个事务已提交的数据!
  • Repeatable Read (RR): 可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免!另外就是记住从该级别才开始加入间隙锁(这句话记下来，后面有用到)!
  • Serializable： 串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

那么关于是表锁还是行锁，大家可以看到网上最流传的一个说法是这样的

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

这句话大家可以搜一下，都是你抄我的，我抄你的。那么，这句话本身有两处错误！
错误一: 并不是用表锁来实现锁表的操作，而是利用了Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现锁表的操作!

为了便于说明，我来个例子，假设有表数据如下，pId为主键索引

pId(int)name(varchar)num(int)
1aaa100
2bbb200
7ccc200

执行语句(name列无索引)
select * from table where name = `aaa` for update

那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此，给人一种整个表锁住的错觉！

ps: 对该结论有疑问的，可自行执行show engine innodb status;语句进行分析。

错误二: 所有文章都不提隔离级别！
注意上面说的，之所以能够锁表，是通过行锁+间隙锁来实现的。那么，RU和RC都不存在间隙锁，这种说法在RU和RC中还能成立么？
因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！

分析 💪
下面来对开始的问题作出解答，假设有表如下，pId为主键索引

pId(int)name(varchar)num(int)
1aaa100
2bbb200
3bbb300
7ccc200

RC/RU+条件列非索引

  1. select * from table where num = 200
    不加任何锁，是快照读。
  2. select * from table where num > 200
    不加任何锁，是快照读。
  3. select * from table where num = 200 lock in share mode
    当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。
  4. select * from table where num > 200 lock in share mode
    当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级S锁，采用当前读。
  5. select * from table where num = 200 for update
    当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。
  6. select * from table where num > 200 for update
    当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级X锁，采用当前读。
RC/RU+条件列是聚簇索引

恩，大家应该知道pId是主键列，因此pId用的就是聚簇索引。此情况其实和RC/RU+条件列非索引情况是类似的。

  1. select * from table where pId = 2
    不加任何锁，是快照读。
  2. select * from table where pId > 2
    不加任何锁，是快照读。
  3. select * from table where pId = 2 lock in share mode
    在pId=2的聚簇索引上，加S锁，为当前读。
  4. select * from table where pId > 2 lock in share mode
    在pId=3，7的聚簇索引上，加S锁，为当前读。
  5. select * from table where pId = 2 for update
    在pId=2的聚簇索引上，加X锁，为当前读。
  6. select * from table where pId > 2 for update
    在pId=3，7的聚簇索引上，加X锁，为当前读。
为什么条件列加不加索引，加锁情况是一样的？

其实是不一样的。在RC/RU隔离级别中，MySQL Server做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程！

RC/RU+条件列是非聚簇索引

我们在num列上建上非唯一索引。此时有一棵聚簇索引(主键索引，pId)形成的B+索引树，其叶子节点为硬盘上的真实数据。以及另一棵非聚簇索引(非唯一索引，num)形成的B+索引树，其叶子节点依然为索引节点，保存了num列的字段值，和对应的聚簇索引。

接下来分析开始

  1. select * from table where num = 200
    不加任何锁，是快照读。
  2. select * from table where num > 200
    不加任何锁，是快照读。
  3. select * from table where num = 200 lock in share mode
    当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。
  4. select * from table where num > 200 lock in share mode
    当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级S锁，采用当前读。
  5. select * from table where num = 200 for update
    当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。
  6. select * from table where num > 200 for update
    当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级X锁，采用当前读。
RR/Serializable+条件列非索引

RR级别需要多考虑的就是gap lock，他的加锁特征在于，无论你怎么查都是锁全表。如下所示
接下来分析开始

  1. select * from table where num = 200
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  2. select * from table where num > 200
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  3. select * from table where num = 200 lock in share mode
    在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  4. select * from table where num > 200 lock in share mode
    在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  5. select * from table where num = 200 for update
    在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
  6. select * from table where num > 200 for update
    在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在
    聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是聚簇索引

恩，大家应该知道pId是主键列，因此pId用的就是聚簇索引。该情况的加锁特征在于，如果where后的条件为精确查询(=的情况)，那么只存在record lock。如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock+gap lock。

  1. select * from table where pId = 2
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。
  2. select * from table where pId > 2
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
  3. select * from table where pId = 2 lock in share mode
    是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。
  4. select * from table where pId > 2 lock in share mode
    是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
  5. select * from table where pId = 2 for update
    是当前读，在pId=2的聚簇索引上加X锁。
  6. select * from table where pId > 2 for update
    在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock
  7. select * from table where pId = 6 [lock in share mode|for update]
    注意了，pId=6是不存在的列，这种情况会在(3,7)上加gap lock。
  8. select * from table where pId > 18 [lock in share mode|for update]
    注意了，pId>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock。
RR/Serializable+条件列是非聚簇索引

这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。
先说一下，唯一索引的情况。如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是:这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！大家可以自行推敲!
下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，不存在gap lock。老规矩在num列建立非唯一索引

  1. select * from table where num = 200
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
  2. select * from table where num > 200
    在RR级别下，不加任何锁，是快照读。
    在Serializable级别下，是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock
  3. select * from table where num = 200 lock in share mode
    是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
  4. select * from table where num > 200 lock in share mode
    是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。
  5. select * from table where num = 200 for update
    是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加X锁，在(100,200)(200,300)加上gap lock。
  6. select * from table where num > 200 for update
    是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock
  7. select * from table where num = 250 [lock in share mode|for update]
    注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock。
  8. select * from table where num > 400 [lock in share mode|for update]
    注意了，pId>400，查询结果是空的。在这种情况下，是在(400,+∞)上加gap lock。