mysql，explain执行计划组合索引测试

我就是我 2024-04-18 19:18 69阅读 0赞

### 创建测试表，添加测试数据 ###

CREATE TABLE `users` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `manager_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idex_name_age_managerid` (`name`,`age`,`manager_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of users
    -- ----------------------------
    INSERT INTO `users` VALUES ('205', null, '24', '102');
    INSERT INTO `users` VALUES ('206', null, '25', '102');
    INSERT INTO `users` VALUES ('101', 'jack', '35', null);
    INSERT INTO `users` VALUES ('102', 'tom', '30', '101');
    INSERT INTO `users` VALUES ('201', 'xiaoli', '23', '102');
    INSERT INTO `users` VALUES ('203', 'xiaoliu', '22', '102');

### 根据组合索引 idex\_name\_age\_managerid (\`name\`,\`age\`,\`manager\_id\`) 测试不同场景下的sql执行计划 ###

1）EXPLAIN SELECT \* FROM \`users\` WHERE name = 'tom' and age = 30 and manager\_id = 101;

![20190910224858586.png][]

2）EXPLAIN SELECT \* FROM \`users\` WHERE name = 'tom';

![20190910224948107.png][]

3）EXPLAIN SELECT \* FROM \`users\` WHERE age = 30;

![20190910225100260.png][]

4）EXPLAIN SELECT \* FROM \`users\` WHERE manager\_id = 101;

![20190910225135181.png][]

5）EXPLAIN SELECT \* FROM \`users\` WHERE name = 'tom' and age = 30;

![20190910225211281.png][]

6）EXPLAIN SELECT \* FROM \`users\` WHERE name = 'tom' and manager\_id = 101;

![20190910225244685.png][]

7）EXPLAIN SELECT \* FROM \`users\` WHERE age = 30 and manager\_id = 101;

![20190910225317251.png][]

8）EXPLAIN SELECT \* FROM \`users\` WHERE age = 30 AND name = 'tom';

![20200413221212872.png][]

9）EXPLAIN SELECT \* FROM \`users\` WHERE manager\_id= 101 AND name = 'tom';

![20200413221259429.png][]

### 结论总结： ###

1、由以上执行计划可以看出，第3、4、7个执行计划没有命中索引，所以，只要是以含有 name条件的sql语句都命中了索引（遵循左前缀原则）；

2、第8、9个sql虽然没有以name开头，但是也命中了索引，说明 mysql的查询优化器会帮你优化成索引可以识别的形式。

3、根据执行计划分析，命中索引的sql第6、9个查询效率是比较低的，filtered只有16.67，所以：

组合索引 idex\_name\_age\_managerid (\`name\`,\`age\`,\`manager\_id\`)   sql 执行为了提高执行效率，where 条件字段顺序应该遵循以下组合

name

name，age

name，age，manager\_id

[20190910224858586.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/3dd868cde7084abaabec56d5373ed658.png
[20190910224948107.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/3781b5c847404431b32bf7b05550668f.png
[20190910225100260.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/12b492c71a444c699eae5ce5174bac06.png
[20190910225135181.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/fb587e8cb2e741368c4e710c6055f78e.png
[20190910225211281.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/c5993d2e21f04504ad463fe588e7b63a.png
[20190910225244685.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d270f0f04f44435b9d5574ae194f2e1c.png
[20190910225317251.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ed2cbfcb08814fb08f595ce83d52bb97.png
[20200413221212872.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/6b91614780444574a2e82bcee8a8f838.png
[20200413221259429.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7c50a52c31624f3baa9dfe7b643b78c1.png