联合索引 where 查询的不同情况

联合索引 where 查询的不同情况

暂时记录一下, 有时间回来更新 , 这里涉及到了 解释器的优化

select count(id) from user 
-- 查看索引
show index from user
-- 删除之前的索引
drop index idx_sex_pass on user

-- 创建联合索引     执行时间  42.493s  
create index `idx_sex_email_nickname` on user(sex,email,nickname)

-- 测试 条件查询 字段顺序 -0.024s  type ref
-- 索引生效
select username, `password` from user where sex = 'male' and email = 'jack1003@itcast.cn' and nickname = '1003'
--  索引生效,  数据量大, 全表扫描  const
-- 第一个字段生效, 后边两个没有用到
select username, `password` from user where sex = 'male' 
--   走索引  违背最左原则  6.229s
-- 中间的索引不生效, 左边和 右边的依然生效
select username, `password` from user where   nickname = '1003' and sex = 'male' and email = 'jack1003@itcast.cn'
-- 没有第一个字段, 不走索引  全表扫描  type All
select username, `password` from user where   email = 'jack1003@itcast.cn' and nickname = '1003'
-- type ref  索引没有完全生效 , 没有第一个字段, 后边两个索引生效
select username, `password` from user where sex = 'male' and  nickname = '1003'
-- type ref  索引没有完全生效 
select username, `password` from user where  email = 'jack1003@itcast.cn' and nickname = '1003' and sex = 'male' 

-- type range 
select username, `password` from user where  nickname >'1003' and sex = 'male' and email = 'jack1003@itcast.cn' 


select username, `password` from user where   nickname = '10080' and sex = 'male' and email = 'jack10080@itcast.cn'
-- 没有第一个字段 不走索引
select username, `password` from user where   nickname = '10080'
-- type ref  
select username, `password` from user where   nickname = '10080' and sex = 'male'