MySQL建索引的几大原则

最左前缀匹配原则

b+树的数据项是复合的数据结构,比如索引为(name, age, sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三, 20, F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的搜索方向,如果name相同再依次比较agesex,最后得到检索的数据;但当(20, F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三, F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了,这个是非常重要的性质,即索引的最左前缀匹配原则。

索引建议

  1. 根据最左前缀匹配原则,MySQL会一直向右匹配直到遇到范围查询(><betweenlike)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4,如果建立(a, b, c, d)顺序的索引,d是用不到索引的,如果建立(a, b, d, c)的索引则都可以用到,abd的顺序可以任意调整。
  2. =in可以乱序,比如a = 1 and b = 2 and c = 3建立(a, b, c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式。
  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0。
  4. 索引列不能参与计算,否则将导致引擎放弃使用索引而进行全表扫描,比如select id from t where num/2=100;应优化成select id from t where num=100*2;
  5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a, b)的索引,那么只需要修改原来的索引即可。

参考资料