MySQL query efficiency after partitioning

Preparatory work:

　　Worm Copy article table adds data to 1.12 million

Syntax: INSERT INTO TABLENAMEA select * from Tablenameb

Non-partitioned query 54s

　　

Changing an existing table partition

Alter Tablet_articlepartition byRANGE (Aid) (PARTITION p0VALUESLess THAN (200000), PARTITION p1VALUESLess THAN (400000), PARTITION p2VALUESLess THAN (600000), PARTITION P3VALUESLess THAN (800000), PARTITION P4VALUESLess THAN (1000000), PARTITION P5VALUESLess THAN (2000000), PARTITION PmaxVALUESLess THAN MAXVALUE);

To delete a table partition

ALTER TABLE DROP PARTITION partition name;   

query time after partition 1.2 s

总结：mysql 中分区可大大增加查询速度，原理和横向分割数据一样，只不过mysql自身做了处理。

MySQL query efficiency after partitioning