mysql索引-Btree索引-hash索引-使用场景

B-tree索引特点:

按定义索引时列的顺序排序

myisam中:存储行物理位置,使用前缀压缩技术使得索引更小;

innodb中:存储主键值,使用B+Tree索引;

  • 加快数据的查询速度
  • 适合范围查找

使用场景:

  • 全值匹配
  • 匹配最左前缀
  • 列前缀
  • 范围值
  • 精确匹配左前列并范围匹配另一列
  • 只访问索引的查询
  • order by

btree限制:

  • 不是最左列开始查找则无法使用
  • 不能跳过索引列
  • not in <>无法使用
  • 某个列使用了范围查找则其右边的所有列无法使用索引

hash索引:(

memory引擎默认索引类型,支持非唯一哈希索引,哈希冲突采用链式法

InnoDB自适应hash索引,基于b-tree之上建立,可关闭

伪哈希索引,将较长的字段通过自定义哈希函数(CRC32、Md5等)得到长度固定的另一列做为索引列

  • hash索引包含hash值和行指针,结构紧凑,速度快
  • 基于hash表实现 对所有索引列计算一个hash码,哈希索引将所有哈希码存储在索引中,同时在哈希表保存指向每个数据行的指针
  • 只有精确匹配hash索引中所有列才能使用到
  • 无法排序 不支持部分索引和范围查找 存在hash冲突,只支持等值查找:=、in()、<=>

索引优缺点:

  • 减少扫描数据量
  • 帮助排序避免使用临时表
  • 把随机io转为顺序io
  • 增加写成本
  • 索引太多 增加了查询优化器的选择时间

索引限制:

  • 查询时索引(左)列上不能使用表达式或函数
  • 索引长度限制: 使用前缀索引 (选择性要高)

小表不需要索引,中到大型非常有效,特大型-分区,数量特多-建立元数据信息表

IGNORE INDEX

联合索引:

   ORDER BY\GROUP BY \DISTINCT

  • 常用列优先
  • 选择性高优先
  • 宽度小的列优先

覆盖索引:(包含所查询的所有列)

  • 优化缓存 减少io
  • 随机io转顺序io
  • 避免对innodb主键索引的二次查询
  • 避免myisam表进行系统调用

覆盖索引限制:

  • 覆盖索引需要存储索引列值,所有只能使用btree索引
  • 无法使用:不支持覆盖索引(memory)
  • 查询中太多列无法覆盖
  • 双百分号无法使用索引

使用索引优化排序:(type=index)   explain分析查询语句

需满足要求:

  • 索引列顺序和orderby顺序及升降序需完全一致
  • orderby的字段全部在关联表中的第一张表中

使用二级索引优化排序 联合索引 顺序 升降一致 左前列不能使用范围查找

模拟hash索引:(innodb不能人为建立hash索引,若能认为能优化效率则采用自适应hash索引)

  • 使用md5对长列加密 再对加密列建立索引
  • 全值匹配 有hash冲突(采用查询两列)

 

减少锁定行(for update加排他锁模拟)

加快处理速度 锁释放速度

查看冗余索引: pt-duplicate-key-checker h=127.0.0.1
查看索引:performance_scheme   information_scheme
分析:analyze table table_name
碎片:optimize table table_name

  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值