MySQL 索引未使用

Question

有一些带有两列索引的表(user_id，date)和SQL查询

select  user_id, stat.in, stat.out, stat.time, date
from stat
where user_id in (select id from users force index (street_id) where street_id=30);

或

select  user_id, stat.in, stat.out, stat.time, date 
from stat where user_id in (select id from users force index (street_id) where street_id=30)
and date between STR_TO_DATE('2010-01-01 00:00:00', '%Y-%m-%d %H:%i:%s') and TR_TO_DATE('2014-05-22 23:59:59', '%Y-%m-%d %H:%i:%s')

在两种情况下索引必须起作用，但我在 in 语句中遇到了问题。如果可能的话，如何让它发挥作用？解释一下:

+----+--------------------+-------+------+---------------+-----------+---------+-------+----------+--------------------------+
| id | select_type        | table | type | possible_keys | key       | key_len | ref   | rows     | Extra                    |
+----+--------------------+-------+------+---------------+-----------+---------+-------+----------+--------------------------+
|  1 | PRIMARY            | stat  | ALL  | NULL          | NULL      | NULL    | NULL  | 32028701 | Using where              |
|  2 | DEPENDENT SUBQUERY | users | ref  | street_id     | street_id | 8       | const |      650 | Using where; Using index |
+----+--------------------+-------+------+---------------+-----------+---------+-------+----------+--------------------------+

如果使用一个 user_id 索引进行搜索有效

explain select  user_id, stat.in, stat.out, stat.time, date
from stat
where user_id=3991;

解释:

+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | stat  | ref  | user_id_2     | user_id_2 | 8       | const | 2973 |       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------+

Answer 1

查询中的第一件事是 IN 子句造成严重破坏，如果我没记错的话，索引没有正确完成。

所以应该是这样的，假设表格如下

create table users (id int, name varchar(100),street_id int);
insert into users values 
(1,'a',20),(2,'b',30),(3,'c',10),(4,'d',20),(5,'e',10),(6,'f',40),(7,'g',20),
(8,'h',10),(9,'i',10),(10,'j',40);


create table stat (user_id int ,`in` int, `out` int, time int , date date);
insert into stat values
(1,1,1,20,'2014-01-01'),
(1,1,1,20,'2014-01-02'),
(3,1,1,20,'2014-01-01'),
(2,1,1,20,'2014-01-01'),
(4,1,1,20,'2014-01-02'),
(6,1,1,20,'2014-01-02'),
(7,1,1,20,'2014-01-02'),
(8,1,1,20,'2014-01-02'),
(1,1,1,20,'2014-01-02'),
(2,1,1,20,'2014-01-02'),
(3,1,1,20,'2014-01-03'),
(4,1,1,20,'2014-01-04'),
(5,1,1,20,'2014-01-04'),
(6,1,1,20,'2014-01-04'),
(7,1,1,20,'2014-01-04'),
(2,1,1,20,'2014-01-04'),
(3,1,1,20,'2014-01-04'),
(4,1,1,20,'2014-01-05'),
(5,1,1,20,'2014-01-05'),
(6,1,1,20,'2014-01-05'),
(7,1,1,20,'2014-01-05'),
(8,1,1,20,'2014-01-05'),
(9,1,1,20,'2014-01-05'),
(10,1,1,20,'2014-01-05'),
(1,1,1,20,'2014-01-06'),
(4,1,1,20,'2014-01-06');

现在在表上添加一些索引

alter table users add index id_idx (id);
alter table users add index street_idx(street_id);

alter table stat add index user_id_idx(user_id);

现在，如果我们执行与您尝试使用解释产量执行的相同查询

EXPLAIN
select  user_id, stat.`in`, stat.`out`, stat.time, date
from stat
where user_id in (select id from users force index (street_id) where street_id=30);


+----+--------------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type        | table | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+--------------------+-------+------+---------------+------------+---------+-------+------+-------------+
|  1 | PRIMARY            | stat  | ALL  | NULL          | NULL       | NULL    | NULL  |   26 | Using where |
|  2 | DEPENDENT SUBQUERY | users | ref  | street_idx    | street_idx | 5       | const |    1 | Using where |
+----+--------------------+-------+------+---------------+------------+---------+-------+------+-------------+

看起来仍然像是在尝试扫描整个表格。

现在让我们修改查询并使用 JOIN 并查看解释内容，请注意，我在两个表上都有用于连接键的索引，并且它们具有相同的类型和大小。

EXPLAIN
select
s.user_id,
s.`in`,
s.`out`,
s.time,
s.date
from stat s
join users u on u.id = s.user_id 
where u.street_id=30 ;

+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys     | key         | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+
|  1 | SIMPLE      | u     | ref  | id_idx,street_idx | street_idx  | 5       | const     |    1 | Using where |
|  1 | SIMPLE      | s     | ref  | user_id_idx       | user_id_idx | 5       | test.u.id |    3 | Using where |
+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+

更好了，嗯？现在让我们尝试范围搜索

EXPLAIN
select
s.user_id,
s.`in`,
s.`out`,
s.time,
s.date
from stat s
join users u on u.id = s.user_id 
where 
u.street_id=30 
and s.date between '2014-01-01' AND '2014-01-06'
;


+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys     | key         | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+
|  1 | SIMPLE      | u     | ref  | id_idx,street_idx | street_idx  | 5       | const     |    1 | Using where |
|  1 | SIMPLE      | s     | ref  | user_id_idx       | user_id_idx | 5       | test.u.id |    3 | Using where |
+----+-------------+-------+------+-------------------+-------------+---------+-----------+------+-------------+

还是更好吧？

因此，基本议程是尝试避免 IN 查询。在索引列上使用 JOIN 并为搜索列正确索引它们。