MySQL: Уменьшение размера ключа привело к увеличению размера индекса. Почему?

[mmaaxx]

MYSQL version(): 5.1.11-beta-debug-log

Была такая таблица (переименована в *_original) :

PHP код:

'events_original', 'CREATE TABLE `events_original` (
  `equipment_id` varchar(16) NOT NULL DEFAULT '0',
  `sensor` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `data` double DEFAULT NULL,
  `message` text,
  KEY `equipmentid` (`equipment_id`,`sensor`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1' 


Размеры такие:

PHP код:

mysql> show table status from phoenix like "events_original";
*************************** 1. row ***************************
           Name: events_original
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3881402
 Avg_row_length: 36
    Data_length: 140661920
Max_data_length: 281474976710655
   Index_length: 32896000
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-10-25 14:30:07
    Update_time: 2006-12-01 13:14:58
     Check_time: 2006-12-01 13:14:58
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec) 


эта таблица была переделана в такую:

PHP код:

'events', 'CREATE TABLE `events` (
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `time` int(10) unsigned NOT NULL DEFAULT '0',
  `data` float DEFAULT NULL,
  `message` text,
  `sensor_id` int(11) NOT NULL,
  KEY `speed` (`sensor_id`,`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1' 


Размеры такие:

PHP код:

mysql> show table status from phoenix like "events";
*************************** 1. row ***************************
           Name: events
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3831702
 Avg_row_length: 20
    Data_length: 78099568
Max_data_length: 281474976710655
   Index_length: 54848512
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2006-12-01 13:31:36
    Update_time: 2006-12-01 13:33:56
     Check_time: 2006-12-01 13:33:56
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec) 


Фактически вместо equipment_id (16 байт) + sensor (1 байт) был введен sensor_id (4 байт). Фактически каждая пара equipment_id @ sensor, была переиндексирована в sensor_id (4 байт для всех сенсоров с головой хватит). Соответственно изменился и ключ (раньше он был 21 байт, сейчас — 8).

Вышло, что каждой паре equipment_id + sensor сейчас отвечает sensor_id, тоесть их количество должно быть равным (тоесть сенсоры как были уникальными, так ими и остались). Время вообще не трогалось (соответственно оно и не должно играть никакой роли).

Тоесть количество уникальных 3 практически равно количеству уникальных 2.

Вот результаты SHOW KEYS:

PHP код:

mysql> show keys from events_or;
+-----------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name    | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| events_or |          1 | equipmentid |            1 | equipment_id | A         |          14 |     NULL | NULL   |      | BTREE      |         |
| events_or |          1 | equipmentid |            2 | sensor       | A         |          80 |     NULL | NULL   |      | BTREE      |         |
| events_or |          1 | equipmentid |            3 | time         | A         |     3881402 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+-------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> show keys from events;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| events |          1 | speed    |            1 | sensor_id   | A         |          76 |     NULL | NULL   |      | BTREE      |         |
| events |          1 | speed    |            2 | time        | A         |     3831708 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec) 


Вот, относительно уникальости пар:

PHP код:

mysql> select count(DISTINCT `sensor_id`) from `events`;
+-----------------------------+
| count(DISTINCT `sensor_id`) |
+-----------------------------+
|                          76 |
+-----------------------------+
1 row in set (42.95 sec)

mysql> select count(DISTINCT `equipment_id`,`sensor`) from `events_or`;
+-----------------------------------------+
| count(DISTINCT `equipment_id`,`sensor`) |
+-----------------------------------------+
|                                      81 |
+-----------------------------------------+
1 row in set (53.94 sec) 


Разница в количестве, объясняется тем что во время переиндексации были найдены events для несуществующих сенсоров (наверно остались с ранних стадий разработки). Всего было удалено 49700 events, тоесть особого влияния они оказать не могут (причем, это удаление должно было бы уменьшить индекс).

Это делалось чтобы уменьшить размер данных и индекса.

НО размер индекса вырос почти в 2 раза!!!
Почему, и как с этим боротся???

Таблица реиндексировалась (как только я увидел выросший размер, сначала сделал OPTIMIZE, потом удалил MYI и пересоздал его). Размер индекса не изменился.

[shuron]

А что точно иммется ввиду под Index_lenght?
Я так понимаю Индекс просто подругому мог быть постоин.
Может во втором случае просто двух димензиаональный индекс используется а в первом два простых. или ещё какие то оптимизации.. Это дело довольно токаи скрытое от глаз пользователя.. да и слава богу...

Imho затачивать DB-cütve надо на быстродействие а не на размеры индекса..
Почему тебе именно размер индекса важен?

Если работает быстрее не все ли равно,?
DB по идее сама выберет оптимальный вариант индекса т.д.

[mmaaxx]

Да именно Index_lenght (или размер MYI файла). В первом посте всё описано, какие индексы, типы, и т.п. Бывают разные задачи и разные затачивания.

От размера индекса зависят многие параметры.

Нет, не все равно.

[shuron]

Сообщение от mmaaxx

Да именно Index_lenght (или размер MYI файла). В первом посте всё описано, какие индексы, типы, и т.п. Бывают разные задачи и разные затачивания.

От размера индекса зависят многие параметры.

Нет, не все равно.

Под типом индекса я имею ввиду его внутренне репрезентацию. тоестьпути доступа... Я уже точно не скажу как эо всё называется, темболее на русском..
На пример простой индекс мождет быть реализован B*-Tree а для двоного могла быть использована уже k-d-Tree, k-d-B Tree. В с тремя ключами бог его занет тут и простой вариант 3 раза возможен и GridFile.

Ведь на каком поле Индекс это одно, другое каков алгоритм индекса. И это уж DBMS выбирает обычно сама. Как это делает MySQL понятия не имею.

Но imho именно в этом дело.