Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve sub-table optimization in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The subtable logic here is divided according to the number of user_name groups in the t_group table.

Because in this case, an index on a separate user_name field is a bad index. It doesn't have any obvious effect.

1. Test PROCEDURE.

DELIMITER $$

Drop PROCEDURE `t _ room`.`sp _ split_ table` $$

Create PROCEDURE `t _ room`.`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 ('tweeted groupmakers and other names)

-- Create new extra table.

Set @ stmt = concat ('create table', 'like tasking group')

Prepare S1 from @ stmt

Execute s1

Drop prepare s1

-- Load data into it.

Set @ stmt = concat ('insert into', 'select * from t_group where user_name =', vroomusername'')

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 tested it with a table with 10 million records.

> 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 (10) unsigned | NO | PRI | NULL | auto_increment |

| | money | decimal (106.2) | NO |

| | user_name | varchar (20) | NO | MUL |

| | create_time | timestamp | NO | | CURRENT_TIMESTAMP |

+-+ +

4 rows in set (0.00 sec)

Index status.

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:

The idx_combination1 index is necessary because it is necessary to GROUP BY the user_name. This is a loose index scan! Of course you can kill her when it's over.

The idx_user_name index is designed to speed up the execution of separate queries of the type constant.

We need to divide the table according to 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 look like this.

Mysql > show tables like'tasking grouping%'

+-+

| | Tables_in_t_girl (tasking grouping%) | |

+-+

| | 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. Compare the results.

Mysql > select count (*) from t_group where user_name = 'david'

+-+

| | count (*) |

+-+

| | 1298576 |

+-+

1 row in set (1.71 sec)

It took nearly 2 seconds to execute.

Mysql > select count (*) from t_group_david

+-+

| | count (*) |

+-+

| | 1298576 |

+-+

1 row in set (0.00 sec)

Almost instantly.

Mysql > select count (*) from t_group where user_name 'david'

+-+

| | count (*) |

+-+

| | 9090032 |

+-+

1 row in set (9.26 sec)

It has been implemented for nearly 10 seconds, and it is conceivable that this is unbearable 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)

Almost instantly.

Let's take a look at the aggregation function.

The operation 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)

The minimum and maximum values are both FULL INDEX SCAN. So it's 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 result of other aggregate functions is not FULL INDEX SCAN. It took 2.15 seconds.

The operation on the small table.

Mysql > select min (money), max (money) from t_group_david

+-+ +

| | min (money) | max (money) | |

+-+ +

| |-6.41 | 500.59 | |

+-+ +

1 row in set (1.5 sec)

The maximum and minimum value is completely FULL TABLE SCAN, which takes 1.50 seconds and is not cost-effective. From this point of view.

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 also took 2 seconds to achieve these two results, which is a little faster.

Let's take a look at the structure of this small watch.

Mysql > desc t_group_david

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (10) unsigned | NO | PRI | NULL | auto_increment |

| | money | decimal (106.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 redundant. Then kill 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 let's rerun the query against the small table

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)

It was controlled within a second this time.

Mysql > Aborted

To sum up: the attributes of the separated small tables are as few as possible.

This is the end of this article on "how to achieve sub-table optimization in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.