Индексы, indexes

[Gas]

Индексы, indexes

Вспомнились вопросы по индексам, которые крутились в голове но ответов ранее не искал:

1. имеется составной (`field_1`, `field_2`), будет ли использоваться индекс на `field_2` при условии WHERE field_1 (> или <) const AND `field_2` = const; или для каждого `field_1` удовлетворяющему условию будет идти полный перебор связанных с ним `field_2` ?

2. имеется поле field varchar(255) и индекс на него `field`(10). При условиях WHERE field='google' или WHERE field LIKE 'g%', будет ли mysql обращаться к "файлу-данных" для записей где значение меньше 10 символов? Или оптимизатор не такой дурак и проверят, ага всё значение (например, "google") влезло в индекс и на этом успокаивается ?
Вопрос вызван тем, чтоб в explain при частичном индексе пропадает using index, я понимаю что он пишет план для общего случая, но мало ли.

3. я его уже задавал, но повторюсь . Пример запроса тут (1-ый пост), допустим есть индекс на `category_id` и индекс на `description`. Действительно ли в случае fulltext поиска, для проверки условия по полю category_id - mysql будет сразу лезть в "файл-данных", а не пытаться найти его в key_buffer (память), вроде это логично и реально ?

p.s. по 3-му пункту наверное действительно никуда не лезет, для этого key_buffer должен содержать не только дерево index->data_pointer но и реверсное data_pointer->index

 

[zerkms]

1. конечно нет

будет ли использоваться индекс на `field_2`

уточняя, "не будет использоваться вторая часть индекса"

2. using index используется только в случае числовых индексов

 

[Gas]

1. понял, так и думал что нет в жизни щастья
2. разве? если поле (var)char полностью проиндексировано - using index появляется в explain select `f` from `t` where `f`='const';

 

[zerkms]

Gas
хм... у меня откуда-то устойчивое мнение, что (var)char никогда не может дать using index, причём как будто бы видел в доке. поиски навскидку ничего не дали... хмхм...

 

[Gas]

а почему нет?
если логически посмотреть: в индексе хранится вся строка, а не хеш или ещё какой-то усечённый вариант, следовательно она извлекается для where и возвращается клиенту - вот и using index.

На примере: есть поле `f` со строкой "марсель" и индексом `f(3)` - то-есть в индексе "мар".
1. `f` = "марс" - первые 3 символа индекса и строки совпадают, но строка длиннее индекса, mysql должен обратиться к данным и получить всё значение поля для точного сравнения.
2. `f` = "ма", вот в этом случае mysql хоть не обращается к данным, он понимает что строка короче индекса или нет и шуршит винтом (или памятью) для получения данных ? Теоритически не должен, но хочется знать

 

[zerkms]

в индексе хранится вся строка, а не хеш или ещё какой-то усечённый вариант, следовательно она извлекается для where и возвращается клиенту - вот и using index.

для этого в индексе также должна быть и информация - хранится ли там всё значение поля, или только усечённый префикс...

mysql> SELECT `name` FROM `test` WHERE `name` = 'q';
+------+
| name |
+------+
| q    |
+------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT `name` FROM `test` WHERE `name` = 'q';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | name          | name | 10      | const |1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

 

[Gas]

Дошли руки потестировать.

[sql]
CREATE TABLE `test` (
`txt` varchar(10) NOT NULL,
KEY `full` (`txt`),
KEY `part` (`txt`(5))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test VALUES('google'),('googlegoog');
[/sql]

Тест номер 1 (поиск по "полному" индексу)

FLUSH STATUS;
EXPLAIN SELECT `txt` FROM `test` FORCE INDEX (`full`) WHERE txt='google' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | full          | full | 32      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

SELECT `txt` FROM `test` FORCE INDEX (`full`) WHERE txt='google' LIMIT 1;

SHOW STATUS LIKE 'handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

видно что используется только индекс.

Тест номер 2 (поиск по partial-индексу, который больше сравниваемой строки)

FLUSH STATUS;
EXPLAIN SELECT `txt` FROM `test` FORCE INDEX (`part`) WHERE txt='google' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | part          | part | 17      | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

SELECT `txt` FROM `test` FORCE INDEX (`part`) WHERE txt='google' LIMIT 1;

SHOW STATUS LIKE 'handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

похоже что ответ на мой второй вопрос такой - несмотря на отсутствие using index в explain, данные берутся только из индекса, то-есть mysql достаточно умный чтоб проверять размер сравниваемой строки с размером ключа. Ну а explain видимо "не знает" о такой оптимизации и показывает пессимистичный прогноз.
И всё таки на 100% утверждать что так и происходит я не могу так-как тест 3.

Тест номер 3 (поиск по partial-индексу, который меньше сравниваемой строки)

FLUSH STATUS;
EXPLAIN SELECT `txt` FROM `test` FORCE INDEX (`part`) WHERE txt='googlegoog' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | part          | part | 17      | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

SELECT `txt` FROM `test` FORCE INDEX (`part`) WHERE txt='googlegoog' LIMIT 1;

SHOW STATUS LIKE 'handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

тут я удивлён, с одной стороны видно что 2-ой и 3-ий запросы mysql обрабатывает по разному, что я и предпологал, но думал должен увеличиться Handler_read_rnd, а не Handler_read_next.

В общем на основе этих результатов я ничего другим утверждать не могу, но для себя составил мнение.

p.s. zerkms, спасибо за участие.