MySQL学习笔记（六）------事务和索引

MySQL学习笔记（六）------事务和索引

事务

什么是事务？

• 将一组SQL放在同一个批次中去执行

• 特点：要么都成功，要么都失败

• 原则：ACID原则：原子性，一致性，隔离性，持久性 （脏读，幻读…）

原子性（Atomicity）

​ 是指一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。

• 比如A给B支付200元，这个事务可以分成两个步骤：1、A减少200元，2、B收到200元。不可能A少了，而B没有多。这两步必须同时完成，要么就都不完成。不能只发生一个动作。

一致性（Consistency）

​ 是指事务的运行并不改变数据库中数据的一致性。

• 最终一致性：A有1000，B有200，那么无论怎么更改，AB总和都有1200

隔离性（Isolation）

​ 事务的隔离性也称作独立性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。

• 如果隔离性出现错误，会有很多问题产生

持久性（Durability）

​ 事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会因为外界原因导致数据丢失。事务一旦提交不可逆。

• 例如：操作前—A：1000，B：200；操作后—A：800，B：400
• 如果在操作前（事务没有提交），服务器断电，那么重启后，数据应为A：1000，B：200
• 如果在操作后（事务已经提交），服务器断电，那么重启后，数据应为A：800，B：400

不考虑隔离性会发生什么问题？

脏读

• 指一个事务读取了另一个事务没有提交的数据

不可重复读

• 在一个事务内读取某一行数据时，多次读取结果不同。（不一定是个错误，只是某些场合不对）
• 例如：开始读取数据时B为300，生成表单时，B收入100，此时读出B的数据为400

幻读（虚读）

• 指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（一般是行的影响，多了一行）
• 例如：开始读取数据时，数据表中只有A,B两行；生成表单时，新插入了数据C，此时读出数据为A,B,C三行

四种隔离级别

读未提交（Read uncommitted）：

• 这种事务隔离级别下，select语句不加锁。

• 此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。

读已提交（Read committed）：

• 可避免脏读的发生。

• 在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

可重复读（Repeatable read）：

• MySql默认隔离级别。

• 可避免脏读 、不可重复读的发生。

串行化（Serializable ）：

• 可避免脏读、不可重复读、幻读的发生。

级别

• 以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别
• 级别越高，执行效率越低

基本语法

• MySQL时默认开启事务自动提交的

SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

• 手动处理事务（关闭自动提交）
• 事务开启

START TRANSACTION 

• 提交一个事务给数据库

COMMIT

• 将事务回滚：回到本次事务开始时的样子

ROLLBACK

• 手动处理事务结束（开启自动提交）

• 保存点（了解）（一个事务可以有多个保存点）

SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 撤销已有保存点

模拟转账

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `cash` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
 
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
 
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
--rollback;
SET autocommit = 1; -- 恢复自动提交

索引

• MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：
• 索引是数据结构
• 常规命名方式：id_表名_字段名

分类

主键索引（Primary Key）

• 唯一标识，主键不可重复
• 只能有一个列作为索引

唯一索引（Unique Key）

• 避免重复的列出现，唯一索引可以重复
• 多个列都可以标识为唯一索引

常规索引（Key/Index）

• 默认的，可以用index或key关键字来设置

全文索引（FullText）

• 在特定的数据库引擎下才有
• 快速定位数据

索引的使用

• 在创建表的时候给字段增加索引
• 创建完毕后，增加索引

基础命令

• 显示所有的索引信息

show index from 表名

• 已知表增加一个索引

alter table `表名` add 索引类型 index `索引名`(`列名`)
alter table `student` add fulltext index `studentname`(`studnetname`)
--给某个表中的某个字段加一个索引
CREATE 索引类型 INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;

• 创建表时增加索引

CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                索引类型   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );

• 删除索引

DROP INDEX 索引名 ON 表名字;          --删除非主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;   --删除主键索引

• 显示索引信息

SHOW INDEX FROM student;

• 分析sql执行的状况

explain select * from student;  --非全文索引
explain select * from student where match(studentname) against('刘'); ---全文索引（在数据量很少的时候没有用）

测试索引

建表

CREATE TABLE `app_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
  `email` varchar(50) NOT NULL COMMENT '用户邮箱',
  `phone` varchar(20) DEFAULT '' COMMENT '手机号',
  `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
  `password` varchar(100) NOT NULL COMMENT '密码',
  `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

插入100万条数据（不用掌握）

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$   --写函数之前必须要写，当作标志
CREATE FUNCTION mock_data()    --创建一个函数
RETURNS INT     --返回值
BEGIN          --函数体
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i < num DO     --循环操作
  	INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
    VALUES(CONCAT('用户', i), '123456897@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
    --concat（'用户',i），拼接用户名为用户i
    --floor取整 rand随机数（有小数，所以要用floor取整）
   SET i = i + 1;      --i自增操作
  END WHILE;           --循环结束
  RETURN i;
END;
SELECT mock_data();

创建常规索引

CREATE INDEX idx_app_user_name ON app_user(name);

索引的用途

• 在有索引的情况下，查询数据更加高效，直接定位需求数据，没有索引需要遍历所有的数据
• 索引在数据量比较小的时候，用处不大；在数据量大的时候，提升效率十分明显

索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上（提高查询速度）

索引的数据类型（拓展）

• Hash类型的索引
• Btree类型的索引：InnoDB的默认数据结构