Извращение во имя милисекунд
Предыстория.
Надо было оптимизировать один скриптик и тут мне пришла идея избавления от чистого "Using where" в селекте. На первый взгляд идея, конечно, бредовая. Но кое-что получилось ;)
Если читателю не знакомы слова InnoDB и MySQL можно дальше не читать.
Собственно история и краткая теория.
Mysql при получении запроса лезет в начале в индекс, где на каждой веточке лежит ссылочка на ряд. Так как в InnoDB данные лежат соответственно праймари кею, если в запросе мы выбираем только праймари кей, то обращаться к самой таблице необходимости нету. И в итоге мы в explain'e получим "Using where; Using index". Если же, нам надо ещё что-то кроме праймари, то тогда будет просто "Using where".
Но, естественно, сферических коней нету, а посему было интересно - что же быстрее селект вначале кея чисто по индексу, а потом селект полей по праймари, или же всё-таки обычный селект.
В пользу первого подхода у меня была мысль, что данные хранятся страничками. То есть, чтобы получить запись нам надо взять страничку после чего найти там запись и её получить. Когда мы ищем по праймари вероятность того, что мы получим сразу несколько записей, спросив только одну страницу, очевидно, больше чем когда мы через веточки ключа будем бегать за данными (в общем случае вообще может быть придётся постоянно в разные концы бегать, что неприятно).
Эксперимент.
В лучших традициях он состоит из трёх частей.
1) На небольших объёмах выборки (~50000).
2) На объёмах побольше(~150000).
3) На объёмах пункта 2), но больше экспериментов.
Рассматривается табличка вида id-keyfield-value с ключом соответственно по keyfield и primary(id).
В качестве простого запроса рассматривается запрос:
select SQL_NO_CACHE value from TestTable force index (keyfield) where keyfield between #тут число# and #тоже число#
В качестве запроса, вначале выбирающего primary я взял запрос:
select SQL_NO_CACHE t1.value from TestTable as t1 join (select SQL_NO_CACHE id from TestTable where keyfield between #тут число# and #тоже число#) as t21 using (id)
1)
Видно что получается одинаково, в пределах погрешности.
2) Взяли несколько побольше разницу между #from# и #to#
Тут уже видно что первый запрос выполняется всегда дольше второго.
Ну и картика номер 3 просто для того чтобы увидеть что дальше оно так и идёт.
То есть видно что есть небольшой прирост производительности порядка 1-3%. На самом деле я взял просто небольшой дамп кое-чего и в нём корреляция id-keyfield довольно сильная. То есть теоретически можно было бы картинки и покрасивее нарисовать (оценку дать побоюсь), но даже при такой табличке видно что второй запрос получается немного быстрее.
Изначально целью было фундаментальное изучение, ибо понятно что такое применять на практике как-то странно. Поэтому я доволен :)
Надо было оптимизировать один скриптик и тут мне пришла идея избавления от чистого "Using where" в селекте. На первый взгляд идея, конечно, бредовая. Но кое-что получилось ;)
Если читателю не знакомы слова InnoDB и MySQL можно дальше не читать.
Собственно история и краткая теория.
Mysql при получении запроса лезет в начале в индекс, где на каждой веточке лежит ссылочка на ряд. Так как в InnoDB данные лежат соответственно праймари кею, если в запросе мы выбираем только праймари кей, то обращаться к самой таблице необходимости нету. И в итоге мы в explain'e получим "Using where; Using index". Если же, нам надо ещё что-то кроме праймари, то тогда будет просто "Using where".
Но, естественно, сферических коней нету, а посему было интересно - что же быстрее селект вначале кея чисто по индексу, а потом селект полей по праймари, или же всё-таки обычный селект.
В пользу первого подхода у меня была мысль, что данные хранятся страничками. То есть, чтобы получить запись нам надо взять страничку после чего найти там запись и её получить. Когда мы ищем по праймари вероятность того, что мы получим сразу несколько записей, спросив только одну страницу, очевидно, больше чем когда мы через веточки ключа будем бегать за данными (в общем случае вообще может быть придётся постоянно в разные концы бегать, что неприятно).
Эксперимент.
В лучших традициях он состоит из трёх частей.
1) На небольших объёмах выборки (~50000).
2) На объёмах побольше(~150000).
3) На объёмах пункта 2), но больше экспериментов.
Рассматривается табличка вида id-keyfield-value с ключом соответственно по keyfield и primary(id).
В качестве простого запроса рассматривается запрос:
select SQL_NO_CACHE value from TestTable force index (keyfield) where keyfield between #тут число# and #тоже число#
В качестве запроса, вначале выбирающего primary я взял запрос:
select SQL_NO_CACHE t1.value from TestTable as t1 join (select SQL_NO_CACHE id from TestTable where keyfield between #тут число# and #тоже число#) as t21 using (id)
1)
Видно что получается одинаково, в пределах погрешности.
2) Взяли несколько побольше разницу между #from# и #to#
Тут уже видно что первый запрос выполняется всегда дольше второго.
Ну и картика номер 3 просто для того чтобы увидеть что дальше оно так и идёт.
То есть видно что есть небольшой прирост производительности порядка 1-3%. На самом деле я взял просто небольшой дамп кое-чего и в нём корреляция id-keyfield довольно сильная. То есть теоретически можно было бы картинки и покрасивее нарисовать (оценку дать побоюсь), но даже при такой табличке видно что второй запрос получается немного быстрее.
Изначально целью было фундаментальное изучение, ибо понятно что такое применять на практике как-то странно. Поэтому я доволен :)