mysql建索引原则及慢查询分析步骤

2014-09-10 posted in [mysql]

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的顺序可以任意调整

• =和in条件里面索引列的顺序可以任意，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以是任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

• 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*), 表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度为1，而一些状态,性别字段可能在大数据面前区分度为0。使用场景不同，这个值也很难确定，一般需要join的字段我们要求是0.1以上，即平均一条扫描10条记录

• 索引列不能参与计算，保持列”干净”,比如from_unixtime(create_time) = ‘2014-09-10’就不能使用索引，原因很简单，b+tree中存得都是数据表中得字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以应该写成create_time = unix_timestamp(‘2014-09-10’)

• 尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要添加(a,b)的索引，那么只需修改原来的索引即可

慢查询优化的基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE

2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查，看哪个字段的区分度最高。

3. explain查看执行计划，是否与1预期一致(从锁定记录较少的表开始查询)

4. order by limit 形式sql语句让排序的表优先查

5. 了解业务使用具体场景

6. 加索引参考建索引的几大原则

7. 观察结果，不符合预期继续从0分析