2020-12-26:mysql中,表person有字段id、name、age、sex,id是主键,name是普通索引,age和sex没有索引。select * from person where id=1 and name=‘james’ and age=1 and sex=0。请问这条语句有几次回表?
DROP TABLE IF EXISTS
person
;
CREATE TABLE
person
(
id
int(11) NOT NULL,
name
varchar(255) NOT NULL,
age
int(11) NOT NULL,
sex
tinyint(4) NOT NULL,
PRIMARY KEY (
id
),
KEY
name
(
name
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Records of person,表中记录
INSERT INTO
person
VALUES (‘1’, ‘james’, ‘11’, ‘1’);
INSERT INTO
person
VALUES (‘2’, ‘haha’, ‘12’, ‘0’);
INSERT INTO
person
VALUES (‘3’, ‘福大大’, ‘13’, ‘2’);
2.查看执行计划:
EXPLAIN SELECT * FROM person WHERE id=3;
EXPLAIN SELECT * FROM person WHERE id=3 AND name=‘福大大’;
EXPLAIN SELECT * FROM person WHERE id=3 AND name=‘福大大’ AND age=13;
EXPLAIN SELECT * FROM person WHERE id=3 AND name=‘福大大’ AND age=13 AND sex=2;