MySQL(六)事物(ADID,四种隔离级别)(七)索引(索引测试,原则)

6-事物

事务原则:ACID原则 原子性, 一致性, 隔离性, 持久性

原子性(Atomicity)

要么都成功, 要么都失败

一致性(Consistency)

事务前后的数据完整性要保证一致

持久性(Durability)

事务一旦提交则不可逆, 被持久化到数据库中

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时, 数据库为每一个用户开启的事物, 不能被其他事物的操作干扰, 事物之间要相互隔离

隔离导致的一些问题

**脏读: **

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

**不可重复读: **

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

**虚读(幻读): **

在一个事务内读取到了其他事物插入的数据, 导致前后读取不一致

1,执行事物

-- mysql默认开启自动提交
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启

-- 手动处理事物
SET autocommit = 0 ; -- 关闭自动提交

-- 事物开启
START TRANSACTION -- 标记一个事务的开始, 从此之后的SQL 都在同一个事务内

-- 数据操作
UPDATE XX

-- 提交: 持久化 - 成功
COMMIT
-- 回滚: 回到原来的样子  - 失败

-- 事物结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点1 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点1 -- 回滚到指定保存点
RELEASE SAVEPOINT 保存点1 -- 撤销指定保存点

2,模拟事务

-- 创建shop库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
use shop;

-- 创建account表
CREATE TABLE `account`(
	`id` int(3) not null auto_increment,
	`name` VARCHAR(30) not null,
	`money` DECIMAL(9,2) not null,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 添加A,B用户
INSERT INTO account(`name`,`money`) 
VALUES ('A',2000.00),('B',10000.00);

-- 模拟转账 事务
set autocommit =  0; -- 关闭自动提交
start TRANSACTION; -- 开启事务
update account set money = money-500 where `name` = 'A'; -- A减500
update account set money = money+500 where `name` = 'B'; -- B加500

commit; -- 提交
rollback; -- 回滚

set autocommit = 1; -- 恢复自动提交

3,事务的四种隔离级别

隔离级别\读数据一致性及允许的并发副作用	读一致性	脏读	不可重复读	幻读
读未提交（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据	是	是	是
读提交（Read committed）	语句级	否	是	是
可重复读（Repeatable Read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

• 读未提交（Read uncommitted） 一个事务可以读取另一个未提交事务的数据
• 读提交（Read committed） 一个事务要等另一个事物提交后才能读取数据
• 可重复读（Repeatable Read） 一个事务在开始读取数据时, 不允许修改操作
• 可序列化（Serializable） 最高隔离级别, 事务串行化顺序执行，可以避免脏读、不可重复读与幻读。效率底下, 一般不使用

详细参考链接：https://blog.csdn.net/djy_2017/article/details/104561930

7-索引

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

Mysql官方对索引的定义为: 索引 index 是帮助MySQL高效获取数据的数据结构板.

提取句子骨干, 就可以得到索引的本质: 索引是数据结构

7.1, 索引的分类

• 主键索引 (PRIMARY KEY)
  • 唯一标识, 主键不可重复
• 唯一索引 (UNIQUE KEY)
  • 避免重复列出现, 可以设置多个唯一索引
• 常规索引 (KEY)
  • 默认的, index, key 关键字设置
• 全文索引 (FULLTEXT)
  • 在特定的数据库引擎采用, MYISAM
  • 快速定位数据

-- 索引的使用
-- 1, 在创建表的时候给字段增加索引
-- 2, 创建完毕后手动添加

-- 显示所有的索引信息
SHOW INDEX FROM student;

-- 增加一个全文索引
ALTER TABLE school.student ADD FULLTEXT INDEX studentname(studentname);

-- EXPLAIN  分析SQL执行情况
EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');

7.2,测试索引

插入100万数据

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'


-- 插入100万数据.
DROP FUNCTION IF EXISTS mock_data;
-- 写函数之前必须要写，标志:$$
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i < num DO
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), CONCAT('100',i,'@qq.com'), CONCAT('13', 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'
> OK
> 时间: 0.476s
EXPLAIN select * from app_user WHERE `name` = '用户9999';
1	SIMPLE	app_user		ALL					992139	10	Using where 
-- 查询了99万数据才找到

-- 添加索引
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name on app_user(`name`)
> OK
> 时间: 3.851s
-- 再次查询
select * from app_user WHERE `name` = '用户9999'
> OK
> 时间: 0.001s

7.3,索引原则

• 索引不是越多越好
• 不对经常变动的数据加索引
• 数据量小的表不需要加索引
• 索引一般加在常查询的字段上

索引的数据结构

Hash类型的索引

Btree: INNODB的默认数据结构

文章: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

显然这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。