mysql分布式思维（五）- 索引优化

 1.索引的利弊

利

• 通过索引列查询数据，能够提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

弊

• 假设表a 其中有列 column ca  给其创建索引indx_a_ca
  •  每次更新ca的操作，都会调整因为更新所带来的键值变化后的索引信息
  • 这样就会增加IO损耗，索引列也是要占用空间的，a列数据的增多，
  • indx_a_ca索引占用的空间也会不断增长。所以索引还会带来存储空间资源的消耗。

2.mysql的索引分类

•  B-Tree索引
•  Hash索引
  • hash索引只能满足"="、"in" <>查询，不能支持范围查询
  • hash索引无法被利用进行排序操作
  • hash索引不能利用部分索引键查询
  • hash索引不能避免表扫描
• full-text索引
  • 只有myisam存储引擎支持--->只有char 、varchar、text支持
• R-Tree索引
  • 主要解决空间数据检索问题，极少使用

3.如何判断是否需要创建索引

• 频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引
  • 该字段可能重复成千上万
  • 即使你创建了索引优化器模块是不会选择使用的
  • 会有极大的性能问题  有很多重复值，会带来大量的随机IO甚至是重复IO
• 更新非常频繁的字段不适合创建索引
  • 不仅仅更新表中的数据，还需要更新索引数据  IO访问增大
• 不会出现在where字句中的字段不该创建索引
• 单键索引还是组合索引

 

 4.mysql中索引的限制（下面的原理在oracle当中基本都成立)

 

• 是否用到了索引可以查看执行计划
• 在任何索引列上做计算、函数、类型转换（哪怕是自动的）都会使得索引失效而转向全表扫描操作
  • 不要在索引列上做任何操作因为可能为导致索引失效
• mysql 在使用不等于(!= or  <>)的时候无法使用索引会导致全表扫描
• is null ,is not null 也无法使用索引
• join 语句中join条件字段类型不一致的时候mysql无法使用索引
• 模糊查询的时候(like操作)如果以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
• 如果使用的是hash索引，在做非等值连接时候无法使用索引，会是全表扫描的操作
  • （select * from t where t>5）效率低于（select * from t where t>=6）
• 在mysql中BLOB和Text类型的列只能创建前缀索引
•  MyISAM存储引擎的话索引键长度总和不能超过1000字节
• 当有唯一性索引和非唯一性索引都存在时，往往只会选择唯一性索引
• 组合索引，查询时组合索引第一列出现的时候会使用索引

注意：大家可以通过查看执行计划，来验证上面的结论

 

  5.使用索引的一些建议

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。减少通过使用Hint认为控制索引的选择，如果使用Hint会使得后期维护成本比较高