The table sharding logic here is based on the number of user_name groups in the t_group table.
In this case, the index on the user_name field is a bad index. It does not have any obvious effect.
1. test 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. test table.
We use a table with 10 million records for testing.
Mysql> select count (*) from t_group;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 10388608 |
+ ---------- +
1 row in SET (0.00 Sec)
Table structure.
Mysql> DESC t_group;
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
| ID | int (10) unsigned | no | pri | null | auto_increment |
| Money | decimal () | no |
| User_name | varchar (20) | no | Mul |
| Create_time | timestamp | no | current_timestamp |
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
4 rows in SET (0.00 Sec)
Index information.
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 | btree |
| T_group | 1 | idx_user_name | 1 | user_name | A | 8 | null | btree |
| T_group | 1 | idx_combination1 | 1 | user_name | A | 8 | null | btree |
| T_group | 1 | idx_combination1 | 2 | money | A | 3776 | null | btree |
+ --------- + ------------ + ------------------ + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- +
4 rows in SET (0.00 Sec)
PS:
Idx_combination1 is a required index because user_name must be set to group. This is a loose index scan! Of course, you can kill her.
The index idx_user_name is used to accelerate the execution of queries of the constant type.
Table sharding is performed based on the user name.
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)
So the result table should be like this.
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. Comparison results.
Mysql> select count (*) from t_group where user_name = 'David ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 1298576 |
+ ---------- +
1 row in SET (1.71 Sec)
It took nearly 2 seconds.
Mysql> select count (*) from t_group_david;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 1298576 |
+ ---------- +
1 row in SET (0.00 Sec)
Almost instantly.
Mysql> select count (*) from t_group where user_name <> 'David ';
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 9090032 |
+ ---------- +
1 row in SET (9.26 Sec)
After nearly 10 seconds of execution, we can imagine that this is intolerable in actual projects.
Mysql> select (select count (*) from t_group)-(select count (*) from t_group_david) as total;
+ --------- +
| Total |
+ --------- +
| 1, 9090032 |
+ --------- +
1 row in SET (0.00 Sec)
Almost instantly.
Let's look at the aggregate function.
Operations on the original table.
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)
minimum. The maximum value is full index scan. So it is instantaneous.
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)
the results of other Aggregate functions are not full index scan. It takes 2.15 seconds.
Small table operations.
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)
The maximum and minimum values are full table scan, which takes 1.50 seconds and is not cost-effective. In this case.
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)
It took two seconds to get these two results.
Let's take a look at the structure of this small table.
Mysql> DESC t_group_david;
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
| ID | int (10) unsigned | no | pri | null | auto_increment |
| Money | decimal () | no |
| User_name | varchar (20) | no | Mul |
| Create_time | timestamp | no | current_timestamp |
+ ------------- + ------------------ + ------ + ----- + ------------------- + ---------------- +
4 rows in SET (0.00 Sec)
Obviously, the user_name attribute is redundant. Then it will be killed.
Mysql> alter table t_group_david drop user_name;
Query OK, 1298576 rows affected (7.58 Sec)
Records: 1298576 duplicates: 0 Warnings: 0
Now run the query on the small table again.
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)
This is an instant.
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)
This time, it is within one second.
Mysql> aborted
Summary: the smaller the table to be split, the better. Do it boldly.