mysql - 如何强制mariaDB使用索引？

Question

我正在尝试执行 SQL 查询。不幸的是，它不使用索引，而是进行表扫描。

我已经创建了以下索引:

• PRIMARY($phone, $$fc_date)
• idx $$fc_status_detail
• idx $$fc_date
• idx $$fc_status
• idx $$电话

此外，我复制了该表，但这也没有提供任何有用的结果。

这是表结构:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| $id                | varchar(100) | NO   |     | NULL    |       |
| $created_date      | varchar(100) | YES  |     | NULL    |       |
| $phone             | varchar(100) | NO   | PRI | NULL    |       |
| $source            | varchar(100) | YES  |     | NULL    |       |
| Orga               | varchar(100) | YES  |     | NULL    |       |
| Anrede             | varchar(100) | YES  |     | NULL    |       |
| Vorname            | varchar(100) | YES  |     | NULL    |       |
| Zuname             | varchar(100) | YES  |     | NULL    |       |
| Strasse            | varchar(100) | YES  |     | NULL    |       |
| PLZ                | varchar(100) | YES  |     | NULL    |       |
| Ort                | varchar(100) | YES  |     | NULL    |       |
| Geburtsdatum       | varchar(100) | YES  |     | NULL    |       |
| Email              | varchar(100) | YES  |     | NULL    |       |
| Zeitschrift        | varchar(100) | YES  |     | NULL    |       |
| Herkunft           | varchar(100) | YES  |     | NULL    |       |
| Zeitschrift_Titel  | varchar(100) | YES  |     | NULL    |       |
| telefon            | varchar(100) | YES  |     | NULL    |       |
| Stornogrund        | varchar(100) | YES  |     | NULL    |       |
| Storno             | varchar(100) | YES  |     | NULL    |       |
| Telefonnummer      | varchar(100) | YES  |     | NULL    |       |
| Postleitzahl       | varchar(100) | YES  |     | NULL    |       |
| Geburtsjahr        | varchar(100) | YES  |     | NULL    |       |
| $$fc_task          | varchar(100) | YES  |     | NULL    |       |
| $$fc_user          | varchar(100) | YES  |     | NULL    |       |
| $$fc_date          | varchar(100) | NO   | PRI | NULL    |       |
| $$fc_status        | varchar(100) | YES  | MUL | NULL    |       |
| $$fc_status_detail | varchar(100) | YES  | MUL | NULL    |       |
| $$qc_task          | varchar(100) | YES  |     | NULL    |       |
| $$qc_user          | varchar(100) | YES  |     | NULL    |       |
| $$qc_date          | varchar(100) | YES  |     | NULL    |       |
| $$qc_status        | varchar(100) | YES  |     | NULL    |       |
| $$qc_status_detail | varchar(100) | YES  |     | NULL    |       |
| $call_duration     | smallint(6)  | YES  |     | NULL    |       |
| $call_attempts     | smallint(6)  | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

这是查询:

EXPLAIN SELECT
    count(*) as total,
    CONCAT(case when c1.$$fc_date < 240 then "short" else "long" end, "/", c1.$$fc_status, "/", c1.$$fc_status_detail) as ergebnis,
    sum(case when c2.$$fc_status = 'success' then 1 else 0 end)/ count(*) as c2_succes_rate
FROM
    contacts c1 FORCE INDEX (PRIMARY),
    contacts_copy c2
WHERE
    c1.$phone = c2.$phone
    and c1.$$fc_date < c2.$$fc_date
group by
    ergebnis

这是结果:

+------+-------------+-------+------+--------------------------------------------------------------+---------+---------+---------------+---------+---------------------------------+
| id   | select_type | table | type | possible_keys                                                | key     | key_len | ref           | rows    | Extra                           |
+------+-------------+-------+------+--------------------------------------------------------------+---------+---------+---------------+---------+---------------------------------+
|    1 | SIMPLE      | c1    | ALL  | PRIMARY                                                      | NULL    | NULL    | NULL          | 2017450 | Using temporary; Using filesort |
|    1 | SIMPLE      | c2    | ref  | PRIMARY,contacts_copy_$phone_IDX,contacts_copy_$$fc_date_IDX | PRIMARY | 402     | nmv.c1.$phone |       1 | Using where                     |
+------+-------------+-------+------+--------------------------------------------------------------+---------+---------+---------------+---------+---------------------------------+

正如您在第 1 行中看到的，虽然它识别 PRIMARY 键，但它不使用它。问题是它扫描了 2 Mio。行且查询持续至少 5 分钟。

有人可以解释一下，问题可能是什么吗？

Answer 1

您正在使用 InnoDB，对吗？ InnoDB 辅助键(例如 INDEX(phone))隐式包含 PRIMARY KEY 的列。因此，索引实际上是一个 (phone, fc_date) 的 BTree。

接下来，我们注意到 c2 还需要另一列:fc_status。因此，优化器研究了两种运行查询的方法。首先，请注意，这两种方法都在索引中具有最佳列，并且它们处于最佳顺序。

A 计划:使用索引，然后在索引和数据之间来回切换。

B 计划:进行表扫描，不需要来回。

优化器正确选择了 B。

你可以有一个更好的索引，优化器很可能会选择它。而且会更快:

INDEX(phone, fc_date, fc_status)  -- in this order

这是“覆盖”，因为所有需要的列都存在。因此，不会有任何来回。

我需要批评VARCHAR(100)。这对于日期来说可能非常糟糕，因为根据格式，它们可能无法正确排序。

名称可能永远不会有 100 个字符；电子邮件可能会更长。等等4个电话号码？怎么了？