MySQL多到大的关系在大桌上缓慢

Question

我有2个与关系表连接的表。

有关表的更多详细信息：

• 商店（当前140.000行）

id (index) store_name city_id (index) ...

• 类别（当前400行）

id (index) cat_name

• store_cat_relation

store_id cat_id

每个商店都属于一个或多个类别。

在store_cat_relation表中，我在（store_id，cat_id）和（cat_id，store_id）上有索引。

我需要在巴黎（city_id = 1）中找到超市（CAT_ID = 1）的总数。我有一个工作查询，但是当数据库在巴黎包含大量商店或数据库中有很多超市时，需要花费太长时间。这是我的查询：

SELECT COUNT(*) FROM stores s, store_cat_relation r WHERE s.city_id = '1' AND r.cat_id = '1' AND s.id = r.store_id

此查询大约需要0,05。数据库包含约8000个超市（具有类别1的商店）和巴黎的大约8000家商店（Store_id = 1）。目前在巴黎合并了550个超市。

我想将查询时间降低到0,01以下，因为数据库只会越来越大。

解释的结果是：

id: 1
select_type: SIMPLE
table: store_cat_relation
type: ref
possible_keys: cat_id_store_id, store_id_cat_id
key: cat_id_store_id
key_len: 4
ref: const
rows: 8043
Extra: Using index
***************************************
id: 1
select_type: SIMPLE
table: stores
type: eq_ref
possible_keys: PRIMARY, city_id
key: PRIMARY
key_len: 4
ref: store_cat_relation.store_id
rows: 1
Extra: Using index condition; Using where

有人知道为什么此查询需要这么长时间？

编辑：我还创建了一个SQL小提琴，每个表有300行。行量较低，它很快，但是我需要使用 100.000行快速。

http://sqlfiddle.com/#！9/675a3/1

Answer 1

我已经进行了一些测试，最好的性能是使用 QUERY CAD CACHE 。您可以启用它们并按需使用。因此，您可以说哪些查询已插入缓存。如果要使用它，则必须在/etc/my.cnf中进行更改，以使其持久。如果更改桌子，也可以运行一些查询来热身缓存

在这里样本

表大小

MariaDB [yourSchema]> select count(*) from stores;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1 min 23.50 sec)
MariaDB [yourSchema]> select count(*) from store_cat_relation;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.45 sec)
MariaDB [yourSchema]>

验证缓存在

上

MariaDB [yourSchema]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

设置缓存大小和按需

MariaDB [yourSchema]> SET GLOBAL query_cache_size = 1000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [yourSchema]> SET GLOBAL query_cache_type=DEMAND;
Query OK, 0 rows affected (0.00 sec)

启用分析

MariaDB [yourSchema]> set profiling=on;

首先执行您的查询 - 服用0.68 sec

MariaDB [yourSchema]> SELECT SQL_CACHE COUNT(*) FROM stores s, store_cat_relation r WHERE s.city_id = '1' AND r.cat_id = '1' AND s.id = r.store_id;
+----------+
| COUNT(*) |
+----------+
|      192 |
+----------+
1 row in set (0.68 sec)

现在从缓存获取

MariaDB [yourSchema]> SELECT SQL_CACHE COUNT(*) FROM stores s, store_cat_relation r WHERE s.city_id = '1' AND r.cat_id = '1' AND s.id = r.store_id;
+----------+
| COUNT(*) |
+----------+
|      192 |
+----------+
1 row in set (0.00 sec)

请参阅美国持续时间的配置文件

MariaDB [yourSchema]> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000039 |
| Waiting for query cache lock   | 0.000008 |
| init                           | 0.000005 |
| checking query cache for query | 0.000056 |
| checking privileges on cached  | 0.000026 |
| checking permissions           | 0.000014 |
| checking permissions           | 0.000025 |
| sending cached result to clien | 0.000027 |
| updating status                | 0.000048 |
| cleaning up                    | 0.000025 |
+--------------------------------+----------+
10 rows in set (0.05 sec)
MariaDB [yourSchema]>

Answer 2

您正在查看的是索引方案：

使用优化器A DBMS试图找到数据的最佳路径。根据数据本身，这可能会导致不同的访问路径，具体取决于所提供的条件（/组合/组，有时是按订单）。其中的数据分布可能是快速查询或非常缓慢的查询的关键。

因此，您目前有2个表，store和store_cat_relation。在商店中，您有2个索引：

• ID（主要）
• city_id

您有一个在city_id上的位置，并且ID上有一个连接。然后，DBMS引擎中的内部执行如下：

1）阅读索引city_id2）然后读取表（确定，主键索引）以查找ID3）加入ID

通过多列索引可以更优化这一点：

CREATE INDEX idx_nn_1 ON store(city_id,id);

这应该导致：

1）读取索引IDX_NN_12）使用此索引IDX_NN_1

加入

您在当前示例中确实具有相当落的数据，其中所有city_id=1在您的示例中。在真实数据中，数据的分布可以给您带来问题，因为where city_id=然后类似于说"只需从表存储中选择所有内容"。在此类情况下，该列上的直方图信息可能会导致不同的计划，但是，如果您的数据分布不那么侧面，则应该很好地工作。

在您的第二个表store_cat_relation上您可以尝试这样的索引：

CREATE INDEX idx_nn_2 ON store_cat_relation(store_id,cat_id);

查看DBM是否决定导致更好的数据访问路径。

在您看到的每一次加入时，都会研究加入，看看多列索引是否可以减少读数。

请勿索引所有列：索引中的列太多会导致插入和更新较慢。

还可能需要一些方案以不同的顺序创建索引，从而导致表上有许多索引（一个具有列（1,2,3），下一列（1,3,2）等）。这也不是一个真正的快乐场景，其中单列或列的限制，仅读取第2,3列的表格。

索引需要测试您最常见的方案，这可能很有趣，因为您会看到一个缓慢的查询如何在100秒内突然在100秒内突然运行，甚至更快地运行。