Table > 2G cần đánh partition, mỗi partition tầm 100K - 2M rows, Select chỉ rõ Partition, dùng local index
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` WHERE DATE = '2022-01-01'; +----+-------------+-----------------+------------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | TABLE | partitions | TYPE | possible_keys | KEY | key_len | REF | ROWS | filtered | Extra | +----+-------------+-----------------+------------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test_partitions | year2022_jan2022 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | USING WHERE | +----+-------------+-----------------+------------------+------+---------------+------+---------+------+------+----------+-------------+ 1 ROW IN SET, 1 warning (0.11 sec) mysql> EXPLAIN SELECT * FROM `test_partitions` PARTITION (jan2023, feb2023); +----+-------------+-----------------+-----------------------------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | TABLE | partitions | TYPE | possible_keys | KEY | key_len | REF | ROWS | filtered | Extra | +----+-------------+-----------------+-----------------------------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | test_partitions | year2023_jan2023,year2023_feb2023 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-----------------+-----------------------------------+------+---------------+------+---------+------+------+----------+-------+ 1 ROW IN SET, 1 warning (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;