«
»

Чудеса…

Vladimir
Опубликовано в: MySQL, Юмор

Если MySQL не находит строку, значит, он её плохо ищет

Началось всё с того, что я забыл заплатить за Internet, и в благодарность за это мой провайдер меня безжалостно отрубил. Дело было вечером, делать было нечего, тем более, что я давно собирался написать статью про MySQL. И вот, когда я стал генерировать тестовую таблицу, начались чудеса…

Создал в MySQL таблицу:

[-]
View Code MySQL
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `k` int(10) unsigned NOT NULL,
  `data` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `k_2` (`k`,`id`),
  KEY `k` (`k`,`data`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

PHP-скриптом запихнул в неё данные:

[-]
View Code PHP
    for ($i=0; $i<1000000; ++$i) {
        $key  = $i % 1000;
        $data = sha1($key);
        mysql_query("INSERT DELAYED INTO `test` VALUES (NULL, {$key}, '{$data}')");
    }

Сделал два INSERT:

[-]
View Code MySQL
INSERT INTO `test` SELECT NULL, `k`, `data` FROM `test`;
INSERT INTO `test` SELECT NULL, `k`, `data` FROM `test`;

В результате получил 4,000,000 строк в таблице.

Теперь самое интересное:

[-]
View Code Text
mysql> SELECT * FROM test WHERE `k` = 5 ORDER BY `data` LIMIT 5;
+------+---+------------------------------------------+
| id   | k | data                                     |
+------+---+------------------------------------------+
|    6 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 3006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+------+---+------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE `k` = 5 LIMIT 5;
+------+---+------------------------------------------+
| id   | k | data                                     |
+------+---+------------------------------------------+
|    6 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 3006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+------+---+------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE `k` = 5 ORDER BY `data` DESC LIMIT 5;
<strong>Empty set (0.01 sec)</strong>

mysql> EXPLAIN SELECT * FROM test WHERE `k` = 5 ORDER BY `data` DESC LIMIT 5;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | k_2,k         | k    | 4       | const | 3831 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE `k` = 5 LIMIT 5;
+------+---+------------------------------------------+
| id   | k | data                                     |
+------+---+------------------------------------------+
|    6 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 3006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+------+---+------------------------------------------+
5 rows in set (0.00 sec)

Внимание, вопрос: какого икса MySQL выдает результаты, если сортировка не осуществляется, либо осуществляется по возрастанию, но не выдает результаты, если сортируем по убыванию?

По ходу я нашел неизвестный баг в MyISAM? :-)

[-]
View Code MySQL
mysql> SHOW CREATE TABLE `test`\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `k` int(10) unsigned NOT NULL,
  `data` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `k_2` (`k`,`id`),
  KEY `k` (`k`,`data`)
) ENGINE=MyISAM AUTO_INCREMENT=4000001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS;
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| test | MyISAM |      10 | Dynamic    | 4000000 |             59 |   239984000 | 281474976710655 |    148987904 |         0 |        4000001 | 2008-09-01 04:17:14 | 2008-09-01 04:21:23 | 2008-09-01 04:21:49 | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec) 
$ mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
$ uname -srvmo
Linux 2.6.24-21-server #1 SMP Mon Aug 25 17:28:54 UTC 2008 x86_64 GNU/Linux

Для очистки совести:

[-]
View Code MySQL
mysql> CHECK TABLE test EXTENDED;
+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (2 min 48.13 sec)

mysql> EXPLAIN SELECT * FROM test WHERE `k` = 5 ORDER BY `data` DESC LIMIT 5;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | test  | ref  | k_2,k         | k    | 4       | const | 3831 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE `k` = 5 ORDER BY `data` DESC LIMIT 5;
Empty set (0.00 sec)

mysql> SHOW KEYS FROM test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test  |          0 | PRIMARY  |            1 | id          | A         |     4000000 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | k_2      |            1 | k           | A         |        1000 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | k_2      |            2 | id          | A         |     4000000 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | k        |            1 | k           | A         |        1000 |     NULL | NULL   |      | BTREE      |         |
| test  |          1 | k        |            2 | data        | A         |        1000 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE test DROP KEY k_2;
Query OK, 4000000 rows affected (35.48 sec)
Records: 4000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE `k` = 5 ORDER BY `data` DESC LIMIT 5;
Empty set (0.00 sec)

Естественно, я перезагрузился, тщательно проверил диски на наличие ошибок, грохнул базу, создал её заново, перезапустил сервер — ошибка не исчезла. Создал еще парочку баз с теми же данными — без изменений.

Сделал так:

[-]
View Code MySQL
mysql> CREATE TABLE test2 AS SELECT * FROM test;
Query OK, 4000000 rows affected (4.67 sec)
Records: 4000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
Empty set (0.00 sec)

mysql> SELECT * FROM test2 WHERE k=5 ORDER BY data DESC LIMIT 5;
+---------+---+------------------------------------------+
| id      | k | data                                     |
+---------+---+------------------------------------------+
| 2686006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2669006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2668006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2667006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2666006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+---------+---+------------------------------------------+
5 rows in set (1.56 sec)

Минут через пять я вспомнил, что CREATE TABLE ... SELECT не создаёт индексы. Я решил проверить догадку:

[-]
View Code MySQL
mysql> SHOW TABLE STATUS;
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| test  | MyISAM |      10 | Dynamic    | 4000000 |             59 |   239984000 | 281474976710655 |    149490688 |         0 |        4000001 | 2008-09-01 05:28:36 | 2008-09-01 05:29:16 | 2008-09-01 05:29:41 | latin1_swedish_ci |     NULL |                |         |
| test2 | MyISAM |      10 | Dynamic    | 4000000 |             59 |   239984000 | 281474976710655 |         1024 |         0 |           NULL | 2008-09-01 05:35:17 | 2008-09-01 05:35:22 | NULL                | latin1_swedish_ci |     NULL |                |         |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE test DROP KEY k, DROP KEY k_2;
Query OK, 4000000 rows affected (14.11 sec)
Records: 4000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
+---------+---+------------------------------------------+
| id      | k | data                                     |
+---------+---+------------------------------------------+
| 2686006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2669006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2668006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2667006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 2666006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+---------+---+------------------------------------------+
5 rows in set (1.63 sec)

mysql> ALTER TABLE test ADD KEY (k, data);
Query OK, 4000000 rows affected (30.35 sec)
Records: 4000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
Empty set (0.00 sec)

mysql> ANALYZE TABLE test;
+-----------+---------+----------+-----------------------------+
| Table     | Op      | Msg_type | Msg_text                    |
+-----------+---------+----------+-----------------------------+
| test.test | analyze | status   | Table is already up to date |
+-----------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

Вот они, чудеса! Точнее, баги. С индексом MySQL не находит нужные строки, без индекса же всё работает. Причем самое странное то, что не работает только k=5; другие значения срабатывают просто на "ура".

Что характерно: я грохнул 2 миллиона строк (эх!) и оптимизировал таблИцу, и всё заработало:

[-]
View Code MySQL
mysql> DELETE FROM test ORDER BY id DESC LIMIT 2000000;
Query OK, 2000000 rows affected (1 min 17.53 sec)

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
Empty set (0.00 sec)

mysql> OPTIMIZE TABLE test;
+-----------+----------+----------+----------+
| Table     | Op       | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| test.test | optimize | status   | OK       |
+-----------+----------+----------+----------+
1 row in set (15.85 sec)

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
+---------+---+------------------------------------------+
| id      | k | data                                     |
+---------+---+------------------------------------------+
| 1999006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1998006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1997006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1996006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 1995006 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+---------+---+------------------------------------------+
5 rows in set (0.00 sec)

Затем добавил те самые два миллиона строк на место:

[-]
View Code MySQL
mysql> INSERT INTO test SELECT NULL, k, data FROM test;
Query OK, 2000000 rows affected (2 min 4.72 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test WHERE k=5 ORDER BY data DESC LIMIT 5;
+---------+---+------------------------------------------+
| id      | k | data                                     |
+---------+---+------------------------------------------+
| 4012000 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4011999 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4011998 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4011997 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
| 4011996 | 5 | ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+---------+---+------------------------------------------+
5 rows in set (0.00 sec)

Что характерно: изменился порядок следования значений: теперь одинаковые значения k следуют друг за другом. Возможно, здесь "схитрил" оптимизатор — после DELETE перед INSERT я сделал OPTIMIZE.

Такие вот пироги… Так что заплачу за Интернет и пойду писать багрепорт.

Добавить в закладки

Связанные записи

, ,
3
1
Сен
2008

Комментарии к статье «Чудеса…» (3)  »

  1. Vladimir says:
    Сентябрь 1, 2008 в 12:02

    Проверено: в InnoDB бага нет.

    Reply
  2. Vladimir says:
    Сентябрь 1, 2008 в 15:50

    Проверил на другом сервере:

    snowboarding-mail-0:~# mysql --version
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (x86_64) using readline 5.2
snowboarding-mail-0:~# uname -srvmo
Linux 2.6.22-14-server #1 SMP Tue Feb 12 03:10:53 UTC 2008 x86_64 GNU/Linux

    То же самое, баг присутствует. По ходу весь 14.12 глючный Будем писать MySQL Dev Team

    Reply
  3. Vladimir says:
    Сентябрь 1, 2008 в 17:43

    Оказалось, что это очень даже известный баг #33758. Исправлен в MySQL 5.0.67.

    В Debian фикса пока нет, а в Ubuntu есть, но только в Intrepid Ibex (8.10). Я поставил MySQL 5.0.67 на Hardy Heron, посмотрим, что получится.

    Reply

Подписаться на RSS-ленту комментариев к статье «Чудеса…» Trackback URL: http://blog.sjinks.org.ua/mysql/327-shit-happens/trackback/

Оставить комментарий к записи «Чудеса…»

Вы можете использовать данные тэги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Оставляя комментарий, Вы выражаете своё согласие с Правилами комментирования.

Подписаться, не комментируя