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

How to optimize the sub-table in MySQL, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

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 (tasking grouprecording name vested username)

-- 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 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 & n |

This is the answer to the question about how to optimize the sub-table in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.