分类字段和时间戳列(varchar)上的mysql分区

Question

目前我们有表:

CREATE TABLE `T_TRANS` (
  `CASE_ID` varchar(20) DEFAULT NULL,
  `C_ID` varchar(20) DEFAULT NULL,
  `C_ST_IND` smallint(6) DEFAULT NULL,
  `D_DTTM` int(11) DEFAULT NULL,
  `E_ID` varchar(10) DEFAULT NULL,
  `E_LONG` decimal(11,7) DEFAULT NULL,
  `E_LAT` decimal(9,7) DEFAULT NULL,
  `EV_IND` smallint(6) DEFAULT NULL,
  `H_B_IND` smallint(6) DEFAULT NULL,
  `V_IND` varchar(15) DEFAULT NULL,
  `I_IND` smallint(6) DEFAULT NULL,
  `I_P_IND` smallint(6) DEFAULT NULL,
  `I_S_IND` smallint(6) DEFAULT NULL,
  `IS_D_IND` smallint(6) DEFAULT NULL,
  `IS_R_IND` smallint(6) DEFAULT NULL,
  `L_IND` smallint(6) DEFAULT NULL,
  `D_LONG` decimal(11,7) DEFAULT NULL,
  `D_LAT` decimal(9,7) DEFAULT NULL,
  `L_P_C_DTTM` int(11) DEFAULT NULL,
  `L_T_E_DTTM` int(11) DEFAULT NULL,
  `M_IND` varchar(20) DEFAULT NULL,
  `N_D_COUNTER` smallint(6) DEFAULT NULL,
  `O_ID` smallint(6) NOT NULL,
  `P_ID` varchar(50) DEFAULT NULL,
  `R_E_IND` smallint(6) DEFAULT NULL,
  `R_IND` smallint(6) DEFAULT NULL,
  `S_C_DTTM` varchar(20) DEFAULT NULL,
  `S_IND` smallint(6) DEFAULT NULL,
  `T_T_RED` varchar(20) DEFAULT NULL,
  `U_D` int(11) DEFAULT NULL,
  `V_D` int(11) DEFAULT NULL,
  `CRT_USR_NAM` varchar(45) DEFAULT NULL,
  `CRT_DTTM` varchar(45) DEFAULT NULL,
  `UPD_USR_NAM` varchar(45) DEFAULT NULL,
  `UPD_DTTM` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我的 where 查询将在以下列上查找特定值或值组合

C_ST_IND values range from (0,1,2,3,4,5,6,7,8,9,10,11,12)
E_IND values range from (0,1,2,3,4,5,6,7)
R_IND Values range from (0,1)
R_E_IND Values range from (0,1)
L_IND Values range from (0,1)
IS_D_IND Values range from (0,1)
I_S_IND Values range from (0,1)
I_P_IND Values range from (0,1)
I_IND Values range from (0,1)
S_IND Values range from (0,1,2,3)
H_B_IND Values range from (0,1)
O_ID Values range from (1,2,3,4,5,6)

此外，我的日期列采用 varchar 格式，格式为 - '2019-01-25 01:01:59'CRT_DTTM 和 UPD_DTTM

平均而言 - 每日负载为

CRT_DTTM    Count
2019-01-20  656601
2019-01-21  686018
2019-01-22  668486
2019-01-23  680922
2019-01-24  693700

该表现在有数百万条记录并且当前正在生产中 - 没有任何分区和索引。

运行任何查询都需要花费大量时间。

现在，我需要创建分区/索引。尝试对现有表进行分区，需要很长时间才能运行。

对于上面列出的列(经常在 where 子句中使用)和日期列(CRT_DTTM 和 UPD_DTTM)，年份的最佳分区方法是什么？ code>、月、周 和 日 分区。还有索引吗？

该表将保存三年的数据。现在我们有 3 个月的数据。如何将当前表移动到新的分区表。我是 mysql 的新手，任何信息都将有助于减少生产查询运行时间和报告生成。

Answer 1

PARTITION本质上不提供任何性能。让我们看看查询，以便我们可以判断您是否遇到一种罕见的情况，例如清除“旧”数据。

建议缩小数据——SMALLINT占用2个字节； TINYINT UNSIGNED 占用 1 个字节，可以轻松保存您提到的所有这些小值。纬度/经度的 7 位小数使您的精度低于 16 毫米或小于 1 英寸。需要那么高的精度吗？考虑纬度为 DECIMAL(8,6)，经度为 (9,6)；这将为每对节省 3 个字节。 (嗯..为什么有两对？)

“运行‘任何’查询需要很长时间”？让我们看看其中的一些并努力优化它们。通常的问题是您需要接触很多行。缩小行(如上所述)会有所帮助。但最大的改进是不再触及那么多行。

这听起来像一个数据仓库应用程序？如果是这样，也许构建和维护汇总表是正确的方法。请参阅http://mysql.rjweb.org/doc.php/summarytables 。显示更多信息，我会帮助您。

您打算在 3 年后清除数据吗？如果是这样，我建议按月分区并有 38 个分区。详细信息在这里:http://mysql.rjweb.org/doc.php/partitionmaint 。这样，680K 行的每晚DELETE 就变得更快DROP PARTITION。 (同时，查询性能可能没有任何好处。)

我的索引食谱:http://mysql.rjweb.org/doc.php/index_cookbook_mysql