Optimization analysis of the performance of MySQL database table

There are a lot of things that are not equal in our project. Writing this article today is a simple analysis of how to optimize the method.
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.

The code is as follows

Copy Code

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.

The code is as follows

Copy Code

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.

The code is as follows	Copy Code
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.

The code is as follows	Copy Code
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.

The code is as follows

Copy Code

MySQL > select COUNT (*) from t_group where user_name = ' David '; +----------+ | count (*) | +----------+ |  1298576 | The +----------+ 1 row in Set (1.71 sec) Executes for nearly 2 seconds. Mysql> Select COUNT (*) from T_group_david +----------+ | count (*) | +----------+ |  12985 76 | +----------+ 1 row in Set (0.00 sec) is almost instantaneous. Mysql> Select COUNT (*) from T_group where user_name <> ' David '; +----------+ | count (*) | +-- --------+ |  9090032 | The +----------+ 1 row in Set (9.26 sec) executed for nearly 10 seconds, as you can imagine, is intolerable in the actual project. Mysql> Select (select COUNT (*) from T_group)-(SELECT COUNT (*) from t_group_david) as total; +---------+ | total   | +---------+ | 9090032 | +---------+ 1 row in Set (0.00 sec) is almost instantaneous.

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

The code is as follows

Copy Code

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.

The code is as follows	Copy Code
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.

The code is as follows	Copy Code
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.

The code is as follows

Copy Code

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 attribute is superfluous. Then get rid of it.

The code is as follows	Copy Code
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 again on the small table.

The code is as follows

Copy Code

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 time is instantaneous. Mysql> Select SUM (Money), Avg. from T_group_david; +--------------+------------+ | SUM (Money) | AVG (Money) | +--------------+------------+ | 319992383.84 | 246.417910 | +--------------+------------+ 1 row in Set (0.94 sec) The control is within a second. Mysql> aborted

Small sum: the small table attributes as little as possible the better. Go and do it boldly.