我有一个表包含大约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 |
+------------+
2条答案
按热度按时间jum4pzuy1#
您注意到它只使用索引的四列：
尽管where子句中的条件引用了所有五列：
然而，这些条件有些不同。上的条件
country,city,gender都是平等条件。一旦搜索找到包含这些值的索引子集，那么该子集将按顺序排列birth_date下一步，如果有一些行birth_date，这些是由orderid.就像你读了一本电话簿，发现所有姓“史密斯”的人，都是按名字排序的。如果有多个名字相同的人，他们会根据各自的电话号码在电话簿中进行订购。
但是如果你搜索所有姓史密斯的人和各种以“s”开头的名字呢？
这些不是按电话号码排序的。只有在前几列并列的情况下，它们才按姓、名、电话号码排序。
如果要按电话号码对它们进行排序，可以创建一个索引，其中列的顺序不同，如姓氏、电话号码和名字。
现在它们是按电话号码顺序排列的，但其中还有其他名称与以“s”开头的名字的条件不匹配。它们甚至没有按名字排序，因为名字的第三列只有在前两列并列时才会排序。
这指出了索引的一个普遍问题：您只能对等式比较中涉及的列重新排序。如果要对结果进行排序，则只有按索引中的列进行排序并且索引的所有前面的列仅用于相等比较时，才能使用索引。
在范围比较中引用一列后，将忽略索引中的任何后续列以进行搜索和排序。
换句话说：索引可以有任意数量的列作为相等条件，索引的下一列可以用于范围条件或对结果进行排序。但对于这两种操作，使用的列都不超过一列。
你不能优化一切。
回复您的评论：如果您在列上有索引
birth_date:然后解释显示没有文件排序：
（the）
rows看起来很低，因为我正在用一个空表测试。）需要注意的是，它使用索引来匹配
country,city,gender，和orderid. 然后mysql将在birth_date困难的方法：一排一排。但在那之后，优化器知道它已经按索引顺序获取了行，因此它知道这将自然地按顺序进行
orderid，因此可以跳过文件排序。这可能是也可能不是一个净胜球。这取决于有多少行是匹配的，但必须由上的条件抛出
birth_date. 以及评估每一行的条件是多么昂贵。这与使用索引进行筛选所节省的成本相比又有什么不同呢birth_date.z9ju0rcb2#
mysql不能使用索引进行排序。你的状况
birthdate表示索引中的行不是按顺序排列的orderid.我不认为有办法解决这个问题。