提高大型SQL表用于访问者统计的性能

Question

我有一张供游客统计的桌子如下：

CREATE TABLE `stats` (
  `key` int(11) NOT NULL AUTO_INCREMENT,
  `u_id` int(11) NOT NULL,
  `app` varchar(20) NOT NULL,
  `type` varchar(20) DEFAULT '',
  `category` varchar(80) DEFAULT '',
  `sub_category` varchar(80) DEFAULT '',
  `date` int(11) NOT NULL,
  `ip` varchar(20) NOT NULL
  PRIMARY KEY (`key`)
) ENGINE=MyISAM AUTO_INCREMENT=41094490 DEFAULT CHARSET=utf8;

它是几年前创建的，从那以后一直在收集数据。它现在有大约4000万行。

按“应用程序”进行选择需要大约1分钟的时间，这太慢了。我通常参与PHP和Javascript，但我的MYSQL知识非常有限。

该表将只需要向其添加行并进行选择查询。select查询用于在前端生成使用状态图。

一个典型的查询将通过匹配2-4列值进行选择，并且总是使用日期范围进行选择。通常，我预计日期范围为1周至6个月，但在某些情况下，用户可能希望查看几年的数据。当务之急是对前者进行优化。

我不介意有一个单独的数据库，根据日期将这些数据分割成不同的表，所以每个月可能有一个表。我可以看到，这将如何帮助几个月的查询一次(使用联接)，但当涉及到数据，在未来几年，它将变得更慢。

在表结构方面还有什么可以改进的吗？是否更好地将表分割成列较少的相关表？

用于测试性能的查询是：

select * from stats where u_id='123' and app='articles'

使用对此的解释函数我得到：

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","stats","ALL",NULL,NULL,NULL,NULL,37462750,"Using where"

Answer 1

这张桌子需要做些改动。

1. 将引擎从MyISAM更改为InnoDB

总的来说，MyISAM和InnoDB的主要区别在于，MyISAM是一个比InnoDB更老、效率更低的存储引擎。这两个引擎之间最常见的区别是: InnoDB更稳定、更快、更易于设置；它还支持事务。它是默认的存储引擎，这是有原因的，您可以使用InnoDB的特性，而无需担心兼容性问题。如果需要存储大量数据或确保事务正常工作，请选择InnoDB。MyISAM引擎不太擅长存储大量数据，因为它将所有内容存储在一个表中。当您需要向数据库添加数据时，您必须锁定整个表，这会导致数据库停止工作，直到它被解锁。在InnoDB引擎中，每一行分别存储在一个单独的表中。这意味着当您将数据插入MySQL数据库时，不需要锁定所有行。

更详细的解释，在这里InnoDB和MyISAM哪个更快？

1. 使用正确的日期数据类型，而不是int。如果按日期存储记录的插入日期，我更喜欢使用insert_record_dt timestamp NOT NULL DEFAULT current_timestamp()
2. 一个典型的查询将通过匹配2-4列值进行选择，并且总是使用日期范围进行选择。

我们需要查看这些查询，以便更好地回答索引问题。

例如，如果您使用以下查询

select s.`key`
       s.`sub_category`
       ..
       ..
from stats s 
where sub_category='test' 
and `date` >='2023-03-01' ;    

所需的索引将是

INDEX sc_dt(`sub_category`,`date`)

1. 如果可能的话，不要使用关键词与保留词，否则您需要使用backticks

Answer 2

为

where u_id='123' and app='articles'

需求

INDEX(u_id, app)  -- either order is good.

为

where `date` >='2023-03-01'  and sub_category='test';    

需求

INDEX(sub_category, date)  -- in this order

关于构建最优索引的更多信息：索引食谱

通过匹配2-4列值进行选择，并将始终使用日期范围进行选择。

如果这些列是用=测试的，那么使用这些列启动一个INDEX，然后在末尾添加date (因为它是一个范围)。

为期1周至6个月，但在某些情况下，用户可能希望查看几年的数据。

较短的索引可能会使用一个索引，并且速度更快，而较长的索引可能还需要很长的时间。

如果您有其他示例查询，我可能会提供更多建议的索引。

我确实同意InnoDB更好。(事实上，MySQL 8.0已经删除了MyISAM。)