MySQL索引相关内容

        MySQL索引使用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree（B+树）索引。在MySQL中默认的存储格式就是以主键建立索引。

聚簇索引和非聚簇索引

根据MySQL数据库存储引擎不同，MySQL的实现方式是不同的。

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB:  其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引（这一过程称为回表，辅助索引又称二级索引,不同于MyISAM的查找过程）。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

四种索引分类

1. 普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
2. 唯一索引：与普通索引类型，不同的是唯⼀索引的列值必须唯⼀，但允许为空值。
3. 全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
4. 组合索引：将几个列作为⼀条索引进行检索，使用最左匹配原则。

建立索引的原则

• 最左前缀匹配原则。MySQL会⼀直向右匹配直到遇到范围查询（>, 3 AND d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引，则都可以用到，a,b,d的顺序可以任意调整。即在组合索引中直到某个索引失效前的索引都是可用的，如果单独使用的一列或几列在有效范围内，那么组合索引对这个查询也是有效的。
• 等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式。
• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少， 唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度是0。使用场景不同，这个值也很难确定，一般需要JOIN的字段要求在0.1以上，即平均1条扫描10条记录。
• 索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使⽤索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time =UNIX_TIMESTAMP('2016-06-06')。
• 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可
• 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择⼀个限制最为严格的索引。