04MySQL索引、事务

最新推荐文章于 2024-08-30 19:56:58 发布

她与断剑皆遗憾

最新推荐文章于 2024-08-30 19:56:58 发布

阅读量803

点赞数 13

文章标签：数据库网络

本文链接：https://blog.csdn.net/m0_70627741/article/details/140717546

版权

将当前会话的自动提交模式设置为手动提交模式

取消当前事务中的所有更改，并结束该事务，使得数据库恢复到事务开始前的状态。mysql> rollback;

将当前事务中的所有操作永久性地应用到数据库，并释放事务占用的所有资源。

提供一个隔离的执行环境，以确保事务的原子性、一致性、隔离性和持久性（ACID特性）。

索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

主要作用：

加快查询速度：索引可以大大加快数据的检查速度，特别是在数据量大时尤为明显
帮助排序：如果索引按照某种顺序存储数据，那么查询中的OPDER BY操作可以得到优化
加速数据的唯一性检查：如果在表的列上创建1了唯一索引，数据库可以检查插入的数据是否与已有的数据重复

创建索引的方式：

单列索引：对表的单个列创建索引，最常见的索引类型
组合索引：对表的多个列联合创建索引，适用于经常一起查询的列
唯一索引：确保索引列中的所有值都是唯一的，可以价绍唯一性约束的检查
全文索引：用于全文搜索的特殊索引类型，支持对文本内容进行快速搜索

索引的注意事项：

影响写入性能：索引不仅仅加快了查询速度，同时会稍微降低数据写入的速度，因为每次插入，更新或删除操作可能需要更新索引
适当使用：需要根据实际查询模式和数据访问方式来选择创建索引的列和类型，不宜过度索引
定期维护：长时间未使用或者数据分布发生变化时，索引可能会失效，需要定期进行优化和重建

事务

事务是指数据库管理系统中的一个逻辑单位，它由一个或多个数据库操作组成，这些操作要么全部执行，要么全部不执行，是数据库操作的最小工作单位

特性

原子性：事务中的所有操作要么全部提交，要么全部回滚，不允许部分提交部分回滚。原子性确保了事务的完整性，即事务的完整性，即事务要么完全执行，要么完全不执行，不存在中间状态

一致性：在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。这意味着事务执行后，数据库从一个一致性状态转移到另一个一致性状态。如果事务执行过程中发生了错误，数据库系统会撤销事务或者进行回滚，使数据恢复到事务开始之前的状态，以保证数据的一致性。

隔离性：多个事务并发执行时，每个事务的操作应该与其他事务隔离开来，即一个事务的执行不应该受到其他事务的干扰。隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据不一致或者读取脏数据的问题。

持久性：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使系统发生故障，也不会丢失提交的数据。持久性保证了事务的提交是永久性的，不会因为系统崩溃或者其他故障而导致数据丢失。

创建测试数据库和表

mysql> create database auth;
mysql> use auth
mysql> create table users (id int(10),user_name char(20),user_pass char(50));

普通索引

创建一个名为aaa的普通索引对于非主键、非唯一约束、非外键的字段、可以创建普通索引（字段名可以有多个，用逗号隔开，一个索引包含多个字段就叫组合索引）

mysql> create index aaa on users (user_name(20));

create index 索引名 on 表名(字段名);
create index 索引名 on 表名(字段名1,字段名2...);

查看索引

mysql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(10) DEFAULT NULL,
  `user_name` char(20) DEFAULT NULL,
  `user_pass` char(50) DEFAULT NULL,
  KEY `aaa` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表结构的方式添加索引语法

向数据库表 users 中的 user_name 列添加一个名为 bbb 的索引。

mysql> alter table users add index bbb (user_name(20));

查看索引的详细信息

mysql> show index from users\G

创建表时，同时创建索引

创建了一个包含三个列（id、name、sex）和一个索引（ccc，针对id列）的表t1。

mysql> create table t1 ( id int (10) , name char(20), sex char(2), index ccc (id));

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(10) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  KEY `ccc` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

唯一索引

唯一索引与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。

唯一索引确保列中的每个值都是唯一的。

mysql> create unique index ddd on t1(id);

创建一个具有特定列和约束的表

mysql> create table t2 ( id int (10) , name char(20), sex char(2), unique eee (id));
mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(10) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  UNIQUE KEY `eee` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表创建唯一索引

在 t1 表上创建了一个唯一约束 fff，该约束要求id 列的值在表中必须是唯一的。

mysql> alter table t1 add unique fff(id);

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

创建主索引

创建一个具有特定列和主索引约束的表 t3。

mysql> create table t3 (id int(10),name char(20),primary key (id));
mysql> show create table t3\G;