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