MySQL 减少大表的索引大小

Question

对于我的在线商店，我有一个用于搜索的表格:

CREATE TABLE `store_search` (
  `term` varchar(50) NOT NULL DEFAULT '',
  `content_id` int(10) unsigned NOT NULL,
  `type` enum('keyword','tag') NOT NULL DEFAULT 'keyword',
  `random` int(10) unsigned NOT NULL,
  `saving` int(10) unsigned NOT NULL,
  PRIMARY KEY (`content_id`,`term`,`type`),
  UNIQUE KEY `saving` (`term`,`saving`,`random`,`content_id`,`type`),
  UNIQUE KEY `random` (`term`,`random`,`content_id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

产品可以通过两种方式列出:随机顺序(基于随机列)或按折扣(基于节省列)。过去的测试表明，使用 UNIQUE 顺序约束比使用标准索引与 ORDER BY 结合使用的性能要高得多。查询可以如下所示:

mysql> EXPLAIN SELECT content_id FROM store_search USE INDEX (random) WHERE term LIKE 'shirt%' AND type='keyword' LIMIT 2000,100;
+----+-------------+--------------+-------+---------------+--------+---------+------+---------+--------------------------+
| id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows    | Extra                    |
+----+-------------+--------------+-------+---------------+--------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | store_search | range | random        | random | 152     | NULL | 9870580 | Using where; Using index |
+----+-------------+--------------+-------+---------------+--------+---------+------+---------+--------------------------+

所以我可以阻止 ORDER BY 子句(使用这种方法不会完成文件排序)。 PRIMARY KEY 用于搜索多个术语时的自联接:

mysql> EXPLAIN SELECT DISTINCT x.content_id
    -> FROM store_search x USE INDEX (saving)
    -> INNER JOIN store_search y ON x.content_id=y.content_id
    -> WHERE x.term LIKE 'shirt%' AND x.type='keyword' AND y.term LIKE 'blue%' AND y.type='keyword'
    -> LIMIT 0,100;
+----+-------------+-------+-------+-----------------------+---------+---------+--------------+----------+-------------------------------------------+
| id | select_type | table | type  | possible_keys         | key     | key_len | ref          | rows     | Extra                                     |
+----+-------------+-------+-------+-----------------------+---------+---------+--------------+----------+-------------------------------------------+
|  1 | SIMPLE      | x     | range | PRIMARY,saving,random | saving  | 152     | NULL         | 11449970 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | y     | ref   | PRIMARY,saving,random | PRIMARY | 4       | x.content_id |       20 | Using where; Using index; Distinct        |
+----+-------------+-------+-------+-----------------------+---------+---------+--------------+----------+-------------------------------------------+

正如我所说，到目前为止这个解决方案都很好。我现在的问题是:这个表目前太大了(~500mio 行)，索引不再适合内存了。这导致 INSERT 和 UPDATE 语句非常慢。数据占用 23GB，索引占用 32GB，因此该表总共需要 55GB。测试是可能的，但是在复制该表时会消耗大量时间，但是有没有人有办法减少索引大小？我想将字符串列的排序规则转换为 latin_1，但我可以合并一些索引吗？

Answer 1

术语 LIKE 'shirt%' 是范围查找。 INDEX(term, ...) 不会通过过滤 term 来获取 type 或其他列。

这一点以及其他基本索引原则在我的 Index Cookbook 中进行了讨论。 .

所以... WHERE term LIKE 'shirt%' AND type='keyword' 请求 INDEX(keyword, term)。添加任何其他列将无助于过滤。

但是...你所依赖的是覆盖。这是所有所需列都位于单个索引中的地方。在这种情况下，可以在索引BTree中执行查询，而无需触及数据BTree。也就是说，添加额外的列可能是有益的。

发生了多件事

SELECT  content_id
    FROM  store_search USE INDEX (random)
    WHERE  term LIKE 'shirt%'
      AND  type='keyword'
    LIMIT  2000,100; 
UNIQUE KEY `random` (`term`,`random`,`content_id`,`type`)

以下是一些:

• 索引处于“覆盖”状态。
• 没有 ORDER BY，因此输出可能首先按 term 排序(假设可能有多个以 'shirt' 开头的短语)，并且仅其次是随机。这并不完全是您想要的，但可能会起作用。
• LIMIT 要求扫描索引的 2000+100 行，然后退出。如果没有足够的衬衫，它就会停止。这可能看起来“很快”。
• UNIQUE 可能是不相关的，并且对于插入来说是浪费。

下一个查询让我们剖析SELECT DISTINCT x.content_id ...。

• 您已将“filesort”替换为类似(可能更快)的 DISTINCT 代码。可能没有净 yield ；计时。
• 如果有 999 件蓝色衬衫，它将找到所有 999 件，然后区分它们，然后交付其中的 100 件。
• 如果没有 ORDER BY，您将无法预测将交付哪 100 个。
• 由于您已经收集了全部 999 个，因此添加 ORDER BY RAND() 不会增加太多开销。
• 您真的希望退回“蓝绿色”衬衫，而不是“浅蓝色”衬衫吗？那么“dress%”选择“礼服裤”怎么样？变态。

底线

• 将 3 个索引替换为 PRIMARY KEY(type, term, content_id)。通过PK，你可以有效地获得“掩护”。
• 使用ORDER BY random或ORDER BY RAND()——看看哪个更适合您。 (后者更加随机!)
• 重新考虑 LIKE 'shirt%' 中的通配符

最重要的是 EAV 模式设计很糟糕。我讨论这个further .