MySql索引

InnoDB存储引擎支持的常见的索引有：B+树索引，全文索引和哈希索引。

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

B+树索引的构造类似于二叉树，根据键值快速查找数据。B+树中的B不是代表二叉树（binary），而是代表平衡（balance），因为B+树是从最早的平衡二叉树演变而来的，但是B+树不是一个二叉树。B+树索引并不能找到一个给定的键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B+树

B+树索引是最为常见，也是在数据库中使用最为频繁的一种索引。B+树是通过二叉查找树，再由平衡二叉树，再到B树演化而来。但是在实际的使用中已经没有使用B树的情况了。注：B树是多叉平衡查找树，也称为B-树。

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，B+树的插入删除操作必须保证插入删除后叶子节点中的记录依然有序。

B+树详解

如上图，是一颗B+树，浅蓝色的块是一个磁盘块，每个磁盘快包含几个数据项和指针，如磁盘快1包含数据项17和35，包含指针P1、P2、P3，P1指向小于17的磁盘块，P2指向17到35的磁盘块，P3指向大于35的磁盘块。真实的数据存放到叶子节点上，非叶子节点不存放真实的数据，只存储指引搜索方向的数据项，如17,35并不真实存在于数据项中。

B+树查找过程

如图所示，如果要查找数据项29的，那么首先会把磁盘块1由磁盘加载到内存中，此时发生1次IO操作，在内存中用二分查找确定29在17和35之前，锁定磁盘块1的P2指针，通过磁盘块的1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存中，发生第二次的IO操作，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时在内存中做二分查找找到29，结束查询，总计3次IO操作。实际上，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要3次IO操作，性能的提高将是非常巨大的，如果没有索引，则每个数据项都要发生一次IO操作，那么共有百万次的IO，显然成本是非常巨大的。

B+树索引

B+树索引的本质就是B+树在数据库中的实现。但是B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2-4层，这也就是说查找某一键值的行记录时最多只需要2到4次IO。数据库中的B+树索引可以分为聚集索引和辅助索引，但是不管是聚集还是辅助索引，其内部实现都是B+树，即高度平衡的，叶子节点上存放着所有的数据，聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

B+树索引最左匹配原则

当B+树的数据项是复合的数据结构，比如（name,age,sex）的时候，B+树是按照从左到右的顺序来建立搜索树的，比如当（张三，20，F）这样的数据来做检索的时候，B+树会优先比较name来确定下一步的检索方向，如果name相同再去比较age和sex，最后得到检索的数据。但当（20，F）这样没有name的数据来做检索的时候，B+树就不知道下一步去检索哪个节点，此时将不走索引。当（张三，F）这样的数据做检索时，B+树可以使用name来指定搜索的方向，但下一个字段age的缺失，只能把name等于张三的数据找到，然后再去匹配性别是F的数据。这个就是索引的最左匹配特性。

索引建立的原则

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，一般要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

自适应哈希索引

自适应哈希索引使用哈希表的实现方式，经哈希函数映射到一个哈希表中，因此查找速度是非常快的，在一般情况下的查找速度是O(1)。哈希索引是数据库自身创建并使用的，DBA本身并不能对其进行干预。需要注意的是，哈希索引只能用来搜索等值的查询，如：select * from table where index_col = '***'，而对于其他查找类型，如范围查找是不能使用哈希索引的。

全文检索

全文检索是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。当前的InnoDB存储引擎的全文检索存在以下的限制：

• 每张表只能有一个全文检索的索引。
• 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。
• 不支持没有单词界定符的语言，如中文、日语、韩语等。     

索引实践

explain命令

SQL语句

• 添加索引：alter table table_name add index IX_StatDate(stat_date)
• 删除索引：drop index IX_StatDate on table_name ;

参考文献

• 美团点评技术团队博客 《MySql索引原理及慢查询优化》
• 《MySql技术内幕 InnoDB存储引擎》