非复合多列索引实战

mysql之explain分析表

#建表

test ： id,name,age,addr,qq
test_copy  ： id,name,age,addr,qq

CREATE TABLE `test` (
  `id` bigint(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  `addr` varchar(23) DEFAULT NULL,
  `qq` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_addr` (`addr`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
CREATE TABLE `test_copy` (
  `id` bigint(12) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  `addr` varchar(23) DEFAULT NULL,
  `qq` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#建test表索引
非唯一性索引【NORMAL】name,age,addr
索引类型

索引方式

false：表示索引失效
true：表示索引有效

以***释内容的含义：是否有效 索引类型 索引列
以下内容直接复制到Navicat的新建查询测试即可

#OR 只要有or索引就无效

EXPLAIN SELECT * from test where age = 1 or name=""  #fasle
EXPLAIN SELECT age from test where age = 1 or name="" #fasle

#LIKE

EXPLAIN SELECT * from test where name like "%a" #fasle
EXPLAIN SELECT name from test where name like "%a" #true index idx_name
EXPLAIN SELECT * from test where name like "%a%" #fasle
EXPLAIN SELECT name from test where name like "%a%" #true index idx_name
EXPLAIN SELECT * from test where name like "a%" #true  range idx_name

#单个字段

EXPLAIN SELECT * from test where age<>1  #fasle
EXPLAIN SELECT * from test where age = 1  #true ref idx_age
EXPLAIN SELECT age from test where age<>1  #true index idx_age
EXPLAIN SELECT * from test where age <= 1  #true range  idx_age
EXPLAIN SELECT * from test where age  BETWEEN 1 and 2  #true range  idx_age

#多个字段

EXPLAIN SELECT * from test where age<>1 and name="a" #true ref idx_name ,顺序无关 
EXPLAIN SELECT * from test where age<1 and name="a" #true ref idx_name,顺序无关

#ORDER BY

EXPLAIN SELECT * from test where age>1  ORDER BY name #fasle
EXPLAIN SELECT * from test where 1=1 BETWEEN 1 and 2  ORDER BY name #fasle
EXPLAIN SELECT * from test where age>1 and name="a" ORDER BY name #true ref idx_name
EXPLAIN SELECT * from test where age=1 and name="a" ORDER BY name #true ref idx_name

#多表查询

EXPLAIN SELECT * from test t,test_copy te where t.id=te.id #true eq_ref  primary
EXPLAIN SELECT * from test t,test_copy te where t.name=te.name #true ref  idx_name
EXPLAIN SELECT * from test t,test_copy te where te.name=t.name #true ref  idx_name
EXPLAIN SELECT * from test t LEFT JOIN test_copy te on t.name=te.name #true ref idx_name

#SUBQUERY

EXPLAIN SELECT * from test where id IN(select id from test where age<>1)#true unique_subquery PRIMARY
EXPLAIN SELECT * from test where id IN(select id from test where age>1)#true unique_subquery PRIMARY
EXPLAIN SELECT * from test where name IN(select name from test where age>1)#true index_subquery idx_name

总结

select *索引失效不代表select column索引也失效
select *索引有效则select column索引肯定有效

select * from user where name="" #索引有效
select name from user where name="" #索引有效

例如

EXPLAIN select * from user where name<>"" #索引无效

可以改善为

EXPLAIN select * from user u,(select name from user where name<>"") u1 where 
u.name=u1.name #使用连接查询使索引起效

此种多列索引一般不推荐，数据库开销大，多列索引请使用复合索引，请看下篇复合索引实战

复合索引实战