mysql - 为什么我可以在 "select count(*) from table_name"上使用索引，但不能在 "select * from table_name"上工作？有什么区别吗？

Question

我有一个这样的表:

+--------------+--------------+------+-----+--------------------------------------+-------+
| Field        | Type         | Null | Key | Default                              | Extra |
+--------------+--------------+------+-----+--------------------------------------+-------+
| id           | varchar(36)  | NO   | PRI | NULL                                 |       |
| provider_id  | varchar(36)  | YES  | MUL | 00000000-0000-0000-0000-000000000000 |       |
| to_provider  | int(11)      | NO   |     | NULL                                 |       |
| to_customer  | int(11)      | NO   |     | NULL                                 |       |
| published_at | datetime     | NO   |     | NULL                                 |       |
| expired_at   | datetime     | NO   |     | NULL                                 |       |
| title        | varchar(512) | NO   |     | NULL                                 |       |
| content      | text         | YES  |     | NULL                                 |       |
| created_at   | datetime     | NO   |     | NULL                                 |       |
| created_by   | varchar(255) | YES  |     | NULL                                 |       |
| updated_at   | datetime     | YES  |     | NULL                                 |       |
| updated_by   | varchar(255) | YES  |     | NULL                                 |       |
| deleted_at   | datetime     | YES  |     | NULL                                 |       |
| deleted_by   | varchar(255) | YES  |     | NULL                                 |       |
+--------------+--------------+------+-----+--------------------------------------+-------+

我创建了一个这样的索引:

+---------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name          | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| announcements |          0 | PRIMARY           |            1 | id           | A         |       76184 |     NULL | NULL   |      | BTREE      |         |               |
| announcements |          1 | idx_announcements |            1 | provider_id  | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| announcements |          1 | idx_announcements |            2 | deleted_at   | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| announcements |          1 | idx_announcements |            3 | published_at | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| announcements |          1 | idx_announcements |            4 | to_provider  | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| announcements |          1 | idx_announcements |            5 | to_customer  | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| announcements |          1 | idx_announcements |            6 | expired_at   | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| announcements |          1 | idx_announcements |            7 | updated_at   | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

当我运行时

MariaDB [table_name]> EXPLAIN SELECT COUNT(*) FROM `announcements` `t` WHERE (((`t`.provider_id = "3c5e63df-cb9b-f5a8-4eaf-7ed0061b797d") OR (provider_id="00000000-0000-0000-0000-000000000000")) AND (`t`.deleted_at IS NULL))AND(((published_at <= "2015-07-17 14:54:36") AND ( to_provider IN (1))) AND (to_customer IN (0, 1)));
+------+-------------+-------+-------+-------------------+-------------------+---------+------+-------+--------------------------+
| id   | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows  | Extra                    |
+------+-------------+-------+-------+-------------------+-------------------+---------+------+-------+--------------------------+
|    1 | SIMPLE      | t     | range | idx_announcements | idx_announcements | 64      | NULL | 38093 | Using where; Using index |
+------+-------------+-------+-------+-------------------+-------------------+---------+------+-------+--------------------------+

看，我可以使用索引，但是当我运行时

MariaDB [table_name]> EXPLAIN SELECT * FROM `announcements` `t` WHERE (((`t`.provider_id = "3c5e63df-cb9b-f5a8-4eaf-7ed0061b797d") OR (provider_id="00000000-0000-0000-0000-000000000000")) AND (`t`.deleted_at IS NULL))AND(((published_at <= "2015-07-17 14:54:36") AND ( to_provider IN (1))) AND (to_customer IN (0, 1)));
+------+-------------+-------+------+-------------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys     | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+-------------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | idx_announcements | NULL | NULL    | NULL | 76184 | Using where |
+------+-------------+-------+------+-------------------+------+---------+------+-------+-------------+

看，键为空!我真的不明白。使用相同的where，但是“select *”不能使用索引。

谁能告诉我吗？

哦，我得到了这样的 SQL

SELECT * FROM `announcements` `t` 
WHERE 
    (((`t`.provider_id = :provider_id) OR (provider_id=:ycp0)) AND (`t`.deleted_at IS NULL)) 
    AND (published_at<="2015-07-17 16:58:57" AND (to_provider IN(1) AND to_customer IN (0,1)) 
    AND expired_at>="2015-07-17 16:58:57" 
    AND (updated_at > "2015-07-10 16:58:57" || published_at > "2015-07-10 16:58:57" )) 
ORDER BY updated_at DESC LIMIT 10

如何为此查询创建索引？我在“OR”问题上遇到了问题~~~

Answer 1

解决 MySQL 在 select 中表的任何实例上仅使用单个索引的问题的一种方法是将 select 拆分为 2 个并将它们联合在一起。

例如，对于您的选择，如果 updated_at 和 published_at 都是有用的索引，那么您可以允许 MySQL 使用它们，如下所示:-

SELECT * 
FROM announcements t 
WHERE t.provider_id IN ( :provider_id, :ycp0)
AND t.deleted_at IS NULL
AND published_at<="2015-07-17 16:58:57" 
AND to_provider IN(1) 
AND to_customer IN (0,1) 
AND expired_at>="2015-07-17 16:58:57" 
AND updated_at > "2015-07-10 16:58:57" 
UNION
SELECT * 
FROM announcements t 
WHERE t.provider_id IN ( :provider_id, :ycp0)
AND t.deleted_at IS NULL
AND published_at<="2015-07-17 16:58:57" 
AND to_provider IN(1) 
AND to_customer IN (0,1) 
AND expired_at>="2015-07-17 16:58:57" 
AND published_at > "2015-07-10 16:58:57" 
ORDER BY updated_at DESC LIMIT 10