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

素颜马尾好姑娘i 2023-03-03 14:26 15阅读 0赞

**一、首先来说说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**  
特点: 将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问  
使用场景:通常用于更新不太频繁的小表，用以快速得到访问结果。  
总结: 针对不同的需求场景,来选择最适合的存储引擎即可!如果不确定,则使用数据库默认的存储引擎.  
**二、事务的隔离级别**  
![在这里插入图片描述][20200731165904992.png]

-- 查询事务隔离级别
    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的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTA3MTc2Ng_size_16_color_FFFFFF_t_70]从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如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的行锁，无索引行锁升级为表锁和间隙锁的情况。

[20200731165904992.png]: /images/20230208/25db6ceec46847ee95a0ace685f98cd1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTA3MTc2Ng_size_16_color_FFFFFF_t_70]: /images/20230208/f4195b1b4f2545c4b7b61f92a8c7abc8.png