Демонстрация выигрыша в производительности от индексации таблицы SQL
Я всегда слышал, что "правильная" индексация таблиц SQL - ключ к производительности. Я никогда не видел реальный пример этого и хотел бы создать пример с использованием SQLFiddle, но не уверен в синтаксисе SQL для этого.
Допустим, у меня есть 3 таблицы: 1) Users 2) Comments 3) Items, Скажем также, что каждый элемент может комментировать любой пользователь. Итак, чтобы получить комментарии item=3, вот что такое SQL SELECT будет выглядеть так:
SELECT * from comments join users on comments.commenter_id=users.user_id
WHERE comments.item_id=3
Я слышал, что, вообще говоря, если число строк становится большим, т. Е. Много тысяч / миллионов, нужно ставить индексы на WHERE и JOIN ред колонка. Так что в этом случае comments.item_id, comments.commenter_id, а также users.user_id,
Я хотел бы создать SQLFiddle для сравнения индексации этих таблиц и не использовать много тысяч, миллионов строк для каждой таблицы. Может ли кто-нибудь помочь с созданием этого SQLFiddle?
2 ответа
Я владелец SQL Fiddle. Это определенно не место для генерации огромных баз данных для тестирования производительности. Существует слишком много других переменных, которые вы не можете (но должны в реальной жизни) контролировать, например, память, конфигурация жесткого диска и т. Д. Кроме того, в качестве общей среды есть другие люди, которые могут ее использовать. также повлиять на ваши тесты. При этом вы все равно можете создать небольшую базу данных в sqlfiddle, а затем просмотреть планы выполнения запросов с индексами и без них. Они будут согласованы независимо от других факторов окружающей среды и станут хорошим источником для оптимизации обучения.
Существует довольно много разных способов индексирования таблицы, и вы можете по-разному индексировать несколько таблиц в зависимости от того, какие операторы SELECT используются чаще всего. Два основных типа индексов называются кластеризованными и некластеризованными.
Кластерные индексы хранят всю информацию о самом индексе, а не хранят список ссылок, из которых база данных может извлечь и затем использовать для поиска фактических данных. Самый простой способ визуализировать это - представить индекс и саму таблицу как отдельные объекты. В кластеризованном индексе, если индексированный столбец используется в качестве критерия (в предложении WHERE), то информация, которую запрашивает запрос, будет извлечена непосредственно из индекса, а не из таблицы.
С другой стороны, некластеризованные индексы больше похожи на справочную таблицу. Он сообщает запросу, где хранится актуальная информация, которую он запрашивает, в самом объекте таблицы. Таким образом, по сути, есть дополнительный шаг, связанный с фактическим извлечением данных из самой таблицы, когда вы используете некластеризованные индексы.
Кластерные индексы физически хранят данные на жестком диске в последовательном порядке, и в результате вы можете иметь только один кластеризованный индекс в таблице (поскольку мы можем хранить таблицу только одним "физическим" способом на диске), Кластерные индексы также должны быть уникальными (хотя это может быть не так, как кажется невооруженным глазом, это всегда относится к самой базе данных). Из-за этого большинство кластерных индексов помещаются в первичный ключ (так как большинство первичных ключей являются уникальными).
В отличие от кластеризованных индексов, у вас может быть столько некластеризованных индексов, сколько вы хотите для таблицы, поскольку в конце концов они являются просто ссылочными таблицами для самой таблицы. Поскольку у нас есть практически неограниченное количество параметров для некластеризованных индексов, пользователи предпочитают помещать столько столбцов, сколько необходимо, в столбцы, которые обычно используются в предложении WHERE оператора SELECT.
Но, как и все вещи, избыток не всегда хорошо. Чем больше индексов вы поместите в таблицу, тем больше "накладных расходов" будет на этой таблице. Индексы могут ускорить выполнение запросов, но чрезмерные накладные расходы также замедляют их. Главное - найти баланс между слишком большим количеством индексов и недостаточным количеством индексов для вашей конкретной ситуации.
Что касается тестирования производительности ваших запросов с индексами или без них, я бы рекомендовал использовать SQL Server. В SQL Server Management Studio есть функция, называемая "План выполнения", которая сообщает стоимость и время выполнения запроса.