时间戳

首页 > 解决方案 > 删除Mysql后对索引表性能的影响

mysql - 删除Mysql后对索引表性能的影响

问题描述

我有一个包含 100 亿行的表，我的搜索查询提取了 150 万行已编入索引的行。我的问题是如果我删除不必要的行并减少到 3 百万行，我想我的搜索条件性能会提高。这是我的问题

  1. 如果我使用 100 亿行与 3 百万行查询相同数量的数据，索引如何工作

这是我的表详细信息和简单的搜索查询。@startdate 是一个输入，它总是一个月

CREATE TABLE `ABCD` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `VAL` varchar(255) DEFAULT NULL,
  `NVAL` varchar(255) DEFAULT NULL,
  `DOC` bigint(20) NOT NULL,
  `DESC` int(11) NOT NULL,
  `DateCreat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),   
  KEY `IDX_DOC` (`DOC`), -- BTREE NON-UNIQUE
  KEY `INDEX_DESC` (`DESC`), -- BTREE NON-UNIQUE
  KEY `INDEX_DateCreat` (`DateCreat`) -- BTREE NON-UNIQUE
) ENGINE=InnoDB AUTO_INCREMENT=14755842749 DEFAULT CHARSET=utf8


SELECT  
     MONTH(@START_DATE) 'Month'
     ,count(distinct  DOC) 'Docs'

FROM 
    ABCD USE INDEX (IDX_DOC, INDEX_DateCreat)
WHERE
    DateCreat >= @START_DATE and DateCreat < @END_DATE

标签： mysqlindexingdatatablesquery-optimization

解决方案

（评论太长了。）

这取决于。在某些情况下不会有任何改善；在某些方面，会有显着的改善。

请提供SHOW CREATE TABLE样品DELETE和SELECT。

“搜索”是如何完成的——通过PRIMARY KEY？二级钥匙？非索引列？

被删除的行是否在表的一个“末端”（例如清除“旧”数据）？还是散落的？

您如何处理返回的 150 万行？（这是很多！）

索引如何工作。从阅读 Wikipedia 中的 B+Trees 开始。或者你正在使用FULLTEXT？或者SPATIAL？

加速

你不想GROUP BY MONTH(DateCreat)和SELECT MONTH(DateCreat)？或者你真的要显示一系列月份，但只标记一个月？

无论如何，如果您有一个汇总表（可能是按天计算），您可以有效地将汇总表中的计数相加，以非常快速地获取COUNT.

再索引

我仍然需要知道您是删除“旧”行还是分散在“月”中的行。

如果您要删除“旧”行，那么PARTITIONing效率会更高。更多细节：http: //mysql.rjweb.org/doc.php/partitionmaint

如果您要删除散布在整个表中的行，让我们进入 BTrees。数据将由 PK 排序。那就是id，它可能是按时间顺序排列的（或至少大致如此）。

数据存储在块中，每个块可能有 100 行，如果这些 varchars 包含“短”字符串，则可能更多。一个块是 16KB。

当您删除分散的行时，您将减少某些块中的行数，但不会减少块的数量。（好的，如果相邻的两个块足够稀疏，它们将被合并在一起。）

查询一个巨大的表（太大而无法缓存在内存中）的速度主要取决于所触及的块数。

所以，这DELETE对这个查询的性能没有多大帮助。

更好的索引

对于这个查询，替换INDEX(doc)为INDEX(doc, date_creat) 或替换INDEX(date_creat)为INDEX(date_creat, doc)将加快查询速度，甚至在任何删除之前。（可以更改两个索引。但这可能需要很长时间。）

每个二级索引都是一个 BTree。这个 BTree 在删除过程中会发生类似于我上面提到的变化。也就是说，删除一些文档很可能会从第一个索引中删除块，或者删除“旧”行很可能会删除第二个索引的一大块。同时，其他指数大多会变得不那么密集。

那些建议的索引是“覆盖”的。这意味着只能使用INDEX.

索引效率

我再次不得不说“这取决于”......

根据经验，如果要使用超过约 20% 的索引，则该索引将被忽略。也就是说，如果WHERE DateCreat >= @START_DATE and DateCreat < @END_DATE是小范围的日期，则将考虑以 开头的任何索引。 DateCreat对于大范围，将忽略索引并使用表。

最佳总结

较大的日期范围： INDEX(date_creat, doc)-- 将进行过滤和覆盖。没有它，整个表都会被扫描；在这种情况下，表中的块数至关重要——所以，回到 Delete 做了什么或没做什么。

较小的日期范围： INDEX(date_creat, doc)最好，但 ``INDEX(date_creat)` 排在第二位。Delete 影响较小，但现在需要在 BTree（数据和索引）中都考虑效果。

推荐阅读