MySQL：索引、事务
目录
索引
是一个经过排序的列表，对数据进行快速的查询
如果你的表没有索引，查找数据时会遍历整个表，如果有索引，会先根据索引找到对应的项，匹配到对应项后，再根据该项映射其他信息
其实索引就是从一个表中挑选一列作为搜索关键字
索引名不能重复
在创建一个表的时候，指定主键其实就是创建了一个索引（主键索引）
优点
- 设置了合适的索引之后，数据库利用各种快速定位技术，提高查询速度。
- 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
- 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
- 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
- 可以加快表与表之间的连接。
- 在使用分组和排序时，可大大减少分组和排序的时间。
缺点
- 索引需要占用额外的磁盘空间。
- 对于MyISAM引擎而言，索引文件和数据文件是分离的（分开存放），索引文件用于保存数据记录的地址。
- 而InnoDB引擎的表数据文件本身就是索引文件。
- 在插入和修改数据时要花费更多的时间，因为索引也要随之变动（重建索引）。
创建索引的原则依据
不是所有的表和列都适合创建索引
适合创建索引的场景
- 如果一个表拥有主键或外键时必须有索引
- 经常用于跨表查询的，需要有索引（内连接、左连接、右连接）
- 经常用where指定条件进行查询的，适合创建索引
- 建立索引的列尽量是小字段（列名的长度短）
不适合创建索引的场景
- 数据很少（300行以下）的表没有必要创建索引
- 唯一性比较差的列或表不适合创建索引
- 更新太频繁的列不适合创建索引
索引类型
创建测试数据库、表
下面一一对每个索引类型进行演示
首先创建出测试使用的数据库和表
mysql> create database auth;
mysql> use auth
mysql> create table users (id int(10), user_name varchar(20), user_pass varchar(50));
普通索引
MySQL创建索引时默认创建的索引类型，没有唯一性之类的限制
不指定索引类型，按照指定索引列，自上而下逐个搜索，直到找到对应的值
使用create创建索引
使用create命令指定创建的对象类型为index（索引），该索引的名称为aaa，基于users表建立，使用该表中的user_name列作为索引，索引长度为20
索引长度最好和该列的长度一致，索引长度的最大值要根据存储引擎
语法： create index 索引名 on 表名(列名(长度));
mysql> create index aaa on users(user_name(20));使用show命令查看users表的信息
其中KEY的行就是索引信息，普通索引在这里只显示KEY `索引名`
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(10) DEFAULT NULL,
`user_name` varchar(20) DEFAULT NULL,
`user_pass` varchar(50) DEFAULT NULL,
KEY `aaa` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)使用alter创建索引
使用alter既可以修改表的结构（数据类型、长度等等），也可以添加索引
mysql> alter table users add index bbb (user_name(20));
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(10) DEFAULT NULL,
`user_name` varchar(20) DEFAULT NULL,
`user_pass` varchar(50) DEFAULT NULL,
KEY `aaa` (`user_name`),
KEY `bbb` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)创建表的同时添加索引
在新创建一个表的同时添加索引，指定索引列为"id"，因为id是int类型所以索引不用指定长度
mysql> create table t1 (
-> id int(10),
-> name char(20),
-> sex char(2),
-> index ccc (id)
-> );查询索引
使用show命令查询刚刚创建出的t1表的信息，可以看到索引名为ccc，类型为B树索引
mysql> show index from t1\G;
*************************** 1. row ***************************
Table: t1
Non_unique: 1
Key_name: ccc
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:使用show命令查询users表中的所有索引
可以看到列出的信息有一行： Index_type: BTREE
这代表MySQL的普通索引，也就是默认创建出的索引类型就是Oracle的B树索引
mysql> show index from users\G;
*************************** 1. row ***************************
Table: users
Non_unique: 1
Key_name: aaa
Seq_in_index: 1
Column_name: user_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users
Non_unique: 1
Key_name: bbb
Seq_in_index: 1
Column_name: user_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:B树索引
oracle的默认索引类型
查询时首先把表一分为二，先判断要查询的数据是在左表还是在右表，假如判断在左表中查询，再次将左表一分为二，再次判断是在左表还是右表中查询，直到找到对应数据
类似于二分查找
唯一索引
可以在一个表里创建多个唯一索引（可以有多个），适用于作为索引的列具有唯一性
除了创建MySQL默认的索引，其他的索引在创建时需要指定类型，这里指定为unique就是唯一索引的类型
使用create创建.
mysql> create unique index ddd on t1(id);使用alter创建
- unique：索引类型
- fff：索引名
mysql> alter table t1 add unique fff(id);在创建表的同时创建索引
在创建表的同时创建索引也需要指定类型，这里直接使用unique / unique index关键字来创建唯一索引
mysql> create table t2 ( id int(10), name char(20), sex char(2), unique eee (id));主键索引
一个表只能有一个主键
表的约束
-
关系型数据库四大约束
-
- 实体完整性约束（主键）
- 域完整性约束（数据类型）
- 引用完整性约束（主外键）
- 用户自定义完整性约束（字段内容固定长度）
创建主键索引
主键索引其实就是当你在创建一个表的时候指定了主键，那么此时主键索引就被创建了出来
mysql> create table t3 (id int(10), name char(20), primary key (id));如果是主键索引的话，在使用show create table时，会显示PRIMARY KEY (`id`)，表示主键索引
mysql> show create table t3\G;
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
组合索引（最左索引）
可以把多个列组合起来作为一个索引
在查询的时候，查询条件的顺序必须和索引中的信息顺序要一致
在创建表的同时创建组合索引
mysql> create table t4 (id int(10), name char(20), age int(2), index ggg(id, name, age));使用索引查询
其他的索引在使用的时候，会检查你作为条件的列名有没有作为索引被创建出来，如果有，那就根据索引类型来查询
而组合索引在使用的时候，需要把查询条件的列名和创建组合索引时指定的列名按顺序排列，才能使用组合索引
mysql> select * from t4 where id = '10' and name = 'tom' and age = '18';
+------+------+------+
| id | name | age |
+------+------+------+
| 10 | tom | 18 |
+------+------+------+全文索引
适用于一个文本，效率低
适用场景：将备注设为索引列
使用create创建
指定的索引列的类型必须是文本类型，才能创建全文索引
mysql> create fulltext index hhh on t4 (name);在创建表的同时创建全文索引
mysql> create table t6 (id int(10), name char(20), info varchar(255), fulltext index iii(info));使用alter创建
mysql> alter table t6 add fulltext jjj(info);查看t6表结构
使用show create table t6\G;或show index from t6\G;都可以查看指定表中的索引
mysql> show create table t6\G;
*************************** 1. row ***************************
Table: t6
Create Table: CREATE TABLE `t6` (
`id` int(10) DEFAULT NULL,
`name` char(20) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `hhh` (`info`),
FULLTEXT KEY `iii` (`info`),
FULLTEXT KEY `jjj` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8删除索引
使用drop语句
指定删除类型为index，跟上索引名称 on 该索引所在的表名;
drop 类型 索引名 on 表名;
使用drop删除索引
mysql> drop index ggg on t4;使用alter删除索引
mysql> alter table t1 drop index ccc;索引的维护
- 当表中的数据被改动，索引就可能会对应不上，就需要重建索引。
- 索引用过一段时间后，会产生碎片，这些碎片会消耗索引空间（索引碎片）
在MySQL可以直接删除该索引来清除索引碎片，删除一个索引后，对应的索引碎片也会被删除。
删除后再创建索引（重建索引）。
事务
事务（transaction）是多个SQL语句的集合，这些语句要么全部执行，要么都不执行
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
特点
原子性
如果在执行事务内的SQL语句时，假如执行到一半出现错误了无法执行，已执行的SQL语句就全部回滚，未执行的就不再执行
- 事务是一个完整的操作，事务的各元素是不可分的
- 事务中的所有元素必须作为一个整体提交或回滚
- 如果事务中的任何元素失败，则整个事务将失败
一致性
- 当事务完成时，数据必须处于一致状态
- 在事务开始前，数据库中存储的数据处于一致状态
- 在正在进行的事务中，数据可能处于不一致的状态
- 当事务成功完成时，数据必须再次回到已知的一致状态
隔离性
- 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
- 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
持久性
- 指不管系统是否发生故障，事务处理的结果都是永久的
- 一旦事务被提交，事务的效果会被永久地保留在数据库中
示例
rollback（回滚）
先创建一个用于测试的表，添加测试的一行数据，然后使用rollback语句，再查询该表，可以看到刚才执行的操作并没有被回滚
mysql> create table test (id int(10), name char(20));
mysql> insert into test values (1, 'tom');
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
mysql> rollback;
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+commit（提交）
到这里就要涉及到提交的概念了
自动提交
默认开启，每一次执行的语句立刻被写入到磁盘
关闭后，执行的语句在提交之前会先被写入到内存中，手动提交后，才会写入到磁盘中
- 内存（数据缓冲区）：存储在这里的数据可以回滚
- 硬盘（永久存储）：存储在这里的数据不可以回滚
- 提交：意味着写入磁盘，就不能回滚了
先关闭自动提交，然后添加一行测试的数据
mysql> set autocommit=0;
mysql> insert into test values (2, 'jerry');
mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+手动提交
使用commit指令来手动提交数据来让数据写入到硬盘中实现永久存储
此时如果再次使用rollback指令执行回滚操作，就不会生效了
mysql> insert into test values (2, 'jerry');
mysql> commit;
mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+事务示例
使用begin指令表示事务的开始，commit表示事务的结束
mysql> begin;
mysql> insert into test values (3, 'A');
mysql> insert into test values (4, 'B');
mysql> commit;原文地址：https://blog.csdn.net/m0_65551023/article/details/140717521
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！