Форум пользователей MySQL

FiMko · 11.03.2012 01:57:01

Здравствуйте! Помогите, пожалуйста, разобраться со следующей проблемой.

Имеются две таблицы:

Обычная таблица (ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_bin):

mysql> describe words;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| id    | int(11)      | NO   | PRI | NULL    | auto_increment |

| word  | varchar(70) | NO   | MUL | NULL    |                |

+-------+--------------+------+-----+---------+----------------+

mysql> select * from words;

+----+-------+

| id | word  |

+----+-------+

|  1 | dog   |

|  2 | fight |

|  3 | qwe   |

|  4 | abc   |

+----+-------+

Временная таблица (ENGINE = MEMORY CHARACTER SET utf8 COLLATE utf8_bin):

mysql> describe tmp_words;

+-------+-------------+------+-----+---------+----------------+

| Field | Type        | Null | Key | Default | Extra          |

+-------+-------------+------+-----+---------+----------------+

| id    | int(11)     | NO   | PRI | NULL    | auto_increment |

| word  | varchar(70) | NO   | MUL | NULL    |                |

+-------+-------------+------+-----+---------+----------------+

mysql> select * from tmp_words;

+----+-------+

| id | word  |

+----+-------+

|  1 | dog   |

|  2 | fight |

+----+-------+

Первый вариант запроса для извлечения данных из таблицы words:

EXPLAIN SELECT word

FROM   words

WHERE  word in ('dog', 'fight');

+----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+

| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |

+----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+

|  1 | SIMPLE      | words  | index | word          | word | 302     | NULL |    4 | Using where; Using index |

+----+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+

Второй вариант запроса для извлечения данных из таблицы words:

explain SELECT word

FROM words

WHERE word in (SELECT word FROM tmp_words);

+----+--------------------+--------------+----------------+---------------+------+---------+------+------+--------------------------+

| id | select_type        | table        | type           | possible_keys | key  | key_len | ref  | rows | Extra                    |

+----+--------------------+--------------+----------------+---------------+------+---------+------+------+--------------------------+

|  1 | PRIMARY            | words        | index          | NULL          | word | 302     | NULL |    4 | Using where; Using index |

|  2 | DEPENDENT SUBQUERY | tmp_words    | index_subquery | word          | word | 212     | func |    2 | Using where              |

+----+--------------------+--------------+----------------+---------------+------+---------+------+------+--------------------------+

Собственно, вопрос: почему во втором варианте запроса possible_keys равно NULL, тем не менее ключ word находится, но не похоже, что применяется, т.к. количество строк равно 4 (перебираются все слова). Можете подсказать почему так работает и как исправить ситуацию? Я пробовал USE INDEX(word) - та же проблема... Заранее спасибо!

Отредактированно FiMko (11.03.2012 02:27:33)

vasya · 11.03.2012 09:50:53

Подзапрос определяется как зависимый (DEPENDENT SUBQUERY), т.е. выполняется для каждой строки основного запроса. Соответственно индекс использован быть не может (possible_keys = null). Но так как вы выбираете только поле word, по которому есть индекс, то выборка идет по нему (type = index) Отсюда и появляются (key = word и rows = 4), так как идет full scan всего индекса.

Эту багу пока исправили только в MariaDB 5.3
В остальных случаях подзапросы в in нужно переписывать через join.

FiMko · 11.03.2012 12:51:02

vasya написал:
В остальных случаях подзапросы в in нужно переписывать через join.

vasya, большое Вам спасибо! Join действительно помог.

FiMko · 11.03.2012 18:24:54

Появился еще вопрос по индексам. Есть простая таблица:

-- структура таблицы

mysql> describe table1;

+-------+------------------+------+-----+---------+----------------+

| Field | Type             | Null | Key | Default | Extra          |

+-------+------------------+------+-----+---------+----------------+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| col1  | int(10) unsigned | NO   | MUL | NULL    |                |

| col2  | int(10) unsigned | NO   | MUL | NULL    |                |

| col3  | int(10) unsigned | NO   | MUL | NULL    |                |

+-------+------------------+------+-----+---------+----------------+

-- данные таблицы

mysql> select * from table1;

+----+------+------+------+

| id | col1 | col2 | col3 |

+----+------+------+------+

|  1 |    1 |    2 |    1 |

|  2 |    2 |    3 |    2 |

|  3 |    2 |    4 |    2 |

|  4 |    5 |    6 |    2 |

|  5 |    7 |    8 |    1 |

+----+------+------+------+

-- индексы таблицы

mysql> SHOW indexes from table1;

+------------+----------------+--------------+-------------+

| Non_unique | Key_name       | Seq_in_index | Column_name |

+------------+----------------+--------------+-------------+

|          0 | PRIMARY        |            1 | id          |

|          1 | col1_col2_col3 |            1 | col1        |

|          1 | col1_col2_col3 |            2 | col2        |

|          1 | col1_col2_col3 |            3 | col3        |

|          1 | col1           |            1 | col1        |

|          1 | col2           |            1 | col2        |

|          1 | col3           |            1 | col3        |

+------------+----------------+--------------+-------------+

Запрос:

EXPLAIN SELECT id

FROM table1

WHERE (col1 = 2 AND col3 = 2) OR (col2 = 2 AND col3 = 1);

+----+-------------+--------+-------+-----------------+----------------+------+------+--------------+

| id | select_type | table  | type  | possible_keys   | key            | ref  | rows | Extra        |

+----+-------------+--------+-------+-----------------+----------------+------+------+--------------+

|  1 | SIMPLE      | table1 | index | col1_col2_col3, | col1_col2_col3 | NULL |    5 | Using where; |

|    |             |        |       | col1,col2,col3  |                |      |      | Using index  |

+----+-------------+--------+-------+-----------------+----------------+------+------+--------------+

Почему для моего запроса используется почти самый наихудший тип связывания - index? Идет перебор по всем строкам индекса. Я пытался менять индексы, безрезультатно.

Я вижу, ситуация меняется, если исключить "составной" where:

mysql> explain SELECT id

    -> FROM table1

    -> WHERE (col1 = 2 AND col3 = 2);

+-------------+--------+------+-----------------+----------------+---------+-------+------+-------------+

| select_type | table  | type | possible_keys   | key            | key_len | ref   | rows | Extra       |

+-------------+--------+------+-----------------+----------------+---------+-------+------+-------------+

| SIMPLE      | table1 | ref  | col1_col2_col3, | col1_col2_col3 | 4       | const |    2 | Using where |

|             |        |      | col1,col3       |                |         |       |      | Using index |

+-------------+--------+------+-----------------+----------------+---------+-------+------+-------------+

Может быть нужно перейти на UNION? Если да, то как обойтись без вложенных запросов в случае, если приведенный выше запрос это JOIN другого большего запроса? Что-то вроде:

SELECT qwe FROM

table2

JOIN table1

    ON (col1 = 2 AND col3 = 2) OR (col2 = 2 AND col3 = 1)

WHERE table2.col2 = table1.col2;

Был бы очень признателен за помощь.

Отредактированно FiMko (11.03.2012 18:45:56)

vasya · 11.03.2012 18:48:51

Добавьте индексы (col1,col3) и (col2,col3).
Посмотрите http://dev.mysql.com/doc/refman/5.5/en/ … ation.html

У вас в таблице слишком мало данных, чтобы изучать выбор оптимизатором того или иного плана.

P.S. Приводите, плиз, структуру таблицы не как "describe table1;"
а
mysql> show create table table1;

vasya · 11.03.2012 18:58:54

Что касается переписать на union без подзапросов, то что-то вроде

SELECT qwe FROM table2
JOIN table1 ON table2.col2 = table1.col2 WHERE (col1 = 2 AND col3 = 2)
union all
SELECT qwe FROM table2
JOIN table1 ON table2.col2 = table1.col2 WHERE (col2 = 2 AND col3 = 1)

Но это лучше оставить на потом.

FiMko · 11.03.2012 19:14:02

vasya написал:
У вас в таблице слишком мало данных, чтобы изучать выбор оптимизатором того или иного плана.

Нет, в "продакшн" таблицах с ~80000 работает также, выше я привел некий тестовый пример.

vasya написал:
P.S. Приводите, плиз, структуру таблицы не как "describe table1;"
а
mysql> show create table table1;

Спасибо, буду знать на будущее. Вот структура:

mysql> show create table table1;

CREATE TABLE `table1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `col1` int(10) unsigned NOT NULL,

  `col2` int(10) unsigned NOT NULL,

  `col3` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`),

  KEY `col1_col2_col3` (`col1`,`col2`,`col3`),

  KEY `col1` (`col1`),

  KEY `col2` (`col2`),

  KEY `col3` (`col3`),

  KEY `col1_2` (`col1`,`col3`),

  KEY `col2_2` (`col2`,`col3`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

vasya написал:
Добавьте индексы (col1,col3) и (col2,col3).

Добавил индексы, не помогло:

+----+-------------+--------+-------+-------------------------+

| id | select_type | table  | type  | possible_keys           |

+----+-------------+--------+-------+-------------------------+

|  1 | SIMPLE      | table1 | index | col1_col2_col3,col1,    |

|    |             |        |       | col2,col3,col1_2,col2_2 |

+----+-------------+--------+-------+-------------------------+

+----------------+---------+------+------+--------------------------+

| key            | key_len | ref  | rows | Extra                    |

+----------------+---------+------+------+--------------------------+

| col1_col2_col3 | 12      | NULL |    5 | Using where; Using index |

+----------------+---------+------+------+--------------------------+

Отредактированно FiMko (11.03.2012 19:16:15)

vasya · 11.03.2012 19:32:46

Хм, у меня на таблице в 32 строки показывает range по col3

mysql> explain SELECT id FROM test WHERE (col1 = 2 AND col3 = 2) OR (col2 = 2 AN

D col3 = 1)\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test

         type: range

possible_keys: col1_col2_col3,col1,col2,col3,col1_3,col2_3

          key: col3

      key_len: 4

          ref: NULL

         rows: 2

        Extra: Using where

1 row in set (0.00 sec)

FiMko · 11.03.2012 19:37:10

vasya написал:
Хм, у меня на таблице в 32 строки показывает range по col3

mysql> explain SELECT id FROM test WHERE (col1 = 2 AND col3 = 2) OR (col2 = 2 AN
D col3 = 1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: range
possible_keys: col1_col2_col3,col1,col2,col3,col1_3,col2_3
key: col3
key_len: 4
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.00 sec)

Ммм... У меня MySQL ver. 5.5.13, может влиять? А range уже приемлемый вариант связывания? Для UNION ref использовался.

mysql.ru написал:
range: При помощи индекса для выборки строк будут извлечены только строки, находящиеся в заданном диапазоне. Используемый индекс указывается в столбце key. Столбец key_len содержит самую длинную часть ключа, которая была использована. Столбец ref будет содержать значения NULL для этого типа.

---
Перепроверил, да, у меня type все еще index для 33-х строк ...

Отредактированно FiMko (11.03.2012 19:43:22)

vasya · 11.03.2012 19:58:43

Версия может влиять, у меня 5.1
range означает, что по индексу будут выбраны строки, удовлетворяющие col3 = 2 or col3 = 1 и для них проверяется where
Если кардиналити col3 высокая, то можно указать использовать этот индекс с помощью
..FROM test use undex(col3)..

FiMko · 11.03.2012 21:08:47

vasya написал:
Версия может влиять, у меня 5.1
range означает, что по индексу будут выбраны строки, удовлетворяющие col3 = 2 or col3 = 1 и для них проверяется where
Если кардиналити col3 высокая, то можно указать использовать этот индекс с помощью
..FROM test use undex(col3)..

Спасибо за помощь! Буду смотреть, что можно придумать...

vasya · 11.03.2012 21:49:13

Кстати, по вопросу различия планов выполнения. Дело не в версиях, а в разных наборах данных и как следствие разных кардиналити индекса col3.
Изначально, только две строки из 32 удовлетворяли условию col3 = 2 or col3 = 1 и выбирался range по col3. Если же уменьшить кардиналити col3, то будет full scan.

FiMko · 11.03.2012 22:50:18

vasya написал:
Кстати, по вопросу различия планов выполнения. Дело не в версиях, а в разных наборах данных и как следствие разных кардиналити индекса col3.
Изначально, только две строки из 32 удовлетворяли условию col3 = 2 or col3 = 1 и выбирался range по col3. Если же уменьшить кардиналити col3, то будет full scan.

Угу, понял. Пока переделал на UNION, стало в ~1,5 раза быстрее.

Форум пользователей MySQL

#1 11.03.2012 01:57:01

Помогите разобраться с работой индексов

#2 11.03.2012 09:50:53

Re: Помогите разобраться с работой индексов

#3 11.03.2012 12:51:02

Re: Помогите разобраться с работой индексов

vasya написал:

#4 11.03.2012 18:24:54

Re: Помогите разобраться с работой индексов

#5 11.03.2012 18:48:51

Re: Помогите разобраться с работой индексов

#6 11.03.2012 18:58:54

Re: Помогите разобраться с работой индексов

#7 11.03.2012 19:14:02

Re: Помогите разобраться с работой индексов

vasya написал:

vasya написал:

vasya написал:

#8 11.03.2012 19:32:46

Re: Помогите разобраться с работой индексов

#9 11.03.2012 19:37:10

Re: Помогите разобраться с работой индексов

vasya написал:

mysql.ru написал:

#10 11.03.2012 19:58:43

Re: Помогите разобраться с работой индексов

#11 11.03.2012 21:08:47

Re: Помогите разобраться с работой индексов

vasya написал:

#12 11.03.2012 21:49:13

Re: Помогите разобраться с работой индексов

#13 11.03.2012 22:50:18

Re: Помогите разобраться с работой индексов

vasya написал:

Board footer