首页 文章

MariaDB中的SQL速度慢

提问于
浏览 次
0

MariaDB的 . 服务器版本：5.5.5-10.1.18-MariaDB MariaDB Server

有3个表： `1. City: 4,000 rows (has foreign key country_id to Country.id and index) 2. Street: 40,000 rows (has foreign key city_id to City.id and index) 3. House: 4,000,000,000 rows (has foreign key to Street.id and index)` 以下SQL从未完成： `select count(*) from House , Street WHERE Street.city_id IN (SELECT id FROM City WHERE country_id=177) and Street.id=House.street_id;` 但是如果在嵌套的SELECT中而不是原始代码：

（SELECT id FROM City WHERE country_id = 177）

我放

SELECT id FROM City WHERE id IN（4617,4618）

当它运行<0.5秒

以下是慢速查询的解释计划

+------+-------------+-------+--------+----------------------------+------------------------+---------+------------------+------------+-------------+
| id   | select_type | table | type   | possible_keys               | key                   | key_len | ref              | rows       | Extra       |
+------+-------------+-------+--------+-----------------------------+-----------------------+---------+------------------+------------+-------------+
|    1 | PRIMARY     | House | index  | IDX_House_streetIndex       | IDX_House_streetIndex | 11      | NULL             | 4,000,000,000 | Using index |
|    1 | PRIMARY     | Street| eq_ref | PRIMARY,IDX_DF9A1AD51E5D0459| PRIMARY               | 4       | House.street_id  |          1 |             |
|    1 | PRIMARY     | City  | eq_ref | PRIMARY,IDX_784DD132166D1F9C| PRIMARY               | 4       | Street.city_id   |          1 | Using where |
+------+-------------+-------+--------+------------------------------------------+------------------------------------------+---------+———————
mysql mariadb

2 回答

  • 0

    试试这个：

    CREATE INDEX i_1 ON City(id);
CREATE INDEX i_2 ON City(country_id);

CREATE INDEX i_3 ON Street(id)
CREATE INDEX i_4 ON Street(city_id)

CREATE INDEX i_5 ON House(street_id);
    回复于
  • 0

    不要使用 IN ( SELECT ... ) ;它通常表现不佳 . 相反，使用等效的 JOIN .

    select  count(*)
    FROM  City
    JOIN  Street  ON Street.city_id = City.id
    JOIN  House   ON House.street_id = Street.id
    WHERE  City.country_id=177

    然后，您需要这些索引：

    City:    INDEX(country_id, id)
Street:  INDEX(city_id, id)
House:   INDEX(street_id)

    在提出这类问题时，请提供 SHOW CREATE TABLE .

    40亿房子？我怀疑世界上是否有这么多人！

    如果这些建议不充分，则重新考虑表格是否“过度标准化” .

    回复于

相关问题