mysql 索引

索引是帮助MySQL高效获取数据的数据结构。

1.索引类型

(1)B-tree索引

　　大部分MySQL引擎都支持这种索引。Archive是个例外：它直到MySQL5.1才支持索引，而且只支持索引单个AUTO_INCRMENT列。

　　MySQL的B树索引有B+树实现的。

　　能使用B-Tree的查询类型：匹配全名，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配一部分并且匹配某个范围中的另一部分，只访问索引的查询。

(2)哈希索引

　　建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。因为索引本身只保存简短的哈希值，哈希索引显得非常紧凑，哈希值的长度不依赖与索引的列，tinyint和大型字符列的哈希索引大小是一样的。

　　哈希索引的局限性有：哈希索引包含了哈希值和行指针，而不是值本身；MySQL的哈希索引不能排序；不支持键匹配；如果有很多哈希冲突，效率也会变低。

　　Memory存储引擎的默认索引是显式的哈希索引，InnoDB支持自适应哈希索引，NDB cluster支持唯一哈希索引。

(3)空间(R-Tree)索引

　　MyISAM支持空间索引。

(4)全文索引

　　MyISAM支持fulltext，MySQL 5.6.4的InnoDB开始支持fulltext。全文索引用于match against操作。

2.高性能索引策略

(1)隔离列

　　如果查询中没有隔离索引的列，MySQL通常是不会索引的。

　　比如下面的sql语句：

select actor_id from sakia.actor where actor_id+1=5 #不会索引
select ... where to_days(current_date)-to_days(date_col) <=10;#不会索引，因为用了to_days()函数
#下面这两种方法是可以的
select ... where date_col >= date_sub(current_date, interval 10 day);#current_date会阻止查询缓存
select ... where date_col >= date_sub('2015-01-13', interval 10 day)

(2)前缀索引

　　如果索引很长的字符列，它会使索引变大并且变慢。一种办法是模拟哈希索引，另一种是列前缀索引。

　　列前缀索引的选择率接近0.31,基本就可以了。

(3)聚集索引

　　聚集索引不是一种单独的索引方式，而是一种存储数据的方式。SolidDB和InnoDB支持聚集索引。

(4)覆盖索引

3.建索引几大原则

(1)最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
(2)=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
(3)尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
(4)索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
(5)尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可