代码之家 › 专栏 › 技术社区 › Prasanna Nandakumar

分类字段和时间戳列上的mysql分区,它是varchar

database-partitioning partition innodb performance mysql

Prasanna Nandakumar · 技术社区 · 6 年前

目前我们有桌子:

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;

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子句中使用)和日期列,什么是最好的分区方法( 和 UPD_DTTM公司 )为了 Year , Month Week 和 Day 隔墙。

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

1 回复 | 直到 6 年前

Rick James diyism 6 年前

PARTITIONs 本质上不提供任何性能。让我们看看查询,这样我们就可以判断您是否有一个罕见的情况,比如清除“旧”数据。

建议你缩小数据-- SMALLINT 占用2个字节; TINYINT UNSIGNED 只需1个字节,就可以轻松保存您提到的所有小值。lat/液化天然气的小数点后7位精度低于16毫米或小于1英寸。你需要那么精确吗?考虑十进制(8,6)表示纬度,十进制(9,6)表示经度;这将为每对节省3个字节。(嗯。。为什么有两对?)

“运行“any”查询需要很长时间”?让我们看看其中的一些,并努力优化它们。通常的问题是你需要触摸很多行。缩小行(如上所述)将有帮助。但最大的改进在于没有碰到那么多的行。

这闻起来像是一个数据仓库应用程序?如果是这样的话,也许构建和维护汇总表是一条路。见 http://mysql.rjweb.org/doc.php/summarytables

你打算在三年后清除数据吗?如果是这样,我建议按月分区,并有38个分区。详情如下: http://mysql.rjweb.org/doc.php/partitionmaint . 有了这个,每晚68000人 DELETE 许多的 DROP PARTITION . (同时,查询的性能可能没有好处。)

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