Question

我的网页上有一个现场搜索。在向服务器发出请求时,接收过滤和分类参数(发展中国家、出版商、基因、时间等)。问题在于,其执行需要相当长的时间,而且由于文字是用户所输入的每一种特性所触发的,这只能看上去。如果你在搜寻和接收开发商时忽略其他参数,那么你就会发现这种疑问(执行时间大约为0.14秒,但在打字时,其速度当然会放缓):

SELECT  games.id AS id
    FROM  games
    LEFT JOIN  games_titles  ON games.id=games_titles.game_id
    WHERE  games_titles.lang=1
      AND  EXISTS (
        SELECT  1
            FROM  games_devs
            WHERE  games_devs.game_id=games.id
              AND  games_devs.dev LIKE  de% 
                  )
    ORDER BY  games.rating DESC
    LIMIT  6

EXPLAIN:

+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
| id | select_type  | table        | type   | possible_keys      | key           | key_len | ref              | rows  | Extra                              |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | games        | index  | PRIMARY            | rating_index  | 8       | NULL             | 1     | Using index                        |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | <subquery2>  | eq_ref | distinct_key       | distinct_key  | 4       | func             | 1     | Using where                        |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | games_titles | ref    | game_id_index,lang | game_id_index | 4       | test_db.games.id | 1     | Using index condition; Using where |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  2 | MATERIALIZED | games_devs   | range  | PRIMARY,dev        | dev           | 452     | NULL             | 49994 | Using where; Using index           |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+

教授:

+----+------------------------+--------+
| 1  | Starting               | 103 µs |
+----+------------------------+--------+
| 2  | Checking Permissions   |  16 µs |
+----+------------------------+--------+
| 3  | Opening Tables         |  32 µs |
+----+------------------------+--------+
| 4  | After Opening Tables   |  11 µs |
+----+------------------------+--------+
| 5  | System Lock            |  11 µs |
+----+------------------------+--------+
| 6  | Table Lock             |  25 µs |
+----+------------------------+--------+
| 7  | Init                   |  48 µs |
+----+------------------------+--------+
| 8  | Optimizing             |  50 µs |
+----+------------------------+--------+
| 9  | Statistics             | 145 µs |
+----+------------------------+--------+
| 10 | Preparing              |  92 µs |
+----+------------------------+--------+
| 11 | Sorting Result         |  23 µs |
+----+------------------------+--------+
| 12 | Executing              |  46 µs |
+----+------------------------+--------+
| 13 | Query End              |  14 µs |
+----+------------------------+--------+
| 14 | Removing Tmp Table     |  13 µs |
+----+------------------------+--------+
| 15 | Query End              |   9 µs |
+----+------------------------+--------+
| 16 | Commit                 |  11 µs |
+----+------------------------+--------+
| 17 | Closing Tables         |  10 µs |
+----+------------------------+--------+
| 18 | Unlocking Tables       |   9 µs |
+----+------------------------+--------+
| 19 | Closing Tables         |  15 µs |
+----+------------------------+--------+
| 20 | Starting Cleanup       |   9 µs |
+----+------------------------+--------+
| 21 | Freeing Items          |  19 µs |
+----+------------------------+--------+
| 22 | Updating Status        |  73 µs |
+----+------------------------+--------+
| 23 | Reset For Next Command |  19 µs |
+----+------------------------+--------+

自2006年以来我在现场有想象力,我需要获得所有条目的数目,以便我能够进入最后一页。但是,在这种情况下,业绩受到的影响要大得多(1.97秒在搜索页时):

SELECT games.id AS id FROM games LEFT JOIN games_titles ON games.id=games_titles.game_id WHERE games_titles.lang=1 AND EXISTS(SELECT 1 FROM games_devs WHERE games_devs.game_id=games.id AND games_devs.dev LIKE  de% ) ORDER BY games.rating

EXPLAIN:

+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
| id | select_type  | table        | type   | possible_keys      | key           | key_len | ref              | rows  | Extra                              |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | games        | index  | PRIMARY            | rating_index  | 8       | NULL             | 99889 | Using index                        |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | <subquery2>  | eq_ref | distinct_key       | distinct_key  | 4       | func             | 1     | Using where                        |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY      | games_titles | ref    | game_id_index,lang | game_id_index | 4       | test_db.games.id | 1     | Using index condition; Using where |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+
|  2 | MATERIALIZED | games_devs   | range  | PRIMARY,dev        | dev           | 452     | NULL             | 49994 | Using where; Using index           |
+----+--------------+--------------+--------+--------------------+---------------+---------+------------------+-------+------------------------------------+

教授:

+----+------------------------+--------+
| 1  | Starting               | 150 µs |
+----+------------------------+--------+
| 2  | Checking Permissions   |  19 µs |
+----+------------------------+--------+
| 3  | Opening Tables         |  30 µs |
+----+------------------------+--------+
| 4  | After Opening Tables   |  12 µs |
+----+------------------------+--------+
| 5  | System Lock            |  11 µs |
+----+------------------------+--------+
| 6  | Table Lock             |  13 µs |
+----+------------------------+--------+
| 7  | Init                   |  57 µs |
+----+------------------------+--------+
| 8  | Optimizing             |  49 µs |
+----+------------------------+--------+
| 9  | Statistics             | 142 µs |
+----+------------------------+--------+
| 10 | Preparing              |  93 µs |
+----+------------------------+--------+
| 11 | Sorting Result         |  22 µs |
+----+------------------------+--------+
| 12 | Executing              |  21 µs |
+----+------------------------+--------+
| 13 | Sending Data           | 182 ms |
+----+------------------------+--------+
| 14 | End Of Update Loop     |  28 µs |
+----+------------------------+--------+
| 15 | Removing Tmp Table     | 790 µs |
+----+------------------------+--------+
| 16 | End Of Update Loop     |  15 µs |
+----+------------------------+--------+
| 17 | Query End              |   9 µs |
+----+------------------------+--------+
| 18 | Commit                 |  11 µs |
+----+------------------------+--------+
| 19 | Closing Tables         |  10 µs |
+----+------------------------+--------+
| 20 | Unlocking Tables       |   9 µs |
+----+------------------------+--------+
| 21 | Closing Tables         |  17 µs |
+----+------------------------+--------+
| 22 | Starting Cleanup       |   9 µs |
+----+------------------------+--------+
| 23 | Freeing Items          |  19 µs |
+----+------------------------+--------+
| 24 | Updating Status        |  75 µs |
+----+------------------------+--------+
| 25 | Reset For Next Command |  18 µs |
+----+------------------------+--------+

CREATE TABLE games ( 
  id int(10) unsigned NOT NULL AUTO_INCREMENT, 
  rating double NOT NULL DEFAULT 0, 
  date date DEFAULT NULL, 
  date_type int(1) NOT NULL DEFAULT 1, 
  img varchar(500) DEFAULT NULL, 
  img_type int(10) NOT NULL, 
  url varchar(100) NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY rating_index (`rating`) USING BTREE, 
  KEY date_index (`date`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=326678 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

CREATE TABLE games_titles ( 
  game_id int(50) NOT NULL, 
  title varchar(150) DEFAULT NULL, 
  lang int(10) NOT NULL DEFAULT 1, 
  KEY game_id_index (`game_id`) USING BTREE, 
  KEY lang (`lang`,`title`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci 

CREATE TABLE games_devs ( 
  game_id int(50) NOT NULL, 
  dev varchar(150) NOT NULL, 
  PRIMARY KEY (`game_id`,`dev`), 
  KEY dev (`dev`,`game_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

请求还用语文进行核对,因为网站是多种语文的。但我决定删除这些细节,因为这里的主要负荷仍然来自搜索本身。我没有把指数放在“发展中国家”领域,而是为其他领域设定了一切。你可以看到上述表格的结构。

您能告诉我,我如何能够改进搜索工作,或者说,与当地和科索沃电力公司经营者相比,你可以提供什么选择? 我听到,许多人不建议使用否决权。现在,在“发展中国家”表中,这些条目有“dev_1”、“dev_2”等形式。如果你将字母“p”改为“p”,则该字母的“d”,当然绕过所有选项需要更长时间。当然,我的理解是,在实际生产方面,书面形式将更加多样,但在这里,最佳解决办法是什么? 在进行规划期间,每次新页的搜索时间都会增加(从逻辑上讲,MySQL算法需要产生新的数据集,但从最初的位置上看不出),恐怕需要很长的时间,因此现在的搜索优化是我的主要目标。

Answer 1

A few things are worth mentioning about this.

在你描述的风格中逐步进行自动化搜索是一个好主意;它使用户经验丰富,帮助人们找到他们迅速寻求的东西。
column LIKE %searchterm% with a leading % is a notorious query performance antipattern. Avoid it.
在你的网页《 Java字典》中,它有一个好的想法,即避免向每一个关键中风发出新的搜索要求。如果你写一些 Java字,以发送(用户分类的)在最后关键中风后固定时间的累积搜索数据,你就更不用了。因此,例如,如果我很快打字,在你发出字前,我可以打上“鼓励”一词。但是,如果我在“选择”之后犹豫不决,那就会出现这种情况。有100毫秒是好的拖延,但你可以试验。

这减轻了服务机的搜索负担,避免了用户经验过于滥用。 (关于其价值: remote data 载于https://jqueryui.com/atocomplete/“rel=“noestlow noreferer”的守则 Qu get get get

Correlated (dependent) subqueries are worth avoiding.

从您的榜样中得出这一疑问。

SELECT games.id AS id
   FROM games
   LEFT JOIN games_titles ON games.id=games_titles.game_id
  WHERE games_titles.lang=1
    AND EXISTS( SELECT 1 
                 FROM games_devs
                 WHERE games_devs.game_id=games.id
                   AND games_devs.dev LIKE  de% 
              )
 ORDER BY games.rating DESC LIMIT 6

如果你这样认为,就会更快。 JOIN与Lex取得相同的过滤行为。

SELECT games.id AS id
   FROM games
   JOIN ( SELECT MAX(game_id) game_id
            FROM games_devs
           WHERE dev LIKE  de%
        ) AS does_dev_exist  
             ON does_dev_exist.game_id = games.id
  WHERE games_titles.lang=1
 ORDER BY games.rating DESC LIMIT 6

这一指数将有助于加快经过改进的分区。

CREATE INDEX dev_game_id ON games_devs(dev, game_id);

表格通常需要多栏索引,与你使用的问题相匹配。每个分局——每一行EXPLAIN——只能使用一个指数。

The indexes you need aren t knowable except with the precise queries you use. This particular index will help your games_titles lookup in your sample query.

CREATE INDEX lang_game_id ON games_titles(lang, game_id);

To efficiently get a total (nonpaginated) result set count do SELECT COUNT(*) and skip the ORDER BY operation. Always skip ORDER BY unless you know you need it. Better yet, if you can figure out how to avoid needing the total result set count in your autocompletion UI, do that instead.

友情链接