Блог

Главная - Вопросы по программированию - Добавление составных индексов в таблицу MYSQL

Добавление составных индексов в таблицу MYSQL

#mysql #optimization #indexing #scalability #innodb

#mysql #оптимизация #индексирование #масштабируемость #innodb

Вопрос:

У меня есть такой стол, как этот

 CREATE TABLE IF NOT EXISTS `billing_success` (
`bill_id` int(11) NOT NULL AUTO_INCREMENT,
`msisdn` char(10) NOT NULL,
`circle` varchar(2) NOT NULL,
`amount` int(11) NOT NULL,
`reference_id` varchar(100) NOT NULL,
`source` varchar(100) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`bill_id`),
KEY `msisdn` (`msisdn`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8573316 ;

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

 1.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='XX' OR source='Y' OR source='STR') AND (time like '$date%')

2.SELECT msisdn,amount,circle FROM billing_total_success WHERE source <> 'RNH' AND source <> 'STR' AND source <> 'XAS' AND source <> 'RTR' AND (time like '$date%')

3.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='STR' OR source='RER' OR source='ASD') AND time BETWEEN  '$date1' AND '$date2'

4.SELECT msisdn,amount,circle FROM billing_total_success WHERE (source='RNH') AND time BETWEEN  '$date1' AND '$date2'

Пожалуйста, скажите мне, куда вообще я должен добавить индексы для оптимизации этих запросов и как добавить правильный индекс с учетом запроса.

Комментарии:

1. Вам следует изучить EXPLAIN инструкцию. Смотрите dev.mysql.com/doc/refman/5.1/en/using-explain.html

2. Я добавил индекс, охватывающий время и источник. Правильно ли это?

3. Попробуйте таким образом. измените таблицу billing_success добавьте индекс i (source, time ) в таком порядке. Я не гуру оптимизации mysql, но я думаю, что было бы лучше избегать поиска like вовремя. Ждите других советов.

4. как говорит @nick — использование LIKE в поле метки времени неэффективно. Лучше использовать BETWEEN как в некоторых других запросах.

Ответ №1:

A. Вероятно, вам нужен индекс в поле метки времени.

Однако это не обязательно конец истории; если ваши запросы сканируют диапазон по индексу метки времени, то использование его в качестве вторичного индекса не обязательно является хорошей идеей в InnoDB.

B. Сделайте временную метку первой частью первичного ключа

Это противоречит интуиции, но поскольку InnoDB кластеризуется по первичному ключу, наличие временной метки в первой части первичного ключа превратит сканирование диапазона вторичных индексов в сканирование диапазона первичных ключей, что в целом лучше. Вторичное сканирование диапазона индексов должно извлекать каждую строку в диапазоне; сканирование по первичному ключу уже извлекло это.

C. Разделение по времени

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

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

Для разбиения на разделы требуются скрипты для выполнения текущего обслуживания таблиц (создание новых разделов; удаление старых). Вам необходимо предоставить и протестировать эти скрипты, так что это явно накладные расходы для разработчика. Операции также должны отслеживать эти сценарии, поэтому у них тоже есть операционные издержки.