小白学java-数据库知识

1. 什么是内连接、外连接、交叉连接、笛卡尔积呢？

内连接（inner join）：取得两张表中满⾜存在连接匹配关系的记录。

外连接（outer join）：不只取得两张表中满⾜存在连接匹配关系的记录，还包括某张表

（或两张表）中不满⾜匹配关系的记录。

交叉连接（cross join）：显⽰两张表所有记录⼀⼀对应，没有匹配关系进⾏筛选，它是笛

卡尔积在SQL中的实现，如果A表有m⾏，B表有n⾏，那么A和B交叉连接的结果就有m*n

⾏。

笛卡尔积：是数学中的⼀个概念，例如集合A={a,b}，集合B={1,2,3}，那么A

✖

B={<a,o>,

<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

2. 那MySQL 的内连接、左连接、右连接有有什么区别？

MySQL的连接主要分为内连接和外连接，外连接常⽤的有左连接、右连接。

inner join 内连接，在两张表进⾏连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进⾏连接查询时，会返回左表所有的⾏，即使在右表中没有匹配的记

录。

right join 在两张表进⾏连接查询时，会返回右表所有的⾏，即使在左表中没有匹配的记

录。

第⼀范式：数据表中的每⼀列（每个字段）都不可以再拆分。

例如⽤户表，⽤户地址还可以拆分成国家、省份、市，这样才是符合第⼀范式的。

第⼆范式：在第⼀范式的基础上，⾮主键列完全依赖于主键，⽽不能是依赖于主键的⼀部

分。

例如订单表⾥，存储了商品信息（商品价格、商品类型），那就需要把商品ID和订单ID

作为联合主键，才满⾜第⼆范式。

第三范式：在满⾜第⼆范式的基础上，表中的⾮主键只依赖于主键，⽽不依赖于其他⾮主

键。

例如订单表，就不能存储⽤户信息（姓名、地址）。

三⼤范式的作⽤是为了控制数据库的冗余，是对空间的节省，实际上，⼀般互联⽹公司的设

计都是反范式的，通过冗余⼀些数据，避免跨表跨库，利⽤空间换时间，提⾼性能。

4.varchar与char的区别？

char：

char表⽰定长字符串，长度是固定的；

如果插⼊数据的长度⼩于char的固定长度时，则⽤空格填充；

因为长度固定，所以存取速度要⽐varchar快很多，甚⾄能快50%，但正因为其长度固定，

所以会占据多余的空间，是空间换时间的做法；

对于char来说，最多能存放的字符个数为255，和编码⽆关

varchar：

varchar表⽰可变长字符串，长度是可变的；

插⼊的数据是多长，就按照多长来存储；

varchar在存取⽅⾯与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的

空间，是时间换空间的做法；

对于varchar来说，最多能存放的字符个数为65532

⽇常的设计，对于长度相对固定的字符串，可以使⽤char，对于长度不确定的，使⽤varchar更

合适⼀些。

5.blob和text有什么区别？

blob⽤于存储⼆进制数据，⽽text⽤于存储⼤字符串。

blob没有字符集，text有⼀个字符集，并且根据字符集的校对规则对值进⾏排序和⽐较

6.DATETIME和TIMESTAMP的异同？

相同点：

1. 两个数据类型存储时间的表现格式⼀致。均为 YYYY-MM-DD HH:MM:SS

2. 两个数据类型都包含「⽇期」和「时间」部分。

3. 两个数据类型都可以存储微秒的⼩数秒（秒后6位⼩数秒）

区别：

1. ⽇期范围 ：DATETIME 的⽇期范围是 1000-01-01 00:00:00.000000 到 9999-12-

31 23:59:59.999999 ；TIMESTAMP 的时间范围是 1970-01-01 00:00:01.000000

UTC 到 ``2038-01-09 03:14:07.999999 UTC

2. 存储空间 ：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节

3. 时区相关 ：DATETIME 存储时间与时区⽆关；TIMESTAMP 存储时间与时区有关，显⽰

的值也依赖于时区

4. 默认值 ：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认

值为当前时间(CURRENT_TIMESTAMP)

7.MySQL中 in 和 exists 的区别？

MySQL中的in语句是把外表和内表作hash 连接，⽽exists语句是对外表作loop循环，每次loop

循环再对内表进⾏查询。我们可能认为exists⽐in语句的效率要⾼，这种说法其实是不准确

的，要区分情景：

1. 如果查询的两个表⼤⼩相当，那么⽤in和exists差别不⼤。

2. 如果两个表中⼀个较⼩，⼀个是⼤表，则⼦查询表⼤的⽤exists，⼦查询表⼩的⽤in。

3. not in 和not exists：如果查询语句使⽤了not in，那么内外表都进⾏全表扫描，没有⽤到索

引；⽽not extsts的⼦查询依然能⽤到表上的索引。所以⽆论那个表⼤，⽤not exists都⽐not

in要快。

8.MySQL⾥记录货币⽤什么字段类型⽐较好？

货币在数据库中MySQL常⽤Decimal和Numric类型表⽰，这两种类型被MySQL实现为同样的

类型。他们被⽤于保存与货币有关的数据。

例如salary DECIMAL(9,2)，9(precision)代表将被⽤于存储值的总的⼩数位数，⽽2(scale)代表

将被⽤于存储⼩数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。

DECIMAL和NUMERIC值作为字符串存储，⽽不是作为⼆进制浮点数，以便保存那些值的⼩

数精度。

之所以不使⽤float或者double的原因：因为float和double是以⼆进制存储的，所以有⼀定的误

差。

9.MySQL怎么存储emoji

$

?

MySQL可以直接使⽤字符串存储emoji。

但是需要注意的，utf8 编码是不⾏的，MySQL中的utf8是阉割版的 utf8，它最多只⽤ 3 个字节

存储字符，所以存储不了表情。那该怎么办？

需要使⽤utf8mb4编码。

alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE

utf8mb4_unicode_ci not null;

11.UNION与UNION ALL的区别？

如果使⽤UNION ALL，不会合并重复的记录⾏

效率 UNION ⾼于 UNION ALL

 

 

12.count(1)、count(*) 与 count(列名) 的区别？

count(*)包括了所有的列，相当于⾏数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，⽤1代表代码⾏，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那⼀列，在统计结果的时候，会忽略列值为空（这⾥的空不是只空

字符串或者0，⽽是表⽰null）的计数，即某个字段值为NULL时，不统计。

执⾏速度：

列名为主键，count(列名)会⽐count(1)快

列名不为主键，count(1)会⽐count(列名)快

如果表多个列并且没有主键，则 count（1） 的执⾏效率优于 count（*）

如果有主键，则 select count（主键）的执⾏效率是最优的

如果表只有⼀个字段，则 select count（*）最优。

 

13.⼀条SQL查询语句的执⾏顺序？

 

1. FROM ：对FROM⼦句中的左表<left_table>和右表<right_table>执⾏笛卡⼉积

（Cartesianproduct），产⽣虚拟表VT1

2. ON ：对虚拟表VT1应⽤ON筛选，只有那些符合<join_condition>的⾏才被插⼊虚拟表VT2

中

3. JOIN ：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么

保留表中未匹配的⾏作为外部⾏添加到虚拟表VT2中，产⽣虚拟表VT3。如果FROM⼦句

包含两个以上表，则对上⼀个连接⽣成的结果表VT3和下⼀个表重复执⾏步骤1）～步骤

3），直到处理完所有的表为⽌

4. WHERE ：对虚拟表VT3应⽤WHERE过滤条件，只有符合<where_condition>的记录才被

插⼊虚拟表VT4中

5. GROUP BY ：根据GROUP BY⼦句中的列，对VT4中的记录进⾏分组操作，产⽣VT5

6. CUBE|ROLLUP ：对表VT5进⾏CUBE或ROLLUP操作，产⽣表VT6

7. HAVING ：对虚拟表VT6应⽤HAVING过滤器，只有符合<having_condition>的记录才被插

⼊虚拟表VT7中。

8. SELECT ：第⼆次执⾏SELECT操作，选择指定的列，插⼊到虚拟表VT8中

9. DISTINCT ：去除重复数据，产⽣虚拟表VT9

10. ORDER BY ：将虚拟表VT9中的记录按照<order_by_list>进⾏排序操作，产⽣虚拟表

VT10。11）

11. LIMIT ：取出指定⾏的记录，产⽣虚拟表VT11，并返回给查询⽤户

 

客户端：最上层的服务并不是MySQL所独有的，⼤多数基于⽹络的客户端/服务器的⼯具

或者服务都有类似的架构。⽐如连接处理、授权认证、安全等等。

Server层：⼤多数MySQL的核⼼服务功能都在这⼀层，包括查询解析、分析、优化、缓存

以及所有的内置函数（例如，⽇期、时间、数学和加密函数），所有跨存储引擎的功能都

在这⼀层实现：存储过程、触发器、视图等。

存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server

层通过API与存储引擎进⾏通信。这些接⼜屏蔽了不同存储引擎之间的差异，使得这些差

异对上层的查询过程透明。

 

15.⼀条 SQL 查询语句在 MySQL 中如何执⾏的？

先检查该语句 是否有权限 ，如果没有权限，直接返回错误信息，如果有权限会先查询缓

存 (MySQL8.0 版本以前)。

如果没有缓存，分析器进⾏ 语法分析 ，提取 sql 语句中 select 等关键元素，然后判断 sql

语句是否有语法错误，⽐如关键词是否正确等等。

语法解析之后，MySQL的服务器会对查询的语句进⾏优化，确定执⾏的⽅案。

完成查询优化后，按照⽣成的执⾏计划 调⽤数据库引擎接⼝ ，返回执⾏结果。

17.那存储引擎应该怎么选择？

⼤致上可以这么选择：

⼤多数情况下，使⽤默认的InnoDB就够了。如果要提供提交、回滚和恢复的事务安全

（ACID 兼容）能⼒，并要求实现并发控制，InnoDB 就是⽐较靠前的选择了。

如果数据表主要⽤来插⼊和查询记录，则 MyISAM 引擎提供较⾼的处理效率。

如果只是临时存放数据，数据量不⼤，并且不需要较⾼的数据安全性，可以选择将数据保

存在内存的 MEMORY 引擎中，MySQL 中使⽤该引擎作为临时表，存放查询的中间结

果。

使⽤哪⼀种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以⼀个数据库中多个

表可以使⽤不同的引擎以满⾜各种性能和实际需求。使⽤合适的存储引擎将会提⾼整个数据

库的性能。

 

Version:0.9 StartHTML:0000000105 EndHTML:0000011680 StartFragment:0000000141 EndFragment:0000011640

1. 存储结构：每个MyISAM在磁盘上存储成三个⽂件；InnoDB所有的表都保存在同⼀个数据

⽂件中（也可能是多个⽂件，或者是独⽴的表空间⽂件），InnoDB表的⼤⼩只受限于操作系

统⽂件的⼤⼩，⼀般为2GB。

2. 事务⽀持：MyISAM不提供事务⽀持；InnoDB提供事务⽀持事务，具有事务(commit)、回

滚(rollback)和崩溃修复能⼒(crash recovery capabilities)的事务安全特性。

3 最⼩锁粒度：MyISAM只⽀持表级锁，更新时会锁住整张表，导致其它查询和更新都会被

阻塞InnoDB⽀持⾏级锁。

4. 索引类型：MyISAM的索引为非聚簇索引，数据结构是B树；InnoDB的索引是聚簇索引，

数据结构是B+树。

5. 主键必需：MyISAM允许没有任何索引和主键的表存在；InnoDB如果没有设定主键或者⾮

空唯⼀索引，就会⾃动⽣成⼀个6字节的主键(⽤户不可见)，数据是主索引的⼀部分，附加索

引保存的是主索引的值。

6. 表的具体⾏数：MyISAM保存了表的总⾏数，如果select count(*) from table;会直接取出出该

值;

InnoDB没有保存表的总⾏数，如果使⽤select count(*) from table；就会遍历整个表;但是在加

了wehre条件后，MyISAM和InnoDB处理的⽅式都⼀样。

7. 外键⽀持：MyISAM不⽀持外键；InnoDB⽀持外键。

19.MySQL⽇志⽂件有哪些？分别介绍下作⽤？

MySQL⽇志⽂件有很多，包括 ：

错误⽇志 （error log）：错误⽇志⽂件对MySQL的启动、运⾏、关闭过程进⾏了记录，

能帮助定位MySQL问题。

慢查询⽇志 （slow query log）：慢查询⽇志是⽤来记录执⾏时间超过 long_query_time 这

个变量定义的时长的查询语句。通过慢查询⽇志，可以查找出哪些查询语句的执⾏效率很

低，以便进⾏优化。

⼀般查询⽇志 （general log）：⼀般查询⽇志记录了所有对MySQL数据库请求的信息，

⽆论请求是否正确执⾏。

⼆进制⽇志 （bin log）：关于⼆进制⽇志，它记录了数据库所有执⾏的DDL和DML语句

（除了数据查询语句select、show等），以事件形式记录并保存在⼆进制⽂件中。

还有两个InnoDB存储引擎特有的⽇志⽂件：

重做⽇志 （redo log）：重做⽇志⾄关重要，因为它们记录了对于InnoDB存储引擎的事务

⽇志。

回滚⽇志 （undo log）：回滚⽇志同样也是InnoDB引擎提供的⽇志，顾名思义，回滚⽇

志的作⽤就是对数据进⾏回滚。当事务对数据库进⾏修改，InnoDB引擎不仅会记录redo

log，还会⽣成对应的undo log⽇志；如果事务执⾏失败或调⽤了rollback，导致事务需要回

滚，就可以利⽤undo log中的信息将数据回滚到修改之前的样⼦。

20.binlog和redo log有什么区别？

bin log会记录所有与数据库有关的⽇志记录，包括InnoDB、MyISAM等存储引擎的⽇志，

⽽redo log只记InnoDB存储引擎的⽇志。

记录的内容不同，bin log记录的是关于⼀个事务的具体操作内容，即该⽇志是逻辑⽇志。

⽽redo log记录的是关于每个页（Page）的更改的物理情况。

写⼊的时间不同，bin log仅在事务提交前进⾏提交，也就是只写磁盘⼀次。⽽在事务进⾏

的过程中，却不断有redo ertry被写⼊redo log中。

写⼊的⽅式也不相同，redo log是循环写⼊和擦除，bin log是追加写⼊，不会覆盖已经写

的⽂件。

 

1. 执⾏器先找引擎获取ID=2这⼀⾏。ID是主键，存储引擎检索数据，找到这⼀⾏。如果

ID=2这⼀⾏所在的数据页本来就在内存中，就直接返回给执⾏器；否则，需要先从磁盘读

⼊内存，然后再返回。

2. 执⾏器拿到引擎给的⾏数据，把这个值加上1，⽐如原来是N，现在就是N+1，得到新的⼀

⾏数据，再调⽤引擎接⼜写⼊这⾏新数据。

3. 引擎将这⾏新数据更新到内存中，同时将这个更新操作记录到redo log⾥⾯，此时redo log

处于prepare状态。然后告知执⾏器执⾏完成了，随时可以提交事务。

4. 执⾏器⽣成这个操作的binlog，并把binlog写⼊磁盘。成提交（commit）状态，

更新完成。

从上图可以看出，MySQL在执⾏更新语句的时候，在服务层进⾏语句的解析和执⾏，在引擎

层进⾏数据的提取和存储；同时在服务层对binlog进⾏写⼊，在InnoDB内进⾏redo log的写

⼊。

不仅如此，在对redo log写⼊时有两个阶段的提交，⼀是binlog写⼊之前 prepare 状态的写

⼊，⼆是binlog写⼊之后 commit 状态的写⼊。

 

22.那为什么要两阶段提交呢？

为什么要两阶段提交呢？直接提交不⾏吗？

我们可以假设不采⽤两阶段提交的⽅式，⽽是采⽤“单阶段”进⾏提交，即要么先写⼊redo

log，后写⼊binlog；要么先写⼊binlog，后写⼊redo log。这两种⽅式的提交都会导致原先数

据库的状态和被恢复后的数据库的状态不⼀致。

先写⼊redo log，后写⼊binlog：

在写完redo log之后，数据此时具有 crash-safe 能⼒，因此系统崩溃，数据会恢复成事务开

始之前的状态。但是，若在redo log写完时候，binlog写⼊之前，系统发⽣了宕机。此时binlog

没有对上⾯的更新语句进⾏保存，导致当使⽤binlog进⾏数据库的备份或者恢复时，就少了上

述的更新语句。从⽽使得 id=2 这⼀⾏的数据没有被更新。

简单说，redo log和binlog都可以⽤于表⽰事务的提交状态，⽽两阶段提交就是让这两个状态

保持逻辑上的⼀致。

什么时候会刷⼊磁盘？

在如下的⼀些情况中，log buffer的数据会刷⼊磁盘：

log buffer 空间不⾜时

log buffer 的⼤⼩是有限的，如果不停的往这个有限⼤⼩的 log buffer ⾥塞⼊⽇志，很快它就

会被填满。如果当前写⼊ log buffer 的redo ⽇志量已经占满了 log buffer 总容量的⼤约⼀半左

右，就需要把这些⽇志刷新到磁盘上。

事务提交时

在事务提交时，为了保证持久性，会把log buffer中的⽇志全部刷到磁盘。注意，这时候，除

了本事务的，可能还会刷⼊其它事务的⽇志。

后台线程输⼊

有⼀个后台线程，⼤约每秒都会刷新⼀次 log buffer 中的 redo log 到磁盘。

正常关闭服务器时

触发checkpoint规则

重做⽇志缓存、重做⽇志⽂件都是以块（block）的⽅式进⾏保存的，称之为重做⽇志块

（redo log block）,块的⼤⼩是固定的512字节。我们的redo log它是固定⼤⼩的，可以看作是

⼀个逻辑上的 log group，由⼀定数量的log block 组成。

它的写⼊⽅式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos 是当前记录的位置，⼀边写⼀边后移，写到第3号⽂件末尾后就回到0号⽂件开

头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更

新到磁盘。

 

当 write_pos 追上 checkpoint 时，表⽰redo log⽇志已经写满。这时候就不能接着往⾥写

数据了，需要执⾏ checkpoint 规则腾出可写空间。

所谓的checkpoint规则，就是checkpoint触发后，将buffer中⽇志页都刷到磁盘。

24.慢SQL如何定位呢？

慢SQL的监控主要通过两个途径：

慢查询⽇志 ：开启MySQL的慢查询⽇志，再通过⼀些⼯具⽐如mysqldumpslow去分析对

应的慢查询⽇志，当然现在⼀般的云⼚商都提供了可视化的平台。

服务监控 ：可以在业务的基建中加⼊对慢SQL的监控，常见的⽅案有字节码插桩、连接

池扩展、ORM框架过程，对服务运⾏中的慢SQL进⾏监控和告警。

 

 

避免不必要的列

这个是⽼⽣常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，⽽不要

包含额外的列，像 slect * 这种写法应该尽量避免。

分页优化

在数据量⽐较⼤，分页⽐较深的情况下，需要考虑分页的优化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit

190289,10;

优化⽅案：

延迟关联

先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据⾏，⽽不

是通过原来的⼆级索引提取数据⾏

例如：

select a.* from table a,

(select id from table where type = 2 and level = 9 order by id asc

limit 190289,10 ) b

where a.id = b.id

书签⽅式

书签⽅式就是找到limit第⼀个参数对应的主键值，根据这个主键值再去过滤并limit

例如：

select * from table where id >

(select * from table where type = 2 and level = 9 order by id asc limit

190

索引优化

合理地设计和使⽤索引，是优化慢SQL的利器。

利⽤覆盖索引

InnoDB使⽤⾮主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字

段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建⽴到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

低版本避免使⽤or查询

在 MySQL 5.0 之前的版本要尽量避免使⽤ or 查询，可以使⽤ union 或者⼦查询来替代，因为

早期的 MySQL 版本使⽤ or 查询可能会导致索引失效，⾼版本引⼊了索引合并，解决了这个

问题。

避免使⽤ != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使⽐较的字段上有

索引

解决⽅法：通过把不等于操作符改成or，可以使⽤索引，避免全表扫描

例如，把 column<>’aaa’，改成column>’aaa’ or column<’aaa’ ，就可以使⽤索引了

适当使⽤前缀索引

适当地使⽤前缀所云，可以降低索引的空间占⽤，提⾼索引的查询效率。

⽐如，邮箱的后缀都是固定的“ @xxx.com ”，那么类似这种后⾯⼏位为固定值的字段就⾮常

适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL⽆法利⽤前缀索引做order by和group by 操

作，也⽆法作为覆盖索引

避免列上函数运算

要避免在列字段上进⾏算术运算或其他表达式运算，否则可能会导致存储引擎⽆法正确使⽤

索引，从⽽影响了查询的效率

select * from test where id + 1 = 50;

select * from test where month(updateTime) = 7;

正确使⽤联合索引

使⽤联合索引的时候，注意最左匹配原则。

JOIN优化

优化⼦查询

尽量使⽤ Join 语句来替代⼦查询，因为⼦查询是嵌套查询，⽽嵌套查询会新创建⼀张临时

表，⽽临时表的创建与销毁会占⽤⼀定的系统资源以及花费⼀定的时间，同时对于返回结果

集⽐较⼤的⼦查询，其对查询性能的影响更⼤

⼩表驱动⼤表

关联查询的时候要拿⼩表去驱动⼤表，因为关联的时候，MySQL内部会遍历驱动表，再去连

接被驱动表。

⽐如left join，左表就是驱动表，A表⼩于B表，建⽴连接的次数就少，查询速度就被加快了。

select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少⼤量的连表查询，因为多张表的连表查询性能很低，所有可以适当的

增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使⽤JOIN关联太多的表

《阿⾥巴巴Java开发⼿册》规定不要join超过三张表，第⼀join太多降低查询的速度，第⼆join

的buffer会占⽤更多的内存。

如果不可避免要join多张表，可以考虑使⽤数据异构的⽅式异构到ES中查询。

排序优化

利⽤索引扫描做排序

MySQL有两种⽅式⽣成有序结果：其⼀是对结果集进⾏排序的操作，其⼆是按照索引顺序扫

描得出的结果⾃然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描⼀条记录回表查询⼀次，这个读操作是

随机IO，通常会⽐顺序全表扫描还慢

因此，在设计索引时，尽可能使⽤同⼀个索引既满⾜排序又⽤于查找⾏

例如：

--建⽴索引（date,staff_id,customer_id）

select staff_id, customer_id from test where date = '2010-01-01' order by

staff_id,customer_id;

只有当索引的列顺序和ORDER BY⼦句的顺序完全⼀致，并且所有列的排序⽅向都⼀样时，

才能够使⽤索引来对结果做排序

UNION优化

条件下推

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查

询，很多优化策略在union查询中都会失效，因为它⽆法利⽤索引

最好⼿⼯将where、limit等⼦句下推到union的各个⼦查询中，以便优化器可以充分利⽤这些条

件进⾏优化

此外，除⾮确实需要服务器去重，⼀定要使⽤union all，如果不加all关键字，MySQL会给临时

表加上distinct选项，这会导致对整个临时表做唯⼀性检查，代价很⾼。

26.怎么看执⾏计划（explain），如何理解其中各个字段的含义？

explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看⼀下执⾏计

划，看看是否还有优化的空间。

 

1. id 列：MySQL会为每个select语句分配⼀个唯⼀的id值

2. select_type 列，查询的类型，根据关联、union、⼦查询等等分类，常见的查询类型有

SIMPLE、PRIMARY。

3. table 列：表⽰ explain 的⼀⾏正在访问哪个表。

4. type 列：最重要的列之⼀。表⽰关联类型或访问类型，即 MySQL 决定如何查找表中的

⾏。

性能从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null >

index_merge > unique_subquery > index_subquery > range > index > ALL

system

system ： 当表仅有⼀⾏记录时(系统表)，数据量很少，往往不需要进⾏磁盘IO，速

度⾮常快

const

const ：表⽰查询时命中 primary key 主键或者 unique 唯⼀索引，或者被连接

的部分是⼀个常量( const )值。这类扫描效率极⾼，返回数据量少，速度⾮常快。

eq_ref

eq_ref ：查询时命中主键 primary key 或者 unique key 索引， type 就是

eq_ref 。

ref_or_null

ref_or_null ：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值

的⾏。

index_merge

index_merge ：使⽤了索引合并优化⽅法，查询使⽤了两个以上的索引。

unique_subquery

unique_subquery ：替换下⾯的 IN ⼦查询，⼦查询返回不重复的集合。

index_subquery

index_subquery ：区别于 unique_subquery ，⽤于⾮唯⼀索引，可以返回重复

值。

range

range ：使⽤索引选择⾏，仅检索给定范围内的⾏。简单点说就是针对⼀个有索引的

字段，给定范围检索数据。在 where 语句中使⽤

bettween...and 、 < 、 > 、 <= 、 in 等条件查询 type 都是 range 。

index

index ： Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，

⽽ ALL 是从硬盘中读取。

ALL

就不⽤多说了，全表扫描。

5. possible_keys 列：显⽰查询可能使⽤哪些索引来查找，使⽤索引优化sql的时候⽐较重

要。

6. key 列：这⼀列显⽰ mysql 实际采⽤哪个索引来优化对该表的访问，判断索引是否失效的

时候常⽤。

7. key_len 列：显⽰了 MySQL使⽤

8. ref 列：ref 列展⽰的就是与索引列作等值匹配的值，常见的有：const（常量），func，

NULL，字段名。

9. rows 列：这也是⼀个重要的字段，MySQL查询优化器根据统计信息，估算SQL要查到结

果集需要扫描读取的数据⾏数，这个值⾮常直观显⽰SQL的效率好坏，原则上rows越少越

好。

10. Extra 列：显⽰不适合在其它列的额外信息，虽然叫额外，但是也有⼀些重要的信息：

Using index：表⽰MySQL将使⽤覆盖索引，以避免回表

Using where：表⽰会在存储引擎检索之后再进⾏过滤

Using temporary ：表⽰对查询结果排序时会使⽤⼀个临时表。

 

主键索引: InnoDB主键是默认的索引，数据列不允许重复，不允许为NULL，⼀个表只能

有⼀个主键。

唯⼀索引: 数据列不允许重复，允许为NULL值，⼀个表允许多个列创建唯⼀索引。

普通索引: 基本的索引类型，没有唯⼀性的限制，允许为NULL值。

组合索引：多列值组成⼀个索引，⽤于组合搜索，效率⼤于索引合并

 

28.为什么使⽤索引会加快查询？

传统的查询⽅法，是按照表的顺序遍历的，不论查询⼏条数据，MySQL需要将表的数据从头

到尾遍历⼀遍。

在我们添加完索引之后，MySQL⼀般通过BTREE算法⽣成⼀个索引⽂件，在查询数据库时，

找到索引⽂件进⾏遍历，在⽐较⼩的索引数据⾥查找，然后映射到对应的数据，能⼤幅提升

查找的效率。

和我们通过书的⽬录，去查找对应的内容

29.创建索引有哪些注意点？

索引虽然是sql性能优化的利器，但是索引的维护也是需要成本的，所以创建索引，也要注

意：

1. 索引应该建在查询应⽤频繁的字段

在⽤于 where 判断、 order 排序和 join 的(on)字段上创建索引。

2. 索引的个数应该适量

索引需要占⽤空间；更新时候也需要维护。

3. 区分度低的字段，例如性别，不要建索引。

离散度太低的字段，扫描的⾏数降低的有限。

4. 频繁更新的值，不要作为主键或者索引

维护索引⽂件需要成本；还会导致页分裂，IO次数增多。

5. 组合索引把散列性⾼(区分度⾼)的值放在前⾯

为了满⾜最左前缀匹配原则

6. 创建组合索引，⽽不是修改单列索引。

组合索引代替多个单列索引（对于单列索引，MySQL基本只能使⽤⼀个索引，所以经常

使⽤多个条件查询时更适合使⽤组合索引）

7. 过长的字段，使⽤前缀索引。

当字段值⽐较长的时候，建⽴索引会消耗很多的空间，搜索起来也会很慢。我们可以通过

截取字段的前⾯⼀部分内容建⽴索引，这个就叫前缀索引。

8. 不建议⽤⽆序的值(例如⾝份证、UUID )作为索引

当主键具有不确定性，会造成叶⼦节点频繁分裂，出现磁盘存储的碎⽚化

30.索引哪些情况下会失效呢？

查询条件包含or，可能导致索引失效

如果字段类型是字符串，where时⼀定⽤引号括起来，否则会因为隐式类型转换，索引失

效

like通配符可能导致索引失效。

联合索引，查询时的条件列不是联合索引中的第⼀个列，索引失效。

在索引列上使⽤mysql的内置函数，索引失效。

对索引列运算（如，+、-、*、/），索引失效。

索引字段上使⽤（！= 或者 < >，not in）时，可能会导致索引失效。

索引字段上使⽤is null， is not null，可能导致索引失效。

左连接查询或者右连接查询查询关联的字段编码格式不⼀样，可能导致索引失效。

MySQL优化器估计使⽤全表扫描要⽐使⽤索引快,则不使⽤索引。

31.索引不适合哪些场景呢？

数据量⽐较少的表不适合加索引

更新⽐较频繁的字段也不适合加索引

离散低的字段不适合加索引（如性别）

32.索引是不是建的越多越好呢？

当然不是。

索引会占据磁盘空间

索引虽然会提⾼查询效率，但是会降低更新表的效率 。⽐如每次对表进⾏增删改操作，

MySQL不仅要保存数据，还有保存或者更新对应的索引⽂件。

 

最外⾯的⽅块，的块我们称之为⼀个磁盘块，可以看到每个磁盘块包含⼏个数据项（粉⾊

所⽰）和指针（黄⾊/灰⾊所⽰），如根节点磁盘包含数据项17和35，包含指针P1、P2、

P3，P1表⽰⼩于17的磁盘块，P2表⽰在17和35之间的磁盘块，P3表⽰⼤于35的磁盘块。真

实的数据存在于叶⼦节点即3、4、5……、65。⾮叶⼦节点只不存储真实的数据，只存储

指引搜索⽅向的数据项，如17、35并不真实存在于数据表中。

叶⼦节点之间使⽤双向指针连接，最底层的叶⼦节点形成了⼀个双向有序链表，可以进⾏

范围查询。

 

假设索引字段是 bigint 类型，长度为 8 字节。指针⼤⼩在 InnoDB 源码中设置为 6 字节，这

样⼀共 14 字节。⾮叶⼦节点(⼀页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表

有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶⼦节点，可以存储的数据为 1170*1170*16=21902400。

在查找数据时⼀次页的查找代表⼀次 IO，也就是说，⼀张 2000 万左右的表，查询数据最多

需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度⼀般为 1-3 层，它就能满⾜千万级的数据存储。

35.为什么要⽤ B+ 树，⽽不⽤普通⼆叉树？

可以从⼏个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁

盘次数。

为什么不⽤普通⼆叉树？

普通⼆叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡⼆叉树相⽐于⼆叉

查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不⽤平衡⼆叉树呢？

读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找⼀次数据就

需要从磁盘中读取⼀个节点，也就是⼀个磁盘块，但是平衡⼆叉树可是每个节点只存储⼀个

键值和数据的，如果是 B+ 树，可以存储更多的节点数据，树的⾼度也会降低，因此读取磁盘

的次数就降下来啦，查询效率就快。

36.为什么⽤ B+ 树⽽不⽤ B 树呢？

B+相⽐较B树，有这些优势：

它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。

B Tree 解决的两⼤问题：每个节点存储更多关键字；路数更多

扫库、扫表能⼒更强

如果我们要对表进⾏全表扫描，只需要遍历叶⼦节点就可以 了，不需要遍历整棵 B+Tree

拿到所有的数据。

B+Tree 的磁盘读写能⼒相对于 B Tree 来说更强，IO次数更少

根节点和枝节点不保存数据区， 所以⼀个节点可以保存更多的关键字，⼀次磁盘加载的

关键字更多，IO次数更少。

排序能⼒更强

因为叶⼦节点上有下⼀个数据区的指针，数据形成了链表。

效率更加稳定

B+Tree 永远是在叶⼦节点拿到数据，所以 IO 次数是稳定的。

37.Hash 索引和 B+ 树索引区别是什么？

B+ 树可以进⾏范围查询，Hash 索引不能。

B+ 树⽀持联合索引的最左侧原则，Hash 索引不⽀持。

B+ 树⽀持 order by 排序，Hash 索引不⽀持。

Hash 索引在等值查询上⽐ B+ 树效率更⾼。

B+ 树使⽤ like 进⾏模糊查询的时候，like 后⾯（⽐如 % 开头）的话可以起到优化的作

⽤，Hash 索引根本⽆法进⾏模糊查询。

38.聚簇索引与⾮聚簇索引的区别？

⾸先理解聚簇索引不是⼀种新的索引，⽽是⽽是⼀种数据存储⽅式。 聚簇表⽰数据⾏和相邻

的键值紧凑地存储在⼀起。我们熟悉的两种存储引擎——MyISAM采⽤的是⾮聚簇索引，

InnoDB采⽤的是聚簇索引。

可以这么说：

索引的数据结构是树，聚簇索引的索引和数据存储在⼀棵树上，树的叶⼦节点就是数据，

⾮聚簇索引索引和数据不在⼀棵树上。

⼀个表中只能拥有⼀个聚簇索引，⽽⾮聚簇索引⼀个表可以存在多个。

聚簇索引，索引中键值的逻辑顺序决定了表中相应⾏的物理顺序；索引，索引中索引的逻

辑顺序与磁盘上⾏的物理存储顺序不同。

聚簇索引：物理存储按照索引排序；⾮聚集索引：物理存储不按照索引排序；

39.回表了解吗？

在InnoDB存储引擎⾥，利⽤辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主

键值查出主键索引⾥⾯没有符合要求的数据，它⽐基于主键索引的查询多扫描了⼀棵索引

树，这个过程就叫回表。

例如:select * from user where name = ‘张三’;

 

40.覆盖索引了解吗？

在辅助索引⾥⾯，不管是单列索引还是联合索引，如果 select 的数据列只⽤辅助索引中就能

够取得，不⽤去查主键索引，这时候使⽤的索引就叫做覆盖索引，避免了回表。

⽐如， select name from user where name = ‘张三’;

41.什么是最左前缀原则/最左匹配原则？

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是⼀个概念。

最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前⼀个/左边的值之后，才能

匹配下⼀个。

根据最左匹配原则，我们创建了⼀个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)

和 (a1,a2,a3) 三个索引。

为什么不从最左开始查，就⽆法匹配呢？

⽐如有⼀个user表，我们给 name 和 age 建⽴了⼀个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建⽴搜索树的 (name 在

左边，age 在右边)。

从这张图可以看出来，name 是有序的，age 是⽆序的。当 name 相等的时候， age 才是有序

的。

这个时候我们使⽤ where name= ‘张三‘ and age = ‘20 ‘ 去查询数据的时候， B+Tree

会优先⽐较 name 来确定下⼀步应该搜索的⽅向，往左还是往右。如果 name 相同的时候再⽐

较age。但是如果查询条件没有 name，就不知道下⼀步应该查哪个 节点，因为建⽴搜索树的

时候 name 是第⼀个⽐较因⼦，所以就没⽤上索引。

 

 

如果按锁粒度划分，有以下3种：

表锁： 开销⼩，加锁快；锁定⼒度⼤，发⽣锁冲突概率⾼，并发度最低;不会出现死锁。

⾏锁： 开销⼤，加锁慢；会出现死锁；锁定粒度⼩，发⽣锁冲突的概率低，并发度⾼。

页锁： 开销和加锁速度介于表锁和⾏锁之间；会出现死锁；锁定粒度介于表锁和⾏锁之

间，并发度⼀般

如果按照兼容性，有两种，

共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。

排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在⼀定时间内，只有⼀个

请求能执⾏写⼊，并阻⽌其它锁读取正在写⼊的数据。

 

我们拿这么⼀个⽤户表来表⽰⾏级锁，其中插⼊了4⾏数据，主键值分别是1,6,8,12，现在简化

它的聚簇索引结构，只保留数据记录。

InnoDB的⾏锁的主要实现如下：

Record Lock 记录锁

记录锁就是直接锁定某⾏记录。当我们使⽤唯⼀性的索引(包括唯⼀索引和聚簇索引)进⾏等值

查询且精准匹配到⼀条记录时，此时就会直接将这条记录锁定。例如 select * from t

where id =6 for update; 就会将 id=6 的记录锁定。

Gap Lock 间隙锁

间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填⼊数据的部分,是⼀个左开右开空

间。

间隙锁就是锁定某些间隙区间的。当我们使⽤⽤等值查询或者范围查询，并且没有命中任何

⼀个 record ，此时就会将对应的间隙区间锁定。例如 select * from t where id =3

for update; 或者 select * from t where id > 1 and id < 6 for update; 就会

将(1,6)区间锁定。

Next-key Lock 临键锁

临键指的是间隙加上它右边的记录组成的左开右闭区间。⽐如上述的(1,6]、(6,8]等。

临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本⾝，还要再

锁住索引之间的间隙。当我们使⽤范围查询，并且命中了部分 record 记录，此时锁住的就

是临键区间。注意，临键锁锁住的区间会包含最后⼀个record的右边的临键区间。例如

select * from t where id > 5 and id <= 7 for update; 会锁住(4,7]、(7,+∞)。

mysql默认⾏锁类型就是 临键锁(Next-Key Locks) 。当使⽤唯⼀性索引，等值查询匹配到⼀

条记录的时候，临键锁(Next-Key Locks)会退化成记录锁；没有匹配到任何记录的时候，退化

成间隙锁。

间隙锁(Gap Locks) 和 临键锁(Next-Key Locks) 都是⽤来解决幻读问题的，在 已提

交读（READ COMMITTED） 隔离级别下， 间隙锁(Gap Locks) 和 临键锁(Next-Key

Locks) 都会失效！

上⾯是⾏锁的三种实现算法，除此之外，在⾏上还存在插⼊意向锁。

Insert Intention Lock 插⼊意向锁

⼀个事务在插⼊⼀条记录时需要判断⼀下插⼊位置是不是被别的事务加了意向锁 ，如果有的

话，插⼊操作需要等待，直到拥有 gap锁 的那个事务提交。但是事务在等待的时候也需要在

内存中⽣成⼀个 锁结构 ，表明有事务想在某个 间隙 中插⼊新记录，但是现在在等待。这种

类型的锁命名为 Insert Intention Locks ，也就是插⼊意向锁 。

假如我们有个T1事务，给(1,6)区间加上了意向锁，现在有个T2事务，要插⼊⼀个数据，id为

4，它会获取⼀个（1,6）区间的插⼊意向锁，又有有个T3事务，想要插⼊⼀个数据，id为3，

它也会获取⼀个（1,6）区间的插⼊意向锁，但是，这两个插⼊意向锁锁不会互斥。

45.意向锁是什么知道吗？

意向锁是⼀个表级锁，不要和插⼊意向锁搞混。

意向锁的出现是为了⽀持InnoDB的多粒度锁，它解决的是表锁和⾏锁共存的问题。

当我们需要给⼀个表加表锁的时候，我们需要根据去判断表中有没有数据⾏被锁定，以确定

是否能加成功。

假如没有意向锁，那么我们就得遍历表中所有数据⾏来判断有没有⾏锁；

有了意向锁这个表级锁之后，则我们直接判断⼀次就知道表中是否有数据⾏被锁定了。

有了意向锁之后，要执⾏的事务A在申请⾏锁（写锁）之前，数据库会⾃动先给事务A申请表

的意向排他锁。当事务B去申请表的互斥锁时就会失败，因为表上有意向排他锁之后事务B申

请表的互斥锁时会被阻塞。

46.MySQL的乐观锁和悲观锁了解吗？

悲观锁 （Pessimistic Concurrency Control）：

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，⼀个事务拿到悲观

锁后，其他任何事务都不能对该数据进⾏修改，只能等待锁被释放才可以执⾏。

数据库中的⾏锁，表锁，读锁，写锁均为悲观锁。

乐观锁（Optimistic Concurrency Control）

乐观锁认为数据的变动不会太频繁。

乐观锁通常是通过在表中增加⼀个版本(version)或时间戳(timestamp)来实现，其中，版本最为

常⽤。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将

其更新到表中时，会将之前取出的版本v1与数据中最新的版本v2相对⽐，如果v1=v2，那么说

明在数据变动期间，没有其他事务对数据进⾏修改，此时，就允许事务对表中的数据进⾏修

改，并且修改时version会加1，以此来表明数据已被变动。

如果，v1不等于v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新

到表中，⼀般的处理办法是通知⽤户让其重新操作。不同于悲观锁，乐观锁通常是由开发者

实现的。

47.MySQL 遇到过死锁问题吗，你是如何解决的？

排查死锁的⼀般步骤是这样的：

（1）查看死锁⽇志 show engine innodb status;

（2）找出死锁 sql

（3）分析 sql 加锁情况

（4）模拟死锁案发

（5）分析死锁⽇志

（6）分析死锁结果

当然，这只是⼀个简单的流程说明，实际上⽣产中的死锁千奇百怪，排查和解决起来没那么

简

 

 

原⼦性：事务作为⼀个整体被执⾏，包含在其中的对数据库的操作要么全部被执⾏，要么

都不执⾏。

⼀致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转

10 块钱，不管成功与否，A 和 B 的总⾦额是不变的。

隔离性：多个事务并发访问时，事务之间是相互隔离的，即⼀个事务不影响其它事务运⾏

效果。简⾔之，就是事务之间是进⽔不犯河⽔的。

持久性：表⽰事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之

中。

49.那ACID靠什么保证的呢？

事务的 隔离性 是通过数据库锁的机制实现的。

事务的 ⼀致性 由undo log来保证：undo log是逻辑⽇志，记录了事务的insert、update、

deltete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。

事务的 原⼦性 和 持久性 由redo log来保证：redolog被称作重做⽇志，是物理⽇志，事务

提交的时候，必须先将事务的所有⽇志写⼊redo log持久化，到事务的提交操作才算完成。

 

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

读未提交（Read Uncommitted）

读已提交（Read Committed）

可重复读（Repeatable Read）

串⾏化（Serializable）

MySQL默认的事务隔离级别是可重复读 (Repeatable Read)。

51.什么是幻读，脏读，不可重复读呢？

事务 A、B 交替执⾏，事务 A 读取到事务 B 未提交的数据，这就是 脏读 。

在⼀个事务范围内，两个相同的查询，读取同⼀条记录，却返回了不同的数据，这就是

不可重复读 。

事务 A 查询⼀个范围的结果集，另⼀个并发事务 B 往这个范围中插⼊ / 删除了数据，并

静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不⼀样了，这就

是 幻读 。

不同的隔离级别，在并发事务下可能会发⽣的问题：

 

52.事务的各个隔离级别都是如何实现的？

读未提交

读未提交，就不⽤多说了，采取的是读不加锁原理。

事务读不加锁，不阻塞其他事务的读和写

事务写阻塞其他事务写，但不阻塞其他事务读；

读取已提交&可重复读

读取已提交和可重复读级别利⽤了 ReadView 和 MVCC ，也就是每个事务只能读取它能看到的

版本（ReadView）。

READ COMMITTED：每次读取数据前都⽣成⼀个ReadView

REPEATABLE READ ： 在第⼀次读取数据时⽣成⼀个ReadView

串⾏化

串⾏化的实现采⽤的是读写都加锁的原理。

串⾏化的情况下，对于同⼀⾏事务， 写 会加 写锁 ， 读 会加 读锁 。当出现读写锁冲突的时

候，后访问的事务必须等前⼀个事务执⾏完成，才能继续执⾏。

 

53.MVCC了解吗？怎么实现的？

MVCC(Multi Version Concurrency Control)，中⽂名是多版本并发控制，简单来说就是通过维

护数据历史版本，从⽽解决并发访问情况下的读⼀致性问题。关于它的实现，要抓住⼏个关

键点，隐式字段、undo⽇志、版本链、快照读&当前读、Read View。

版本链

对于InnoDB存储引擎，每⼀⾏记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR

DB_TRX_ID ，事务ID，每次修改时，都会把该事务ID复制给 DB_TRX_ID ；

DB_ROLL_PTR ，回滚指针，指向回滚段的undo⽇志。

假如有⼀张 user 表，表中只有⼀⾏记录，当时插⼊的事务id为80。此时，该条记录的⽰例图

如下：

接下来有两个 DB_TRX_ID 分别为 100 、 200 的事务对这条记录进⾏ update 操作，整个过程

如下：

 

 

由于每次变动都会先把 undo ⽇志记录下来，并⽤ DB_ROLL_PTR 指向 undo ⽇志地址。因此

可以认为，对该条记录的修改⽇志串联起来就形成了⼀个 版本链 ，版本链的头节点就是当前

记录最新的值。如下：

对于 Read Committed 和 Repeatable Read 隔离级别来说，都需要读取已经提交的事

务所修改的记录，也就是说如果版本链中某个版本的修改没有提交，那么该版本的记录

时不能被读取的。所以需要确定在 Read Committed 和 Repeatable Read 隔离级别

下，版本链中哪个版本是能被当前事务读取的。于是就引⼊了 ReadView 这个概念来解

决这个问题。

Read View就是事务执⾏快照读时，产⽣的读视图，相当于某时刻表记录的⼀个快照，通过这

个快照，我们可以获取：

m_ids ：表⽰在⽣成 ReadView 时当前系统中活跃的读写事务的事务id 列表。

min_trx_id ：表⽰在⽣成 ReadView 时当前系统中活跃的读写事务中最⼩的 事务id ，也就

是 m_ids 中的最⼩值。

max_trx_id ：表⽰⽣成 ReadView 时系统中应该分配给下⼀个事务的 id 值。

creator_trx_id ：表⽰⽣成该 ReadView 的事务的 事务id

有了这个 ReadView ，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是

否可见：

如果被访问版本的 DB_TRX_ID 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当

前事务在访问它⾃⼰修改过的记录，所以该版本可以被当前事务访问。

如果被访问版本的 DB_TRX_ID 属性值⼩于 ReadView 中的 min_trx_id 值，表明⽣成该版

本的事务在当前事务⽣成 ReadView 前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的 DB_TRX_ID 属性值⼤于 ReadView 中的 max_trx_id 值，表明⽣成该版

本的事务在当前事务⽣成 ReadView 后才开启，所以该版本不可以被当前事务访问。

如果被访问版本的 DB_TRX_ID 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那

就需要判断⼀下trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时⽣成

该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时⽣成

该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下⼀个版本的数据，继续

按照上边的步骤判断可见性，依此类推，直到版本链中的最后⼀个版本。如果最后⼀个版本

也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

在 MySQL 中， READ COMMITTED 和 REPEATABLE READ 隔离级别的的⼀个⾮常⼤的区

别就是它们⽣成ReadView的时机不同。

READ COMMITTED 是每次读取数据前都⽣成⼀个ReadView，这样就能保证⾃⼰每次都能

读到其它事务提交的数据；REPEATABLE READ 是在第⼀次读取数据时⽣成⼀个

ReadView，这样就能保证后续读取的结果完全⼀致。

 

读写分离的基本实现是:

数据库服务器搭建主从集群，⼀主⼀从、⼀主多从都可以。

数据库主机负责读写操作，从机只负责读操作。

数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。

业务服务器将写操作发给数据库主机，将读操作发给数据库从机

55.那读写分离的分配怎么实现呢？

将读写操作区分开来，然后访问不同的数据库服务器，⼀般有两种⽅式：程序代码封装和中

间件封装。

1. 程序代码封装

程序代码封装指在代码中抽象⼀个数据访问层（所以有的⽂章也称这种⽅式为 "中间层封装"

） ，实现读写操作分离和数据库服务器连接的管理。例如，基于 Hibernate 进⾏简单封装，就

可以实现读写分离：

⽬前开源的实现⽅案中，淘宝的 TDDL (Taobao Distributed Data Layer, 外号：头都⼤了）是⽐

较有名的。

2. 中间件封装

中间件封装指的是独⽴⼀套系统出来，实现读写操作分离和数据库服务器连接的管理。中间

件对业务服务器提供 SQL 兼容的协议，业务服务器⽆须⾃⼰进⾏读写分离。

对于业务服务器来说，访问中间件和访问数据库没有区别，事实上在业务服务器看来，中间

件就是⼀个数据库服务器。

56.主从复制原理了解吗？

master数据写⼊，更新binlog

master创建⼀个dump线程向slave推送binlog

slave连接到master的时候，会创建⼀个IO线程接收binlog，并记录到relay log中继⽇志中

slave再开启⼀个sql线程读取relay log事件并在slave执⾏，完成同步

slave记录⾃⼰的binglog

 

57.主从同步延迟怎么处理？

主从同步延迟的原因

⼀个服务器开放Ｎ个链接给客户端来连接的，这样有会有⼤并发的更新操作, 但是从服务器的

⾥⾯读取 binlog 的线程仅有⼀个，当某个 SQL 在从服务器上执⾏的时间稍长 或者由于某个

SQL 要进⾏锁表就会导致，主服务器的 SQL ⼤量积压，未被同步到从服务器⾥。这就导致了

主从不⼀致， 也就是主从延迟。

主从同步延迟的解决办法

解决主从复制延迟有⼏种常见的⽅法:

1. 写操作后的读操作指定发给数据库主服务器

例如，注册账号完成后，登录时读取账号的读操作也发给数据库主服务器。这种⽅式和业务

强绑定，对业务的侵⼊和影响较⼤，如果哪个新来的程序员不知道这样写代码，就会导致⼀

个bug。

2. 读从机失败后再读⼀次主机

这就是通常所说的 "⼆次读取" ，⼆次读取和业务⽆绑定，只需要对底层数据库访问的 API 进

⾏封装即可，实现代价较⼩，不⾜之处在于如果有很多⼆次读取，将⼤⼤增加主机的读操作

压⼒。例如，⿊客暴⼒破解账号，会导致⼤量的⼆次读取操作，主机可能顶不住读操作的压

⼒从⽽崩溃。

3. 关键业务读写操作全部指向主机，⾮关键业务采⽤读写分离

例如，对于⼀个⽤户管理系统来说，注册 + 登录的业务读写操作全部访问主机，⽤户的介

绍、爰好、等级等业务，可以采⽤读写分离，因为即使⽤户改了⾃⼰的⾃我介绍，在查询时

却看到了⾃我介绍还是旧的，业务影响与不能登录相⽐就⼩很多，还可以忍受。

58.你们⼀般是怎么分库的呢？

垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

⽔平分库：以字段为依据，按照⼀定策略（hash、range 等），将⼀个库中的数据拆分到

多个库中。

 

 

 

60.⽔平分表有哪⼏种路由⽅式？

什么是路由呢？就是数据应该分到哪⼀张表。

⽔平分表主要有三种路由⽅式：

范围路由 ：选取有序的数据列 （例如，整形、时间戳等） 作为路由的条件，不同分段分

散到不同的数据库表中。

我们可以观察⼀些⽀付系统，发现只能查⼀年范围内的⽀付记录，这个可能就是⽀付公司按

照时间进⾏了分表。

 

范围路由设计的复杂点主要体现在分段⼤⼩的选取上，分段太⼩会导致切分后⼦表数量过

多，增加维护复杂度；分段太⼤可能会导致单表依然存在性能问题，⼀般建议分段⼤⼩在 100

万⾄2000 万之间，具体需要根据业务选取合适的分段⼤⼩。

范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如，现在的⽤户是 100 万，如

果增加到 1000 万，只需要增加新的表就可以了，原有的数据不需要动。范围路由的⼀个⽐较

隐含的缺点是分布不均匀，假如按照 1000 万来进⾏分表，有可能某个分段实际存储的数据

量只有 1000 条，⽽另外⼀个分段实际存储的数据量有 900 万条。

Hash 路由 ：选取某个列 （或者某⼏个列组合也可以） 的值进⾏ Hash 运算，然后根据

Hash 结果分散到不同的数据库表中。

同样以订单 id 为例，假如我们⼀开始就规划了 4个数据库表，路由算法可以简单地⽤ id % 4

的值来表⽰数据所属的数据库表编号，id 为 12的订单放到编号为 50的⼦表中，id为 13的订单

放到编号为 61的字表中。

Hash 路由设计的复杂点主要体现在初始表数量的选取上，表数量太多维护⽐较⿇烦，表数量

太少又可能导致单表性能存在问题。⽽⽤了 Hash 路由后，增加⼦表数量是⾮常⿇烦的，所有

数据都要重分布。Hash 路由的优缺点和范围路由基本相反，Hash 路由的优点是表分布⽐较均

匀，缺点是扩充新的表很⿇烦，所有数据都要重分布。

配置路由 ：配置路由就是路由表，⽤⼀张独⽴的表来记录路由信息。同样以订单id 为

例，我们新增⼀张 order_router 表，这个表包含 orderjd 和 tablejd 两列 , 根据 orderjd 就可以

查询对应的 table_id。

配置路由设计简单，使⽤起来⾮常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，

然后修改路由表就可以了。

 

61.不停机扩容怎么实现？

实际上，不停机扩容，实操起来是个⾮常⿇烦⽽且很有风险的操作，当然，⾯试回答起来就

简单很多。

第⼀阶段：在线双写，查询⾛⽼库

1. 建⽴好新的库表结构，数据写⼊旧库的同时，也写⼊拆分的新库

2. 数据迁移，使⽤数据迁移程序，将旧库中的历史数据迁移到新库

3. 使⽤定时任务，新旧库的数据对⽐，把差异补齐

第⼆阶段：在线双写，查询⾛新库

1. 完成了历史数据的同步和校验

2. 把对数据的读切换到新库

第三阶段：旧库下线

1. 旧库不再写⼊新的数据

2. 经过⼀段时间，确定旧库没有请求之后，就可以下线⽼库

63.那你觉得分库分表会带来什么问题呢？

从分库的⾓度来讲：

事务的问题

使⽤关系型数据库，有很⼤⼀点在于它保证事务完整性。

⽽分库之后单机事务就⽤不上了，必须使⽤分布式事务来解决。

跨库 JOIN 问题

在⼀个库中的时候我们还可以利⽤ JOIN 来连表查询，⽽跨库了之后就⽆法使⽤ JOIN 了。

此时的解决⽅案就是在业务代码中进⾏关联，也就是先把⼀个表的数据查出来，然后通过得

到的结果再去查另⼀张表，然后利⽤代码来关联得到最终的结果。

这种⽅式实现起来稍微⽐较复杂，不过也是可以接受的。

还有可以适当的冗余⼀些字段。⽐如以前的表就存储⼀个关联 ID，但是业务时常要求返回对

应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操

作。

还有⼀种⽅式就是数据异构，通过binlog同步等⽅式，把需要跨库join的数据异构到ES等存储

结构中，通过ES进⾏查询。

从分表的⾓度来看：

跨节点的 count,order by,group by 以及聚合函数问题

只能由业务代码来实现或者⽤中间件将各表中的数据汇总、排序、分页然后返回。

数据迁移，容量规划，扩容等问题

数据的迁移，容量如何规划，未来是否可能再次需要扩容，等等，都是需要考虑的问题。

ID 问题

数据库表被切分后，不能再依赖数据库⾃⾝的主键⽣成机制，所以需要⼀些⼿段来保证全局

主键唯⼀。

1. 还是⾃增，只不过⾃增步长设置⼀下。⽐如现在有三张表，步长设置为3，三张表 ID 初始

值分别是1、2、3。 这样第⼀张表的 ID 增长是 1、4、7。第⼆张表是2、5、8。第三张表

是3、6、9，这样就不会重复了。

2. UUID，这种最简单，但是不连续的主键插⼊会导致严重的页分裂，性能⽐较差。

3. 分布式 ID，⽐较出名的就是 Twitter 开源的 sonwflake 雪花算法

64.百万级别以上的数据如何删除？

关于索引：由于索引需要额外的维护成本，因为索引⽂件是单独存在的⽂件,所以当我们对数

据的增加,修改,删除,都会产⽣额外的对索引⽂件的操作,这些操作需要消耗额外的IO,会降低增/

改/删的执⾏效率。

所以，在我们删除数据库百万级别数据的时候，查询MySQL官⽅⼿册得知删除数据的速度和

创建的索引数量是成正⽐的。

1. 所以我们想要删除百万数据的时候可以先删除索引

2. 然后删除其中⽆⽤数据

3. 删除完成后重新创建索引创建索引也⾮常快

65.百万千万级⼤表如何添加字段？

当线上的数据库数据量到达⼏百万、上千万的时候，加⼀个字段就没那么简单，因为可能会

长时间锁表。

⼤表添加字段，通常有这些做法：

通过中间表转换过去

创建⼀个临时的新表，把旧表的结构完全复制过去，添加字段，再把旧表数据复制过去，

删除旧表，新表命名为旧表的名称，这种⽅式可能回丢掉⼀些数据。

⽤pt-online-schema-change

pt-online-schema-change 是percona公司开发的⼀个⼯具，它可以在线修改表结构，

它的原理也是通过中间表。

先在从库添加 再进⾏主从切换

如果⼀张表数据量⼤且是热表（读写特别频繁），则可以考虑先在从库添加，再进⾏主从

切换，切换后再将其他⼏个节点上添加字段。

66.MySQL 数据库 cpu 飙升的话，要怎么处理呢？

排查过程：

（1）使⽤ top 命令观察，确定是 mysqld 导致还是其他原因。

（2）如果是 mysqld 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的

sql 在运⾏。

（3）找出消耗⾼的 sql，看看执⾏计划是否准确， 索引是否缺失，数据量是否太⼤。

处理：

（1）kill 掉这些线程 (同时观察 cpu 使⽤率是否下降)，

（2）进⾏相应的调整 (⽐如说加索引、改 sql、改内存参数)

（3）重新跑这些 SQL。

其他情况：

也有可能是每个 sql 消耗资源并不多，但是突然之间，有⼤量的 session 连进来导致 cpu 飙

升，这种情况就需要跟应⽤⼀起来分析为何连接数会激增，再做出相应的调整，⽐如说限制

连接数等