MySQL索引

MyISAM 和 InnoDB 区别

• MyISAM引擎是 5.5版本之前的默认数据库引擎,InnoDB引擎是 5.5版本后的默认引擎
• MyISAM 只有表级锁,而 InnoDB 支持行级锁和表级锁,默认行级锁
• MyISAM 强调性能,每次查询具有原子性,执行速度比InnoDB快,但是不支持事务. InnoDB 支持事务,可以回滚和崩溃修复功能
• MyISAM不支持外键, InnoDB支持外键等
• 是否支持MVCC, 仅InnoDB支持
  • MVCC: 多版本并发控制, 指一种提高并发的技术. 读不加锁, 读写不冲突
• MyISAM 索引叶子节点的data存放的是数据记录的地址(非聚簇索引),而 InnoDB 其数据文件本身就是索引文件(聚簇索引)

为什么要用索引

• 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
• 可以大大加快数据的检索速度
• 帮助服务器避免排序和临时表 (临时表主要是在排序和分组过程中创建)
• 将随机IO变为有序IO (B+Tree 索引是有序的, 会将相邻的数据存储在一起)
• 可以加速表和表直接的连接

索引的数据结构

• 哈希索引
  • 底层数据结构就是哈希表,绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能快,其余大部分建议用B+Tree索引
• B+Tree 索引

B+Tree 原理

• 指平衡树
• 基于 B Tree 的叶子节点顺序访问指针进行实现, 具有 B Tree 的平衡性, 并且通过顺序访问指针来提高区间查询性能
• 在 B+Tree 中, 一个节点的key从左到右依次增大排列, 如果 key 左右相邻的分别为 key _i 和 key_i+1, 则key大于等于key_i, 小于等于 key_i+1
• 进行查找时, 首先在根节点进行二分查找, 找到key所在的指针, 然后地柜在指针所指节点进行查找, 查找到叶子节点后, 在叶子节点进行二分查找. 找到对应的数据
• 插入删除操作会迫害平衡树的平衡性. 所以需要对树进行分裂, 合并和旋转等操作来维护平衡性

B+Tree 和 B 树的区别

• B 树的所有叶子节点即存放键, 也存放值, 而 B+Tree 只有叶子节点存放键和值. 其他节点只存放键
• B 树的叶子节点都是独立的, B+Tree 的叶子节点有一条引用链指向相邻的叶子节点
• B 树的检索过程相当于范围内的每个节点的关键字做二分查找, 可能还没达到叶子节点, 检索就结束了. 而 B+Tree 的检索效率就很稳定, 任何查找都是从根节点到叶子节点的过程, 叶子节点的顺序检索很明显

B+Tree 和红黑树的比较

• B+Tree 有更低的树高
• 磁盘寻道的次数与树高成正比, 由于 B+Tree 树高更低, 所以 B+Tree 更适合磁盘数据读取
• 磁盘预读特性, 相邻的节点能够被预先载入

B+Tree 和 Hash 索引比较

• Hash 索引 定位快
• Hash 冲突问题
• Hash 索引不支持排序和范围查询, B+Tree 是有序的, 如在范围查询中, 直接遍历比 N 小的子节点就可以了, 而 Hash 索引要根据hash 来定位, 每一个都需要进行 hash 计算

为什么 MySQL 默认索引是 B+Tree

• 因为 B+Tree 可以不在需要进行全表扫描, 只需要对树进行搜索即可, 查询速度快
• 因为 B+Tree 的有序性, 除了用于查询, 还可以用于排序和分组
• 可以指定多个索引列, 多个索引列共同组成键
• 适用于全键值, 键值范围和键前缀查询, 其中键前缀查询只适用于最左前缀查询, 如果不是按照索引列的顺序查询, 无法使用索引

MySQL 实现 B+Tree 索引

MyISAM 实现 B+Tree

B+Tree 叶子节点的 data 域存放的是数据记录的地址, 在索引检索时, 首先按照 B+Tree 搜索算法搜索索引, 如果知道的 key 存在, 则取出器 data 域的值, 然后以 data 域的值为地址读取相应的数据记录, 被称为非聚簇索引

非聚簇索引优点

更新代价比聚簇索引药效, 叶子节点不存放数据

非聚簇索引缺点

1. 依赖于有序的数据
2. 可能会回表操作(二次查询), 当查询到索引对应的指针或主键后, 还需要根据主键再到数据文件或表中查询数据(当查询的字段未索引时, 可以不用回表操作)

InnoDB 实现 B+Tree

其数据文件本身就是索引文件, 相比 MyISAM, 索引文件和数据文件时分离的, 表数据文件本身就是按 B+Tree 组织的一个索引结构, 树的叶子节点 data 域保存了万总的数据记录, 这个所有的 key 是数据表的主键, 因此, InnoDB 表数据文件本身就是主索引, 被称为 聚簇索引

聚簇索引有点

查询速度非常快, 因为整个 B+Tree 本身就是一颗多叉平衡树, 叶子节点也是有序的, 定位到所有的节点也就相当于定位到了数据

聚簇索引缺点

1. 依赖于有序的数据
2. 更新代价大

索引缺点

1. 当对表中的数据进行增加, 删除和修改的时候, 索引也要动态的维护, 这样就降低了数据的维护速度
2. 索引需要占据物理空间, 出来数据表占据空间之外, 每个索引还要占据一定的物理空间, 如果建聚簇索引, 所需空间更大
3. 创建索引和维护索引需要耗费时间, 随着数据量的增加而增加

如何使用索引

1. 在经常需要搜索的列上创建索引, 可以加快搜索速度
2. 在进出使用 WHERE 子句中的列上创建索引, 可以加快条件的判断速度
3. 在经常需要排序的裂伤创建索引, 因为索引已经排序, 加快排序查询时间
4. 对中到大型表索引都是非常有效的, 但在特大型表中维护开销非常大, 不适合简历索引
5. 在经常用到连接的列上, 可以加快连接速度
6. 避免在 WHERE 语句中对字段使用函数或者表达式
7. 在使用 InnoDB 时使用与业务无关的自增作为主键, 不要使用业务主键
8. 建议将列设置为 not null, 因为 null 需要更多存储空间且 null值无法参与某些计算
9. 删除长期未使用的索引, 减少出查询操作外的其他操作不必要的性能损耗
10. 在时用 limit offset 查询缓存慢时, 可以借助索引来提高性能
11. 最左前缀原则, 创建联合索引时, 尽量吧查询最频繁的字段作为第一个字段, 查询的时候也尽量以这个字段未第一条件

覆盖索引

一个索引包含或者覆盖所有需要查询的字段的值
覆盖所有需要查询的字段正好是索引的字段, 那么直接根据该索引就能查询到数据, 无需回表操作

查询性能优化

1. 使用 Explain 分析
   a. select_type: 查询类型, 简单查询, 联合查询, 子查询
   b. type : ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好, ALL 代表这条 SQL 语句全表扫描了，需要优化。一般来说需要达到range 级别及以上。）
   b. key: 使用的索引
   c. rows: 扫描的行数
2. 减少请求的数据量
   a. 只返回必要的列, 尽量不使用 SELECT * FROM
   b. 只返回必要的行, 使用 LIMIT 限制返回数据
   c 缓存重复查询的数据
3. 使用覆盖索引查询
4. 切分大查询, 分解成对每个表进行一次单表查询, 然后在程序中关联, 如果一次执行, 会锁住很多数据, 占满书屋日志, 耗尽资源, 阻塞其他查询
   a. 可以让缓存更高效, 对于连接查询, 如果其中一个表数据发生变化, 那么整个查询就无法继续使用, 分解后的, 对其他表的查询缓存任然可以使用
   b. 单表查询缓存结果可以别其他查询使用到
   c. 较少锁竞争
   d. 更容易对数据库拆分