我正在尝试在 mysql 中构建索引以支持键集分页查询。我的查询如下所示:
SELECT * FROM invoice
WHERE company_id = 'someguid'
AND id > 'lastguidfromlastpage'
ORDER BY id
LIMIT 10
这方面的常识表明,索引company_id
将包含表的主键(id
)。因此,我希望能够直接使用索引中的行,而不需要查询先对结果进行排序,但是我的解释计划显示了文件排序和索引合并:
mysql> explain SELECT *
-> FROM invoice
-> WHERE company_id = '37687714-2e9d-4daa-aee6-f7d56962f903'
-> AND id > '525ae038-0cc3-4f9a-85e6-6f36d43fae40'
-> ORDER BY id
-> LIMIT 10;
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+---------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+---------------------------------------------------------------------------+
| 1 | SIMPLE | invoice | NULL | index_merge | PRIMARY,invoice__company_id | invoice__company_id,PRIMARY | 76,38 | NULL | 48 | 100.00 | Using intersect(invoice__company_id,PRIMARY); Using where; Using filesort |
+----+-------------+---------+------------+-------------+-----------------------------+-----------------------------+---------+------+------+----------+---------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
如果我明确地将 id 添加到索引中,那么我会得到我期望的解释计划:
mysql> explain SELECT *
-> FROM invoice
-> WHERE company_id = '37687714-2e9d-4daa-aee6-f7d56962f903'
-> AND id > '525ae038-0cc3-4f9a-85e6-6f36d43fae40'
-> ORDER BY id
-> LIMIT 10;
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | invoice | NULL | range | PRIMARY,invoice__company_id_id | invoice__company_id_id,PRIMARY | 76 | NULL | 98 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------------------+--------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
显示创建表:
CREATE TABLE `invoice` (
`id` varchar(36) NOT NULL,
`company_id` varchar(36) NOT NULL DEFAULT '0',
`invoice_number` varchar(36) NOT NULL DEFAULT '0',
`identifier` varchar(255) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_by` varchar(36) DEFAULT NULL,
`data_source` varchar(36) NOT NULL,
`type` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `invoice__company_id_id` (`company_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
选择@@optimizer_switch;
use_index_extensions=on
MySQL版本:
- 版本:5.7.26-29-57-log
- innodb_版本:5.7.26-29
- version_comment:Percona XtraDB Cluster (GPL),版本 rel29,修订版 03540a3,WSREP 版本 31.37,wsrep_31.37
显示类似“char%”的变量;
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
有一些消息来源解释说,company_id 索引本身就足够了:
- https://stackoverflow.com/a/30152513/64023 https://stackoverflow.com/a/30152513/64023
- https://dba.stackexchange.com/a/136029/166838 https://dba.stackexchange.com/a/136029/166838
我一直无法找到有关具体预期内容的官方文档。这与 id 的数据类型有关吗?关于mysql+innodb行为的常识不正确吗?