mysql索引

1. 索引类型

普通索引：最基本的索引，它没有任何限制，用于加速查询。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
联合（组合）索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

2. MySAM实现索引

• 介绍

  MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引。

如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

• 原理图

  这里设表一共有三列，假设我们以Col1为主键，则下图是一个MyISAM表的主索引（Primary key）示意。
  可以看出MyISAM的索引文件仅仅保存数据记录的地址。

3. InnoDB索引实现

注：虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

区别1：InnoDB的数据文件本身就是索引文件

1. 从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
2. 而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
3. 这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

说明：
　　可以看到叶节点包含了完整的数据记录，这种索引叫做聚集索引。
　　因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）
　 如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键
　　如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

区别2：InnoDB的辅助索引data域存储相应记录主键的值而不是地址

1. 换句话说，InnoDB的所有辅助索引都引用主键作为data域。
2. 这里以英文字符的ASCII码作为比较准则。
3. 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引
4. 首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

4. 索引越多越好吗？

索引并不是建立越多越好，把索引比作目录，相当于，一本100页的书，如果有50页目录，书本这么厚，实际内容就那么少，谁还会去翻看，若在面试中被问到，可以根据以下三点来回答：

1. 数据量小的表不需要建立索引,建立会增加额外的索引开销；
2. 数据变更需要维护索引,因此更多的索引意味着更多的维护成本；
3. 更多的索引意味着也需要更多的空间（索引也是需要空间来存放的）；

5. 建索引原则

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)的索引，那么只需要修改原来的索引即可