Java八股文系列一(mysql篇)

目录

事务

四大属性

事务隔离

幻读

锁种类

共享锁、排它锁

记录锁（Record Locks）

间隙锁（Gap Locks）

临键锁（Next-Key Locks）

索引

聚簇索引和非聚簇

Inndb的表是否必须要聚簇索引

辅助索引

数据结构

B+树

MVCC

设计优化

三范式

QA

回表查询

深分页

索引失效

---

前言

mysql是web应用程序必备的数据库之一，学习mysql的实现原理帮助我们快速深入理解mysql的运行机制。

事务

四大属性

1. 原子性（Atomicity）

        原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。通过undolog实现。

1. 一致性（Consistency）

        事务前后数据的完整性必须保持一致。

1. 隔离性（Isolation）

        事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。通过加锁和MVCC实现。

1. 持久性（Durability）

        持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。通过redo log实现。

事务隔离

幻读

• 使用“串行化”隔离级别： 将事务隔离级别设置为“串行化”，可以避免幻读问题，但会影响并发性能。
• 使用锁： 在查询操作时对相关数据行加锁，可以阻止其他事务对这些数据行进行插入或删除操作。
• 使用MVCC（多版本并发控制）： MySQL中使用MVCC可以在一定程度上解决幻读问题，通过使用版本号控制事务间的隔离。
• 使用索引覆盖扫描： 使用覆盖索引扫描可以减少查询的范围，从而降低幻读的概率。

锁种类

共享锁、排它锁

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

-- 加共享锁（S）

select * from table_name where ... lock in share mode

-- 加排它锁（X)

select * from table_name where ... for update

表级锁(MyISAM)、行级锁(InnoDB)、页级锁(BerkeleyDB)

记录锁（Record Locks）

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。比如

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

需要注意的是：

• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

间隙锁（Gap Locks）

间隙锁 是 Innodb 在 RR(可重复读) 隔离级别 下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101，下面的SQL：

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

临键锁（Next-Key Locks）

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

索引

聚簇索引和非聚簇

聚簇索引叶子节点存放一整行数据。非聚簇索引存放的是聚簇索引的key。InnDB主键使用的就是聚簇索引。MyISAM不管是主键索引，还是二级索引使用的都是非聚簇索引。

InnfoBD的聚簇索引是按主键顺序构建的B+tree.B+tree的叶子节点就是行记录，行记录和主键紧凑的存储在一起。

Inndb的表是否必须要聚簇索引

1. 如果表定义了主键，则主键索引就是聚簇索引。
2. 如果表没有定义主键，则第一个非空unique列作为聚簇索引
3. 否则会创建一个隐藏的row-id作为聚簇索引

辅助索引

        也叫二级索引，是根据列索引构建B+tree结构。但在B＋tree的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多。可以创建多个辅助索引。

数据结构

B+树

        InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。在B+树中，一个结点就是一页。非叶子结点由主键值和一个指向下一层的地址的指针组成的组合组成。叶子结点中由一组键值对和一个指向该层下一页的指针组成，键值对存储的主键值和数据。由存储结构，可以大概计算出一个B+树能存储的数据数量。

指针在InnoDB中为6字节，设主键的类型是bigint，占8字节。一组就是14字节。计算出一个非叶子结点可以存储16 * 1024 / 14 = 1170个索引指针。假设一条数据的大小是1KB，那么一个叶子结点可以存储16条数据。得出两层B+树可以存储1170 x 16 = 18720 条数据。三层B+树可以存储1170 x 1170 x 16 = 21902400条数据。

MVCC

        Multi-Version Concurrency Control，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。

设计优化

三范式

第一范式(1NF):表的数据不可分，数据项不可分。

第二范式(2NF):实体属性完全依赖主关键字。

第三范式(3NF):数据表中的字段不能有依赖传递

QA

回表查询

        通过普通索引查询方式，则需要先搜索该索引树，然后得到主键 ID的值，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

深分页

        查询偏移量过大的分页会导致数据库获取数据性能低下，以如下SQL为例：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后，再获取10条记录，其性能可想而知。这种查询偏移量过大的场景我们称为深分页。

MySQL的深分页会带来性能下降等问题，而这个问题在分布式数据库场景下，会变得更加复杂。

索引失效

1. 使用or
2. 复合索引会失效
3. like查询
4. 索引查询时使用函数
5. 隐式类型转换
6. 对索引进行表达式计算