Индекс 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
