Форум программистов «Весельчак У»
  *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

  • Рекомендуем проверить настройки временной зоны в вашем профиле (страница "Внешний вид форума", пункт "Часовой пояс:").
  • У нас больше нет рассылок. Если вам приходят письма от наших бывших рассылок mail.ru и subscribe.ru, то знайте, что это не мы рассылаем.
   Начало  
Наши сайты
Помощь Поиск Календарь Почта Войти Регистрация  
 
Страниц: [1]   Вниз
  Печать  
Автор Тема: MySQL. Интересные особенности.  (Прочитано 10424 раз)
0 Пользователей и 1 Гость смотрят эту тему.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« : 10-06-2011 15:43 » 

База 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
Глобальный модератор

ru
Offline Offline
Пол: Мужской
... и можно без хлеба!


« Ответ #1 : 10-06-2011 17:35 » 

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

Удачного всем кодинга! -=x[PooH]x=-
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #2 : 10-06-2011 18:02 » 

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

Мы все учились понемногу... Чему-нибудь и как-нибудь.
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #3 : 10-06-2011 18:08 » 

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

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

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

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

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

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
PooH
Глобальный модератор

ru
Offline Offline
Пол: Мужской
... и можно без хлеба!


« Ответ #4 : 10-06-2011 19:49 » 

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

Удачного всем кодинга! -=x[PooH]x=-
RXL
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #5 : 10-06-2011 21:09 » 

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

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Sla
Команда клуба

ua
Offline Offline
Пол: Мужской

WWW
« Ответ #6 : 11-06-2011 03:46 » 

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

Код:
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
Технический
Администратор

ru
Offline Offline
Пол: Мужской

WWW
« Ответ #7 : 11-06-2011 07:17 » new

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

Ага... Для самостоятельного определения порядка таблиц есть опция 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)
« Последнее редактирование: 11-06-2011 10:11 от RXL » Записан

... мы преодолеваем эту трудность без синтеза распределенных прототипов. (с) Жуков М.С.
Страниц: [1]   Вверх
  Печать  
 

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines