MySQL存储引擎、索引、事务和锁 [TOC] ## 一、索引 ### 1. 索引的分类 - 单列索引 - 普通索引：没什么限制，只是为了让查询更快 - 唯一所以：保证值在全表中不重复，允许为`NULL`，但是为`NULL`时唯一索引不生效 - 主键索引：特殊的唯一索引，不允许为`NULL` - 联合索引：由多个字段组成的索引 - 全文索引：只有`MyISAM`引擎可以使用，并且只能为`char`、`varchar`、`text`字段类型设置 - 空间索引：······ ### 2. 索引的类型 - B+Tree - Hash 区别： 1. Hash索引不支持最左前缀原则 2. 大量数据重复时Hash索引会变慢，因为哈希碰撞问题 3. Hash索引无法利用索引排序 4. Hash索引只能匹配等值查询，无法匹配区间查询 ### 3. 创建索引的原则 - 优先选择唯一索引 - 同样的效率下，索引越少越好 - 为经常需要查询、排序、分组、以及联合查询的列创建索引 - 最左前缀匹配原则 - 索引列不能参与计算 ### 4. 最左前缀原则 - 由多个列组成的索引叫做联合索引，一个联合索引最多包含16个列 - 一个联合索引，如`(col1,col2,col3)`，可以拆分为`(col1)`、`(col1,col2)`、`(col1,col2,col3)` ```mysql # 联合索引由左向右匹配索引，上述索引以下where子句都可以命中 where col1=1; where col1=1 and col2=2; where col1=1 and col2=2 and and col3=3; ``` ## 二、存储引擎 ### 1. 常见的存储引擎 - InnoDB - MyISAM - Memory - Archive ### 2. 存储引擎的区别 | 功能 | InnoDB | MyISAM | MEMORY | ARCHIVE | | :----------- | :----- | :----- | :----- | :------ | | 存储限制 | 64TB | 265TB | RAM | NONE | | 支持事务 | YES | NO | NO | NO | | 支持全文索引 | NO | YES | NO | NO | | 支持B树索引 | YES | YES | YES | NO | | 支持哈希索引 | NO | NO | YES | NO | | 支持集群索引 | YES | NO | NO | NO | | 支持数据索引 | YES | NO | YES | NO | | 支持数据压缩 | NO | YES | NO | NO | | 支持外键 | YES | NO | NO | NO | | 支持数据缓存 | YES | NO | N/A | NO | | 空间使用率 | 高 | 低 | N/A | N/A | #### 2.1 InnoDB的特点 - 行级锁 - 支持热备份 - 支持缓存 - 支持事务 - 支持外键 - 支持缓存（innodb_buffer_pool_size） - 容灾性强。可通过`bin-log`恢复 #### 2.2 MyISAM的特点 - 支持大文件 - 支持全文索引 - 支持数据压缩 ### 3. 各存储引擎的适用场景 - InnoDB：数据安全性要求高，写操作频繁时 - MyISAM：新增、查询操作频繁时 - Memory：数据安全性要求不高、存放临时数据、数据量小时 - Archive：数据安全性要求不高、并发写操作时。例如日志 ## 三、事务 ### 1. ACID #### 1.1 A(Atomicity) 原子性 > 一句话概述：要么全部成功，要么全部失败。 指数据库中的所有操作全部成功才算事务成功，只要有一个操作执行出错则所以已执行的操作全部回滚。 #### 1.2 C(Consistency) 一致性 > 一句话概述：从一个正确的状态，迁移到另一个正确的状态。 指事务将数据库从一个操作的一致性状态转到下一个操作的一致性状态，在事务开始之前和之后，不能破坏数据库的完整性约束。 #### 1.3 I(Isolation) 隔离性 指每个读写事务对其他事务的操作对象能相互分离。 #### 1.4 D(Durability) 持久性 指事务一旦提交，其结果就是永久性的。 ### 2. 事务隔离级别 #### 2.1 读未提交 指一个事务可以读取到其他事务对数据的修改，即使其他事务没有提交。 问题：脏读 #### 2.2 不可重复读 指一个事务只能读取到其他事务已经提交的修改。 问题：破坏了事务的隔离性、幻读 #### 2.3 可重复读 指一个事务只能读取到自己开始时的数据。 问题：幻读：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读 #### 2.4 串行化 指后一个事务的执行只能等待前一个事务的结束。 ## 四、锁 #### 1. 各锁的特点 | 锁 | 开销 | 加锁时间 | 死锁 | 粒度 | 发生锁冲突的概率 | 并发度 | | ------ | ---- | -------- | -------- | ---- | ---------------- | ------ | | 表级锁 | 小 | 快 | 不会出现 | 大 | 高 | 低 | | 行级锁 | 大 | 慢 | 会出现 | 小 | 低 | 高 | | 页面锁 | 一般 | 一般 | 不会出现 | 一般 | 一般 | 一般 | #### 2. 锁的分类 | 锁粒度 | MyISAM | InnoDB | | ------ | ------------------------------------------ | ------------------------------------------------------ | | 表级锁 | 表级共享锁（读锁）<br />表级排他锁（写锁） | 意向共享锁（IS锁）<br />意向排他锁（IX锁） | | 行级锁 | 无 | 行级共享锁（读锁）<br />行级排他锁（写锁）<br />间隙锁 | ##### 2.1 表级锁 ###### 2.1.1 表级共享锁（读锁） 共享锁不会阻塞其他进程的读请求，但是会阻塞写请求，只有当前进程释放共享锁后，其他进程才可以进行写操作。 ###### 2.1.2 表级排他锁（写锁） 排他锁会阻塞其他的进程的读、写请求，只有当前进程释放排他锁后，其他进程才可以进行读、写操作。 ###### 2.1.3 MyISAM 优化建议 > MySQL会自动为`select`语句加上共享锁，为`insert`、`update`、`delete`加上排他锁。 由于这一特性，当多进程并发插入同一张表时就会因为排他锁而出现阻塞，MySQL中可以通过配置`concurrent_insert`系统变量控制阻塞。 - `concurrent_insert=0`：阻塞其他写操作 - `concurrent_insert=1`：若当前表中没有`空洞`存在，则在尾部插入记录（**MySQL默认设置**） - `concurrent_insert=2`：即使当前表中存在`空洞`，也在尾部插入记录 **空洞：** > 可以理解为软删除。当使用`delete`语句删除一行数据后，该行数据实际上并没有被删除，而是被MySQL标记为了已删除，由另一进程执行删除。 所以，如果一张表的删除操作较为频繁，且数据量较大时可以将`concurrent_insert`设置为`2`。 另外，如果一个进程在请求一张表的读锁的同时，另一个进程也在请求该表的写锁，即使读请求先到达，也会是写请求获得锁，这是因为MySQL默认**写比读优先**，要解决这一问题可以通过设置`low_priority_updates=1 `让读的优先级高于写。 ###### 2.1.3 意向共享锁（IS锁） 事务在获取行级共享锁之前，必须先获取到意向共享锁。MySQL会自动获取意向共享锁。 ###### 2.1.4 意向排他锁（IX锁） 事务在获取行级排他锁之前，必须先获取到意向排他锁。MySQL会自动获取意向排他锁。 ###### 2.1.5 意向锁存在的意义 假如没有意向锁，一个事务在获取行级锁之前需要对每一行检查是否已上锁，这样就很大的浪费了系统资源。 ##### 2.2 行级锁 ###### 2.2.1 行级共享锁（读锁） 当一个事务获取到某行的共享锁后，共享锁不会阻塞其他事务对该行的读请求，但是会阻塞其他事务对该行的写请求，直到共享锁被释放。 ###### 2.2.2 行级排他锁（写锁） 当一个事务获取到某行的排他锁后，排他锁会阻塞其他事务对该表的读、写请求，直到排他锁被释放。 ###### 2.2.3 四种锁之间的兼容 | 请求锁类型 ><br />当前锁类型 V | 排他锁（X锁、写锁） | 共享锁（S锁、读锁） | 意向排他锁（IX锁） | 意向共享锁（IS锁） | | ------------------------------ | ------------------- | ------------------- | ------------------ | ------------------ | | 排他锁（X锁、写锁） | 冲突 | 冲突 | 冲突 | 冲突 | | 共享锁（S锁、读锁） | 冲突 | 兼容 | 冲突 | 兼容 | | 意向排他锁（IX锁） | 冲突 | 冲突 | 兼容 | 兼容 | | 意向共享锁（IS锁） | 冲突 | 兼容 | 兼容 | 兼容 | ###### 2.2.4 间隙锁（Gap锁） > "间隙"指的是符合查询范围但是并不存在的记录。 > > 例如：id列中存在1,...,10,11的记录，SELECT语句的查询条件为`where id > 10`，那么>10但是并不存在的记录，如12、13等都属于“间隙”。 间隙锁可以解决`幻读`的问题。 间隙锁+行锁=Next-Key锁。 #### 3. 死锁 > 两个排他锁互相等待对方释放锁，才能继续执行称为“死锁”。 ###### 3.1 死锁检测 - InnoDB会自动检测死锁，一旦发现死锁，则会返回一个错误 - 通过以下变量获取 - 表级锁`Table_locks_immediate`：产生表级锁的次数 - 表级锁`Table_locks_waited`：表示不能立即获取锁，需要等待锁的次数 - 行级锁`innodb_row_lock_time`：表示从系统锁定到现在的总时长 - 行级锁`innodb_row_lock_avg`：表示每次等待所花的平均时间 - 行级锁`innodb_row_lock_waits：表示从系统锁定到现在的总次数 ###### 3.2 死锁恢复 当发生死锁后，只有其中一个事务完全或部分回滚才可以恢复。InnoDB的做法是检测到死锁后，回滚行级排他锁最少的事务。 ###### 3.3 避免死锁 - 在一个事务中，如果需要更新某行数据，可以先使用`SELECT...FOR UPDATE`语句申请排他锁，即使更新语句是放在后面执行 - 改变事务隔离级别 2019-11-21 MySQL 存储引擎 索引 事务 锁 展开评论
