TungNT (Blue)

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== Partition ======

Table > 2G cần đánh partition, mỗi partition tầm 100K - 2M rows, Select chỉ rõ Partition, dùng local index

<code>
SELECT
    PLUGIN_NAME as Name,
    PLUGIN_VERSION as Version,
    PLUGIN_STATUS as Status
    FROM INFORMATION_SCHEMA.PLUGINS
    WHERE PLUGIN_TYPE='STORAGE ENGINE';

DROP TABLE IF EXISTS `test_partitions`;
CREATE TABLE `test_partitions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `eid` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int NOT NULL DEFAULT '0',
  `amount` double(20,2) NOT NULL,
  `date` date NOT NULL DEFAULT (curdate()),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`,`date`),
  UNIQUE KEY `test_partitions_eid_date_unique` (`eid`,`date`),
  UNIQUE KEY `test_partitions_request_id_user_id_date_unique` (`request_id`,`user_id`,`date`),
  KEY `test_partitions_eid_index` (`eid`),
  KEY `test_partitions_request_id_index` (`request_id`),
  KEY `test_partitions_user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY RANGE (year(`date`))
SUBPARTITION BY HASH (month(`date`))
(PARTITION year2021 VALUES LESS THAN (2022)
 (SUBPARTITION dec2021 ENGINE = InnoDB,
  SUBPARTITION jan2021 ENGINE = InnoDB,
  SUBPARTITION feb2021 ENGINE = InnoDB,
  SUBPARTITION mar2021 ENGINE = InnoDB,
  SUBPARTITION apr2021 ENGINE = InnoDB,
  SUBPARTITION may2021 ENGINE = InnoDB,
  SUBPARTITION jun2021 ENGINE = InnoDB,
  SUBPARTITION jul2021 ENGINE = InnoDB,
  SUBPARTITION aug2021 ENGINE = InnoDB,
  SUBPARTITION sep2021 ENGINE = InnoDB,
  SUBPARTITION oct2021 ENGINE = InnoDB,
  SUBPARTITION nov2021 ENGINE = InnoDB),
 PARTITION year2022 VALUES LESS THAN (2023)
 (SUBPARTITION dec2022 ENGINE = InnoDB,
  SUBPARTITION jan2022 ENGINE = InnoDB,
  SUBPARTITION feb2022 ENGINE = InnoDB,
  SUBPARTITION mar2022 ENGINE = InnoDB,
  SUBPARTITION apr2022 ENGINE = InnoDB,
  SUBPARTITION may2022 ENGINE = InnoDB,
  SUBPARTITION jun2022 ENGINE = InnoDB,
  SUBPARTITION jul2022 ENGINE = InnoDB,
  SUBPARTITION aug2022 ENGINE = InnoDB,
  SUBPARTITION sep2022 ENGINE = InnoDB,
  SUBPARTITION oct2022 ENGINE = InnoDB,
  SUBPARTITION nov2022 ENGINE = InnoDB),
 PARTITION year2023 VALUES LESS THAN (2024)
 (SUBPARTITION dec2023 ENGINE = InnoDB,
  SUBPARTITION jan2023 ENGINE = InnoDB,
  SUBPARTITION feb2023 ENGINE = InnoDB,
  SUBPARTITION mar2023 ENGINE = InnoDB,
  SUBPARTITION apr2023 ENGINE = InnoDB,
  SUBPARTITION may2023 ENGINE = InnoDB,
  SUBPARTITION jun2023 ENGINE = InnoDB,
  SUBPARTITION jul2023 ENGINE = InnoDB,
  SUBPARTITION aug2023 ENGINE = InnoDB,
  SUBPARTITION sep2023 ENGINE = InnoDB,
  SUBPARTITION oct2023 ENGINE = InnoDB,
  SUBPARTITION nov2023 ENGINE = InnoDB),
 PARTITION year2024 VALUES LESS THAN (2025)
 (SUBPARTITION dec2024 ENGINE = InnoDB,
  SUBPARTITION jan2024 ENGINE = InnoDB,
  SUBPARTITION feb2024 ENGINE = InnoDB,
  SUBPARTITION mar2024 ENGINE = InnoDB,
  SUBPARTITION apr2024 ENGINE = InnoDB,
  SUBPARTITION may2024 ENGINE = InnoDB,
  SUBPARTITION jun2024 ENGINE = InnoDB,
  SUBPARTITION jul2024 ENGINE = InnoDB,
  SUBPARTITION aug2024 ENGINE = InnoDB,
  SUBPARTITION sep2024 ENGINE = InnoDB,
  SUBPARTITION oct2024 ENGINE = InnoDB,
  SUBPARTITION nov2024 ENGINE = InnoDB),
 PARTITION year2025 VALUES LESS THAN (2026)
 (SUBPARTITION dec2025 ENGINE = InnoDB,
  SUBPARTITION jan2025 ENGINE = InnoDB,
  SUBPARTITION feb2025 ENGINE = InnoDB,
  SUBPARTITION mar2025 ENGINE = InnoDB,
  SUBPARTITION apr2025 ENGINE = InnoDB,
  SUBPARTITION may2025 ENGINE = InnoDB,
  SUBPARTITION jun2025 ENGINE = InnoDB,
  SUBPARTITION jul2025 ENGINE = InnoDB,
  SUBPARTITION aug2025 ENGINE = InnoDB,
  SUBPARTITION sep2025 ENGINE = InnoDB,
  SUBPARTITION oct2025 ENGINE = InnoDB,
  SUBPARTITION nov2025 ENGINE = InnoDB)) */ 
	
INSERT INTO test_partitions (`eid`, `request_id`, `user_id`, `amount`, `date`, `created_at`, `updated_at`) VALUES ('EID001', 'REQUESTID001', 1, 10000, '2023-01-01', '2023-01-01 15:53:45', '2023-01-01 15:53:45'); 
INSERT INTO test_partitions (`eid`, `request_id`, `user_id`, `amount`, `date`, `created_at`, `updated_at`) VALUES ('EID002', 'REQUESTID002', 1, 20000, '2023-02-01', '2023-02-01 15:53:45', '2023-01-02 15:53:45'); 
INSERT INTO test_partitions (`eid`, `request_id`, `user_id`, `amount`, `date`, `created_at`, `updated_at`) VALUES ('EID003', 'REQUESTID003', 1, 30000, '2023-03-01', '2023-03-01 15:53:45', '2023-01-03 15:53:45'); 
INSERT INTO test_partitions (`eid`, `request_id`, `user_id`, `amount`, `date`, `created_at`, `updated_at`) VALUES ('EID004', 'REQUESTID004', 1, 40000, '2023-04-01', '2023-04-01 15:53:45', '2023-01-04 15:53:45'); 
</code>

<code>
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'test_partitions';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| year2021       | dec2021           |          0 |
| year2021       | jan2021           |          0 |
| year2021       | feb2021           |          0 |
| year2021       | mar2021           |          0 |
| year2021       | apr2021           |          0 |
| year2021       | may2021           |          0 |
| year2021       | jun2021           |          0 |
| year2021       | jul2021           |          0 |
| year2021       | aug2021           |          0 |
| year2021       | sep2021           |          0 |
| year2021       | oct2021           |          0 |
| year2021       | nov2021           |          0 |
| year2022       | dec2022           |          0 |
| year2022       | jan2022           |          0 |
| year2022       | feb2022           |          0 |
| year2022       | mar2022           |          0 |
| year2022       | apr2022           |          0 |
| year2022       | may2022           |          0 |
| year2022       | jun2022           |          0 |
| year2022       | jul2022           |          0 |
| year2022       | aug2022           |          0 |
| year2022       | sep2022           |          0 |
| year2022       | oct2022           |          0 |
| year2022       | nov2022           |          0 |
| year2023       | dec2023           |          0 |
| year2023       | jan2023           |          1 |
| year2023       | feb2023           |          1 |
| year2023       | mar2023           |          1 |
| year2023       | apr2023           |          1 |
| year2023       | may2023           |          0 |
| year2023       | jun2023           |          0 |
| year2023       | jul2023           |          0 |
| year2023       | aug2023           |          0 |
| year2023       | sep2023           |          0 |
| year2023       | oct2023           |          0 |
| year2023       | nov2023           |          0 |
| year2024       | dec2024           |          0 |
| year2024       | jan2024           |          0 |
| year2024       | feb2024           |          0 |
| year2024       | mar2024           |          0 |
| year2024       | apr2024           |          0 |
| year2024       | may2024           |          0 |
| year2024       | jun2024           |          0 |
| year2024       | jul2024           |          0 |
| year2024       | aug2024           |          0 |
| year2024       | sep2024           |          0 |
| year2024       | oct2024           |          0 |
| year2024       | nov2024           |          0 |
| year2025       | dec2025           |          0 |
| year2025       | jan2025           |          0 |
| year2025       | feb2025           |          0 |
| year2025       | mar2025           |          0 |
| year2025       | apr2025           |          0 |
| year2025       | may2025           |          0 |
| year2025       | jun2025           |          0 |
| year2025       | jul2025           |          0 |
| year2025       | aug2025           |          0 |
| year2025       | sep2025           |          0 |
| year2025       | oct2025           |          0 |
| year2025       | nov2025           |          0 |
+----------------+-------------------+------------+
60 rows in set (0.00 sec)
</code>

<code>
mysql> SELECT * FROM `test_partitions` PARTITION (year2023);
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
| id | eid    | request_id   | user_id | amount   | date       | created_at          | updated_at          |
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
|  1 | EID001 | REQUESTID001 |       1 | 10000.00 | 2023-01-01 | 2023-01-01 15:53:45 | 2023-01-01 15:53:45 |
|  2 | EID002 | REQUESTID002 |       1 | 20000.00 | 2023-02-01 | 2023-02-01 15:53:45 | 2023-01-02 15:53:45 |
|  3 | EID003 | REQUESTID003 |       1 | 30000.00 | 2023-03-01 | 2023-03-01 15:53:45 | 2023-01-03 15:53:45 |
|  4 | EID004 | REQUESTID004 |       1 | 40000.00 | 2023-04-01 | 2023-04-01 15:53:45 | 2023-01-04 15:53:45 |
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM `test_partitions` PARTITION (jan2023, feb2023);
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
| id | eid    | request_id   | user_id | amount   | date       | created_at          | updated_at          |
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
|  1 | EID001 | REQUESTID001 |       1 | 10000.00 | 2023-01-01 | 2023-01-01 15:53:45 | 2023-01-01 15:53:45 |
|  2 | EID002 | REQUESTID002 |       1 | 20000.00 | 2023-02-01 | 2023-02-01 15:53:45 | 2023-01-02 15:53:45 |
+----+--------+--------------+---------+----------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `test_partitions` PARTITION (year2023);
+----+-------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table           | partitions                                                                                                                                                                                                  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_partitions | year2023_dec2023,year2023_jan2023,year2023_feb2023,year2023_mar2023,year2023_apr2023,year2023_may2023,year2023_jun2023,year2023_jul2023,year2023_aug2023,year2023_sep2023,year2023_oct2023,year2023_nov2023 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ALTER TABLE `test_partitions` ADD PARTITION (PARTITION year2026 VALUES LESS THAN (2027)(
	SUBPARTITION dec2026 ENGINE = InnoDB,
  SUBPARTITION jan2026 ENGINE = InnoDB,
  SUBPARTITION feb2026 ENGINE = InnoDB,
  SUBPARTITION mar2026 ENGINE = InnoDB,
  SUBPARTITION apr2026 ENGINE = InnoDB,
  SUBPARTITION may2026 ENGINE = InnoDB,
  SUBPARTITION jun2026 ENGINE = InnoDB,
  SUBPARTITION jul2026 ENGINE = InnoDB,
  SUBPARTITION aug2026 ENGINE = InnoDB,
  SUBPARTITION sep2026 ENGINE = InnoDB,
  SUBPARTITION oct2026 ENGINE = InnoDB,
  SUBPARTITION nov2026 ENGINE = InnoDB
));

# Insert 10M records to transactions table
DROP PROCEDURE IF EXISTS insertRecords;
DELIMITER $$  
CREATE PROCEDURE insertRecords()
BEGIN
		SET @a = 0;
		insert_loop: LOOP
			 SET @a=@a+1;
				INSERT INTO transactions (`request_id`, user_id, amount, _month, created_at, updated_at) VALUES (@a, @a, 1000, 202301 , '2023-01-01 15:53:45', '2023-01-01 15:53:45');
			 IF @a=10000000 THEN
					LEAVE insert_loop;
			 END IF;
   END LOOP insert_loop;
END $$

CALL insertRecords();

INSERT INTO transactions_1 SELECT * FROM transactions;
</code>

Please fill all the letters into the box to prove you're human. O᠎ C B G J Please keep this field empty:

Edit summary