服务器相关（6）：mysql索引、事务

一、创建索引、删除索引、查询索引

-- 索引的类型，主键索引（primary key）、唯一索引(unique)、普通索引(index)、全文索引(fulltext)
	CREATE TABLE suoyin_tab(
		id INT ,
		`name`VARCHAR(32) 
	);
	-- 查询表是否有索引
	SHOW INDEXES FROM suoyin_tab;
	-- 添加唯一索引
	CREATE UNIQUE INDEX id_index ON suoyin_tab(id);
	-- 查询表是否有索引
	SHOW INDEXES FROM suoyin_tab;
	
	-- 删除索引
	DROP INDEX id_index ON suoyin_tab;
	-- 添加普通索引
	CREATE INDEX id_index ON suoyin_tab(id);
	-- 查询表是否有索引
	SHOW INDEXES FROM suoyin_tab;
	
	-- 删除索引
	DROP INDEX id_index ON suoyin_tab;
	-- 添加普通索引的方式2
	ALTER TABLE suoyin_tab ADD INDEX id_index(id);
	-- 查询表是否有索引
	SHOW INDEXES FROM suoyin_tab;
	
	-- 删除索引
	DROP INDEX id_index ON suoyin_tab;
	-- 添加主键索引
	ALTER TABLE suoyin_tab ADD PRIMARY INDEX id_index(id);
	-- 查询表是否有索引
	SHOW INDEXES FROM suoyin_tab;

二、事务

2.1事务操作

-- 事务就是保证数据的一致性
	
	-- 1、创建一张测试表
	CREATE TABLE shiwu_tab(
		id INT ,
		`name`VARCHAR(32) 
	);
	
	-- 2、开始事务
	START TRANSACTION
	-- 3、设置保存点a
	SAVEPOINT a
	-- 执行dml
	INSERT INTO shiwu_tab VALUES(100,'tom');
	-- 查询
	SELECT * FROM shiwu_tab;
	-- 设置保存点b
	SAVEPOINT b
	INSERT INTO shiwu_tab VALUES(200,'jack');
	SELECT * FROM shiwu_tab;
	-- 回退到b
	ROLLBACK TO b
	SELECT * FROM shiwu_tab;
	
	-- 回退到a
	ROLLBACK TO a
	SELECT * FROM shiwu_tab;
	
	-- commit提交事务后，所有的操作生效，就不能回退了

2.2 事务隔离级别

-- 多个事务开启各自事务操作数据库的数据时，数据库要负责隔离操作
-- 如果不考虑隔离型，会产生”脏读“、”不可重复读“、”幻读“

	-- 1、脏读：事务A读取了事务B尚未提交的改变（update、insert、delete），那么A读取到的数据是脏数据
	
	-- 2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
	
	-- 3、幻读：系统管理员A将数据库中所有学生的成绩从具体分 数改为ABCDE等级，但是系统管理员B就在这个时候插入了一 条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

事务隔离级别	脏读	不可重复读	幻读	加锁读
读未提交	是	是	是	不加锁
不可重复读	否	是	是	不加锁
可重复读	否	否	是	不加锁
串行化	否	否	否	加锁

-- 1、开启两个控制台
	-- mysql -u root -p
	-- 2、查看当前mysql的隔离级别
	-- mysql> select @@tx_isolation;
	-- +-----------------+
	-- | @@tx_isolation  |
	-- +-----------------+
	-- | REPEATABLE-READ |
	-- +-----------------+
	-- 1 row in set (0.00 sec)

-- 3、把控制端2设置成read uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 4、两边启动事务
START TRANSACTION;

-- 5、创建表
USE db01

CREATE TABLE `account`(
	id INT,
	`name` VARCHAR(32),
	money INT);
-- 查看隔离级别	
SELECT @@tx_isolation;
-- 添加数据
INSERT INTO `account` VALUES(100,'tom',1000);
-- 查询产生脏读
SELECT * FROM `account`;

UPDATE `account` SET money = 800 WHERE id = 100;
INSERT INTO `account` VALUES(200,'jack',2000);
COMMIT;
-- 对第二个控制端，发现产生幻读
SELECT * FROM `account`;

-- 两边都
COMMIT;
-- 将控制端2的隔离级别改成读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation;
-- 两边开启事务
START TRANSACTION;
-- 控制端1添加数据并查看
INSERT INTO `account` VALUES(300,'scout',3000);
SELECT * FROM `account`;
-- 控制端2查看，不会出现脏读
SELECT * FROM `account`;

-- 两边都
COMMIT;
-- 将控制端2的隔离级别改成读可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@tx_isolation;
-- 两边开启事务
START TRANSACTION;
-- 控制端1添加数据并查看
INSERT INTO `account` VALUES(400,'milan',6000);
UPDATE `account` SET money = 100 WHERE id = 300;
-- 两边查看，控制端1按照已有改变进行改变了，控制端2未发生脏读、可重读读、幻读
SELECT * FROM `account`;


-- 两边都
COMMIT;
-- 将控制端2的隔离级别改成读可重复读
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@tx_isolation;
-- 两边开启事务
START TRANSACTION;
-- 控制端1添加数据并查看
INSERT INTO `account` VALUES(500,'terry',80000);
UPDATE `account` SET money = 900 WHERE id = 300;
-- 两边查看，控制端1按照已有改变进行改变了，控制端2会被卡住,直到超时，
SELECT * FROM `account`;
-- 控制端1提交，控制端2正常
COMMIT;

三、 表类型和存储引擎

-- 表类型由存储引擎决定，存储引擎主要包括MySAM、innodb、Memery
-- mysql表主要支持6种类型，CSV、Memory、ARCHIVE、MRG_MYSAM、MYSIAM、InnoBDB 
SHOW ENGINES;

-- innodb 1、支持事务 2、支持外键 3、支持表级锁
	
	
	CREATE TABLE yinqing_tab(
		id INT ,
		`name`VARCHAR(32)) ENGINE MYISAM,	
	
	-- mysiam 1、添加速度快 2、不支持外键和事务 3、支持表级锁
	START TRANSACTION;
	SAVEPOINT t1
	-- 控制端1添加数据并查看
	INSERT INTO yinqing_tab VALUES(1,'jack');
	SELECT * FROM yinqing_tab;
	ROLLBACK TO t1;
	
	
	-- memory 1、数据存储在内存中 2、执行速度很快，因为没有IO读写 3、默认支持索引（哈希表）
	CREATE TABLE memory_tab(
		id INT ,
		`name`VARCHAR(32)) ENGINE MEMORY	
	DESC memory_tab
	INSERT INTO memory_tab
		VALUES(1,'tom'),(2,'jack'),(3,'ch')
	SELECT * FROM memory_tab;