· 6 years ago · Mar 06, 2019, 06:16 AM
1-----------
2drop table if exists t;
3create table t(a int unsigned not null auto_increment,b int,c varchar(64),primary key(a,b))
4partition by range(b)
5(
6 partition p1 values less than (1000),
7 partition p2 values less than (2000),
8 partition p3 values less than (3000),
9 partition p4 values less than (4000),
10 partition p5 values less than (5000),
11 partition p6 values less than (6000),
12 partition p7 values less than (7000),
13 partition p8 values less than (8000),
14 partition p9 values less than (9000),
15 partition p10 values less than (10000),
16 partition p11 values less than (11000),
17 partition p12 values less than (12000),
18 partition p13 values less than (13000),
19 partition p14 values less than (14000),
20 partition p15 values less than (15000),
21 partition p16 values less than (16000),
22 partition p17 values less than (17000),
23 partition p18 values less than (18000),
24 partition p19 values less than (19000),
25 partition p20 values less than (20000),
26 partition p21 values less than (21000),
27 partition p22 values less than (22000),
28 partition p23 values less than (23000),
29 partition p24 values less than (24000),
30 partition p25 values less than (25000),
31 partition p26 values less than (26000),
32 partition p27 values less than (27000),
33 partition p28 values less than (28000),
34 partition p29 values less than (29000),
35 partition p30 values less than (30000),
36 partition p31 values less than (31000),
37 partition p32 values less than (maxvalue)
38);
39
40insert into t(b,c) values (floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand());
41insert into t(b,c) values (floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand());
42insert into t(b,c) select floor(rand()*32000),rand() from t a,t b,t c,t d,t e,t f;
43analyze table t;
44select * from information_schema.partitions where table_name='t' and table_schema=database();
45
46explain select * from t where a=21648 and b=1000;
47flush status;
48select * from t where a=21648 and b=1000;
49show status like '%handler%';
50
51
52explain select * from t where b=1000;
53flush status;
54select * from t where b=1000;
55show status like '%handler%';
56
57
58------------------
59
60
61
62mysql> drop table if exists t;
63Query OK, 0 rows affected (0.56 sec)
64
65mysql> create table t(a int unsigned not null auto_increment,b int,c varchar(64),primary key(a,b))
66 -> partition by range(b)
67 -> (
68 -> partition p1 values less than (1000),
69 -> partition p2 values less than (2000),
70 -> partition p3 values less than (3000),
71 -> partition p4 values less than (4000),
72 -> partition p5 values less than (5000),
73 -> partition p6 values less than (6000),
74 -> partition p7 values less than (7000),
75 -> partition p8 values less than (8000),
76 -> partition p9 values less than (9000),
77 -> partition p10 values less than (10000),
78 -> partition p11 values less than (11000),
79 -> partition p12 values less than (12000),
80 -> partition p13 values less than (13000),
81 -> partition p14 values less than (14000),
82 -> partition p15 values less than (15000),
83 -> partition p16 values less than (16000),
84 -> partition p17 values less than (17000),
85 -> partition p18 values less than (18000),
86 -> partition p19 values less than (19000),
87 -> partition p20 values less than (20000),
88 -> partition p21 values less than (21000),
89 -> partition p22 values less than (22000),
90 -> partition p23 values less than (23000),
91 -> partition p24 values less than (24000),
92 -> partition p25 values less than (25000),
93 -> partition p26 values less than (26000),
94 -> partition p27 values less than (27000),
95 -> partition p28 values less than (28000),
96 -> partition p29 values less than (29000),
97 -> partition p30 values less than (30000),
98 -> partition p31 values less than (31000),
99 -> partition p32 values less than (maxvalue)
100 -> );
101Query OK, 0 rows affected (0.65 sec)
102
103mysql>
104mysql> insert into t(b,c) values (floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand());
105Query OK, 5 rows affected (0.01 sec)
106Records: 5 Duplicates: 0 Warnings: 0
107
108mysql> insert into t(b,c) values (floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand()),(floor(rand()*32000),rand());
109Query OK, 5 rows affected (0.05 sec)
110Records: 5 Duplicates: 0 Warnings: 0
111
112mysql> insert into t(b,c) select floor(rand()*32000),rand() from t a,t b,t c,t d,t e,t f;
113Query OK, 1000000 rows affected (21.75 sec)
114Records: 1000000 Duplicates: 0 Warnings: 0
115
116mysql> analyze table t;
117+--------+---------+----------+----------+
118| Table | Op | Msg_type | Msg_text |
119+--------+---------+----------+----------+
120| test.t | analyze | status | OK |
121+--------+---------+----------+----------+
1221 row in set (0.83 sec)
123
124mysql> select * from information_schema.partitions where table_name='t' and table_schema=database();
125+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------
126| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE
127+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------
128| def | test | t | p1 | NULL | 1 | NULL | RANGE | NULL | `b` | NULL | 1000 | 31432 | 83 | 2637824 | 0 | 0 | 0
129| def | test | t | p10 | NULL | 10 | NULL | RANGE | NULL | `b` | NULL | 10000 | 31761 | 83 | 2637824 | 0 | 0 | 0
130| def | test | t | p11 | NULL | 11 | NULL | RANGE | NULL | `b` | NULL | 11000 | 30652 | 86 | 2637824 | 0 | 0 | 0
131| def | test | t | p12 | NULL | 12 | NULL | RANGE | NULL | `b` | NULL | 12000 | 31432 | 83 | 2637824 | 0 | 0 | 0
132| def | test | t | p13 | NULL | 13 | NULL | RANGE | NULL | `b` | NULL | 13000 | 30642 | 86 | 2637824 | 0 | 0 | 0
133| def | test | t | p14 | NULL | 14 | NULL | RANGE | NULL | `b` | NULL | 14000 | 30632 | 86 | 2637824 | 0 | 0 | 0
134| def | test | t | p15 | NULL | 15 | NULL | RANGE | NULL | `b` | NULL | 15000 | 30637 | 86 | 2637824 | 0 | 0 | 0
135| def | test | t | p16 | NULL | 16 | NULL | RANGE | NULL | `b` | NULL | 16000 | 31747 | 83 | 2637824 | 0 | 0 | 0
136| def | test | t | p17 | NULL | 17 | NULL | RANGE | NULL | `b` | NULL | 17000 | 31432 | 83 | 2637824 | 0 | 0 | 0
137| def | test | t | p18 | NULL | 18 | NULL | RANGE | NULL | `b` | NULL | 18000 | 30642 | 86 | 2637824 | 0 | 0 | 0
138| def | test | t | p19 | NULL | 19 | NULL | RANGE | NULL | `b` | NULL | 19000 | 31428 | 83 | 2637824 | 0 | 0 | 0
139| def | test | t | p2 | NULL | 2 | NULL | RANGE | NULL | `b` | NULL | 2000 | 31113 | 51 | 1589248 | 0 | 0 | 0
140| def | test | t | p20 | NULL | 20 | NULL | RANGE | NULL | `b` | NULL | 20000 | 31447 | 83 | 2637824 | 0 | 0 | 0
141| def | test | t | p21 | NULL | 21 | NULL | RANGE | NULL | `b` | NULL | 21000 | 31437 | 83 | 2637824 | 0 | 0 | 0
142| def | test | t | p22 | NULL | 22 | NULL | RANGE | NULL | `b` | NULL | 22000 | 30326 | 52 | 1589248 | 0 | 0 | 0
143| def | test | t | p23 | NULL | 23 | NULL | RANGE | NULL | `b` | NULL | 23000 | 30652 | 86 | 2637824 | 0 | 0 | 0
144| def | test | t | p24 | NULL | 24 | NULL | RANGE | NULL | `b` | NULL | 24000 | 31428 | 83 | 2637824 | 0 | 0 | 0
145| def | test | t | p25 | NULL | 25 | NULL | RANGE | NULL | `b` | NULL | 25000 | 30637 | 86 | 2637824 | 0 | 0 | 0
146| def | test | t | p26 | NULL | 26 | NULL | RANGE | NULL | `b` | NULL | 26000 | 31756 | 83 | 2637824 | 0 | 0 | 0
147| def | test | t | p27 | NULL | 27 | NULL | RANGE | NULL | `b` | NULL | 27000 | 31442 | 83 | 2637824 | 0 | 0 | 0
148| def | test | t | p28 | NULL | 28 | NULL | RANGE | NULL | `b` | NULL | 28000 | 31761 | 83 | 2637824 | 0 | 0 | 0
149| def | test | t | p29 | NULL | 29 | NULL | RANGE | NULL | `b` | NULL | 29000 | 30958 | 85 | 2637824 | 0 | 0 | 0
150| def | test | t | p3 | NULL | 3 | NULL | RANGE | NULL | `b` | NULL | 3000 | 31752 | 83 | 2637824 | 0 | 0 | 0
151| def | test | t | p30 | NULL | 30 | NULL | RANGE | NULL | `b` | NULL | 30000 | 30637 | 86 | 2637824 | 0 | 0 | 0
152| def | test | t | p31 | NULL | 31 | NULL | RANGE | NULL | `b` | NULL | 31000 | 30326 | 52 | 1589248 | 0 | 0 | 0
153| def | test | t | p32 | NULL | 32 | NULL | RANGE | NULL | `b` | NULL | MAXVALUE | 31428 | 83 | 2637824 | 0 | 0 | 0
154| def | test | t | p4 | NULL | 4 | NULL | RANGE | NULL | `b` | NULL | 4000 | 30958 | 85 | 2637824 | 0 | 0 | 0
155| def | test | t | p5 | NULL | 5 | NULL | RANGE | NULL | `b` | NULL | 5000 | 30647 | 86 | 2637824 | 0 | 0 | 0
156| def | test | t | p6 | NULL | 6 | NULL | RANGE | NULL | `b` | NULL | 6000 | 31104 | 51 | 1589248 | 0 | 0 | 0
157| def | test | t | p7 | NULL | 7 | NULL | RANGE | NULL | `b` | NULL | 7000 | 30642 | 86 | 2637824 | 0 | 0 | 0
158| def | test | t | p8 | NULL | 8 | NULL | RANGE | NULL | `b` | NULL | 8000 | 31756 | 83 | 2637824 | 0 | 0 | 0
159| def | test | t | p9 | NULL | 9 | NULL | RANGE | NULL | `b` | NULL | 9000 | 30637 | 86 | 2637824 | 0 | 0 | 0
160+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------
16132 rows in set (0.06 sec)
162
163mysql>
164mysql> explain select * from t where a=21648 and b=1000;
165+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
166| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
167+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
168| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
169+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1701 row in set, 1 warning (0.00 sec)
171
172mysql> flush status;
173Query OK, 0 rows affected (0.00 sec)
174
175mysql> select * from t where a=21648 and b=1000;
176Empty set (0.00 sec)
177
178mysql> show status like '%handler%';
179+----------------------------+-------+
180| Variable_name | Value |
181+----------------------------+-------+
182| Handler_commit | 1 |
183| Handler_delete | 0 |
184| Handler_discover | 0 |
185| Handler_external_lock | 2 |
186| Handler_mrr_init | 0 |
187| Handler_prepare | 0 |
188| Handler_read_first | 0 |
189| Handler_read_key | 1 |
190| Handler_read_last | 0 |
191| Handler_read_next | 0 |
192| Handler_read_prev | 0 |
193| Handler_read_rnd | 0 |
194| Handler_read_rnd_next | 0 |
195| Handler_rollback | 0 |
196| Handler_savepoint | 0 |
197| Handler_savepoint_rollback | 0 |
198| Handler_update | 0 |
199| Handler_write | 0 |
200+----------------------------+-------+
20118 rows in set (0.00 sec)
202
203mysql>
204mysql>
205mysql> explain select * from t where b=1000;
206+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
207| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
208+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
209| 1 | SIMPLE | t | p2 | ALL | NULL | NULL | NULL | NULL | 31113 | 10.00 | Using where |
210+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2111 row in set, 1 warning (0.00 sec)
212
213mysql> flush status;
214Query OK, 0 rows affected (0.00 sec)
215
216mysql> select * from t where b=1000;
217+--------+------+----------------------+
218| a | b | c |
219+--------+------+----------------------+
220| 71749 | 1000 | 0.6465969957845258 |
221| 115354 | 1000 | 0.6166487490913354 |
222| 142110 | 1000 | 0.3711491081594947 |
223| 175192 | 1000 | 0.41793289353878504 |
224| 205097 | 1000 | 0.9385242331200505 |
225| 206239 | 1000 | 0.540675731879357 |
226| 211328 | 1000 | 0.7131090319837528 |
227| 240083 | 1000 | 0.5763033130916798 |
228| 270072 | 1000 | 0.08806294304138323 |
229| 289682 | 1000 | 0.13271184929899113 |
230| 295634 | 1000 | 0.5078063500186487 |
231| 319122 | 1000 | 0.9986156811924797 |
232| 369080 | 1000 | 0.539861027654131 |
233| 447478 | 1000 | 0.6139470270033432 |
234| 476911 | 1000 | 0.42401163412631643 |
235| 483446 | 1000 | 0.40702808779387556 |
236| 521742 | 1000 | 0.5497648413756535 |
237| 524810 | 1000 | 0.4707333766582733 |
238| 528922 | 1000 | 0.49454311141236046 |
239| 547603 | 1000 | 0.2766120194239654 |
240| 629426 | 1000 | 0.37878758681825137 |
241| 675224 | 1000 | 0.9942706534585642 |
242| 703243 | 1000 | 0.34290753430026355 |
243| 789073 | 1000 | 0.03806874625204946 |
244| 821839 | 1000 | 0.9797167610188171 |
245| 838864 | 1000 | 0.9751406442142471 |
246| 856798 | 1000 | 0.8551231900743425 |
247| 873127 | 1000 | 0.9728078013032747 |
248| 882221 | 1000 | 0.043601875234024486 |
249| 886758 | 1000 | 0.3161585929954039 |
250| 915109 | 1000 | 0.4359569525681035 |
251| 951768 | 1000 | 0.10353198843405767 |
252| 982872 | 1000 | 0.7628593656819866 |
253+--------+------+----------------------+
25433 rows in set (0.01 sec)
255
256mysql> show status like '%handler%';
257+----------------------------+-------+
258| Variable_name | Value |
259+----------------------------+-------+
260| Handler_commit | 1 |
261| Handler_delete | 0 |
262| Handler_discover | 0 |
263| Handler_external_lock | 2 |
264| Handler_mrr_init | 0 |
265| Handler_prepare | 0 |
266| Handler_read_first | 1 |
267| Handler_read_key | 1 |
268| Handler_read_last | 0 |
269| Handler_read_next | 0 |
270| Handler_read_prev | 0 |
271| Handler_read_rnd | 0 |
272| Handler_read_rnd_next | 31018 |
273| Handler_rollback | 0 |
274| Handler_savepoint | 0 |
275| Handler_savepoint_rollback | 0 |
276| Handler_update | 0 |
277| Handler_write | 0 |
278+----------------------------+-------+
27918 rows in set (0.00 sec)
280
281mysql>
282mysql>