Нужна помощь в понимании того, как работают индексы mysql

У меня есть таблица, которая выглядит так:

CREATE TABLE `metric` (
  `metricid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) NOT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  `lastmetricvalue` double DEFAULT NULL,
  `receivedat` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `sampleid` tinyint(3) unsigned NOT NULL,
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `lastrawvalue` double NOT NULL,
  `priority` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`metricid`),
  UNIQUE KEY `unique-metric` (`userid`,`host`,`name`,`sampleid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000221496 DEFAULT CHARSET=utf8

На данный момент у него 177 892 строки, и когда я запускаю следующий запрос:

select metricid, lastrawvalue, receivedat, name, sampleid
FROM metric m
WHERE m.userid = 8
  AND (host, name, sampleid) IN (('localhost','0.4350799184758216cpu-3/cpu-nice',0),
  ('localhost','0.4350799184758216cpu-3/cpu-system',0),
  ('localhost','0.4350799184758216cpu-3/cpu-idle',0),
  ('localhost','0.4350799184758216cpu-3/cpu-wait',0),
  ('localhost','0.4350799184758216cpu-3/cpu-interrupt',0),
  ('localhost','0.4350799184758216cpu-3/cpu-softirq',0),
  ('localhost','0.4350799184758216cpu-3/cpu-steal',0),
  ('localhost','0.4350799184758216cpu-4/cpu-user',0),
  ('localhost','0.4350799184758216cpu-4/cpu-nice',0),
  ('localhost','0.4350799184758216cpu-4/cpu-system',0),
  ('localhost','0.4350799184758216cpu-4/cpu-idle',0),
  ('localhost','0.4350799184758216cpu-4/cpu-wait',0),
  ('localhost','0.4350799184758216cpu-4/cpu-interrupt',0),
  ('localhost','0.4350799184758216cpu-4/cpu-softirq',0),
  ('localhost','0.4350799184758216cpu-4/cpu-steal',0),
  ('localhost','_util/billing-bytes',0),('localhost','_util/billing-metrics',0));

для возврата результатов требуется 0,87 секунды, объяснение:

    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: ref
possible_keys: unique-metric
          key: unique-metric
      key_len: 5
          ref: const
         rows: 85560
        Extra: Using where
1 row in set (0.00 sec)

профиль выглядит так:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000160 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000021 |
| exit open_tables()             | 0.000008 |
| System lock                    | 0.000008 |
| mysql_lock_tables(): unlocking | 0.000005 |
| exit mysqld_lock_tables()      | 0.000007 |
| init                           | 0.000068 |
| optimizing                     | 0.000018 |
| statistics                     | 0.000091 |
| preparing                      | 0.000042 |
| executing                      | 0.000005 |
| Sending data                   | 0.870180 |
| innobase_commit_low():trx_comm | 0.000012 |
| Sending data                   | 0.000111 |
| end                            | 0.000009 |
| query end                      | 0.000009 |
| ha_commit_one_phase(-1)        | 0.000015 |
| innobase_commit_low():trx_comm | 0.000004 |
| ha_commit_one_phase(-1)        | 0.000005 |
| query end                      | 0.000005 |
| closing tables                 | 0.000012 |
| freeing items                  | 0.000562 |
| logging slow query             | 0.000005 |
| cleaning up                    | 0.000005 |
| sleeping                       | 0.000006 |
+--------------------------------+----------+

Что кажется мне слишком высоким. Я попытался заменить часть userid = 8 and (host, name, sampleid) IN первого запроса на (userid, host, name, sampleid) IN, и этот запрос выполняется примерно на 0,5 с — почти в 2 раза быстрее, для справки, вот запрос:

select metricid, lastrawvalue, receivedat, name, sampleid
FROM metric m
WHERE (userid, host, name, sampleid) IN ((8,'localhost','0.4350799184758216cpu-3/cpu-nice',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-system',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-idle',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-wait',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-interrupt',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-softirq',0),
  (8,'localhost','0.4350799184758216cpu-3/cpu-steal',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-user',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-nice',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-system',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-idle',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-wait',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-interrupt',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-softirq',0),
  (8,'localhost','0.4350799184758216cpu-4/cpu-steal',0),
  (8,'localhost','_util/billing-bytes',0),
  (8,'localhost','_util/billing-metrics',0));

его объяснение выглядит так:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 171121
        Extra: Using where
1 row in set (0.00 sec)

Затем я обновил таблицу, чтобы она содержала один объединенный столбец:

alter table `metric` add `forindex` varchar(120) not null default '';
update metric set forindex = concat(userid,`host`,`name`,sampleid);
alter table metric add index `forindex` (`forindex`);

Обновлен запрос, чтобы искать только 1 строку:

select metricid, lastrawvalue, receivedat, name, sampleid
FROM metric m
WHERE (forindex) IN (('8localhost0.4350799184758216cpu-3/cpu-nice0'),
  ('8localhost0.4350799184758216cpu-3/cpu-system0'),
  ('8localhost0.4350799184758216cpu-3/cpu-idle0'),
  ('8localhost0.4350799184758216cpu-3/cpu-wait0'),
  ('8localhost0.4350799184758216cpu-3/cpu-interrupt0'),
  ('8localhost0.4350799184758216cpu-3/cpu-softirq0'),
  ('8localhost0.4350799184758216cpu-3/cpu-steal0'),
  ('8localhost0.4350799184758216cpu-4/cpu-user0'),
  ('8localhost0.4350799184758216cpu-4/cpu-nice0'),
  ('8localhost0.4350799184758216cpu-4/cpu-system0'),
  ('8localhost0.4350799184758216cpu-4/cpu-idle0'),
  ('8localhost0.4350799184758216cpu-4/cpu-wait0'),
  ('8localhost0.4350799184758216cpu-4/cpu-interrupt0'),
  ('8localhost0.4350799184758216cpu-4/cpu-softirq0'),
  ('8localhost0.4350799184758216cpu-4/cpu-steal0'),
  ('8localhost_util/billing-bytes0'),
  ('8localhost_util/billing-metrics0'));

И теперь я получаю те же результаты за 0,00 секунды! Объяснить это:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
         type: range
possible_keys: forindex
          key: forindex
      key_len: 362
          ref: NULL
         rows: 17
        Extra: Using where
1 row in set (0.00 sec)

Итак, подводя итоги, вот результаты:

  1. m.userid = X AND (host, name, sampleid) IN - используется индекс, просканировано 85560 строк, выполняется за 0,9 с.
  2. (userid, host, name, sampleid) IN - индекс не используется, просканировано 171121 строк, выполняется за 0,5 с.
  3. дополнительный столбец с составным индексом заменен индексом по объединенному вспомогательному столбцу - используется индекс, просканировано 17 строк, выполняется в 0 с

Почему второй запрос выполняется быстрее первого? И почему третий запрос намного быстрее остальных? Должен ли я вести такой столбец с единственной целью более быстрого поиска?

Версия MySQL: mysqld Ver 5.5.34-55 for Linux on x86_64 (Percona XtraDB Cluster (GPL), wsrep_25.9.r3928)


mysql performance indexing innodb
person Fluffy    schedule 10.04.2014    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Индексы помогают вашим условиям поиска в предложении WHERE, максимально сужая поиск. Вы можете видеть, как это происходит...

Поле rows в EXPLAIN дает оценку того, сколько строк необходимо будет проверить запросу, чтобы найти строки, соответствующие вашему запросу. Сравнивая rows в каждом EXPLAIN, вы можете увидеть, насколько лучше ваш более оптимизированный запрос:

     rows: 85560 -- first query

     rows: 171121 -- second query examines 2x more rows, but it was probably 
                  -- faster because the data was buffered after the first query

     rows: 17 -- third query examines 5,000x fewer rows than first query

Вы также заметили бы в деталях SHOW PROFILE, если бы вы выполнили это для третьего запроса, что «Отправка данных» намного быстрее для более быстрого запроса. Это состояние процесса указывает, сколько времени потребовалось для копирования строк из механизма хранения на уровень SQL MySQL. Даже при копировании из памяти в память это занимает некоторое время для стольких тысяч строк. Вот почему индексы так полезны.

Дополнительные полезные пояснения см. в моей презентации Как правильно разрабатывать индексы.

person Bill Karwin    schedule 10.04.2014
comment
На самом деле я запускал первый запрос несколько раз с одинаковыми результатами, поэтому данные также были буферизованы. В итоге я использовал много операторов (x and x) or (x and x), но дело в том, почему where (a,b) in ((x1,x2),(x3,x4)) не работает? - person Fluffy; 10.04.2014
comment
Что касается производительности второго запроса, может ли быть так, что поиск 85560 записей (от вторичного к кластеризованному индексу) был медленнее, чем простое сканирование через кластеризованный индекс? - person Marcus Adams; 10.04.2014
comment
@Fluffy, я не уверен, может быть, просто синтаксис не оптимизирован. - person Bill Karwin; 11.04.2014
comment
@Bill Karwin, на самом деле кажется, что простой mysql в этом случае использует составные индексы, а percona xtradb - нет. Ну что ж - person Fluffy; 11.04.2014
comment
Это было бы удивительно, так как Percona ничего не изменила в оптимизаторе. Более вероятно, что оценка статистики таблицы InnoDB отличается в одном из ваших экземпляров. Попробуйте запустить ANALYZE TABLE для указанных таблиц. - person Bill Karwin; 11.04.2014