三大范式
- 第一范式（1NF）：每一列都是不可分割的原子数据项。
- 第二范式（2NF）在1NF的基础上，非属性码的属性必须完全依赖于主码。
- 第三范式（3NF）：在2NF基础上，消除传递依赖。
事务
概述
事务指的是逻辑上的一组操作，组成这组操作的各个单元要么全都成功，要么全都失败。
四大特性ACID
- 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）：事务前后数据的完整性必须保持一致。
- 隔离性（Isolation）：多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。隔离性由隔离级别保障！
- 持久性（Durability）：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
并发问题
- 脏读：一个事务读到了另一个事务未提交的数据
- 不可重复读：一个事务读到了另一个事务已经提交(update)的数据。引发事务中的多次查询结果不一致
- 虚读 /幻读：一个事务读到了另一个事务已经插入(insert)的数据。导致事务中多次查询的结果不一致
##
- read uncommitted 读未提交【RU】，一个事务读到另一个事务没有提交的数据
存在：3个问题（脏读、不可重复读、幻读）。
- read committed 读已提交【RC】，一个事务读到另一个事务已经提交的数据
存在：2个问题（不可重复读、幻读）。 解决：1个问题（脏读）
- repeatable read:可重复读【RR】，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交
解决：3个问题（脏读、不可重复读、幻读）
- serializable 串行化，同时只能执行一个事务，相当于事务中的单线程
解决：3个问题（脏读、不可重复读、幻读）
索引
概述
高效获取数据的数据结构。
基本需求
索引的数据结构，至少需要支持两种最常用的查询需求：
- 等值查询：根据某个值查找数据，比如： select * from t_user where age=76;
- 范围查询：根据某个范围区间查找数据，比如： select * from t_user where age>=76 and age<=86;
- 排序
- 分组
优势和劣势
优势
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
- 占据磁盘空间
- 降低更新表的效率
类型
单列索引
- 主键索引：索引列中的值必须是唯一的不允许有空值。
- 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
- 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
- 全文索引：只能在文本类型CHAR，VARCHAR，TEXT类型字段上创建全文索引。
组合索引
- 组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。
- 一般情况下，建议使用组合索引代替单列索引（主键索引除外）。
数据结构
Hash表
Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；
- 但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
- 数据结构比较稀疏，不适合做聚合，不适合做范围等查找。
使用场景：
- 对查询并发要求很高，K/V内存数据库，缓存
二叉查找树
- 二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。
- 二叉树的检索复杂度和树高相关：理想状态下效率可以达到O(logn)，极端情况下，二叉查找树会构建成为单向链表 = 查找全表扫描。对磁盘不友好（一旦变成了全表扫描，磁盘io将是极其沉重）
红黑树
红黑树是一个近似平衡的二叉树。
平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。
使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。
存在的问题：
- 时间复杂度和树高相关：树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作（瓶颈）。
- 平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率极差。
- 数据量大的情况下，索引存储空间占用巨大
B树
主要特点：
- B树的节点中存储着多个元素，每个内节点有多个分叉。
- 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
- 父节点当中的元素不会出现在子节点中。
- 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。
优点：
- 磁盘IO次数会大大减少。
- 比较是在内存中进行的，比较的耗时可以忽略不计。
- B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。
缺点：
- B树不支持范围查询的快速查找：如果我们想要查找15和26之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
- 空间占用较大：如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。
B+树
只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
优点：
- 继承了B树的优点（多叉树的优点）
- 保证等值和范围查询的快速查找
- MySQL的索引就采用了B+树的数据结构。
缺点：
- 维护成本高：对B+树进行插入、删除和更新操作的成本较高。由于B+树需要保持平衡，这些操作可能需要进行节点的分裂、合并、重新连接等操作，这会导致维护成本增加。
- 不适合随机访问和更新：尽管B+树适合范围查询和顺序访问，但对于随机访问和更新，其性能可能不如其他数据结构，比如哈希表。
InnoDB索引
简介
每个InnoDB表都有一个聚簇索引，也叫聚集索引。聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引。
主键索引
- 主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。
- InnoDB要求表必须有一个主键索引(MyISAM 可以没有)。
辅助索引
- 除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。
- 使用辅助索引需要检索两遍索引（回表）：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。
组合索引
组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。
创建原则：
- 频繁出现在where条件中的列，建议创建组合索引。
- 频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。例如order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的。
- 常出现在select语句中的列，也建议创建组合索引。
覆盖索引
select中列数据如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了查询需求，这时MySQL就不会回表查询，这中现象就是覆盖索引。
条件下推ICP
官方索引条件下推：Index Condition Pushdown，简称ICP。是MySQL5.6对使用索引从表中检索行的一种优化。
可以在索引遍历过程中，对where中包含的索引条件先做判断，只有满足条件才会回表查询读取行数据。
ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。可用于 InnoDB 和 MyISAM 表，对于InnoDB表ICP仅用于辅助索引。
创建原则
哪些情况需要创建索引
- 频繁出现在where条件字段，order排序，group by分组字段
- select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
- 多表join关联查询，on字段两边的字段都要创建索引
索引优化建议
- 表记录很少不需创建索引：索引是要有存储的开销
- 一个表的索引个数不能过多
- 频繁更新的字段不建议作为索引
- 区分度低的字段，不建议建索引
- 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
- 不建议用无序的值作为索引
- 尽量创建组合索引，而不是单列索引
索引失效分析
- 全值匹配我最爱
- 最左前缀匹配原则
- 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效，转而使用全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少使用select *
- 不等于（!= 或<>），索引会失效
- is null，is not null，索引会失效
- like以通配符开头，索引会失效
- 字符串不加单引号，索引会失效
- 少用or，用它来连接时，索引会失效
