[toc]

建立索引的规则原则

• 左前缀匹配原则，非常重要的原则，MySQL会一直向右匹配直到遇到<、>、between、like就停止匹配了，比如按照建立(a,b,c,d)的的顺序索引，a=1 and b=2 and c>3 and d=4 ,那么d将不会走索引(注意c可以走索引，只是向后停止了)，如果按照(a,d,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)='2014-05-29'就不能使用索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大，索引语句应该写成create_time =unix_timestamp('2014-05-29')
• 尽量的扩展索引，不要新建索引，比如表中已经有a的索引，现在要加(a,b)索引，那么只需要修改原来的索引即可

联合索引的最左匹配原则

建立一个联合索引(a,b,c)实际相当于建立了a，(a,b)，(a,b,c)三个索引。

假如建立联合索引(a,b,c)下列语句使用索引的情况和使用的列：

where a=3 //是，使用了a列
where a=3 and b=5 //是，使用了ab列
where a=3 and c=4 and b=5 //是，使用了abc列(因为MySQL查询优化器会帮助优化成索引可以识别的形式像=和in操作是可以乱序的)
where b=3 //否
where a=3 and c=4 //是，使用了a列
where a=3 and b>10 and c=7 //是使用了a，b列
where a=3 and b like 'xx%' and c=7 //是，使用了ab列(like '%xx'是不能使用索引的，'xx%'可以使用索引的)