我们有一个社交网站，成员可以互相评价兼容性或匹配性。这个
user_match_ratings表包含超过2.2亿行（9 gig数据或近20 gig索引）。对该表的查询通常显示在slow.log（阈值> 2秒）中，并且是系统中最常记录的慢速查询：Query_time: 3 Lock_time: 0 Rows_sent: 3 Rows_examined: 1051
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 395357 group by rating;"
Query_time: 4 Lock_time: 0 Rows_sent: 3 Rows_examined: 1294
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 4182969 group by rating;"
Query_time: 3 Lock_time: 0 Rows_sent: 3 Rows_examined: 446
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 630148 group by rating;"
Query_time: 5 Lock_time: 0 Rows_sent: 3 Rows_examined: 3788
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1835698 group by rating;"
Query_time: 17 Lock_time: 0 Rows_sent: 3 Rows_examined: 4311
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1269322 group by rating;"
协议版本：10
版本：5.0.77-log
版本bdb：Sleepycat软件：Berkeley DB 4.1.24：（2009年1月29日）
版本编译机：x86_64 version_compile_os：redhat-linux-gnu
表信息：
SHOW COLUMNS FROM user_match_ratings;
给出：
╔═══════════════╦════════════╦════╦═════╦════════╦════════════════╗
║ id ║ int(11) ║ NO ║ PRI ║ NULL ║ auto_increment ║
║ rater_user_id ║ int(11) ║ NO ║ MUL ║ NULL ║ ║
║ rated_user_id ║ int(11) ║ NO ║ MUL ║ NULL ║ ║
║ rating ║ varchar(1) ║ NO ║ ║ NULL ║ ║
║ created_at ║ datetime ║ NO ║ ║ NULL ║ ║
╚═══════════════╩════════════╩════╩═════╩════════╩════════════════╝
示例查询：
select * from mutual_match_ratings where id=221673540;
给出：
╔═══════════╦═══════════════╦═══════════════╦════════╦══════════════════════╗
║ id ║ rater_user_id ║ rated_user_id ║ rating ║ created_at ║
╠═══════════╬═══════════════╬═══════════════╬════════╬══════════════════════╣
║ 221673540 ║ 5699713 ║ 3890950 ║ N ║ 2013-04-09 13:00:38 ║
╚═══════════╩═══════════════╩═══════════════╩════════╩══════════════════════╝
索引
该表设置了3个索引：
rated_user_id上的单个索引rater_user_id上的复合索引和created_at和rated_user_id的综合索引show index from user_match_ratings;
给出：
╔════════════════════╦════════════╦═══════════════════════════╦══════════════╦═══════════════╦═══════════╦═════════════╦══════════╦════════╦═════════════════════════╦════════════╦══════════════════╗
║ Table ║ Non_unique ║ Key_name ║ Seq_in_index ║ Column_name ║ Collation ║ Cardinality ║ Sub_part ║ Packed ║ Null ║ Index_type ║ Comment ║
╠════════════════════╬════════════╬═══════════════════════════╬══════════════╬═══════════════╬═══════════╬═════════════╬══════════╬════════╬═════════════════════════╬════════════╬══════════════════╣
║ user_match_ratings ║ 0 ║ PRIMARY ║ 1 ║ id ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index1 ║ 1 ║ rater_user_id ║ A ║ 11039059 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index1 ║ 2 ║ created_at ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index2 ║ 1 ║ rated_user_id ║ A ║ 4014203 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index2 ║ 2 ║ rater_user_id ║ A ║ 220781193 ║ NULL ║ NULL ║ BTREE ║ ║ ║
║ user_match_ratings ║ 1 ║ user_match_ratings_index3 ║ 1 ║ rated_user_id ║ A ║ 2480687 ║ NULL ║ NULL ║ BTREE ║ ║ ║
╚════════════════════╩════════════╩═══════════════════════════╩══════════════╩═══════════════╩═══════════╩═════════════╩══════════╩════════╩═════════════════════════╩════════════╩══════════════════╝
>即使使用索引，这些查询也很慢。
我的问题：
将表/日期分开如果服务器上的另一个数据库具有足够的内存来将这些数据存储在内存中，这会加快这些查询的速度吗？无论如何，表/索引是否有任何设置可以改进以使这些查询更快？
当前我们有16GB的内存；但是，我们正在考虑将现有计算机升级到32GB或添加至少具有那么多（可能还有固态驱动器）的新计算机。
#1 楼
关于该问题的想法，以随机顺序抛出：该查询的明显索引是：
(rated_user_id, rating)。一个仅获取百万用户中的一个数据并且需要17秒的查询，这是在做错什么：从(rated_user_id, rater_user_id)索引读取，然后从表中读取rating列的值（数百至数千），因为rating不在任何列中指数。因此，查询必须读取表的许多行，这些行位于许多不同的磁盘位置。在开始在表中添加许多索引之前，请尝试分析整个数据库的性能，以及整个慢速查询的集合，请再次检查数据类型的选择，您使用的引擎和配置设置。
考虑迁移到MySQL的新版本，即5.1、5.5甚至5.6（也包括：Percona和MariaDB版本）。已更正，优化程序得到了改进，您可以将慢速查询的低阈值设置为少于1秒（例如10毫秒）。这将为您提供有关慢查询的更好的信息。
rating的数据类型选择很奇怪。 VARCHAR(1)？为什么不CHAR(1)？为什么不TINYINT？这样既可以节省表空间，也可以节省表空间和包含该列的索引空间。 varchar（1）列比char（1）还要多一个字节，如果它们是utf8，则（var）char列将需要3（或4）个字节，而不是1（tinyint）。 >评论
如果您使用错误的数据类型，会对性能造成多少影响或以百分比为单位的存储浪费？
–FlyingAtom
17年2月8日在0:39
@FlyingAtom这取决于大小写，但是对于某些仍需要扫描的索引列（例如，当您没有where子句但仅在检索该列时），引擎可能会决定扫描索引而不是扫描表格，如果您将数据类型优化为大小的一半，则扫描速度将是两倍，而响应速度将是大小的一半。如果您仍在扫描表而不是索引（例如，当您检索更多的列而不仅是索引中的列时），那么收益将不太明显。
–塞巴斯蒂安·格里尼戈利（SebastiánGrignoli）
19年8月27日在12:22
#2 楼
我为德国政府处理过一些表格，有时有6000万条记录。我们有很多这样的表格。
我们需要多次了解一个表中的总行数。
与Oracle和Microsoft程序员交谈后，我们并不高兴...
因此，我们（一组数据库程序员）决定，在每个表中的记录都是总存储有总记录号的记录。我们根据INSERT或DELETE行更新了此数字。
我们尝试了所有其他方法。这是迄今为止最快的方法。
自1998年以来，我们一直使用这种方式，并且在我们所有的数百万条记录表中，行数从来没有错误。
评论
我建议您研究一下过去18年中引入的一些功能。除其他外，count（*）有一些改进。
– dezso
16-10-2在8:48
您怎么知道如果您不算数的话，您永远不会有错误的号码？嗯...
–通卡
19年5月2日在13:18
#3 楼
我将尝试按评分类型进行分区，例如：mutual_match_ratings_N，interactive_match_ratings_S等。
您应该对每种类型执行查询，但这也许比其他方法要快。尝试一下。
假设您拥有固定数量的评分类型，并且不需要该表来进行其他查询，这些查询对于使用这种新结构来说最糟糕。
这种情况下，您应该寻找其他方法，或者维护表的两个副本（初始表和分区表的两个副本），如果在空间和可维护性（或应用程序逻辑）方面可以承受的话。
评论
您的问题令人难以置信。我对您当前的解决方案非常感兴趣，即您如何在2秒内获得结果？因为我有一个表只有2000万条记录，但是SELECT QUERY仍然需要30秒。你能建议吗？附言您的问题迫使我加入了这个社区（y）;）查看要查询的表上的索引。通常，通过创建适当的索引可以对查询进行很多改进。并非总是如此，但是在很多情况下，可以通过对查询的where子句中的列提供索引来快速进行查询。特别是当桌子越来越大时。
当然@Ranknoodle。谢谢。我会分别检查。