从存储引擎入手学习MySQL的事务隔离级别、锁和索引

一、首先来说说MySQL的存储引擎

	-- 查看数据库支持的存储引擎
	show engines;
	-- 查询某个数据表的存储引擎
	show table status from 数据库名 
		where name = '数据表名';
	-- 创建表,并指定存储引擎
	create table 表名(
		列名 数据类型 约束,
		...
	)engine = 引擎名;	
	-- 修改数据表的存储引擎
	alter table 表名 engine = 引擎名;
	show engines;	-- 查看当前数据库软件支持的所有引擎
	show create table account; -- 查看表的创建信息

1，oracle/sql server:只有一个引擎；mysql:多个引擎（但是一张表只能用某一个），很牛逼的插件化搜索引擎结构，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
MySQL的体系结构大概分为：连接层，服务层，引擎层，存储层。很明显，引擎在引擎层。
2，MySQL的引擎有很多种，我们这里只说常见的三种InnoDB，MyISAM，MEMORY。其中InnoDB提供事务安全表。注意，MySQL5.5之前默认存储引擎是MyISAM，之后就改为了InnoDB。
3，InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
4：特点和使用场景：
InnoDB
特点: 是Mysql的默认存储引擎，支持事务和外键操作
使用场景:对事务的完整性有比较高的要求，在并发条件下要求数据的一致性,读写频繁的操作
MyISAM
特点: 不支持事务和外键操作.读取速度快,节约资源
使用场景: 以查询操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高
MEMORY
特点: 将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问
使用场景:通常用于更新不太频繁的小表，用以快速得到访问结果。
总结: 针对不同的需求场景,来选择最适合的存储引擎即可!如果不确定,则使用数据库默认的存储引擎.
二、事务的隔离级别

-- 查询事务隔离级别
select @@tx_isolation; -- 当前的mysql隔离级别

-- 设置全局事务隔离级别
set global transaction isolation level 四种级别名字;
-- 设置会话事务隔离级别(只对当前连接生效)
set session transaction isolation level 四种级别名字;

/*
	1. Read uncommitted (读未提交)： 级别最低，会同时出现三种问题
	2. Read committed (读已提交)：可以解决脏读的问题
	3. Repeatable read (可重复读)：可以解决脏读和不可重复读的问题
	4. Serializable (串行化)： 所有的事务都是以串行的方式执行，没有并发执行的事务
*/

三、锁
1，锁概述
锁用在并发场景下

• 锁机制: 数据库为了保证数据的一致性,在共享资源被并发访问时变得安全所设计的一种规则.
• 锁机制类似多线程中的同步, 作用就是可以保证数据的一致性和安全性.
  2，锁分类
  从对数据操作的粒度分 ：
• 表锁：操作时，会锁定整个表。开销小,加锁快.锁定粒度大,发生锁冲突概率高,并发度低
• 行锁：操作时，会锁定当前操作行。开销大,加锁慢.锁定粒度小,发生锁冲突概率低,并发度高
  从对数据操作的类型分：
• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响,但是不能修改数据。
• 写锁（排它锁）：当前操作没有完成之前，它会阻断其他操作的读取和写入。
  3，mysql锁
  对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：
  从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用系统。
  4，MyISAM 表锁

select @@autocommit; -- 1表示自动提交,0表示手动提交
set @@autocommit = 0; -- 禁止事务自动提交
-- 读锁: 其他连接能读,但是不能写
lock table tb_user read;
unlock tables; -- 解锁之后别人才能改
-- 写锁: 其他连接不能读,也不能写
lock table tb_user write;

1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
5， InnoDB 行锁
行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
优化建议：

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范
• 尽可能减少索引条件，及索引范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可使用低级别事务隔离（但是需要业务层面满足需求）

四，索引
1，创建索引
① 直接创建

-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2);
-- name字段适合设置什么索引？ 普通
CREATE INDEX idx_name ON student(`name`);

-- telephone适合设置什么索引？ 唯一
CREATE UNIQUE INDEX idx_uni_telephone ON student(telephone);

② 修改表时指定

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique 索引名(字段);
-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index 索引名(字段); 

-- 	该语句指定了索引为FULLTEXT， 用于全文索引(了解)
alter table 表名 add fulltext 索引名(字段);

-- 指定id为主键索引
ALTER TABLE student ADD PRIMARY KEY(id);
-- 指定name为普通索引
ALTER TABLE student ADD INDEX idx_name(`name`);
-- 指定telephone为唯一索引
ALTER TABLE student ADD UNIQUE idx_uni_telephone(telephone);

③ 创建表时指定

-- 创建教师表
CREATE TABLE teacher(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 `name` VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 student_id INT,
 INDEX(`name`), -- 普通索引
 CONSTRAINT fk_01 FOREIGN KEY (student_id) REFERENCES student(id) -- 外键索引   
);

-- 备注: 如果不指定索引名称,默认为建立索引的字段名

2，删除索引

-- 直接删除
drop index 索引名 on 表名;

-- 修改表时删除 【掌握】
alter table 表名 drop index 索引名;

-- 删除name普通索引
DROP INDEX idx_name ON student;

-- 删除telephone唯一索引
ALTER TABLE student DROP INDEX idx_uni_telepphone;

3，索引的设计
索引的特点总结

1. 索引本身也是一张表, 也需要存在磁盘上
2. 优点: 减少磁盘IO,提高查询效率
3. 缺点: 索引占用空间,在进行增删改操作时,索引的维护会增加成本,可能降低服务器性能

索引的设计原则

1. 对查询频次较高,且数据量比较大的表建立索引
2. 使用唯一索引,区分度越高,使用索引的效率越高(精准查询比模糊查询快)
3. 索引字段的选择,最佳候选列应当从where字句的条件中提取
4. 不要随意创建索引,因为维护索引也需要成本

最左匹配原则（适用组合索引）

• alter table user add index idx_three(name,address,phone);
• 实际上建立了(name)、(name,address)、(name,address,phone)三个索引；
• 可以被三种情况命中：(name)、(name,address)、(name,address,phone)；
• 索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序；
  整体总结：
  1，MySQL没有事务，所以就算把autocommit设置为0也没有用，还是会自动提交。由于没有事务，所以只能用表锁来保证并发的安全性，所以增删频繁的时候效率很低。
  它在读取和写入的时候都会自动加锁，读锁不允许别人写数据，写锁不允许别人读和写数据。
  我们不好演示高并发情况下的读写锁，所以我们手动加上锁演示。
  2，因为MySQL默认的隔离级别是repeatable read(可重复读)，且InnoDB有事务，所以它读的时候不用加锁，但写的时候会加排它锁。
  3，三种并发访问问题（脏读，不可重复读，幻读）对应四种权限。
  4，注意InnoDB的行锁，无索引行锁升级为表锁和间隙锁的情况。