MySQL分表优化试验

我们的项目中有好多不等于的情况。今天写这篇文章简单的分析一下怎么个优化法。
这里的分表逻辑是根据t_group表的user_name组的个数来分的。
因为这种情况单独user_name字段上的索引就属于烂索引。起不了啥名明显的效果。

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
1、试验PROCEDURE.
DELIMITER $$
DROP PROCEDURE `t_girl`.`sp_split_table`$$
CREATE  PROCEDURE `t_girl`.`sp_split_table`()
BEGIN
  declare done int default 0;
  declare v_user_name varchar(20) default '';
  declare v_table_name varchar(64) default '';
  -- Get all users' name.
  declare cur1 cursor for select user_name from t_group group by user_name;
  -- Deal with error or warnings.
  declare continue handler for 1329 set done = 1;
  -- Open cursor.
  open cur1;
  while done <> 1
  do 
    fetch cur1 into v_user_name;
    if not done then
      -- Get table name.
      set v_table_name = concat('t_group_',v_user_name);
      -- Create new extra table.
      set @stmt = concat('create table ',v_table_name,' like t_group');
      prepare s1 from @stmt;
      execute s1;
      drop prepare s1;
      -- Load data into it.
      set @stmt = concat('insert into ',v_table_name,' select * from t_group where user_name = ''',v_user_name,'''');
      prepare s1 from @stmt;
      execute s1;
      drop prepare s1;
    end if;
  end while;
  -- Close cursor.
  close cur1;
  -- Free variable from memory.
  set @stmt = NULL;
END$$
 
DELIMITER ;
2、试验表。
我们用一个有一千万条记录的表来做测试。
 
mysql> select count(*) from t_group;
+----------+
| count(*) |
+----------+
| 10388608 | 
+----------+
1 row in set (0.00 sec)
 
表结构。
mysql> desc t_group;
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| money       | decimal(10,2)    | NO   |     |                   |                | 
| user_name   | varchar(20)      | NO   | MUL |                   |                | 
| create_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
 
索引情况。
 
mysql> show index from t_group;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_group |          0 | PRIMARY          |            1 | id          | A         |    10388608 |     NULL | NULL   |      | BTREE      |         | 
| t_group |          1 | idx_user_name    |            1 | user_name   | A         |           8 |     NULL | NULL   |      | BTREE      |         | 
| t_group |          1 | idx_combination1 |            1 | user_name   | A         |           8 |     NULL | NULL   |      | BTREE      |         | 
| t_group |          1 | idx_combination1 |            2 | money       | A         |        3776 |     NULL | NULL   |      | BTREE      |         | 
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)
 
PS:
idx_combination1 这个索引是必须的,因为要对user_name来GROUP BY。此时属于松散索引扫描!当然完了后你可以干掉她。
idx_user_name 这个索引是为了加快单独执行constant这种类型的查询。
我们要根据用户名来分表。
 
mysql> select user_name from t_group where 1 group by user_name;
+-----------+
| user_name |
+-----------+
| david     | 
| leo       | 
| livia     | 
| lucy      | 
| sarah     | 
| simon     | 
| sony      | 
| sunny     | 
+-----------+
8 rows in set (0.00 sec)
 
所以结果表应该是这样的。
mysql> show tables like 't_group_%';
+------------------------------+
| Tables_in_t_girl (t_group_%) |
+------------------------------+
| t_group_david                | 
| t_group_leo                  | 
| t_group_livia                | 
| t_group_lucy                 | 
| t_group_sarah                | 
| t_group_simon                | 
| t_group_sony                 | 
| t_group_sunny                | 
+------------------------------+
8 rows in set (0.00 sec)
 
3、对比结果。
 
 
mysql> select count(*) from t_group where user_name = 'david';
+----------+
| count(*) |
+----------+
|  1298576 | 
+----------+
1 row in set (1.71 sec)
 
执行了将近2秒。
 
mysql> select count(*) from t_group_david;
+----------+
| count(*) |
+----------+
|  1298576 | 
+----------+
1 row in set (0.00 sec)
几乎是瞬间的。
 
mysql> select count(*) from t_group where user_name <> 'david';
+----------+
| count(*) |
+----------+
|  9090032 | 
+----------+
1 row in set (9.26 sec)
执行了将近10秒,可以想象,这个是实际的项目中是不能忍受的。
mysql> select (select count(*) from t_group) - (select count(*) from t_group_david) as total;
+---------+
| total   |
+---------+
| 9090032 | 
+---------+
1 row in set (0.00 sec)
几乎是瞬间的。
 
 
我们来看看聚集函数。
对于原表的操作。
 
mysql> select min(money),max(money) from t_group where user_name = 'david';
+------------+------------+
| min(money) | max(money) |
+------------+------------+
|      -6.41 |     500.59 | 
+------------+------------+
1 row in set (0.00 sec)
最小,最大值都是FULL INDEX SCAN。所以是瞬间的。
mysql> select sum(money),avg(money) from t_group where user_name = 'david';
+--------------+------------+
| sum(money)   | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 | 
+--------------+------------+
1 row in set (2.15 sec)
其他聚集函数的结果就不是FULL INDEX SCAN了。耗时2.15秒。
 
对于小表的操作。
mysql> select min(money),max(money) from t_group_david;
+------------+------------+
| min(money) | max(money) |
+------------+------------+
|      -6.41 |     500.59 | 
+------------+------------+
1 row in set (1.50 sec)
最大最小值完全是FULL TABLE SCAN,耗时1.50秒,不划算。以此看来。
mysql> select sum(money),avg(money) from t_group_david;
+--------------+------------+
| sum(money)   | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 | 
+--------------+------------+
1 row in set (1.68 sec)
 
取得这两个结果也是花了快2秒,快了一点。
 
我们来看看这个小表的结构。
mysql> desc t_group_david;
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL              | auto_increment | 
| money       | decimal(10,2)    | NO   |     |                   |                | 
| user_name   | varchar(20)      | NO   | MUL |                   |                | 
| create_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |                | 
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)
 
明显的user_name属性是多余的。那么就干掉它。
mysql> alter table t_group_david drop user_name;
Query OK, 1298576 rows affected (7.58 sec)
Records: 1298576  Duplicates: 0  Warnings: 0
 
现在来重新对小表运行查询
 
mysql> select min(money),max(money) from t_group_david;
+------------+------------+
| min(money) | max(money) |
+------------+------------+
|      -6.41 |     500.59 | 
+------------+------------+
1 row in set (0.00 sec)
 
此时是瞬间的。
mysql> select sum(money),avg(money) from t_group_david;
+--------------+------------+
| sum(money)   | avg(money) |
+--------------+------------+
| 319992383.84 | 246.417910 | 
+--------------+------------+
1 row in set (0.94 sec)
 
这次算是控制在一秒以内了。
 
mysql> Aborted

小总结一下:分出的小表的属性尽量越少越好。大胆的去干吧。
本文出自 “上帝,咱们不见不散!” 博客,请务必保留此出处http://yueliangdao0608.blog.51cto.com/397025/107356

本文出自 51CTO.COM技术博客

发表评论

电子邮件地址不会被公开。 必填项已用*标注