Shulou(Shulou.com)06/02 Report--

This article mainly explains "MYSQL partition test command". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn the "MYSQL table partition test commands"!

Reference: https://blog.csdn.net/fengnp/article/details/81239286

-1. Create a partition table

CREATE TABLE part_tab (

C1 INT DEFAULT NULL

C2 VARCHAR (30) DEFAULT NULL

C3 DATE NOT NULL

) PARTITION BY RANGE (YEAR (c3)) (

PARTITION p0 VALUES LESS THAN (1995)

PARTITION p1 VALUES LESS THAN (1996)

PARTITION p2 VALUES LESS THAN (1997)

PARTITION p3 VALUES LESS THAN (1998)

PARTITION p4 VALUES LESS THAN (1999)

PARTITION p5 VALUES LESS THAN (2000)

PARTITION p6 VALUES LESS THAN (2001)

PARTITION p7 VALUES LESS THAN (2002)

PARTITION p8 VALUES LESS THAN (2003)

PARTITION p9 VALUES LESS THAN (2004)

PARTITION p10 VALUES LESS THAN (2010)

PARTITION p11 VALUES LESS THAN (MAXVALUE)

);

-- 2. Create a non-partitioned table

CREATE TABLE no_part_tab (

C1 INT DEFAULT NULL

C2 VARCHAR (30) DEFAULT NULL

C3 DATE NOT NULL

);

-3. Query

SELECT * FROM part_tab

SELECT * FROM no_part_tab

-- 4. Stored procedure inserts data

DROP PROCEDURE IF EXISTS load_part_tab

DELIMITER / /

CREATE PROCEDURE load_part_tab ()

BEGIN

DECLARE v INT DEFAULT 0

WHILE v

< 8000000 DO INSERT INTO part_tab VALUES (v, 'testing partitions', ADDDATE('1995-01-01', (RAND(v)*36520)MOD 3652)); SET v = v + 1; END WHILE; END; // CALL load_part_tab; INSERT INTO no_part_tab SELECT * FROM part_tab; -- 5. 测试查询效率 SELECT COUNT(*) FROM part_tab WHERE c3 >

DATE '1995-01-01'AND c3

< DATE '1995-12-31'; SELECT COUNT(*) FROM no_part_tab WHERE c3 >

DATE '1995-01-01'AND c3

< DATE '1995-12-31'; -- 6. 通过explain语句来分析执行情况 EXPLAIN SELECT COUNT(*) FROM part_tab WHERE c3 >

DATE '1995-01-01'AND c3

< DATE '1995-12-31'; EXPLAIN SELECT COUNT(*) FROM no_part_tab WHERE c3 >

DATE '1995-01-01'AND c3 < DATE' 1995-12-31'

At this point, I believe that everyone on the "MYSQL table partition test command" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.