Mysql要点记录--索引

MyIsam和InnoDB默认索引都是B-tree索引。

只有MyIsam存储引擎支持全文本索引。

创建索引：create index cityname on city（city（10））；

索引设计的原则：

①：最适合索引的列是出现在where中的列。

②：使用短索引。

③：索引的列的选择性越高越好。我的理解是作为索引中的列，列内数据的区分度一定要大，。

④：InnoDB自己指定主键，当表中有多个可以指定为主键的列的时候，选择最常访问的作为索引。

 

索引可以包含一个或者多个列的值，因为索引只能使用最左前缀，所以列的顺序很重要。

索引在存储层而非服务器层所以不同引擎存储引擎的索引的工作方式不太一样。

MyIsam通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B--Tree索引：基于B-Tree存储的数据表。

B--tree对索引列是顺序组织存储的，所以很适合查找范围数据。

B--tree适用于全键值、键值范围或键前缀查找，键前缀查找只适用于根据最左前缀的查找。

B-Tree索引的限制：

①：如果不是从索引的最左列开始查找，则无法使用索引。

②：不能跳过索引中的列。

③：若查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

HASH索引：哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。因为哈希索引自身只存储对应的哈希值和行指针，所以索引的结构十分紧凑，因此哈希查询速度非常快。‘

hash索引的缺点：

①：哈希索引只包含哈希值和行指针，因此必须读取行。

②：哈希索引并不是按索引值顺序存储的，因此无法排序。

③：哈希索引不支持部分索引列匹配查找，因为哈希索引是利用索引列的全部内容来计算哈希值的。

④：哈希索引只支持等值比较查询。

⑤：哈希冲突多的话，索引维护代价很高。

如何选择合适的前缀长度？

：计算完整列的选择性，然后计算各前缀长度，选择接近完整列选择性的前缀长度。

：查找出现次数最多的列，然后分别以不同的前缀长度计算出现次数，选择接近的前缀长度。

前缀索引无法实现ORDER BY和GROUP BY，也无法实现覆盖扫描。

选择合适的索引顺序：

经验上一般将选择性最高的索引列放在索引的最前面，但有时候也将那些运行频率最高的查询来调整索引列的顺序。