MySql索引设计

---

title: MySql索引设计

索引设计提纲

• 索引概述
• 索引类型
• 高性能索引策略

索引概述

• 什么是索引
  索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。
  索引对于良好的性能非常关键。尤其当数据量越来越大的时候，索引对于性能愈发重要。不恰当的索引会导致随着数据量增大，性能急剧下降。

优点：
1.提高检索速度；
2.帮助服务器避免排序和临时表
3.将随机IO变为顺序IO
缺点：
1.创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；
2.索引需要占用物理空间；
3.增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了
综上可以总结出来
1.索引适用于多读少事务的表
2.数据唯一性低的表并不合适
索引并不是最好的工具，总的来说只有查询性能提高大于索引带来的额外开销的时候才有效。
对于特大型的表，可以建立一个元数据信息表，用来查询需要用到的某些特性。

索引类型

索引有很多种类型，不同的存储引擎支持和实现也可能不同。下面我们看下MySql支持的索引类型。

• B-Tree
  B-Tree通常意味着所有的值按顺序存储，并且叶子页到根的距离相同。下图展示了B-Tree索引的抽象表示。大致反映了InnoDB索引是如何工作的。
  
  B-Tree可以加快访问数据的速度，因为存储引擎不再需要进行全表扫描取而代之的是从索引的跟节点。
  可以使用B-Tree的查询类型
  1.全值匹配
  全值匹配指的是和索引中的所有列进行匹配
  2.匹配最左前缀
  即使用索引的第一列
  3.匹配前缀
  匹配某一列的值的开头某一部分
  4.匹配范围值
  字段范围内容值，或者精确某一列模糊匹配另外一列。
  上面的前提都是从索引最左列开始！如果不明白可以再看看B-Tree树结构图
• 哈希索引
  哈希索引是基于哈希表实现。只有精确匹配索引所有列的查询才有效。在MySql中只有Memory支持也是默认方式，不过mysql官方文档说是说InnoDB不支持哈希。
  其实InnoDB是可以使用的不过是自适应的，不能人为干预是否在一张表中哈希索引。
• 空间索引
  MyISAM支持空间索引，不过真没用过不敢多写。
• 全文索引
  全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其它几类索引的匹配方式完全不一样。

高性能索引策略

• 独立的列
  我们常看到查询不当的使用索引，或者使得MySql无法使用索引。如果查询中的列不是独立，则MySql就不会使用索引。
  “独立索引”是指索引列不能是表达式的一部分，也不能是函数的参数。
  如下面两句SQL则都是错误例子
  SELECT id FROM test WHERE id+1=5;
  SELECT … WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;
• 前缀索引
  有时候索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。
  但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值，索引的选择性越高查询效率越高。这里的诀窍就是选择合适的前缀长度以
  保证较高的选择性。为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的列表进行比较。其中还得考虑未来数据的变化性，不能仅仅只是
  靠查几条SQL就随便判断了。SQL如下所示
  ALTER TABLE demo ADD key(city(7));
• 多列索引
  很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
  一般由于人们听到一些专家诸如“把WHERE条件里面的列都建立上索引”这些模糊的建议导致。
  
  如上图在EXPLAIN中出现了索引合并策略，实际上出现这种情况多半说明索引建立的很糟糕。
  1.出现服务器对多个索引做相交操作的时候，通常意味着需要一个包含所有列相关的多列索引，而不是多个独立的单列索引。
  2.多个索引做联合操作的时候消耗大量的CPU和内存资源
  3.优化器不会把这些计算到查询成本，优化器只关心读取。使得查询的成本被低估，导致执行计划还不如直接走全表。
• B-Treee索引选择合适的索引顺序
  正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需求。
• 聚簇索引
  一般为InnoDB的主键，除此之外不能通过其它方式指定。如果没有主键则找一个非空唯一的列，如果没有这种列则会建立一个不可见的系统默认的主键row_id。
  优点
  1.可以把相关数据保存在一起。
  2.访问速度更快
  3.使用覆盖索引扫描的查询可以使用页节点中的主键值。
  缺点
  1.插入速度严重依赖于插入顺序
  2.更新聚簇索引的代价高，因为会强制每个被更新的行移动到新的位置。并且还会面临页分裂，当插入到某个已满的页中时。
  3.导致全表扫描变慢，当行比较稀疏或者页分裂导致数据存储不连续的情况。
• 覆盖索引
  MySql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。向这样包含满足查询需要的数据的索引称为覆盖索引。
  有时候写SQL的时候可以考虑先通过覆盖索引查出来数据再获取其它数据的方式，稍微提高性能。

欢迎扫码加入知识星球继续讨论