事务

什么是事务？

要么都成功，要么都失败

事务ACID原则：

原子性，一致性，持久性，隔离性

reference: blog.csdn.net/dengjili/ar…

SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启 （mysql是默认开启事务自动提交的）

手动处理事务的完整流程

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

-- 事务开启，只有2种结果，要么提交，要么回滚
START TRANSACTION -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT

-- 回滚：回到原来的样子（失败！）
ROLLBACK

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

测试事务 实现转账

use shop;

SET autocommit = 0;
START TRANSACTION;

UPDATE `account` SET money=money-500 WHERE `name` = 'A';
UPDATE `account` SET money=money+500 WHERE `name` = 'B';

COMMIT;
ROLLBACK;

SET autocommit = 1;

索引

索引在小数据量的时候，用处不大，但在大数据的时候，区别十分明显。可以用 EXPLAIN 来分析 sql 执行的状况。

索引是帮助MySQL高效获取数据的数据结构

分类：

• 主键索引 (primary key)
  • 唯一标识

  CREATE INDEX index_name ON table_name(column_name);
  

• 唯一索引 (unique key)
  • 避免重复的类出现，唯一索引可以重复

  CREATE UNIQUE INDEX index_name on table_name(column_name);
  

• 常规索引 (key/index)
• 全文索引 (fulltext)

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

EXPLAIN SELECT * FROM student; -- explain 分析 sql 的执行状况

-- 创建索引
-- CREATE INDEX 索引名 on 表(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`);

SQL编程创建100万条数据 测试索引

首先建表： sql 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用户表';

（即用函数来插入数据）

创建函数如下，了解即可。mock_data() 是函数名

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`) VALUES(CONCAT('用户',i),'19224305@qq.com',123456789,FLOOR(RAND()*2));
		SET i=i+1;

	END WHILE;
	RETURN i;
END

执行函数

SELECT mock_data(); -- 执行此函数 生成一百万条数据

-- 对比下执行时间
SELECT * FROM `app_user` WHERE `name` = '用户999'; -- 查询耗时448ms

SELECT * FROM student; -- 查询耗时2ms

-- 分析下这条 sql 语句
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户999'; -- 看 rows 列就好了，查了995115行

-- 创建索引 (作用：优化查询时间)
-- CREATE INDEX 索引名 on 表(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM `app_user` WHERE `name` = '用户999'; -- 查询耗时0.001秒

-- 分析 创建索引后 的这条 sql 语句
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户999'; -- rows 只查了1行，唯一定位，不用去遍历了！！

索引原则

• 小的数据表不应当使用索引；
• 需要频繁进行大批量的更新或者插入操作的表不应当使用索引；
• 索引一般加在常用来查询的字段上