背景 ：a服务器数据库磁盘已满，但是没有新的磁盘支持，只好想办法删数据省空间
首先 要看一下各表行记录
执行sql：select table_name,table_rows from tables where table_schema = 'axxxx' ORDER BY TABLE_ROWS DESC
谁最胖就删谁初步锁定前4名，优先txx表，
执行SELECTCONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ' GB') AS 'Tot
al Data Size' FROMinformation_schema.TABLES WHERE table_name LIKE 'txx'
发现这个表竟然达到8G，另外排名越靠前并不代表着占的空间越大，和表的存储内容有关。经与开发沟通txx 主要是第三方缓存信息，没多大用可以删除。为了方便以后维护删除，决定对该表进行按月分区（range），数据只保留两个月。
alter table txx
partition by range(to_days(createtime)) partitions 47 (
partition txx_1803 values less than (to_days('2018-04-01')),
partition txx_1804 values less than (to_days('2018-05-01')),
partition txx_1805 values less than (to_days('2018-06-01')),
partition txx_1806 values less than (to_days('2018-07-01')),
partition txx_1807 values less than (to_days('2018-08-01')),
partition txx_1808 values less than (to_days('2018-09-01')),
partition txx_1809 values less than (to_days('2018-10-01')),
partition txx_1810 values less than (to_days('2018-11-01')),
partition txx_1811 values less than (to_days('2018-12-01')),
partition txx_1812 values less than (to_days('2019-01-01')),
partition txx_1901 values less than (to_days('2019-02-01')),
partition txx_1902 values less than (to_days('2019-03-01')),
partition txx_1903 values less than (to_days('2019-04-01')),
partition txx_1904 values less than (to_days('2019-05-01')),
partition txx_1905 values less than (to_days('2019-06-01')),
partition txx_1906 values less than (to_days('2019-07-01')),
partition txx_1907 values less than (to_days('2019-08-01')),
partition txx_1908 values less than (to_days('2019-09-01')),
partition txx_1909 values less than (to_days('2019-10-01')),
partition txx_1910 values less than (to_days('2019-11-01')),
partition txx_1911 values less than (to_days('2019-12-01')),
partition txx_1912 values less than (to_days('2020-01-01')),
partition txx_2001 values less than (to_days('2020-02-01')),
partition txx_2002 values less than (to_days('2020-03-01')),
partition txx_2003 values less than (to_days('2020-04-01')),
partition txx_2004 values less than (to_days('2020-05-01')),
partition txx_2005 values less than (to_days('2020-06-01')),
partition txx_2006 values less than (to_days('2020-07-01')),
partition txx_2007 values less than (to_days('2020-08-01')),
partition txx_2008 values less than (to_days('2020-09-01')),
partition txx_2009 values less than (to_days('2020-10-01')),
partition txx_2010 values less than (to_days('2020-11-01')),
partition txx_2011 values less than (to_days('2020-12-01')),
partition txx_2012 values less than (to_days('2021-01-01')),
partition txx_2101 values less than (to_days('2021-02-01')),
partition txx_2102 values less than (to_days('2021-03-01')),
partition txx_2103 values less than (to_days('2021-04-01')),
partition txx_2104 values less than (to_days('2021-05-01')),
partition txx_2105 values less than (to_days('2021-06-01')),
partition txx_2106 values less than (to_days('2021-07-01')),
partition txx_2107 values less than (to_days('2021-08-01')),
partition txx_2108 values less than (to_days('2021-09-01')),
partition txx_2109 values less than (to_days('2021-10-01')),
partition txx_2110 values less than (to_days('2021-11-01')),
partition txx_2111 values less than (to_days('2021-12-01')),
partition txx_2112 values less than (to_days('2022-01-01')),
partition txx_max values less than maxvalue)
结果，报错：alter table t1 add PRIMARY KEY(ID,createtime) 报错1503 - A PRIMARY KEY must include all columns in the table's partiti
MySQL要求分区字段必须包含在主键字段内，但是这个表已经有主键了，所以要删除原先主键创建一个复合主键。
首先 先删除原主键alter table txx drop
primary key; 删除主键。
然后创建复合主键
Alter table txx add primary key(nid,createtime)
注意 在MySQL中去掉一个表格的主键需要分2中情况：
1、该列（column）不光设置为主键（primary key），还有自增长（auto_increment）；
句式：alter table +表名+modify id int, drop primary key;
2、如果没有设置为自增长（auto_increment），那么可以直接删除主键（primary key ）;
句式：alter table +表名+dropprimary key;
最后执行成功 分区完成
ps：由于磁盘已满，该表有比较大，所以删除主键也不会成功，会报错 tables is full的提示。最后只好使用 delete from txx where 的语句分几次再夜里服务较闲的时候删除部分数据，然后optimize table 释放空间，最终才删除主键成功。
alter table txx
drop partition t_ts_notice_1812;删除分区到18年12月，再看一空间
该表已经变成1G，达到目的。
