Индекс MySQL больше, чем хранятся данные

У меня есть база данных со следующей статистикой

Tables     Data   Index   Total
11     579,6 MB  0,9 GB  1,5 GB

Итак, как вы можете видеть, индекс близок к 2x больше. И есть одна таблица с ~ 7 миллионами строк, которая занимает не менее 99% от этого.

У меня также есть два индекса, которые очень похожи

a) UNIQUE KEY `idx_customer_invoice` (`customer_id`,`invoice_no`),
b) KEY `idx_customer_invoice_order` (`customer_id`,`invoice_no`,`order_no`)

Обновить. Вот определение таблицы (по крайней мере, структурно) самой большой таблицы

CREATE TABLE `invoices` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned NOT NULL,
  `order_no` varchar(10) default NULL,
  `invoice_no` varchar(20) default NULL,
  `customer_no` varchar(20) default NULL,
  `name` varchar(45) NOT NULL default '',
  `archived` tinyint(4) default NULL,
  `invoiced` tinyint(4) default NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `group` int(11) default NULL,
  `customer_group` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_customer_invoice` (`customer_id`,`invoice_no`),
  KEY `idx_time` (`time`),
  KEY `idx_order` (`order_no`),
  KEY `idx_customer_invoice_order` (`customer_id`,`invoice_no`,`order_no`)
) ENGINE=InnoDB AUTO_INCREMENT=9146048 DEFAULT CHARSET=latin1 |

Обновление 2:

mysql> show indexes from invoices;
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| invoices |          0 | PRIMARY                    |            1 | id          | A         |     7578066 |     NULL | NULL   |      | BTREE      |         |
| invoices |          0 | idx_customer_invoice       |            1 | customer_id | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| invoices |          0 | idx_customer_invoice       |            2 | invoice_no  | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_time                   |            1 | time        | A         |      541290 |     NULL | NULL   |      | BTREE      |         |
| invoices |          1 | idx_order                  |            1 | order_no    | A         |        6091 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            1 | customer_id | A         |          17 |     NULL | NULL   |      | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            2 | invoice_no  | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
| invoices |          1 | idx_customer_invoice_order |            3 | order_no    | A         |     7578066 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Мои вопросы:

Есть ли способ найти неиспользуемые индексы в MySQL?
Существуют ли какие-либо распространенные ошибки, влияющие на размер индекса?
Можно ли безопасно удалить indexA?
Как вы можете измерить размер каждого индекса? Все, что я получаю, это общее количество всех индексов.

Ответ 1

Вы можете удалить индекс A, потому что, как вы отметили, это подмножество другого индекса. И это возможно сделать, не нарушая нормальной обработки.

Размер индексных файлов сам по себе не является тревожным, и легко можно утверждать, что чистая прибыль является положительной. Другими словами, полезность и значение индекса не должны быть дисконтированы, потому что это приводит к большому файлу.

Индексный дизайн - это сложное и тонкое искусство с глубоким пониманием объяснений оптимизатора запросов и тщательного тестирования. Но одна распространенная ошибка состоит в том, чтобы включить слишком мало полей в индекс, чтобы сделать его меньше. Другим является проверка индексов с недостаточными или недостаточно репрезентативными данными.

Ответ 2

Я могу ошибаться, но первый индекс (idx_customer_invoice) является UNIQUE, второй (idx_customer_invoice_order) - нет, поэтому вы, вероятно, потеряете ограничение уникальности при его удалении. Нет?

Ответ 3

Есть ли способ найти неиспользуемые индексы в MySQL?

Оптимизатор движка базы данных будет выбирать правильный индекс при попытке оптимизировать ваш запрос. В зависимости от того, когда вы собрали статистику по вашим показателям, выбранный индекс будет отличаться. Неиспользованные индексы могут внезапно использоваться из-за нового перераспределения данных.

Можно ли безопасно удалить indexA?

Я бы сказал, да, если indexA и indexB являются индексами B-Tree. Это связано с тем, что индекс, начинающийся с одних и тех же столбцов в том же порядке, будет иметь одинаковую структуру.

Ответ 4

использовать

show indexes from table;

чтобы определить, какие индексы у вас есть в конкретной таблице. Кардинальность скажет, насколько полезен ваш индекс.

Вы можете безопасно удалить свои индексы (он не сломает таблицу), но будьте осторожны: некоторые запросы могут выполняться медленнее. Сначала вы должны проанализировать свои запросы, чтобы решить, нужен ли вам определенный индекс или нет.

Я не думаю, что вы можете узнать длину данных для определенного индекса.

НО, я думаю, вы, вероятно, думаете, что если длина индексов больше длины данных дважды, это что-то ненормальное... Ну, вы ошибаетесь. Все ваши индексы могут быть полезны;) Если у вас есть таблица, которая предоставляет много информации, и вам нужно искать ее на большом количестве столбцов, легко может быть, что индексы этой таблицы будут в 2 раза больше по размеру, данные таблиц.

Ответ 5

indexA может удалить, потому что есть indexB включает indexA
Что влияет ваша длина индекса ваш тип столбца и длина столбца
Использование:

выберите index_length из information_schema.tables где table_name = 'your_table_name' и table_schema = 'your_db_name';

получить таблицу index_length