Существует несколько таблиц (с большими количествами записей), выборка из которых в более чем 95% случаев делается с условием отбора записей по не уникальному полю. Для упрощения возьмем пример:
TaskID - PK, clustered index;
ClusterID - non unique, secondary index;
Другие поля...

Индекс по этому полю ClusterID конечно имеется, но логическая особенность данных в любой из этих таблиц состоит в том, что записи с одним значением ClusterID разбросаны по всем страницам таблицы, поэтому SELECT * ... WHERE ClusterID=1097 вызывает много чтений с диска. Запросов много и диски сильно загружены.
Предполагаемый мной вариант решения проблемы: реорганизация хранения данных так, чтобы все записи с одинаковым ClusterID хранились рядом друг с другом. Насколько я знаю, нет возможности сделать кластерный индекс по не уникальному полю ни в MySQL, ни в других старых добрых SQL. Если все-таки есть - подскажите!
Фактически должно получиться решение, как будто использующее какой-либо Key-Value storage, но сохраняющее при этом возможности обработки SQL-запросов по отношению к тому, что содержится в Value. Я никогда ранее не имел дело с такими СУБД, поэтому желательно от вас получить несколько вариантов, чтобы потом выбрать наиболее подходящий по другим условиям проекта.
Возможности Оракла не предлагать :)

miksoftдвижок таблицы какой?
InnoDB, но при необходимости можно рассмотреть изменение.

miksoftРеально ли в запросе нужны все поля таблицы?
Да.

ScareCrowавторНасколько я знаю, нет возможности сделать кластерный индекс по не уникальному полю ни в MySQL, ни в других старых добрых SQL
чего? где вы такое прочитали?
Вот здесь конкретно про MySQL 5.5: 14.3.5.13.2. Clustered and Secondary Indexes
Про остальные СУБД: я почитал про PostgreSQL и MS SQL (уже закрыл источник...) - там не написано прямо, что нельзя, но написано прямо, что движок делает индекс из уникального поля... Возможно я неправильно понял...

AkinaЭто сколько ж на сервере оперативы, какой же объём таблиц и сколько записей тащится таким запросом? По уму - надо делать так, чтобы такие разрозненные таблицы кэшировались полностью или около того - пусьт даже ценой деления таблицы на две части со связью 1:1. Можно ещё сопроводить процесс партиционированием - тоже способствует.
Сейчас в сумме около 500млн записей в таких таблицах. Запрос возвращает в типовом варианте от 500 до 2000 записей, максимум 10000. Оперативки выделено 32Гб, но на кэширование всего ее не хватает. Даже если ее размер увеличить сейчас, то в будущем будет нужно еще и еще... Экстенсивный путь всегда ограничен доступными ресурсами (в данном случае платятся свои деньги, а не заказчика, государства, газпрома и т.п.). Нужно произвести оптимизацию, пока это не слишком сложно, а когда начнется шардинг и другие современные полезности, то изменения структуры, движков и т.п. станут на порядок сложнее.

Обдумав сказанное всеми вами, сейчас рассматриваю следующие варианты:
1) Сделать уникальное поле, содержащее каким-либо образом ClusterID, и скорее всего текстовое. При этом можно попробовать заодно решить еще задачу оптимизации сортировки дерева при выдаче результата (есть такая потребность в одной из таблиц);
2) Сменить СУБД на PostgreSQL. Покурил мануал: там можно использовать команду CLUSTER и всякие настройки для ее оптимальной работы. Но там тоже много тараканов:
а) требуется дополнительное однократное (по отн. к размеру таблицы) либо двукратное место на диске для хранения индекса, а значит движок будет этот файл использовать со всеми вытекающими...
б) все равно новые записи не будут автоматически вставляться на ту же самую дисковую страницу соответственно ClusterID, а тупо дописываться в конец, что немного ухудшает результат;
3) Сделать партиционирование конкретных таблиц (не меняя СУБД). Но тут мне не все ясно. Например, как указать движку, на каком именно диске нужно хранить конкретную партицию? Без этого эффект партиционирования мне кажется неполноценным. Но зато есть огромный плюс: партиционирование делается офигенно просто :)

Переход на MSSQL и на Оракл не рассматриваю, т.к. перечисленные мной выше варианты представляются гораздо менее затратными и более эффективными.

MasterZivУ тебя комплексные проблемы, так с кондачка в форуме с ними не разберешься...
Поэтому стараюсь отвечать на уточняющие вопросы и предлагаю для оценки уже более-менее обдуманные варианты...
По описанным мной выше трем вариантам кто что может подсказать? В частности по партиционированию: можно ли как-то добиться, чтобы партиции были на разных дисках?

ScareCrowсимлинк. но глубина анализа просто поражает. ты бы хоть конктерыне данные дал. железо, диски, характеристику базы.
За симлинк спасибо. Дал предложения DBA, думают...
Про железо: СУБД размещена в дата-центре, там свои ДБА, вроде толковые ребята и не по..исты, плюс они связываются с техподдержкой вендора оборудования. А что там у них за железо, я не спрашиваю, но уверен, что не 10-летней давности.

Cygapb-007Можно сделать фильтрующий подзапрос, возвращающий PK, и связать с ним всю таблицу по JOIN. В этом случае в покрывающем индексе будут только поля, необходимые для фильтрации записей, а в итоговый результат попадут все поля исходной таблицы. эмммм... недогнал...
Предположим, у меня для фильтрации записей только одно условие: ClusterID=XXXX. Можете пояснить свое предложение на примере?

MasterZivЯ не думаю, что партицирование и разнесение данных по разным физическим девайсам сильно поможет.
Потому что -- ещё раз -- оба снижают сложность задачи не на порядки.
Тут можно сказать даже более конкретно: партиционирование снизит нагрузку на диск в 2, в 3, в 4 и т.д. раз (не ровно, конечно, а чуть меньше), в зависимости от того, как удастся договориться с админами дата-центра.
А для искоренения проблемы будем искать другое решение.

ScareCrowглубина анализа не перестаёт поражать
Могу дать адресок, где я беру эту дрянь