窥探MySQL索引与事务，java数据库连接池面试题

①原子性的实现

②持久性的实现

③隔离性实现

2.4、总结

---

一、MySQL索引

=============

1.1、概念

---

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，在 MySQL 中也被称为 Key。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

注意事项：有序性是因为一切二分法查找法都要求数据已经是排好顺序的。如果把索引看做 key（虽然 key 数据也是来自于表单中一行记录的某些字段值），那么 value 在 MyISAM 中就是记录的在存储文件中的地址，而在 InnoDB 中 value 直接就是对应的一行数据。

假设我们有一张数据表 workers(员工表)，该表有三个字段（列）,分别是name、age 和address。假设表works有上万行数据，现在需要从这个表中查找出所有名字是‘ZhangSan’的雇员信息，你会快速的写出SQL语句：

select name,age,address from workers where name=‘ZhangSan’

如果数据库还没有索引这个东西，一旦我们运行这个SQL查询，查找名字为ZhangSan的雇员的过程中，究竟会发生什么？数据库不得不在workes表中的每一行查找并确定雇员的名字（name）是否为‘ZhangSan’。

由于我们想要得到每一个名字为ZhangSan的雇员信息，在查询到第一个符合条件的行后，不能停止查询，因为可能还有其他符合条件的行，所以必须一行一行的查找直到最后一行——这就意味数据库不得不检查上万行数据才能找到所有名字为ZhangSan的雇员。这就是所谓的全表扫描，显然这种模式效率太慢。

使用索引的全部意义就是：通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（定义真特么拗口）。大白话意思是索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请牢记这一点：索引是一种数据结构。

1.2、作用

---

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。

索引所起的作用类似书籍目录，可用于快速定位、检索数据。

索引对于提高数据库的性能有很大的帮助。

1.3、使用场景

---

索引的本质实际上还是存储在磁盘上的数据结构，它可以有的存储结构：

• 二叉搜索树；

• 红黑树；

• Hash 表；

• B-Tree；

其中 MySQL 的 InnoDB 支持 B+Tree 以及 Hash 表，下面会具体分析各个数据结构的区别。

①哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效生。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 MySQL 中，只有 Memory 引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

下面来看一个例子。假设有如下表：

create table tes(

fname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

KEY USING HASH(fname)

) ENGINE = MEMORY;

然后再填入相关数据后，表格有如下数据：

假设索引使用假想的哈希函数 f()，它返回下面的值(都是示例数据，非真实数据) :

• f(‘a’) = 23

• f(‘b’) = 74

• f(‘p’) = 87

• f(‘v’) = 24

则哈希索引的数据结构如下：

槽（Slot）	值（Value）
23	指向第 1 行的指针
24	指向第 4 行的指针
74	指向第 2 行的指针
87	指向第 3 行的指针

注意每个槽的编号是顺序的，但是数据行不是。

下面使用 hash 索引字段进行查询，有：

select lname from tes where fname = ‘p’;

其分为如下的步骤：

• MSQL 先计算 ‘p’ 的哈希值；

• 根据哈希值进行寻找对应的地址指针，意味 hash 槽是有序的，因此查询效率很高；

• 读取对应指针上的数据是否为 ‘p’，是则返回

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。

• 哈希索引只支持等值比较查询，包括 =、IN()、<=>；不支持任何范围查询，例如 WHERE price>100。

• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

②TREE

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

数据量较大，且经常对这些列进行条件查询。

该数据库表的插入操作，及对这些列的修改操作频率较低。

索引会占用额外的磁盘空间。

在没有GUI工具的情况下，可以使用以下命令查看索引：

上述ad_article表中有两个索引，Key_name中有显示:

-PRIMARY主键索引，Seq_in_index索引序号为1，从1开始，Collation为“A”表示升序（或NULL无分类），对应字段是id

-idx_cid是自建索引，由cid、available、id三个字段组成，分别对应序号1,2,3

Index_type=BTREE这块内容很多人不懂其意思，其实通过GUI工具创建索引时也会有BTREE 的显示，先着重了解一下。

在计算机数据结构（不懂数据结构的自行充电）体系中，为了加速查找的速度，常见的数据结构有两种：

-Hash哈希结构，例如Java中的HashMap，这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(1);

-Tree 树 结构 ， 这种数据组织结构可以让查询/插入/修改/删除的平均时间复杂度都为O(log(n));

不管读还是写，Hash这种类型比Tree树这种类型都要更快一些，那为什么MySQL的开发者既使用Hash类型做为索引，又使用了BTREE呢？

确实用HASH索引更快，因为每次都只查询一条信息（重名的雇员姓名也才几条而已），但实际上业务对于SQL的应用场景是：

-orderby 需要排个序

-groupby 还要分个组

-还要比较大小 大于或小于等等

这种情况下如果继续用HASH类型做索引结构，其时间复杂度会从O(1)直接退化为O(n)，相当于全表扫描了，而Tree的特性保证了不管是哪种操作，依然能够保持O(log(n))的高效率。

那MySQL中的BTREE和TREE又有啥联系与区别呢？先来看看传统的二叉树：

二叉树是大家熟知的一种树，用它来做索引行不行，可以是可以，但有几个问题：

-如果索引数据很多，树的层次会很高（只有左右两个子节点），数据量大时查询还是会慢

-二叉树每个节点只存储一个记录，一次查询在树上找的时候花费磁盘IO次数较多

所以它并不适合直接拿来做索引存储，算法设计人员在二叉树的基础之上进行了变种，引入了

③BTREE

如上图可知BTREE有以下特点：

-不再是二叉搜索，而是N叉搜索，树的高度会降低，查询快

-叶子节点，非叶子节点，都可以存储数据，且可以存储多个数据

-通过中序遍历，可以访问树上所有节点

BTREE被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”，其设计逻辑是这样的：

-内存读写快，磁盘读写慢，而且慢很多

-磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载一些看起来是冗余的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘读写，提高效率（通常，一页数据是4K）

-局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO效能

④****B+TREE

早先的MySQL就是使用的BTREE做为索引的数据结构，随着时间推移，B树发生了较多的变种，其中最常见的就是B+TREE变种，现在MySQL用的就是这种，示意如下：

B+TREE改进点及优势所在：

-仍然是N叉树，层级小，非叶子节点不再存储数据，数据只存储在同一层的叶子节点上，B+树从根到每一个节点的路径长度一样，而B树不是这样

-叶子之间，增加了链表(图中红色箭头指向)，获取所有节点，不再需要中序遍历，使用链表的next节点就可以快速访问到

-范围查找方面，当定位min与max之后，中间叶子节点，就是结果集，不用中序回溯（范围查询在SQL中用得很多，这是B+树比B树最大的优势）

-叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储

-非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引

可以来初步计算一下：假设key、子树节点指针均占用4B，则B树一个节点占用4 + 4 = 8B，一页页面大小4KB，则N = 4 * 1024 / 8B = 512，一个512叉的B树，1000w的数据，深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24，相差了5倍以上。

假如一个节点大小是4KB，一个KEY有8字节，一页可以存4000/8=500个KEY，根据N叉树特点,就算一层500叉节点，则：

第一层树：1个节点，1*500KEY , 大小4K

第二层树：500节点 500*500=25万个KEY，500*4K=2M

第三层树：500 * 500节点 500*500*500=1.2亿KEY，500*500*4K=1G

如果没算错，1G空间，只用三层树结构，可以存1.2亿行数据的KEY。

所以B+TREE索引只用占用很少的内存空间，却大大提升了查询效率（不论是单个查询、范围查询还是有序性查询），并且还减少了磁盘读写，所以好的算法与数据结构是可以省钱的。

1.4、使用

---

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

①查看索引

show index from 表名;

案例：查看学生表已有的索引

show index from student;

②创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

案例：创建班级表中，name字段的索引

create index idx_classes_name on classes(name);

创建主键索引

主键索引的定义：InnoDB 中的表单数据本身就要创建为一棵 B+ 树，而这棵排序节点用到的索引就被称为主键索引。

只要有主键，那么主键索引根据的就是主键，我们在创建表和后续修改时都能够通过指定主键来确定主键索引，如下：

创建表同时设置主键

create table teacher(

id int(10) auto_increment,

name varchar(20),

age int(10),

phone varchar(11),

primary key (id));–主键设置

单独设置主键

alter table teacher add primary key (id);

创建唯一索引

create table teacher(

id int(10) auto_increment,

name varchar(20),

age int(10),

phone varchar(11),

primary key (id),

unique index idx_phone(phone(11)));–唯一索引

–单独建唯一索引

create unique index idx_phone on teacher(phone(11));

–删除唯一索引

drop index idexName on tableName;

–修改建唯一索引

alter table teacher add unique idx_phone (phone(11));

创建普通索引

create table teacher(

id int(10) auto_increment,

name varchar(20),

age int(10),

phone varchar(11),

primary key (id),

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

创建普通索引

create table teacher(

id int(10) auto_increment,

name varchar(20),

age int(10),

phone varchar(11),

primary key (id),

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-LvnVmlya-1710935019528)]
[外链图片转存中…(img-5YaZaFFW-1710935019529)]
[外链图片转存中…(img-Ob9dqdYp-1710935019530)]

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-OSiibVwp-1710935019530)]