Mysql资料 索引--索引优化（上）

覆盖索引

该sql命中了索引，但未覆盖索引。 select * from s1 where id=123;

利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。 但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，就减去了这份苦恼，如下

这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，速度很快 select id from s1 where id=123;

联合索引

create index idn on s1(1d,name);

索引选择情况

1. #1.最左前缀匹配原则，非常重要的原则，
2. create index ix_name_email on s1(name,email,)
3. - 最左前缀匹配：必须按照从左到右的顺序匹配
4. select * from s1 where name='egon'; #可以
5. select * from s1 where name='egon' and email='asdf'; #可以
6. select * from s1 where email='alex@oldboy.com'; #不可以
7. mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
8. 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
9. d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
10. #2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
11. 会帮你优化成索引可以识别的形式
12. #3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，
13. 表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
14. 性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
15. 这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
16. #4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
17. 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，
18. 但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
19. 所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
20. #5.需要在那些上面建立索引
21. 解答： select user,host from mysql.user where password=...
22. 索引一定要创建在 where 后的条件列上，而不是 select 后的选择数据的列上，另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如:男女性别列唯一值， 不适合建立索引

慢查询优化

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4. order by limit 形式的sql语句让排序的表优先查
5. 了解业务方使用场景
6. 加索引时参照建索引的几大原则
7. 观察结果，不符合预期继续从0分析