Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа

9 Обзор языка MySQL

MySQL имеет очень сложный, но интуитивно понятный интерфейс SQL. Эта глава описывает различные команды, типы и функции, которые Вы должны знать, чтобы использовать MySQL эффективно. Эта глава также может служить справочником по всем функциональным возможностям, включенным в MySQL.

9.1 Базисные команды клиента MySQL

9.1.1 Синтаксис `USE`

USE db_name

Команда USE db_name сообщает, чтобы MySQL использовал базу данных db_name как заданную по умолчанию для последующих запросов. База данных остается текущей до конца сеанса, или пока не будет выдана другая инструкция USE:

mysql> USE db1;
mysql> SELECT count(*) FROM mytable;      # selects from db1.mytable
mysql> USE db2;
mysql> SELECT count(*) FROM mytable;      # selects from db2.mytable

Создание специфической базы данных посредством инструкции USE не препятствует Вам обращаться к таблицам в других базах данных. Пример ниже обращается к таблице author из базы данных db1 и таблице editor из базы данных db2:

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
           WHERE author.editor_id = db2.editor.editor_id;

Инструкция USE предусмотрена для совместимости с Sybase.

9.1.2 Синтаксис `DESCRIBE` (получение информации о столбцах)

{DESCRIBE | DESC} tbl_name {col_name | wild}

DESCRIBE представляет собой сокращение для вызова SHOW COLUMNS FROM. Подробности в разделе "4.10 Получение информации о базах данных, таблицах, столбцах и индексах".

DESCRIBE обеспечивает информацию относительно столбцов таблицы. col_name может быть именем столбца или строкой, содержащей групповые символы SQL `%' и `_'.

Если типы столбцов не те, которые Вы задавали в инструкции CREATE TABLE, обратите внимание, что MySQL иногда изменяет типы столбцов. Подробности в разделе "7.3.1 Тихие изменения спецификации столбца".

Эта инструкция предусмотрена для совместимости с Oracle.

Инструкция SHOW обеспечивает подобную информацию. Подробности в разделе "4.10 Синтаксис SHOW".

9.2 Команды транзакций и блокировок в MySQL

9.2.1 Синтаксис `BEGIN/COMMIT/ROLLBACK`

По умолчанию, MySQL выполняется в режиме autocommit. Это означает, что, как только Вы сделаете модификацию, MySQL сохранит ее на диск.

Если Вы используете транзакционно-безопасные таблицы (подобно BDB, InnoDB, Вы можете перевести MySQL в режим не-autocommit следующей командой:

SET AUTOCOMMIT=0

После того, как это сделано, Вы должны использовать COMMIT, чтобы сохранить Ваши изменения на диске, или ROLLBACK, если Вы хотите игнорировать изменения, которые сделали с начала Вашей транзакции.

Если Вы хотите переключать режим AUTOCOMMIT для одного набора инструкций, Вы можете использовать команды обрамления BEGIN или BEGIN WORK так:

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Обратите внимание, что, если Вы используете не транзакционно-безопасные таблицы, изменения будут сохранены сразу, независимо от состояния режима autocommit.

Если Вы делаете ROLLBACK, когда Вы модифицировали не транзакционно-безопасные таблицы, Вы получите ошибку (ER_WARNING_NOT_COMPLETE_ROLLBACK) как предупреждение. Все транзакционно-безопасные таблицы будут восстановлены, но любая транзакционно-небезопасная таблица не будет изменяться.

Если Вы используете BEGIN или SET AUTOCOMMIT=0, Вы должны использовать двоичный файл регистрации MySQL для резервирования вместо старого файла регистрации модификаций. Транзакции сохранены в двоичном протоколе, запись для COMMIT может гарантировать, что транзакции, которые прокручены обратно, не сохранены.

Следующие команды автоматически заканчивают транзакцию (как будто Вы сделали COMMIT перед выполнением команды):

`ALTER TABLE`	`BEGIN`	`CREATE INDEX`
`DROP DATABASE`	`DROP TABLE`	`RENAME TABLE`
`TRUNCATE`

Вы можете изменять уровень изоляции для транзакций командой SET TRANSACTION ISOLATION LEVEL .... Подробности в разделе "9.2.3 Синтаксис SET TRANSACTION".

9.2.2 Синтаксис `LOCK TABLES/UNLOCK TABLES`

LOCK TABLES tbl_name [AS alias] {READ|[READ LOCAL]|[LOW_PRIORITY] WRITE}
            [, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES блокирует таблицы для текущего потока. UNLOCK TABLES снимает любые блокировки для текущего потока. Все таблицы, которые блокированы текущим потоком, автоматически разблокируются, когда поток выдает другую команду LOCK TABLES, или подключение к серверу нормально закрывается.

Основные причины использовать LOCK TABLES: эмуляция транзакций или получение большего быстродействия при модифицировании таблиц. Это объясняется более подробно позже.

Если поток получает блокировку READ на таблице, он (и все остальные) могут только читать из таблицы. Если поток получает блокировку WRITE на таблице, то только он может читать или писать таблицу. Другие потоки блокированы.

Различие между READ LOCAL и READ в том, что READ LOCAL позволяет непротиворечивым инструкциям INSERT выполняться в то время, как установлена блокировка. Это не может использоваться, если Вы собираетесь управлять файлами базы данных снаружи MySQL в то время, как Вы поставили блокировку.

Когда Вы используете LOCK TABLES, Вы должны блокировать все таблицы, которые Вы собираетесь использовать, и использовать тот же самый псевдоним, который собираетесь применить в Ваших запросах! Если Вы используете таблицу в запросе несколько раз (с псевдонимами), Вы должны получить блокировку для каждого псевдонима!

Блокировки WRITE обычно имеют более высокий приоритет, чем READ, чтобы гарантировать, что модификации будут обработаны как можно скорее. Это означает, что, если один поток получает блокировку READ, и затем другой поток запрашивает блокировку WRITE, последующие запросы блокировки READ будут ждать, пока поток WRITE не получит блокировку и не снимет ее. Вы можете использовать блокировку LOW_PRIORITY WRITE, чтобы позволить другим потокам получать блокировки READ, в то время как поток ждет блокировку WRITE. Вы должны использовать блокировку LOW_PRIORITY WRITE только в случае, если Вы уверены, что будет в конечном счете такой момент, когда никакие потоки не будут иметь запрос на блокировку READ.

LOCK TABLES работает так:

Сортирует все таблицы, которые будут блокированы, во внутреннем определенном порядке (с точки зрения пользователя, порядок неопределен).
Если таблица блокирована с помощью блокировок read и write, write всегда размещается перед read.
Блокируется одна таблица за раз, пока поток не получает все блокировки.

Эта стратегия гарантирует, что блокировка таблицы свободна от тупиков. Имеются, однако, другие вещи, о которых надо знать:

Если Вы используете блокировку LOW_PRIORITY_WRITE для таблицы, это означает, что MySQL будет ждать эту блокировку до тех пор, пока не останется потока, который просит блокировку READ. Когда поток имеет блокировку WRITE и ждет, чтобы получить блокировку для следующей таблицы в списке таблиц блокировки, все другие потоки будут ждать освобождения блокировки WRITE. Если это становится серьезной проблемой для Вашей прикладной программы, Вы должны рассмотреть преобразование некоторых из Ваших таблиц в транзакционно-безопасные.

Вы можете безопасно уничтожать поток, который ждет блокировку таблицы, с помощью команды KILL. Подробности в разделе "4.9 Синтаксис KILL".

Обратите внимание, что Вы НЕ должны блокировать таблицы, которые Вы используете с INSERT DELAYED. Это потому, что в этом случае INSERT выполняется отдельным потоком.

Обычно Вы не должны блокировать таблицы, поскольку все одиночные инструкции UPDATE атомные: никакой поток не может сталкиваться с любым другим, в настоящее время выполняющим инструкции SQL. Имеется несколько случаев, когда стоит блокировать таблицы:

Если Вы собираетесь выполнять много операций на связке таблиц, намного быстрее блокировать таблицы, которые Вы собираетесь использовать. Конечно, никакой другой поток не может модифицировать блокированную на READ таблицу, и никакой поток не сможет читать блокированную на WRITE таблицу. Причина того, что некоторые вещи выполняются быстрее под LOCK TABLES в том, что MySQL не будет сбрасывать на диск кэш ключей для блокированных таблиц до вызова UNLOCK TABLES (обычно кэш ключей сбрасывается на диск после каждой инструкции SQL). Это ускоряет вставки, удаления и обновления на таблицах MyISAM.
Если Вы используете драйвер таблицы в MySQL, который не поддерживает транзакции, Вы должны использовать LOCK TABLES, если Вы хотите гарантировать, что никакой другой поток не обработается между SELECT и UPDATE. Пример, показанный ниже, требует LOCK TABLES, чтобы выполниться безопасно:
```
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement
                  where customer_id=some_id;
mysql> UNLOCK TABLES;
```
Без LOCK TABLES имеется возможность, что другой поток может вставлять новую строку в таблице trans между выполнением инструкций SELECT и UPDATE.

Используя инкрементные модификации (UPDATE customer SET value=value+new_value) или функцию LAST_INSERT_ID(), Вы во многих случаях можете избежать использования LOCK TABLES.

Вы можете также решать некоторые проблемы, используя функции GET_LOCK() и RELEASE_LOCK(). Эти блокировки сохранены в таблице hash на сервере и выполнены через вызовы pthread_mutex_lock() и pthread_mutex_unlock() для ускорения работы. Подробности в разделе "6.5.2 Дополнительные функции ".

Вы можете блокировать все таблицы во всех базах данных с блокировками чтения командой FLUSH TABLES WITH READ LOCK. Подробности в разделе "4.8 Синтаксис FLUSH". Это очень удобный способ получать резервные копии, если Вы имеете файловую систему, подобную Veritas, которая может делать кадры состояния.

ОБРАТИТЕ ВНИМАНИЕ: LOCK TABLES не транзакционно-безопасна и автоматически завершает любые активные транзакции перед попыткой блокировать таблицы.

9.2.3 Синтаксис `SET TRANSACTION`

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

Устанавливает уровень изоляции транзакции глобально, для целого сеанса или следующей транзакции.

Заданное по умолчанию поведение должно установить уровень изоляции для следующей (не начатой) транзакции.

Если Вы устанавливаете привилегию GLOBAL, это будет воздействовать на все новые созданные потоки. Вы будете нуждаться в привилегии PROCESS, чтобы сделать это.

Установка привилегии SESSION будет воздействовать на следующую и на все будущие транзакции.

9.3 Синтаксис `HANDLER`

HANDLER table OPEN [AS alias]
HANDLER table READ index {=|>=|<=|<} (value1, value2, ...)
                   [WHERE ...] [LIMIT ...]
HANDLER table READ index {FIRST|NEXT|PREV|LAST} [WHERE ...] [LIMIT ...]
HANDLER table READ {FIRST|NEXT} [WHERE ...] [LIMIT ...]
HANDLER table CLOSE

Команда HANDLER обеспечивает прямой доступ к интерфейсу таблиц MySQL, совершая обход SQL-оптимизатора. Таким образом, это работает быстрее, чем SELECT.

Первая форма инструкции HANDLER открывает таблицу, делая ее доступной через следующий вызов HANDLER ... READ.

Вторая форма выбирает одну (или определенное предложением LIMIT число) строку, где определенный индекс соответствует условию и определение WHERE выполнено. Если индекс состоит из нескольких частей (промежутки более, чем в несколько столбцов) значения должны быть определены в разделяемом запятыми списке.

Третья форма выбирает одну (или определенное предложением LIMIT число) строку в индексном порядке, соответствуя условиям определения WHERE запроса.

Четвертая форма (без индексной спецификации) выбирает одну (или определенное предложением LIMIT число) строку из таблицы в естественном порядке строк (как они сохранены в файле данных), соответствуя условиям определения WHERE запроса. Это быстрее, чем HANDLER table READ index, когда нужен полный просмотр таблицы.

Последняя форма закрывает таблицу, открытую с помощью вызова HANDLER ... OPEN.

HANDLER это инструкция низкого уровня, например, она не обеспечивает непротиворечивость. Вызов HANDLER ... OPEN НЕ блокирует таблицу. Так что другие потоки могут работать с таблицей и менять данные.

9.4 Полнотекстовый поиск в MySQL

Начиная с Version 3.23.23, MySQL имеет поддержку для полнотекстовой индексации и поиска. Полнотекстовые индексы в MySQL представляют собой индекс типа FULLTEXT. Индекс FULLTEXT может быть создан из столбцов VARCHAR и TEXT в вызове CREATE TABLE или добавлен позже через инструкции ALTER TABLE или CREATE INDEX. Для больших наборов данных, добавление индекса FULLTEXT через ALTER TABLE (или CREATE INDEX) намного быстрее, чем вставка строк в пустую таблицу с индексом.

Поиск выполняется с помощью функции MATCH.

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles VALUES
    -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'),
    -> (0,'How To Use MySQL Efficiently', 'After you went through a ...'),
    -> (0,'Optimizing MySQL','In this tutorial we will show how to ...'),
    -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalize ...'),
    -> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'),
    -> (0,'MySQL Security', 'When configured properly, MySQL could be ...');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles WHERE MATCH (title,body)
                    AGAINST ('database');
+----+-------------------+---------------------------------------------+
| id | title             | body                                        |
+----+-------------------+---------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison we ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase Management ...     |
+----+-------------------+---------------------------------------------+
2 rows in set (0.00 sec)

Функция MATCH соответствует запросу естественного языка для текстовой совокупности AGAINST, которая является просто набором столбцов, покрытых индексом FULLTEXT). Для каждой строки в таблице это возвращает релевантность: меру подобия между текстом в этой строке (в столбцах, которые являются частью совокупности) и запросом. Когда это используется в предложении WHERE (см. пример выше) возвращенные строки автоматически сортируются с уменьшением релевантности. Релевантность представлена неотрицательным числом с плавающей запятой. Нулевая релевантность означает, что нет никакого подобия.

Вышеупомянутое представляет собой базисный пример использования функции MATCH. Строки будут возвращены с уменьшением релевантности.

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.64840710366884 |
|  2 |                                       0 |
|  3 |                        0.66266459031789 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
5 rows in set (0.00 sec)

Этот пример показывает, как найти релевантность. Поскольку предложения WHERE или ORDER BY не присутствуют в запросе, возвращенные строки не упорядочиваются.

mysql> SELECT id, body, MATCH (title,body) AGAINST (
    -> 'Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-----------------------------------------------+-----------------+
| id | body                                          | score           |
+----+-----------------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. Normalize ... | 1.5055546709332 |
|  6 | When configured properly, MySQL could be ...  |   1.31140957288 |
+----+-----------------------------------------------+-----------------+
2 rows in set (0.00 sec)

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

MySQL использует очень простой синтаксический анализатор, чтобы расчленить текст на слова. Слово является любой последовательностью символов, чисел, знаков ' и _. Любое слово, которое присутствует в списке stopword или слишком короткое (3 символа или меньше), игнорируется.

Каждое правильное слово в совокупности и в запросе взвешивается, согласно значению в запросе или совокупности. Этим путем слово, которое присутствует во многих строках, будет иметь более низкий вес (и может даже иметь нулевой вес) потому, что оно имеет более низкое семантическое значение в этой специфической совокупности. Иначе, если слово редко, оно получит более высокий вес. Веса слов затем будут сложены, чтобы вычислить релевантность.

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

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

Поиск слова MySQL не производит никаких результатов в вышеупомянутом примере. Слово MySQL присутствует больше, чем в половине строк, и обрабатывается как stopword (то есть с семантическим значением, равным нулю).

Слово, которое соответствует половине строк в таблице, менее вероятно определяет релевантные документы. Фактически, наиболее вероятно, что поиск по нему найдет множество несоответствующих документов. Все мы знаем, что это случается очень часто, когда мы пробуем что-то поискать в Internet... Таким строкам были назначены низкие семантические значения в этом специфическом наборе данных.

9.4.1 Полнотекстовые ограничения

Все параметры для функции MATCH должны быть столбцами из той же самой таблицы, которая является частью того же самого индекса.
Параметром AGAINST должна быть строка-константа.

9.4.2 Подстройка полнотекстового поиска MySQL

Обратите внимание, что поиск был тщательно настроен для самой лучшей эффективности. Изменение заданного по умолчанию поведения будет, в большинстве случаев, делать результаты поиска хуже. Не изменяйте исходники MySQL, если Вы не знаете точно, что Вы делаете!

Минимальная длина слова, которое будет индексировано определена в файле myisam/ftdefs.h строкой
```
#define MIN_WORD_LEN 4
```
Измените это на значение, которое Вы предпочитаете, перекомпилируйте MySQL и пересоздайте индексы FULLTEXT.
Список stopword определен в файле myisam/ft_static.c. Поменяйте его по своему вкусу, пересоберите MySQL и пересоздайте индексы FULLTEXT.
Порог в 50% задан в соответствии со специфической выбранной схемой надбавки. Чтобы отключить это, измените следующую строку в файле myisam/ftdefs.h:
```
#define GWS_IN_USE GWS_PROB
```
на
```
#define GWS_IN_USE GWS_FREQ
```
После этого пересоберите MySQL. Индексы в этом случае пересоздавать не надо.

9.4.3 Новые свойства в полнотекстовом поиске в MySQL 4.0

Этот раздел включает список свойств, которые уже реализованы в версии 4.0. Здесь также описано, что еще планируется сделать.

REPAIR TABLE и ALTER TABLE работают с индексами FULLTEXT, а OPTIMIZE TABLE с индексами FULLTEXT теперь работает в 100 раз быстрее.
MATCH ... AGAINST поддерживает следующие boolean operators:
- +слово означает, что слово должно присутствовать в каждой возвращенной строке.
- -слово означает, что слово не должно присутствовать в каждой возвращенной строке.
- < и > могут использоваться, чтобы уменьшить и увеличить вес слова в запросе.
- ~ может использоваться, чтобы назначить отрицательный вес слову.
- * является оператором усечения.
Булев поиск использует более упрощенный путь вычисления релевантности, который не имеет порога 50%.
Поиски теперь работают в 2 раза быстрее из-за оптимизированного алгоритма.
Утилита ft_dump добавлена для индексных операторов низкого уровня FULLTEXT (запросы, дампы, статистика).

9.9.4 Что еще надо сделать в полнотекстовом поиске

Ускорить все операции с индексами FULLTEXT.
Поддержка скобок () в булевом поиске.
Поиск фраз, операторы близости.
Булев поиск может работать без индекса FULLTEXT (но очень медленно).
Поддержка для "always-index words". Это такие строки, которые пользователь определяет как слова, например, "C++", "AS/400", "TCP/IP" и т.д.
Поддержка для поиска в таблицах типа MERGE.
Поддержка для многобайтных наборов символов.
Сделать список stopword зависимым от языка данных в таблице.
Происхождение (зависимое от языка данных, конечно).
Универсальный обработчик пользовательских UDF (?).
Сделать модель более гибкой (добавляя некоторые корректируемые параметры для FULLTEXT в вызов CREATE/ALTER TABLE).

Партнёры:

Хостинг:

Закладки на сайте
Проследить за страницей

Created 1996-2024 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру