Mysql索引与事务（四）

前言

本文将学习索引与事务，首先要讲解索引的概念与各类型索引的创建、删除，并且学习事务的流程。

一、数据库索引基础

1.1 为什么要索引

请大家试想一下，当服务访问量（并发量）高时，每个客户端（用户）的数据都要存入mysql的数据库中，这很容易导致堵塞，使用户访问速度十分慢，俗称“卡”，而索引的出现，目的就是为了缓解“卡”的这种现象。
假设没有索引，我们要去找到一个用户张三，就得在自己的表中从上到下一行一行寻找，如果表中数据有十万行，那么你可能得到第四万多行才找的到，这个数据量是非常庞大的，而如果你创建一个以字母分类的目录，那么你只要从z类中开始寻找就可以了，这大大减少了搜索量。

1.2 索引运用场景

1.主键和外键
主键：唯一性标识，自动创建索引
外键：关联查询，快速定位 两张表 学生 学习表 信息表
2.大表（>300行）
超过300行的表建议创建索引
小表索引效果不明显，反而影响写入性能
3.频繁查询的字段
经常出现在WHERE子句中的字段
用户名、手机号、邮箱等常用查询字段
4.连接字段
经常用于表连接的字段 订单表 下单表
如用户表和订单表的user_id

1.3 索引的优点与缺点

优点：
1、加快表之间的连接速度
2、加快访问速度
3、降低排序成本
缺点：
1、使用麻烦：每次增删改表，都需要修改索引
2、内存占用大

二、索引实操

2.1 创建索引

2.1.1 直接创建

CREATE INDEX 索引名 ON 表名 (列名[(length)]) ————————创建索引

2.1.2 修改表方式

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

2.1.3 创建表时创建索引

CREATE TABLE 表名 ([…],PRIMARY KEY (列名));

2.2 组合索引（复合索引）

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

触发索引要按照顺序搜索，比如刚刚做的索引为id,age 那么触发索引则要select id,age from 表名

2.3 全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

2.4 主键索引

主键索引是特殊的一种唯一索引，它不支持空数据，并且每个表中只允许有一个主建索引。
ALTER TABLE 表名 ADD PRIMARY KEY (列名); ————修改表时创建
CREATE TABLE 表名 ([…],PRIMARY KEY (列名)); ——————创建表时创建索引

三、索引的查看与维护

3.1 查看索引

SHOW INDEX FROM 表名;
SHOW INDEX FROM 表名\G; ——————————竖直排列

3.2 删除索引

DROP INDEX 索引名 ON 表名 ————————删除普通索引

ALTER TABLE 表名 DROP PRIMARY KEY ————————删除主键索引


ALTER TABLE 表名 DROP INDEX 索引名; ——————修改表时创建索引

5、MySQL事务基础

5.1 事务的原理

例如a向用户转账100元
1、检查a用户余额是否有100元
2、减少a用户100元
3、b用户增加100元
4、记录转账日志
事务就是用来解决a用户已经扣钱，但是b用户没有加钱这一问题的

5.2 事务的基本特性

1、原子性
四个操作必须都要完成，中间如果有一步没有完成，转账过程将会终止。
2、一致性
转账后总额数不变，与转账钱保持一致
3、隔离性
每个用户的事务是独立的，互不影响。
4、持久性
保存进数据库中的数据是永远不会自然丢失的

5.3 测试事务提交

SET AUTOCOMMIT=0; ——————禁止自动提交
SET AUTOCOMMIT=1; ————————开启自动提交，Mysql默认为1
SHOW VARIABLES LIKE ‘AUTOCOMMIT’; ——————查看Mysql中的AUTOCOMMIT值
begin； ——————开始事务
commit; ————————保存数据

5.4 事务回滚

回滚可以理解为一个存档，可以通过这个存档，返回之前的时间点。
也可以理解为不对该次数据变动进行保存。
rollback； ————————回滚
rollback to 节点 ————————回滚到某一节点，并保存该节点之前的修改
savepoint 节点名称 ————————保存该数据库数据为某一节点

测试多点回滚


如果开起了自动提交，mysql会把每个sql语句当成一个事务，然后自动的commit。
如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback或commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。

总结

本文讲解了索引的概念与操作以及事务的流程并且进行了测试，希望本文内容可以对大家有所帮助，谢谢观看😜