mysql 一些知识点 面试用

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);

5.9.4 查询条件中有or，只要有未添加索引的字段，索引失效

因为索引加在col1 和col2 上，两个是一起的，所以or col2 和仅仅使用col2是相同的，都会全表扫描，但是如果是and 就是索引。

5.9.5 当查询的符合条件的记录在表中占比较大，索引失效

因为这时候 和全表扫描 没什么区别了，使用索引还需要回表，所以不会有什么提升。

5.9.6 关于is nuIl和is not null的索引失效问题

和 上述 占比较大 是一样的。
如果把表中的一列都设置为空，如果你查询都为空的则都占了，直接全表扫描了。

6 存储过程

数据库内置的编程语言。
优缺点：

7、事务日志

7.1 重做日志