MySQL Sub-table optimization test Code _MYSQL

The table logic here is divided according to the number of user_name groups in the T_group table.
Because this is the case, the index on the field alone user_name the bad index. Can not play the obvious effect of name.

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 () default ";
DECLARE v_table_name varchar () 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 do <> 1
Todo
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 to do the testing.

Mysql> Select COUNT (*) from T_group;
+----------+
| COUNT (*) |
+----------+
| 10388608 |
+----------+
1 row in Set (0.00 sec)

Table structure.
mysql> desc T_group;
+-------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+----------------+
| ID | int (a) 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)

index condition.

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 This index is necessary because you want to user_name to group by. At this time belong to loose index scan! After all, you can kill her.
Idx_user_name This index is to speed up the individual execution of constant queries of this type.
We need to divide the table by 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 this way.
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. The result of comparison.


Mysql> Select COUNT (*) from t_group where user_name = ' David ';
+----------+
| COUNT (*) |
+----------+
| 1298576 |
+----------+
1 row in Set (1.71 sec)

Executed for nearly 2 seconds.

Mysql> Select COUNT (*) from T_group_david;
+----------+
| COUNT (*) |
+----------+
| 1298576 |
+----------+
1 row in Set (0.00 sec)
It's almost instantaneous.

Mysql> Select COUNT (*) from T_group where user_name <> ' David ';
+----------+
| COUNT (*) |
+----------+
| 9090032 |
+----------+
1 row in Set (9.26 sec)
Executed for nearly 10 seconds, as can be imagined, this is an actual project that is intolerable.
Mysql> Select (select COUNT (*) from T_group)-(SELECT COUNT (*) from t_group_david) as total;
+---------+
| Total |
+---------+
| 9090032 |
+---------+
1 row in Set (0.00 sec)
It's almost instantaneous.


Let's take a look at the aggregate function.
The operation for the original table.

mysql> Select Min, max (money) from t_group where user_name = ' David ';
+------------+------------+
| Min (Money) | Max (Money) |
+------------+------------+
|     -6.41 | 500.59 |
+------------+------------+
1 row in Set (0.00 sec)
The minimum and maximum values are full INDEX SCAN. So it's instantaneous.
Mysql> Select SUM (Money), Avg. 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 the full INDEX scan. Takes 2.15 seconds.

For operations on small tables.
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 minimum value is full TABLE SCAN, which takes 1.5 seconds and is not cost-effective. In this view.
Mysql> Select SUM (Money), Avg. from T_group_david;
+--------------+------------+
| SUM (Money) | AVG (Money) |
+--------------+------------+
| 319992383.84 | 246.417910 |
+--------------+------------+
1 row in Set (1.68 sec)

It took 2 seconds to get these two results, a bit faster.

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 (a) 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)

The obvious user_name property is superfluous. Then get rid of it.
mysql> ALTER TABLE t_group_david drop user_name;
Query OK, 1298576 rows Affected (7.58 sec)
records:1298576  duplicates:0  warnings:0

now come Run the query again on the small table

mysql> Select min, max (money) from T_group_david;
+------------+------------+
| min (Money) |
+------------+------------+
|      -6.41 |     500.59 |
+------------+------------+
1 row in Set (0.00 sec)

This is instantaneous.
mysql> Select sum (Money), Avg. from T_group_david;
+--------------+------------+
| sum (Money)    | avg |
+--------------+------------+
| 319992383.84 | 246.417910 |
+--------------+------------+
1 row in Set (0.94 sec)

The control is within a second.

mysql> aborted

small sum: the smaller the attributes of the small table, the less the better. Go and do it boldly.