MySQL（五） 事务和索引(测试百万数据下索引的快速查询)

事务 ：是一组sql 要么都成功 要么都失败

1.事务原则 : ACID 原子性 一致性 隔离性 持久性 (脏读 幻读)

原子性（Atomicity）
要么都成功，要么都失败
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离,互不干扰。
持久性（Durability）
事物一旦提交则不可逆，被持久化到数据库中
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

​

脏读

指一个事务读取了另一个事务未提交的数据

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

--   ===================== 事务 ===============================

-- mysql默认开启事务自动提交的

SET autocommit = 0  -- 关闭自动提交
SET autocommit = 1  -- 开启自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始 ,从这个之后的sql都在同一个sql

COMMIT -- 提交 ：持久化(成功)

ROLLBACK -- 回滚:回到原来的样子

 -- 事务结束
 SET autocommit =1 -- 开启自动提交
 
  --了解
SAVEPOINT 保存点 -- 设置一个事务的保存点

ROLLBACK TO SAVEPOINT 保存点 -- 回滚到保存点

RELEASE 保存点 -- 移除保存点

2.索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引(primary key)
唯一的标识，主键不可重复，只能只有一个列作为索引
唯一索引 (unique key)
避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引(key / index)
默认的 index key 关键字来设置
全文索引(fulltext)
在特定的数据库引擎下

-- 索引
-- 1 在创建表的时候给字段增加索引
-- 2 创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM `student`

-- 增加一个索引
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)


-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM `student` -- 非全文索引

EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('张')

3.插入百万数据进行索引查询

建表app_user：

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用户表'

批量插入数据：100w

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), '1550213743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

SELECT * FROM app_user WHERE `name` ='用户9999';第一次查询

CREATE INDEX id_app_name ON `app_user`(`name`);添加索引

SELECT * FROM app_user WHERE `name` ='用户9999';第二次查询

第一次时间

第二次时间

4.索引原则

索引不是越多越好

不要对经常变得数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

索引的数据结构

hash 类型的索引

Btree: innoDB 的默认数据结构