MySQL面试专题

2021-04-20  本文已影响0人  橙一万

基础

什么是事务？MySQL 事务四大特性 事务的隔离级别

事务是一组操作，组成这组操作的各个单元，要么全都成功要么全都失败，这个特性就是事务。
在 MySQL 中，事务是在引擎层实现的，只有使用 innodb 引擎的数据库或表才支持事务。

脏读：事务 A 读取了事务 B 更新后的数据，但是事务 B 没有提交，然后事务 B 执行回滚操作，那么事务 A 读到的数据就是脏数据 简而言之：事务A读取到了事务B 未提交的内容
不可重复读：事务 A 进行多次读取操作，事务 B 在事务 A 多次读取的过程中执行更新操作并提交，提交后事务 A 读到的数据不一致。
幻读：事务 A 将数据库中所有学生的成绩由 A -> B，此时事务 B 手动插入了一条成绩为 A 的记录，在事务 A 更改完毕后，发现还有一条记录没有修改，那么这种情况就叫做出现了幻读。

SQL的隔离级别：

这四个隔离级别可以解决 脏读、不可重复读、幻读 这三类问题。总结如下：

事务隔离级别 读数据一致性 脏读 不可重复读 幻读
读未提交（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是
读已提交（Read committed） 语句级 否 是 是
可重复读（Repeatable read） 事务级 否 否 是
串行化（Serializable） 最高级别，事务级 否 否 否

其中隔离级别由低到高是：读未提交 < 读已提交 < 可重复读 < 串行化
【注】隔离级别越高，越能够保证数据的完整性和一致性，但是对并发的性能影响越大。大多数数据库的默认级别是读已提交(Read committed)，比如 Sql Server、Oracle ，但是 MySQL的默认隔离级别是 可重复读(repeatable-read)。

能说下myisam 和 innodb的区别吗？

在 MySQL 中，事务是在引擎层实现的，只有使用 innodb 引擎的数据库或表才支持事务。
InnoDB 是 MySQL 默认支持的存储引擎，支持事务、行级锁定和外键。

MyISAM 存储引擎的特点

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，使用的场景比较少，主要特点是

Full-Text 索引：它的出现是为了解决针对文本的模糊查询效率较低的问题。
B-Tree 索引：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点
R-Tree索引：它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE，RTREE 的优势在于范围查找。

数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复。

InnoDB 存储引擎的特点

自从 MySQL 5.1 之后，默认的存储引擎变成了 InnoDB 存储引擎，相对于 MyISAM，InnoDB 存储引擎有了较大的改变，它的主要特点是

MyISAM 和 InnoDB 存储引擎的对比

image

SQL 的执行顺序

#-----正常的SQL顺序
SELECT DISTINCT <select_list>
FROM 
  <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>


#-----MySQL执行的顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT <select_list>
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

【注】每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者。

Mysql的索引有哪些

从数据结构角度

1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理
2、hash索引：
a 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持hash索引
3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

1、聚簇索引（聚集索引）（clustered index）

聚簇索引 就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
　　Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

2、非聚集索引（non-clustered index）

在聚簇索引之上创建的索引称之为辅助索引，如果建立的索引不能满足要查询的字段，辅助索引访问数据总是需要 二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

聚集索引和非聚集索引的区别如下：
　　1) 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦
具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。
　　2) 聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索
引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，
降低了执行速度。非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值（一般情况下是聚簇索引）
2、普通索引或者单列索引
3、复合索引（多列索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
4、唯一索引或者非唯一索引

那你知道什么是覆盖索引和回表吗？

首先回表，上面有隐示的提到过：如果建立的索引不能满足要查询的字段，辅助索引访问数据总是需要 二次查找。

由于普通索引无法直接定位行记录，通常情况下，需要扫描两遍索引树。先通过普通索引定位到主键值，再通过聚集索引定位到行记录。这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树效率更低。

什么是覆盖索引(Covering index)？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

索引的优缺点

索引是一种快速查找的数据结构

优点

缺点

锁的类型有哪些呢

按照锁操作类型

按照锁粒度

谈谈 SQL 优化的经验（常问）

我个人认为呢其实SQL优化不仅仅是对于SQL的优化，还有对索引的优化。什么是索引的优化呢，比如说我们怎么写SQL能更多的利用上索引，避免全表扫描。我们怎么建立索引能避免回表查询（查一次表和查两次表的速度 必定会有差距）等等。因为对于数据库来说，索引是一个能大大提升查询效率的数据结构（能有效减少磁盘IO），

那么怎么提升SQL的查询速度 我个人认为主要在于平常 怎么写SQL 和 怎么建立索引两个方面来控制（这两者缺一不可）

查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
不要直接使用 SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是 type = all。
为每一张表设置一个 ID 属性
避免在 WHERE 字句中对字段进行 NULL 判断
避免在 WHERE 中使用 != 或 <> 操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引
对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等
拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
进行水平切割或者垂直分割
“
水平分割：通过建立结构相同的几张表分别存储数据
垂直分割：将经常一起使用的字段放在一个单独的表中，分割后的表记录之间是一一对应关系。

高级

explain关键字

mysql> explain select * from employee where id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.32-log |
+------------+
1 row in set (0.03 sec)

【id】

id是select查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的执行顺序，有三种情况：id相同、id不同、id相同又不同。

1、id相同的情况下，数据表的执行顺序由上往下依次执行；
2、id不同的情况下，如果是子查询，id的序号递增，id值越大优先级越高，越先被执行；
3、id相同又不同的情况下，id如果相同，可以认为是一组，从上往下执行，在所有组中，id值越大，优先级越高，越先被执行；

select_type

select_type的类型主要有六个：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
select_type的作用：主要用于区分是否是子查询、联合查询、子查询等的复杂查询；

1、SIMPLE   简单的select查询，查询中不包括 子查询 或 UNION
2、PRIMARY  查询中包含任何复杂的子部分，最外层查询（最后加载的那个）则被标记为 PRIMARY 
3、SUBQUERY 在select或where列表中包含了子查询（括号里面的）
4、DERIVED  在from列表中包含的子查询被标志为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表里。
5、UNION    若第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层select将被标记为：DERIVED
6、UNION RESULT  从UNION表获取结果的SELECT（union的结果集）

【type】

type主要有：ALL、INDEX、RANGE、REF、EQ_REF、CONST、SYSTEM、NULL这几种值；
以上值从最好到最差依次是：system > const > eq_ref > ref > range > index > all

【possible_keys和key】

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不丢失精确性的情况下，长度越短越好；
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数（const），哪些列或常量被用于查找索引列上的值；

【rows】

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

【Extra】

什么原因会导致行锁升级为表锁

为什么索引失效会升级成表锁

个人理解，加索引后，读取数据按索引查找行，行锁；不加索引，会查询整张表，为了避免被其他线程进来，所以锁住整张表

文章参考

上一篇 下一篇

猜你喜欢

热点阅读