#set @@global.sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION'; create database test; use test; set global innodb_lock_wait_timeout=1000; DROP TABLE IF EXISTS delete_tbtest_part; DROP TABLE IF EXISTS detele_tbtest_part_2; DROP TABLE IF EXISTS delete_tbtest_no_part; DROP TABLE IF EXISTS delete_tbtest_part_01; DROP TABLE IF EXISTS delete_tbtest_part_02; DROP TABLE IF EXISTS delete_tbtest_no_part_01; DROP TABLE IF EXISTS delete_tbtest_no_part_02; CREATE TABLE IF NOT EXISTS delete_tbtest_part ( id INT, tinyint_field TINYINT, smallint_field SMALLINT, bigint_field BIGINT, float_field FLOAT, datetime_field DATETIME, timestamp_field TIMESTAMP, char_field CHAR(8), varchar_field VARCHAR(10), text_field TEXT(20), PRIMARY KEY (id) )ENGINE=Innodb PARTITION BY HASH(id DIV 10000) PARTITIONS 6; CREATE TABLE IF NOT EXISTS detele_tbtest_part_2 ( id INT, tinyint_field TINYINT, smallint_field SMALLINT, bigint_field BIGINT, float_field FLOAT, datetime_field DATETIME, timestamp_field TIMESTAMP, char_field CHAR(8), varchar_field VARCHAR(10), text_field TEXT(20), PRIMARY KEY (id) )ENGINE=Innodb PARTITION BY HASH(id DIV 10000) PARTITIONS 6; CREATE TABLE IF NOT EXISTS delete_tbtest_no_part ( id INT, tinyint_field TINYINT, smallint_field SMALLINT, bigint_field BIGINT, float_field FLOAT, datetime_field DATETIME, timestamp_field TIMESTAMP, char_field CHAR(8), varchar_field VARCHAR(10), text_field TEXT(20), PRIMARY KEY (id) )ENGINE=Innodb ; CREATE TABLE IF NOT EXISTS delete_tbtest_no_part_01 like delete_tbtest_no_part; CREATE TABLE IF NOT EXISTS delete_tbtest_no_part_02 like delete_tbtest_no_part; CREATE TABLE IF NOT EXISTS delete_tbtest_part_01 like delete_tbtest_part; CREATE TABLE IF NOT EXISTS delete_tbtest_part_02 like delete_tbtest_part; TRUNCATE TABLE delete_tbtest_part; TRUNCATE TABLE detele_tbtest_part_2; TRUNCATE TABLE delete_tbtest_no_part; TRUNCATE TABLE delete_tbtest_part_01; TRUNCATE TABLE delete_tbtest_part_02; TRUNCATE TABLE delete_tbtest_no_part_01; TRUNCATE TABLE delete_tbtest_no_part_02; DROP PROCEDURE IF EXISTS load_tbtest_delete; DELIMITER $$ CREATE PROCEDURE load_tbtest_delete( id_count INT) BEGIN SET @id = 1; SET @tinyint_f = 1; SET @smallint_f = 1; SET @bigint_f = 1; SET @float_f = 1; SET @lsql = ''; WHILE @id <= id_count DO SET @datetime_f = '2018-08-08'; SET @timestamp_f = '2018-08-08'; SET @char_f = @id ; SET @varchar_f = concat(repeat('a',10-length(@id)),@id); SET @text_f = concat(repeat('a',20-length(@id)),@id); SET @lsql = CONCAT('(',@id,',', @tinyint_f,',', @smallint_f,',', @bigint_f,',', @float_f,',\'', @datetime_f,'\',\'', @timestamp_f,'\',\'', @char_f,'\',\'', @varchar_f,'\',\'', @text_f,'\'),',@lsql); IF @id % 10000 = 0 THEN SET @lsql=left(@lsql,CHAR_LENGTH(@lsql)-1); SET @lsql = CONCAT('INSERT INTO delete_tbtest_part VALUES',@lsql,';'); PREPARE st_insert FROM @lsql; EXECUTE st_insert ; COMMIT; SET @lsql = ''; DEALLOCATE PREPARE st_insert; END IF; SET @id = @id + 1; SET @float_f = @float_f + 1; SET @bigint_f = @bigint_f + 1; END WHILE; COMMIT; END ; $$ DELIMITER ; call load_tbtest_delete(180000); INSERT INTO detele_tbtest_part_2 SELECT * from delete_tbtest_part ; INSERT INTO delete_tbtest_no_part SELECT * from delete_tbtest_part ; INSERT INTO delete_tbtest_no_part_01 SELECT * from delete_tbtest_part ; INSERT INTO delete_tbtest_no_part_02 SELECT * from delete_tbtest_part ; INSERT INTO delete_tbtest_part_01 SELECT * from delete_tbtest_part ; INSERT INTO delete_tbtest_part_02 SELECT * from delete_tbtest_part ; CREATE UNIQUE INDEX idx_uni ON delete_tbtest_part(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_part(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_part(float_field desc); CREATE INDEX idx_d ON delete_tbtest_part(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest as SELECT * FROM delete_tbtest_part ; CREATE UNIQUE INDEX idx_uni ON delete_tbtest_part_01(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_part_01(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_part_01(float_field desc); CREATE INDEX idx_d ON delete_tbtest_part_01(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest_01 as SELECT * FROM delete_tbtest_part_01 ; CREATE UNIQUE INDEX idx_uni ON delete_tbtest_part_02(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_part_02(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_part_02(float_field desc); CREATE INDEX idx_d ON delete_tbtest_part_02(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest_02 as SELECT * FROM delete_tbtest_part_02 ; CREATE UNIQUE INDEX idx_uni ON detele_tbtest_part_2(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON detele_tbtest_part_2(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON detele_tbtest_part_2(float_field desc); CREATE INDEX idx_d ON detele_tbtest_part_2(datetime_field asc, timestamp_field desc); CREATE UNIQUE INDEX idx_uni ON delete_tbtest_no_part(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_no_part(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_no_part(float_field desc); CREATE INDEX idx_d ON delete_tbtest_no_part(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest_no as SELECT * FROM delete_tbtest_no_part ; CREATE UNIQUE INDEX idx_uni ON delete_tbtest_no_part_01(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_no_part_01(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_no_part_01(float_field desc); CREATE INDEX idx_d ON delete_tbtest_no_part_01(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest_no_01 as SELECT * FROM delete_tbtest_no_part_01 ; CREATE UNIQUE INDEX idx_uni ON delete_tbtest_no_part_02(id,tinyint_field asc, smallint_field asc, bigint_field asc); CREATE INDEX idx_f ON delete_tbtest_no_part_02(char_field asc, varchar_field desc, text_field(20) asc); CREATE INDEX idx_n ON delete_tbtest_no_part_02(float_field desc); CREATE INDEX idx_d ON delete_tbtest_no_part_02(datetime_field asc, timestamp_field desc); CREATE OR REPLACE VIEW delete_v_tbtest_no_02 as SELECT * FROM delete_tbtest_no_part_02 ; commit;