存储引擎
InnoDB是MySQL的默认存储引擎,MySQL5.1版本前是MyISAM
| 存储引擎 | 外键 | 事务 | 锁 | 崩溃恢复 | |
|---|---|---|---|---|---|
| InnoDB | 支持 | 支持 | 行锁及表锁 | 事务日志恢复 | |
| MyISAM | 不支持 | 不支持 | 表锁 | 单元格 | 无 |
索引
索引的作用相当于书的目录,是一种用于快速查询和检索数据的数据结构
- 优点 加速查询；将随机I/O变成顺序I/O（B+树）；
- 缺点 对数据进行增删改需要花时间变更索引，同时索引需要占据空间存储
B树和B+树
B+树的内部节点只有键没有值，叶子节点存放所有的键和值且有序相连，而B树中的内部节点和叶子节点均存放键和值
- B树适用于随机检索，而B+树适用于随机检索和顺序检索
- B+树的空间利用率更高，因为B树每个节点要存储键和值，而B+树的内部节点只存储键，这样B+树 的一个节点就可以存储更多的索引，从而使树的高度变低，减少了I/O次数，使得数据检索速度更快
- B+树的叶子节点都是连接在一起的，所以范围查找，顺序查找更加方便
- B+树的性能更加稳定，因为在B+树中，每次查询都是从根节点到叶子节点，而在B树中，要查询的 值可能不在叶子节点，在内部节点就已经找到
哈希索引和B+树
链地址法就是将哈希冲突数据存放在链表中
- 哈希索引不支持排序，因为哈希表是无序的
- 哈希索引不支持范围查找
- 哈希索引不支持模糊查询及多列索引的最左前缀匹配
聚簇索引和非聚簇索引
聚簇索引是指数据和索引一起存储，索引结构的叶子节点保留了数据；非聚簇索引是指数据和索引分开存储，索引叶子节点存储的是指向数据行的地址
- InnoDB存储引擎（默认B+树）中主键创建的索引为主索引，也是聚簇索引，叶子节点放的数据；在主索引之上创建的索引为辅助索引，也是非聚簇索引，叶子节点放的是主键（一般情况下需要回表查询即查询主键，再去主索引里查数据，例外情况是select 主键 ... 即查到主键即可的情况不需要回表）
- MyISAM存储引擎（默认B+树）中主索引和辅助索引都是非聚簇索引，叶子节点放的指向数据的地址
索引的设计原则
- 最适合索引的列是在where后面出现的列或者连接句子中指定的列
- 索引列的基数越大，索引的效果越好，例如只有男或女这两种数据不适合作为索引
- 尽量使用短索引
最左匹配原则
从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配
- 索引(a,b,c)
- select * from table_name where c = 1 and b = 2 and a = 3 用到索引，自动优化调整顺序
- select * from table_name where b = 1 没用到
索引失效
- 在使用like查询时以%开头会导致索引失效
- 索引上使用！、=、<>进行判断时会导致索引失效
- 索引字段上使用 is null/is not null判断时会导致索引失效
- 在索引中使用函数会导致索引失效
- 在索引的类型上进行数据类型的隐形转换
- 在索引上进行计算会导致索引失效
- 条件中有or
数据库事务
事务是逻辑上的一组操作，要么都执行，要么都不执行
四大特性（ACID）
- 原子性：原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。
- 一致性：一致性指事务在执行前后状态是一致的。
- 隔离性：一个事务所进行的修改在最终提交之前，对其他事务是不可见的。
- 持久性：数据一旦提交，其所作的修改将永久地保存到数据库中。
InnoDB事务实现原理
- redo log(重做日志)持久性
- 使用undo log(回滚日志)来保证事务的原子性
- 通过锁机制、MVCC等手段来保证事务的隔离性（默认支持的隔离级别是
REPEATABLE-READ） - 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障
并发一致性问题
- 脏读：事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚 了，事务B读取到的数据就成为脏数据了。
- 不可重复读：事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交 了，导致事务A多次读取到的数据并不一致。
- 幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时 发现多了几条数据，和之前读取的数据不一致。
- 丢失修改：事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改 覆盖了事务A的修改。
隔离级别
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离原理
事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的，提交读和可重复读可以通过MVCC 实现，串行化可以通过锁机制实现
- 当前读：读取的是数据库的最新版本，并且在读取时要保证其他事务不会修该当前记录，所以会对 读取的记录加锁。
- 快照读：不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗。
MVCC
-
版本链 每条数据有两个隐藏字段trx_id以及roll_pointer；每当数据更新时，都会把旧版本的数据写到undo日志中，roll_pointer就相当于一个指针，指向上一条记录（事务id大的指向事务id小的），形成版本链
-
readview mvcc只在rc（read committed）和rr（repeatable read）中使用到，是因为read uncommitted会造成脏读，那读取最新的表头数据就可以，serializable会锁定整张表，也用不到。当我们写一条select语句的时候，会自动生成一个readview视图，视图中包含以下信息
-
trx_ids: 当前系统活跃(未提交)事务版本号集合。
-
low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。
-
up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”
-
creator_trx_id: 创建当前read view的事务版本号；
如果落在绿色区域，这个数据是可见的
如果落在红色区域，这个数据是不可见的
如果落在黄色区域：
1，如果当前版本的事务id是未提交的，就不可见
2，如果当前版本的事务id是已经提交的，可见
- 注意
repeatable read ——第一次读取数据的时候生成一个readview
read committed ——每次读取数据的时候生成一个readview
