The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

Глава 2. Получены неверные данные.

Мы научились находить проблемный запрос.

Мы уже рассмотрели 2 примера синтаксической ошибки и один пример логической. Охватывают ли эти примеры все возможности, предоставляемые нам анализом проблемного запроса? Конечно, нет! Хотя строго говоря все ошибки в запросе можно свести к этим двум подтипам. В этой главе мы рассмотрим что ещё можно сделать с проблемным запросом.

Синтаксические ошибки тривиальны, поэтому мы далее не будем на них останавливаться.

Одна из часто встречающихся проблем - запрос SELECT возвращает неверные данные. Это может происходить по ряду причин.

Рассмотрим простейший - с точки зрения устранения - пример.

mysql> select count(*) as b from t3 order by b,a;
+---+
| b |
+---+
| 2 |
| 2 |
+---+

mysql> select count(*) as b from t3;
+---+
| b |
+---+
| 2 |
+---+
1 row in set (0.01 sec)

mysql> show create table t3\G
************ 1. row ************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Несмотря на необычность запроса поведение явно неверное: count(*) не может возвращать более одной строчки, если не использован GROUP BY. Запрос тривиален и упрощать его некуда. В этом случае ваш путь по адресу http://bugs.mysql.com, в поиск, где мы и находим соответствующий баг: http://bugs.mysql.com/bug.php?id=47280 Там же мы выясняем, что баг обнаружен в версии 5.1.38 и сейчас исправлен. Соответственно обновление до версии 5.1.38 или более новой устранит проблему.

Само собой разумеется, если бага, подобного обнаруженного вами, нет, нужно его послать по тому же адресу http://bugs.mysql.com

Но что делать, если вы не можете в данный момент обновить MySQL сервер? В данном случае убрать order by. Данный пример демонстрирует не только то, что MySQL Server тоже содержит баги в коде, но и ещё один метод работы с проблемным запросом.

Приём 3: после того как вы выявили запрос, вызывающий проблемы, запустите его в командной строке и проанализируйте полученный результат.

Но существуют и баги с workaround, не требующими модификации SQL. Как правило если workaround не очевиден он описан в bug report.

Возьмём следующий пример:

mysql> create table `a` (
    ->   `id` bigint(20) not null auto_increment,
    ->   primary key (`id`)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> create table `b` (
    ->   `id` bigint(20) not null auto_increment,
    ->   `a_id` bigint(20) default null,
    ->   primary key (`id`)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into `a` values (1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into `b` values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select a.id as a_id, count(distinct b.id) as cnt from a a left join b b on a.id = b.a_id
    -> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
|    1 |   8 |
| NULL |   8 |
+------+-----+
2 rows in set (0.01 sec)

Откуда у нас 8 строк в a_id=1? Чётко видно, что мы внесли только 3 строки с a_id = 1:

mysql> insert into `b` values (1,1),(2,1),(3,1),
(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

Что и подтвержает последующий запрос уже без группировок.

mysql> select a.id as a_id, b.id from a a left join b b on a.id = b.a_id where a.id = 1;
+------+------+
| a_id | id   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+
3 rows in set (0.00 sec)

Этот случай описан по адресу http://bugs.mysql.com/bug.php?id=47650. Он также был обнаружен в версии 5.1.38 и на момент написания данного текста не был исправлен.

Но здесь имеется workaround:

mysql> alter table b add index(a_id);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select a.id as a_id, count(distinct b.id) as cnt from a a left join b b on a.id = b.a_id
    -> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
|    1 |   3 |
| NULL |   3 |
+------+-----+
2 rows in set (0.02 sec)

Как вы могли убедиться результат теперь правильный.

Приём 4: пробуйте изменить SQL таким образом, чтобы результат был правильным. Пользуйтесь поисковыми системами для нахождения workaround.

Случаи с багами в коде MySQL хоть и легки для устранения, всё-таки встречаются гораздо реже, чем баги в SQL коде пользователя.

Что же делать, чтобы определить почему запрос SELECT работает не так как ожидается?

Рассмотрим пример.

mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| f1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Но

mysql> select * from t1, t2; Empty set (0.00 sec)

Почему SELECT из двух таблиц возвращает пустой набор, хотя строки в таблице t1 существуют?

На помощь на приходит EXPLAIN EXTENDED:

mysql> \W
Show warnings enabled.

mysql> explain extended select * from t1, t2;
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                     |
|  1 | SIMPLE      | t2    | system | NULL          | NULL | NULL    | NULL |    0 |     0.00 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
2 rows in set, 1 warning (0.20 sec)

Note (Code 1003): select '1' AS `f1`,'0' AS `f2` from `test`.`t1` join `test`.`t2`

Как вы видите, запрос преобразован в запрос с JOIN, который является синоимом INNER JOIN и который не может вернуть строки из табицы t1, если в таблице t2 нет соответствующих строк. Так как таблица t2 не содержит записей, запрос ничего и не возвращает.

Для более сложных (длинных) запросов алгоритм подобный: запустить EXPLAIN EXTENDED, если ошибка не выявлена, то разбить или упростить запрос, повторить.

EXPLAIN также поможет, если ваш запрос выполняется очень долго, хотя и возвращает верные данные. Мы не будем здесь подробно на этом останавливаться, поскольку данная возможность хорошо описана в официальном MySQL User Manual. Смотрите соответствующие главы.

Приём 5: используйте EXPLAIN EXTENDED для того, чтобы понять каким образом оптимизируется (а значит и выполняется) SQL запрос.

Назад Содержание Вперёд

Автор 2009 Света Смирнова
COPYRIGHT © 2009 С.Смирнова и С.Ласунов
sveta_гав_js-client_точка_com




Спонсоры:
Слёрм
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2020 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру