MySQL连表查询性能优化：为什么要为连接条件加上索引？ |
您所在的位置：网站首页 › mysql多表联合查询性能优化 › MySQL连表查询性能优化：为什么要为连接条件加上索引？ |
MySQL连表查询性能优化：为什么要为连接条件加上索引？
|
我们在平时的SQL书写时，join是我们sql中再平常不过的连表操作了,当我们要查询的数据来源于多张表中，我们需要通过连接条件来关联多张表，话不多说，结合图来解释 1 首先我们创建两张表 粉丝表 CREATE TABLE `fans` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `start_id` int(11) NULL DEFAULT NULL COMMENT '球星id', `name` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '姓名', `sex` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '性别', `age` int(11) NULL DEFAULT NULL COMMENT '年龄', `area` varchar(255) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '地区(省份)', PRIMARY KEY (`id`) USING BTREE )球星表 CREATE TABLE `stars` ( `id` int(11) NOT NULL AUTO_INCREMENT, `s_name` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL, `sex` tinyint(4) NULL DEFAULT NULL, `city` varchar(25) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL, `rigist_time` date NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE )分别往两张表里插入一些测试数据 粉丝表
球星表
使用left join 来连接查询粉丝喜欢的球星时，我们知道，left左边的表作为驱动表，也就是我们常说的主表，此时的查询结果 SELECT * from stars a LEFT JOIN fans b ON a.id = b.start_id
我们EXPLAIN分析看一下它的执行计划
可以发现：粉丝表和球星表两张表都是走的全表扫描，毫无疑问，在稍微大数据量一点的情况下，这种查询效率无疑是最低的，我们球星表stars的id是主键id,为什么还是走的全表扫描呢？其实这就涉及我们拿哪张表来作为驱动表(主表)了,上述的sql中，我们使用的是球星表stars去 join 粉丝表fans, 也就是用球星表stars作为驱动表，查询粉丝表里start_id = 球星表的id 这一条件时，由于粉丝表的start_id字段没有建立索引，所以只有粉丝表只能走全表扫描，而球星stars表作为主表，无论如何都会全表扫描（没加where条件时），那我们反过来查看一下，当使用粉丝表fans作为驱动表连接球星表stars时的执行计划呢？
可以看到，这回球星表stars走了索引，正如我们上面所解释的，当我们使用粉丝表fans作为驱动表时，查询球星表stars里id = 粉丝表的start_id时，由于球星表的id字段是主键索引，所以执行计划里可以看到是走了索引查询了的，那么毫无疑问，这样的查询效率会高上许多。 那么是不是我们只要给粉丝表的start_id字段加上索引后，当我们使用粉丝表作为驱动表那么就一定会走索引了呢？我们不妨来试一下 CREATE INDEX start_id_index ON fans(start_id)我们可以看一下此时粉丝表的索引信息
可以看到此时粉丝表start_id是建立了一个普通索引，我们再来执行一下查询计划
采用存储过程向粉丝表添加100条数据 CREATE DEFINER=`root`@`localhost` PROCEDURE `demo_5`(in param int) begin while param < 100 do insert into fans(start_id,name,sex,age,area) values(1003,'阿飞',1,19,'湖北'); set param=param+1; end while; end 再来查看一下使用球星表作为驱动表时，粉丝表的start_id有没有走索引 果然，这时候走了索引。 在没有where条件过滤时，驱动表做全表扫描，被驱动表走索引扫描，假设驱动表示M行，被驱动表示M行，那么时间复杂度就是NlognM，n为多路搜索树的阶数。接下来比较一下大表join小表和小表join大表的差别，假设小表a1000行，大表b10000行，为方便计算，取搜索树阶数为10，小表join大表复杂度为1000 * lg10000 = 4000,而大表join小表复杂度为10000 * lg1000 = 30000,这个复杂度的差别还是非常明显的，因此表关联需要用小表join大表 总结：在联表查询时，尽量为被驱动表的条件列加上索引
|
我们发现type依然还是ALL,这是为什么呢？仔细观察我们可以发现，执行计划里粉丝表中possible_key里有我们刚刚新建的start_id索引列，而possible_key的意思是可能会用到的索引，但为什么没有用到呢？其实是因为我们粉丝表中的数据量太少了，我们之前为了测试方便只向粉丝表中添加了6条数据，在MySQL中有查询优化器，它会选择出执行成本最低的执行计划，由于数据量过少，优化器通过比较发现走全表的成本更低，所以采用了全表扫描，我们可以来验证一下：
【本文地址】
今日新闻 |
推荐新闻 |