1
2
3
4
5
6
7
8
9
10

CREATE TABLE `salary_static` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `school_id` int(11) NOT NULL COMMENT '学校id',
  `student_id` int(11) NOT NULL COMMENT '毕业生id',
  `salary` int(11) NOT NULL DEFAULT '0' COMMENT '毕业薪水',
  `year` int(11) NOT NULL COMMENT '毕业年份',
  PRIMARY KEY (`id`),
  KEY `school_id_key` (`school_id`) USING BTREE,
  KEY `year_school_key` (`year`,`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='毕业生薪水数据统计';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

delimiter  //
CREATE PROCEDURE init_salary_static() 
BEGIN 
	DECLARE year INT;
	DECLARE schid INT;
	DECLARE stuid INT;
	SET year = 2000;
	WHILE year < 2020 DO
		START TRANSACTION; 
		SET schid = 1;
		WHILE schid < 100 DO
			SET stuid = 1;
			WHILE stuid < 1000 DO
				insert into salary_static(school_id,student_id,salary,year) values (schid,stuid,floor(rand()*10000),year);
				SET stuid = stuid + 1;
			END WHILE;
			SET schid = schid + 1;
		END WHILE;
		SET year = year + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;
call init_salary_static();

1

select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;

1

select school_id,avg(salary) from salary_static force index(year_school_key) where year between 2015 and 2019 group by school_id;

1
2
3

SET optimizer_trace="enabled=on";
select school_id,avg(salary) from salary_static where year between 2016 and 2019 group by school_id;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

"range_analysis": {
  "table_scan": {
	"rows": 1973546,
	"cost": 399741
  },
  "potential_range_indices": [
	{
	  "index": "PRIMARY",
	  "usable": false,
	  "cause": "not_applicable"
	},
	{
	  "index": "school_id_key",
	  "usable": true,
	  "key_parts": [
		"school_id",
		"id"
	  ]
	},
	{
	  "index": "year_school_key",
	  "usable": true,
	  "key_parts": [
		"year",
		"school_id",
		"id"
	  ]
	}
  ],
  "setup_range_conditions": [
  ],
  "group_index_range": {
	"chosen": false,
	"cause": "not_applicable_aggregate_function"
  },
  "analyzing_range_alternatives": {
	"range_scan_alternatives": [
	  {
		"index": "year_school_key",
		"ranges": [
		  "2016 <= year <= 2019"
		],
		"index_dives_for_eq_ranges": true,
		"rowid_ordered": false,
		"using_mrr": false,
		"index_only": false,
		"rows": 812174,
		"cost": 974610,
		"chosen": false,
		"cause": "cost"
	  }
	],
	"analyzing_roworder_intersect": {
	  "usable": false,
	  "cause": "too_few_roworder_scans"
	}
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13

{
    "reconsidering_access_paths_for_index_ordering": {
      "clause": "GROUP BY",
      "index_order_summary": {
        "table": "`salary_static`",
        "index_provides_order": true,
        "order_direction": "asc",
        "index": "school_id_key",
        "plan_changed": true,
        "access_type": "index_scan"
      }
    }
}

1

CREATE INDEX year_school_salary_key ON salary_static (year, school_id, salary);

1
2
3
4
5
6
7
8
9
10
11
12

全表扫描的查询成本 = I/O成本 + CPU成本
= b*a/1024/16 * 1.0 + b * 0.2


索引扫描的查询成本 = I/O成本 + CPU成本
= c * 1.0 + c * 0.2 = c * 1.2


b*a/1024/16 * 1.0 + b * 0.2 = c * 1.2
临界比例 = c/b 
= (a/1024/16 + 0.2)/1.2
= a * 5E-5 + 0.1667