Mysql索引说明

关于mysql索引的说明,比较散的说明。

  1. 索引的好处,除了提高检索效率之外:另外是有利于排序&分组

索引最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率,加快检索时间,降低检索过程中所需要读取的数据量。索引还有一个非常重要的用途,那就是降低数据的排序成本。每个索引中索引数据都是按照索引键键值进行排序后存放的,所以,当我们的Query 语句中包含排序分组操作的时候,如果我们的排序字段和索引键字段刚好一致,MySQL Query Optimizer就会告诉mysqld 在取得数据之后不用排序了,因为根据索引取得的数据已经是满足客户的排序要求。

那如果是分组操作呢?分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的,所以当我们的Query 语句中包含分组操作,而且分组字段也刚好和索引键字段一致,那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。

排序分组操作主要消耗的是我们的内存和CPU 资源,如果我们能够在进行排序分组操作中利用好索引,将会极大的降低CPU 资源的消耗。

? ?2. 唯一性太差的字段不适合单独创建索引。

如果一个键值对应了太多的数据记录,也就是说通过该键值会返回占整个表比例很大的记录的时候,由于根据索引扫描产生的都是随机IO,其效率比进行全表扫描的顺序IO 的效率要差很多,即使不会出现重复IO 的读取,同样会造成整体IO 性能的下降。很多比较有经验的Query 调优专家经常说,当一条Query 所返回的数据超过了全表的15% 的时候,就不应该再使用索引扫描来完成这个Query 了。对于“15%”这个数字我们并不能判定是否很准确,但是之少侧面证明了唯一性太差的字段并不适合创建索引。

3. 创建多个单独的索引的情况讨论

在这样的情况下,MySQL Query Optimizer 大多数时候都只会选择其中的一个索引,然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通过INDEX_MERGE 来优化查询,可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选择通过INDEX_MERGE 来优化查询,就需要访问多个索引,同时还要将通过访问到的几个索引进行merge操作,所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。

?4. 组合索引

组合索引的弊端是比单个索引更容易被更新,因为涉及的列多了。但是如果不能有某个单个索引能过滤出相当大部分(90%)以上的结果集,推荐使用组合索引。

?5.?Query 的索引选择

在有些场景下,Query 由于存在多个过滤条件,而这多个过滤条件可能会存在于两个或者更多的索引中。在这种场景下,MySQL Query Optimizer 一般情况下都能够根据系统的统计信息选择出一个针对该Query 最优的索引完成查询,但是在有些情况下,可能是由于我们的系统统计信息的不够准确完整,也可能是MySQL Query Optimizer 自身功能的缺陷,会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。在这种时候,我们就不得不通过认为干预,在Query 中增加Hint 提示MySQL Query Optimizer 告诉他该使用哪个索引而不该使用哪个索引,或者通过调整查询条件来达到相同的目的。

6. Mysql索引选择参考

  • ?对于单键索引,尽量选择针对当前Query 过滤性更好的索引;
  • ?在选择组合索引的时候,当前Query 中过滤性最好的字段在索引字段顺序中排列越靠前越好;
  • ?在选择组合索引的时候,尽量选择可以能够包含当前Query 的WHERE 子句中更多字段的索引;
  • ?尽可能通过分析统计信息和调整Query 的写法来达到选择合适索引的目的而减少通过使用Hint 人为控制索引的选择,因为这会使后期的维护成本增加,同时增加维护所带来的潜在风险。

?7.?Myslq索引限制说明.

其实只有前四条是mysql特征的需要注意。?

  • ?MyISAM 存储引擎索引键长度总和不能超过1000 字节;
  • ?BLOB 和TEXT 类型的列只能创建前缀索引;
  • MySQL 目前不支持函数索引;
  • 使用非等值查询的时候MySQL 无法使用Hash 索引;
  • 使用不等于(!= 或者<>)的时候MySQL 无法使用索引;
  • 过滤字段使用了函数运算后(如abs(column)),MySQL 无法使用索引;
  • Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引;
  • 使用LIKE 操作的时候如果条件以通配符开始( ‘%abc…’)MySQL 无法使用索引;

参考:

《MySQL性能调优与架构设计》

原创文章。为了维护文章的版本一致、最新、可追溯,转载请注明: 转载自idouba

本文链接地址: Mysql索引说明


, ,

No comments yet.

发表评论