MySQL. Любые дополнительные индексы ускорили бы этот запрос?

user164863 спросил: 13 июня 2018 в 05:30 в: mysql

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

Мой запрос:

SELECT p.id, n.people_type_id, n.full_name, n.post, p.nick, 
p.key_name, p.email, p.internal_user_id FROM email_routing e 
JOIN people_emails p ON p.id=e.receiver_email_id 
JOIN people n ON n.id = p.people_id
WHERE e.message_id = 897360 AND e.basket=1

Вот результат EXPLAIN:

EXPLAIN SELECT p.id, n.people_type_id, n.full_name, n.post, p.nick, 
p.key_name, p.email, p.internal_user_id FROM email_routing e 
JOIN people_emails p ON p.id=e.receiver_email_id 
JOIN people n ON n.id = p.people_id 
WHERE e.message_id = 897360 AND e.basket=1
id select_type table partitions type possible_keys key  key_len ref         rows      filtered   Extra
1  SIMPLE      n     NULL       ALL  PRIMARY       NULL NULL    NULL        1         100.00     NULL
1  SIMPLE      p     NULL       ALL  PRIMARY       NULL NULL    NULL        3178      10.00      Using where; Using join buffer (Block Nested Loop)
1  SIMPLE      e     NULL       ref  bk1           bk1  4       server.p.id 440       1.00       Using where; Using

И вот таблицы strucutre:

SHOW CREATE TABLE people_emails; 
CREATE TABLE `people_emails` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `nick` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `key_name` varchar(255) NOT NULL,
 `people_id` int(11) NOT NULL,
 `status` int(11) NOT NULL DEFAULT '0',
 `activity` int(11) NOT NULL,
 `internal_user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=22114 DEFAULT CHARSET=utf8SHOW CREATE TABLE email_routing; 
CREATE TABLE `email_routing` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `message_id` int(11) NOT NULL,
 `sender_email_id` int(11) NOT NULL,
 `receiver_email_id` int(11) NOT NULL,
 `basket` int(11) NOT NULL,
 `status` int(11) NOT NULL,
 `popup` int(11) NOT NULL DEFAULT '0',
 `tm` int(11) NOT NULL DEFAULT '0',
 KEY `id` (`id`),
 KEY `bk1` (`receiver_email_id`,`status`,`sender_email_id`,`message_id`,`basket`),
 KEY `bk2` (`sender_email_id`,`tm`)
) ENGINE=InnoDB AUTO_INCREMENT=1054618 DEFAULT CHARSET=utf8SHOW CREATE TABLE people; 
CREATE TABLE `people` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `fname` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `lname` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `patronymic` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `gender` tinyint(1) NOT NULL,
 `full_name` varchar(255) NOT NULL DEFAULT ' ',
 `category` int(11) NOT NULL,
 `people_type_id` int(255) DEFAULT NULL,
 `tags` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `job` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `post` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `profession` varchar(255) CHARACTER SET cp1251 DEFAULT NULL,
 `zip` varchar(16) CHARACTER SET cp1251 NOT NULL,
 `country` int(11) DEFAULT NULL,
 `region` varchar(10) NOT NULL,
 `city` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `address` varchar(255) CHARACTER SET cp1251 NOT NULL,
 `address_date` date DEFAULT NULL,
 `inner` tinyint(4) NOT NULL,
 `contact_through` varchar(255) DEFAULT '',
 `next_call` date NOT NULL,
 `additional` text CHARACTER SET cp1251 NOT NULL,
 `user_id` int(11) NOT NULL,
 `changed` datetime NOT NULL,
 `status` int(11) DEFAULT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `birthday` date DEFAULT NULL,
 `last_update_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `area` text NOT NULL,
 `reviewed_` tinyint(4) NOT NULL,
 `phones_old` text NOT NULL,
 `post_sticker` text NOT NULL,
 `permissions` int(120) NOT NULL DEFAULT '0',
 `internal_user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `most_used` (`category`,`status`,`city`,`lname`,`next_call`),
 KEY `registrars` (`category`,`status`,`contact_through`,`next_call`),
 FULLTEXT KEY `lname` (`lname`),
 FULLTEXT KEY `fname` (`fname`),
 FULLTEXT KEY `mname` (`patronymic`),
 FULLTEXT KEY `Full Name` (`full_name`)
) ENGINE=MyISAM AUTO_INCREMENT=415009 DEFAULT CHARSET=utf8

Как выбрать столбцы для построения индексы, нужно также отображать текстовые столбцы или работать только с числовыми столбцами


2 ответа

EchoMike444 ответил: 13 июня 2018 в 06:34

В таблице email_routing имеется 1054618 строк.

И вы пытаетесь найти одну строку с помощью message_id.

e.message_id = 897360

BUT message_id необходимо индексировать для ускорения запроса.

message_id является частью индекса bk1, но это недостаточно, потому что message_id не является первым столбцом индекса.

user164863 ответил: 13 июня 2018 в 06:44
хорошая точка, просто отдельный индекс для message_id один?
EchoMike444 ответил: 13 июня 2018 в 07:14
если message_id уникально да
Есть решение
Rick James ответил: 13 июня 2018 в 07:22

email_routing требуется

INDEX ( message_id, basket,  -- first, in either order
        receiver_email_id )  -- for "covering"

bk1 запускает с помощью receiver_email_id; это not почти так же хорошо.

  1. Включить столбцы в WHERE, которые протестированы с помощью =.
  2. Включить другие столбцы из WHERE, GROUP BY и ORDER BY (в вашем случае нет ни одного); порядок важен, но выходит за рамки этого обсуждения .
  3. Включите любые другие столбцы одной таблицы, используемые в любом месте запроса, - это сделать его "закрывающим"" индекс. Но не беспокойтесь, если это приведет к более чем, скажем, 5 столбцам или будет включать TEXT, который не может быть в индексе.

Затем перейдите к другие таблицы. В обоих JOINs кажется, что они будут поражены их кодом PRIMARY KEYs (JOIN x ON x.id = ...)

Подробнее: Поваренная книга для создание индексов

По другим вопросам ...

Вам действительно нужно перейти в InnoDB. Начиная с 5.6, он включает FULLTEXT, но есть некоторые различия . В частности, вам может потребоваться больше полнотекстовых индексов. Например, MATCH(lname, fname) требует FULLTEXT(lname, fname).

Вы действительно хотите придерживаться cp1251? Это ограничивает вашу интернализацию главным образом английским, русским, болгарским, сербским и македонским языками. И неясно, насколько хорошо FULLTEXT (MyISAM или InnoDB) будет работать с теми неанглийскими языками.

INTs всегда 4 байта; рассмотрите использование меньших версий.

Есть ли только один people? Оптимизатор решил, что это лучший стол для начала, но это не так. Я надеюсь, что мой улучшенный индекс на email_routing обманет его, начиная с этой таблицы, что определенно будет оптимальным.