mg4377娱乐娱城官网_mg4377娱乐手机版_www.mg4377.com

当前位置: mg4377娱乐娱城官网 > mg > 正文

分表优化考试代码,MySQL分表优化

时间:2019-06-29 15:50来源:mg
1、试验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

1、试验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$$

此间的分表逻辑是依照t_group表的user_name组的个数来分的。
因为这种气象单独user_name字段上的目录就属于烂索引。起持续啥名显然的功能。

此处的分表逻辑是依据t_group表的user_name组的个数来分的。
因为这种情状单独user_name字段上的目录就属于烂索引。起不断啥名明显的效应。

作者们的种类中有许多不等于的景况。前几日写那篇小说轻巧的深入分析一下怎么个优化法。
 
  这里的分表逻辑是根据t_group表的user_name组的个数来分的。
分表优化考试代码,MySQL分表优化。  因为这种景观单独user_name字段上的目录就属于烂索引。起持续啥名显明的功用。
  1、试验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、试验表。
  大家用二个有壹仟万条记下的表来做测量试验。
 
mysql> select count(*) from t_group;
----------
| count(*) |
----------
| 10388608 |
----------
1 row in set (0.00 sec)
 
  表结构。
 
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)
 
  索引情形。
 
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 以此目录是必须的,因为要对user_name来GROUP BY。此时属于松散索引围观!当然完了后您能够杀死他。
  idx_user_name 这一个目录是为着加紧单独实行constant那类别型的询问。
  大家要基于用户名来分表
 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)
 
  所以结果表应该是如此的。
 
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、相比较结果。
 
mysql> select count(*) from t_group where user_name = 'david';
----------
| count(*) |
----------
| 1298576 |
----------
1 row in set (1.71 sec)
 
  实践了邻近2秒。
 
mysql> select count(*) from t_group_david;
----------
| count(*) |
----------
| 1298576 |
----------
1 row in set (0.00 sec)
 
  差不多是转弹指的。
 
mysql> select count(*) from t_group where user_name <> 'david';
----------
| count(*) |
----------
| 9090032 |
----------
1 row in set (9.26 sec)
试行了近乎10秒,能够想像,这几个是事实上的项目中是不能够忍受的。
mysql> select (select count(*) from t_group) - (select count(*) from t_group_david) as total;
---------
| total |
---------
| 9090032 |
---------
1 row in set (0.00 sec)
 
  大致是须臾间的。
  我们来探望集中函数。
  对于原表的操作。
 
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)
微小,最大值都以FULL INDEX SCAN。所以是弹指间的。
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)
其他聚集函数的结果就不是FULL INDEX SCAN了。耗费时间2.15秒。
 
  对于小表的操作。
 
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)
 
  最大十分小值完全部是FULL TABLE SCAN,耗费时间1.50秒,不划算。以此看来。
 
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)
 
  取得那四个结实也是花了快2秒,快了有个别。
  大家来探望这一个小表的结构。
 
mysql> desc t_group_david;
------------- ------------------ ------ ----- ------------------- ----------------
| 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)
 
  明显的user_name属性是剩下的。那么就干掉它。
 
mysql> alter table t_group_david drop user_name;
Query OK, 1298576 rows affected (7.58 sec)
Records: 1298576 Duplicates: 0 Warnings: 0
 
  现在来重新对小表运维查询
 
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)
 
  此时是一弹指顷的。
 
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)
 
  本次终于调整在一秒之内了。
  mysql> Aborted
  小计算一下:分出的小表的属性尽量越少越好。大胆的去干啊
 
 
 
第零空间版权全体  

DELIMITER ;
2、试验表。
作者们用贰个有1000万条记下的表来做测量检验。

1、试验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$$

1、试验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$$

这里的分表逻辑是依照t_group表的user_name组的个数来分的...

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

DELIMITER ;
2、试验表。
大家用二个有一千万条记下的表来做测量检验。

DELIMITER ;
2、试验表。
我们用八个有一千万条记下的表来做测量试验。

表结构。
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)

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

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

目录情状。

表结构。
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)

表结构。
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)

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 以此目录是必须的,因为要对user_name来GROUP BY。此时属于松散索引围观!当然完了后您能够杀死他。
idx_user_name 这一个目录是为着加快单独施行constant那类别型的询问。
大家要依靠用户名来分表。

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)

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)

编辑:mg 本文来源:分表优化考试代码,MySQL分表优化

关键词: