数据库性能优化

Question

我们有一张很大的桌子，有大约2.5亿张记录。表越来越大，因为每隔一秒就会插入许多记录。此表中没有进行任何更新/删除操作。该表的结构类似于下面的示例结构

CREATE TABLE `sample_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `BatchNo` varchar(12) DEFAULT NULL,
  `LotNo` varchar(12) DEFAULT NULL,
  `ModelNumber` varchar(20) DEFAULT NULL,
  `Cost` decimal(4,2) DEFAULT NULL,
  `QualityPercentage` tinyint(3) unsigned DEFAULT NULL,
  `Type` varchar(5) DEFAULT NULL,
  `Vendor` varchar(5) DEFAULT NULL,
  `Send_Time` datetime DEFAULT NULL,
  `InsertTime` int(10) unsigned NOT NULL,
  `VendorRating` tinyint(3) unsigned DEFAULT NULL,
  `ProductType` tinyint(4) DEFAULT NULL,
  `SerialNumber` varchar(4) DEFAULT NULL,
  `ItemID` int(11) DEFAULT NULL,
  `BrandID` int(11) DEFAULT NULL,
  `CategoryID` int(11) DEFAULT NULL,
  `SubTitle` varchar(10) NOT NULL DEFAULT 'gl200',
  `Extras` text,
  PRIMARY KEY (`id`),
  KEY `ModelNumber` (`ModelNumber`),
  KEY `SendTime` (`Send_Time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 

问题

其性能较慢，特别是当我们选择获取较大日期范围(Send_Time)的记录时。就像我们尝试从一年前到现在获取记录一样，mysql崩溃需要花费很长时间。

我们征询了DBA的意见，他说，索引每次都在变化，因为每分钟都有许多记录被插入，抓取会很慢，所以他建议-

1. 不要从同一个数据库表"sample_table“中读或写。
2. 创建另一个数据库表，如"sample_fetch_table“，其结构与"sample_table”相同
3. 从现在开始，"sample_table“只用于插入记录。
4. 在夜间，当写入频率很低时，从"sample_table“获取最后24小时的记录，并将它们插入"sample_fetch_table”中。然后从"sample_table“中删除插入的记录。
5. 现在使用"sample_fetch_table“表选择并显示网页上的记录。
6. 将X年前的记录存档到另一个存档表中。

更新

我们运行的常见查询是：

SELECT * 
FROM sample_table 
WHERE (    ModelNumber = "12890" 
       AND Send_Time > FROM_UNIXTIME(1556515028) AND Send_Time < FROM_UNIXTIME(1556661540)) 
       AND (   (    Vendor in('11','12') 
                AND ItemID = 31 
                AND SubTitle LIKE 'ABC-123'
               ) 
            OR (    Vendor in('ABTU','RAST','RAMT','ABRT','ABNM')  
                AND UPPER(SubTitle) IN('RA200','PR55','XY100','TW20','D1EV','FR091','FR093','FRA12','AB23')
               )
           ) 
       AND BatchNo != 0 
       AND LotNo != 0 
 ORDER BY Send_time

对上述查询

的解释

id = 1
select_type =  SIMPLE
table = sample_table
type = range
possible_keys = ModelNumber, SendTime
key = SendTime
key_len = 6
ref = (Null)
rows =  202042
Extra = Using index condition; Using where 

我们对这个建议有点怀疑。这是最佳的解决方案吗？还有其他更好的选择吗？

Answer 1

考虑到表的大小及其增长率，分区将是一件好事。

尽管如此，在执行此操作之前，您可以尝试改进查询。

和SubTitle类似的'ABC-123‘

您没有做任何外卡搜索，所以"like“操作符是不必要的；简单的"=”就可以了。但是您的DBMS很可能能够自己解决这个问题。

和上(SubTitle)IN(‘IN 200’，.

现在有个问题。

除非您有基于函数的索引，否则数据库必须扫描每条记录，通过SubTitle ()函数传递字段，然后根据" in“子句中的值测试计算结果。扫描每一个记录是我们所谓的表扫描，在这样大小的表上，它将是缓慢的！

您需要修复该字段中的数据，使其在大写中保持一致，并从查询中删除Upper()函数调用。

..。由于索引每次都在变化，因为每分钟都有许多记录被插入，抓取将是缓慢的。

如果只添加新记录，索引将频繁扩展，但是已经写入的索引记录将基本保持不变，因此，除非您总是在寻找最新的记录，否则这不会产生太大的影响。

我希望表格扫描问题会给你带来更大的打击。

性能较慢，特别是当我们选择获取较大日期范围(Send_Time)的记录时。就像我们尝试从一年前到现在获取记录一样，mysql崩溃需要花费很长时间。

你打算用一整年的数据做什么呢？如果要进行任何类型的聚合，请查看将其推回数据库。

Answer 2

考虑到查询和表大小，这看起来是分区在Send_Time列上的一个很好的候选。

Answer 3

INDEX(ModelNumber, Send_Time)

对于当前的查询，应该是最优的。添加时，只需将索引放在ModelNumber上，因为它将是多余的。

UPPER(SubTitle)使表达式不是“可增强的”，因此不适合使用索引。相反，可以使用排序规则来避免对UPPER的需求。

但是，OR还防止试图在索引中使用OR部件中的任何列。

一旦使用了上面的“复合”索引，PARTITION BY TO_DAYS(Send_Time)就不会有进一步的好处了。如果您计划清除“旧”数据，那么分区有一个好处。

其他说明：

你知道cost DECIMAL(4,2)只限于"99.99“吗？

一些INTs可能会缩水。(INT为4字节；SMALLINT为2字节，但范围更有限；等等)空间对性能有一定的影响。

DBA给你的指令列表很复杂，几乎没有什么好处。拥有一天数据的新表并不比在Send_Time上对现有表包含一个狭窄的范围更好。

如果您在一个较宽的时间范围内获取数据，这是否意味着将返回许多行？如果是这样，客户端将如何处理如此大量的数据？