+----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
| type | enum('Website','Facebook','Twitter','Linkedin','Youtube','SeatGeek','Yahoo') | NO | MUL | NULL | |
| name | varchar(100) | YES | MUL | NULL | |
| processing_interface_id | bigint(20) | YES | MUL | NULL | |
| processing_interface_table | varchar(100) | YES | MUL | NULL | |
| create_time | datetime | YES | MUL | NULL | |
| run_time | datetime | YES | MUL | NULL | |
| completed_time | datetime | YES | MUL | NULL | |
| reserved | int(10) | YES | MUL | NULL | |
| params | text | YES | | NULL | |
| params_md5 | varchar(100) | YES | MUL | NULL | |
| priority | int(10) | YES | MUL | NULL | |
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| status | varchar(40) | NO | MUL | none | |
+----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
select * from remote_request use index ( processing_order ) where remote_request.status = 'none' and type = 'Facebook' and reserved = '0' order by priority desc limit 0, 40;
此表接收非常大量的写入和读取。 每个remote_request最终都会成为一个进程,它可以生成 0 到 5 个其他remote_requests,具体取决于请求的类型以及请求的作用。
该表目前大约有 350 万条记录,当站点本身负载过重并且我同时运行超过 50 个或更多实例时,它会以蜗牛的速度运行。(REST 请求是表的目的,以防您不确定(。
随着桌子的增长,情况变得越来越糟。 我可以每天清除已处理的请求,但最终这并不能解决问题。
我需要的是让此查询始终具有非常低的响应率。
以下是表格上的当前索引。
+----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| remote_request | 0 | PRIMARY | 1 | id | A | 2403351 | NULL | NULL | | BTREE | | |
| remote_request | 1 | type_index | 1 | type | A | 18 | NULL | NULL | | BTREE | | |
| remote_request | 1 | processing_interface_id_index | 1 | processing_interface_id | A | 18 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | processing_interface_table_index | 1 | processing_interface_table | A | 18 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | create_time_index | 1 | create_time | A | 160223 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | run_time_index | 1 | run_time | A | 343335 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | completed_time_index | 1 | completed_time | A | 267039 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | reserved_index | 1 | reserved | A | 18 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | params_md5_index | 1 | params_md5 | A | 2403351 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | priority_index | 1 | priority | A | 716 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | status_index | 1 | status | A | 18 | NULL | NULL | | BTREE | | |
| remote_request | 1 | name_index | 1 | name | A | 18 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | processing_order | 1 | priority | A | 200 | NULL | NULL | YES | BTREE | | |
| remote_request | 1 | processing_order | 2 | status | A | 200 | NULL | NULL | | BTREE | | |
| remote_request | 1 | processing_order | 3 | type | A | 200 | NULL | NULL | | BTREE | | |
| remote_request | 1 | processing_order | 4 | reserved | A | 200 | NULL | NULL | YES | BTREE | | |
+----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
知道我如何解决这个问题吗?难道不可能制作某种复杂的索引,自动对它们进行优先级排序,然后选择与"Facebook"类型匹配的前 40 个吗? 它当前正在扫描超过 500k 行的表,然后返回效率极低的结果。
我一直在修补的其他查询版本是:
select * from remote_request use index ( type_index,status_index,reserved_index,priority_index ) where remote_request.status = 'none' and type = 'Facebook' and reserv ed = '0' order by priority desc limit 0, 40
如果我们可以根据进入表的请求类型的数量将扫描的行数增加到 1000 行以下,那就太神奇了。
提前感谢,除了最有经验的 mysql 专家之外,这可能是大多数人的真正胡桃夹子?
您的四列索引具有正确的列,但顺序错误。
您希望索引首先查找匹配的行,这是按三列查找的。 您正在按三个相等条件进行查找,因此您知道,一旦索引找到匹配行的集合,这些行的顺序基本上是相对于前三列的平局。 因此,要解决平局问题,请将要排序的列添加为第四列。
如果这样做,则ORDER BY
将变为无操作,因为查询只能按照行在索引中的存储顺序读取行。
所以我会创建以下索引:
CREATE INDEX processing_order2 ON remote_request
(status, type, reserved, priority);
前三列的顺序可能没有太大意义,因为它们都是相等的,与AND
. 但priority
列属于最后。
您可能还想阅读我的演示文稿如何设计索引,真的。
顺便说一下,如果你有正确的索引,就没有必要使用USE INDEX()
,MySQL的优化器大部分时间都会自动选择它。 但是USE INDEX()
可能会阻止优化器考虑您创建的新索引,因此它成为代码维护的缺点。
这不是一个完整的答案,但评论太长了:
您实际上是在搜索所有这些索引吗?如果没有摆脱一些。额外的索引会减慢写入速度。
其次,在查询中使用EXPLAIN
,并且在执行此操作时不要指定索引。看看MySQL想要如何处理它而不是强制一个选项(通常它做正确的事情(。
最后,排序可能是最伤害你的。 如果你不排序,它可能会很快得到记录。它必须扫描并排序符合您条件的每一行,然后才能返回前 40 行。
选项:
- 尝试创建一个视图(不熟悉视图,但它可能会起作用(
- 将此表拆分为较小的表
- 使用第三方工具,例如狮身人面像或Lucene创建专门的索引进行搜索。(我已经以前用狮身人面像做过这样的事情。您可以在以下位置找到它http://sphinxsearch.com/(。
- 或者考虑使用NoSQL解决方案,您可以在其中使用Map函数来做到这一点。
编辑 我读了一些关于使用 VIEW 的信息,我认为这对您没有帮助,因为您有这么大的桌子。查看此线程中的答案:使用 MySQL 视图提高性能