[решено] Чем KEY отдличается от INDEX?

[Vlad Drakula]

Чем KEY отдличается от INDEX?

[mar]

если ты имеешь в виду PRIMARY KEY и INDEX в MySQL, то в общем, ничем. (кроме того, что PRIMARY KEY единственный и неповторимый :)) И вообще скорее это исторически сложившиеся названия. Но бывают еще, например, такие звери, как REFERENCE KEY (по-моему, в новых версиях MySQL - тоже) Там идет отслеживание целостности данных.

[vadimiron]

Вообще вроде в теории INDEX-это поле, которое просто указывает на определённые запись данных, то есть по идеи INDEX может повторятся в одной и той же таблице, если он указывает на одинаковые записи данных
PRIMARY KEY - это вроде индекс, но его значения не повторяются, то есть как написала mar-он единственный и неповторимый

Добавлено:

поэтому поле с auto_increment будет всегда PRIMARY, так как его значения не могут повторятся

[Vlad Drakula]

mar
в MySQL есть еще и просто KEY. Хранится он в таблице индексов. для оптимизации работы есть ANALYZE TABLE причем в документации сказано что она оптимизирует KEY причем это правда, я наблюдал прирост в 100раз (!), но там ничего не сказано об оптимизации INDEX.

чтоты разрешить что лучьше использовать для ускорения поиска и в чем их разничия я напишу пару тестов, а потом приведу здесь их результаты.

[Vlad Drakula]

6) KEY vs INDEX и нужно ли применять ANALYZE TABLE.
Многие слышали про KEY и про INDEX, но когда им задают вопрос «а чем они отличаются?» они затрудняются ответить. Для того чтобы найти ответ на этот вопрос я решил произвести несколько тестов. Первый тест был на создание таблицы, я создавал таблицу с ключами по нескольким столбцам, потом создавал такую же таблицу, но в место KEY использовал INDEX, результат – размер таблиц идентичен. Скорость выборки была тоже идентична. Примечание: после каждого добавления большого куска данных применялся ANALYZE TABLE. Теперь я создаю те же самые таблицы, но без вызова ANALYZE TABLE. Для таблицы с индексами размер идентичен, что и с вызовом ANALYZE TABLE, аналогично и для KEY. Теперь перейдем к тестам на скорость выборки, и тут нас ожидает сюрприз – скорость выборки упала в 3.3 раза. Выводы: INDEX и KEY хранятся одинаково, для ускорения выборки обязательно нужно применять ANALYZE TABLE, влияние на скорость выборки у них идентичное, основное различие в синтаксисе их применения!

[mar]

Влад, существует MySQL, версии которого несколько отличаются друг от друга и все - от стандарта SQL
Поэтому, наверное, можно эксперементировать, а можно посмотреть, что предлагает реляционная модель данных и что из этого имеется в MySQL
индекс (index) в реляционных базах данных вообще - *своего рода предметный указатель по атрибутам (полям), дающие ссылки на строки (записи), в которых встречается то, или иное значение. Таким образом, индекс обеспечивает увеличение скорости доступа - выборок.
Лучше поцитируем что-нибудь умное :) (Справочное руководство по языку SQL ( Версия 6.0 Part Number 778-V6.0 * * * * Ноябрь 1988 (Пересмотрено - Февраль 1990) (http://www.otd.tstu.ru/is/bd/sql.txt):
Справочное руководство по языку SQL * * * *1-8
* * * * Индексы * * * * * * * * * * * * * * * * * * *
* * * * В системах управления реляционными базами данных ORACLE *индексы используются с двумя целями:
* * * * * * ** индексы обеспечивают быстрый доступ к строкам таблицы
* * * * * * ** индексы обеспечивают уникальность строк внутри таблицы.

* * * * Обеспечение быстрого доступа * * * * * * * * *
* * * * Индексы обеспечивают *более быстрый доступ к данным для операций, возвращающих небольшие части строк таблицы. Например, * индекс на столбце EMPNO таблицы EMP дает возможность системе ORACLE непосредственно обратиться к служащему с номером 7544 вместо сканирования *всей таблицы для поиска данного сотрудника.
* * * * ORACLE не ограничивает количество *индексов, *которые *можно создавать на одной таблице; *однако для определения индексируемых столбцов надо использовать одно практическое правило.
* * * * Практическое правило: *SQL - операторы, *запрашивающие менее 10-15% *строк *таблицы *выполняются быстрее с использованием индекса. Обсуждение преимуществ использования индексов можно найти *в *разделе *"Настройка SQL - операторов и приложений" Главы 2 документа "ORACLE RDBMS *Performance *Tuning *Guide" * * (Руководство *по настройке производительности ORACLE RDBMS).
Общая дискуссия об индексах ведется в Главе 5 "Пользователь *ские *объекты *базы данных" документа "ORACLE RDBMS *database Administrator's Guide" (ORACLE RDBMS Руководство администратора базы данных).

* * * * Обеспечение уникальности * * * * * * * * * * *
* * * * Уникальность индексов *обеспечивает *требование * отсутствия дублирующихся строк *в *таблице. *В *общем случае необходимо строить уникальный индекс на основном ключе *каждой таблицы.
* * * * В редких *случаях правда необходимо хранить в таблице дублирующиеся строки и для их обеспечения ORACLE не требует *обязательного создания уникального индекса.
*Уфф! умная цитата окончена. Что касается MySQL - то имеющаяся по этому поводу документация (http://dev.mysql.com/doc/mysql/ru/MySQL_indexes.html) так и говорит: Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то MySQL может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску.
Причем, по докам в MySQl различается аж 3 типа индексов: PRIMARY, UNIQUE, и INDEX и хранятся они (опять же по докам) в виде B-деревьев. Проблемы, естественно (и не только в MySQL) возникают при работе с текстом.
Еще один момент - и не только в MySQL: СУБД "выбирают", использовать ли индекс, или обойтись без него. Хорошо, если правильно :(

Теперь ключ (Key) - в релизах MySQl имеет несколько невыраженный характер (в отличие от тестовых новых версий), о чем, помнится, писалось в руководстве по MySQL. (поэтому primary key там отличается от index только свойством primary и, *требованием к уникальности данных)
В общем же случае в реляционных базах данных ключи работают для сохранения целостности данных (я упоминала о внешнем ключе - в MySQL он появился, насколько я понимаю только в последних версиях и не для всех типов таблиц, а в стандарте SQL это понятие имеется) Существуют по крайней мере 3 типа ключей (может что еще кто знает - поправте):
- candidate (по-моему, в MySQL нет, или есть, но в последних версиях) Уникальные поля (одно, или комбинация из нескольких), однозначно идентифицирующие запись.
- primary (имеется в MySQL) отличается от candidate единственностью.
- foreign (только в последних версиях MySQL) - набор атрибутов (полей), *присутствующих в нескольких таблицах и являющимся первичным ключеи для одной из них. Обеспечивают связь данных нескольких таблиц и целостность данных (не висят мертвые души в одних таблицах при удалении данных из других таблиц.

Ну, и по поводу оптимизации (http://dev.mysql.com/doc/mysql/ru/MySQL_Optimisation.html): ANALYZE TABLE, OPTIMIZE TABLE применять, несомненно стоит, хотя бы потому, что при изменении таблиц (удалении, добавлении записей) надо оптимизировать (дефрагментировать) файловое *пространство и перестроить индексы и ключи. (в старые добрые времена при сбоях в машине, аварийном выключении питания и т.д. надо было переиндексировать таблицы - тогда они назывались базами данных, - замечательной тогда СУБД FoxPRO, а то индексы ломались в хлам) При этом - NB - MySQL - не единственная СУБД, в Oracle, Postgresql данные хранятся просто совсем не так (хотя мы и видим все те же таблицы) Уфф Все надоело писать, пусть желающие правят-дописывают :)

[dasknix]

mar, спасибо Вам большое. да Вы ещё и прекрасная наша половина, оказывается!
всю жизнь в ИТ, и только сегодня регнулся на старом добром осзоне, и то, только ради
того, чтобы лично Вас отблагодарить, весь инет набит рассуждениями, включая разные
киберфорумы (от которых уже неприятно сидеть в нете, хотя наткнулся тут на топ (http://www.cyberforum.ru/mysql/thread783148.html)
который был более-менее активен и интересен), так вот, по теме только Ваш пост помог.


Мир Вам и много послушных детей :)