索引和事务

今天药忘吃喽~ 2024-02-19 09:07 40阅读 0赞

#### 文章目录 ####

*  一、索引
 *   *  1.1 概念
     *  1.2 作用
     *  1.3 使用场景
     *  1.4 使用
 *  二、事务
 *   *  2.1 为什么要使用事务
     *  2.2 事务的概念
     *  2.3 事务的使用
 *  三、内容重点总结

## 一、索引 ##

### 1.1 概念 ###

索引是一种特殊的文件，包含对数据表所有记录的引用指针。**在MySQL中，索引是基于一个或多个列的数据结构，是一种数据库对象，它们存储有序的键值对，其中键是列的值，而值是指向表中数据行的物理位置**。可以对表中的一列或多列创建索引，并指定索引类型，各类索引有各自的数据结构实现。

### 1.2 作用 ###

*  数据库中的表、数据、索引之间的关系类似于书架上的图书、书籍内容、书籍目录的关系。
 *  索引可用于快速定位，检索数据，**加快查询的速度**，避免对表进行遍历，是对查询操作的优化手段  
    ![在这里插入图片描述][bc6ddad6920343fe84a40aeb5f3873f1.png]  
    索引能提高查询的速度，但也会有一定的代价
    
    1.  占用更多的空间，生成索引，需要一系列的数据结构和额外的数据，来存储到硬盘空间中
    2.  可能会降低插入、更新和删除操作的速度

### 1.3 使用场景 ###

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

1.  数据量较大，经常对列进行条件查询
2.  该表的插入和对这些列的修改操作频率较低
3.  索引会占用额外的磁盘空间  
    满足上述条件，考虑对表中这些字段创建索引，来提高查询效率，但如果不是条件查询列，或者经常使用插入、修改操作，或者磁盘空间不足时，不考虑创建索引

### 1.4 使用 ###

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

*  查看索引

> show index from 表名;

-- 查看学生表已有的索引
    show index from student;

*  创建索引  
    要创建索引的列是非主键、非唯一约束、非外键的字段

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

-- 创建课程表中name 字段的索引
    create index idx_course_name on classes(name);

*  删除索引  
    自动创建的索引不能删除

> drop index 索引名 on 表名;

-- 删除课程表中name字段的索引
    drop index idx_course_name on course;

拓展：  
![在这里插入图片描述][14f0bc7780484db0944f959679c25ae7.png]  
索引保存的数据结构主要为B+树（N叉搜索树），及hash的方式。  
![在这里插入图片描述][ae3b0cfb86c14112849b8260029beb54.png]  
在查询的时候，直接从根节点出发，判断当前要查的数据在节点上的那个区间中，决定下一步向哪走，进行添加和删除元素可能涉及节点的拆分和合并  
![在这里插入图片描述][ea2ac4c5ed204dc5a318a43b7a103929.png]  
![在这里插入图片描述][ec9930c458244590b0a9b00b7c2e8736.png]  
![在这里插入图片描述][523efa28abc349348220fc312196f8b6.png]  
![在这里插入图片描述][6d7d6381cdae4b66baba3bd3eb1e1690.png]

## 二、事务 ##

### 2.1 为什么要使用事务 ###

-- 创建一个银行账户的表
    create table accout(
    	id int primary key auto_increment,
    	name varchar(20) comment '账户名称',
    	money decimal(11,2) comment '金额'
    );
    -- 插入数据
    insert into accout(name, money) values('张三',10000),('李四',10000);

现在要实现张三向李四转账1000元

-- 张三账户减少1000
    update accout set money=money-1000 where name = '张三';
    -- 李四账户增加1000
    update accout set money=money+1000 where name = '李四';

如果在执行第一个update 语句时，程序或者数据库崩溃了，或者是机器断电了，张三帐户会减少1000，但李四账户没有增加1000金额，此时就需要**使用事务来控制，保证两条update语句要么都执行成功，要么都执行失败**。

### 2.2 事务的概念 ###

事务是指逻辑上的一组操作，组成这个操作的单元，要么全部成功，要么全部失。在不同环境中，都可以有事务，而对应在数据库中的，就是数据库事务。  
以2.1 例子来说，事务，就是将这要实现的业务操作进行打包成一个整体，这个整体中的操作要么都成功，要么都失败（需要返回事务开始前的状态，即回滚），保证数据的一致性和完整性

### 2.3 事务的使用 ###

1.  开启事务：start transaction;
2.  执行多条SQL语句
3.  回滚或提交：rollback / commit

开启事务后，单独执行的每个 sql 都是独立的，这些sql 之间没有原子性‘，提交事务是使用 commit 将事务的操作持久化保存到数据库中。  
当出现错误或异常情况时，可以使用 rollback 语句撤销事务中的所有更改。也就是说当**执行 rollback 时，MySQL会撤销所有未提交的事务并回滚到事务开始之前的状态，回滚只适用于尚未提交的事务，一旦事务被提交，就无法再进行回滚操作**。

拓展：  
![在这里插入图片描述][3b36c8d1ab57405f9f23c41c8bf9ad23.png]  
![在这里插入图片描述][91e43d798cf44c0b84e58b2d631ddb36.png]  
![在这里插入图片描述][7369ffa4de354eed8e25b72e1d9aef97.png]  
![在这里插入图片描述][99bf4b3aea324c4b8bcc85dd1e6387c4.png]

## 三、内容重点总结 ##

*  索引：  
    索引是基于一个或多个列的数据结构，是一种数据库对象，它们存储有序的键值对，其中键是列的值，而值是指向表中数据行的物理位置。
    
    1.  插入和删除数据频繁的表，不适用索引
    2.  对某列修改频率高的，该列不适合用索引
    3.  某列或某些列条件查询频率高的，可以对这些列创建索引

> 查看索引 show index on 表名

> 创建索引 create index 索引名 on 表名(列名)

> 删除索引 drop index 索引名 on 表名

*  事务：  
    事务是指逻辑上的一组操作，组成这个操作的单元，要么全部成功，要么全部失败

> 开启事务 start transaction

> 执行多条SQL语句

> 提交或回滚事务 commit / rollback

[bc6ddad6920343fe84a40aeb5f3873f1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/3090b8431777442392ce587b40de0391.png
[14f0bc7780484db0944f959679c25ae7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/b7dc2d2fe40546849b0de12ab5b981b2.png
[ae3b0cfb86c14112849b8260029beb54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/81b0977eb1344e43973df021c8faa6ed.png
[ea2ac4c5ed204dc5a318a43b7a103929.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/ec205b8123ef40dd87db80e8b5acac9e.png
[ec9930c458244590b0a9b00b7c2e8736.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/e5c74703aea5457bb5fdef397a5ec713.png
[523efa28abc349348220fc312196f8b6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/a793b68c04544d948f658499431bf296.png
[6d7d6381cdae4b66baba3bd3eb1e1690.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/0838edb2dd99474a82781ac4a50564cb.png
[3b36c8d1ab57405f9f23c41c8bf9ad23.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/606a5000ca30469f99205db2794a723d.png
[91e43d798cf44c0b84e58b2d631ddb36.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/eb60131b081544d2a32cdb29c305a3d8.png
[7369ffa4de354eed8e25b72e1d9aef97.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/239acf526b284fdbb14bad36c891d1c2.png
[99bf4b3aea324c4b8bcc85dd1e6387c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/19/19df350ab70c425d81dd678353287926.png