未使用 MySQL 复合索引 [英] MySQL compound index not being used

问题描述

我有一张大表，我必须从中选择大量行.

I have a large table from which I must select large amounts of rows.

该表存储呼叫详细记录 (CDR).示例:

The table stores call detail records (CDR's). Example:

+-------------+--------------+------+-----+---------------------+----------------+
| Field       | Type         | Null | Key | Default             | Extra          |
+-------------+--------------+------+-----+---------------------+----------------+
| id          | int(45)      | NO   | PRI | NULL                | auto_increment |
| calldate    | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| accountcode | varchar(100) | NO   |     |                     |                |
| other...    | varchar(45)  | NO   |     |                     |                |

由于我的查询在特定日期查找客户来电，因此我将 calldate 和 accountcode 一起编入聚集索引中，如下所示:

Since my queries look for a customers calls in certain dates, I indexed calldate and accountcode together in a clustered index like so:

CREATE TABLE `cdr` (
  `id` int(45) NOT NULL AUTO_INCREMENT,
  `calldate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `accountcode` varchar(100) NOT NULL DEFAULT '',
   other fields...
PRIMARY KEY (`id`),
KEY `date_acc` (`calldate`,`accountcode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1

但是，在执行以下查询时，EXPLAIN 结果显示仅使用了键的日期时间部分:

However, when executing the following query, the EXPLAIN result shows that only the datetime portion of the key is being used:

查询:

SELECT * 
FROM cdr
WHERE calldate > '2010-12-01'
  AND accountcode = 'xxxxxx';

解释结果:

+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | cdr   | range | date_acc      | date_acc | 8       | NULL | 3312740 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+----------+-------------+

似乎只使用了前 8 个字节(密钥的日期部分).然而，WHERE 子句使用 AND 显式引用键的两个部分，因此理论上应该使用完整键.

It seems only the first 8 bytes (the date portion of the key) is being used. However the WHERE clause explicitly references both parts of the key with an AND, so in theory the full key should be used.

我应该为 calldate 和 accountcode 创建单独的索引并让查询优化器合并它们吗?为什么不使用完整索引?

Should I create separate indexes for calldate and accountcode and let the query optimizer merge them? Why is the full index not being used?

感谢您的帮助！

推荐答案

简短回答:如果您的键是 (accountcode, calldate) 而不是 (calldate, accountcode)，您将能够在这里更有效地使用索引.

Short answer: You'd be able to use the index more effectively here if your key was (accountcode, calldate) instead of (calldate, accountcode).

理解问题的最好方法是将多列键视为不同列的串联.例如，如果第 1 列的值为A、B、C、D"和第 2 列的W、X、Y、Z"，则您将在AW、BX、CY、DZ"等上构建索引并将所有将它们合并到 B 树中.

The best way to understand the problem is by thinking of multi-column keys as being a concatenation of the different columns. As an example if column 1 had values 'A,B,C,D' and column 2 'W,X,Y,Z' you'd construct an index on 'A-W, B-X, C-Y, D-Z' etc. and put all of those into a B-tree.

要进行范围查询，您需要找到范围低端的第一个后继，并迭代直到超出范围上限.这意味着您只能有效地使用索引对键的后缀进行范围查询.

To do a range query, you find the first successor of low end of the range, and iterate till you exceed the upper range. This means that you can only effectively use the index to do a range query on a suffix of the key.

这篇关于未使用 MySQL 复合索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！