索引学习笔记

本篇博客中有一些错误的有待纠正的问题，函待更正

 

添加测试数据

数据体量必须要大 否则就算建了联合索引 因为数据体量很少 innodb也会自动选用全表扫描的方式来查询，可能这样效率更高

本次笔记测试数据表的数据体量在5000条左右，实测时索引建立后可以生效

##创建联合索引
create index index_test_k_u_s_m on users_front_login_session(user_id,status,mac_address);

 

#测试最左匹配原则
explain select * from users_front_login_session where user_id=2 ;

explain select * from users_front_login_session where user_id=2 and status=1 ;

违背最左原则案例

explain select * from users_front_login_session where status=2 ;

这个测试的结果看似违背了最左原则实际上不是，是因为 select * 造成的随机io 所以解析器没有使用索引造成的，这个在后面会给与解释，只需要记住 最左原则在都是非范围匹配的时候是和顺序无关的就是了，想要让上面的这条sql使用到索引 把* 替换为status即可

 

explain select * from users_front_login_session where user_id=2 and status=2 and  mac_address='10.51.73.11';

 

explain select * from users_front_login_session where user_id=2 and status>1 and  mac_address='10.51.73.11';

可见 user_id 和 status索引一共扫描了 13行 其中   mac_address 没有使用

 

explain select mac_address
        from users_front_login_session
        where status = 2
        group by mac_address;

 

explain
    select mac_address
    from (
             select mac_address
             from users_front_login_session
             where status = 2
             group by mac_address) as tab
    group by tab.mac_address;

可见排序字段的索引其实并没有使用

 

 

#再创建一个索引
create index index_test_k_s_m on users_front_login_session (status, mac_address);

explain select * from users_front_login_session where status=2 ;

explain select * from users_front_login_session where status=1 ;

实际情况中还要根据数据情况去实际的查看 索引是否被使用，并不是所哟的情况建立了索引，innodb就会去用 ，有些时候因为数据体量的问题全局扫描的优先级别会更高

 

 

#where顺序是否会对 索引匹配有影响
create index index_test_k_c_u_s on users_front_login_session (company_id, user_id, status);

#按照简历索引的顺序
explain select login_time from users_front_login_session where company_id=18113 and user_id=4 and status=2;
#前两个互换顺序
explain select login_time from users_front_login_session where user_id=4 and company_id=18113  and status=2;
#最后一个提到最前面
explain select login_time from users_front_login_session where status=2 and user_id=4 and company_id=18113 ;
#索引不全的那种测试
explain select status from users_front_login_session where status=2;

测试结果一样都使用了索引

 

#第二条的状态就是属于第三颗星的索引优化    只使用的第三颗星的索引  而对于第一颗星的索引并没有使用
explain select * from users_front_login_session where company_id=18113 and status=2 and user_id=4;
explain select status from users_front_login_session where status=2 and user_id=4;

 

第二条sql的执行解析

 

转载于:https://my.oschina.net/fusublog/blog/3066443