MySQL查询分析

 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

--Create Database
CREATE DATABASE test

--Create Table
USE test;

CREATE TABLE `single_table` (    
`id` INT(11) NOT NULL AUTO_INCREMENT,    
`key1` VARCHAR(100),    
`key2` INT(11) ,    
`key3` VARCHAR(100),   
`key_part1` VARCHAR(100),    
`key_part2` VARCHAR(100),    
`key_part3` VARCHAR(100),    
`common_field` VARCHAR(100),    
PRIMARY KEY (`id`),    
KEY idx_key1 (`key1`),    
UNIQUE KEY uq_key2 (`key2`),    
KEY idx_key3 (`key3`),    
KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)
) Engine=InnoDB;

--Create Procedure
DELIMITER ;;
CREATE PROCEDURE cost_data()
    BEGIN    
    DECLARE i INT;    
    SET i=0;    
    WHILE i<10000 DO        
        INSERT INTO single_table(key1,key2,key3,key_part1,key_part2,key_part3,common_field)
		VALUES(SUBSTRING(MD5(RAND()),1,2),i+1,SUBSTRING(MD5(RAND()),1,3),SUBSTRING(MD5(RAND()),1,4),
		SUBSTRING(MD5(RAND()),1,5),SUBSTRING(MD5(RAND()
),1,6),SUBSTRING(MD5(RAND()),1,7));        
        SET i=i+1;    
    END WHILE;
END;;
DELIMITER ;

--Call Procedure
CALL cost_data();

--Query Data
SELECT COUNT(*) FROM test.single_table;
SELECT * FROM test.single_table LIMIT 20;

1
2
3
4
5
6
7

xxx  < ANY(SELECT inner_expr...)
-- 可以转化为
xxx < (SELECT max(inner_expr)...)

xxx > ANY(SELECT inner_expr...)
-- 可以转换为
xxx > (SELECT min(inner_expr)...)

1
2
3
4
5
6
7

xxx < ALL(SELECT inner_expr...)
-- 可以转换为
xxx < (SELECT min(inner_expr)...)

xxx > ALL(SELECT inner_expr...)
-- 可以转换为
xxx > (SELECT max(inner_expr)...)

1
2
3
4

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a';
-- s2的key2是唯一索引，肯定不重复
-- 相当于 s1.key2 = s2.key2，直接将子查询上提
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a'

1
2
3
4
5
6

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b')
-- 相当于 s1.key3 = s2.key1
-- 子查询本身带了 WHERE，对 s2的key1做了范围扫描 (a, b)
-- 对于满足条件的如 s2.key1：aa、aa、aa、ab、ab、ac、ac
-- 只需要取重复记录的第一条即可
-- 所以叫松散扫描

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

SHOW TABLE STATUS LIKE 'single_table'\G
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 9748
 Avg_row_length: 163
    Data_length: 1589248
Max_data_length: 0
   Index_length: 1212416
      Data_free: 4194304
 Auto_increment: 10001
    Create_time: 2022-12-14 19:49:32
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

1
2
3
4
5
6

SELECT * FROM single_table WHERE
  key1 IN ('a', 'b', 'c') AND 
  key2 > 10 AND key2 < 1000 AND 
  key3 > key2 AND
  key_part1 LIKE '%hello%' AND
  common_field = '123'

1

SHOW VARIABLES LIKE '%dive%'

1

SELECT * FROM s1 WHERE key1 IN (.....)   

1

SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages'

1

SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

1

ANALYZE TABLE sinle_table   

1

FLUSH TABLE single_table;   

1

SHOW VARIABLES LIKE 'innodb_stats_method'

1
2

UPDATE `mysql`.`server_cost` SET `cost_value` = '0.00001' WHERE `cost_name` = 'disk_temptable_create_cost'; 
FLUSH OPTIMIZER_COSTS;

1

SHOW VARIABLES LIKE 'join_buffer_size'

1
2
3
4

SELECT * FROM s1 INNER JOIN s2
  ON s1.key1 = s2.common_field
  WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
        s2.key2 > 1000 AND s2.key2 < 2000

1
2
3
4
5

-- 搜索深度默认 62   
SHOW VARIABLES LIKE 'optimizer_search_depth'

--- 是否启动这些启发式规则，帮助减枝
SHOW VARIABLES LIKE 'optimizer_prune_level'

1

EXPLAIN SELECT * FROM s1;

1

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

1

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

1

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

1

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b')

1
2
3

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b' UNION 
	SELECT key1 FROM s2 WHERE key1 = 'b' UNION SELECT key1 FROM single_table WHERE key1 = 'c'
)

1

explain select * from (select key1, count(*) as c from s1 group by key1) as derived_s1 where c > 1;

1

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

 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

CREATE TABLE `tr` (
     `id` INT, 
     `name` VARCHAR(50), 
     `purchased` DATE
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
);


INSERT INTO `tr` VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'alarm clock', '1997-11-05'),
 (3, 'chair', '2009-03-10'),
 (4, 'bookcase', '1989-01-10'),
 (5, 'exercise bike', '2014-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'espresso maker', '2011-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '2006-09-16'),
 (10, 'lava lamp', '1998-12-25');

1

EXPLAIN SELECT * FROM tr WHERE purchased = '2014-05-09'

1
2
3

CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t; 

1

EXPLAIN SELECT * FROM s1 WHERE id = 5;

1

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id

1

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1;

1

EXPLAIN SELECT * FROM single_table WHERE key1 = 'abc'

1

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL

1

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

1

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT id FROM s2 WHERE s1.common_field = s2.common_field) OR key3 = 'a';

1

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 WHERE s1.common_field = s2.common_field) OR key3 = 'a';

1

EXPLAIN SELECT * FROM s1 WHERE KEY1 IN ('a', 'b', 'c');

1

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

1

EXPLAIN SELECT * FROM s1 ORDER BY id;

1

EXPLAIN SELECT * FROM s1 ORDER BY id;

1

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

1

SELECT 1

1

SELECT * FROM s1 WHERE 1 != 1

1

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg'

1

SELECT key1 FROM s1 WHERE key1 = 'a'

1

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

1

SELECT * FROM s1 WHERE common_field = 'a'

1

SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field

1
2

-- Using union(PRIMARY,uq_key2); Using where
SELECT * FROM s1 WHERE id = 1 OR key2 = 2

1

SELECT * FROM s1 LIMIT 0

1

SELECT * FROM s1 ORDER BY common_field LIMIT 10

1

SELECT DISTINCT common_field FROM s1

1
2
3
4

SELECT common_field, COUNT(*) AS c FROM s1 GROUP BY common_field

-- 改成这样就没有 filesort了   
SELECT common_field, COUNT(*) AS c FROM s1 GROUP BY common_field ORDER BY NULL

1
2

-- 驱动该表显示： Using where; Using index; LooseScan
EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 >= 'z')

1
2

-- 被驱动表提示： Using where; FirstMatch(s1)
SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 WHERE s1.key3 = s2.key3)

1
2

EXPLAIN FORMAT=JSON
SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field= 'a'

 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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

{
	"query_block": {
		"select_id": 1,
		"cost_info": {
			"query_cost": "3294.12"
		},
		"nested_loop": [{
				"table": {
					"table_name": "s1",
					"access_type": "ALL",
					"possible_keys": [
						"idx_key1"
					],
					"rows_examined_per_scan": 9991,
					"rows_produced_per_join": 999,
					"filtered": "10.00",
					"cost_info": {
						"read_cost": "1895.38",
						"eval_cost": "199.82",
						"prefix_cost": "2095.20",
						"data_read_per_join": "601K"
					},
					"used_columns": [
						"id",
						"key1",
						"key2",
						"key3",
						"key_part1",
						"key_part2",
						"key_part3",
						"common_field"
					],
					"attached_condition": "((`test`.`s1`.`common_field` = 'a') and (`test`.`s1`.`key1` is not null))"
				}
			},
			{
				"table": {
					"table_name": "s2",
					"access_type": "ref",
					"possible_keys": [
						"uq_key2"
					],
					"key": "uq_key2",
					"used_key_parts": [
						"key2"
					],
					"key_length": "5",
					"ref": [
						"test.s1.key1"
					],
					"rows_examined_per_scan": 1,
					"rows_produced_per_join": 999,
					"filtered": "100.00",
					"index_condition": "(`test`.`s1`.`key1` = `test`.`s2`.`key2`)",
					"cost_info": {
						"read_cost": "999.10",
						"eval_cost": "199.82",
						"prefix_cost": "3294.12",
						"data_read_per_join": "601K"
					},
					"used_columns": [
						"id",
						"key1",
						"key2",
						"key3",
						"key_part1",
						"key_part2",
						"key_part3",
						"common_field"
					]
				}
			}
		]
	}
}

1

SHOW VARIABLES LIKE 'optimizer_trace'

1

SET optimizer_trace="enabled=on"

1
2
3
4
5
6

EXPLAIN 
SELECT * FROM s1 WHERE 
key1 > 'z' AND 
key2 < 100000 AND 
key3 IN ('a', 'b', 'c') AND
common_field = 'abc'

1

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
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          {
            "expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,`s1`.`key2` AS `key2`,
			`s1`.`key3` AS `key3`,`s1`.`key_part1` AS `key_part1`,`s1`.`key_part2` AS `key_part2`,`s1`.`key_part3` AS
			`key_part3`,`s1`.`common_field` AS `common_field` from `s1` where ((`s1`.`key1` > 'z') 
			and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))
			limit 0,1000"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')) and multiple equal('abc', `s1`.`common_field`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')) and multiple equal('abc', `s1`.`common_field`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')) and multiple equal('abc', `s1`.`common_field`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`s1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`s1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 9991,
                    "cost": 11991
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "uq_key2",
                      "usable": true,
                      "key_parts": [
                        "key2"
                      ]
                    },
                    {
                      "index": "idx_key1",
                      "usable": true,
                      "key_parts": [
                        "key1",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_key3",
                      "usable": true,
                      "key_parts": [
                        "key3",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_key_part",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "uq_key2",
                        "ranges": [
                          "NULL < key2 < 100000"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 10000,
                        "cost": 12001,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "idx_key1",
                        "ranges": [
                          "z < key1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1,
                        "cost": 2.21,
                        "chosen": true
                      },
                      {
                        "index": "idx_key3",
                        "ranges": [
                          "a <= key3 <= a",
                          "b <= key3 <= b",
                          "c <= key3 <= c"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 3,
                        "cost": 6.61,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_key1",
                      "rows": 1,
                      "ranges": [
                        "z < key1"
                      ]
                    },
                    "rows_for_plan": 1,
                    "cost_for_plan": 2.21,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`s1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 1,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_key1"
                      },
                      "resulting_rows": 1,
                      "cost": 2.41,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 2.41,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`s1`",
                  "attached": "((`s1`.`common_field` = 'abc') and (`s1`.`key1` > 'z') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`s1`",
                "pushed_index_condition": "(`s1`.`key1` > 'z')",
                "table_condition_attached": "((`s1`.`common_field` = 'abc') and (`s1`.`key2` < 100000) and (`s1`.`key3` in ('a','b','c')))"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}