MySQL 索引顺序

本文讨论了在 MySQL 中如何使用升序和降序索引来提高查询的性能。

在 MySQL 中，您可以在创建索引的时候指定索引的顺序。默认情况下，索引按照升序存储。

MySQL 索引语法

要指定索引的顺序，您可以按照如下的语法创建索引：

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_name [ASC | DESC], ...)
[algorithm_option | lock_option];

或者在创建表时直接创建索引，如下：

CREATE TABLE table_name(
  column_list
  INDEX index_name (column_name [ASC | DESC], ...)
);

这里， ASC 指定索引按照升序存储，它是默认值。 DESC 指定索引按照降序存储。

注意，直到 MySQL 8.0， MySQL 才真正支持降序索引。虽然之前的 MySQL 版本也支持 DESC 关键字，但是 MySQL 会忽略它。 这意味着， 在MySQL 8 之前，降序扫描索引的成本很高。

MySQL 索引顺序实例

首先，我们使用下面的语句创建一个 test 表用于测试：

CREATE TABLE test (
  v INT,
  INDEX idx_v_asc (v),
  INDEX idx_v_desc (v DESC)
);

这里，我们创建了两个索引：

  • idx_v_asc: 升序存储
  • idx_v_desc: 降序存储

然后我们使用下面的 sql 语句插入 10 万条数据：

INSERT INTO test (v)
SELECT (
    t4.i * 10000 + t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i
  ) v
FROM (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t0,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t1,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t2,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t3,
  (
    SELECT 0 i
    UNION
    SELECT 1
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    SELECT 4
    UNION
    SELECT 5
    UNION
    SELECT 6
    UNION
    SELECT 7
    UNION
    SELECT 8
    UNION
    SELECT 9
  ) t4
ORDER BY v;

Query OK, 100000 rows affected (1.24 sec)
Records: 100000  Duplicates: 0  Warnings: 0

以下语句根据 v 列升序检索行：

EXPLAIN
SELECT *
FROM test
ORDER BY v;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_v_asc | 5       | NULL | 100425 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

输出显示该语句使用了 idx_v_asc 索引，这对应了语句中的 ORDER BY v 子句。

以下语句根据 v 列降序检索行：

EXPLAIN
SELECT *
FROM test
ORDER BY v DESC;

+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_v_desc | 5       | NULL | 100425 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

输出显示该语句使用了 idx_v_desc 索引，这对应了语句中的 ORDER BY v DESC 子句。

结论

在 MySQL 中，您可以在创建索引的时候指定索引的顺序。默认情况下，索引按照升序存储。