MySQL数据库总结

索引

索引概念

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优势与劣势

优势

1. 提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势

1. 索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的
2. 虽然索引大大提高了查询效率，在insert、update、delete操作时，MySQL不仅要保存数据，还要保存索引文件每次改动列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引结构

索引是在MySQL中的存储引擎层中实现的，不是服务器层。所以每种存储引擎的索引实现方式或有不同。目前提供了以下4中索引：

• B-TREE 索引：最常见
• HASH 索引：只有Memory引擎支持，使用场景简单
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引

注：

• 聚类索引、复合索引、前缀索引、唯一索引默认都是使用B+tree索引，统称为 索引
• InnoDB是通过B+TREE结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6字节的row_id来作为主键
• InnoDB和MyISAM的区别：
  1、innodb支持事务，myisam不支持
  2、innodb支持外键，myisam不支持
  3、innodb支持表锁和行锁，myisam只支持表锁
  4、innodb在5.6之后支持全文索引，myisam一直支持
  5、innodb索引的叶子节点直接存放数据，而myisam存放地址

B-TREE索引

为什么

从局部性原理与磁盘预读原理来了解索引机制

文件结构

.frm ：存储结构
.ibd ： 存储索引和数据

B-TREE结构

B-TREE 又叫多路平衡搜索树，一颗 m 叉的 B-TREE 特性如下：

• 树中每一个节点最多包含 m 个子节点
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)]个子节点
• 若根节点不是叶子节点，则至少有两个子节点
• 所有的叶子节点都在同一层
• 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中 [ceil(m/2)-1] <= n <= m-1

例：
C N G A H E K Q M F W L T Z D P R X Y S

B-TREE和二叉树相比，查询数据的效率更高，因为对于相同的数量来说，B-TREE的层级结构比二叉树小，因此搜索速度块。

B+TREE 结构

B+TREE 和 B-TREE 的区别：

1. n 叉 B+TREE 最多包含由 n 个 key，而 B-TREE 最多含有 n-1 个 key
2. B+TREE 的叶子节点保存所有的 key 信息，依 key 大小顺序排列
3. 所有的非叶子节点都可以看作是key的索引部分
   
   MySQL索引数据结构对经典的B+TREE进行了优化，在原B+TREE的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+TREE，提高区间访问的性能。

MySQL中的B+TREE索引结构示意图：

索引分类

1. 主键索引：按照主键创建的索引
2. 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引
3. 唯一索引：索引列的值必须是唯一的，但允许由空值
4. 组合索引：即一个索引包含多个列
5. 全文索引：

索引匹配方式

• 全值匹配：和索引中的所有列进行匹配
• 匹配最左前缀：只会匹配前面的几列
• 匹配列前缀：可以匹配某一列值得开头部分
• 匹配范围值：可以查找某一个范围得数据
• 精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分
• 之访问索引的查询：覆盖索引

优化小细节

• 当使用索引列进行查询的时候尽量不要使用表达式，吧计算放到业务层而不是数据库层
• 尽量使用主键查询，而不是其他所有，因此主键查询不会触发回表
• 使用前缀索引
• 使用索引扫描来排序
• union all，in，or都能够使用索引，但是推荐使用in
• 范围列可以用到索引（但是范围列后的列无法用到索引，索引最多用于一个范围列）
• 强制类型转换会全表扫描
• 更新十分频繁，数据区分度不高的字段上不宜建立索引
• 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
• 能使用limit的时候尽量使用limit
• 单表索引建议控制在5个以内
• 单索引字段数不允许超过5个（组合索引）
• 创建索引的时候应该避免以下错误概念（索引越多越好；过早优化，在不了解系统的情况下进行优化）

补充

• 前缀索引：文字比较多时，截取一部分作为索引，这样大大的节省索引空间，从而提高索引效率，但这会降低索引的算则行。

-- 增加demo表的idx_text索引，截取text列的前三个字符
alter table demo add index idx_text(text(3));

• 如果是单机，推荐采用自增主键，减少页合并和分裂操作，分布式不建议
• 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表
• 在通过非主键索引查询时，查询不仅含主键的字段会触发回表，如果仅查询主键不会触发，这叫做索引覆盖
• 复合索引优化必须满足最左匹配
• 直接根据复合索引获取数据再返回给server，不在server层做数据筛选，从而较少I/O操作，这叫做索引下推
• MMR，mult_range read
• FIC，fast index create

HASH索引

文件结构

.frm ：存储结构
.MYD ： 存储数据
.MYI ： 存储索引

缺点

1. 利用hash存储需要将所有的数据文件添加到内存，比较耗费内存空间
2. 如果所有的查询都是等值查询，那么hash确实很快，但是在实际工作环境中范围查找的数据更多，因此hash就不太适合了

事务

MySQL数据库事务及其原理

特性

• 原子性 -> undo log -> 保存的是跟执行操作相反的操作
• 一致性 ->
• 隔离性 -> 锁 ->
  • 读未提交
  • 读已提交
  • 可重复读
  • 串行化
• 持久性 -> redo log -> crash safe