本篇博客中有一些错误的有待纠正的问题,函待更正
添加测试数据
数据体量必须要大 否则就算建了联合索引 因为数据体量很少 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的执行解析