CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`dep_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`tt` char(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix` (`name`,`dep_id`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
创建了联合索引:KEY ix
(name
,dep_id
,age
)
explain select * from test where name = ‘qwe’ and dep_id = 1 and age = 9\G;
使用了全部
mysql> explain select * from test where name = 'qwe' and dep_id = 1 and age = 9\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 73
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
使用了2个
explain select * from test where name = ‘qwe’ and dep_id = 1\G;
mysql> explain select * from test where name = 'qwe' and dep_id = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 68
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
使用了1个
explain select * from test where name = ‘qwe’ and age = 9\G;
mysql> explain select * from test where name = 'qwe' and age = 9\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 63
ref: const
rows: 1
filtered: 33.33
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
没有使用
explain select * from test where dep_id = 1 and age = 9\G;
mysql> explain select * from test where dep_id = 1 and age = 9\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
explain select * from test where name = ‘qwe’\G; 使用了1个
mysql> explain select * from test where name = 'qwe'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 63
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
explain select * from test where dep_id = 1 and name = ‘qqw’\G; 优化器会调整顺序,使用了2个
mysql> explain select * from test where dep_id = 1 and name = 'qqw'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 68
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
**explain select * from test where dep_id = 1\G;**没有使用
mysql> explain select * from test where dep_id = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
explain select * from test where age = 1 and name = ‘qqw’\G;调整顺序,使用了1个
mysql> explain select * from test where age = 1 and name = 'qqw'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix
key: ix
key_len: 63
ref: const
rows: 1
filtered: 33.33
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)