mysql 经典面试题 - 好文

1、MySQL的复制原理以及流程

基本原理流程，3个线程以及之间的关联；

1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

3. 从：sql执行线程——执行relay log中的语句；

详解：mysql主从复制

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。

Mysql主从复制的实现原理图大致如下(来源网络)：

MySQL之间数据复制的基础是二进制日志文件（binary log
file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

2、Mysql中的myisam与innodb的区别

1、InooDB支持事务，而MyISAM不支持事务

2、InnoDB支持行级锁，而MyISAM支持表级锁

3、InnoDB支持MVCC，而MyISAM不支持

4、InnoDB支持外键，而MyISAM不支持

5、InnoDB不支持全文索引，而MyISAM支持

<2>InnoDB引擎的四大特性

插入缓冲，二次写，自适应哈希索引，预读

<3>InooDB和MyISAM的select count（*）哪个更快，为什么

myisam更快，因为myisam内部维护了一个计算器，可以直接调取。MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

3、MySQL中的varchar和char的区别以及varchar（50）中的50代表的涵义

（1）varchar和char

char是一种固定长度的类型，varchar则是一种可变长度的类型

（2）varchar（50）的涵义

最多存放50个字符，varchar（50）和（200）存储hello所占空间一样，但后者在排序时会消耗更多的内存，因为order by
col采用fixed_length计算col长度（memory引擎也一样）

4、InnoDB的事务与日志的实现方式

（1）有多少种日志

错误日志：记录出错信息，也记录一些警告信息或者正确的信息

查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行

慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中

二进制日志：记录对数据库执行更改的所有操作

中继日志，事务日志。

（2）事务的4种隔离级别

读未提交

读已提交

可重复读

串行化

读未提交产生脏读问题：
#首先，修改隔离级别 set tx_isolation='READ-UNCOMMITTED'; select @@tx_isolation;
+------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED
| +------------------+ #事务A：启动一个事务 start transaction; select * from tx;
+------+------+ | id | num | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 |
+------+------+ #事务B：也启动一个事务(那么两个事务交叉了) 在事务B中执行更新语句，且不提交 start transaction;
update tx set num=10 where id=1; select * from tx; +------+------+ | id | num |
+------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +------+------+
#事务A：那么这时候事务A能看到这个更新了的数据吗? select * from tx; +------+------+ | id | num |
+------+------+ | 1 | 10 | --->可以看到！说明我们读到了事务B还没有提交的数据 | 2 | 2 | | 3 | 3 |
+------+------+ #事务B：事务B回滚,仍然未提交 rollback; select * from tx; +------+------+ |
id | num | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +------+------+
#事务A：在事务A里面看到的也是B没有提交的数据 select * from tx; +------+------+ | id | num |
+------+------+ | 1 | 1 | --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！ | 2
| 2 | | 3 | 3 | +------+------+

读已提交这种隔离级别出现的问题是——不可重复读(Nonrepeatable
Read)：不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

(1)这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
(2)它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变
#首先修改隔离级别 set tx_isolation='read-committed'; select @@tx_isolation;
+----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED |
+----------------+ #事务A：启动一个事务 start transaction; select * from tx;
+------+------+ | id | num | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 |
+------+------+ #事务B：也启动一个事务(那么两个事务交叉了) 在这事务中更新数据，且未提交 start transaction;
update tx set num=10 where id=1; select * from tx; +------+------+ | id | num |
+------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +------+------+
#事务A：这个时候我们在事务A中能看到数据的变化吗? select * from tx; ---------------> +------+------+ |
| id | num | | +------+------+ | | 1 | 1 |--->并不能看到！ | | 2 | 2 | | | 3 | 3 | |
+------+------+ |——>相同的select语句，结果却不一样 | #事务B：如果提交了事务B呢? | commit; | | #事务A: |
select * from tx; ---------------> +------+------+ | id | num | +------+------+
| 1 | 10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化 | 2 | 2 | | 3 | 3 | +------+------+

可重复读

(1)这是MySQL的默认事务隔离级别
(2)它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
(3)此级别可能出现的问题——幻读(Phantom
Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行
#首先，更改隔离级别 set tx_isolation='repeatable-read'; select @@tx_isolation;
+-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ |
+-----------------+ #事务A：启动一个事务 start transaction; select * from tx;
+------+------+ | id | num | +------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 |
+------+------+ #事务B：开启一个新事务(那么这两个事务交叉了) 在事务B中更新数据，并提交 start
transaction; update tx set num=10 where id=1; select * from tx; +------+------+
| id | num | +------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 | +------+------+
commit; #事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？ select * from tx; +------+------+ | id
| num | +------+------+ | 1 | 1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题) | 2 |
2 | | 3 | 3 | +------+------+ #事务A：只有当事务A也提交了，它才能够看到数据变化 commit; select * from
tx; +------+------+ | id | num | +------+------+ | 1 | 10 | | 2 | 2 | | 3 | 3 |
+------+------+

可串行化

(1)这是最高的隔离级别
(2)它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
(3)在这个级别，可能导致大量的超时现象和锁竞争
#首先修改隔离界别 set tx_isolation='serializable'; select @@tx_isolation;
+----------------+ | @@tx_isolation | +----------------+ | SERIALIZABLE |
+----------------+ #事务A：开启一个新事务 start transaction;
#事务B：在A没有commit之前，这个交叉事务是不能更改数据的 start transaction; insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1; ERROR 1205 (HY000): Lock wait timeout
exceeded; try restarting transaction

5、一张表里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把mysql重启，再insert一条记录，这条记录的ID是18还是15
？

6、mysql为什么用自增列作为主键

作者：在云端
链接：https://juejin.im/post/5b1480646fb9a01e6b2c0b82

*
如果我们定义了主键(PRIMARY
KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

*

数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

*
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

*

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE
TABLE来重建表并优化填充页面。

7、为什么使用数据索引能提高效率

* 数据索引的存储是有序的
* 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
* 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)
8、B+树索引和哈希索引的区别

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且叶子节点的指针相互链接，是有序的

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从跟节点到叶子节点逐级查找，只需要一次哈希算法即可，是无序的

哈希索引的优势：

* 等值查询。哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）
哈希索引不适用的场景：

* 不支持范围查询
* 不支持索引完成排序
* 不支持联合索引的最左前缀匹配规则
通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：
select id,name from table where name='李明'; — 仅等值查询复制代码

而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index
key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

注意：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

9、B树和B+树的区别

1、B树，每个节点都存储key和data，所有的节点组成这可树，并且叶子节点指针为null，叶子节点不包含任何关键字信息

2、B+树，所有的叶子节点中包含全部关键字的信息，及指向含有这些关键字记录的指针，且叶子节点本身依关键字的大小自小到大的顺序链接，所有的非终端节点可以看成是索引部分，节点中仅含有其子树根节点中最大（或最小）关键字

10 、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

*
B+的磁盘读写代价更低
B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

*
B+-tree的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

11、 mysql联合索引

*
联合索引是两个或更多个列上的索引。对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key
index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
* 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引
不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知
道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
12、什么情况下应不建或少建索引

* 表记录太少
* 经常插入、删除、修改的表
*
数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
* 经常和主字段一块查询但主字段索引值比较多的表字段
13、MySQL分区

什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

表分区有什么好处？

* 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 2. 和单个磁盘或者文件系统相比，可以存储更多数据
*
优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
* 分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
* 可与使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。
14、分区表的限制因素

1、一个表最多只能有1024个分区

2、MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

3、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

4、分区表中无法使用外键约束

5、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

15、如何判断当前Mysql是否支持分区？

命令：show variables like '%partition%'

16、Mysql支持的分区类型有哪些？

1、RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

2、List分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照list中的值分区，与RANGE的区别是，range分区的区间范围值是连续的

3、HASH分区：这种模式允许通过对表的一个或多个列的Hash
Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表

4、KEY分区：上面Hash模式的一种延伸，这里的Hash Key是Mysql系统产生的

17、行级锁定的优点

1、当在许多线程中访问不同的行时只存在少量锁定冲突

2、回滚时只有少量的更改

3、可以长时间锁定单一的行

18、行级锁定的缺点

1、比页级或表级锁定占用更多的内存

2、当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁

3、如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多

4、用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定

19、Mysql优化

1、开启查询缓存，优化查询

2、explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序

3、当只要一行数据时使用limit 1，Mysql数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据

4、为搜索字段建索引

5、使用ENUM而不是VARCHAR，如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR

6、Prepared Statement Prepared
Statements很像存储过程，是一种运行在后台的sql语句集合，我们可以从使用prepared
statement获得很多好处，无论是性能问题还是安全问题。Prepared
Statements可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击

7、垂直分表

8、选择正确的存储引擎

20、key和index的区别

1、key是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary
key，unique key，foregin key等

2、index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等

热门工具换一换