Mysql

將列拆分為規範化表

  • December 14, 2012

我有一個非規範化表

原始去規範化表

id  text
23  first,second|third,fourth,fifth|sixth

我想規範化數據庫以創建以下表

表一：（用“|”分割）

id  parent_id   value
1   23          first,second
2   23          third,fourth,fifth
3   23          sixth

然後，

表2（以“，”分隔）

id  parent_id   value
1   1           first
2   1           second
3   2           third
4   2           fourth
5   2           fifth
6   3           sixth

最簡單直接的方法是使用子查詢（SELECT），但我關心的是性能，因為這個過程應該針對一個很長的表並且定期（新數據到來）進行。

有沒有一種有效的方法可以高速規範化這個表？

我創建了一個儲存過程解決方案來執行以下操作：

  • 將 t1 的 txt 欄位轉換為由 ‘|’ 分隔的擴展 INSERT 列表
  • 執行擴展的 INSERT
  • 將 t2 的 value 欄位轉換為由 ‘,’ 分隔的擴展 INSERT 列表
  • 執行擴展的 INSERT

這是範例數據

DROP DATABASE IF EXISTS all_denorm;
CREATE DATABASE all_denorm;
USE all_denorm
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1
(
   id INT NOT NULL,
   txt TEXT
) ENGINE=InnoDB;
INSERT INTO  t1 (id,txt) VALUES
(23,'first,second|third,fourth,fifth|sixth'),
(24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
(25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
(26,'eighteenth,nineteenth|twentieth');
CREATE TABLE t2
(
   id INT NOT NULL AUTO_INCREMENT,
   parent_id INT NOT NULL,
   VALUE VARCHAR(255),
   PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t3 LIKE t2;

這是儲存過程

DELIMITER $$

DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
CREATE PROCEDURE `all_denorm`.`process_denorm` ()
BEGIN

   DECLARE given_id,current_id,last_id,processing INT;
   DECLARE delimiter_char CHAR(1);

   SET current_id = 0;
   SET processing = 1;
   SET delimiter_char = '|';
   WHILE processing = 1 DO
       SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
       IF ISNULL(given_id) THEN
           SET processing = 0;
       ELSE
           SET current_id = given_id;
           SELECT
               REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
               INTO @insert_list FROM t1 WHERE id = given_id;
           SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
           PREPARE stmt FROM @insert_SQL;
           EXECUTE stmt;
           DEALLOCATE PREPARE stmt;
       END IF;
   END WHILE;

   SET current_id = 0;
   SET processing = 1;
   SET delimiter_char = ',';
   WHILE processing = 1 DO
       SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
       IF ISNULL(given_id) THEN
           SET processing = 0;
       ELSE
           SET current_id = given_id;
           SELECT
               REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
               INTO @insert_list FROM t2 WHERE id = given_id;
           SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
           PREPARE stmt FROM @insert_SQL;
           EXECUTE stmt;
           DEALLOCATE PREPARE stmt;
       END IF;
   END WHILE;

   SELECT * FROM t1;
   SELECT * FROM t2;
   SELECT * FROM t3;

END $$

DELIMITER ;

在這裡執行

mysql> DROP DATABASE IF EXISTS all_denorm;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE all_denorm;
Query OK, 1 row affected (0.00 sec)

mysql> USE all_denorm
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1
   -> (
   ->     id INT NOT NULL,
   ->     txt TEXT
   -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO  t1 (id,txt) VALUES
   -> (23,'first,second|third,fourth,fifth|sixth'),
   -> (24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
   -> (25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
   -> (26,'eighteenth,nineteenth|twentieth');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2
   -> (
   ->     id INT NOT NULL AUTO_INCREMENT,
   ->     parent_id INT NOT NULL,
   ->     VALUE VARCHAR(255),
   ->     PRIMARY KEY (id)
   -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t3 LIKE t2;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `all_denorm`.`process_denorm` ()
   -> BEGIN
   ->
   ->     DECLARE given_id,current_id,last_id,processing INT;
   ->     DECLARE delimiter_char CHAR(1);
   ->
   ->     SET current_id = 0;
   ->     SET processing = 1;
   ->     SET delimiter_char = '|';
   ->     WHILE processing = 1 DO
   ->         SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
   ->         IF ISNULL(given_id) THEN
   ->             SET processing = 0;
   ->         ELSE
   ->             SET current_id = given_id;
   ->             SELECT
   ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
   ->                 INTO @insert_list FROM t1 WHERE id = given_id;
   ->             SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
   ->             PREPARE stmt FROM @insert_SQL;
   ->             EXECUTE stmt;
   ->             DEALLOCATE PREPARE stmt;
   ->         END IF;
   ->     END WHILE;
   ->
   ->     SET current_id = 0;
   ->     SET processing = 1;
   ->     SET delimiter_char = ',';
   ->     WHILE processing = 1 DO
   ->         SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
   ->         IF ISNULL(given_id) THEN
   ->             SET processing = 0;
   ->         ELSE
   ->             SET current_id = given_id;
   ->             SELECT
   ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
   ->                 INTO @insert_list FROM t2 WHERE id = given_id;
   ->             SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
   ->             PREPARE stmt FROM @insert_SQL;
   ->             EXECUTE stmt;
   ->             DEALLOCATE PREPARE stmt;
   ->         END IF;
   ->     END WHILE;
   ->
   ->     SELECT * FROM t1;
   ->     SELECT * FROM t2;
   ->     SELECT * FROM t3;
   ->
   -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

這是輸出：

mysql> CALL process_denorm();
+----+-----------------------------------------------------+
| id | txt                                                 |
+----+-----------------------------------------------------+
| 23 | first,second|third,fourth,fifth|sixth               |
| 24 | seventh,eighth|ninth|tenth,eleven|twelfth           |
| 25 | thirteenth|fourteenth|fifteenth|sixteen|seventeenth |
| 26 | eighteenth,nineteenth|twentieth                     |
+----+-----------------------------------------------------+
4 rows in set (0.01 sec)

+----+-----------+-----------------------+
| id | parent_id | VALUE                 |
+----+-----------+-----------------------+
|  1 |        23 | first,second          |
|  2 |        23 | third,fourth,fifth    |
|  3 |        23 | sixth                 |
|  4 |        24 | seventh,eighth        |
|  5 |        24 | ninth                 |
|  6 |        24 | tenth,eleven          |
|  7 |        24 | twelfth               |
|  8 |        25 | thirteenth            |
|  9 |        25 | fourteenth            |
| 10 |        25 | fifteenth             |
| 11 |        25 | sixteen               |
| 12 |        25 | seventeenth           |
| 13 |        26 | eighteenth,nineteenth |
| 14 |        26 | twentieth             |
+----+-----------+-----------------------+
14 rows in set (0.01 sec)

+----+-----------+-------------+
| id | parent_id | VALUE       |
+----+-----------+-------------+
|  1 |         1 | first       |
|  2 |         1 | second      |
|  3 |         2 | third       |
|  4 |         2 | fourth      |
|  5 |         2 | fifth       |
|  6 |         3 | sixth       |
|  7 |         4 | seventh     |
|  8 |         4 | eighth      |
|  9 |         5 | ninth       |
| 10 |         6 | tenth       |
| 11 |         6 | eleven      |
| 12 |         7 | twelfth     |
| 13 |         8 | thirteenth  |
| 14 |         9 | fourteenth  |
| 15 |        10 | fifteenth   |
| 16 |        11 | sixteen     |
| 17 |        12 | seventeenth |
| 18 |        13 | eighteenth  |
| 19 |        13 | nineteenth  |
| 20 |        14 | twentieth   |
+----+-----------+-------------+
20 rows in set (0.04 sec)

Query OK, 0 rows affected (0.10 sec)

mysql>

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/30423

相關問答

Mysql

InnoDB 的規範化和分解表

  • May 14, 2012
檢視問答
Mysql

MySQL 能否在 10GB 網路儲存上使用 raid 與本地 HDD 表現得更好？

  • May 16, 2022
檢視問答
Mysql

如何提高mysql中頻繁更新表的性能？

  • March 23, 2022
檢視問答
Mysql

MySQL - 簡單更新非常慢並且導致大量伺服器的高負載平均

  • March 16, 2022
檢視問答
Mysql

mysql 簡單更新 4000 萬和 128GB RAM 花費太多時間

  • March 3, 2022
檢視問答
Mysql

是否應該將頻繁更新的計數器列儲存在單獨的表中？

  • March 3, 2022
檢視問答