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

[mmaaxx]

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

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

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

'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'


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

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)

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

'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'


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

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). Это делалось чтобы уменьшить размер данных и индекса.

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

 

[MadMike]

Возможно, дело в том, что раньше уникальных значений индекса(уникальных троек) было гораздо меньше, чем сейчас уникальных пар `sensor_id`,`time`?
Помедитируй для начала в эту сторону...

-~{}~ 03.12.06 12:46:

Сделай show keys from table_name для старой и новой таблицы, наверняка там что-нибудь полезное будет.

 

[mmaaxx]

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

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

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)

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

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, тоесть особого влияния они оказать не могут (причем, это удалении должно было бы еще уменьшить индекс).

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

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