mysql优化器只是忽略了我用来在复合索引中排序的最后一列

kiz8lqtg  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(411)

我有一个表包含大约300万行,其结构如下:

CREATE TABLE `profiles3m` (
  `uid` int(10) unsigned NOT NULL,
  `birth_date` date NOT NULL,
  `gender` tinyint(4) NOT NULL DEFAULT '0',
  `country` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ID',
  `city` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Makassar',
  `created_at` timestamp NULL DEFAULT NULL,
  `premium` tinyint(4) NOT NULL DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT NULL,
  `latitude` double NOT NULL DEFAULT '0',
  `longitude` double NOT NULL DEFAULT '0',
  `orderid` int(11) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `idx_composites_latitude_longitude_gender_birth_date_created_at` (`latitude`,`longitude`,`country`,`city`,`gender`,`birth_date`) USING BTREE,
  KEY `idx_composites_country_city_gender_birth_date` (`country`,`city`,`gender`,`birth_date`,`orderid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我没有告诉mysql优化器使用复合索引定义中的所有列,似乎优化器只是忽略了最后一列 orderid 用于订购,仅为 uid 如您所知,innodb表中的主键列不能用于排序,因为它可能会指示优化器将主键用作索引,而不是使用我们的复合索引,这就是创建 orderid 列来自。
下面的sql查询以及explain json和show index语句可以显示表上的所有索引统计信息,这可能有助于分析原因。

SELECT
    pro.uid 
FROM
    `profiles3m` AS pro 
WHERE
    pro.country = 'INDONESIA' 
    AND pro.city IN ( 'MAKASSAR' ) 
    AND pro.gender = 0 
    AND ( pro.birth_date BETWEEN ( NOW()- INTERVAL 35 YEAR ) AND ( NOW()- INTERVAL 25 YEAR ) ) 
    AND pro.orderid > 0 
ORDER BY
    pro.orderid
LIMIT 30

解释json如下:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "45278.73"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "19051.43"
      },
      "table": {
        "table_name": "pro",
        "access_type": "range",
        "possible_keys": [
          "idx_composites_country_city_gender_birth_date"
        ],
        "key": "idx_composites_country_city_gender_birth_date",
        "used_key_parts": [
          "country",
          "city",
          "gender",
          "birth_date"
        ],
        "key_length": "488",
        "rows_examined_per_scan": 57160,
        "rows_produced_per_join": 19051,
        "filtered": "33.33",
        "using_index": true,
        "cost_info": {
          "read_cost": "22417.02",
          "eval_cost": "3810.29",
          "prefix_cost": "26227.30",
          "data_read_per_join": "9M"
        },
        "used_columns": [
          "uid",
          "birth_date",
          "gender",
          "country",
          "city",
          "orderid"
        ],
        "attached_condition": "((`restful`.`pro`.`gender` = 0) and (`restful`.`pro`.`country` = 'INDONESIA') and (`restful`.`pro`.`city` = 'MAKASSAR') and (`restful`.`pro`.`birth_date` between <cache>((now() - interval 35 year)) and <cache>((now() - interval 25 year))) and (`restful`.`pro`.`orderid` > 0))"
      }
    }
  }
}

下面是show index语句:

+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Non_unique | Key_name                                                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 0          | PRIMARY                                                        | 1            | uid         | A         | 2984412     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 1            | latitude    | A         | 2934360     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 2            | longitude   | A         | 2984080     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 3            | country     | A         | 2984080     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 4            | city        | A         | 2984080     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 5            | gender      | A         | 2984080     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_latitude_longitude_gender_birth_date_created_at | 6            | birth_date  | A         | 2984080     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_country_city_gender_birth_date                  | 1            | country     | A         | 1           |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_country_city_gender_birth_date                  | 2            | city        | A         | 14          |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_country_city_gender_birth_date                  | 3            | gender      | A         | 29          |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_country_city_gender_birth_date                  | 4            | birth_date  | A         | 362449      |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| 1          | idx_composites_country_city_gender_birth_date                  | 5            | orderid     | A         | 2984412     |          |        |      | BTREE      |
+------------+----------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

在explain json中真正有趣的是,他们告诉我们,如果优化器只能使用索引的四部分,那么毫无疑问,排序操作使用的是filesort,正如您所知,这意味着执行速度较慢,这对应用程序性能不利。 idx_composites_country_city_gender_birth_date ( country , city , gender , birth_date , orderid )

"ordering_operation": {
          "using_filesort": true,
.....

"key": "idx_composites_country_city_gender_birth_date",    
"used_key_parts": [
              "country",
              "city",
              "gender",
              "birth_date"
            ],

我错过了什么,是不是 RANGE 我方合同条款 WHERE 语句?,我已经用复合索引序列中的不同列组合进行了测试,例如我正在更改 orderid 带的列 premium 这是一个只包含0和1的标志列类型,mysql优化器可以利用所有五个列,那么为什么优化器不能对 orderid 列?这和基数有关吗?我不太确定,我唯一能保证的是我必须做出决定 ORDER BY 无论如何操作,都不会对应用程序性能产生任何影响。
这几天我一直在寻找答案,但还是解决不了。差点忘了提到mysql版本,以防万一。

+------------+
| version()  |
+------------+
| 5.7.29-log |
+------------+
jum4pzuy

jum4pzuy1#

您注意到它只使用索引的四列:

"used_key_parts": [
      "country",
      "city",
      "gender",
      "birth_date"
    ],

尽管where子句中的条件引用了所有五列:

WHERE
    pro.country = 'INDONESIA' 
    AND pro.city IN ( 'MAKASSAR' ) 
    AND pro.gender = 0 
    AND ( pro.birth_date BETWEEN ( NOW()- INTERVAL 35 YEAR ) AND ( NOW()- INTERVAL 25 YEAR ) ) 
    AND pro.orderid > 0

然而,这些条件有些不同。上的条件 country , city , gender 都是平等条件。一旦搜索找到包含这些值的索引子集,那么该子集将按顺序排列 birth_date 下一步,如果有一些行 birth_date ,这些是由 orderid .
就像你读了一本电话簿,发现所有姓“史密斯”的人,都是按名字排序的。如果有多个名字相同的人,他们会根据各自的电话号码在电话簿中进行订购。

Smith, Sarah 408-555-1234
Smith, Sarah 408-555-5678

但是如果你搜索所有姓史密斯的人和各种以“s”开头的名字呢?

Smith, Sam   408-555-3298
Smith, Sarah 408-555-1234
Smith, Sarah 408-555-5678
Smith, Stan  408-555-4224

这些不是按电话号码排序的。只有在前几列并列的情况下,它们才按姓、名、电话号码排序。
如果要按电话号码对它们进行排序,可以创建一个索引,其中列的顺序不同,如姓氏、电话号码和名字。

Smith 408-555-1234 Sarah
Smith 408-555-2020 David
Smith 408-555-3298 Sam
Smith 408-555-4100 Charlie
Smith 408-555-4224 Stan
Smith 408-555-5555 Annette
Smith 408-555-5678 Sarah

现在它们是按电话号码顺序排列的,但其中还有其他名称与以“s”开头的名字的条件不匹配。它们甚至没有按名字排序,因为名字的第三列只有在前两列并列时才会排序。
这指出了索引的一个普遍问题:您只能对等式比较中涉及的列重新排序。如果要对结果进行排序,则只有按索引中的列进行排序并且索引的所有前面的列仅用于相等比较时,才能使用索引。
在范围比较中引用一列后,将忽略索引中的任何后续列以进行搜索和排序。
换句话说:索引可以有任意数量的列作为相等条件,索引的下一列可以用于范围条件或对结果进行排序。但对于这两种操作,使用的列都不超过一列。
你不能优化一切。
回复您的评论:如果您在列上有索引 birth_date :

alter table profiles3m add key bk1 (country, city, gender, orderid);

然后解释显示没有文件排序:

EXPLAIN SELECT
    pro.uid 
FROM
    `profiles3m` AS pro 
WHERE
    pro.country = 'INDONESIA' 
    AND pro.city IN ( 'MAKASSAR' ) 
    AND pro.gender = 0 
    AND ( pro.birth_date BETWEEN ( NOW()- INTERVAL 35 YEAR ) AND ( NOW()- INTERVAL 25 YEAR ) ) 
    AND pro.orderid > 0 
ORDER BY
    pro.orderid
LIMIT 30\G

***************************1. row***************************

           id: 1
  select_type: SIMPLE
        table: pro
   partitions: NULL
         type: range
possible_keys: bk1
          key: bk1
      key_len: 489
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using where

(the) rows 看起来很低,因为我正在用一个空表测试。)
需要注意的是,它使用索引来匹配 country , city , gender ,和 orderid . 然后mysql将在 birth_date 困难的方法:一排一排。
但在那之后,优化器知道它已经按索引顺序获取了行,因此它知道这将自然地按顺序进行 orderid ,因此可以跳过文件排序。
这可能是也可能不是一个净胜球。这取决于有多少行是匹配的,但必须由上的条件抛出 birth_date . 以及评估每一行的条件是多么昂贵。这与使用索引进行筛选所节省的成本相比又有什么不同呢 birth_date .

z9ju0rcb

z9ju0rcb2#

mysql不能使用索引进行排序。你的状况 birthdate 表示索引中的行不是按顺序排列的 orderid .
我不认为有办法解决这个问题。

相关问题