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 和 keyi+1, 则key大于等于keyi, 小于等于 keyi+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 域的值为地址读取相应的数据记录, 被称为非聚簇索引
非聚簇索引优点
更新代价比聚簇索引药效, 叶子节点不存放数据
非聚簇索引缺点
- 依赖于有序的数据
- 可能会回表操作(二次查询), 当查询到索引对应的指针或主键后, 还需要根据主键再到数据文件或表中查询数据(当查询的字段未索引时, 可以不用回表操作)
InnoDB 实现 B+Tree
其数据文件本身就是索引文件, 相比 MyISAM, 索引文件和数据文件时分离的, 表数据文件本身就是按 B+Tree 组织的一个索引结构, 树的叶子节点 data 域保存了万总的数据记录, 这个所有的 key 是数据表的主键, 因此, InnoDB 表数据文件本身就是主索引, 被称为 聚簇索引
聚簇索引有点
查询速度非常快, 因为整个 B+Tree 本身就是一颗多叉平衡树, 叶子节点也是有序的, 定位到所有的节点也就相当于定位到了数据
聚簇索引缺点
- 依赖于有序的数据
- 更新代价大
索引缺点
- 当对表中的数据进行增加, 删除和修改的时候, 索引也要动态的维护, 这样就降低了数据的维护速度
- 索引需要占据物理空间, 出来数据表占据空间之外, 每个索引还要占据一定的物理空间, 如果建聚簇索引, 所需空间更大
- 创建索引和维护索引需要耗费时间, 随着数据量的增加而增加
如何使用索引
- 在经常需要搜索的列上创建索引, 可以加快搜索速度
- 在进出使用 WHERE 子句中的列上创建索引, 可以加快条件的判断速度
- 在经常需要排序的裂伤创建索引, 因为索引已经排序, 加快排序查询时间
- 对中到大型表索引都是非常有效的, 但在特大型表中维护开销非常大, 不适合简历索引
- 在经常用到连接的列上, 可以加快连接速度
- 避免在 WHERE 语句中对字段使用函数或者表达式
- 在使用 InnoDB 时使用与业务无关的自增作为主键, 不要使用业务主键
- 建议将列设置为 not null, 因为 null 需要更多存储空间且 null值无法参与某些计算
- 删除长期未使用的索引, 减少出查询操作外的其他操作不必要的性能损耗
- 在时用 limit offset 查询缓存慢时, 可以借助索引来提高性能
- 最左前缀原则, 创建联合索引时, 尽量吧查询最频繁的字段作为第一个字段, 查询的时候也尽量以这个字段未第一条件
覆盖索引
一个索引包含或者覆盖所有需要查询的字段的值
覆盖所有需要查询的字段正好是索引的字段, 那么直接根据该索引就能查询到数据, 无需回表操作
查询性能优化
- 使用 Explain 分析
a. select_type: 查询类型, 简单查询, 联合查询, 子查询
b. type : ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好, ALL 代表这条 SQL 语句全表扫描了,需要优化。一般来说需要达到range 级别及以上。)
b. key: 使用的索引
c. rows: 扫描的行数 - 减少请求的数据量
a. 只返回必要的列, 尽量不使用 SELECT * FROM
b. 只返回必要的行, 使用 LIMIT 限制返回数据
c 缓存重复查询的数据 - 使用覆盖索引查询
- 切分大查询, 分解成对每个表进行一次单表查询, 然后在程序中关联, 如果一次执行, 会锁住很多数据, 占满书屋日志, 耗尽资源, 阻塞其他查询
a. 可以让缓存更高效, 对于连接查询, 如果其中一个表数据发生变化, 那么整个查询就无法继续使用, 分解后的, 对其他表的查询缓存任然可以使用
b. 单表查询缓存结果可以别其他查询使用到
c. 较少锁竞争
d. 更容易对数据库拆分