Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / ORDER BY не использует индекс / 21 сообщений из 21, страница 1 из 1
15.07.2014, 00:18:17
    #38696437
qtech
qtech
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
Все привет. Почитал доку по использованию индекса при сортировке link .

Но не могу понять почему в моем случае не используется индекс. Сам запрос довольно сложный с 5 left join, но даже упростив его к самому простому виду индекс все равно не используется.

Вот упрощенный запрос и вывод explain:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
EXPLAIN SELECT SQL_NO_CACHE id, email, first_name, last_name  FROM customer ORDER BY last_name ASC, first_name  ASC;

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



SQL на создание таблицы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `integration_id` smallint(6) DEFAULT NULL,
  `website_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  `customer_group_id` int(11) DEFAULT NULL,
  `contact_id` int(11) DEFAULT NULL,
  `name_prefix` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middle_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name_suffix` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gender` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `vat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `origin_id` int(10) unsigned DEFAULT NULL,
  `lifetime` decimal(19,4) DEFAULT NULL COMMENT '(DC2Type:money)',
  `currency` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_owner_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_origin_id_integration_id` (`origin_id`,`integration_id`),
  KEY `IDX_2A61EE7D18F45C82` (`website_id`),
  KEY `IDX_2A61EE7DB092A811` (`store_id`),
  KEY `IDX_2A61EE7DD2919A68` (`customer_group_id`),
  KEY `IDX_2A61EE7DE7A1254A` (`contact_id`),
  KEY `IDX_2A61EE7D9B6B5FBA` (`account_id`),
  KEY `IDX_2A61EE7D72F5A1AA` (`integration_id`),
  KEY `customer_name_idx` (`first_name`,`last_name`),
  KEY `customer_rev_name_idx` (`last_name`,`first_name`),
  KEY `IDX_2A61EE7D9EB185F9` (`user_owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



когда указываю force index, то запрос выполняется в разы быстрее, но этто решение не подходитт так как сортировка может меняться пользователем с UI и ко всему может добавиться множество условий фильтрации.
Пробовал как на маленьком кол-ве записей так и на 500 000, результат одинаковый.

Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 00:59:46
    #38696454
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
qtech,

чет там много букв по ссылке.
Сказали что не работает если 2 сорта из РАЗНЫХ индекса.
У вас ласт-ферст -- части одного индекса, но
на ферст-ласт есть и другой индекс, может это сбивает оптимизатор
с толку.

Попробуйте упростить ситуацию до одного сорта и одного индекса
а потом добавляйте и смотрите где произойдет срыв.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 05:52:38
    #38696501
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
qtech,

а если добавить limit 10?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 12:26:24
    #38696718
alex564657498765453
alex564657498765453
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
а если обратить внимание на

) ENGINE=InnoDB AUTO_INCREMENT=50

и вспомнить, что при малом количестве данных, чем читать индекс с шдд, и чтото делать, проще считать только данные, и отсортировать.

загрузи данных 5000 строк хотябы, и если не будет использовать, ТОГДА будем думать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 12:35:26
    #38696734
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
alex564657498765453а если обратить внимание на

) ENGINE=InnoDB AUTO_INCREMENT=50Это верно, но ТС написал, чтоqtechПробовал как на маленьком кол-ве записей так и на 500 000, результат одинаковый.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 12:37:38
    #38696739
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
авторУ вас ласт-ферст -- части одного индекса, но
на ферст-ласт есть и другой индекс, может это сбивает оптимизатор
с толку.

неверно. он выбирает ВСЮ таблицу. читать всю по индексу никто не будет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 12:48:53
    #38696756
alex564657498765453
alex564657498765453
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
ScareCrowавторУ вас ласт-ферст -- части одного индекса, но
на ферст-ласт есть и другой индекс, может это сбивает оптимизатор
с толку.

неверно. он выбирает ВСЮ таблицу. читать всю по индексу никто не будет.

да поигрался на своей базе -мало много данных(две базы, одна рабочая, другая тестовая - идентичны, только в одной 20 юзеров и 20000 файлов, в другой 500 000 юзерово, и 1.4млрд файлов.

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

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

итого

при запросе select * from table order by key - мускл походу

чем читать индекс, а потом бегать по винчестеру в зад вперёд выискивать нужные записи,
щитеат что лучше он будет блоками вычитывать записи как лежат, сортировать сам , сохранять сортированый блок во временый файл, и короче ...заниматься не хилым гемороем.

вообще, я думаю вцелом оно оправдано...
ибо запрос
select * from table order by key - для таблицы с милиардом записей попахивает идиотизмом.

если надо 10 записей(лимит задать) индекс будет использован.

если выборка из колонок, которые входят в состав индекса(индекс_дата = индекс_поле1 +...+ индекс_полеН + первичный ключ ---для ИнноДБ)
то вместо чтения данных таблицы будет вычитываться индекс, который уже упорядочен по нужному полю.


остальные случаи использования индекса - это

сортировка по двум "инддексам" - по одному будет вычитываться, второй как файлсорт

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

=====
таки да, человек прав - всю таблицу вычитывать по индексу мускл не будет...правда я не до конца уверен что понимаю почему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 13:03:52
    #38696777
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
автор.правда я не до конца уверен что понимаю почему.
потому что там в оптимизаторе зашито помоему 5% от все таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 13:33:17
    #38696803
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
ScareCrow, то ли 5, то ли 20, но в любом случае не вся. Почему я про лимит и спросил - мало ли, вдруг у ТСа вообще какой-то своеобразный глюк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 14:07:02
    #38696837
alex564657498765453
alex564657498765453
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
тоесть типо если считываем больше чем 20% таблицы, то читаем без индекса???

или как применяеться этот процент???
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 15:52:03
    #38696981
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
tanglirScareCrow, то ли 5, то ли 20, но в любом случае не вся. Почему я про лимит и спросил - мало ли, вдруг у ТСа вообще какой-то своеобразный глюк.

Мне один ДБА сказал что 5% -- один из стандартов в Оракле.
Для МуСКЛа в интернете есть статейке где человек показал
20% для МуСКЛа.

кроме того, 5-20% -- это все для чтения
при наличии WHERE key (=><in..) value.

Индекс для сортировки вроде отдельный вопрос.
По ссылке есть примеры где индекс для сортировки
вообше без WHERE.

Также не совсем понятно как он (сортировочный индек)
проявляется в ЕКСПЛЕЙН. Похоже не в посибле кейс и не
в кейз, а только по наличию или точнее по
отсутсвию filesort в ячейке extra.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 16:13:34
    #38697005
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
javajdbcТакже не совсем понятно как он (сортировочный индек)
проявляется в ЕКСПЛЕЙН. Похоже не в посибле кейс и не
в кейз, а только по наличию или точнее по
отсутсвию filesort в ячейке extra.

авторWith EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.

авторПо ссылке есть примеры где индекс для сортировки
вообше без WHERE.

авторas long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 16:50:25
    #38697072
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
ScareCrowjavajdbcТакже не совсем понятно как он (сортировочный индек)
проявляется в ЕКСПЛЕЙН. Похоже не в посибле кейс и не
в кейз, а только по наличию или точнее по
отсутсвию filesort в ячейке extra.

авторWith EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.

авторПо ссылке есть примеры где индекс для сортировки
вообше без WHERE.

авторas long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause


по ЕКСПЛЕЙНУ так и не понятно есть ли "позитивная"
индикация использования индекса для сорта.
Как мы видим, наличие filesort есть "негативная"
индикация об отсутсвии использования сорт-индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 18:04:43
    #38697146
alex564657498765453
alex564657498765453
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
javajdbc,

сорт индекс??? это огткуда?!

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

под словом выгрузка, я понимал значительные обьёмы что выгрузка производиться преимущественно в файл

а выборка, это для человека, исходя из того, что человеку --- ну 20, ну 50 записей надо, больше никто перечитывать не будет.

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

примеры

table(id -PK,name INDEX, extra)

select id,name from table order by name //use index,проще выбрать эти два поля из индекса
-данные даже никчему читать

select * from table order by name//no index
select * from table where name > 'a' order by name //use index,потому что он уже будет использоваться, для выборки нужных значений---соответсвенно читая индекс(где все итак отсортировано по нейм, ...мы не сортируем по нейм, оно уже вычитываеться отсортировано по этому полю

select * from table order by name limit 10//индекс используеться не для сортировки, а для оптимизации выборки...

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

ввиду того, что индексы нужны для выборки данных, а сортировка это уже послеобработка (после выборки перед отдачей клиента) - то индексы и не используються при сортировке.

возможна ситуации
- когда сортировка не будет выполняться(файлсорт) ибо выборка будет уже в правильной последовательности
- когда именно сортировка заставит использовать индекс по сортируемому полю, но это будет не первопричиной - первопричина - оптимизация самой выборки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 18:09:51
    #38697151
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
авторкогда именно сортировка заставит использовать индекс по сортируемому полю, но это будет не первопричиной - первопричина - оптимизация самой выборки.
эпично.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 19:34:58
    #38697200
qtech
qtech
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
javajdbc,
Второй индекс(реверс) был добавлен позже, но и 1й не работал(в правильном порядке part1_part2)

tanglir,
Запросы с limit тоже не используют индекс

alex564657498765453а если обратить внимание на
и вспомнить, что при малом количестве данных, чем читать индекс с шдд, и чтото делать, проще считать только данные, и отсортировать.
загрузи данных 5000 строк хотябы, и если не будет использовать, ТОГДА будем думать.
Пробовали на базе 500 000+ как и писал в стартовом посте


alex564657498765453select * from table order by name limit 10//индекс используеться не для сортировки, а для оптимизации выборки...
как ответил выше насколько мне не изменяет память с лимитом тоже файл сорт.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT SQL_NO_CACHE id, email, first_name, last_name  FROM customer ORDER BY last_name ASC, first_name  ASC LIMIT  5 offset 0;

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



5 записей из 131 это меньше 5% не говоря о 20... Хотя мне кажется что этот процент прямо пропорционален sort_buffer_size
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2014, 21:55:03
    #38697243
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
завтра проверю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.07.2014, 00:22:19
    #38697303
qtech
qtech
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
ScareCrow,

Большое спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.07.2014, 05:11:13
    #38697377
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
qtech5 записей из 131 это меньше 5%131 запись - это, я уверен на 146%, всего одно чтение с диска (только данные)
а если читать индекс, а потом данные - это будут 2 чтения

результаты на сотнях и тысячах записей не говорят вообще ни о чём
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.07.2014, 10:51:55
    #38697594
alex564657498765453
alex564657498765453
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
qtechjavajdbc,
Второй индекс(реверс) был добавлен позже, но и 1й не работал(в правильном порядке part1_part2)

tanglir,
Запросы с limit тоже не используют индекс

alex564657498765453а если обратить внимание на
и вспомнить, что при малом количестве данных, чем читать индекс с шдд, и чтото делать, проще считать только данные, и отсортировать.
загрузи данных 5000 строк хотябы, и если не будет использовать, ТОГДА будем думать.
Пробовали на базе 500 000+ как и писал в стартовом посте


alex564657498765453select * from table order by name limit 10//индекс используеться не для сортировки, а для оптимизации выборки...
как ответил выше насколько мне не изменяет память с лимитом тоже файл сорт.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT SQL_NO_CACHE id, email, first_name, last_name  FROM customer ORDER BY last_name ASC, first_name  ASC LIMIT  5 offset 0;

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



5 записей из 131 это меньше 5% не говоря о 20... Хотя мне кажется что этот процент прямо пропорционален sort_buffer_size

КОГДА Я писал тут свои умозаключения, это я делал тесты по всякому меняя запросы, и сразу на двух базах идентичных(рабочая с 10к записей, и с тестовой производительности - с милиардом записей)
и так да, у меня при лимите индекс используеться.

НО
я не отказываюсь от своей начальной фразы..ибо я гдето это слышал, хотя может в вузе, а значит для микрософта скл, но слышал точно..
что индекс для оптимизации может не использоваться для малых таблиц, ибо проще взять данные(особено если таблица уже в памяти==кешировалась) и без индекса обработать.


Боллее того, когда у меня на таблице из 4 строчек(список активных серверов) подобное делать, то да..при лимите нету использования индекса, но на таблице в 30к записей есть.

второе что я заметил, беру с утра счас выполняю с лимитом запрос(эксплейн)
на малой таблице(10 строк) - индекс есть, повторно - индекса уже нету(в кеше таблица)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.07.2014, 14:53:02
    #38697997
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORDER BY не использует индекс
у меня в запросах на большой таблице LIMIT всегда идет по возможному индексу. даже если выбирать половину таблицы. маленьких у меня нет, проверить не на чем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / ORDER BY не использует индекс / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 336ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1834503]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 202ms
total:  336ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]