按条件排序和分页

Question

我的数据库mysql中有一千条记录，我使用分页来检索只有10个结果。

当我在查询中添加一个order by时，查询变得缓慢了，但是当我省略它时，查询非常快。

我知道问题出在查询加载整个结果集，对其进行排序，然后获取这10条记录。

我没有使用索引，因为用于排序的列是主键，如果我没有错，在mysql中每个主键都会自动创建索引。

1. 为什么我的主键上的索引没有被使用？
2. 有没有其他替代方案可以不加载所有数据进行排序？
3. 如何将新插入的数据添加到表的第一行而不是表的末尾？

我的SQL查询语句：

    select distinct ...... order by appeloffre0_.ID_APPEL_OFFRE desc limit 10

我的索引

mysql> show index from appel_offre;
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| appel_offre |          0 | PRIMARY            |            1 | ID_APPEL_OFFRE      | A         |       13691 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_1 |            1 | ID_APPEL_OFFRE_MERE | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_2 |            1 | ID_ACHETEUR         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_3 |            1 | USER_SAISIE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | appel_offre_ibfk_4 |            1 | USER_VALIDATION     | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_3            |            1 | TYPE_MARCHE         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| appel_offre |          1 | ao_fk_5            |            1 | USER_CONTROLE       | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)

在解释命令中未选择索引：

+----+-------------+---------------+--------+-------------------------------------+--------------------+---------+----------------
| id | select_type | table         | type   | possible_keys                       | key                | key_len | ref
+----+-------------+---------------+--------+-------------------------------------+--------------------+---------+----------------
|  1 | SIMPLE      | appeloffre0_  | ALL    | NULL                                | NULL               | NULL    | NULL

更新解决方案

问题出在distinct，当我删除它后，查询最终使用了索引。

- Hayi

---

你的ao.idAppleOffre字段上有索引吗？ - kiks73

我没有创建任何索引，但我认为在MySQL中，每个主键都会自动创建一个索引。 - Hayi

尝试在用于排序的列上创建索引。对于您的第二个问题，在插入时没有“第一行”和“最后一行”。 - Michal

用于排序的列是主键，我认为在MySQL中每个主键都会自动创建索引。如果没有第一个或最后一个，为什么我得到的结果从第一行插入开始排序到最后一行？ - Hayi

当您不使用order by时，由于这是MySQL内部工作方式，您会得到按PK排序的行。然而，没有保证这种情况总是会保持不变，因为根据定义表数据是未排序的，所以在没有order by的情况下，MySQL可以自由地按任何可能的顺序提供结果。 - Michal

Answer 1

因为你已经在"USER_VALIDATION"上使用了索引，MySQL不会使用ID索引。

试着重建USER_VALIDATION索引以包括ID:

CREATE UNIQUE INDEX appel_offre_ibfk_4 ON appel_offre (USER_VALIDATION, ID);

更新

记录所有Hibernate查询，提取慢查询并在db控制台中使用EXPLAIN来了解MySQL为此查询选择的执行计划。即使您有索引，由于索引太大而无法适合内存，因此数据库可能会使用FULL TABLE SCAN。尝试按照此文章中所述的提示来解决问题。

根据MySQL ORDER BY 优化文档 ，您应该:

为了增加ORDER BY速度，请检查是否可以让MySQL使用索引而不是额外的排序阶段。如果不可能，您可以尝试以下策略：

• 增加sort_buffer_size变量值。

• 增加read_rnd_buffer_size变量值。

• 只声明足以容纳其中存储的值的大小的列，以减少每行使用的RAM。例如，如果值从未超过16个字符，则CHAR（16）比CHAR（200）更好。

• 将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。变量值可以列出多个以轮询方式使用的路径；您可以使用此功能将负载分布到多个目录中。在Unix上应使用冒号字符（“：”），在Windows、NetWare和OS/2上应使用分号字符（“;”）分隔路径。这些路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是位于同一磁盘的不同分区。

还要确保DISTINCT不会覆盖索引。尝试删除它，看看是否有所帮助。

- Vlad Mihalcea

---

尝试使用索引提示。 - Vlad Mihalcea

但它必须自动检测索引！！ - Hayi

引擎决定使用哪个索引，如果有的话，你最多只能建议一个索引。执行计划可能会考虑表扫描而不是索引，比如当索引不适合可用内存或者必须访问表时。 - Vlad Mihalcea

我已经更新了我的问题，并提供了完整的选择语句，今晚我会检查你的解决方案。 - Hayi

我也将您的解决方案添加到了这个答案中，因为它已经包含了一些很好的技巧。 - Vlad Mihalcea

Answer 2

1. 将你所排序的列添加索引。
2. 无法在表的开头添加行，就像无法在表的末尾添加行一样。数据库表是多集合。 多集合 的定义是无序集合。对于多集合而言，“第一个元素”或“最后一个元素”的概念毫无意义。

- Oswald

---

我不使用索引，因为用于排序的列是主键，我认为在MySQL中每个主键都会自动创建一个索引，如果我没记错的话。 - Hayi

如果没有“第一行”或“最后一行”，为什么我得到的结果会按照插入的第一行到最后一行进行排序？ - Hayi

MySQL没有义务按照插入的第一行到最后一行的顺序给您排序结果；有时它不会这样做。例如CREATE TABLE foo (a int) ENGINE=MyISAM; INSERT INTO foo (a) VALUES (1), (2), (3); DELETE FROM foo WHERE a=2; INSERT INTO foo (a) VALUES (4); SELECT * FROM foo会按照1、4、3的顺序给您结果。 - Oswald