我們的項(xiàng)目中有好多不等于的情況。今天寫這篇文章簡單的分析一下怎么個(gè)優(yōu)化法。
這里的分表邏輯是根據(jù)t_group表的user_name組的個(gè)數(shù)來分的。
因?yàn)檫@種情況單獨(dú)user_name字段上的索引就屬于爛索引。起不了啥名明顯的效果。

1、試驗(yàn)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 ;
2、試驗(yàn)表。
我們用一個(gè)有一千萬條記錄的表來做測試。

mysql> select count(*) from t_group;
+----------+
| count(*) |
+----------+
| 10388608 |
+----------+
1 row in set (0.00 sec)

表結(jié)構(gòu)。
mysql> desc t_group;
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) 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)