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

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

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

  1. CREATE TABLE `users` (
  2.   `id` int(11) NOT NULL,
  3.   `name` varchar(255) DEFAULT NULL,
  4.   `age` int(11) DEFAULT NULL,
  5.   `manager_id` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`id`),
  7.   KEY `idex_name_age_managerid` (`name`,`age`,`manager_id`) USING BTREE
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  10. -- ----------------------------
  11. -- Records of users
  12. -- ----------------------------
  13. INSERT INTO `users` VALUES ('205', null, '24', '102');
  14. INSERT INTO `users` VALUES ('206', null, '25', '102');
  15. INSERT INTO `users` VALUES ('101', 'jack', '35', null);
  16. INSERT INTO `users` VALUES ('102', 'tom', '30', '101');
  17. INSERT INTO `users` VALUES ('201', 'xiaoli', '23', '102');
  18. 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

发表评论

表情：
评论列表 （有 0 条评论，112人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQLExplain详解

    Explain使用场景 1. 查询性能优化：`EXPLAIN`可以帮助开发者分析查询语句的执行计划，判断是否有效地使用了索引、是否有可能导致全表扫描等性能问题。通过`E

    左手的ㄟ右手左手的ㄟ右手/ 0 赞/ 151 阅读

    相关 如何有效进行测试执行进度计划

    测试执行通常都是处于软件测试生命周期的关键路径上，它不仅在测试过程中占有重要的地位，并且也会花费大量的测试时间。针对测试执行而进行的计划，即测试执行进度计划，是进行测试执行进度

    悠悠悠悠/ 0 赞/ 111 阅读

    相关 执行计划-1：获取执行计划

    看懂执行计划是作为处理数据库性能问题的必备技能之一，接下来一系列的文章我会告诉你怎么去做。 我们会从一些文章开始讨论几种获取执行计划的方法，并且评估不同来源的执行计划最适合哪

    水深无声水深无声/ 0 赞/ 82 阅读