当前位置：首页 > news >正文

MySQL索引和事务

news 2025/4/16 5:33:52

MySQL索引和事务

1.索引
- 1.1概念
- 1.2作用
- 1.3使用场景
- 1.4使用
- - 1.4.1查看索引
  - 1.4.2创建索引
  - 1.4.3删除索引
2.事务
- 2.1使用
- - 2.1.1开启事务
  - 2.1.2执行多条SQL语句
  - 2.1.3回滚或提交
- 2.2事务的特性
- - 2.2.1回滚是怎么做到的
  - 2.2.2原子性
  - 2.2.3一致性
  - 2.2.4持久性
  - 2.2.5隔离性
  - - 2.2.5.1脏读
    - 2.2.5.2不可重复读
    - 2.2.5.3幻读
- 2.3隔离级别（四种）
3.索引保存的数据结构（面试常考）
- 3.1概念
- 3.1B树
- 3.2B+树

1.索引

索引属于是针对查询操作引入的优化手段，可以通过索引加快查询的速度避免针对表进行遍历

1.1概念

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

1.2作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系
索引引起的作用类似于书籍目录，可用于快速定位、检索数据
索引对于提高数据库的性能有很大帮助

引入索引的代价：

占用更多的空间，生成索引是需要一系列的数据结构以及一系列的额外的数据来存储到硬盘中的
可能会降低插入修改删除的速度

1.3使用场景

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

数据量较大，且经常对这些列进行条件查询
该数据库表的插入操作以及对这些列的修改操作频率较低
索引会占用额外的磁盘空间

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率，反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引

1.4使用

创建主键约束、唯一约束、外键约束时，会自动创建对应列的索引

1.4.1查看索引

语法：

show index from 表名

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

mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | sn         |            1 | sn          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | classes_id |            1 | classes_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

1.4.2创建索引

这是一个危险操作，创建的时候需要对现有的数据进行大规模的重新整理，如果表很大，创建索引，很容易把数据库服务器卡住，一般来说创建索引都是在创建表的时候就规划好的，一旦表有很多数据了再修改索引，就要慎重
语法：

create index 索引名 on 表名(列名)

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

mysql> create index idx_clases_name on classes(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看创建之后的结果
mysql> show index from classes;;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| classes |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| classes |          1 | idx_clases_name |            1 | name        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.4.3删除索引

手动创建的可以删除，自动创建的（unique，主键，外键）不能删除

语法：

drop index 索引名 on 表名

例：删除班级表中name字段的索引

mysql> drop index idx_clases_name on classes;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 查看删除后的结果
mysql> show index from classes;;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| classes |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

说明：一个索引是针对一个列来指定的，只有针对这一列进行条件查询时，查询速度才能被索引优化
比如此处对id列创建索引，使用id进行条件查询时，速度很快，若访问name列则仍需要遍历表

2.事务

开发中经常设计一些场景，需要一气呵成的完成一些操作，比如转账。事务可以把多个SQL打包成一个整体，可以保证这些SQL要么执行，要么就看起来像一个都不执行一样，关键操作就是翻新，此时在数据库中，称为回滚

事务指的是逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败，在不同的环境中，都可以有事务，对应在数据库中，就是数据库事务

2.1使用

2.1.1开启事务

mysql> start transaction;

2.1.2执行多条SQL语句

**事务把多个SQL打包到一起，作为一个整体来执行，这样的特点称为“原子性”

mysql> update account set money = money-2000 where name = '小明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update account set money = money+2000 where name = '李华';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.1.3回滚或提交

commit：提交，证明事务结束了
roolback：主动触发回滚，一般要搭配条件判断逻辑来使用的，可搭配其他编程语言

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

2.2事务的特性

2.2.1回滚是怎么做到的

通过日志（打印出来的内容，在文件里，即使主机掉电也不影响）的方式记录事务中的关键操作，记录就是回滚的操作

2.2.2原子性

通过回滚的方式保证这一系列操作都能执行正确或恢复如初

2.2.3一致性

事务作出的修改，都是在硬盘上持久保存的，重启服务器数据仍然存在，事务执行的修改仍然有效

2.2.4持久性

事务做出的修改，都是在硬盘上持久保存的，重启服务器数据仍然存在，事务指定的修仍然有效

2.2.5隔离性

数据库并发执行多个事务时涉及到的问题，并发成都越高，整体效率越高，但是提高了并发程度可能会存在一些问题，导致数据出现一些“错误”的情况，隔离级别就是在“数据正确”和“效率”之间的权衡，往往提高效率，正确性降低、提高正确性，效率降低

2.2.5.1脏读

一个事物A在写数据的过程中，另一个数据B读取了同一个数据，接下来事务A又改了数据，导致B之前读的数据是一个无效的数据、过时的数据，也称为脏读

解决方法：核心思路是针对写操作加锁，写加锁后并发程度降低，隔离性提高，效率降低，准确性提高

2.2.5.2不可重复读

并发执行事务中，如果事务A在内部多次读取同一个数据时，出现不同情况，这种就是不可重复读、事务A在两次读之间，有一个事务B修改了数据并提交了事务。

解决方法：需要给读操作加锁，并发程度降低，隔离性提高，效率降低，准确性提高

2.2.5.3幻读

一个事务A执行过程中，两次的读取操作，数据内容没变，但是结果变了，这种称为幻读，具体这个情况是不是问题，需要具体场景具体分析

解决方法：引入串行化的方式，解决幻读，保持绝对的串行执行事务，此时完全没有并发了（从根本上解决了并发中涉及的各个问题）此时并发程度最低（没有并发）隔离性最高，效率最低，数据最准确

2.3隔离级别（四种）

对应上述三种问题
根据不同场景，修改配置文件，设置不同隔离级别，隔离级别默认为repeatable read

包含上述全部问题，准确性最低：
read uncommitted（读未提及）并发程度最高，速度最快，隔离性最低，准确性最低
脏读解决
read committed（读已提及）引入了写加锁，只能读写完之后提交的版本，并发程度降低了，速度降低了，隔离性提高了，准确性提高了
不可重复读解决
repeatable read（可重复读）引入了写/读加锁，写时不能读，读时不能写，并发程度进一步降低，速度降低，隔离性提高，准确性提高
幻读解决
serializable（串行化）严格按照串行的方式，一个一个执行事务，没有并发，速度最低，隔离性最高，准确性最高

3.索引保存的数据结构（面试常考）

3.1概念

索引保存的数据结构主要为B+树，以及hash的方式，在这里我们主要介绍B+树
哈希表能进行精准匹配，无法范围查询和模糊匹配
红黑树可以精准匹配也可以范围查询和模糊匹配
红黑树也是二叉树，每一个节点最多两个子树，树的分叉少，此时表示同样数量的结果集合，树的高度会更高，一旦树的高度更高，查询时io访问次数就会更多

数据库引入的索引是一个改进的树形结构B+树，也称为n叉搜索树

3.1B树

为了了解B+树，我们先了解B树
在这里插入图片描述

B树的每一个节点的度是不确定的，一个节点保存N个key，就划分出N+1个空间，每个节点是在一个硬盘的区域中，一次读硬盘就读出了整个节点（多个key）再进行几次比较（读一次硬盘，相当于1万次比较）

3.2B+树

也称为N叉搜索树，一个节点存在N个key，划分成N个空间
B+树相对于B树、哈希、红黑树的优点：树的高度有限，降低io次数，非常擅长范围查询，所有查询最终都要到叶子结点，不会出现这次快，下次慢的情况，数据库是按行组织数据的，创建索引的时候是针对这一列进行创建，这一行数据（id）内容较多，叶子结点非常占用空间，非叶子节点占不了多少空间（缓存到内存中）整体查询就可以在内存中进行，减少io访问次数
在这里插入图片描述
每个节点上的N个key，最后一个就相当于当前子树的最大值
父节点上的每个key都会以最大值的身份在子节点的对应区间中存在（key可能重复出现）使叶子结点这个层包含了数据全集，B+树会使用单链表的结构把叶子结点串起来，此时可以非常方便的从数据集合中按照范围取出一个子集