#mysql #indexing #database-schema
#mysql #индексирование #database-schema
Вопрос:
У меня есть простая таблица mysql, которую я хочу использовать для ежедневного отслеживания статистики. После ввода в него некоторой информации и тестирования запроса samply, который я бы использовал для него, он не использует мой индекс! Я не могу понять, почему.
Я попытался ввести больше тестовых данных — 200 строк. Я также попытался изменить select * на select created,value1. Нет кубиков.
mysql> show create table stat_generalG
*************************** 1. row ***************************
Table: stat_general
Create Table: CREATE TABLE `stat_general` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`nameid` smallint(6) NOT NULL,
`value1` int(11) NOT NULL,
`value2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `created` (`created`,`nameid`,`value2`)
) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from stat_general;
---- --------------------- -------- -------- --------
| id | created | nameid | value1 | value2 |
---- --------------------- -------- -------- --------
| 1 | 2011-06-09 09:43:41 | 1 | 511803 | NULL |
| 2 | 2011-06-09 09:43:41 | 2 | 44 | NULL |
| 3 | 2011-06-09 09:43:41 | 3 | 9128 | NULL |
| 4 | 2011-06-09 09:43:41 | 4 | 219 | NULL |
| 5 | 2011-06-09 09:43:41 | 5 | 0 | NULL |
| 6 | 2011-06-09 09:43:41 | 6 | 0 | NULL |
| 7 | 2011-06-09 09:43:41 | 7 | 0 | NULL |
| 8 | 2011-06-09 09:43:41 | 8 | 0 | NULL |
| 9 | 2011-06-09 09:43:41 | 9 | 0 | NULL |
| 10 | 2011-06-09 09:43:41 | 10 | 140 | NULL |
| 11 | 2011-06-09 09:43:41 | 11 | 0 | NULL |
| 12 | 2011-06-09 09:43:41 | 12 | 146 | NULL |
| 13 | 2011-06-09 09:43:41 | 13 | 0 | NULL |
| 14 | 2011-06-09 09:43:41 | 14 | 1 | NULL |
| 15 | 2011-06-09 09:43:41 | 15 | 8981 | NULL |
| 16 | 2011-06-09 09:43:41 | 16 | 1 | -127 |
| 17 | 2011-06-09 09:43:41 | 16 | 2 | -2 |
| 18 | 2011-06-09 09:43:41 | 16 | 939 | -1 |
| 19 | 2011-06-09 09:43:41 | 16 | 146 | 1 |
| 20 | 2011-06-09 09:43:41 | 16 | 8011 | 3 |
| 21 | 2011-06-09 09:43:41 | 16 | 28 | 127 |
| 22 | 2011-06-09 09:43:41 | 16 | 1 | 128 |
| 23 | 2011-06-09 09:43:41 | 17 | 146 | 1 |
| 24 | 2011-06-09 09:43:41 | 18 | 146 | 1 |
| 25 | 2011-06-09 09:44:08 | 1 | 511803 | NULL |
| 26 | 2011-06-09 09:44:08 | 2 | 44 | NULL |
| 27 | 2011-06-09 09:44:08 | 3 | 9128 | NULL |
| 28 | 2011-06-09 09:44:08 | 4 | 219 | NULL |
| 29 | 2011-06-09 09:44:08 | 5 | 0 | NULL |
| 30 | 2011-06-09 09:44:08 | 6 | 0 | NULL |
| 31 | 2011-06-09 09:44:08 | 7 | 0 | NULL |
| 32 | 2011-06-09 09:44:08 | 8 | 0 | NULL |
| 33 | 2011-06-09 09:44:08 | 9 | 0 | NULL |
| 34 | 2011-06-09 09:44:08 | 10 | 140 | NULL |
| 35 | 2011-06-09 09:44:08 | 11 | 0 | NULL |
| 36 | 2011-06-09 09:44:08 | 12 | 146 | NULL |
| 37 | 2011-06-09 09:44:08 | 13 | 0 | NULL |
| 38 | 2011-06-09 09:44:08 | 14 | 1 | NULL |
| 39 | 2011-06-09 09:44:08 | 15 | 8981 | NULL |
| 40 | 2011-06-09 09:44:08 | 16 | 1 | -127 |
| 41 | 2011-06-09 09:44:08 | 16 | 2 | -2 |
| 42 | 2011-06-09 09:44:08 | 16 | 939 | -1 |
| 43 | 2011-06-09 09:44:08 | 16 | 146 | 1 |
| 44 | 2011-06-09 09:44:08 | 16 | 8011 | 3 |
| 45 | 2011-06-09 09:44:08 | 16 | 28 | 127 |
| 46 | 2011-06-09 09:44:08 | 16 | 1 | 128 |
| 47 | 2011-06-09 09:44:08 | 17 | 146 | 1 |
| 48 | 2011-06-09 09:44:08 | 18 | 146 | 1 |
---- --------------------- -------- -------- --------
48 rows in set (0.00 sec)
mysql> select * from stat_general where created>'2011-06-09 9:44' AND nameid=1;
---- --------------------- -------- -------- --------
| id | created | nameid | value1 | value2 |
---- --------------------- -------- -------- --------
| 25 | 2011-06-09 09:44:08 | 1 | 511803 | NULL |
---- --------------------- -------- -------- --------
1 row in set (0.00 sec)
mysql> explain select * from stat_general where created>'2011-06-09 9:44' AND nameid=1;
---- ------------- -------------- ------ --------------- ------ --------- ------ ------ -------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- -------------- ------ --------------- ------ --------- ------ ------ -------------
| 1 | SIMPLE | stat_general | ALL | created | NULL | NULL | NULL | 48 | Using where |
---- ------------- -------------- ------ --------------- ------ --------- ------ ------ -------------
1 row in set (0.00 sec)
Ответ №1:
Две причины:
Прежде всего, ваша таблица слишком мала. С этими несколькими строками быстрее открывать всю таблицу и фильтровать строки одну за другой, независимо от запроса.
Далее, даже если у вас было больше строк с очень похожими данными, ваш текущий индекс недостаточно выборочен. В таких ситуациях не стоит случайно открывать страницы диска; будет быстрее открывать их последовательно и отфильтровывать недопустимые строки.
Комментарии:
1. Итак, я предполагаю, что это просто функция того, что мои тестовые данные плохие. В реальном мире у меня были бы строки для nameid = 1-18 один раз в день, возможно, для данных на год. Я мог бы извлечь последние 3 недели для определенного показателя.
2. И в этом случае он будет использовать индекс, если только он не извлекает огромную часть строк.
Ответ №2:
Просто для усмешек… Будут ли ваши запросы в основном основаны на заданном «NameID» или «Created». Если NameID (т. Е. Что происходит для конкретного пользователя), я бы изменил индекс и критерии на
KEY `byName` ( `nameid`,`created`,`value2`)
тогда ваш запрос к…
where nameid = 1 AND created > '2011-06-09 9:44'
Я не уверен, но если критерии находятся в естественном порядке соответствия индексу, он может использовать его напрямую, а не угадывать индекс … (особенно с большим набором данных)… В противном случае @Denis прав, что он просто запрашивает страницы данных, поскольку они такие маленькие, и просматривает их независимо, вместо того, чтобы пытаться придумать, какой индекс он должен использовать.
Комментарии:
1. Добавление большего количества данных исправило это. Но это хорошая идея — изменить индекс. У меня всегда будет nameid = X в моем запросе, но не всегда созданный (что, если я хочу извлекать данные за все время?). Я переставлю индекс. Спасибо!