MySQL数据库索引和事务图文详解

蓝宇飞龙 發表於 2026-1-5 08:22:18

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">索引</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">一. 索引的重要性</a></li><li><a href="#_lab2_0_1">二. 理解索引</a></li><li><a href="#_lab2_0_2">三. 索引操作</a></li><ul class="third_class_ul"><li><a href="#_label3_0_2_0">1. 创建索引</a></li><li><a href="#_label3_0_2_1">2. 查询索引</a></li><li><a href="#_label3_0_2_2">3. 删除索引</a></li></ul></ul><li><a href="#_label1">事务</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">一. 什么是事务</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_1_4">二. 事务提交方式</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_1_5">三. 事务操作方式</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_1_6">四. 事务隔离级别</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label2">总结</a></li><ul class="second_class_ul"></ul></ul></div><a name="_label0"></a><h2>索引</h2>
<a name="_lab2_0_0"></a><h3>一. 索引的重要性</h3>
MySQL 的索引就是数据结构，能够对多列的值进行排序来更快的进行查询数据。索引就像是一本书的目录，通过目录我们可以快速定位到数据所在的位置。无需扫描整张表来找数据。
虽然查询速度变快了，但是与之而来的是读写操作增加了更多的IO，但是为了提高检索速度是值得的。
<a name="_lab2_0_1"></a><h3>二. 理解索引</h3>
要想知道为何添加索引之后可以更快的查询数据，我们就得理解索引。
首先明确，MySQL的操作都是在内存当中进行的，它的任务就是对数据进行CURD操作。那么数据是保存在磁盘的。MySQL与磁盘进行IO交互时通过一个个page进行的。page是内存与磁盘交互的最小单位。page的目的在于一次性多加载，以达到减少IO的目的。相比于要多少加载多少可以有效减少IO次数。
那MySQL是怎么提升自己的查询速度的呢？
通过索引数据结构。MySQL采用B+树的结构，类似与多叉树，根结点存储page目录用于定位所要信息的page处，叶子结点存储数据信息。这些数据是通过索引进行排序的，都是有序的。
整个查找的办法我们可以类比于找数字。例如我现在要找一个数字52，首先我通过树结构先找到50-60范围的起始点50，然后从50向60进行遍历找到52。
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175293.png" />
根结点存储page页的指针，page页存储着实际存储数据的page指针，接着就是存储数据的page。
<a name="_lab2_0_2"></a><h3>三. 索引操作</h3>
<a name="_label3_0_2_0"></a><h4>1. 创建索引</h4>
创建索引有三种方式，第一种直接跟在列后面，第二种在最后面指定列，第三种创建完表后对指定列添加索引
主键索引案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175183.png" />
唯一键索引案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175293.png" />
普通索引案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175242.png" />
<a name="_label3_0_2_1"></a><h4>2. 查询索引</h4>
<blockquote>show  【索引】from【表】</blockquote>
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175215.png" />
<a name="_label3_0_2_2"></a><h4>3. 删除索引</h4>
删除主键索引
<blockquote>alter table【表】drop  primary  key；</blockquote>
删除其他索引
<blockquote>alter  table  【表】drop  【索引名】</blockquote>
<a name="_label1"></a><h2>事务</h2>
<a name="_lab2_1_3"></a><h3>一. 什么是事务</h3>
事务是数据库操作的逻辑执行单元，这些操作要么全部实现，要么全部失败回滚。
为的就是保证数据的一致性和完整性。
事务必须满足四个特性（ACID）
<blockquote>原子性
事务是一个整体，要么全部成功要么全部失败回滚，例如转账时A账户扣钱和B账户加钱必须同时成功。
一致性
执行前后，数据库状态要保持一致，如转账后两个账户总金额不变。
隔离性
多个事务并发执行，彼此间的操作不能互相干扰
持久性
事务一旦提交，结果就是永久性的，即使系统崩溃数据也不会丢失。</blockquote>
<a name="_lab2_1_4"></a><h3>二. 事务提交方式</h3>
事务提交就是将事务执行的操作永久保存到数据库并且释放锁，主要有显示提交和隐式提交
<blockquote>显示提交
显示提交是使用了 commit 进行手动提交事务
隐式提交
隐式提交涉及到MySQL的autocommit模式，当autocommit = on 时代表隐式模式开启，每条SQL语句都会被自动提交。当 autocommit = off 时就默认为显示提交</blockquote>
我们可以查看autocommit并且进行修改
<div class="jb51code"><pre class="brush:sql;">show variables like ‘autocommmit'
set autocommit = （1开 / 0关）</pre></div>
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175277.png" />
<a name="_lab2_1_5"></a><h3>三. 事务操作方式</h3>
正常处理事务
通过begin开始事务，commit结束事务，savepoint【名字】可以暂存点，rollback【名字】可以进行回档。
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175293.png" />
当我们开启两台终端时，隔离级别为未读提交，当我们在终端a插入数据但为commit，此时在终端b访问表就无法查看到数据
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175248.png" />
当我们对事务进行commit后，此时中断主机A，在主机B上查询表可以发现依然能看到commit前插入的数据。说明commit将数据永久保存在数据库中
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175221.png" />
关闭autocommit，当我们向主机A表中插入数据时，主机B也会同步更新表。当主机A在未commit下突然崩溃，表会自动进行回滚
<a name="_lab2_1_6"></a><h3>四. 事务隔离级别</h3>
隔离性就是多个操作之间互不干扰的状态。
事务的隔离等级分为四种：读未提交，读提交，可重复读，串行化
<blockquote>查看与设置隔离性
查看全局隔离级别
SELECT @@global.transaction_isolation;
查看当前会话全局隔离级别
SELECT @@transaction_isolation;
当前会话只影响当前数据库的隔离级别，全局会话会设置默认的新链接
案例:
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175280.png" /></blockquote>
<blockquote>设置当前会话隔离级别
set  【session/global】transaction  isolation  level  【read  uncommitted  |  read  committed  |  repeatable  read  |  serializable】
设置为全局或者当前会话，设置等级为四个
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175280.png" /></blockquote>
<blockquote>读未提交
当我们使用读未提交时，主机A和主机B同时begin开始事务，主机A更新数据库一条数据，但是未commit，此时主机B访问表可以实时查看到数据被更改
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175259.png" />
一个事务在执行中，读到另一个执行中事务的更新但是未commit数据，这种现象叫做脏读</blockquote>
<blockquote>读提交
我们让主机A和主机B同时开启一项事务，主机A对列更新但未保存，此时主机B访问该列拿到的是久值。主机B倘若对就值进行处理，此时主机A对该列进行commit，就会造成读取到了不同的值
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175246.png" />
这种现象就是不可重读读</blockquote>
<blockquote>可重读读
同时开启主机A和主机B的事务，主机A更改某列数据，此时主机B访问表发现表仍然为原先旧数据；主机A进行commit保存数据，主机B再次访问，发现仍然为原先数据；主机B进行commit保存数据，此时再进行访问表，表才进行了更新。
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175236.png" />
倘若我们主机A更改列后不进行commit，此时主机B对该列进行修改，那么此时就会卡住，直到主机Acommit后主机B更改才能成功。
这也就导致我们插入数据不断查询的过程中值在不断地变化，如同产生幻觉，这就是幻读</blockquote>
<blockquote>串行化
同时开启主机A和主机B，当主机A对表进行修改时，此时主机B访问该表就会造成阻塞，直到主机A进行commit后，主机B才会显示
案例：
<img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010508175218.png" />
串行化虽然说隔离级别最严格，但也伴随着效率是其中最低下的那一种方式</blockquote>
隔离级别是一致性与性能的权衡，级别越高，一致性越好，但性能也会越差。
<a name="_label2"></a><h2>总结</h2>

頁: [1]

琼殿技术论坛's Archiver

MySQL数据库索引和事务图文详解