| Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа |
| |
|
Мы научились находить проблемный запрос.
Мы уже рассмотрели 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 |
|
Закладки на сайте Проследить за страницей |
Created 1996-2024 by Maxim Chirkov Добавить, Поддержать, Вебмастеру |