可能是糟糕的索引，全表扫描 [英] Probably bad index, full table scan

问题描述

你能帮我索引我的桌子吗？

Can you help me with index my tables?

问题是我索引了我的表格，但我的解释中仍然有全表扫描

Problem is that i indexed my tables, but i still have "full table scan" in my explain

这是我的（工作）查询，但在大表上它可能很慢，我不知道如何更改

this is my (working) query, but on big tables it could be slow, and i dont know how to change this

EXPLAIN select * from  stats_clicked s
join visitor v on s.visitor_id=v.id

ps 。 index3 - 当访问者= 1刷新页面id = 5时，我不会很多次值（1,5）

ps. index3 - I dont wan't many times values (1,5) when visitor=1 refresh page with id=5

CREATE TABLE `visitor` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `visited_time` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `stats_clicked` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `visitor_id` int(11) NOT NULL,
   `page_clicked_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `index3` (`visitor_id`,`page_clicked_id`),
   KEY `index1` (`visitor_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


insert into visitor (`visited_time`) values
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944);

insert into `stats_clicked` ( `visitor_id`,`page_clicked_id`) values
(1,47),(2,24),(3,83),(3,8),(3,85),(3,88),(4,57),
(5,2),(6,1),(7,28),(8,83),(9,11),(9,16),(9,1),(10,17),
(11,70),(12,73),(13,97),(14,57),(15,30),(15,2),(15,22);

推荐答案

如果我执行你上面所做的事情，我会

If I perform what you did above, I get

EXPLAIN select * from  stats_clicked s 
join visitor v on s.visitor_id=v.id 
+----+-------------+-------+------+---------------+--------+---------+--------------------+------+-------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref                | rows | Extra       |
+----+-------------+-------+------+---------------+--------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | v     | ALL  | PRIMARY       | NULL   | NULL    | NULL               |   15 | NULL        |
|  1 | SIMPLE      | s     | ref  | index3,index1 | index3 | 4       | so_gibberish2.v.id |    1 | Using index |
+----+-------------+-------+------+---------------+--------+---------+--------------------+------+-------------+

但是如果我截断那么执行以下大量数据加载（最后超过100K行）：

However if I truncate then do the following load of a lot of data (ending up with over 100K rows):

truncate table visitor;

insert into visitor (`visited_time`) values
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944),
(1467122944),(1467122944),(1467122944);

insert into visitor (`visited_time`) values
(1467122945),(1467122945),(1467122945),
(1467122945),(1467122945),(1467122945),
(1467122945),(1467122945),(1467122945),
(1467122945),(1467122945),(1467122945),
(1467122945),(1467122945),(1467122945),


insert into visitor (`visited_time`) values
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946),
(1467122946),(1467122946),(1467122946);

insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;
insert visitor(`visited_time`) select `visited_time` from visitor;

select count(*) from visitor;
-- 104448 rows

这导致不表扫描：

EXPLAIN select * from  stats_clicked s 
join visitor v on s.visitor_id=v.id; 

+----+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+
|  1 | SIMPLE      | s     | index  | index3,index1 | index3  | 9       | NULL                       |   22 | Using index |
|  1 | SIMPLE      | v     | eq_ref | PRIMARY       | PRIMARY | 4       | so_gibberish2.s.visitor_id |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+------+-------------+

原因列于手册页面如何MySQL使用索引：

索引少于
对小型表或报表$的大表的查询很重要b $ b查询处理大部分或全部行。当查询需要访问大多数行的
时，顺序读取比通过
索引更快。顺序读取最小化磁盘搜索，即使查询不需要所有
行。

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

上面列出的原因。在您的问题示例中，您使用的行数太少，无法使索引值得使用。因此，db引擎选择了所谓的（并且可能）更快的方法，即不在小桌子上使用索引。

The reason being listed above. In your question's example, you had too few rows for making the index use worth it. So the db engine chose its allegedly (and probably) faster way of not using the index on your small table.

这篇关于可能是糟糕的索引，全表扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！