MySQL面试锦囊

编程小学生哈
204 阅读33分钟

数据库架构相关

问题：简单介绍下MySQL体系架构是怎么样的？

答：可以简单概括为：Client Connectors 层、MySQL Server 层及存储引擎层组成。 image.png

  • Client Connectors 层：负责处理客户端的连接请求，与客户端创建连接。
  • MySQL Server 层：
    • 连接器: 管理连接, 权限验证;
    • 分析器: 词法分析, 语法分析;
    • 优化器: 执行计划生成, 索引的选择;
    • 执行器: 操作存储引擎, 返回执行结果;
  • 存储引擎: 存储数据, 提供读写接口。

问题：为啥要 mysql 驱动？

答：我们不可能自己去写代码和 mysql 服务器建立 tcp、socket 连接，因此 mysql 给各个语言都提供了 mysql 驱动，封装了最底层的网络通信，提供 Connection 连接对象。基于这个 Connection 连接就可以和 mysql 服务器通信了，比如增删改查。

问题：为啥要数据库连接池？

答：tomcat 容器是多线程的，他们去抢夺一个 connection 去访问数据库的话，性能肯定低。 每个线程都去创建使用和销毁 connection 的话，建立网络连接是耗时的。

那么池子的好处是： 一批建立好连接的 connection 扔到池子里面去，用的时候去池子里面拿，不用的时候还到池子里面，也不去销毁，后续可以继续使用。 解决了并发建立 connection 和 connection 销毁的问题。

问题：mysql server 端为什么要连接池

很多客户端系统需要和mysql服务端建立连接，因此服务端要需要一个连接池，来维持和客户端的连接 还需要进行账号密码的认证。

问题：一条SQL的处理流程？

image.png

问题：数据在磁盘是如何存放的？

逻辑上，我们有库、表、行的概念，在实际物理存储的时候，每个表里面的行的数据都是按照一页一页来存储的，一页的大小是16kb。

问题：数据如何存放在buffer pool？

有了buffer pool这样一个内存的容器，磁盘的数据必定需要按照一定的约定和格式存放进去。mysql的数据在磁盘上是16kb的大小存放在一页上，定位一行数据，需要先找到这一页，那么对应的读取一页的数据，也是按照一页的形式存放在buffer pool里面的，物理和内存结构映射，就是一个一个的缓存页和物理页对应（这就有了一个描述数据，里面存放这页数据、在buffer pool的地址、以及对应的物理表空间、页编号等信息，这样就可以将缓存页和物理页对应了）

InnoDB存储引擎的架构设计

问题：redo日志如何保证数据能还原？

有三种模式

  • redo log 实时刷盘强制更新到磁盘
    事务里面更新数据的同时，把这条数据更新后的字段，记录到redo日志内存缓冲池里面。 事务提交的时候，策略innodb_flush_log_at_trx_commit=1，redo必须刷盘并强制更新到磁盘，然后事务才能成功。 系统再次恢复的时候，就能根据redo日志来重做缓冲区脏数据 但是，在这种策略下，事务的写入速度就会大大下降。

  • 其他2个策略：
    如果innodb_flush_log_at_trx_commit=0，就代表mysql定时器每秒去刷盘并强制更新到磁盘 如果innodb_flush_log_at_trx_commit=2，就代表事务提交的时候先刷盘，由os自己去更新到磁盘。 0和2的模式下，很可能宕机会丢失redo日志数据。

  • flush和fsync理解如下：
    innodb_flush_log_at_trx_commit = 0 就是 定时器自己维护，每秒调用 flush + fsync。 innodb_flush_log_at_trx_commit = 1 就是实时调用 flush + fsync 没法批处理，性能很低。 innodb_flush_log_at_trx_commit = 2 就是实时flush ,定时 fsync 交给OS维护定时器。
    如果要保证缓冲池的数据0丢失，则值为1；
    如果高效的写入操作，不考虑数据的一致性，则值为0；
    如果即想高速写入又保证数据丢失少，则值为2；

问题：什么是binlog？

binlog是属于mysql server自己的日志文件，叫做归档日志，不是InnoDB存储引擎特有的日志文件。 是一种逻辑性的日志，比如： update table set xxx wherer id=1， 而 redo 日志记录的是对哪个数据页中的什么记录，做了个什么修改。

问题：binlog什么时候写入磁盘？

提交事务阶段：redo log flush，mysql执行器会将binlog flush到磁盘。

问题：binlog 刷盘策略？

sync_binlog参数可以控制binlog的刷盘策略，他的默认值是0
把binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存缓存。 也就是实时flush ,定时 fsync 交给OS维护定时器，因此会丢数据

sync_binlog=1,就是flush+fsync，必须实时刷盘强制更新磁盘。

问题：如何才算事务提交成功？

redolog flush + binlog flush 之后，还需要把写入binlog的文件名和写入位置，以及commit标记更新到redolog。才算事务提交成功。

问题：为什么事务提交成功要在redo日志写commit标记？

保证redolog和binlog的一致性，只有redolog和binlog都写入成功了，才能判定事务提交成功。 否则只有redolog成功，binlog没有成功，或者binlog日志位置信息没有写入redolog，都判定事务不成功。 事务判定成功的三个步骤：redolog刷盘+binlog刷盘+commit标记写入redo日志

问题：io线程刷盘内存缓冲区的脏数据

事务已经提交成功了，但是内存缓冲区里面的数据可能还没有刷新到磁盘。有一个io线程会在某个时间，把脏数据刷新到磁盘。

如果系统宕机了，系统恢复之后，可以根据redo日志重做缓冲区的脏数据。之后io线程又可以继续刷盘了。

事务相关

问题：MySQL事务的隔离级别, 分别有什么特点？这些隔离级别怎么实现的？

答：

  • 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到，会出现脏读，每次都是读的MVCC最新的版本。
  • 读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到，会出现不可重复读，幻读，实现机制依靠MVCC，每一次进行普通SELECT操作前都会生成一个ReadView。
  • 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的，实现机制依靠MVCC，只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了
  • 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行，每次都也需要加锁实现。

问题：介绍一下可重复读隔离级别？

答：可重复读隔离级别，事务 T 启动的时候会创建一个视图 read-view，之后事务 T 执行期间，即使有其他事务修改了数据，事务 T 看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。

问题：可重复读适用场景：

答：假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

问题：什么是幻读？怎么解决幻读?

答：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

  • 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  • 幻读仅专指“新插入的行”，对于其他行修改操作不属于幻读。

解决幻读方式有两种：

  • 一是使用MVCC机制。
  • 加gap锁。

问题：InnoDB为什么可以支持非锁定读呢？

答：InnoDB支持非锁定读是依靠MVCC实现的。而MVCC依靠的是undo日志实现的。undo日志中保存了多版本的记录，undo支持事务回滚的同时，也支持数据的一致性读。

问题：MVCC有什么作用？

答：MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，ReadView保存当前正在执行的事务信息。ReadView用于判断可见性。
READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

总结一下：

  • 两个前提

    1. 每条完整的记录都在聚集索引叶子节点里面。
    2. 每个数据都有多个版本，多个版本保存在undo日志里面，每条数据都有一个事务ID(trx_id)和上一个版本指针（roll_pointer），保存在undo日志里面的也有。这样undo日志里面的数据就可以通过roll_pointer关联找到，并且每个数据都有一个这个数据改动时的事务ID。每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表。 image.png 上面这图表示一行数据在主键索引叶子节点的存储或者在undo日志里存储格式。 image.png 上面这幅图就是undo日志中多版本组成了版本链

  • 有了上面两个前提，就可以分析READ COMMITTD、REPEATABLE READ隔离级别在MVCC下是怎么实现的。
    对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，设计InnoDB的大叔提出了一个ReadView的概念。
    ReadView中包含

    • m_ids ：当前活跃事务（正在执行的事务和未开启的事务）
    • min_trx_id：活跃的最小事务，m_ids中的最小值。
    • max_trx_id：ReadView时系统中应该分配给下一个事务的id值。
    • creator_trx_id：ReadView的事务的事务id 那么怎么判断版本链中的版本对当前视图可见呢？那就需要讲版本链中的版本事务ID和ReadView的内容比较：
    • 被访问版本的trx_id = ReadView中的creator_trx_id，表示前事务在访问它自己修改过的记录，可见
    • 被访问版本的trx_id < ReadView中的min_trx_id，表示被访问的版本在当前事务生成ReadView前已经提交，可见。
    • 被访问版本的trx_id >= ReadView中的max_trx_id，表明生成该版本的事务在当前事务生成ReadView后才开启，不可见。
    • ReadView中的min_trx_id < 被访问版本的trx_id < ReadView中的max_trx_id,需要判断trx_id在不在ReadView视图的m_ids中，在，表示活跃事务，不可见，不在，表示历史事务，可见。 接下来就是比较了。如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。如下图所示：
      image.png

问题：事务没提交，更新的数据要回滚怎么办？

在内存修改了数据，但是后面需要回滚怎么办？ 在更新数据之前，把这条记录需要改修的字段的信息记录到磁盘上的undo日志里面，后面回滚的时候在提取出来还原。

问题：事务没提交，为什么会出现脏数据？

事务没有提交，在缓冲池修改了数据，此时内存数据和磁盘上存储的数据不一样，这条数据就成了脏数据。

问题：事务没有提交，系统宕机了怎么办？

事务还没有提交，磁盘上的数据还没有修改，系统宕机了，客户端会收到一个数据库异常，此时对系统没有任何影响，等mysql恢复了，磁盘上的数据还是原来的数据。此时恢复了对事务进行回滚

问题：事务提交过程中，系统宕机了怎么办？

redo log prepare阶段： 已经flush redo log了，没有flush binlog，此时，crash，系统恢复后，事务回滚 已经flush redo log了，也flush binlog了，此时，crash，系统恢复后，会继续提交

问题：事务提交之后，系统宕机了怎么办？

redo log commit了，commit字段更新了，此时crash，可能缓冲池里面的数据还没有刷盘，缓冲池里面的数据就没了。为了能够还原缓冲池的状态就引入了redo日志。

redo log，是一种偏向物理性质的重做日志，因为他里面记录的是类似这样的东西， “对哪个数据页中的什么记录，做了个什么修改”。

问题：MySQL 是怎么解决脏读、不可重复读、幻读现象的？

答：其实有两种可选的解决方案

  • 方案一：读操作利用多版本并发控制（MVCC），写操作进行加锁。
    MVCC解决方式是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本是由undo日志构建的），查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。
    而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
    ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象。而REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。
    使用MVCC的读通常称为一致性读，或者一致性非锁定读。

  • 方案二：读、写操作都采用加锁的方式。
    脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

    不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

    幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录，我们把新插入的那些记录称之为幻影记录。 但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上行锁，所以出现了Gap Locks。 使用锁的读写被称为锁定读。

问题：begin/start transaction和transaction with consistent snapshot区别？

答：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

索引相关

问题：索引的常见模型有哪些？

答：

  • 哈希表：哈希表这种结构适用于只有等值查询的场景，对范围查询不友好。
  • 有序数组：在等值查询和范围查询场景中的性能就都非常优秀。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。
  • 搜索树

问题：为啥不使用二叉树而是使用多叉树？

答：二叉树每个节点只有两个儿子，多叉树每个节点可以有多个儿子，所以同样的节点数，二叉树的书高一定高于多叉树的书高，所以二叉树需要访问的次数就多，访问的数据块也就多，也就需要访问很多磁盘。 引用某衫老师的文章

问题：主键索引和普通索引有什么区别？

答：主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。
非主键索引需要回表。回表是随机IO。

问题：B+ Tree索引和Hash索引有什么区别？

答：两者主要有以下区别：

  • 哈希索引适合等值查询，但是不无法进行范围查询。
  • 哈希索引没办法利用索引完成排序，因为是(K,V)结构，多个数据存储上没有顺序，自然无法排序。
  • 哈希索引不支持多列联合索引的最左匹配规则。
  • 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

问题：做过哪些MySQL索引相关优化？

答：

  • 尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗。
  • MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗。
  • 若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表（所以 select * 要注意避免）。
  • 联合索引字段的顺序选择要从复用性上和空间使用上考虑，复用性上，根据最左前缀原则，高频字段放在最左边。空间使用上，如果两个字段既要分开查询又要联合查询，可以使用字段小的单独创建一个索引，字段大的和字段小的组成联合索引，字段大的在前面。

问题：自增主键有什么好处？

答：

  • 从性能上讲，自增主键符合递增插入的场景，所以每次都是追加，好处就是是顺序写，效率高，而且不移动其他记录，不涉及页分裂。业务逻辑做主键则往往不容易保证有序插入，这样写数据成本相对较高。
  • 从空间利用上讲，假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，如果使用身份证号做主键。每个非主键索引的叶子节点都是存储了主键的值，所以非主键索引的叶子节点大约需要20字节，如果使用整型做主键只要4字节，长整型8字节。
    显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

问题：有没有什么场景适合用业务字段直接做主键的呢？

答：有的。比如，有些业务的场景需求是这样的：

  1. 只有一个索引；
  2. 该索引必须是唯一索引。
    这就是典型的 KV 场景（比如说字典表）。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。 这时候我们就要优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

问题：在下面这个表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

答：初始化脚本

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
 
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

image.png 现在，我们一起来看看这条 SQL 查询语句的执行流程：

  1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
  2. 再到 ID 索引树查到 ID=300 对应的 R3；
  3. 在 k 索引树取下一个值 k=5，取得 ID=500；
  4. 再回到 ID 索引树查到 ID=500 对应的 R4；
  5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。 在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

问题：select k from T where k between 3 and 5 和 select * from T where k between 3 and 5有什么区别？（T表结构和上一题一样）

答：由于对字段K建立了索引，所以第一条语句直接查K可以不用回标，这个叫索引覆盖，而第二条语句，查询所有，在K的索引树上，叶子节点存储的是k的值和ID值，所以没有字段s的值，因此需要回表。

问题：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？表解构如下：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

答：身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。但是如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。 当然，索引维护也是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

问题：在建立联合索引的时候，如何安排索引内的字段顺序？

答：评估标准是

  • 索引的复用能力
    因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
    另外，在创建多列索引时，我们根据业务需求，where子句中使用最频繁的一列放在最左边，因为MySQL索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
  • 考虑空间使用
    如果既有联合查询，又有基于 a、b 各自的查询，查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时的原则就是哪个字段小为哪个字段单独建立索引。比如上面市民表，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

问题：索引下推是什么？基于上面市民表分析

答：以上面市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

根据最左前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，然后找到和"张"相关的记录根据主键一个个回表吗？ 假设市民表中以（name, age）的索引如下图： image.png 那么MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。如下图： image.png

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 image.png 这就是索引下推。
所以，索引下推主要作用是减少回表次数，并且它是MySQL5.7做的优化。

问题：怎么给字符串字段加索引？

答：假设有个邮箱登录的系统，用户表设计：

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb;

使用邮箱查询一般SQL语句：mysql> select f1, f2 from SUser where email='xxx'; 而对于email，可以如下创建索引：

mysql> alter table SUser add index index1(email); // 创建完整索引
或
mysql> alter table SUser add index index2(email(6));// 创建前缀索引

创建完整索引就得到类似下图这样的 image.png

创建前缀索引就得到类似下图这样的 image.png 从图中你可以看到，由于 email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。
但，这同时带来的损失是，可能会增加额外的记录扫描次数。因为第二种的区分度不高，如果执行select id,name,email from SUser where email='zhangssxyz@xxx.com';这条语句，使用完整索引只需要找一次就找到了，而使用前缀索引由于前缀不完整，它只能过滤不是这个前缀的，所以它需要四次回表比较。
也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。
而且前缀索引不止影响回表次数，而且它还不支持覆盖索引，因为在二级索引上，它只存了部分数据，所以必须回表找到完整数据，因此覆盖索引用不到。
如果遇到前缀区分度不高，比如，我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。要是获取查同一个县，以前6为做索引，那区分度就很低了，但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。这时候，我们可以使用倒序存储，再创建前缀索引，这样区分度就高了。
还可以使用 hash 字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

总结 ：

  • 直接创建完整索引，这样可能比较占用空间；
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

问题：如果代码已经控制了数据的唯一性，从性能的角度考虑，你选择唯一索引还是普通索引呢？

答：这个选择要从查询和更新的角度来分析，举个例子，如下图： image.png

  • 查询过程
    假设执行select id from T where k=5。

    • 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
    • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。 那么唯一索引性能高？其实差不多，因为InnoDB的数据是按页来读取的，数据页默认16k，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。如果K=5这个记录刚好在这个页的最后一条记录，那要麻烦一点，但是这种概率很小。所以总体来说，查询的性能差异不大的。

  • 更新过程

    • 对于普通索引来说，如果更新一个数据页时，如果数据页在内存中就直接更新，如果这个数据页不在内存中，那么它会写入change buffer（可以被持久化的）之后会被应用到原始页中，这个过程称为 merge 。

    • 而对于唯一索引，如果更新一个数据页时，如果数据页在内存中则判断到没有冲突，没有冲突就更新。如果这个数据页不在内存中，因为它要判断是否冲突，所以它不能用change buffer，只能从磁盘上读取数据页，并判断更新。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

    回到问题，普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议尽量选择普通索引。

问题：change buffer 的使用场景？

答：使用 change buffer 对更新过程的加速作用， change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。但是不是所有的普通索引都适用change buffer。 change buffer 的主要目的就是将记录的变更动作缓存下来。所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

问题：change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢？

答：不会丢失。虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

问题：DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。 但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？ 同事告诉他，是因为他们的业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？ 答：ca索引可以去掉，cb索引可以保留。 ca索引，通过索引对数据进行筛选，回表的时候，a本身就是主键索引，所以可以保证有序； cb索引，b上并没有索引，ab索引也无法满足最左匹配原则，可以保留加快排序速度。

问题：哪些情况需要创建索引？

答：

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

问题：哪些情况不能创建索引？

MySQL锁相关

问题：锁可以分为哪几种？

答：根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

  • 全局锁
    全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。全局锁的典型使用场景是，做全库逻辑备份。当然如果不使用全局锁，也可以使用可重复读隔离级别下开启一个事务来完成备份，因为可重复读隔离级别下能确保拿到一致性视图。
    你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

    也许你会问既然要全库只读，为什么不使用 set global readonly=true 的方式呢？原因在于

    • 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。

    • 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

  • 表级锁 表级锁分为两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

    • 表锁
      表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

    • 元数据锁（MDL）
      MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性，可以分为读锁和写锁。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

  • 行锁
    MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。
    行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
    在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。正是因为这一点，所以如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。因为这样，最有可能造成冲突的那一行的行锁在一个事务中不会停留很长时间。

问题：你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？

问题：如何安全地给小表加字段？

答：首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
但是如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，这时候 kill 可能未必管用，因为新的请求马上就来了。
比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

问题：如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。
你会选择哪一种方法呢？为什么呢？
答：第二种方式是相对较好的。
第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

数据库规划

问题：数据库的机器配置

一台机器能抗下每秒多少请求，往往是跟你每个请求处理耗费多长时间是关联的，根据经验值而言，Java应用系统部署的时候常选用的机器配置大致是2核4G和4核8G的较多一些。4核8g，按500个请求来配置，根据任务时长，从一两百到七八百都有可能，java应用的内存运算性能是很高的，压力都在和外部通信上。
数据库部署的时候常选用的机器配置最低在8核16G以上，正常在16核32G，推荐16核以及以上的配置。
8核16G，按一两千的请求来配置，再高可能有危险，因为数据库的cpu、磁盘、io、内存负载都会很高，数据库压力过大就会宕机。
16核32G，按两三千的请求来配置，甚至三四千也是有可能，再高的话，cpu、磁盘、io、内存、网络负载都会很高，数据库也有可能会扛不住宕机。
如果是ssd磁盘，能抗的并发可能会更高一点，数据库最大的复杂就在于磁盘的io，需要大量的读写磁盘文件。

问题：QPS、TPS区分

  • QPS：每秒可以处理的请求数
    QPS可以理解为每秒的请求数量，TPS可以理解为每秒完成的业务量，比如交易这个整体的业务行为。

  • TPS：每秒可以处理的事务数
    TPS在事务中用的会比较多一些，他是说数据库每秒能处理多少次事务的commit和rollback。

问题：数据库IO压测指标

  • IOPS：机器随机IO并发处理的能力
    比如说200IOPS，意思是每秒可以执行200个随机io读写请求。这个是关键指标，因为后台IO线程去刷盘缓冲区的脏数据，是在不确定的时间里面刷回去的，是一个随机IO的过程。如果说IOPS指标太低，说明刷回效率就比较低。

  • 吞吐量：机器每秒可以读写多少字节的数据量
    这个也是关键指标,提交事务的时候，会大量的写redo log之类的日志，这些日志都要写磁盘。一台机器它的存储可以每秒读写多少字节的数据量，就决定了可以把多少redo log之类的日志写到磁盘。一般来说我们写redolog、binlog都是顺序写，那么一般磁盘的顺序写入，每秒的吞吐量可以达到200MB左右，通常机器的磁盘吞吐量都是足够承载高并发请求的

  • latency延迟：往磁盘写入一条数据的延迟。
    这个也是关键指标，执行sql的时候，写redo日志到磁盘，到底延迟到1ms还是多少，会影响到sql语句的执行性能。磁盘写入延迟越低，数据库性能越好，sql的性能就越高。

  • CPU负载

  • 网络负载 看每秒网卡的输入输出多少MB的数据，一般1000MB的网卡，最大传输100MB的数据。

  • 内存负载

其他

问题：select count(*) 为什么比较慢？

答：select count(*)慢其实是指的InnoDB存储引擎，因为它需要把数据一行一行地从引擎里面读出来，然后累积计数。MyISAM 中 count(*) 是很快，因为它是个统计值，直接返回就行。那为什么InnoDB不能用统计值呢？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

问题：select count(*)这么慢，该怎么办呢？

答：我们可以把计数直接放到数据库里单独的一张计数表 C 中。但是不能放入redis中。
把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。而把计数值也放在 MySQL 中，就解决了一致性视图的问题。也就是由于MVCC(多版本并发机制)的存在不同事务看到的总数是不样的，所以直接放入redis是不行的。

问题：count(*)、count(主键 id)、count(字段) 和 count(1) 我该用哪个统计？

答：首先 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。
所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

  • 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

  • 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

  • 对于 count(字段) 来说：

    • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

    • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

  • 但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。 所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

问题：如果发现只查一行的语句也很慢，那是什么原因？该如何排查？

答：如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，如果不是这种原因，可以从以下方向去考虑。

  • 表级锁 一般大概率是这个表被锁住了，可以使用show processlist查看语句什么状态。 image.png 上面这个就是出现了MDL锁(元数据锁)，这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉，可以通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

  • 行锁 MySQL5.6之后通过select * from t sys.innodb_lock_waits where locked_table='test'.'t'\G，找到之后然后kill。

  • 慢查询
    如果上面都不符合，可以开启慢查询分析，比较下面两句：

     select * from t where id=1；
 select * from t where id=1 lock in share mode

    如果第一句很慢，第二句很快，那可能是MVCC导致的，带 lock in share mode 的 SQL 语句，是当前读，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，需要当前读到的依次执行 undo log。

问题：假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’，

mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设现在执行语句是这么写的: mysql> select * from table_a where b='1234567890abcd';，这时候，MySQL 会怎么执行呢？

答：最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。可惜，MySQL 并没有这么做。
那要不，就是把’1234567890abcd’拿到索引里面去做匹配，肯定也没能够快速判断出索引树 b 上并没有这个值，也很快就能返回空结果。
但实际上，MySQL 也不是这么做的。
这条 SQL 语句的执行很慢，流程是这样的：
在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；
这样满足条件的数据有 10 万行；
因为是 select *， 所以要做 10 万次回表；
但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
返回结果是空。

avatar
文章
阅读
粉丝
相关推荐
MySQL优化面试
30k阅读
 · 
505点赞
mysql索引相关面试题
15k阅读
 · 
21点赞
面试官问我MySQL索引
15k阅读
 · 
238点赞
MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列）
112k阅读
 · 
1.5k点赞
面试分享：两年工作经验成功面试阿里P6总结
327k阅读
 · 
3.9k点赞