MySQL Partitioning показывает низкую производительность

Question

Я пытался проверить, является ли реализация разбиения базы данных MySQL полезной для нашего приложения или нет. Я много слышал о преимуществах использования разбиения для большого количества записей. Но удивительно, что время отклика приложения сократилось в 3 раза при выполнении нагрузочного тестирования после разделения. Может кто-нибудь, пожалуйста, помогите объяснить причину, по которой это может произойти?

Позвольте мне объяснить подробно:

Ниже приведен DDL таблицы, когда разделение не было выполнено.

CREATE TABLE `myTable` ( 
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`column2` char(3) NOT NULL, 
`column3` char(3) NOT NULL, 
`column4` char(2) NOT NULL, 
`column5` smallint(4) unsigned NOT NULL, 
`column6` date NOT NULL, 
`column7` varchar(2) NOT NULL, 
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.', 
`column9` varchar(2) NOT NULL, 
`column10` varchar(4) NOT NULL, 
`column11` char(2) NOT NULL, 
`column12` datetime NOT NULL, 
`column13` datetime DEFAULT NULL, 
PRIMARY KEY (`column1`), 
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`), 
KEY `index2` (`column2`,`column3`,`column6`,`column4`) 
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1; 

И ниже - DDL той же таблицы после реализации разбиения "Range" на основе поля даты.

CREATE TABLE `myTable` ( 
`column1` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
`column2` char(3) NOT NULL, 
`column3` char(3) NOT NULL, 
`column4` char(2) NOT NULL, 
`column5` smallint(4) unsigned NOT NULL, 
`column6` date NOT NULL, 
`column7` varchar(2) NOT NULL, 
`column8` tinyint(3) unsigned NOT NULL COMMENT 'Seat Count Ranges from 0-9.', 
`column9` varchar(2) NOT NULL, 
`column10` varchar(4) NOT NULL, 
`column11` char(2) NOT NULL, 
`column12` datetime NOT NULL, 
`column13` datetime DEFAULT NULL, 
PRIMARY KEY (`column1`,`column6`), 
KEY `index1` (`column2`,`column3`,`column4`,`column5`,`column7`,`column6`), 
KEY `index2` (`column2`,`column3`,`column6`,`column4`) 
) ENGINE=InnoDB AUTO_INCREMENT=342024674 DEFAULT CHARSET=latin1 
PARTITION BY RANGE COLUMNS(`column6`) 
(PARTITION date_jul_11 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB, 
PARTITION date_aug_11 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB, 
PARTITION date_sep_11 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB, 
PARTITION date_oct_11 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB, 
PARTITION date_nov_11 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB, 
PARTITION date_dec_11 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB, 
PARTITION date_jan_12 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB, 
PARTITION date_feb_12 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB, 
PARTITION date_mar_12 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB, 
PARTITION date_apr_12 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB, 
PARTITION date_may_12 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB, 
PARTITION date_jun_12 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB, 
PARTITION date_jul_12 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB, 
PARTITION date_aug_12 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB, 
PARTITION date_sep_12 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB, 
PARTITION date_oct_12 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB, 
PARTITION date_nov_12 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB, 
PARTITION date_dec_12 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB, 
PARTITION date_jan_13 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB, 
PARTITION date_feb_13 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB, 
PARTITION date_mar_13 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB, 
PARTITION date_apr_13 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB, 
PARTITION date_may_13 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB, 
PARTITION date_jun_13 VALUES LESS THAN ('2013-07-01') ENGINE = InnoDB, 
PARTITION date_oth VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB); 

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

SELECT column8, column9
FROM myTable
WHERE column2 = ? AND column3 = ? AND column4 =? AND column5 = ? AND column7 = ? AND column6 = ?
LIMIT 1 

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

Обратите внимание, что количество записей в таблице "myTable" составляет около 342 миллионов, а количество тестовых данных, используемых для тестирования производительности, составляет около 2 миллионов.

Однако, как я уже сказал, производительность после реализации разбиения была в три раза ниже. Есть идеи, что могло вызвать это?

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

Answer 1

Помните, что цель разделения заключается в ускорении запросов, когда ваш запрос ограничивает количество разделов, в которых может быть найден результат. Я думаю, что проблема заключается в column6 = ? в вашем тестовом запросе. Я предполагаю, что для столбца 6, требующего точного значения, а не диапазона, ваш набор результатов сокращается до очень небольшого количества значений. Следовательно, в процессе сужения разделов вы уже по существу нашли результат. А так как индексы разделены по нескольким разделам, этот процесс сужается.

Тип запроса, который вы ожидаете получить от разделения на столбец 6, - это такой запрос, который возвращает диапазон значений, ограниченный небольшим количеством разделов. Например, попробуйте что-то вроде этого в качестве тестового запроса:

SELECT column8, column9
FROM myTable
WHERE column6 < ? AND column6 > ? AND column2 = ? AND column3 = ? AND column4 =? AND column5 = ?

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

Это может помочь: http://dev.mysql.com/tech-resources/articles/partitioning.html

Answer 2

Глядя на это, я хотел бы рассмотреть несколько вещей.

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

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

И column6 =?

То есть вы тестировали производительность данных только из одного раздела? В лучшем случае это будет не быстрее, чем со всеми данными в одной таблице.

Как указывает Натан, вы разбиваете на столбцы 6 - но у вас нет этого в начале любого из ваших индексов, поэтому СУБД должна искать индекс в каждом разделе, чтобы найти данные - это также причина, почему производительность такая плохая. (Я не согласен с тем, что разбиение помогает только в диапазоне запросов).