MySQL. Интересные особенности.

[RXL]

База GeoIP.
Таблица с диапазонами глобальных сетей и идентификаторами местоположения:

Код: (MySQL)

CREATE TABLE `blocks` (
  `startIP` int(10) unsigned NOT NULL,
  `endIP` int(10) unsigned NOT NULL,
  `locID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`endIP`)
)

Оптимизированный запрос:

Код: (MySQL)

-- p_ip - текстовое представление IP
SELECT b.locID
    FROM geoip.blocks b
    WHERE b.endIP = (
        SELECT IFNULL(MIN(b2.endIP), 0)
        FROM geoip.blocks b2
        WHERE b2.endIP >= INET_ATON(p_ip)
    )

План выполнения:

Код:

+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | b     | const | PRIMARY       | PRIMARY | 4       | const |    1 |                              |
|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+

Особая оптимизированная форма запроса - выполняется мгновенно. Для "b2.endIP >=" и "MIN(b2.endIP)" проводится быстрый поиск по индексу.

Теперь добавим в запрос таблицу логов, содержащую поле IP.

Код: (MySQL)

SELECT b.locID
    FROM geoip.blocks b, test.requests r
    WHERE b.endIP = (
        SELECT IFNULL(MIN(b2.endIP), 0)
        FROM geoip.blocks b2
        WHERE b2.endIP >= r.ip
    )

План поменялся:

Код:

+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | r     | index  | NULL          | ip      | 4       | NULL |   12134 | Using index              |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 | Using where              |
|  2 | DEPENDENT SUBQUERY | b2    | index  | PRIMARY       | PRIMARY | 4       | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+

Теперь на поиск каждого IP тратится 2-3 секунды! Но, если в таблице test.requests будет только одна запись, то результат будет мгновенным, как и с первым запросом.

Теперь выделим определение места в функцию:

Код: (MySQL)

CREATE FUNCTION geoip.get_location_id2 (p_ip INT UNSIGNED) RETURNS mediumint(9)
    READS SQL DATA
    DETERMINISTIC
RETURN IFNULL(
    (
        SELECT b.locID
            FROM geoip.blocks b
            WHERE b.endIP = (
                    SELECT IFNULL(MIN(b2.endIP), 0)
                    FROM geoip.blocks b2
                    WHERE b2.endIP >= p_ip
                )
                AND b.startIP <= p_ip
    ),
    0
);

Модернизируем запрос:

Код: (MySQL)

SELECT geoip.get_location_id2(r.ip)
FROM test.requests r

Код:

+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | r     | index | NULL          | ip   | 4       | NULL | 12134 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+-------+-------------+

13 тысяч строк обрабатываются за 1.19 секунд.

[PooH]

пока странностей не вижу.
 можно подробней про "Особая оптимизированная форма запроса - выполняется мгновенно. " каковы условия её применения?

[Sla]

особенность, видимо, в использовании функции.
Поведение очевидно. Ведь функция "скопмпилирована, и не нужно ее парсить.

[RXL]

Особая форма - это мое выражение. MySQL это назвало "Select tables optimized away".

endIP - первичный ключ. Формат ключа - BTREE.

"b2.endIP >=" задает диапазон "от и до конца".
"MIN(b2.endIP)" говорит, что нужно использовать начальное значение диапазона.

Т.е. в двоичном дереве индекса выбирается одно значение. Никаких сканирований индекса и чтений таблиц.
Если в запросе более одной таблицы, то такая фишка не работает. По этому поиск вынесен в подзапрос. Но оказалось, что в запрос все равно нельзя добавить таблиц - начинается что-то непонятное.

Добавлено через 6 минут и 50 секунд:
Тут дело не в синтаксическом разборе. Менее 10 мс на одиночное значение или 2-3 секунды на строку на выборку - это огромная разница. Сдается мне, что начинает использоваться full scan: второй вариант отработал за 4 часа.

[PooH]

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

[RXL]

Совершенно верно.
В принципе, функция решает проблемы. Просто странно, что введение зависимости в подзапрос приводит к изменению плана: если в requests одна строка, то выборка быстрая, а если две - то за 5-6 секунд выполняется.

[Sla]

а если поменять местами

Код:

SELECT b.locID
    FROM geoip.blocks b, test.requests r
    WHERE b.endIP = (
        SELECT IFNULL(MIN(b2.endIP), 0)
        FROM geoip.blocks b2
        WHERE b2.endIP >= r.ip
    )

Код:

SELECT b.locID
    FROM test.requests r, geoip.blocks b
    WHERE b.endIP = (
        SELECT IFNULL(MIN(b2.endIP), 0)
        FROM geoip.blocks b2
        WHERE b2.endIP >= r.ip
    )

[RXL]

То ничего не изменится. Порядок выполнения определяет оптимизатор.

Ага... Для самостоятельного определения порядка таблиц есть опция STRAIGHT_JOIN:

Код: (MySQL)

SELECT STRAIGHT_JOIN ...
FROM table_first, table_second ...

STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use
this to speed up a query if the optimizer joins the tables in nonoptimal order. STRAIGHT_JOIN also can be used in the table_
references list. See Section 12.2.8.1, “JOIN Syntax”.
STRAIGHT_JOIN does not apply to any table that the optimizer treats as a const or system table. Such a table produces a
single row, is read during the optimization phase of query execution, and references to its columns are replaced with the appropriate
column values before query execution proceeds. These tables will appear first in the query plan displayed by EXPLAIN.
See Section 7.2.1, “Optimizing Queries with EXPLAIN”. This exception may not apply to const or system tables that are
used on the NULL-complemented side of an outer join (that is, the right-side table of a LEFT JOIN or the left-side table of a
RIGHT JOIN.

Вот в чем фишка "супер-оптимизации": если планируемый результат чтения таблицы состоит из одной строки, то она и считывается до завершения работы оптимизатора. Значит оптимизатор такой хреновый.

Добавлено через 4 минуты и 38 секунд:
Вот влияние STRAIGHT_JOIN.

mysql> explain SELECT STRAIGHT_JOIN b.locID FROM geoip.blocks b, test.requests r WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra                                       |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
|  1 | PRIMARY            | b     | ALL   | PRIMARY       | NULL    | NULL    | NULL | 3648186 |                                             |
|  1 | PRIMARY            | r     | index | NULL          | ip      | 4       | NULL |   12134 | Using where; Using index; Using join buffer |
|  2 | DEPENDENT SUBQUERY | b2    | index | PRIMARY       | PRIMARY | 4       | NULL | 3648186 | Using where; Using index                    |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
3 rows in set (0.02 sec)

mysql> explain SELECT b.locID FROM geoip.blocks b, test.requests r WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | r     | index  | NULL          | ip      | 4       | NULL |   12134 | Using index              |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 | Using where              |
|  2 | DEPENDENT SUBQUERY | b2    | index  | PRIMARY       | PRIMARY | 4       | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.01 sec)

Переставил таблицы.

mysql> explain SELECT STRAIGHT_JOIN b.locID FROM test.requests r, geoip.blocks b WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | r     | index  | NULL          | ip      | 4       | NULL |   12134 | Using index              |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 | Using where              |
|  2 | DEPENDENT SUBQUERY | b2    | index  | PRIMARY       | PRIMARY | 4       | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.00 sec)

mysql> explain SELECT b.locID FROM test.requests r, geoip.blocks b WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref  | rows    | Extra                    |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | r     | index  | NULL          | ip      | 4       | NULL |   12134 | Using index              |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY       | PRIMARY | 4       | func |       1 | Using where              |
|  2 | DEPENDENT SUBQUERY | b2    | index  | PRIMARY       | PRIMARY | 4       | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.00 sec)

Добавлено через 3 минуты и 57 секунд:
Выбираю отличающийся запрос. Запускаю...

Код: (MySQL)

SELECT STRAIGHT_JOIN b.locID
FROM geoip.blocks b, test.requests r
WHERE b.endIP = (
        SELECT IFNULL(MIN(b2.endIP), 0)
        FROM geoip.blocks b2
        WHERE b2.endIP >= r.ip
    );

Результат такой же хреновый: все 12 тыс. строк обработаны не за пару секунд - не стал ждать более минуты, ибо нет смысла.

Добавлено через 1 час, 4 минуты и 36 секунд:
С выбранным порядком получается full join для внешних таблиц. Для оптимальной же формы выполняется:
1. Выборка единственного значения r.ip.
2. Подзапрос b2.
3. Выборка по равенству из b.
От того и быстро выполняется.


Почитал: тип "index" операции в плане выполнения - это full index join. Т.ч. Пух прав как никогда


Добавлено через 1 час, 12 минут и 21 секунду:
Итого:

mysql> explain SELECT l.country, COUNT(*) FROM test.requests r, geoip.locations l WHERE l.locID = geoip.get_location_id2(r.ip) GROUP BY l.country;
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | r     | index  | NULL          | ip      | 4       | NULL | 12134 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY | 3       | func |     1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT l.country, COUNT(*) FROM test.requests r, geoip.locations l WHERE l.locID = geoip.get_location_id2(r.ip) GROUP BY l.country;
+---------+----------+
| country | COUNT(*) |
+---------+----------+
| AE      |        1 |
| AM      |        4 |
| AR      |       21 |
| AZ      |       52 |
| BE      |       13 |
| BG      |        1 |
| BR      |        9 |
| BY      |      101 |
| CH      |        1 |
| CL      |       18 |
| CN      |        3 |
| CO      |        2 |
| CZ      |       57 |
| DE      |       71 |
| DK      |       14 |
| EC      |        3 |
| EE      |       42 |
| ES      |       47 |
| FI      |        1 |
| FR      |       16 |
| GB      |       19 |
| GR      |       36 |
| GT      |        2 |
| HR      |        3 |
| HU      |        2 |
| IL      |       32 |
| IN      |       26 |
| IT      |       15 |
| JP      |       23 |
| KG      |        8 |
| KZ      |       47 |
| LT      |       35 |
| LU      |        1 |
| LV      |        9 |
| MD      |       29 |
| MO      |       17 |
| MX      |       38 |
| NL      |       13 |
| NO      |       12 |
| NP      |        2 |
| PE      |        1 |
| PL      |       28 |
| PT      |       17 |
| RO      |       20 |
| RS      |        7 |
| RU      |     5592 |
| SE      |        3 |
| SK      |       47 |
| TH      |        7 |
| TR      |       28 |
| TW      |        3 |
| UA      |     5443 |
| US      |       90 |
| UY      |        2 |
+---------+----------+
54 rows in set (2.10 sec)