使用索引的目的
使用索引的目的是提高数据库查询的效率。索引是怎么提高数据库查询的效率的呢？举个通俗的例子，查字典。数据库中的数据就好比新华字典中的词条，索引就是新华字典的目录。没有建立索引的数据库就好像被撕掉目录的新华字典，只能从头到尾一条一条地查询，效率极其低下。
为了能更快地查字典，哦不，是查询数据库，我们就需要为数据库建立索引。
索引的原理
索引的主要思想是将数据分段，从而减少查询时的无效数据，提高查询效率。比如有1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。
当然，上面只是主要思想，MySQL使用了更加具体的数据结构来实现索引。本文不对索引的数据结构展开讨论。
建立索引的几大原则
如何建立合适的索引，从而最大程度地优化查询效率是一件需要精心设计的事情。本节只介绍几个建立索引时须遵循的原则。
- 最左前缀匹配原则，非常重要的原则。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的顺序可以任意调整。关于最左前缀匹配原则，在最左前缀匹配原则一节有详细说明。 - 尽量选择区分度高的列作为索引,区分度公式为
count(distinct col)/count(*)，即一列中内容不同的记录数占总记录数的比例。通过这个公式我们可以得到字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。所以唯一索引的效率是最高的。在不是唯一键的时候，就要具体情况具体分析了，这也是索引设计的关键点之一。 - 索引列不能参与计算，保持列“干净”，比如
from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，索引的数据结构中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); - 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
- 使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做 。
例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，
那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空
间，也可能会使查询更快。
最左前缀匹配原则
最左前缀使用场景是使用复合索引的时候。当使用复合索引时，如果想要索引有效，where之后的表达式就要满足最左前缀匹配原则。
我总结了一下最左前缀匹配的特点，就是从表达式最左边开始，到第一个范围查询结束，在这个闭区间内的字段应该是索引字段的最左前缀。
最左前缀
在这里解释一下最左前缀，因为没有百度到感觉比较靠谱的解释，所以我在这里谈一下我的理解，仅供参考。
前缀应该不用解释了，学过英语的都该懂点。前缀加个左就是左前缀了，表示从左边开始查找的前缀。但是，这个左只是表示查找的顺序是从左边开始，不是从右边开始，并没有说从哪一位开始，可能是第一位，也可能是第三位。这时候再加一个 最 就表示了从最左边开始。
比如复合索引是(a,b,d,c)，那么查询时表达式的字段顺序为(a)、(a,b)、(a,b,d)、(a,b,d,c)的这些都是它的最左前缀，而(b)、(a,d)、(a,b,c)、(a,b,c,d)这些就不是。
关于MySQL的查询优化器
最左前缀的概念我们已经明白了，但是在MySQL中的情况又有点不同。比如我们发现，索引的顺序是(a,b,d,c)，我们的查询条件是这样写的：where b=10 and c=16 and a=26 and d=0，从严格意义上来讲，这个顺序是不符合最左前缀匹配原则的，但是MySQL的确使用索引完成了查询。这是怎么回事呢？这是因为MySQL的查询优化器帮我们调整了查询条件的顺序。MySQL查询优化器会判断纠正一条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。在有索引的情况下当然是利用索引查询顺序的效率最高咯，所以，MySQL查询优化器会最终以索引的顺序进行查询执行。
最左前缀匹配原则的原理
上面说了这么多的最左前缀的概念，那么我们到底为什么一定要符合最左前缀匹配原则呢？
因为复合索引只有第一个字段是绝对有序的，从第二个开始的字段都只是相对前一个字段有序，在全局范围内是无序的。只有满足最左前缀原则，才可以保证查询内容的有序，而有序又是索引使用的前提。
我们来看个例子，以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：
| name | cid |
|---|---|
| a | 6 |
| c | 4 |
| c | 5 |
| h | 1 |
| z | 9 |
MySQL创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。
第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的。
那么什么时候才能用到呢?当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。这也就是MySQL索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因（最左前缀匹配原则）。
补充使用索引时的具体情况
更多使用索引的详细情况，可以参考最左前缀原理与相关优化
本文的参考资料
http://blog.jobbole.com/86594/
网友评论