mysql - 在有序的范围查询上优化 mysql 索引

我正在寻求有关我目前针对我的服务器运行的一些攻击性 mysql 查询的帮助。我的目标是展示结束时间不到一个月前的最昂贵的 eBay 商品。我正在使用 MySQL 5.1。我的查询如下('ebay_items' 有 ~350,000 行):explain SELECT `ebay_items`.* FROM `ebay_items`WHERE (endtime > NOW()-INTERVAL 1 MONTH) ORDER BY price desc\G;产量:*************************** 1. row ***************************id: 1select_type: SIMPLEtable: ebay_itemstype: rangepossible_keys: endtimekey: endtimekey_len: 9ref: NULLrows: 71760Extra: Using where; Using filesort1 row in set (0.00 sec)此查询导致使用 71760 行的昂贵的“文件排序”。show indexes on ebay_items;产量(我只包括了有问题的索引，“结束时间”):*************************** 7. row ***************************Table: ebay_itemsNon_unique: 1Key_name: endtimeSeq_in_index: 1Column_name: endtimeCollation: ACardinality: 230697Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:*************************** 8. row ***************************Table: ebay_itemsNon_unique: 1Key_name: endtimeSeq_in_index: 2Column_name: priceCollation: ACardinality: 230697Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:仅使用复合结束时间索引(结束时间、价格)的“结束时间”键。据我所知，MySQL 在处理范围查询和 'order by' 子句时不会有效地使用复合索引。有没有人找到解决这个问题的好方法？我主要想在数据库级别解决它(通过更智能地使用索引或架构更改)，但我愿意接受建议。我可以避免范围查询的一​​种方法是让后台任务每 X 小时循环一次，并将 ebay_items 上的枚举类型字段标记为“有没有办法用 order by 子句执行 MySQL 范围查询，以有效的方式查询？非常感谢您的帮助!编辑:Kohányi Róbert 提出了一个很好的观点，即我应该澄清我在查询中遇到的确切问题。查询导致磁盘 I/O 在其持续时间内被挂起。如果其中几个查询同时运行，进程就会备份并且机器锁定。我的假设是文件排序正在吃掉 I/O。我还应该提到该表使用的是 MyISAM 引擎。使用 InnoDB 引擎是否会提高性能和减少 I/O 密集度？再次感谢。 最佳答案 介绍我喜欢你的问题，所以我玩了一点 MySQL 并试图找到问题的根源。为此，我创建了一些测试。数据我使用名为 Random Data Generator 的工具生成了 100.000 行样本数据(我认为文档有点过时，但它有效)。我传给gendata.pl的配置文件如下。 $tables = { rows => [100000], names => ['ebay_items'], engines => ['MyISAM'], pk => ['int auto_increment']};$fields = { types => ['datetime', 'int'], indexes => [undef]};$data = { numbers => [ 'tinyint unsigned', 'smallint unsigned', 'smallint unsigned', 'mediumint unsigned' ], temporals => ['datetime']};我运行了两批独立的测试:一个使用 MyISAM 表，另一个使用 InnoDB。 (所以基本上你在上面的代码片段中用 InnoDB 替换了 MyISAM。) table 该工具会创建一个表，其中的列名为 pk 、 col_datetime 和 col_int 。我已将它们重命名以匹配您表的列。结果表就在下面。 +---------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+----------+------+-----+---------+----------------+| endtime | datetime | YES | MUL | NULL | || id | int(11) | NO | PRI | NULL | auto_increment || price | int(11) | YES | MUL | NULL | |+---------+----------+------+-----+---------+----------------+指数该工具不创建索引，因为我希望它手动创建它们。 CREATE INDEX `endtime` ON `ebay_items` (endtime, price);CREATE INDEX `price` ON `ebay_items` (price, endtime);CREATE INDEX `endtime_only` ON `ebay_items` (endtime);CREATE INDEX `price_only` ON `ebay_items` (price);询问我使用过的查询。 SELECT `ebay_items`.*FROM `ebay_items`FORCE INDEX (`endtime|price|endtime_only|price_only`)WHERE (`endtime` > '2009-01-01' - INTERVAL 1 MONTH)ORDER BY `price` DESC(使用其中一个索引的四个不同查询。我使用 2009-01-01 而不是 NOW() 因为该工具似乎生成了 2009 年左右的日期。)解释这是上面查询的 EXPLAIN 输出，用于 ​​MyISAM(顶部)和 InnoDB(底部)表上的每个索引。时间结束 id: 1 select_type: SIMPLE table: ebay_items type: rangepossible_keys: endtime key: endtime key_len: 9 ref: NULL rows: 25261 Extra: Using where; Using filesort id: 1 select_type: SIMPLE table: ebay_items type: rangepossible_keys: endtime key: endtime key_len: 9 ref: NULL rows: 21026 Extra: Using where; Using index; Using filesort价格 id: 1 select_type: SIMPLE table: ebay_items type: indexpossible_keys: NULL key: price key_len: 14 ref: NULL rows: 100000 Extra: Using where id: 1 select_type: SIMPLE table: ebay_items type: indexpossible_keys: NULL key: price key_len: 14 ref: NULL rows: 100226 Extra: Using where; Using indexendtime_only id: 1 select_type: SIMPLE table: ebay_items type: rangepossible_keys: endtime_only key: endtime_only key_len: 9 ref: NULL rows: 11666 Extra: Using where; Using filesort id: 1 select_type: SIMPLE table: ebay_items type: rangepossible_keys: endtime_only key: endtime_only key_len: 9 ref: NULL rows: 21270 Extra: Using where; Using filesort价格_only id: 1 select_type: SIMPLE table: ebay_items type: indexpossible_keys: NULL key: price_only key_len: 5 ref: NULL rows: 100000 Extra: Using where id: 1 select_type: SIMPLE table: ebay_items type: indexpossible_keys: NULL key: price_only key_len: 5 ref: NULL rows: 100226 Extra: Using where基于这些，我决定使用 endtime_only 索引进行我的测试，因为我也必须对 MyISAM 和 InnoDB 表运行查询。但是正如您所看到的，最合乎逻辑的 endtime 索引似乎是最好的。测试为了使用 MyISAM 和 InnoDB 表测试查询的效率(关于生成的 I/O 事件)，我编写了以下简单的 Java 程序。 static final String J = "jdbc:mysql://127.0.0.1:3306/test?user=root&password=root";static final String Q = "SELECT * FROM ebay_items FORCE INDEX (endtime_only) WHERE (endtime > '2009-01-01'-INTERVAL 1 MONTH) ORDER BY price desc;";public static void main(String[] args) throws InterruptedException { for (int i = 0; i < 1000; i++) try (Connection c = DriverManager.getConnection(J); Statement s = c.createStatement()) { TimeUnit.MILLISECONDS.sleep(10L); s.execute(Q); } catch (SQLException ex) { ex.printStackTrace(); }}设置我在 Dell Vostro 1015 笔记本电脑、Intel Core Duo T6670 @ 2.20 GHz、4 GB RAM 上运行 MySQL 5.5 的 Windows 二进制文件。 Java 程序通过 TCP/IP 与 MySQL 服务器进程通信。状态在使用 MyISAM 和 InnoDB(使用 Process Explorer )对表运行测试之前和之后，我捕获了 mysqld 进程的状态。前之后——MyISAM之后——InnoDB结论基本上，这两次运行仅在单个 I/O 读取的数量上有所不同，当表使用 MyISAM 引擎时，这非常大。这两个测试都运行了 50-60 秒。在 MyISAM 引擎的情况下，CPU 的最大负载约为 42%，而使用 InnoDB 时约为 38。我不太确定大量 I/O 读取的含义是什么，但在这种情况下，越小越好(可能)。如果您的表中有更多列(除了您指定的列)并且有一些非默认 MySQL 配置(关于缓冲区大小等)，则 MySQL 可能会使用磁盘资源。关于mysql - 在有序的范围查询上优化 mysql 索引，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8372121/