mysql 数据库 相关 概念知识

1、脏读、不可重复读、幻读 概念

• 脏读：并发情况下，A事务将价格修从90改为100元，但未提交事务，B事务读取到了这100元。事务之间可以互相访问数据一样，不同的事务之间没有隔离
• 不可重复读：并发情况下，B事务读取到了一条记录，此时A事务修改了这条记录的一个字段：价格从90 修改为100，B事务又读取了此记录，读取两次，发现记录居然不一样，这里注意没有要求A事务非要提交事务，也就说就算A提交事务，对于B事务来说也是不一样的记录。
• 幻读：并发情况下，A事务读取了一条记录，此时B事务插入一条记录，A事务又读取，读到了两条数据，此时就造成了读取数据不一致，这里也要注意，也没要求B事务是否提交

隔离等级对 事务并发问题的解决

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这个隔离级别最低，最为脆弱，什么问题都会发生，没什么好解释的，过。

• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。问题来了，为啥可以阻止脏读，其他阻止不了，因为脏读是读取其他事务中的数据，本隔离等级只能读取已经提交的事务，所以可以阻止脏读

  模拟场景：如果事务A读取了价格为90，B事务修改了记录，将价格改为了100，提交B事务，此时A事务再读取这条记录，就会得到100这个价格，此时就造成了不可重复读，同样对于幻读也可以这么理解。

• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  REPEATABLE-READ 这个隔离级别肯定也是只允许访问 已提交事务的数据，感觉是在第二个隔离级别上做的一些工作，不太清楚它是怎么实现 避免 不可重复读的，这个需要弄懂一些。

• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。所有的并发事务都变成单线程事务，一个一个执行。

MySQL 事务隔离实验-认识：脏读、不可重复读、幻读

ACID 理解

一致性 Consistency

一致性：教科书式的定义事务必须是使数据库从一个一致性状态变到另一个一致性状态。通俗理解：

• 在事务T开始时，此时数据库有一种状态，这个状态是所有的MySQL对象处于一致的状态，例如数据库完整性约束正确，日志状态一致等。
• 当事务T提交后，这时数据库又有了一个新的状态，不同的数据，不同的索引，不同的日志等，但此时，约束，数据，索引，日志等MySQL各种对象还是要保持一致性（正确性）。 这就是 从一个一致性的状态，变到另一个一致性的状态。

事务执行前后，状态都被记录下来，并没有破坏数据库的完整性约束（一切都是对的）。MySQL数据库innodb的事务，是通过redo log（innodb log)，undo log，锁机制，来维护这个一致性的。也有人理解为一致性体现在2个方面：一致读和一致写。

• 一致写：事务执行的数据变更只能基于上一个一致的状态，且只能体现在一个状态中。T(n)的变更结果只能基于C(n-1)，C(n-2), …C(1)状态，且只能体现在C(n)状态中。一个状态只能有一个事务变更数据，不允许有2个或者2个以上事务在一个状态中变更数据。至于具体一致写基于哪个状态，需要判断T(n)事务是否和T(n-1)，T(n-2),…T(1)有依赖关系。
• 一致读：事务读取数据只能从一个状态中读取，不能从2个或者2个以上状态读取。也就是T(n)只能从C(n-1），C(n-2)… C(1)中的一个状态读取数据，不能一部分数据读取自C(n-1)，而另一部分数据读取自C(n-2)。

这个理解 可以方便大家更了解。

数据库有 ACID 四大特性为啥还会有脏读等问题

原子性、一致性、隔离性、持久性 是 ACID 四大特性，那么为啥还会出现脏读等问题，其实这个问题在上面的事务隔离级别中就有提到，完全的ACID事务，性能比较差，高并发情况下性能不会太好，所以才有了其他隔离级别，用来不同的场景，基本上，大家都设置的隔离级别都是 READ - COMMITTED(读取已提交)。这个也够用了。

数据库锁概念

锁：锁的主要作用是管理共享资源的并发访问，用于实现事务的隔离性。
那么问题来了，锁一定是不可访问的吗？锁类型有共享锁、排它锁。表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（x）.

• 共享锁（读锁、S锁）：共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。select 一般都是共享锁，显式的加上共享锁：select … lock in share mode语句，除了select …for update语句，这是排它锁。

• 排它锁（写锁、X锁）：排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，获取到排他锁的事务是可以对数据就行读取和修改。加排他锁可以使用select …for update语句，update,delete,insert都是排它锁。

乐观锁、悲观锁是从是否锁数据方面进行分类，共享锁、排它锁是从锁的类型进行分类

InnoDB存储引擎支持表锁和行锁。顾名思义，表锁是锁住整张表，行锁只是锁住某些行。

• InnoDB通过给索引项加锁来实现行锁，
• 如果没有索引，则通过隐藏的聚簇索引来对记录加锁。
• 如果操作不通过索引条件检索数据，InnoDB 则对表中的所有记录加锁，实际效果就和表锁一样。

所以锁的情况跟索引有很多关联，

锁算法

InnoDB存储引擎对于任何操作是先锁数据，再查询，比如你要更新一条数据，首先根据where条件 的索引来锁数据，然后再查询数据，更新数据。如果你的where条件 没有一个索引可以匹配上，那么整个表都会加上行锁，相当于表锁一样的效果。

InnoDB存储引擎 行锁 有3种算法：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。

这些gap lock 、 Next-key lock 是基于普通索引的，聚蔟索引会直接升级到record lock。

例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要：

• 使用Record Lock将10，11，13，20四个索引锁住
• 使用Gap Lock将(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五个范围区间锁住。
• Next-Key Locking类似于上述两种锁的结合，它可以锁住的区间有为(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)，可以看出它即锁定了一个范围，也会锁定记录本身。

为啥 根据索引来锁数据？

为啥 没有数据，也要锁范围？比如上面就四个索引，可以认为就4条记录，间隙锁锁的范围是(13,20)范围，但这中间又都没有记录，为什么这么做？
猜测是担心有数据插入，所以直接把这个范围锁住了，也就是解决幻读，

Next-Key Lock与Gap Lock 有什么区别？

因为前面两个似乎已经够了，其实gap lock 没有应用场景的，它只是一个算法用来支撑Next-Key Lock，Next-Key Lock 等于 Record Lock + gap lock。所以实际应用都是Next-Key Lock算法，它会调用gap lock。

InnoDB引擎采用Next-Key Lock来解决幻读问题。因为Next-Key Lock是锁住一个范围，这个范围内是不会插入任何数据的，其他事务也不能插入数据进来然后提交事务，也就不会产生幻读问题。但是需要注意的是，InnoDB只在Repeatable Read隔离级别下使用该机制。

聚蔟索引

这个网上介绍的资料非常多，但是一个问题就是，网上都比较抽象，但我的问题就是InnoDB 中的B+树索引是什么索引？ 后面找我公司的DBA咨询了此问题，他说这个就是一个聚簇索引，而且细想了一下B+树索引+数据行其实就一个聚簇索引，而且有资料说如果没有主键，innodb会有一个默认的主键，我估计就是利用这个默认的主键来做聚簇索引，否则就不会强调这一点，聚簇索引保证了数据和索引都是在一起的。所以说聚簇索引并不是一种单独索引，而是一种数据储存方式。

另一个问题就是聚簇索引有几层，正常来说聚簇索引最多不超过4层，因为它不是一颗二叉树，其实根节点就可以有多个子节点，这样基本上就可以不需要太多层来做这么一件事了。

聚簇索引和非聚簇索引都在一个文件里。
innoddb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以在一个表中只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。因为存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

InnoDb将通过主键聚集数据。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。IInnoDB值聚集在同一个页面中的记录。包含相邻键值的页面可能会相距很远。

• 优点：
  1、数据访问快
  2、减少磁盘IO，因为每次读取一个数据页，数据页中有很多数据，后续的查询数据可能也在数据页中，就避免再读取一次磁盘IO
  3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值

• 缺点：
  1、更新聚簇索引的代价很高，因为会强制InooDB将每个更新的数据移动到新的位置
  2、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
  3、采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

参考博客：聚簇索引

索引覆盖

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
这里强调了，查询字段和查询条件都是索引字段，因为建表的时候可以建立多列索引，所以某些查询可以用到覆盖索引，比如：

select id,age from tb_student where age < 25

假设我们对id、age做了索引，这样上述sql就用到了覆盖索引。这样避免了查到索引后再返回表操作，减少I/O提高效率。

到以下情况，执行计划不会选择覆盖查询：

• 1.select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。
• 2.where条件中不能含有对索引进行like的操作。

当然这里要解释一下，聚簇索引与联合索引的概念问题，这两者就是一样的，覆盖索引只是一种概念，本质就是利用了联合索引。

索引 最左匹配算法

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列a、列b和列c 建一个联合索引，是一个索引树，逻辑上就会有三种情况会走索引树，分别是a,ab,abc。
也就说查询条件 顺序很重要，但即使查询条件不符合顺序，也是可以的，借助mysql查询优化器explain，explain会有一套算法来优化你的sql，尽可能的匹配的索引，纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划，失效规则有：

• 范围索引会导致后续的字段失效，比如b使用范围查找就会导致c用不上，但a，b还是可以用上索引的
• 不等于
• like
• 字段操作：函数、转换、类型强制转换
• is null，is not null就不能用索引

Mysql索引最左匹配原则

死锁

死锁是资源的竞争导致的，比如：事务A 需要两个记录，先修改一条记录a，再查询一个记录b，这时事务B 也先修改再查询，先修改记录b，再查询记录a，这样记录a、b都是排它锁，两个事务相互等待，这样就造成了死锁，
解决死锁：

• 扩大锁范围来实现，通过表级锁来减少死锁产生的概率；
• 多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；
• 同一个事务尽可能做到一次锁定所需要的所有资源。

唯一索引

unique key 也叫唯一索引，表明这一列数据不重复，有点像主键，但不是主键，区别：

• 一张表 主键只有一个，唯一索引可以有多个，也就是多个列是唯一索引
• 主键一般是数字行，唯一索引不限制字段类型
• 主键不能为null，但唯一索引可以

主键id 为什么要求是数字类型

其实也就是为啥不能是字符串：

• uuid占空间大， 如果你建的索引越多， 影响越严重。

• 写入的目标页可能已经写入到磁盘而不只是存在于内存中，又或者目标页还没有被加载到内存中，InnoDB 在插入前需要先找到并从磁盘中读取目标页到内存中去，这会产生大量的磁盘随机 IO。

• 字符串写入是乱序的，无法简单地总是把新行插入到索引的最后，InnoDB 需要频繁地做页分裂操作，一遍为新的行分配空间。页分裂需要移动大量数据。

uuid 优点：出现数据拆分、合并存储的时候，能达到全局的唯一性。

由于X锁与S锁互斥，T2和T3都等待对方释放S锁。

这个主要跟唯一索引有关系

Redo log, bin log, Undo log

• Redo log：是保证事务持久性
  Redo log记录了数据操作在物理层面的修改，mysql中使用了大量缓存，缓存存在于内存中，修改操作时会直接修改内存，而不是立刻修改磁盘，当内存和磁盘的数据不一致时，称内存中的数据为脏页(dirty page)。为了保证数据的安全性，事务进行中时会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中, redo log是按照顺序写入的，磁盘的顺序读写的速度远大于随机读写。当数据库或主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。这样实现了事务的原子性、一致性和持久性。

• Undo log：是保证事务原子性
  除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

页

• 计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节
• 文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k
• 对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。

在 MySQL 中我们的 InnoDB 页的大小默认是 16k，当然也可以通过参数设置，B+树的叶子节点就是存放数据的，这个数据就是页作为存储单位的，除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3 的页，该页存放键值和指向数据页的指针，这样的页由 N 个键值 + 指针组成。

高度为3的B+树可以存2千万左右的数据，因为innodb一页是16K，聚簇索引里面都是主键索引，主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16*1024/14=1170，也就是说根节点可以存放1170个指针，每个指针都对应着一个子结点，一个子结点也是16K，我们假设一行记录为1k，那么一个子节点可以存放16行记录，那么：

• 一个高度为2的B+树可以存放：1170*16=18720，
• 一个高度为3的B+树可以存放：1170 * 1170* 16=21902400

SELECT b.name,a.name,index_id,type,a.space,a.PAGE_NO FROM 
information_schema.INNODB_SYS_INDEXES a,information_schema.INNODB_SYS_TABLES b 
where 
a.table_id = b.table_id

社招、校招内推时刻

本人在阿里巴巴工作，业余时间做了社招、校招的公众号，可以内推大家，免筛选直接面试，公众号的一些文章也帮助大学、研究生的一些同学了解校招、了解名企，工作几年的同学想换工作也可以找我走社招内推，同时大家对文章有问题，也可以公众号找我，扫码关注哦！

参考博客

InnoDB一棵B+树可以存放多少行数据？