Вероятно, плохой индекс, полное сканирование таблицы

Можете ли вы помочь мне с индексацией моих таблиц?

Проблема в том, что я проиндексировал свои таблицы, но у меня все еще есть «полное сканирование таблицы» в моем объяснении.

это мой (рабочий) запрос, но на больших таблицах он может быть медленным, и я не знаю, как это изменить

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

пс. index3 - я не хочу много раз значения (1,5), когда посетитель = 1 обновить страницу с идентификатором = 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);

mysql indexing full-table-scan
person Bolek Lolek    schedule 29.06.2016    source источник
comment
В чем твоя ошибка? Что ты не можешь сделать?   -  person Fredster    schedule 29.06.2016
comment
Кажется, ваша схема верна для таблицы stats_clicked, если вы хотите, чтобы (visitor_id, page_clicked_id) была уникальной и не принимала visitor_id = 1 и page_clicked_id =5 несколько раз.   -  person Fredster    schedule 29.06.2016
comment
Проблема в том, что я проиндексировал свои таблицы, но у меня все еще есть полное сканирование таблицы в моем объяснении.   -  person Bolek Lolek    schedule 29.06.2016
comment
Не уверен ... но, возможно, вам нужно FOREIGN KEY на visitor_id в таблице stats_clicked, чтобы сделать этот столбец уникальным и сделать join быстрее, поскольку это единственное условие, которое вы используете в join.   -  person Fredster    schedule 29.06.2016
comment
Возможно, это применимо. В предложении ON или WHERE для индексированных столбцов нет полезных ограничений. в dev.mysql.com/doc /refman/5.7/en/how-to-avoid-table-scan.html   -  person Fredster    schedule 29.06.2016

Ответы (2)


arrow_upward
0
arrow_downward

Если я выполню то, что вы сделали выше, я получу

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 |
+----+-------------+-------+------+---------------+--------+---------+--------------------+------+-------------+

Однако, если я усекаю, то выполняю следующую загрузку большого количества данных (в итоге получается более 100 тыс. строк):

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 использует индексы:

Индексы менее важны для запросов к небольшим таблицам или к большим таблицам, где запросы отчетов обрабатывают большую часть или все строки. Когда запросу требуется доступ к большинству строк, последовательное чтение выполняется быстрее, чем работа по индексу. Последовательное чтение сводит к минимуму поиск на диске, даже если для запроса нужны не все строки.

Причина указана выше. В примере с вашим вопросом у вас было слишком мало строк, чтобы использовать индекс. Таким образом, движок БД выбрал свой якобы (и, вероятно) более быстрый способ не использовать индекс на вашей маленькой таблице.

person Drew    schedule 29.06.2016
comment
Конечно, это не сканирование таблицы, а сканирование индекса. - person Rick James; 02.07.2016
comment
Я показываю, что нужно верить explain при адекватных данных, а не 15 вставкам. - person Drew; 02.07.2016

arrow_upward
0
arrow_downward

stats_clicked не нужен id. На самом деле его наличие замедляет все вероятные запросы к этой таблице. Подробнее.

person Rick James    schedule 02.07.2016