索引大型mysql表的最佳方法是什么？

fnx2tebb  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(373)

下面是表的create语句：

CREATE TABLE `inodes_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `I_ID` int(11) unsigned NOT NULL DEFAULT '0',
  `Time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Stored` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dataIndex` int(11) unsigned DEFAULT NULL,
  `memoryAddress` int(11) DEFAULT NULL,
  `Sens1` float DEFAULT NULL,
  `Sens2` float DEFAULT NULL,
  `Sens3` float DEFAULT NULL,
  `Sens4` float DEFAULT NULL,
  `rawData` char(50) NOT NULL,
  PRIMARY KEY (`id`,`Time`,`I_ID`),
  UNIQUE KEY `i_id_time_idx` (`I_ID`,`Time`),
  KEY `I_ID` (`I_ID`),
  KEY `IX_TIME` (`Time`)
) ENGINE=InnoDB AUTO_INCREMENT=8289060 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=83
/*!50100 PARTITION BY RANGE (TO_DAYS(Time))
(PARTITION p2011_Prior VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p2011_01 VALUES LESS THAN (734534) ENGINE = InnoDB,
 PARTITION p2011_02 VALUES LESS THAN (734562) ENGINE = InnoDB,
 PARTITION p2011_03 VALUES LESS THAN (734593) ENGINE = InnoDB,
 PARTITION p2011_04 VALUES LESS THAN (734623) ENGINE = InnoDB,
 PARTITION p2011_05 VALUES LESS THAN (734654) ENGINE = InnoDB,
 PARTITION p2011_06 VALUES LESS THAN (734684) ENGINE = InnoDB,
 PARTITION p2011_07 VALUES LESS THAN (734715) ENGINE = InnoDB,
 PARTITION p2011_08 VALUES LESS THAN (734746) ENGINE = InnoDB,
 PARTITION p2011_09 VALUES LESS THAN (734776) ENGINE = InnoDB,
 PARTITION p2011_10 VALUES LESS THAN (734807) ENGINE = InnoDB,
 PARTITION p2011_11 VALUES LESS THAN (734837) ENGINE = InnoDB,
 PARTITION p2011_12 VALUES LESS THAN (734868) ENGINE = InnoDB,
 PARTITION p2012_01 VALUES LESS THAN (734899) ENGINE = InnoDB,
 PARTITION p2012_02 VALUES LESS THAN (734928) ENGINE = InnoDB,
 PARTITION p2012_03 VALUES LESS THAN (734959) ENGINE = InnoDB,
 PARTITION p2012_04 VALUES LESS THAN (734989) ENGINE = InnoDB,
 PARTITION p2012_05 VALUES LESS THAN (735020) ENGINE = InnoDB,
 PARTITION p2012_06 VALUES LESS THAN (735050) ENGINE = InnoDB,
 PARTITION p2012_07 VALUES LESS THAN (735081) ENGINE = InnoDB,
 PARTITION p2012_08 VALUES LESS THAN (735112) ENGINE = InnoDB,
 PARTITION p2012_09 VALUES LESS THAN (735142) ENGINE = InnoDB,
 PARTITION p2012_10 VALUES LESS THAN (735173) ENGINE = InnoDB,
 PARTITION p2012_11 VALUES LESS THAN (735203) ENGINE = InnoDB,
 PARTITION p2012_12 VALUES LESS THAN (735234) ENGINE = InnoDB,
 PARTITION p2013_01 VALUES LESS THAN (735265) ENGINE = InnoDB,
 PARTITION p2013_02 VALUES LESS THAN (735293) ENGINE = InnoDB,
 PARTITION p2013_03 VALUES LESS THAN (735324) ENGINE = InnoDB,
 PARTITION p2013_04 VALUES LESS THAN (735354) ENGINE = InnoDB,
 PARTITION p2013_05 VALUES LESS THAN (735385) ENGINE = InnoDB,
 PARTITION p2013_06 VALUES LESS THAN (735415) ENGINE = InnoDB,
 PARTITION p2013_07 VALUES LESS THAN (735446) ENGINE = InnoDB,
 PARTITION p2013_08 VALUES LESS THAN (735477) ENGINE = InnoDB,
 PARTITION p2013_09 VALUES LESS THAN (735507) ENGINE = InnoDB,
 PARTITION p2013_10 VALUES LESS THAN (735538) ENGINE = InnoDB,
 PARTITION p2013_11 VALUES LESS THAN (735568) ENGINE = InnoDB,
 PARTITION p2013_12 VALUES LESS THAN (735599) ENGINE = InnoDB,
 PARTITION p2014_01 VALUES LESS THAN (735630) ENGINE = InnoDB,
 PARTITION p2014_02 VALUES LESS THAN (735658) ENGINE = InnoDB,
 PARTITION p2014_03 VALUES LESS THAN (735689) ENGINE = InnoDB,
 PARTITION p2014_04 VALUES LESS THAN (735719) ENGINE = InnoDB,
 PARTITION p2014_05 VALUES LESS THAN (735750) ENGINE = InnoDB,
 PARTITION p2014_06 VALUES LESS THAN (735780) ENGINE = InnoDB,
 PARTITION p2014_07 VALUES LESS THAN (735811) ENGINE = InnoDB,
 PARTITION p2014_08 VALUES LESS THAN (735842) ENGINE = InnoDB,
 PARTITION p2014_09 VALUES LESS THAN (735872) ENGINE = InnoDB,
 PARTITION p2014_10 VALUES LESS THAN (735903) ENGINE = InnoDB,
 PARTITION p2014_11 VALUES LESS THAN (735933) ENGINE = InnoDB,
 PARTITION p2014_12 VALUES LESS THAN (735964) ENGINE = InnoDB,
 PARTITION p2015_01 VALUES LESS THAN (735995) ENGINE = InnoDB,
 PARTITION p2015_02 VALUES LESS THAN (736023) ENGINE = InnoDB,
 PARTITION p2015_03 VALUES LESS THAN (736054) ENGINE = InnoDB,
 PARTITION p2015_04 VALUES LESS THAN (736084) ENGINE = InnoDB,
 PARTITION p2015_05 VALUES LESS THAN (736115) ENGINE = InnoDB,
 PARTITION p2015_06 VALUES LESS THAN (736145) ENGINE = InnoDB,
 PARTITION p2015_07 VALUES LESS THAN (736176) ENGINE = InnoDB,
 PARTITION p2015_08 VALUES LESS THAN (736207) ENGINE = InnoDB,
 PARTITION p2015_09 VALUES LESS THAN (736237) ENGINE = InnoDB,
 PARTITION p2015_10 VALUES LESS THAN (736268) ENGINE = InnoDB,
 PARTITION p2015_11 VALUES LESS THAN (736298) ENGINE = InnoDB,
 PARTITION p2015_12 VALUES LESS THAN (736329) ENGINE = InnoDB,
 PARTITION p2016_01 VALUES LESS THAN (736360) ENGINE = InnoDB,
 PARTITION p2016_02 VALUES LESS THAN (736389) ENGINE = InnoDB,
 PARTITION p2016_03 VALUES LESS THAN (736420) ENGINE = InnoDB,
 PARTITION p2016_04 VALUES LESS THAN (736450) ENGINE = InnoDB,
 PARTITION p2016_05 VALUES LESS THAN (736481) ENGINE = InnoDB,
 PARTITION p2016_06 VALUES LESS THAN (736511) ENGINE = InnoDB,
 PARTITION p2016_07 VALUES LESS THAN (736542) ENGINE = InnoDB,
 PARTITION p2016_08 VALUES LESS THAN (736573) ENGINE = InnoDB,
 PARTITION p2016_09 VALUES LESS THAN (736603) ENGINE = InnoDB,
 PARTITION p2016_10 VALUES LESS THAN (736634) ENGINE = InnoDB,
 PARTITION p2016_11 VALUES LESS THAN (736664) ENGINE = InnoDB,
 PARTITION p2016_12 VALUES LESS THAN (736695) ENGINE = InnoDB,
 PARTITION p2017_01 VALUES LESS THAN (736726) ENGINE = InnoDB,
 PARTITION p2017_02 VALUES LESS THAN (736754) ENGINE = InnoDB,
 PARTITION p2017_03 VALUES LESS THAN (736785) ENGINE = InnoDB,
 PARTITION p2017_04 VALUES LESS THAN (736815) ENGINE = InnoDB,
 PARTITION p2017_05 VALUES LESS THAN (736846) ENGINE = InnoDB,
 PARTITION p2017_06 VALUES LESS THAN (736876) ENGINE = InnoDB,
 PARTITION p2017_07 VALUES LESS THAN (736907) ENGINE = InnoDB,
 PARTITION p2017_08 VALUES LESS THAN (736938) ENGINE = InnoDB,
 PARTITION p2017_09 VALUES LESS THAN (736968) ENGINE = InnoDB,
 PARTITION p2017_10 VALUES LESS THAN (736999) ENGINE = InnoDB,
 PARTITION p2017_11 VALUES LESS THAN (737029) ENGINE = InnoDB,
 PARTITION p2017_12 VALUES LESS THAN (737060) ENGINE = InnoDB,
 PARTITION p2018_01 VALUES LESS THAN (737091) ENGINE = InnoDB,
 PARTITION p2018_02 VALUES LESS THAN (737119) ENGINE = InnoDB,
 PARTITION p2018_03 VALUES LESS THAN (737150) ENGINE = InnoDB,
 PARTITION p2018_04 VALUES LESS THAN (737180) ENGINE = InnoDB,
 PARTITION p2018_05 VALUES LESS THAN (737211) ENGINE = InnoDB,
 PARTITION p2018_06 VALUES LESS THAN (737241) ENGINE = InnoDB,
 PARTITION p2018_07 VALUES LESS THAN (737272) ENGINE = InnoDB,
 PARTITION p2018_08 VALUES LESS THAN (737303) ENGINE = InnoDB,
 PARTITION p2018_09 VALUES LESS THAN (737333) ENGINE = InnoDB,
 PARTITION p2018_10 VALUES LESS THAN (737364) ENGINE = InnoDB,
 PARTITION p2018_11 VALUES LESS THAN (737394) ENGINE = InnoDB,
 PARTITION p2018_12 VALUES LESS THAN (737425) ENGINE = InnoDB,
 PARTITION p2019_01 VALUES LESS THAN (737456) ENGINE = InnoDB,
 PARTITION p2019_02 VALUES LESS THAN (737484) ENGINE = InnoDB,
 PARTITION p2019_03 VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION p2019_04 VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION p2019_05 VALUES LESS THAN (737576) ENGINE = InnoDB,
 PARTITION p2019_06 VALUES LESS THAN (737606) ENGINE = InnoDB,
 PARTITION p2019_07 VALUES LESS THAN (737637) ENGINE = InnoDB,
 PARTITION p2019_08 VALUES LESS THAN (737668) ENGINE = InnoDB,
 PARTITION p2019_09 VALUES LESS THAN (737698) ENGINE = InnoDB,
 PARTITION p2019_10 VALUES LESS THAN (737729) ENGINE = InnoDB,
 PARTITION p2019_11 VALUES LESS THAN (737759) ENGINE = InnoDB,
 PARTITION p2019_12 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION pUnknown VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

对该表运行的最常见查询是：

SELECT  *
    FROM  inodes_data
    WHERE  I_ID = xxxxx
      AND  Time BETWEEN 'xxxx-xx-xx xx:xx:xx' AND 'xxxx-xx-xx xx:xx:xx';

或

SELECT  *
    FROM  inodes_data
    WHERE  I_ID IN (xxxxx,xxxx,....)
      AND  Time BETWEEN 'xxxx-xx-xx xx:xx:xx' AND 'xxxx-xx-xx xx:xx:xx';

目前，从这个表中查询数据需要很长时间。如果我试着抽一年，可能需要15秒甚至几分钟。我已经研究过了，正在努力寻找一种方法来加速这一进程。有没有更好的方法来建立索引，如果有，有人能告诉我为什么吗！？我很感激你的帮助。

mysqlquery-performanceindexingdatabase-partitioning

3条答案

按热度按时间
vkc1a9a2

vkc1a9a21#

我认为mysql会使用 I_ID 或者 Time 索引已经存在。以确保你可以使用 EXPLAIN 在查询的开始部分，查看mysql的查询计划实际上是什么(https://dev.mysql.com/doc/refman/5.7/en/explain.html)
即： EXPLAIN SELECT * FROM inodes_data WHERE I_ID = xx AND Time BETWEEN 'x-xx-xx xx:xx:xx' AND 'yyyy-yy-yy yy:yy:yy'; //添加 AND 陈述
更多信息： EXPLAIN EXTENDED SELECT * FROM inodes_data WHERE I_ID = xx AND Time BETWEEN 'x-xx-xx xx:xx:xx' AND 'yyyy-yy-yy yy:yy:yy'; //添加 AND 声明。
另外，您还可以使用这两种方法创建一个复合索引 I_ID 以及 Time 如果总是使用这两个列进行查询，可能会创建更好的索引。您可以更改alter以添加另一个索引：-缺少现有索引 UNIQUE 已经是多列索引。
(https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html)

赞(0）分享  回复(0）举报  2021-06-21
3phpmpom

3phpmpom2#

你真的在做什么 SELECT * ? 会返回多少行？网络时间不是真正的问题吗？
如果你真的在总结东西，让我们看看 GROUP BY 等等。这对如何回答你的问题有很大的影响。
与此同时。。。
摆脱pk，提升 UNIQUE KEY (I_ID,Time) 成为pk。这将使第一个查询以最佳方式工作。事实上，如果不使用它，效果会明显更好 PARTITIONing .
分区不要超过50个。分区越多，开销就越大。
在需要分区之前不要添加分区。又是“头顶”。保留“未来”分区并使用 REORGANIZE PARTITION 添加新分区时。更多讨论请参见此处。
收缩 INTs （例如， SMALLINT UNSIGNED )如适用。
去掉多余的 KEY (I_ID) . 它浪费了磁盘空间，速度也变慢了 INSERTs .
删除上的索引 time --以分区键开始的索引几乎总是低效的。
因为第二个查询需要一个二维索引，所以我不建议删除分区。这是它为数不多的用途之一 PARTITION .
别说了 CHAR(50) 除非文本是固定长度的(不，将行设为“固定长度”没有帮助。）
建议对时间范围使用此结构：

WHERE Time >= '2017-02-01`
   AND Time  < '2017-02-01` + INTERVAL 5 MONTH

总结：
清理分区（更少，没有未来等）
掷
id CHAR -> VARCHAR 将4个索引更改为1： PRIMARY KEY(I_ID, time)

赞(0）分享  回复(0）举报  2021-06-21
pcww981p

pcww981p3#

建立和维护两个“汇总表”。每张table都有

Time (truncated to day for one table, hour for the other)
I_ID
3 columns for each sensor
miscellany

一个传感器的列：

average for the day (or hour)
min
max

根据时间范围。。。
在不到一周的时间内，使用原始数据，就像你现在所做的那样(我们可能需要重新处理分区和索引以使其更好地工作。）
对于1周到6个月，使用小时汇总表
6个月以上，使用每日汇总表。
这样，你至少得100分。调整截止值，作为查询速度和图中细节之间的权衡。
如果您愿意，您可以显示一个垂直条而不是一个点——这可以让您表示在一小时或一天内有多少变化。如果用户需要更多细节，可以放大。
摘要表可以是非分区的，并且

PRIMARY KEY(I_ID, Time),
INDEX(Time)

有关摘要表的详细信息：http://mysql.rjweb.org/doc.php/summarytables
对于表的维护，请执行以下操作：在每个表的末尾，运行如下查询：

INSERT INTO Hourly (...)
     SELECT FLOOR(Time / 3600) AS the_hour,
            I_ID, ...
            AVG(sensor1),
            MIN(sensor1),
            MAX(sensor1),
            ...
        FROM ...
        WHERE ...  -- just the one hour
        GROUP BY the_hour, I_ID;

在每天结束时，把每小时的工资汇总到每天的工资中。

赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com