Скрытые возможности MySQL

Одна из не столь скрытых особенностей MySQL заключается в том, что он не очень хорош в том, чтобы быть совместимым с SQL, ну, на самом деле не ошибками, но, более ошибки ...

Команда, чтобы узнать, кто чем занимается:

mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host            | db   | Command | Time | State                            | Info             |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
|  1 | root        | localhost:32893 | NULL | Sleep   |    0 |                                  | NULL             |
|  5 | system user |                 | NULL | Connect |   98 | Waiting for master to send event | NULL             |
|  6 | system user |                 | NULL | Connect | 5018 | Reading event from the relay log | NULL             |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec) 

И вы можете убить процесс с помощью:

mysql>kill 5 

Команда, чтобы узнать, какие таблицы в данный момент находятся в кеше:

mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | a     |      3 |           0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)

(От Блог о производительности MySQL)

Мне особенно нравится встроенная поддержка MySQL для inet_ntoa() и inet_aton(). Это упрощает обработку IP-адресов в таблицах (по крайней мере, если они являются только адресами IPv4!)

Некоторые вещи могут вас заинтересовать:

<query>\G -- \G in the CLI instead of the ; will show one column per row
explain <query>; -- this will show the execution plan for the query

Вот несколько моих советов - я писал о них в своем блоге (Ссылка на сайт)

1. Вам не нужно использовать знак «@» при объявлении переменных.
2. Вы должны использовать разделитель (по умолчанию ';'), чтобы обозначить конец оператора - Ссылка на сайт
3. Если вы пытаетесь переместить данные между MS-SQL 2005 и mySQL, есть несколько препятствий, которые нужно пройти - Ссылка на сайт
4. Выполнение совпадений с учетом регистра в mySQL - связь

Если вы собираетесь работать с большими и / или высокотранзакционными базами данных InnoDb, выучите и поймите "ПОКАЗАТЬ СТАТУС INNODB" Блог о производительности MySQL, он станет вашим другом.

Не скрытая функция, но тем не менее полезная: http://mtop.sourceforge.net/

InnoDB по умолчанию хранит все таблицы в одном глобальном табличном пространстве, которое будет никогда не сжимайся.

Вы можете использовать innodb_file_per_table, который поместит каждую таблицу в отдельное табличное пространство, которое будет удалено при удалении таблицы или базы данных.

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

Использование табличных пространств для таблиц

Если вы используете cmdline Mysq, вы можете взаимодействовать с командной строкой (на машинах Linux - не уверен, есть ли эквивалентный эффект в Windows), используя крик / восклицательный знак. Например:

\! cat file1.sql

отобразит код для file1.sql. Чтобы сохранить выписку и запрос в файл, используйте тройник

\T filename

чтобы выключить это, используйте \ t

Наконец, чтобы запустить сценарий, который вы уже сохранили, используйте «имя файла источника». Конечно, нормальной альтернативой является указание имени скрипта при запуске mysql из командной строки:

    mysql -u root -p < case1.sql

Надеюсь, это кому-то пригодится!

Обновлено: только что вспомнил еще один - при вызове mysql из командной строки вы можете использовать переключатель -t, чтобы вывод был в формате таблицы - настоящее благо для некоторых запросов (хотя, конечно, завершение запросов с помощью \ G, как упоминалось в другом месте, также полезно в этом отношении). Еще много чего о различных переключателях Инструмент командной строки

Только что нашел изящный способ изменить порядок сортировки (обычно используйте Case ...) Если вы хотите изменить порядок сортировки (возможно, отсортировать по 1, 4, 3, 2 вместо 1, 2, 3,4), вы можете использовать функцию поля в предложении Order by. Например

Сортировать по полю (sort_field, 1,4,3,2)

• Нашел это здесь Заказ по day_of_week в MySQL любезно предоставлено пользователем gms8994

Мне нравится on duplicate key (AKA upsert, merge) за все виды счетчиков, созданных лениво:

insert into occurances(word,count) values('foo',1),('bar',1) 
  on duplicate key cnt=cnt+1

Вы можете вставить много строк в один запрос и сразу обработать повторяющийся индекс для каждой строки.

Команда "пейджер" в клиенте

Если у вас есть, скажем, 10 000 строк в вашем результате и вы хотите их просмотреть (предполагается, что доступны команды «less» и «tee», что обычно имеет место в Linux; в Windows YMMV.)

pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;

И вы получите их в программе просмотра файлов с меньшим количеством страниц, чтобы вы могли легко пролистывать их, выполнять поиск и т. д.

Также

pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;

Будет удобно записывать в файл.

Опять же - не совсем скрытые функции, но очень удобные:

Характерная черта

Легко захватить DDL:

SHOW CREATE TABLE CountryLanguage

выход:

CountryLanguage | CREATE TABLE countrylanguage (
  CountryCode char(3) NOT NULL DEFAULT '',
  Language char(30) NOT NULL DEFAULT '',
  IsOfficial enum('T','F') NOT NULL DEFAULT 'F',
  Percentage float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (CountryCode,Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Функция: агрегатная функция GROUP_CONCAT () Создает объединенную строку своих аргументов для каждой детали и агрегирует, объединяя их для каждой группы.

Пример 1: простой

SELECT   CountryCode
,        GROUP_CONCAT(Language) AS List
FROM     CountryLanguage
GROUP BY CountryCode             

Выход:

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | Dutch,English,Papiamento,Spanish   |
. ...         . ...                                .
| ZWE         | English,Ndebele,Nyanja,Shona       |
+-------------+------------------------------------+

Пример 2: несколько аргументов

SELECT   CountryCode
,        GROUP_CONCAT(
             Language
,            IF(IsOfficial='T', ' (Official)', '')
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Выход:

+-------------+---------------------------------------------+
| CountryCode | List                                        |
+-------------+---------------------------------------------+
| ABW         | Dutch (Official),English,Papiamento,Spanish |
. ...         . ...                                         .
| ZWE         | English (Official),Ndebele,Nyanja,Shona     |
+-------------+---------------------------------------------+

Пример 3: Использование настраиваемого разделителя

SELECT   CountryCode
,        GROUP_CONCAT(Language SEPARATOR ' and ') AS List
FROM     CountryLanguage
GROUP BY CountryCode

Выход:

+-------------+----------------------------------------------+
| CountryCode | List                                         |
+-------------+----------------------------------------------+
| ABW         | Dutch and English and Papiamento and Spanish |
. ...         . ...                                          .
| ZWE         | English and Ndebele and Nyanja and Shona     |
+-------------+----------------------------------------------+

Пример 4: Управление порядком элементов списка

SELECT   CountryCode
,        GROUP_CONCAT(
         Language
         ORDER BY CASE IsOfficial WHEN 'T' THEN 1 ELSE 2 END DESC
         ,        Language
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Выход:

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | English,Papiamento,Spanish,Dutch,  |
. ...         . ...                                .
| ZWE         | Ndebele,Nyanja,Shona,English       |
+-------------+------------------------------------+

Функция: COUNT (DISTINCT) с несколькими выражениями

Вы можете использовать несколько выражений в выражении COUNT (DISTINCT ...) для подсчета количества комбинаций.

SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage

Функция / Подсказка: нет необходимости включать неагрегированные выражения в список GROUP BY.

Большинство реляционных СУБД применяют совместимую с SQL92 GROUP BY, которая требует, чтобы все неагрегированные выражения в списке SELECT отображались в GROUP BY. В этих СУБД этот оператор:

SELECT     Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

недействителен, потому что список SELECT содержит неагрегированный столбец Country.Continent, которого нет в списке GROUP BY. В этих СУБД вы должны либо изменить список GROUP BY, чтобы читать

GROUP BY   Country.Code, Country.Continent

или вы должны добавить какой-нибудь бессмысленный агрегат в Country.Continent, например

SELECT     Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)

Дело в том, что по логике нет ничего, что требовало бы объединения Country.Continent. Смотрите, Country.Code - это первичный ключ таблицы Country. Country.Continent также является столбцом из таблицы Country и, таким образом, по определению функционально зависит от первичного ключа Country.Code. Таким образом, в Country.Continent должно существовать ровно одно значение для каждой отдельной Country.Code. Если вы это понимаете, то понимаете, что нет смысла ни агрегировать его (есть только одно значение, верно), ни группировать по нему (так как это не сделает результат более уникальным, поскольку вы уже группируете по ПК)

В любом случае - MySQL позволяет включать неагрегированные столбцы в список SELECT, не требуя, чтобы вы также добавляли их в предложение GROUP BY.

Проблема в том, что MySQL не защищает вас в случае, если вы используете неагрегированный столбец. Итак, такой запрос:

SELECT     Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

Будет выполнено без претензий, но столбец CountryLanguage.Percentage будет содержать бессмысленные значения (то есть, из процентов всех языков одно из доступных значений процента будет выбрано случайным образом или, по крайней мере, вне вашего контроля.

См .: Развенчание группы мифами

mysqlsla - один из наиболее часто используемых инструментов анализа журнала медленных запросов. Вы можете увидеть 10 самых плохих запросов с тех пор, как вы последний раз выкатывали журналы медленных запросов. Он также может сказать вам, сколько раз был запущен BAD-запрос и сколько общего времени он занял на сервере.

Собственно задокументированный, но очень напрягает: автоматические преобразования для неверных дат и другого некорректного ввода.

Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.

Что касается дат: иногда вам «повезет», когда MySQL не настраивает ввод на близкие допустимые даты, а вместо этого сохраняет его как 0000-00-00, что по определению недействительно. Однако даже в этом случае вы, возможно, захотели бы, чтобы MySQL потерпел неудачу, вместо того, чтобы молча сохранить это значение для вас.

Встроенный Профилировщик SQL.

Если вы вставите в столбец datetime пустое строковое значение "", MySQL сохранит значение как 00/00/0000 00:00:00. В отличие от Oracle, который сохранит нулевое значение.

Я не думаю, что это специфично для MySQL, но мне интересно:

Вместо того, чтобы писать

WHERE (x.id > y.id) OR (x.id = y.id AND x.f2 > y.f2) 

Ты можешь просто написать

WHERE (x.id, x.f2) > (y.id, y.f2)

Во время моих тестов с большими наборами данных и полями DATETIME всегда медленнее выполнять этот запрос:

SELECT * FROM mytable
WHERE date(date_colum) BETWEEN '2011-01-01' AND ''2011-03-03';

Чем этот подход:

SELECT * FROM mytable
WHERE date_column BETWEEN '2011-01-01 00:00:00' AND '2011-03-03 23:59:59'