MySQL事务、索引数据结构

远山归云海
61 阅读9分钟

MySQL写入数据的流程

INNODB引擎

索引页

B 树

性质

首先介绍一下一棵 m 阶的 B 树的特性。m 表示这个树的每一个节点最多可以拥有的子节点个数。一棵 m 阶的 B 树满足的性质如下：

  1. 每个节点最多有 m 个子节点。

  2. 每一个非叶子节点（除根节点）最少有个子节点。

  3. 如果根节点不是叶子节点，那么它至少有两个子节点。

  4. 有 k 个子节点的非叶子节点拥有 k−1 个键，且升序排列，满足 k[i] < k[i+1]。

  5. 每个节点至多包含 2k-1 个键。

  6. 所有的叶子节点都在同一层。

B 树的基本操作有查找，遍历，插入，删除。

B+树

B+ 树是 B 树 的一个升级，它比 B 树更适合实际应用中操作系统的文件索引和数据库索引。目前现代关系型数据库最广泛的支持索引结构就是 B+ 树。

B+ 树是一种多叉排序树，即每个节点通常有多个孩子。一棵 B+ 树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。

一棵 m 阶 B+ 树的特性。m 表示这个树的每一个节点最多可以拥有的子节点个数。一棵 m 阶的 B+ 树和 B 树的差异在于：

  1. 有 n 棵子树的节点中含有 n-1 个关键字（即将区间分为 n 个子区间，每个子区间对应一棵子树）。
  2. 所有叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接。
  3. 所有的非叶子节点可以看成是索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。
  4. 除根节点外，其他所有节点中所含关键字的个数最少有 （注意：B 树中除根以外的所有非叶子节点至少有 截图 棵子树）。

B+ 树相比于 B 树的优势

  • 由于索引节点上只有索引而没有数据，所以==索引节点上能存储比 B 树更多的索引==，这样树的高度就会更矮。树的高度越矮，磁盘寻道的次数就会越少。
  • 因为数据都集中在叶子节点，而==所有叶子节点的高度相同==，那么可以在叶子节点中增加前后指针，指向同一个父节点的相邻兄弟节点，这样可以更好地支持查询一个值的前驱或后继，使连续访问更容易实现。

索引优化

查看是否用了索引

==explain ==select * from test where id =1;

mysql> explain select * from student where id=1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

Explain列的含义

explain extended :能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到，从而看出优化器优化了什么。

explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

参考博客：blog.csdn.net/liruichuan/…

  • id：id列的编号是 select 的序列号，有几个 select 就有几个id，id不同，id值越大，优先级越高，越先 执行

  • select_type ：显示本行是简单或复杂select。如果查询有任何复杂的子查询，则外层标记为 PRIMARY（DERIVED、UNION、UNION RESUlT）

  • table ：访问查询的表名或表别名|

  • type ：表的访问类型（ MySQL 如何查询表中的行记录）效率高低：const eq_ref/ref/range/index/all

    最优到最差为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    • const, system：Mysql查询对其进行优化并转化为一个常量，只查询一次就搜索出结果，用于 primary key 或 unique key 的所有列与常数比较时，所以表多有一个匹配行，读取1次，速度比较快。
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或索引扫描
    • ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者联合索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行.
    • ref_or_null：与ref类型差不多，但是这种类型可以搜索为Null的行
    • index_merge：使用了索引合并的优化方法
    • range： 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
    • index：Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小
    • All:扫描全表，与index其实都是扫描全表进行检索数据，区别在于，index类型是扫描索引树进行数据扫描，而All类型则是直接扫磁盘，所以相对index类型比较慢

  • possible_keys：指出MySQL能使用哪个索引在该表中找到行

  • key ：MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

  • key_len ：实际使用的索引长度（单位：字节）该字段显示为索引字段的大可能长度，并非实际使 用长度。

  • ref ：用于索引查找的值的来源，如果值未常量，则 ref 为 const

  • rows ：预计查询需要扫描的行数（在表或索引树中）

  • filtered ：查询条件所过滤的行记录数占比

  • Extra：额外的信息：

    • Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
    • Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
    • Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
    • Using where :表明使用where过滤
    • using join buffer:使用了连接缓存

  • impossible where:where子句的值总是false，不能用来获取任何元组

  • select tables optimized away：在没有group by子句的情况下，基于索引优化 Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  • distinct：优化distinct操作，在找到匹配的元组后即停止找同样值的动作

type值的效率高低

事务

A 原子性：==事务是一个原子操作，要么全部执行成功，要么全部回滚失败。==如果事务中的任何一部分操作失败，整个事务将被回滚到初始状态，保持数据的一致性。

C 一致性：==事务在执行前后，数据库必须保持一致性状态。==

I 隔离性：==事务之间应该相互隔离，使得每个事务感觉自己在独立地操作数据库==。隔离性确保了并发执行的事务不会相互干扰，避免了数据不一致性的问题。

三大问题

  • 脏读（Dirty Read）：脏读指的是一个事务读取了另一个事务尚未提交的数据。
  • 不可重复读（Non-repeatable Read）：不可重复读指的是在同一个事务中，多次读取同一数据时，得到的结果不一致。
  • 幻读（Phantom Read）：幻读指的是在同一个事务中，多次执行相同的查询时，得到的结果集不一致。

四大隔离级别

  • 读未提交（Read Uncommitted）：在该隔离级别下，一个事务可以读取到其他事务尚未提交的数据，即==脏读。还有不可重复读、幻读问题==。这是最低级别的隔离级别，它提供了==最高的并发性==，但也带来了最大的数据不一致性风险。
  • 读提交（Read Committed）：在该隔离级别下，一个事务只能读取到已经提交的数据。这意味着事务之间不会出现脏读问题，但==可能会出现不可重复读和幻读问题==。
  • 可重复读（Repeatable Read）：在该隔离级别下，一个事务在执行期间多次读取同一数据时，得到的结果保持一致。其他事务对该数据的修改只有在当前事务提交后才能被读取到。这可以避免脏读和不可重复读问题，但仍==可能出现幻读问题==。
  • 串行化（Serializable）：在该隔离级别下，事务串行执行，每个事务都相当于独占整个数据库。这是最高级别的隔离级别，可以避免脏读、不可重复读和幻读问题，但并发性最低，可能导致性能下降。

D 持久性：==一旦事务提交成功，其所做的修改将永久保存在数据库中==，即使在系统故障或崩溃后也不会丢失。

  • 重做日志（Redo Log）是一种==用于记录事务修改操作的机制==，它可以确保事务的==持久性和恢复能力==，并提高数据库的性能。
  • 撤销日志（Undo Log） 是一种==用于实现事务回滚和并发控制的机制==，它记录了事务执行期间所做的修改操作的反向操作。通过撤销日志，数据库管理系统可以在事务回滚或并发控制时撤销事务的修改，并==确保数据的一致性和隔离性==。
avatar
文章
阅读
粉丝