LINUX.ORG.RU
Форум — Development  

mysql одноколоночный и многоколоночный индекс, поле rows

 ,


0

1

есть запрос: SELECT * FROM test2 WHERE f1 = 0 AND f2 = 0 ORDER BY f3 LIMIT 100, 100;

есть индекс по f1,f2,f3 и есть только по f3.

mysql выбирает многоколоночный индекс, но поле rows (explain) показывает 500000 для миллиона записей в таблице, если принудить использовать одноколоночный индекс то поле rows будет совсем не существенное 100-200, скорость запросов в обоих случаях одинаковая, почему mysql сканирует (и сканирует ли) столько записей и считает это выгодней, чем использование одноколоночного без сканирования.



Последнее исправление: babushka (всего исправлений: 1)

Тут нужно использовать индекс (f1,f2,f3), всё верно он выбирает. Одноколоночный по f3 однозначно приведёт к проблемам скорости если конечно поля f1 f2 действительно меняются. Чтобы mysql правильнее показывало статистику запроса в explain - попробуй сделать optimize table. Реально он проверит примерно 100 строк - индекс по f1,f2,f3 позволяет сразу выбрать нужные и не заниматься брутфорсом.

firkax ★★★★★
()
Ответ на: комментарий от firkax

optimize table показывает ‘Table does not support optimize, doing recreate + analyze instead’, analyze table тоже не меняет ситуацию с rows

еще заметил что при использовании (f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5 (длина char поля f3), т.е. при использовании первого индекса длина f3 не включается в статистике key_len странно это как то..

babushka
() автор топика
Ответ на: комментарий от babushka

Пересоздай ключ, может там при создании подкрутили чтобы из f3 бралось 0 символов.

Table does not support optimize, doing recreate + analyze instead

InnoDb что ли? Тогда проверит не 100 а 200 строк (offset будет вручную пересчитывать), потому что в нём индексы немного порезанные по сравнению с MyISAM. Но всё равно луче чем ключ чисто по f3.

И вообще можешь show create table выложить.

firkax ★★★★★
()
Последнее исправление: firkax (всего исправлений: 1)
Ответ на: комментарий от firkax

Пересоздай ключ, может там при создании подкрутили чтобы из f3 бралось 0 символов.

для char полей в отличие от text нет необходимости писать длину поля, всё поле индексируется по умолчанию

еще заметил что при использовании (f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5 (длина char поля f3), т.е. при использовании первого индекса длина f3 не включается в статистике key_len странно это как то..

такое поведение наблюдал много где, не зависит от конкретно этой таблицы

InnoDb что ли?

да, MyISAM вроде как умер

babushka
() автор топика
Ответ на: комментарий от babushka

Ну как видишь у тебя индексируется не всё поле. Если боишься трогать имеющийся - создай ещё один ключ (f1,f2,f3) с дефолтами и посмотри.

да, MyISAM вроде как умер

Тебя жестоко обманули. Это InnoDb плавно идёт к депрекации, вместо него готовится развивающийся Aria. А в нише быстрых и простых движков MyISAM вне конкуренции как был так и остался.

Сделай `SHOW CREATE TABLE tablename` и `SHOW KEYS IN tablename`

firkax ★★★★★
()
Последнее исправление: firkax (всего исправлений: 3)

Может быть потому что type «ref» считается лучше чем type «index», а «rows» планировщик в MySQL не анализирует?

Правда ни «500000 для миллиона записей», ни "(f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5" у меня не получилось воспроизвести.

При вот таком заполнении: 

SET SESSION max_recursive_iterations = 2000000;
INSERT INTO test2
	WITH RECURSIVE tgen(f1, f2, f3) AS (
		VALUES (0, 1, char(64))
		UNION ALL
		SELECT f1 + 2, f2 + 2, char(64 + (64 * rand()))
		FROM tgen
		WHERE f1 < 2000001
	)
	SELECT * FROM tgen
;
Всё красиво с индексом по f1,f2,f3. А вот по f3 так 200 rows и есть, как у вас.

Может быть MySQL не в курсе какая нужда была строить индексы при куче записей с f1=0 и f2=0, и поэтому считает, что обычно «ref» должен быть лучше.

Toxo2 ★★★
()
Ответ на: комментарий от Toxo2

Может быть потому что type «ref» считается лучше чем type «index», а «rows» планировщик в MySQL не анализирует?

Может быть MySQL не в курсе какая нужда была строить индексы при куче записей с f1=0 и f2=0, и поэтому считает, что обычно «ref» должен быть лучше.

Это могло бы быть аргументами к «почему mysql не хочет использовать трёхколоночный ключ». Но он его очень даже хочет использовать, проблема там только в том как именно он его использует.

firkax ★★★★★
()
Последнее исправление: firkax (всего исправлений: 1)

Я думаю ты что-то путаешь. Используя индекс по f1,f2,f3 можно index scan’ом достать только нужные строки, вообще без единой лишней (за исключением тех то пропускает limit, естественно). А если ты будешь сканировать по индексу включающему только f3, то придётся пропускать все строки для которых f1 != 0 или f2 != 0.

slovazap ★★★★★
()
Ответ на: комментарий от babushka

Хм, неожиданно. Ну, могу ещё такое предположить: содержимое индекса используется для WHERE, а сортировку делать не нужно т.к. индекс и так отсортирован. Т.е. находим нужную позицию по 8 байтам а потом просто читаем индекс по очереди, уже не смотря на значения ключа в нём. Вероятно и правда больше чем 8 байт читать не потребуется. Но третье поле индекса таки используется - неявно, потому что при его составлении (не этим запросом, а до) оно учитывалось.

firkax ★★★★★
()
Последнее исправление: firkax (всего исправлений: 2)
Ответ на: комментарий от firkax

Aria

Aria это движок из MariaDB, а не MySQL.

InnoDb плавно идёт к депрекации, вместо него готовится развивающийся Aria

Всё-таки Aria это замена MyISAM, а не InnoDB. В Aria вроде и не предполагаются блокировки на уровне строк, только таблиц целиком. А это значит, что если записей будет чуть больше, чем почти нисколько, то движок упрётся в блокировки.

i-rinat ★★★★★
()
Ответ на: комментарий от i-rinat

Aria это движок из MariaDB, а не MySQL.

А MariaDB это то, куда переехала оригинальная ветка разработки после того, как название MySQL оказалось захвачено проприетарной компанией.

Всё-таки Aria это замена MyISAM, а не InnoDB.

Ну ладно тут спорный вопрос. Но InnoDb это как ни крути сторонний код, хоть и тесно интегрированный в базу. Возможно, заменят в итоге на свой аналог, как бы он ни назывался.

firkax ★★★★★
()
Development