SQLinfo.ru - Все о MySQL PG day 17

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 30.03.2017 18:12:14

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Как правильно расставить индексы

Здравствуйте, извиняюсь если такая тема уже была. Изучаю mysql, допустим есть такие таблицы:


CREATE TABLE `brand` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL,
  `parent` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `title` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE TABLE `images` (
  `id` int(10) UNSIGNED NOT NULL,
  `img` varchar(255) NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

CREATE TABLE `product` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `brand_id` smallint(5) UNSIGNED NOT NULL,
  `category_id` smallint(5) UNSIGNED NOT NULL,
  `first_image_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 


Как правильно расставить индексы для таких запросов, как например фильтр товаров?


SELECT product.id, product.title product_title, brand.title brand_title, category.title category_title, images.img FROM product INNER JOIN brand ON brand.id=product.brand_id INNER JOIN category ON category.id=product.category_id INNER JOIN images ON images.id=product.first_image_id WHERE product.brand_id IN (1,2,3) AND product.category_id IN (1,2,3) LIMIT 10,10;
 


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

Неактивен

 

#2 30.03.2017 22:55:36

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

1. укажите в явном виде первичные ключи по id
2. про составные индексы см FAQ №5

если может быть условие по одному из полей, то
(brand_id,  category_id) и отдельно (category_id)
или
(category_id, brand_id) + (brand_id)
или, например, при небольшом количестве возможных значений brand_id при отсутствии условия на это поле можно добавить условие в запрос, перечислив все поля - brand_id IN (1,2,3,4,5)

p.s. limit без order by - игра в рулетку

Неактивен

 

#3 31.03.2017 11:41:52

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

1. укажите в явном виде первичные ключи по id
2. про составные индексы см FAQ №5

если может быть условие по одному из полей, то
(brand_id,  category_id) и отдельно (category_id)
или
(category_id, brand_id) + (brand_id)
или, например, при небольшом количестве возможных значений brand_id при отсутствии условия на это поле можно добавить условие в запрос, перечислив все поля - brand_id IN (1,2,3,4,5)

p.s. limit без order by - игра в рулетку

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

Неактивен

 

#4 31.03.2017 11:49:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

вы смотрели FAQ №5? (там в том числе и этот вопрос разбирался)

Неактивен

 

#5 31.03.2017 17:58:34

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

вы смотрели FAQ №5? (там в том числе и этот вопрос разбирался)

Да посмотрел, спасибо.

Неактивен

 

#6 05.04.2017 11:56:52

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

p.s. limit без order by - игра в рулетку

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

Отредактированно webJunior (05.04.2017 11:57:15)

Неактивен

 

#7 05.04.2017 12:02:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

да, иначе у вас нет гарантии того какие результаты вернет запрос

Неактивен

 

#8 05.04.2017 13:06:40

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

да, иначе у вас нет гарантии того какие результаты вернет запрос

В как быть с такими запросами, когда сортировка не нужна?

Например:

SELECT * FROM t WHERE t.id=:id LIMIT 1

Неактивен

 

#9 05.04.2017 13:11:54

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

если id уникальное поле, то зачем здесь limit?
если одному значению id соответствует несколько записей, то какую из них вы хотите получить? и устроит ли вас ситуация, когда выполняя один и тот же запрос вы получаете разные данные? если да, то конечно можно и не использовать сортировку

Неактивен

 

#10 05.04.2017 13:19:47

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

если id уникальное поле, то зачем здесь limit?
если одному значению id соответствует несколько записей, то какую из них вы хотите получить? и устроит ли вас ситуация, когда выполняя один и тот же запрос вы получаете разные данные? если да, то конечно можно и не использовать сортировку

Ok.
Я просто немного запутался, под разными данными что имеется ввиду? Например если есть условие where, можно получить данные не попадающие в условие или то что данные придут в разной последовательности?

Неактивен

 

#11 05.04.2017 13:27:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

пусть условию where соответствуют 3 записи
вы пишите limit 1 без сортировки
какая из 3 записей, соответствующих условию where будет возвращена?
и всегда ли при повторном выполнении запроса будет возвращена именно та запись, которая была получена первоначально, или в будущем можно получить другую из те 3ёх, что соответствуют условию where?

Неактивен

 

#12 05.04.2017 13:35:03

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

пусть условию where соответствуют 3 записи
вы пишите limit 1 без сортировки
какая из 3 записей, соответствующих условию where будет возвращена?
и всегда ли при повторном выполнении запроса будет возвращена именно та запись, которая была получена первоначально, или в будущем можно получить другую из те 3ёх, что соответствуют условию where?

Про Limit 1 понял, спасибо.

Вот например есть такой запрос, с постраничной навигацией где не важно в какой последовательности получу данные:
 
SELECT * FROM messages WHERE  user_id=:user_id LIMIT 0,10

Я получу данные попадающие в условие, но разной последовательности? Т.е в таком случае если последовательность не важна, ORDER BY могу не указывать?

Отредактированно webJunior (05.04.2017 13:36:00)

Неактивен

 

#13 05.04.2017 13:44:58

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5156

Re: Как правильно расставить индексы

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

и с т.з. оптимизации order by + limit позволяют иногда оптимизировать выполнение запроса

Неактивен

 

#14 05.04.2017 13:47:22

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 21

Re: Как правильно расставить индексы

vasya написал:

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

и с т.з. оптимизации order by + limit позволяют иногда оптимизировать выполнение запроса

Ясно, спасибо!

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson