Future Database Size

The goal is to build a system that can have 1M unique cluster_index values 1M unique match values, approx 3,000,000,000 table rows with a response time to the query of around 0.5s (we can add more ram as necessary and distribute the database across the cluster)

1. 我认为我们希望将整个记录集保存在 ram 中，这样查询就不会触及磁盘，如果我们将整个数据库保存在 MySQL 缓存中，是否就不需要 memcachedb?
2. 试图将整个数据库保存在 MySQL 缓存中是一种糟糕的策略，因为它的设计目的不是持久化吗?像 memcachedb 或 redis 这样的方法会是更好的方法吗?如果是，为什么?
3. 查询完成时，查询创建的临时表结果"是否会自动销毁?
4. 我们是否应该从 Innodb 切换到 MyISAM [因为它适合读取大量数据，而 InnoDB 适合写入大量数据][11]?
5. 我的缓存在我的 [查询缓存配置][12] 中似乎没有设为零，为什么当前查询在我第二次运行时发生得更快?
6. 我可以重组我的查询以消除使用临时"和使用文件排序"的情况吗?我应该使用联接而不是子查询吗?
7. 您如何查看 MySQL [Data Cache][13] 的大小?
8. 您建议将值 table_cache、key_buffer、sort_buffer、read_buffer_size、record_rnd_buffer 的大小作为起点?

链接

• 1:stackoverflow.com/questions/658937/cache-re-use-a-subquery-in-mysql
• 2:databasejournal.com/features/mysql/article.php/10897_1382791_4/Optimizing-MySQL-Queries-and-Indexes.htm
• 3:debianhelp.co.uk/mysqlperformance.htm
• 4:20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
• 5:20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
• 6:dev.mysql.com/doc/refman/5.0/en/explain.html
• 7:dev.mysql.com/doc/refman/5.0/en/explain-output.html
• 8:httpd.apache.org/docs/2.2/programs/ab.html
• 9:mtop.sourceforge.net/
• 10:dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
• 11: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
• 12:dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
• 13:dev.mysql.com/tech-resources/articles/mysql-query-cache.html

推荐答案

换桌

<小时>

基于这篇文章中的建议 如何为 order by 和 group by 查询选择索引 表格现在看起来像

CREATE TABLE ClusterMatches 
(
    cluster_index INT UNSIGNED, 
    match_index INT UNSIGNED,
    id INT NOT NULL AUTO_INCREMENT,
    tfidf FLOAT,
    PRIMARY KEY (match_index,cluster_index,id,tfidf)
);
CREATE TABLE MatchLookup 
(
    match_index INT UNSIGNED NOT NULL PRIMARY KEY,
    image_match TINYTEXT
);

没有按 SUM(tfidf) 对结果进行排序的查询看起来像

The query without sorting the results by the SUM(tfidf) looks like

SELECT match_index, SUM(tfidf) FROM ClusterMatches 
WHERE cluster_index in (1,2,3 ... 3000) GROUP BY match_index LIMIT 10;

这消除了使用临时和使用文件排序

Which eliminates using temporary and using filesort

explain extended SELECT match_index, SUM(tfidf) FROM ClusterMatches 
WHERE cluster_index in (1,2,3 ... 3000) GROUP BY match_index LIMIT 10;
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | ClusterMatches       | range | PRIMARY       | PRIMARY | 4       | NULL | 14938 | Using where; Using index | 
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+--------------------------+

但是，如果我将 ORDER BY SUM(tfdif) 添加到

However if i add the ORDER BY SUM(tfdif) in

SELECT match_index, SUM(tfidf) AS total FROM ClusterMatches
WHERE cluster_index in (1,2,3 ... 3000) GROUP BY match_index 
ORDER BY total DESC LIMIT 0,10;
+-------------+--------------------+
| match_index | total              |
+-------------+--------------------+
|         868 |   0.11126546561718 | 
|        4182 | 0.0238558370620012 | 
|        2162 | 0.0216601379215717 | 
|        1406 | 0.0191618576645851 | 
|        4239 | 0.0168981291353703 | 
|        1437 | 0.0160425212234259 | 
|        2599 | 0.0156466849148273 | 
|         394 | 0.0155945559963584 | 
|        3116 | 0.0151005545631051 | 
|        4028 | 0.0149106932803988 | 
+-------------+--------------------+
10 rows in set (0.03 sec)

在这种规模下，结果相当快，但 ORDER BY SUM(tfidf) 意味着它使用临时和文件排序

The result is suitably fast at this scale BUT having the ORDER BY SUM(tfidf) means it uses temporary and filesort

explain extended SELECT match_index, SUM(tfidf) AS total FROM ClusterMatches 
WHERE cluster_index IN (1,2,3 ... 3000) GROUP BY match_index 
ORDER BY total DESC LIMIT 0,10;
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
| id | select_type | table                | type  | possible_keys | key     | key_len | ref  | rows  | Extra                                                     |
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | ClusterMatches       | range | PRIMARY       | PRIMARY | 4       | NULL | 65369 | Using where; Using index; Using temporary; Using filesort | 
+----+-------------+----------------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+

我正在寻找一种不使用临时或文件排序的解决方案，类似于

Im looking for a solution that doesn't use temporary or filesort, along the lines of

SELECT match_index, SUM(tfidf) AS total FROM ClusterMatches 
WHERE cluster_index IN (1,2,3 ... 3000) GROUP BY cluster_index, match_index 
HAVING total>0.01 ORDER BY cluster_index;

在哪里我不需要硬编码总阈值，有什么想法吗?

where I dont need to hardcode a threshold for total, any ideas?