mysql 数据库表被锁住了_Mysql数据库表分区

原因
我们有一张大的数据表5000多万的数据，查询时间跨度比较大的数据的时候，比较慢。

解决办法

解决数据库性能问题方法是多种多样，在不动代码逻辑的情况下，比较节省开发成本的方式就是表分区。

实现

一般分2种方法，第一种是对现有表进行分区，第二种新创建同样字段的分区表，把老表的数据导入。

需求：按月对表分区

首先，为了方便，采用第一种对现有表进行分区。使用 Alter table （更改表）命令。

ALTER TABLE weather_dat PARTITION BY RANGE (unix_timestamp(create_time))
(   
PARTITION p0 VALUES LESS THAN (unix_timestamp('2020-02-01')),
PARTITION p1 VALUES LESS THAN (unix_timestamp('2020-03-01')), 
PARTITION p2 VALUES LESS THAN (unix_timestamp('2020-04-01')), 
PARTITION p3 VALUES LESS THAN (unix_timestamp('2020-05-01')), 
PARTITION p4 VALUES LESS THAN (unix_timestamp('2020-06-01')), 
PARTITION p5 VALUES LESS THAN (unix_timestamp('2020-07-01')), 
PARTITION p6 VALUES LESS THAN (unix_timestamp('2020-08-01')), 
PARTITION p7 VALUES LESS THAN (unix_timestamp('2020-09-01')), 
PARTITION p8 VALUES LESS THAN (unix_timestamp('2020-10-01')), 
PARTITION p9 VALUES LESS THAN (unix_timestamp('2020-11-01')), 
PARTITION p10 VALUES LESS THAN (unix_timestamp('2020-12-01')), 
PARTITION p11 VALUES LESS THAN MAXVALUE );

上面代码意思是，根据月份更改weather_da表作为分区表。Range表示是按范围分区。我现在是按数据记录创建时间作为关键字，按月进行分区。

因为create_time是timestamp类型，我们需要按时间戳进行比对，来划分数据记录应该进那个分区表。

报错：

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

意思是在表的分区函数里面，主键必须包含所有的字段。现在用到“create_time”这个字段，但它不是主键，所以报错了。

navicat调整create_time成主键。结果又报错，提示如下：

并且，反复几次不成功。只能决定采用第二种方式进行分区。

创建空的分区表：

CREATE TABLE `weather_dat1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识ID，主键',
  `road_id` int(11) DEFAULT NULL COMMENT '路段Id',
  `device_id` int(11) NOT NULL,
  `pressure` float(11,1) DEFAULT NULL COMMENT '单位毫巴hPa，1hPa=100帕斯卡',
  `atmospheric_temperature` float(11,1) DEFAULT NULL COMMENT '大气温度,单位：℃',
  `relative_humidity` int(3) DEFAULT NULL COMMENT '相对湿度，单位：%，如85表示相对湿度85%',
  `dew_point_temperature` float(11,1) DEFAULT NULL COMMENT '露点温度，单位：℃，有的有输出，有的需根据温湿度计算',
  `wind_speed` float(11,1) DEFAULT NULL COMMENT 'm/s',
  `wind_direction` int(11) DEFAULT NULL COMMENT '风向，360° ',
  `precipitation` float(11,1) DEFAULT NULL COMMENT '降水量，单位：mm/min，表示每分钟的mm降水量',
  `visibility` int(11) DEFAULT NULL COMMENT '能见度，单位：m',
  `weather_phenomena` varchar(255) DEFAULT NULL COMMENT '天气现象，取决于天气现象传感器的输出',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '精确到分钟，即每分钟一条记录，如“201905202310”',
  PRIMARY KEY (`id`,`create_time`) USING BTREE,
  KEY `idx_weatherDat_deviceIdCreateTime` (`device_id`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=118196010 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
PARTITION BY RANGE (unix_timestamp(create_time))
(PARTITION p0 VALUES LESS THAN (1580486400) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1582992000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1585670400) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (1588262400) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (1590940800) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (1593532800) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (1596211200) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (1598889600) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (1601481600) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (1604160000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (1606752000) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

老表数据导入到新表SQL语句：

insert into weather_dat1  SELECT * FROM weather_dat

5000万数据挂机一个晚上导入成功。导入成功后，把分区表命名weather_dat。

总结：对大数据表直接进行更改为分区表失败的概率很大，可能是因为数据量太大的原因。建议采用新建同字段的分区表，再把数据导入。

另外，关于现在分区表按日期是hardcode了，如果超出分区的时间，表又需要重新分区。如何自动创建分区表是可以探索下一个话题。