MySQL 篇-快速了解事务、索引

🔥博客主页： 【小扳_-CSDN博客】
❤感谢大家点赞👍收藏⭐评论✍  

文章目录

        1.0 事务概述

        1.1 事务四大特性(ACID)

        2.0 索引概述

        2.1 关于 “索引一定要创建在主键上？” 的问题

        2.2 索引操作语法

        2.3 索引结构

---

        1.0 事务概述

        事务是作为单个逻辑工作单元执行的一组数据库操作，要么全部成功执行，要么全部失败回滚，保证数据的完整性。

        默认 MySQL 的事务是自动提交的，也就是说，当执行一条 DML 语句，MySQL 会立即隐式的提交事务。

        用简单通俗的话来说，将 SQL 每一条语句都 “打包” 起来一起执行，如果某一条语句出现错误，则进行回滚操作，最终可以使得数据回复到原来的样子；如果 “打包” 在一起的语句都没有出现错误，则进行提交操作，数据就会进行相应的变化并保持持久性。

语法结构：

-- 开启事务，接下来的 SQL 语句都会打包起来
start transaction;
 
-- 如果这些语句都没有出现错误的话，可以提交事务
commit;
 
-- 如果这些语句，哪怕有一条语句出现错误，则操作回滚事务
rollback;

        1.1 事务四大特性(ACID)

        1）原子性(A)：事务是不可分割的最小单元，要么全部成功，要么全部失败。

        2）一致性(C)：事务完成时，必须使所有数据都保持一致状态。

        3）隔离性(I)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

        4）持久性(D)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

        2.0 索引概述

        在 MySQL 中，索引是一种数据结构，用于加快数据库表中数据的检索速度。通过在表的列上创建索引，可以帮助数据库系统快速定位到符合检索条件的记录，从而提高查询效率。

索引的优点：

        提高数据查询的效率，降低数据库的 IO 成本。通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 消耗。

索引的缺点：
        索引会占用存储空间，索引大大提高了查询效率，同时却也降低了 insert, update, delete 的效率。

        总的来说，优点大于缺点，实际中大多数的操作都是查询操作，至于插入、修改、删除的操作占小部分。

        2.1 关于 “索引一定要创建在主键上？” 的问题

        不，索引并不一定要创建在主键上。虽然主键通常会自动创建一个唯一索引，但在实际数据库设计中，还可以在其他列上创建索引以提高查询性能。
        在设计索引时，需要根据实际的查询需求和数据访问模式来选择合适的列进行索引。一些常见的情况包括：

        主键索引：主键用于唯一标识表中的每条记录，通常会自动创建一个主键索引。主键索引在查询单条记录时非常高效。

        外键索引：外键用于建立表与表之间的关联关系，可以在外键列上创建索引以加快关联查询的速度。

        经常用于查询的列：如果某个列经常用于查询条件或排序操作，可以考虑在该列上创建索引，以提高查询效率。

        经常用于连接的列：如果某个列经常用于连接操作（如 JOIN 语句），可以在连接列上创建索引，加速连接操作。

        补充：在 MySQL 中，默认加上主键约束、UNIQUE 约束都会自动创建索引。

        2.2 索引操作语法

        1）创建索引

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

        2）查看索引

show index from 表名;

        3）删除索引

drop index 索引名 on 表名;

        4）举个例子，在创建图书表，表中的字段有 id, name, author, update_time ，为了通过查询书名来快速得到书的信息，就可以在书名这个字段创建索引。

代码如下：

create table books(
    id tinyint unique primary key comment '序号',
    name varchar(10) not null comment '书名',
    author varchar(10) not null comment '作者',
    update_time datetime comment '更新时间'
)comment '图书';
 
-- 添加数据
insert into books
values (1, 'Java', '小童', now()),
       (2, 'C++', '小鱼', now()),
       (3, 'Python', '小吴', now());

创建索引：

-- 创建索引在书名上，可以通过查找书名，快速找到
create index index_books_name on books(name);

查看表中的索引：

-- 查看表中的所有索引
show index from books;

        有两个字段创建了索引，一个主键 (id) 是默认创建了索引，另一个 (name) 是自己手动创建的索引。

删除表中的索引：

-- 删除表中的索引
drop index index_books_name on books;

再来查看表中的索引情况，

        可以看到 name 这个字段上的索引已经被删除了。

       

        2.3 索引结构

        在上面说到，索引就是一个数据结构，创建索引，就是在将该列中的数据按照规定的数据结构进行重新存储整理起来，从而在查询过程中，可以快速的查询相对应的数据。所以对于没有创建索引，查询指定的数据时，是通过全盘扫描的方式进行。

        那么 MySQL 中，索引是基于 B 树(B-tree) 或者 B+ 树(B+tree) 结构实现的，这些树结构是一种高效的数据结构，可以加速数据库表中的检索速度。

B 树 (B-tree)：
        B 树是一种平衡的多路搜索树，用于在数据库中存储和管理索引数据。B 树的特点是每个节点可以存储多个关键字，并且具有平衡性，保证查询的时间复杂度为 O(logN) 。在 MySQL 中，普通索引、唯一索引和主键索引通常都是基于 B 树结构实现的。

B+ 树 (B+tree)：
        B+ 树是在 B 树的基础上做了一些优化，更适合作为数据库索引的数据结构。B+ 树的非叶子节点只存储索引字段的值，而实际数据都存储在叶子节点上，这样可以减少磁盘 I/O 操作。B+ 树的叶子节点之间通过指针连接，形成一个有序链表，方便范围查询和排序操作。在 MySQL 中，InnoDB 存储引擎的聚簇索引(Clustered Index)就是基于 B+ 树结构实现的。

对于 B+ 树来说：1）每一个节点可以存储多个 key (有 n 个 key ，就有 n 个指针)。2）所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。3）叶子节点形成了一个双向链表，便于数据的排序及区间范围查询。