Ускорение запроса к БД

tvv, Еще раз спасибо. С вашей помощью я много узнал и сделал.

Цитата ("tvv"):

....
Далее, есть еще варианты ускорения. Но для этого нужно знать больше о природе данных.
Можете пожалуйста привести пару десятков примеров значений поля artik?
Также, какое среднее соотношение цифр и латинских букв в значениях этого поля? Чего больше, и насколько?
Может ли такое быть, что все значение целиком состоит только из букв, или только из цифр?

Я понял главную идею про уменьшение обращений к диску.


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

Количественный состав:
70 процентов - это только цифры
30 процентов - а) среди цифр встречаются буквы (~29%), б) только буквы

примеры:
01990013831
11000018017
82830022122
6728384
MB795760
9392NK
93050306260GRV
9333012010R

Ага, я так и предполагал. Тогда продолжаем.
В порядке эксперимента попробуйте таблицу (с id и artik) разбить на две или более:

1. id, и artik типа BINARY(18) - тут будут цифры и буквы
2. id, и artik типа UNSIGNED BIGINT(18) - только для цифр
3. можно сделать и больше таблиц, смотря какие данные превалируют (например, если очень большой процент значений входило бы в 9 цифр, можно было бы сделать UNSIGNED INT(9), или если большой процент циферно-буквенных значений меньше, скажем, 8 символов, то делать еще одну таблицу с artik типа BINARY(8), и т.д.).

И теперь делать два (или более, если таблиц больше двух) SQL запроса к этим таблицам, объединенных по UNION ALL (не UNION!).

Это пока навскидку, нуждается в экспериментальной проверке. Теоретически должен быть ощутимый выигрыш.

Еще одно, в данном случае мы оптимизируем схему данных именно для данного запроса LIKE '%....%'.
Если этот запрос далеко не единственный, то к этому нужно относиться осторожнее, и смотреть на другие запросы, которые будут использоваться.
Оптимизировать схему данных таким путем можно лишь в случае, когда точно известны все возможные запросы.

Далее, если используется несколько жестких дисков, то разные таблицы можно хранить на разных дисках.
Или, при наличии очень больших таблиц, разбивать их на партиции и также размещать на разных дисках.
Тогда общая скорость чтения будет выше из-за того, что чтения с дисков будут лучше параллелиться.

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

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

Еще эксперимент. Скорее всего в данном случае можно пренебречь целостностью данных ради производительности.
Поэтому, если данная таблица будет использоваться только для запроса типа LIKE '%...%', то можно еще существенно секономить на размере таблицы (и соответственно используемой памяти), убив в ней первичный ключ (то есть оставить поле id, но не делая его primary key).

Также пожалуйста проверьте, чтобы оба поля были заданы как NOT NULL.

Какая СУБД написали бы...

Не читал всего, возможно повторюсь, самое важное - это определить план запроса, в MSSQL его прекрасно видно, так же как и дисковые, процессорные и т.д. нагрузки, из этого и следует плясать.
Разбивайте сервак на кластера, разбивайте таблицы на более мелкие, запихните запрос в хранимку, почитайте про тип поля "хэш", возможно поможет, может и юникод там лишний ? where обычно медленнее чем join, в MSSQL-е можно определять свои типы данных, если напр. у вас только цифры и пару букв, то это ускорит выборку, методов много, нужно пробовать все.

MMM_Corp, тут речь идет про MySQL. Далее по пунктам:
- да, я согласен, план запроса нужно определять прежде всего и всегда, хотя на запросе типа LIKE '%...%' и так ясно что будет фулскан, но все равно полезно.
- разбивка на кластера - в данном случае немного поможет, но смысла нет, так как записей всего лишь 6 млн, это мелочи.
- разбить таблицу на более мелкие - вот, согласен, это обязательно должно помочь (выше я написал вариант принципа разбиения)
- хранимая процедура тут не нужна, ради одного типа запроса же оптимизируем.
- хеш тут не нужен вообще, на запросах типа LIKE '%...%' он работать не может. Хеш оптимально использовать лишь для поиска по полному совпадению среди большого количества строк с относительно длинными, часто повторяющимися префиксами.
- юникод - лишний однозначно, уже убрали.
- холивар WHERE vs JOIN тут нет смысла устраивать, цель - лишь оптимизировать LIKE.
- увы, типы данных в MySQL подобно как в MSSQL определять нельзя.

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

... where (locate(lower('2345'),lower(artik))>0)