SQL索引

存储方式索引的类型

B-TREE索引：

就是创建B树（在InooDB里是B+树），查询不再全表查询，而是去查B树的根节点，再一层一层往下查到叶子节点。子节点的指针是指向下一层，而叶子节点指向的是被索引的数据。
Inoodb引擎中B+树索引并不能找到具体键值的索引行，而是找到数据所在页，然后再将页读到内存中查找，找到所对应的数据。

———《MySql技术内幕Innodb存储引擎》

有效范围

index(a,b,c) 按a排序，再按b排序，再按c排序

所以按顺序来才能找到，a精确按顺序查，b才可以精确按顺序查，c才可以按顺序查。当a按范围查，b就无法排序，所以就不走所以。

a精确查，跳过b查c，b不是精确的，所以也无法走所以。所以a依赖于b依赖于c。

推荐利用索引的有序性

  1. where a=? and b = ? order by c

全值匹配，

  1. select * from t where a=? and b=? c=?
  2. --等价
  3. select * from t where c=? and b=? a=?

最左前缀（多列索引的第一列）

  1. select * from t where a=? -- yes
  3. select * from t where c=? -- no
  5. select * from t where a=? and c=? -- no

列前缀（某一列值的开头），

  1. select * from t where a like 'x%' -- yes
  2. select * from t where b like 'x%' -- no
  3. select * from t where a like '%x' -- no

匹配范围值（包括精确一列到范围其他列），只访问索引的查询（覆盖索引，只访问索引)

  1. select * from t where a between '' and ''; -- yes
  2. select * from t where b between '' and ''; -- no

全值匹配 + 范围匹配

  1. select * from t where a = ''and b between '' and '' -- yes
  2. select * from t where b = ''and c between '' and '' 
  3. -- no
  4. select * from t where a between '' and '' and b = ''
  5. -- no

可以利用它来排序（order by ， group by）

限制：

多列索引从左到右，无法跳过中间某列而查找以后的列。

如果具有范围查询（比如LIKE）那么右边的列就无法使用索引。如果范围有限可以用多个等于号以至于能用上索引。

索引选择性

  1. count(distinct index_value) / count(*) --此值越高越好

对于varchar类型的字段，太过冗长会对索引的查询有影响。

可以这样计算

  1. count(distinct left(varchar_index_value,x)) / count(*)

当该值接近于0.031即可用。前缀索引是能使索引更小，更快的有效办法。但MYSQL无法使用前缀索引进行ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

哈希索引：

只有精确匹配索引的所有列才有效。对所有的索引列计算一个hash码。hash索引把所有的hash码和指针存储在索引中。

只有Memoriy引擎表显式支持hash索引。同时使用链表存储相同hash冲突的数据。

InooDB具有可关闭的“自适应hash索引”功能，在意识到频繁使用的索引时，会为其创建hash索引方便查找，此功能对于用户是透明的。

对于很长的存储字段（比如url）可以手动创建url_crc列用于创建CRC32hash码（比MD5

SHA1长度小且精简方便查询），利用此伪hash索引来提升查询速度。不过维护数据的hash码会有些困扰，手动创建或者用触发器。

空间索引

MYisAM引擎从所有维度创建索引并有效的从任意维度来组合查询。

全文索引

用于查找文中关键词。

索引的好处

  1. 大大减少MYSQL服务器需要查询的数据量
  2. 帮助服务器避免排序和临时表
  3. 索引可以将随机I/O变为顺序I/O

索引的 ”三星“ 系统：

  1. 将相关列放在一起（一星）
  2. 索引数据顺序和排列顺序一致（二星）
  3. 索引的列包含了查询中所有的列（三星）

索引适用于：中大型表适合用索引。小型表适合全表查询。巨大型需要用到分区技术。

索引的优化

单列索引并不比多列索引好，甚至会触发MYSQ自动索引合并（MYSQL5.0及以上），这样并不好，优化器带来了更多的开销，应该检查索引是否满足需求，或者建立多列索引。

选择合适的索引列顺序，当不需要排列和分组时，把选择性最高的列排在最前面通常很好，只作用于优化where条件。也可以在最频繁调用的查询语句中，根据索引列的行的数量来决定，通常把索引列数据最少的放在前面（非常依赖于具体的值）。主要时where子句中排序，分组和范围条件等其他因素。

数据分布分类型

聚簇索引

聚簇索引包含了所有的数据，通常主键索引就是聚簇索引。

聚簇就是把key和数据紧凑地放在一起。

优点：

  1. 可以把相关数据聚集在一起
  2. 数据访问更快
  3. 覆盖索引扫描可以直接使用叶节点的主键值

缺点：

非顺序插入，更新聚簇索引列的代价比较大。二级索引开销大，先查主键，再根据主键去查聚簇索引（自适应哈希能够优化）。更新不当导致页分裂。

二级索引

该索引包含了一个数据的id，后面通过id再进行查询。叶子节点包含了索引的值和主键。很有可能会再去查聚集索引。

回表情况：覆盖索引

只有select、where中出现的列，被索引覆盖的情况才是覆盖索引，此时Extra会显示Using index，这个索引就是覆盖索引

index(a,b,c)

SELECT a,b,c FROM xx WHERE a = ?, b = ?

可以直接走索引，不用回表。

覆盖索引可以不走二次查询的原因是，再InnoDB引擎中，二级索引的在叶子节点中包含了主键id，如果二级主键能够覆盖查询，就可以避免对主键索引的二次查询。(就是二级索引里包含了你想查的列的数据，当然不用再去查聚簇索引了。)