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

Question

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

Я работаю с Microsoft SQL Server уже много лет, но только недавно начал использовать MySQL с моими веб-приложениями, и я жажду знаний.

Чтобы продолжить с длинной строкой вопросов о "скрытых возможностях", я хотел бы узнать о любых скрытых или удобных функциях MySQL, которые, я надеюсь, улучшат мои знания об этой базе данных с открытым исходным кодом.

101

mysql database hidden-features

Источник

user383 15 дек '08 в 16:09

20 ответов

Решение

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

22

Источник

user42083 15 дек '08 в 16:21

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

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

(Из блога производительности MySQL)

21

Источник

user10585 15 дек '08 в 16:30

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

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

15

Источник

user5445 15 дек '08 в 16:22

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

11

Источник

user6782 22 дек '08 в 10:20

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

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

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

11

Источник

user27009 21 июн '09 в 20:35

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

Особенность

Легко захватить 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

Feature / Gotcha: нет необходимости включать неагрегированные выражения в список GROUP BY

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

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 будет содержать бессмысленный смысл (то есть, для всех языков в процентах, одно из доступных значений для процента будет выбрано случайным образом или, по крайней мере, вне вашего контроля.

Смотрите: Группа Разоблачения По Мифам

10

Источник

22 июн '09 в 11:31

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

Если у вас есть, скажем, 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;

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

7

Источник

user13724 22 июн '09 в 06:14

Некоторые вещи, которые вы можете найти интересными:

<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

6

Источник

user69524 27 фев '09 в 20:52

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

4

Источник

user67900 06 мар '09 в 00:54

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

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

3

Источник

user14944 03 мар '09 в 06:19

Я не думаю, что это специфично для 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)

3

Источник

user650492 21 сен '11 в 12:14

Если вы собираетесь работать с базами данных InnoDb с большими и / или большими транзакциями, изучите и поймите "SHOW INNODB STATUS" Блог производительности Mysql, он станет вашим другом.

3

Источник

user67935 03 мар '09 в 06:45

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

\! cat file1.sql

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

\T filename

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

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

    mysql -u root -p < case1.sql

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

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

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

Упорядочить по полю (sort_field,1,4,3,2)

Выяснили это здесь Order by day_of_week в MySQL учтивость пользователя gms8994

3

Источник

user123595 20 июн '09 в 13:18

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

До MySQL 5.0.2 MySQL прощает недопустимые или неправильные значения данных и приводит их к допустимым значениям для ввода данных. В MySQL 5.0.2 и выше это остается поведением по умолчанию, но вы можете изменить режим SQL сервера, чтобы выбрать более традиционную обработку неверных значений, чтобы сервер отклонял их и прерывал оператор, в котором они встречаются.

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

2

Источник

user84237 12 июл '09 в 12:20

Встроенный SQL Profiler.

2

Источник

user244297 16 мар '11 в 14:57

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

2

Источник

22 июн '09 в 11:53

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

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

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

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

1

Источник

user84760 05 июн '09 в 08:08

Во время моих тестов с большими наборами данных и полями 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'

1

Источник

user550178 08 окт '11 в 17:15

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

1

Источник

user454841 01 авг '11 в 12:04

Другие вопросы по тегам mysql database hidden-features

user18749 01 мар '09 в 23:32 2009-03-01 23:32 · Accepted Answer · 2009-03-01 23:32

Так как вы положили награду, я поделюсь своими с трудом завоеванными секретами...

В общем, все SQL-запросы, которые я сегодня настраивал, требовали использования подзапросов. Исходя из мира баз данных Oracle, вещи, которые я воспринимал как должное, не работали так же с MySQL. И мое чтение по настройке MySQL заставляет меня заключить, что MySQL отстает от Oracle с точки зрения оптимизации запросов.

Хотя простые запросы, требуемые для большинства приложений B2C, могут хорошо работать для MySQL, большинство запросов сводного типа, необходимых для аналитической отчетности, по-видимому, требуют тщательного планирования и реорганизации запросов 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, вероятно, будут очень важны. Значения будут зависеть от сложности запроса и доступных ресурсов, но 4Мб и 32Мб, соответственно, рекомендуются для начала.

Примечание. Это значения для каждого соединения, среди которых 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, если он задан в определении столбца. Это было реализовано для совместимости с другими системами баз данных.

PRIMARY KEY - это уникальный индекс, где все ключевые столбцы должны быть определены как NOT NULL.

Если индекс PRIMARY KEY или UNIQUE состоит только из одного столбца с целочисленным типом, вы также можете ссылаться на этот столбец как "_rowid" в инструкциях SELECT.

В MySQL имя ПЕРВИЧНОГО КЛЮЧА является ПЕРВИЧНЫМ

В настоящее время только таблицы InnoDB (v5.1?) Поддерживают внешние ключи.

Обычно вы создаете все индексы, которые вам нужны при создании таблиц. Любой столбец, объявленный как PRIMARY KEY, KEY, UNIQUE или INDEX, будет проиндексирован.

NULL означает "не имеющий значения". Чтобы проверить NULL, вы не можете использовать операторы арифметического сравнения, такие как =, <или <>. Вместо этого используйте операторы IS NULL и IS NOT NULL:

NO_AUTO_VALUE_ON_ZERO подавляет автоматическое увеличение на 0, так что только NULL генерирует следующий порядковый номер. Этот режим может быть полезен, если в столбце AUTO_INCREMENT таблицы хранится 0. (Кстати, хранение 0 не рекомендуется).

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

ALTER TABLE mytable AUTO_INCREMENT = value;

или SET INSERT_ID = значение;

Если не указано иное, значение будет начинаться с: 1000000 или указывать его следующим образом:

...) ENGINE = MyISAM CHARSET ПО УМОЛЧАНИЮ = latin1 AUTO_INCREMENT = 1

Timestamps:

Значения для столбцов 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 в 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 ТОЛЬКО обновляет DateTime, если поле изменено. Если UPDATE не приводит к изменению полей, DateTime НЕ обновляется!

Кроме того, First TIMESTAMP по умолчанию всегда AUTOUPDATE, даже если не указан

Работая с датами, я почти всегда согласен с Юлианом Дейтом, потому что математика данных - это просто вопрос сложения или вычитания целых чисел и секунд после полуночи по той же причине. Я редко нуждаюсь во временном разрешении с более тонкой детализацией, чем секунды.

Оба они могут быть сохранены как 4-байтовое целое число, и, если пространство действительно ограничено, может быть объединено во время UNIX (секунды с эпохи 01.01.1970) как целое число без знака, которое будет работать до 2106 года, как:

сек в 24 часа = 86400

Целое число со знаком max val = 2 147 483 647 - может содержать 68 лет секунд

Целое число без знака 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(), и оно оценивается как ИСТИНА, если поле числовое:

Стоит отметить, что двоичные данные МОГУТ быть отправлены внутри обычного запроса, если вы избежите их и помните, что MySQL требует только обратной косой черты и символа кавычки. Так что это действительно простой способ ВСТАВИТЬ более короткие двоичные строки, например, зашифрованные / Соленые пароли.

Главный сервер:

http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

ГРАНТ РЕПЛИКАЦИЯ РАБА НА . для 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

Функции UDF

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, PRIMARY KEY (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, которая занимает много времени (как и восстановление)

Хитрость заключается в том, чтобы сделать копию этих файлов, а затем сбросить таблицу. Когда вы положите файлы обратно, 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 составляет 65535 байт.

Эффективная максимальная длина VARCHAR в MySQL 5.0.3 и on = максимальный размер строки (65535 байт)

Значения VARCHAR не заполняются при сохранении. Конечные пробелы сохраняются при сохранении и получении значений в соответствии со стандартным SQL.

Значения CHAR и VARCHAR в MySQL сравниваются без учета конечных пробелов.

Использование CHAR только ускорит ваш доступ, если вся запись имеет фиксированный размер. То есть, если вы используете какой-либо объект переменного размера, вы можете также сделать их все переменным размером. Вы не получаете скорости, используя CHAR в таблице, которая также содержит VARCHAR.

Начиная с MySQL 5.0.3, ограничение VARCHAR в 255 символов было увеличено до 65535 символов.

Полнотекстовый поиск поддерживается только для таблиц 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-mode="STRICT_TRANS_TABLES,...

ПОКАЗАТЬ КОЛОННЫ ОТ mytable

SELECT max(namecount) AS virtualcolumn ИЗ 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 last_insert_id()

получает вам PK последней строки, вставленной в текущий поток, max(pkcolname) возвращает вам последний PK в целом.

Примечание: если таблица пуста, max(pkcolname) возвращает 1 mysql_insert_id() преобразует тип возврата встроенной функции MySQL C API mysql_insert_id () в тип long (с именем int в PHP).

Если ваш столбец AUTO_INCREMENT имеет тип столбца BIGINT, значение, возвращаемое mysql_insert_id (), будет неверным. Вместо этого используйте внутреннюю функцию SQL MySQL LAST_INSERT_ID() в запросе SQL.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

Просто обратите внимание, что когда вы пытаетесь вставить данные в таблицу и получаете сообщение об ошибке:

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