mysql 一些知识点面试用

爱吃肉c

已于 2024-08-17 17:58:55 修改

阅读量512

点赞数 4

分类专栏： mysql 文章标签： mysql 数据库

于 2024-08-16 11:20:42 首次发布

本文链接：https://blog.csdn.net/qq_45906101/article/details/141254200

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql

1、4个隔离级别与3个现象
2、快照读与当前读
- 2.1 可重复读的情况下出现幻读问题的两种情况
3 数据库常用引擎
4、InnoDB存储引擎对MVCC的实现
5、索引(重点)
6 存储过程
7、事务日志
- 7.1 重做日志

1、4个隔离级别与3个现象

SQL 标准定义了四个隔离级别：
READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。 (INNODB 默认的隔离级别)
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
在这里插入图片描述
三个现象

脏读 ：即读取未提交的数据

不可重复读：即读取同一个数据行，内容不一致

幻读：
不可重复读关注的是同一条记录在两次读取之间被其他事务修改的情况。
幻读关注的是同一查询条件下，结果集中行数在两次读取之间发生变化的情况。

2、快照读与当前读

在可重复读的隔离级别下

快照读解决幻读问题采用MVCC 多版本并行控制
即undo 与redo
在这里插入图片描述
当前读解决幻读问题采用 next-key lock 即间隙+记录锁
间隙锁防止增加数据记录锁防止修改删除数据
当前读只是阻止了对当前查询数据的锁定，大多数是数据表中的某几行，但是如果采用并行化则会阻止整个数据库。
在这里插入图片描述

2.1 可重复读的情况下出现幻读问题的两种情况

① 刚开始采用快照读后来又采用当前读则会导致幻读问题
②刚开始采用快照读，但是后来执行了DML语句，又执行快照读，则也会出现幻读。

可重复读的隔离级别不会完全防止幻读的出现。

如果想尽量出现幻读，则在刚开始时就进行当前读，对要读取的数据进行锁定/

3 数据库常用引擎

在这里插入图片描述

4、InnoDB存储引擎对MVCC的实现

InnoDB存储引擎对MVCC的实现

5、索引(重点)

5.1 什么是索引

索引是一种能够提高检索(查询)效率的提前安排好序的数据结构。
索引是解决sql慢查询的一种方式。

mysql 索引数据结构默认采用B+树

5.2 索引的创建与删除

会自动添加索引的情况

在这里插入图片描述

5.2.1 查看表中有哪些索引

show index  from 表名

5.2.2 添加索引

在这里插入图片描述

5.2.3 删除索引

在这里插入图片描述

5.3 索引的分类

在这里插入图片描述
按照数据结构：
B+索引：
Hash索引：只有Memory引擎支持该索引
按照物理存储方式：
非聚集索引(索引与表的内容不在一起，一张表可以有多个索引
聚集索引(索引与表的内容数据在一起，一张表只能有一张索引
按照字段的特性分类：
主键索引
唯一索引
普通索引
全文索引(对字段内容有约束，字段类型必须是文本内容
按照字段个数分类：
单列索引，联合索引（复合索引，组合索引

5.4 树数据结构

树的高度越低，性能越高,因为每一个节点都是一次IO

5.4.1 二叉树排序二叉树

不在赘述
会有极端情况
在这里插入图片描述

5.4.2 红黑树（自平衡二叉树

自己平衡，但没有平衡二叉树那样严格

在这里插入图片描述

缺点：数据量很庞大，由于每个节点只有两个分叉，树的高度仍然很高。

5.4.3 B 树

更矮更胖。查询效率会更高，每个结点不止存储一个数据。
3阶的话，一个结点可以存储两个数据,

在这里插入图片描述

5.4.4 B+ 树

mysql采用16阶。非叶子结点只存放索引值，叶子结点才存放数据。

在这里插入图片描述

5.5 其他索引及相关调优

5.5.1 哈希索引

在这里插入图片描述

5.5.2 聚集(簇)索引和非聚集(簇)索引

在这里插入图片描述

InnoDB 中，一个表只能有一个聚集索引，通常由主键承担。
为其他字段添加索引会创建非聚集索引，这些索引不会改变数据的物理存储顺序，也不会成为聚集索引。
非聚集索引的叶子节点存储的是索引字段的键值和指向数据行的指针，而聚集索引直接存储数据行。

在这里插入图片描述
优点就是效率高，查询的时候能直接查询到数据。
但是缺点是由于数据保存在b+树上，就会导致，我们删除修改插入数据时需要更新索引树，有开销，而不是去直接更新物理上具体的表内容。

5.5.3 二级索引

在这里插入图片描述

假设有一张表 my_table，定义如下：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    INDEX (age)
) ENGINE=InnoDB;

主键 id 是聚集索引，表中的数据将按照 id 的顺序存储。
索引 age 是非聚集索引，它的叶子节点存储的是 age 的值以及与该 age 对应的 id 值。当你通过 age 字段查询时，数据库首先通过 age 索引找到对应的 id，然后再根据 id 从表中获取完整的数据行。

5.5.4 覆盖索引

在这里插入图片描述

5.5.5 索引下推

通过减少回表次数，通常建立在多列索引的基础之上。
在这里插入图片描述
覆盖索引其实是不需要回表了但是索引下推仍然是需要回表的只是会减少回表次数

5.5.6 单一索引和复合索引(联合索引

5.5.6.1 索引最左匹配

CREATE TABLE test (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Co11 INT,
    Co12 INT,
    CO13 INT
);

CREATE INDEX idx_c1c2 ON test(Co11, Co12);
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/a136067884c24a5a855d2bd122e06573.png)

执行

EXPLAIN SELECT * FROM test t WHERE Co11 =100 AND Co12 = 100;

我们可以看到是用索引来检索的
在这里插入图片描述

只用Col1,也是进行的范围搜索
在这里插入图片描述

如果只有cOL2 则就是全表扫描。
在这里插入图片描述

在这里插入图片描述

5.6 索引优缺点

在这里插入图片描述

5.7 何时用索引

在这里插入图片描述

5.8 SQL性能分析工具

在这里插入图片描述

5.9 索引失效的情况

5.9.1 索引列参加了运算

没参加运算正常情况：
在这里插入图片描述

参加了运算：索引失效。全表扫描
在这里插入图片描述

5.9.2 模糊查询以%开始

上述已经有过案例了，不再阐述。

5.9.3 索引列是字符串类型，但查询时省略了单引号

CREATE TABLE test1 (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Co11 VARCHAR(255),
    Co12 INT,
    CO13 INT
);

CREATE INDEX idx_c1c2 ON test1(Co11, Co12);