2'), ('July', 23, 'dev5', '2018-06-04 16:02:02'), ('July', 23, 'dev7', '2018-06-04 16:02:02'), ('July', 23, 'dev8', '2018-06-04 16:02:02'), ('July', 23, 'dev9', '2018-06-04 16:02:02'), ('July', 23, 'dev10', '2018-06-04 16:02:02'), ('Clive', 23, 'dev1', '2018-06-04 16:02:02'), ('Clive', 23, 'dev2', '2018-06-04 16:02:02'), ('Clive', 23, 'dev3', '2018-06-04 16:02:02'), ('Clive', 23, 'dev4', '2018-06-04 16:02:02'), ('Clive', 23, 'dev6', '2018-06-04 16:02:02'), ('Clive', 23, 'dev5', '2018-06-04 16:02:02'), ('Clive', 23, 'dev7', '2018-06-04 16:02:02'), ('Clive', 23, 'dev8', '2018-06-04 16:02:02'), ('Clive', 23, 'dev9', '2018-06-04 16:02:02'), ('Clive', 23, 'dev10', '2018-06-04 16:02:02');
执行Explain -- 再次执行同样的查询语句,会发现走到索引上了 explain select * from staffs where name = 'July' and age = 23; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE staffs NULL ref idx_nap idx_nap 78 const,const 13 100.00 NULL
查看新的Trace内容 -- 再看下优化器执行过程 { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" },   |