MySQL 支持的索引类型
B-tree 索引的特点
B-tree 索引以 B + 树的结构存储数据
B-tree 索引能够加快数据的查询速度
B-tree 索引更适合进行行范围查找
B-tree 结构图
在什么情况下可以用到 B 树索引?
1 全值匹配的查询
例如我们有个订单号, 订单号为 order_sn='98764322119900'
2 匹配最左前缀的查询
3 匹配列前缀查询
例如: order_sn like '9876%'
4 匹配范围值的查找
例如: order_sn > '98764322119900' and order_sn < '98764322119999'
5 精确匹配左前列并范围匹配另外一列
6 只访问索引的查询
Btree 索引的使用限制
如果不是按着索引最左列开始查找, 则无法使用索引
使用索引时不能跳过索引中的列
Not in 和 <> 操作无法使用索引
如果查询中有某个列的查询范围, 则其右边所有列都无法使用索引
Hash 索引的特点
Hash 索引是基于 Hash 表实现的, 只有查询条件精确匹配 Hash 索引中的所有列时, 才能够使用到 hash 索引
对于 Hash 索引中的所有列, 存储引擎都会为每一行计算一个 Hash 码, Hash 索引中存储的就是 Hash 码
Hash 索引的限制
使用 hash 索引需要二次查找
Hash 索引无法用于排序
Hash 索引不支持部分索引查找也不支持范围查找
为什么要使用索引?
索引大大减少了存储引擎需要扫描的数据量
索引可以帮助我们进行排序以比喵使用临时表的 IO 消耗, 提升 MySQL 的能力
索引可以把随机 I/O 变为顺序 I/O
索引是不是越多越好?
场景: 开发人员要求来创建更多的索引, 问: 为何要添加更多的索引?, 答: 因为导入速度的时候很慢, 以此来加快导入的速度!!!,so.. 开发人员对索引的理解不是那么的透彻, 虽然索引能够为查找带来速度上的提升, 但是也会对性能有一些损失, 衡量标准, 当创建索引带来的好处多过于消耗的时候, 才是最优的选择~
1 索引会增加写操作的成本
2 太多的索引会增加查询优化器的选择时间
索引的优化策略
索引列上不能使用表达式或函数
上图中的第一个 select 语句中使用表达式, 因此不满足创建索引的要求, 改为第二个 select 语句即可
前缀索引和索引列的选择性
联合索引
如何选择索引列的顺序
1 经常会被使用到的列优先
2 选择性高的列优先
覆盖索引
优点:
1 可以优化缓存, 减少磁盘 IO 操作
2 可以减少随机 IO, 使随机 IO 操作变为顺序 IO 操作
3 可以避免对 Innodb 主键索引的二次查询
4 可以避免 MyISAM 表进行系统调用
无法使用覆盖索引的情况
1 存储引擎不支持覆盖索引
2 查询中使用了太多的列
3 使用了双 % 号的 like 查询
使用索引优化查询
使用索引扫描来优化排序
通过排序操作
按着索引顺序扫描数据
注意事项:
1 索引的列顺序和 order by 子句的顺序完全一致
2 索引中所有列的方向 (升序, 降序) 和 order by 子句完全一样
3order by 中的字段全部在关联表中的第一张表中
利用索引优化
1 索引可以减少锁定的行数
2 索引以加快处理速度, 同事也加快了锁的释放
索引的维护和优化
删除重复复制和容易的索引
更新索引统计信息及减少索引碎片
analyze table table_name
未完待续, MySQL SQL 语句的优化查询请等待下篇文章
来源: https://www.cnblogs.com/demon89/p/8507149.html