Я работаю с Microsoft SQL Server уже много лет, но только недавно начал использовать MySQL с моими веб-приложениями, и я жажду знаний.
Чтобы продолжить длинную строку "скрытых функций" , я хотел бы знать любые скрытые или удобные функции MySQL, которые, надеюсь, улучшат мои знания об этом база данных с открытым исходным кодом.
С тех пор, как вы поставили щедрость, я поделюсь своими секретами... [/p >
В общем, все SQL-запросы, которые я настроил сегодня, требуют использования подзапросов. Выйдя из мира баз данных Oracle, все, что я считал само собой разумеющимся, не менялось с MySQL. И мое чтение по настройке MySQL заставляет меня прийти к выводу, что MySQL стоит за Oracle с точки зрения оптимизации запросов.
В то время как простые запросы, требуемые для большинства приложений B2C, могут хорошо работать для MySQL, большая часть совокупного типа отчетов, необходимых для отчета Intelligence Reporting, по-видимому, требует справедливого планирования и повторной организации SQL-запросов для руководства MySQL для выполнения они быстрее.
max_connections - количество одновременных подключений. Значение по умолчанию - 100 соединений (151 с 5.0) - очень мало.
Примечание:
соединения занимают память, и ваша ОС не сможет обрабатывать множество подключений.
Бинарные файлы MySQL для Linux/x86 позволяют иметь до 4096 одновременных подключений, но само скомпилированные двоичные файлы часто имеют меньший предел.
Установите table_cache в соответствии с количеством открытых таблиц и одновременных подключений. Посмотрите на значение open_tables, и если он быстро растет, вам нужно будет увеличить его размер.
Примечание:
Для двух предыдущих параметров может потребоваться много открытых файлов. 20 + max_connections + table_cache * 2 - хорошая оценка того, что вам нужно. MySQL в Linux имеет параметр open_file_limit, установите этот предел.
Если у вас есть сложные запросы, то sort_buffer_size и tmp_table_size, вероятно, будут очень важны. Значения будут зависеть от сложности запросов и доступных ресурсов, но рекомендуется 4Mb и 32Mb, начиная с начальной точки.
Примечание. Это значения "для каждого соединения", среди read_buffer_size, read_rnd_buffer_size и некоторые другие, что означает, что это значение может потребоваться для каждого соединения. Поэтому при настройке этих параметров учитывайте свою нагрузку и доступный ресурс. Например sort_buffer_size выделяется только в том случае, если MySQL должен выполнить сортировку. Примечание: будьте осторожны, чтобы не хватить памяти.
Если у вас установлено много подключений (т.е. веб-сайт без постоянных подключений), вы можете повысить производительность, установив thread_cache_size в ненулевое значение. 16 - хорошее значение для начала. Увеличьте значение, пока ваши threads_created не будут расти очень быстро.
В таблице может быть только один столбец AUTO_INCREMENT, он должен быть проиндексирован и не может иметь значение DEFAULT
KEY обычно является синонимом INDEX. Ключевой атрибут PRIMARY KEY также может быть указан как KEY, если задан в определении столбца. Это было реализовано для совместимости с другими системами баз данных.
ПЕРВИЧНЫЙ КЛЮЧ - уникальный индекс, где все ключевые столбцы должны быть определены как NOT NULL
Если индекс PRIMARY KEY или UNIQUE состоит только из одного столбца, который имеет целочисленный тип, вы также можете обратиться к столбцу как "_rowid" в операторах SELECT.
В MySQL имя PRIMARY KEY является PRIMARY
В настоящее время только таблицы InnoDB (v5.1?) поддерживают внешние ключи.
Обычно вы создаете все индексы, которые вам нужны при создании таблиц. Любой столбец, объявленный как PRIMARY KEY, KEY, UNIQUE или INDEX, будет проиндексирован.
NULL означает "не имеет значения". Чтобы проверить NULL, вы не можете использовать операторы арифметического сравнения, такие как =, <, или < > . Вместо этого используйте операторы IS NULL и NOT NULL:
NO_AUTO_VALUE_ON_ZERO подавляет автоматический приращение для 0, так что только NULL генерирует следующий порядковый номер. Этот режим может быть полезен, если 0 хранится в столбце AUTO_INCREMENT таблицы. (Кстати, сохранение 0 - это не рекомендуемая практика.)
Чтобы изменить значение счетчика AUTO_INCREMENT для новых строк:
ALTER TABLE mytable AUTO_INCREMENT = value;
или SET INSERT_ID = значение;
Если не указано иное, значение начинается с: 1000000 или указывается так:
...) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1
Значения для столбцов TIMESTAMP преобразуются из текущего часового пояса в UTC для хранения, и от UTC к текущему часовому поясу для извлечения.
http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Для одного столбца TIMESTAMP в таблице вы можете назначить текущую временную метку как значение по умолчанию и значение автоматического обновления.
одно, что нужно учитывать при использовании одного из этих типов в предложении WHERE, лучше всего сделать WHERE datecolumn = FROM_UNIXTIME (1057941242) и не WHERE UNIX_TIMESTAMP (datecolumn) = 1057941242. выполнение последнего не будет использовать индекс в этом столбце.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
UNIX_TIMESTAMP()
FROM_UNIXTIME()
UTC_DATE()
UTC_TIME()
UTC_TIMESTAMP()
если вы конвертируете дату и время в unix timestamp в MySQL:
А затем добавьте к нему 24 часа:
И затем преобразуйте его обратно в день, когда он волшебным образом теряет час!
Вот что происходит. При преобразовании временной отметки unix в дату и время учитывается часовой пояс, и только так случилось, что между 28 и 29 октября 2006 года мы ушли с летнего времени и потеряли час.
Начиная с MySQL 4.1.3 функции CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE() и FROM_UNIXTIME() возвращают значения в текущий часовой пояс соединения, который доступен как значение системной переменной time_zone. Кроме того, UNIX_TIMESTAMP() предполагает, что его аргумент является значением даты и времени в текущем часовом поясе.
Текущий часовой пояс не влияет на значения, отображаемые такими функциями, как UTC_TIMESTAMP() или значения в столбцах DATE, TIME или DATETIME.
ПРИМЕЧАНИЕ. ON UPDATE ONLY обновляет DateTime, если поле было изменено. Если UPDATE не приводит к изменению полей, значение DateTime не обновляется!
Кроме того, первый TIMESTAMP всегда AUTOUPDATE по умолчанию, даже если не указан
При работе с датами я почти всегда провожу Julian Date, потому что Data math - это просто вопрос добавления или вычитания целых чисел, а также Seconds from Midnight по той же причине. Редко мне нужно время для восстановления более тонкой детализации, чем секунды.
Оба они могут быть сохранены как целое число в 4 байта, и если пространство действительно жестко, можно объединить в UNIX-время (секунды с эпохи 1/1/1970) как целое число без знака, которое будет хорошим до примерно 2106:
'secs в 24Hrs = 86400
'Подпись Integer max val = 2,147,483,647 - может содержать 68 лет секунд
'Unsigned Integer max val = 4 294 967 295 - может содержать 136 лет секунд
В MySQL 4.1 был введен двоичный протокол, который позволяет отправлять нестрочные значения данных и возвращается в собственном формате без преобразования в и из строкового формата. (Очень полезно)
Кроме того, mysql_real_query() быстрее, чем mysql_query(), потому что он не вызывает strlen() для работы с строкой оператора.
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html Бинарный протокол поддерживает подготовленные на стороне сервера операторы и позволяет передавать значения данных в собственном формате. Бинарный протокол подвергся довольно незначительной ревизии во время предыдущих выпусков MySQL 4.1.
Вы можете использовать макрос IS_NUM(), чтобы проверить, имеет ли поле числовой тип. Передайте значение типа в значение IS_NUM(), и оно будет иметь значение TRUE, если поле является числовым:
Следует отметить, что двоичные данные могут быть отправлены внутри обычного запроса, если вы избегаете его и помните, что MySQL требует только того, что обратная косая черта и символ кавычки будут экранированы. Таким образом, это действительно простой способ для INSERT более коротких двоичных строк, таких как зашифрованные/соленые пароли, например.
http://www.experts-exchange.com/Database/MySQL/Q_22967482.html
http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2
ПОДАВЛЕНИЕ ГРАНТА. to slave_user ИДЕНТИФИЦИРОВАН ПО "slave_password"
#Master Binary Logging Config STATEMENT causes replication
to be statement-based - default
log-bin=Mike
binlog-format=STATEMENT
server-id=1
max_binlog_size = 10M
expire_logs_days = 120
#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2
Файл двоичного журнала должен читать:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html
Вы можете удалить все файлы двоичного журнала с помощью оператора RESET MASTER или подмножества из них с помощью PURGE MASTER
- result-file = binlog.txt TrustedFriend-bin.000030
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx
http://souptonuts.sourceforge.net/readme_mysql.htm
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2
http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/
Следует отметить, что в смешанной таблице с CHAR и VARCHAR mySQL изменит CHAR на VARCHAR
RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, ПЕРВИЧНЫЙ КЛЮЧ (RecNum)
MySQL всегда представляет даты с первым годом, в соответствии со стандартными спецификациями SQL и ISO 8601.
Извлечение некоторых функций MySQl приведет к уменьшению файлов данных и более быстрый доступ. Например:
- datadir укажет каталог данных и
- skip-innodb отключит опцию inno и сохранит вас 10-20M
Подробнее здесь http://dev.mysql.com/tech-resources/articles/mysql-c-api.html
Скачать главу 7 - бесплатно
InnoDB является транзакционным, но есть накладные расходы, связанные с производительностью. Я нашел таблицы MyISAM достаточными для 90% моих проектов. Таблицы с безопасными транзакциями (MyISAM) обладают рядом преимуществ, все из которых происходят из-за того, что:
нет накладных расходов на транзакцию:
Гораздо быстрее
Более низкие требования к дисковому пространству
Меньшая память, необходимая для выполнения обновлений
Каждая таблица MyISAM хранится на диске в трех файлах. Файлы имеют имена, которые начинаются с имени таблицы и имеют расширение для указания типа файла. Файл .frm сохраняет формат таблицы. Файл данных имеет расширение .MYD(MYData). Файл индекса имеет расширение .MYI(MYIndex).
Эти файлы могут быть скопированы в хранилище без изменений, не используя функцию резервного копирования MySQL Administrators, которая требует много времени (так же как и восстановление)
Трюк состоит из копии этих файлов, затем DROP table. Когда вы помещаете файлы обратно MySQl распознает их и обновит отслеживание таблиц.
Если вы должны выполнить резервное копирование/восстановление,
Восстановление резервной копии или импорт из существующего файла дампа может занять много времени в зависимости от количества индексов и первичных ключей, которые у вас есть в каждой таблице. Вы можете значительно ускорить этот процесс, изменив исходный файл дампа, окружив его следующим:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
Чтобы значительно увеличить скорость перезагрузки, добавьте команду SQL SET AUTOCOMMIT = 0; в начале файла дампа и добавьте COMMIT; до конца.
По умолчанию автокоммит включен, что означает, что каждая команда вставки в файл дампа будет рассматриваться как отдельная транзакция и записан на диск до того, как будет запущен следующий. Если вы не добавите эти команды, перезагрузка большой базы данных в InnoDB может занять много часов...
Максимальный размер строки в таблице MySQL составляет 65 535 байт.
Эффективная максимальная длина VARCHAR в MySQL 5.0.3 и on = максимальный размер строки (65 535 байт)
Значения VARCHAR не заполняются при их сохранении. Промежуточные пробелы сохраняются, когда значения сохраняются и извлекаются в соответствии со стандартным SQL.
CHAR и значения VARCHAR в MySQL сравниваются без учета конечных пробелов.
Использование CHAR только ускорит ваш доступ, если вся запись фиксированного размера. То есть, если вы используете какой-либо объект с переменными размерами, вы можете также сделать их все переменными. Вы не получаете скорость, используя CHAR в таблице, которая также содержит VARCHAR.
Предел VARCHAR из 255 символов был увеличен до 65535 символов с MySQL 5.0.3
Полнотекстовые поисковые запросы поддерживаются только для таблиц MyISAM.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Столбцы BLOB не имеют набора символов, а сортировка и сравнение основаны на числовые значения байтов в значениях столбцов
Если строгий режим SQL не включен и вы назначаете значение столбцу BLOB или TEXT, превышает максимальную длину столбца, значение усекается, чтобы соответствовать, и генерируется предупреждение.
проверить строгий режим: SELECT @@global.sql_mode;
отключить строгий режим:
SET @@global.sql_mode = '';
SET @@global.sql_mode = 'MYSQL40'
или удалить: SQL-режим = "STRICT_TRANS_TABLES,...
ПОКАЖИТЕ КОЛОНКИ ОТ mytable
SELECT max (namecount) AS virtualcolumn FROM mytable ORDER BY virtualcolumn
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id()
получает PK последней строки, вставленной в текущий поток max (pkcolname), получает последний PK в целом.
Примечание: если таблица пуста max (pkcolname) возвращает 1 mysql_insert_id() преобразует возвращаемый тип собственной функции API MySQL C mysql_insert_id() в тип long (с именем int в PHP).
Если ваш столбец AUTO_INCREMENT имеет тип столбца BIGINT, значение, возвращаемое mysql_insert_id() будет некорректным. Вместо этого в SQL-запросе используйте внутреннюю функцию MySQL SQL LAST_INSERT_ID().
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Просто обратите внимание, что когда вы пытаетесь вставить данные в таблицу и получите ошибку:
Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’
используя что-то вроде
INSERT INTO table (this, that) VALUES ($this, $that)
потому что у вас нет апострофов вокруг значений, которые вы пытаетесь вставить в таблицу. Поэтому вы должны изменить свой код на:
INSERT INTO table (this, that) VALUES ('$this', '$that')
напоминание, что `` используются для определения полей, баз данных или таблиц MySQL, а не значений;)
Потерянное соединение с сервером во время запроса:
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.1/en/show-variables.html
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
http://dev.mysql.com/doc/refman/5.1/en/error-log.html
http://www.artfulsoftware.com/infotree/queries.php?&bw=1313
Ну, этого должно быть достаточно, чтобы заработать бонус, который я бы подумал... Плоды многих часов и многих проектов с большой бесплатной базой данных. Я разрабатываю серверы данных приложений на платформах Windows, в основном с MySQL. Худший беспорядок, который я должен был выправить, был
Конечный кошмар базы данных устаревшего MySQL
Это потребовало серии приложений для обработки таблиц во что-то полезное, используя многие из трюков, упомянутых здесь.
Если вы нашли это удивительно полезным, выразить свою благодарность, проголосовав за него.
Также ознакомьтесь с моими другими статьями и документами по адресу: www.coastrd.com
Одна из не столь скрытых особенностей MySQL заключается в том, что она не очень хороша в отношении соответствия SQL, ну, а не ошибок, но, более gotchas...: -)
Команда, чтобы узнать, какие таблицы находятся в кеше:
mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | a | 3 | 0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)
Команда, чтобы узнать, кто что делает:
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 для inet_ntoa() и inet_aton(). Это делает обработку IP-адресов в таблицах очень простой (по крайней мере, пока они являются только адресами IPv4!)
Я люблю on duplicate key (AKA upsert, merge) для всех типов счетчиков, созданных лениво:
insert into occurances(word,count) values('foo',1),('bar',1)
on duplicate key cnt=cnt+1
Вы можете вставлять много строк в один запрос и сразу обрабатывать повторяющийся индекс для каждой из строк.
Опять же - не совсем скрытые функции, но действительно удобные:
Функция
Легко захватить 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
Функция /Gotcha: нет необходимости включать неагрегированные выражения в список GROUP BY
Большинство RDBMS-приложений применяют SQL-совместимую группу GROUP BY, которая требует, чтобы все неагрегированные выражения в списке SELECT отображались в GROUP BY. В этих RDBMS-es это утверждение:
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. В этих RDBMS-классах вы должны либо изменить список 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.Code. Таким образом, в стране должно существовать ровно одно значение. Контракт для каждого отдельного Country.Code. Если вы это осознаете, то понимаете, что нет смысла собирать его (есть только одно значение, правильно) и не группировать его (так как это не сделает результат более уникальным, поскольку вы уже группируете pk)
В любом случае 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 будет содержать не-смысл (т.е. из всех процентных значений в языках, одно из доступных значений для процента будет выбрано наугад или, по крайней мере, вне вашего контроля.
Смотрите: Отладка группы мифами
Команда "пейджер" в клиенте
Если у вас есть, скажем, 10 000 строк в вашем результате и вы хотите их просмотреть (это предполагает, что доступны команды "меньше" и "тройник", что обычно происходит в 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;
Будет удобно записывать файл.
Некоторые вещи, которые могут показаться вам интересными:
<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
Не скрытая функция, но полезная тем не менее: http://mtop.sourceforge.net/
Вот некоторые из моих советов - я писал о них в своем блоге (Ссылка)
Если вы собираетесь работать с большими и/или высокопроизводительными транзакционными базами данных InnoDb, изучите и поймите "SHOW INNODB STATUS" Блог о производительности Mysql, он станет вашим другом.
Если вы используете cmdline Mysq, вы можете взаимодействовать с командной строкой (на машинах Linux - не уверен, есть ли эквивалентный эффект для Windows), используя восклицательный знак/восклицательный знак. Например:
\! cat file1.sql
отобразит код файла file1.sql. Чтобы сохранить выражение и запрос к файлу, используйте средство tee
\T filename
чтобы отключить это использование \t
Наконец, чтобы запустить script, который вы уже сохранили, используйте "source filename". Конечно, обычной альтернативой является указание имени script при запуске 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)
Я не думаю, что это зависит от 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)
mysqlsla - один из наиболее часто используемых инструментов анализа журналов медленных запросов. Вы можете увидеть 10 самых популярных запросов, так как вы последний раз выложили медленные журналы запросов. Он также может сообщать вам количество раз, когда был запущен BAD-запрос, и сколько общего времени на сервере.
Фактически документирован, но очень раздражает: автоматические преобразования для неправильных дат и другого неправильного ввода.
До MySQL 5.0.2 MySQL прощает незаконные или неправильные значения данных и принуждает их к правовым значениям для ввода данных. В MySQL 5.0.2 и выше это остается по умолчанию, но вы можете изменить режим SQL сервера, чтобы выбрать более традиционное обращение с плохими значениями, чтобы сервер отклонил их и прервал утверждение, в котором они происходят.
Что касается дат: иногда вам будет "повезло", когда MySQL не будет корректировать входные данные до ближайших действительных дат, но вместо этого сохраняет его как 0000-00-00, который по определению недействителен. Однако даже тогда вы, возможно, захотели, чтобы MySQL терпел неудачу, вместо того, чтобы молча хранить это значение для вас.
Встроенный SQL Profiler.
InnoDB по умолчанию сохраняет все таблицы в одном глобальном табличном пространстве, которое никогда не будет сокращаться.
Вы можете использовать innodb_file_per_table, который поместит каждую таблицу в отдельное табличное пространство, которое будет удалено при удалении таблицы или базы данных.
Запланируйте это заранее, так как вы должны сбросить и восстановить базу данных, чтобы освободить место в противном случае.
Если вы вставляете в столбце datetime значение пустой строки "", MySQL сохранит значение 00/00/0000 00:00:00. В отличие от Oracle, который сохранит нулевое значение.
Во время моих тестов с большими наборами данных и полями 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'