Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
Страницы: (3) [1] 2 3  ( Перейти к первому непрочитанному сообщению )  
Фильтр авторов:    показать 
  скрыть
  Закрытая темаСоздание новой темыСоздание опроса

> Cелективность в составном индексе
GET  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



TERRAFORMING ENGINEER
******

Профиль
Журнал
Группа: ★ЛжеЭксперт★
Завсегдатай форума
Сообщений: 3890
Пользователь №: 21196
На форуме: 7 лет, 2 месяца, 27 дней
Карма: 87




Привет.

Решил вот проверить, есть ли разница между размерами таблиц с одинаковыми данными, но по разному раположенными столбцами в составном индексе, а заодно и скорость выборки.

По теории левее желательно ставить более селективный столбец, но, как-то разницы вот не заметил.

Результат: скорость одинаковая, размер таблиц одинаковый.

Просто решил выложить, если кому любопытно.

Итак, 2 таблицы INNODB, по 100 000 строк в каждой.

Test1 и Test2

user posted image

У первой составной индекс:a/b/c/d
У второй составной индекс:d/a/b/c

Запросы вида:

SELECT `name` 
FROM `test1`
WHERE `a`='1'
AND `b`='0'
AND `c`='1'
AND `d` = '112915'


и

SELECT `name` 
FROM `test2`
WHERE `d`='112915'
AND `a`='1'
AND `b`='0'
AND `c`='1'


Показали одинаковую скорость.
На диске таблицы и их индексы занимают одинаковое место.

Может есть какие-то комментарии? :)


--------------------
Не тот велик, кто не падал, а тот кто падал и поднимался.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
GET  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



TERRAFORMING ENGINEER
******

Профиль
Журнал
Группа: ★ЛжеЭксперт★
Завсегдатай форума
Сообщений: 3890
Пользователь №: 21196
На форуме: 7 лет, 2 месяца, 27 дней
Карма: 87




MiksIr

Я подумал о размерах индексов прежде всего, подумал, что возможно в первом варианте как-бы на строение деревьев возможно будет больше затрат. smile.gif


--------------------
Не тот велик, кто не падал, а тот кто падал и поднимался.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
vasa_c  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 4434
Пользователь №: 17
На форуме: 11 лет, 17 дней
Карма: 16




А выборка только по `a` как?


--------------------
PMСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
T1grOK  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 2788
Пользователь №: 24406
На форуме: 6 лет, 7 месяцев, 13 дней
Карма: 180




ABC Забудь про ENUM как за страшный сон. Бесполезный тип данных.
Лучше использовать вначале столбцы с большей селективностью, чтоб не перегребать лишние данные в памяти(хоть это выполняется и очень быстро).


--------------------
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
PMПисьмо на e-mail пользователюСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
vasa_c  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 4434
Пользователь №: 17
На форуме: 11 лет, 17 дней
Карма: 16




Да причём тут селективность-шмелективность?
EXPLAIN SELECT WHERE `a`=?


--------------------
PMСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15357
Пользователь №: 4190
На форуме: 9 лет, 3 месяца, 24 дня
Карма: 468




Цитата (ABC @ 28.01.2014 - 17:01)
Решил вот проверить, есть ли разница между размерами таблиц с одинаковыми данными, но по разному раположенными столбцами в составном индексе, а заодно и скорость выборки.

Я подобную хрень проверял в Постгре, с таблицами большого размера. И однозначно могу сказать, что от порядка колонок в индексе скорость зависит. Причем заметно. Только надо "правильно проверять" smile.gif, в т.ч. исходные данные должны быть подходящие.
Уточняю насчет "подходящести". Если у тебя разных вариаций в каждой колонке примерно одинаковое количество, то порядок будет не важен. Но если у тебя в колонке "a" всего 100 разных значений, в колонке "b" 1000, а в колонке "c" - 10000, причем всего в таблице 2-3 млн записей, то тут уже порядок будет важен.

PS. По-моему, я тут даже тему делал об этом... Только было сие несколько лет тому назад. Сейчас искать лень, если честно smile.gif

Это сообщение отредактировал sergeiss - 28.01.2014 - 20:56


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Valick  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 5618
Пользователь №: 35718
На форуме: 4 года, 6 месяцев, 10 дней
Карма: 173




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


--------------------
wmr - R281553014107
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
T1grOK  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 2788
Пользователь №: 24406
На форуме: 6 лет, 7 месяцев, 13 дней
Карма: 180




Valick Ты меня пугаешь blink.gif


--------------------
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
PMПисьмо на e-mail пользователюСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
sergeiss  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Сидел он, дум великих полон - и вдаль глядел
******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 15357
Пользователь №: 4190
На форуме: 9 лет, 3 месяца, 24 дня
Карма: 468




Цитата (Valick @ 28.01.2014 - 21:10)
но что бы зависела выборка, это как-то противоречит самому понятию индекса

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


--------------------
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL

* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.

* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)

user posted image
PMICQ
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Valick  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 5618
Пользователь №: 35718
На форуме: 4 года, 6 месяцев, 10 дней
Карма: 173




sergeiss, может я не внимательно тему прочитал


--------------------
wmr - R281553014107
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
T1grOK  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 2788
Пользователь №: 24406
На форуме: 6 лет, 7 месяцев, 13 дней
Карма: 180




Есть такое понятие как "мощность"(Сardinality) индекса, который зависит от селективности.
Так вот, в том же Mysql, оптимизатор проверяет эффективность индексов и если в случае когда есть композитный индекс field1 + field2 и он используется, то при field2 + field1 Mysql может вообще отказаться использовать индекс предпочтя FULL TABLE SCAN.


--------------------
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api
PMПисьмо на e-mail пользователюСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
GET  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



TERRAFORMING ENGINEER
******

Профиль
Журнал
Группа: ★ЛжеЭксперт★
Завсегдатай форума
Сообщений: 3890
Пользователь №: 21196
На форуме: 7 лет, 2 месяца, 27 дней
Карма: 87




Цитата
ABC Забудь про ENUM как за страшный сон. Бесполезный тип данных.


Нууу...Если звезды зажигаются, значит это кому-нибудь нужно? Пруф о бесполезности работы проектировщиков MySQL можно?

Цитата
Лучше использовать вначале столбцы с большей селективностью


Так и делаю:
Цитата
По теории левее желательно ставить более селективный столбец



Цитата
Да причём тут селективность-шмелективность?

Посмотрите внимательно на оба запроса.


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

Составной индекс на 4 колонны, по всем идет выборка.

Цитата
И однозначно могу сказать, что от порядка колонок в индексе скорость зависит. Причем заметно.



Вот ради этого я пост и создал. Хотя мой тест с таблицами по 100 тыс. этого не подтвердил.

Вот, что я предположил, почему скорость должна быть разной при больших объемах.

Если первый столбец поле d - varchar - с очень высокой селективностью. В моем случае 3 строчки на 100 тысяч. Тогда для второго столбца всего 3 строки на обработку, а для последующих вообще единственная. Т.е. материал с которым придется работать MySQL совсем мал, всего 3 строчки изначально.

Если первый столбец поле a - ENUM - с очень низкой селективностью. В моем случае ~50000 строк на 100 тысяч. Тогда вплоть до самого последнего столбца будет "ворочаться" тысячи строк.

Больший объем будет занят в оперативной памяти.

Прочитав эту замечательную статью: http://habrahabr.ru/post/141767/
Цитата
Данные в InnoDB хранятся страницами по 16 Кб. Размер одной страницы — это предельный размер узла нашей древовидной структуры, от которого зависит в какой момент начнётся ветвление. Если вся таблица помещается в одну страницу, то она хранится в виде плоского списка, отсортированного по ключевому полю, без отдельной индексной таблицы.


подумал о степени заполнения страниц, ну и решил просто набросать тест, чтоб посмотреть что будет.


Спасибо всем, кто прокомментировал.


--------------------
Не тот велик, кто не падал, а тот кто падал и поднимался.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Invis1ble  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме




******

Профиль
Группа: Эксперт
Группа переписки
Сообщений: 11934
Пользователь №: 23195
На форуме: 6 лет, 10 месяцев, 6 дней
Карма: 435

Трезвый :
7 лет, 9 месяцев, 5 дней


Цитата
Забудь про ENUM как за страшный сон. Бесполезный тип данных.

это еще почему? blink.gif


--------------------
PMПисьмо на e-mail пользователюСайт пользователя
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темыСтраницы: (3) [1] 2 3  Закрытая темаСоздание новой темыСоздание опроса