MySQL面试题

MySQL隔离级别

并发访问数据库时存在的问题

  • 脏读：又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改但是还未提交，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改并提交，这就导致了T2所读取到的数据是无效的，值得注意的是，脏读一般是针对于update操作的。
  • 不可重复读：是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
  • 幻读：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。

插入删除数据，修改整个数据结构，被另一个线程读取到的是幻读，
而不可重复读是修改数据的值导致的。

当然, 从总的结果来看, 似乎两者都表现为两次读取的结果不一致.
但如果你从控制的角度来看, 两者的区别就比较大

  • 对于前者, 只需要锁住满足条件的记录
  • 对于后者, 要锁住满足条件及其相近的记录

四种隔离级别：

隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
读提交（read-committed） 否 是 是
可重读（repeatable-read） 否 否 是
可串行化（serializable） 否 否 否

read-uncommitted 读未提交：

事务可以读取其他事务尚未提交的数据。
本隔离级别很少用于实际应用，因为它的性能不比其他级别好多少。读取未提交的数据，也称之为脏读。

read-committed 读提交内容：

事务只能读取已经提交的数据。
这是大多数数据库系统的默认隔离级别（但不是MYSQL默认的），它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。但可能出现不可重复读（Non-Repeatable Read）和幻读（Phantom Read）问题。

repeatable-read 可重读：

事务执行期间，保证多次读取同一数据的结果是一致的。
是MYSQL默认的
通过读取记录时加锁（行锁）来实现，避免了不可重复读问题。

serializable 可串行化：

最高的隔离级别，完全串行化执行事务，确保事务之间没有并发操作。
简而言之，他是在每个读的数据行上加上共享锁。在这个级别可能导致大量的超时现象和锁竞争。

MySQL的复制原理

随机读写（效率更差）
顺序读写

异步复制过程
总体来说，复制有3个步骤：
1、主服务器把数据更改记录到二进制日志中。（这叫做二进制日志事件）
2、从服务器把主服务器的二进制日志拷贝到自己的中继日志中。
3、从服务器重放中继日志中的事件，把更改应用到自己的数据上。
这只是概述，每一个步骤都很复杂。下图更清晰描述了复制的过程。
imagepng

MySQL为什么需要主从同步：

  • 读写分离：让主库负责写，从库负责度

  • 做数据的热备：访问量激增，做备份，备机不参与业务，当主机挂了，备机上场

  • 架构扩展：业务量过大，进行分库分表，类似查询请求的效率能够保证

    MySQL聚簇索引和非聚簇索引的区别

    索引存储在硬盘当中

  • InnoDB引擎 ===》索引与数据文件未分开：非聚簇索引+ 1个聚簇索引

    • .frm 是表结构
    • .ibd 是索引加内容

  • MyISAM引擎 ===》索引与数据文件分开：非聚簇索引

    • .MYD 是数据文件
    • .MYI 是索引文件

InnoDB引擎中主键默认为索引，没有主键默认唯一键为索引，没有唯一键则会自动生成6个字节的rowid

为什么聚簇索引只能有一个

因为InnoDB的引擎的存储数据结构为B+Tree的叶子结点直接存储索引对应的数据，若有第二个索引，第二个索引叶子节点存放就是该数据对应的聚簇索引。

索引的基本原理

  1. 为什么用索引：提高查询效率
  2. 什么是索引：是一种特殊的键，帮助快速找到数据的一种数据结构
  3. 索引的原理：通过不断地索引小范围筛选范围，得到最终结果
  4. 索引的数据结构：
  5. Hash索引 ==> 查找单条数据极快，不支持范围查找与排序（效率极低） Memory引擎
  6. B+Tree索引 ==> 单条数据查找速度不及Hash索引，但更适合范围查找与排序 InnoDB、MyISAM引擎
  7. B+Tree在实现索引上的优势及过程

红黑树

imagepng
红黑树是一种自平衡的二叉查找树，是一种高效的查找树。
红黑树具有良好的效率，它可在 **O(logN) **时间内完成查找、增加、删除等操作。
以红黑树为例，红黑树通过如下的性质定义实现自平衡：

  • 节点是红色或黑色。

  • 根是黑色。

  • 所有叶子都是黑色（叶子是NIL节点）。

  • 每个红色节点必须有两个黑色的子节点。（从每个叶子到根的所有路径上不能有两个连续的红色节点。）

  • 从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点（简称黑高）。

    B树与B+树

    B+树一种多路平衡搜索树。
    B+树只有叶子节点存放数据库中的数据，且有指向前后数据节点的指针。
    因此其能存储更多数据且查找效率更高，且支持排序范围查找等操作。

由于红框框起来的一个页大小是固定的，因此B+树除了叶子节点外，每个节点可以拥有更多的分支，因此能存储更多的数据。
imagepng

MyISM引擎的索引数据结构

主键索引
imagepng

辅助索引
imagepng
可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

InnoDB引擎的索引数据结构

主键索引
imagepng

辅助索引
imagepng

最左匹配原则

最左匹配原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：

select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引（a,b,c）,用上a,b,c 
select * from t where a=1 and b=1;     #这样可以利用到定义的索引（a,b,c）,用上a,b 
select * from t where b=1 and a=1;     #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器） 
select * from t where a=1;     #这样也可以利用到定义的索引（a,b,c）,用上a 
select * from t where b=1 and c=1;     #这样不可以利用到定义的索引（a,b,c） select * from t where a=1 and c=1;     #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到

也就是说通过最左匹配原则你可以定义一个联合索引，但是使得多中查询条件都可以用到该索引。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：

select * from t where a=1 and b>1 and c =1; #这样a,b可以用到（a,b,c），c索引用不到

这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。
但是如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引，因为优化器会自动改写为最优查询语句

select * from t where a=1 and b >1 and c=1;  #如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引 #优化器改写为 
select * from t where a=1 and c=1 and b >1;

这也是最左前缀原理的一部分，索引index1:(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段。
另外还有一个特殊情况说明下，select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的也只会有 a与b 走索引，c不会走。
像select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。
以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。
我们都知道索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的健值数量不是一个，而是多个。构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。
例子：假如创建一个（a,b,c)的联合索引，那么它的索引树是这样的：
imagepng

MySQL锁的类型有哪些？

  • 按锁的属性分类：
    • 共享锁（读锁）
    • 排它锁（写锁）
  • 按锁的粒度分类：
    • 行级锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    • 表级锁：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
    • 页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
  • 基于锁的状态分类：
    • 意向共享锁
    • 意向排它锁
  • 行锁 ：也叫记录锁 锁定的是某一行一级
  • 间隙锁 ：锁定的是记录与记录之间的空隙，间隙锁只阻塞插入操作，解决幻读问题
  • 临键锁 ：nextkeylock 是行锁与间隙锁的并集，是mysql加锁的基本单位

MySQL的执行计划怎么看

业务中大量需要看执行计划
怎么加索引需要参考执行计划

explain select * from t_user;
  • id：如果ID相同，执行顺序从上至下。如果ID不同，ID越大执行的优先级越高
  • table：执行的具体是哪个表
  • type： system -> const -> eq-ref -> ref -> fulltext ->...->range -> index ->all
    • system效率最高，尽量保证range，拒绝出现all
  • key：当前选择的索引
  • rows：当前选择的预估值
  • extre：使用的技巧点

事务的概念

在MySQL环境中，事务是由一个单元的一个或多个SQL语句组成，这个单元的每个SQL语句是相互依赖的，而且单元作为一个整体是不可分割的。如果单元中的一个语句不能完成，整个单元就会回滚（撤销），所有影响到数据将返回到事务开始以前的状态，因此，只有事务中所有SQL语句执行成功，则才能说明这个事务被成功执行。使用一个简单的例子来理解事务：

posted @ 2024-03-06 17:27  岁  阅读(23)  评论(0)  编辑  收藏  举报