mysql> explain SELECT * -> FROM (`items`) -> WHERE `states` = 'A' -> ORDER BY `updated_date` desc -> LIMIT 20520, 40; +----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+ | 1 | SIMPLE | items | ref | states_updated_date,states | states_updated_date | 6 | const | 19040 | Using where | +----+-------------+--------+--------+----------------------------+---------------------+---------+----------+-------+-------------+ mysql> describe items; +---------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | url | varchar(255) | NO | UNI | NULL | | | vendor | varchar(255) | NO | | NULL | | | title | varchar(255) | NO | | NULL | | | price | int(11) | YES | | NULL | | | contact_seller_link | varchar(255) | NO | | NULL | | | seller | varchar(255) | NO | | NULL | | | query_count | varchar(7) | NO | | NULL | | | warrenty | varchar(63) | NO | | NULL | | | created_date | varchar(63) | NO | | NULL | | | notes_count | varchar(7) | NO | | NULL | | | views_count | varchar(7) | NO | | NULL | | | effective_date | varchar(63) | NO | | NULL | | | updated_date | varchar(63) | NO | MUL | NULL | | | images | text | NO | | NULL | | | description | text | NO | | NULL | | | extra_comment | text | NO | | NULL | | | queries | text | NO | | NULL | | | crawl_date | datetime | NO | | NULL | | | brand | varchar(63) | NO | MUL | NULL | | | states | varchar(1) | YES | MUL | NULL | | | focal_length_id | int(9) | YES | MUL | NULL | | +---------------------+--------------+------+-----+---------+----------------+ mysql> show index from items;; +-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | items | 0 | PRIMARY | 1 | id | A | 124498 | NULL | NULL | | BTREE | | | items | 0 | url | 1 | url | A | 124498 | NULL | NULL | | BTREE | | | items | 1 | focal_length_id | 1 | focal_length_id | A | 669 | NULL | NULL | YES | BTREE | | | items | 1 | focal_length_id_states | 1 | focal_length_id | A | 308 | NULL | NULL | YES | BTREE | | | items | 1 | focal_length_id_states | 2 | states | A | 604 | NULL | NULL | YES | BTREE | | | items | 1 | brand | 1 | brand | A | 26 | NULL | NULL | | BTREE | | | items | 1 | updated_date | 1 | updated_date | A | 124498 | NULL | NULL | | BTREE | | | items | 1 | states_updated_date | 1 | states | A | 4 | NULL | NULL | YES | BTREE | | | items | 1 | states_updated_date | 2 | updated_date | A | 124498 | NULL | NULL | | BTREE | | | items | 1 | states | 1 | states | A | 10 | NULL | NULL | YES | BTREE | | +-------+------------+------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ 10 rows in set (0.13 sec) 它正在处理每个查询19k行，数据集约40k。 我可以通过添加索引来改善它？ 或者增加使用我的MySQL的内存？
SELECT的MySQL负载应该是CPU绑定的，而不是I / O绑定的。 如果是I / O绑定，那么你应该：
您需要创build的索引在WHERE和ORDER BY子句，状态和updated_date中给出
ALTER TABLE items ADD INDEX states_updated_date_ndx (states,updated_date);
之后重新运行您的EXPLAIN计划。
您也可以像这样运行PROCEDURE ANALYZE（）来减小列的大小：
SELECT states,updated_date FROM items PROCEDURE ANALYSE();
这不会显示所有的行。 它将简单地通过所有的行并确定状态和date_updated列的最佳数据types。
如果表项是MyISAM，则必须增加key_buffer_size。 如果是InnoDB，增加innodb_buffer_pool_size。 至less， 确保所有的caching已经足够大了 。
试一试 ！！！
您可以通过将states栏更改为CHAR（1）来缩小索引的大小。 较小的索引更适合记忆。
甚至不考虑没有索引或键的性能。