MySQL table sharding optimization test _ MySQL

MySQL table sharding optimization experiment there are many unequal situations in our project. This article briefly analyzes how to optimize it.

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;