Нормализация и борьба с «using temporary; using filesort»

Question

[Дмитрий Сергеев]

База данных работников.

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `surname` varchar(150) NOT NULL,
  `city_id` int(3) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `last_vacation_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Последнее время отпуска (timestamp)',
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`,`last_vacation_time`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Таблица с возрастами детей работников
--

CREATE TABLE IF NOT EXISTS `employee_child_age` (
  `employee_id` int(11) unsigned NOT NULL,
  `child_age` int(3) unsigned NOT NULL DEFAULT '0',
  KEY `employee_id` (`employee_id`,`child_age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Таблица с городами
--

CREATE TABLE IF NOT EXISTS `employee_city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(100) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=44 ;

-- --------------------------------------------------------

--
-- Таблица с различным пометками к работникам
--

CREATE TABLE IF NOT EXISTS `employee_mark` (
  `employee_id` int(11) unsigned NOT NULL,
  `mark_name` varchar(15) CHARACTER SET utf8 NOT NULL,
  KEY `employee_id` (`employee_id`,`mark_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

SELECT e.*, city.* FROM employee AS e 
    INNER JOIN employee_child_age AS age ON age.employee_id=e.id AND age.child_age>10 
    INNER JOIN employee_city AS city ON city.city_id=e.city_id 
    WHERE e.deleted=0 ORDER BY e.last_vacation_time;

EXPLAIN

+----+-------------+-------+------+---------------+-------------+---------+-----------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+-----------+------+----------------------------------------------+
| 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 3 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | age | ref | employee_id | employee_id | 4 | test.e.id | 1 | Using where; Using index |
| 1 | SIMPLE | city | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+-------------+---------+-----------+------+----------------------------------------------+

Или теже самые требования но только нужно выбрать работников с определённой меток

    SELECT e.*, city.* FROM employee AS e 
    INNER JOIN employee_child_age AS age ON age.employee_id=e.id AND age.child_age>10 
    INNER JOIN `employee_mark` AS mark ON mark.employee_id=e.id AND mark.mark_name='frg' 
    INNER JOIN employee_city AS city ON city.city_id=e.city_id 
    WHERE e.deleted=0 ORDER BY e.last_vacation_time;

EXPLAIN

+----+-------------+-------+------+-----------------+-------------+---------+-----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-------------+---------+-----------------+------+---------------------------------+
| 1 | SIMPLE | e | ref | PRIMARY,deleted | deleted | 1 | const | 2 | Using temporary; Using filesort |
| 1 | SIMPLE | age | ref | employee_id | employee_id | 4 | test.e.id | 1 | Using where; Using index |
| 1 | SIMPLE | city | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 1 | SIMPLE | mark | ref | employee_id | employee_id | 51 | test.e.id,const | 2 | Using where; Using index |
+----+-------------+-------+------+-----------------+-------------+---------+-----------------+------+---------------------------------+

Пробовал различные типа индексов но от Using temporary; Using filesort не получилось избавиться. Может как-то по другому можно нормализовать?

Answer 1

[mayorovp]

Однозначно, надо создать индексы по вторичным ключам во всех таблицах (на всякий случай напомню, что вторичный ключ — это поле, которое используется в JOIN), а также индекс по last_vacation_time в таблице employee — без этих индексов оптимизировать попросту нечего. Также поможет индекс по тем полям, по которым происходит выборка.

Для начала стоит попробовать убрать AND из условия на JOIN и перенести его в блок WHERE — смысл один, но оптимизатор MySQL никогда не отличался умом и сообразительностью.

Еще стоит попробовать использовать одну ENGINE для всех таблиц.

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

— Кстати, по поводу схемы данных. Я бы сделал словарь типов (имен) меток, чтобы не дублировать строковую информацию. И отпуски хранил бы все в отдельной таблице, а не только последний. Но сначала, конечно же, надо научиться строить быстрые запросы.

Comments

[Денис]

MySQL никогда не отличался умом и сообразительностью.

Хватит уже чушь нести!

Возможно, в последней версии эту особенность починили.

Однозначно улучшили!

Answer 2

[bav]

filesort у вас потому что вы есть «ORDER BY e.last_vacation_time» и нет ключа по этому полю. Тот ключ, что есть («deleted») не подошел оптимизатору видимо по той причине что last_vacation_time не на первом месте.

Попробуйте сделать ключ с «last_vacation_time» и убедитесь что он используется. При разных наборах данных оптимизатор mysql вполне может выбирать разные ключи. На таком количестве записей как у вас вы не получите реальной картины по производительности.