MySQL的隐藏功能

Question

已锁定 。该问题及其答案被锁定，因为该问题是题外话，但具有历史意义。它目前不接受新的答案或互动。

我从事Microsoft SQL
Server
已有多年，但直到最近才开始在我的Web应用程序中使用MySQL，而且我渴望获得知识。

为了继续回答“隐藏功能”问题，我想知道MySQL的任何隐藏或方便功能，这些功能有望改善我对这个开源数据库的了解。

Answer 1

既然您悬赏，我将分享我的来之不易的秘密…

通常，我今天调整的所有SQL都需要使用子查询。来自Oracle数据库世界后，我认为理所当然的事情与MySQL并不一致。我对MySQL调优的阅读使我得出结论，就优化查询而言，MySQL落后于Oracle。

虽然大多数B2C应用程序所需的简单查询在MySQL上可能会很好地运行，但是Intelligence
Reporting所需的大多数汇总报告查询类型似乎都需要进行大量规划和重新组织SQL查询，以指导MySQL更快地执行它们。

max_connections是并发连接数。默认值为100个连接（从5.0开始为151个）-非常小。

注意：

连接会占用内存，并且您的操作系统可能无法处理很多连接。

适用于Linux / x86的MySQL二进制文件最多可允许4096个并发连接，但是自编译二进制文件通常没有限制。

设置table_cache以匹配打开的表和并发连接的数量。观察open_tables值，如果它快速增长，则需要增加其大小。

注意：

前面的2个参数可能需要打开大量文件。20 + max_connections + table_cache * 2是您需要的理想估计。Linux上的MySQL具有open_file_limit选项，请设置此限制。

如果您有复杂的查询，sort_buffer_size和tmp_table_size可能非常重要。值将取决于查询的复杂性和可用资源，但是建议分别将4Mb和32Mb作为起点。

注意：这些是read_buffer_size，read_rnd_buffer_size和其他一些值中的“每个连接”值，这意味着每个连接都可能需要该值。因此，在设置这些参数时，请考虑您的负载和可用资源。例如，仅当MySQL需要进行排序时才分配sort_buffer_size。注意：注意不要耗尽内存。

如果建立了许多连接（即没有持久连接的网站），则可以通过将thread_cache_size设置为非零值来提高性能。首先是16很好的价值。增加该值，直到您的threads_created不会很快增长。

首要的关键：

每个表只能有一个AUTO_INCREMENT列，必须对其建立索引，并且不能具有DEFAULT值

KEY通常是INDEX的同义词。在列定义中给出键属性PRIMARY KEY时，也可以将其指定为KEY。这样做是为了与其他数据库系统兼容。

PRIMARY KEY是唯一索引，其中所有键列必须定义为NOT NULL

如果PRIMARY KEY或UNIQUE索引仅由具有整数类型的一列组成，则还可以在SELECT语句中将该列称为“ _rowid”。

在MySQL中，PRIMARY KEY的名称为PRIMARY

当前，仅InnoDB（v5.1？）表支持外键。

通常，创建表时会创建所需的所有索引。任何声明为PRIMARY KEY，KEY，UNIQUE或INDEX的列都将建立索引。

NULL表示“没有值”。要测试NULL， 不能 使用算术比较运算符，例如=，<或<>。请使用IS NULL和IS 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()

如果在MySQL
中将日期时间转换为Unix时间戳：然后对其添加24小时：
然后将其转换回其神奇地浪费了一个小时的日期时间！

这是正在发生的事情。当将unix时间戳转换回日期时间时，会考虑时区，并且恰好发生在2006年10月28日至29日之间，我们不使用夏令时并浪费了一个小时。

从MySQL
4.1.3开始，CURRENT_TIMESTAMP（），CURRENT_TIME（），CURRENT_DATE（）和FROM_UNIXTIME（）函数在连接的
当前时区中 返回值，这些值可用作time_zone系统变量的值。另外，UNIX_TIMESTAMP（）假定其参数是当前时区中的datetime值。

当前时区设置不会影响由UTC_TIMESTAMP（）之类的函数显示的值或DATE，TIME或DATETIME列中的值。

注意：ON UPDATE 仅 在字段更改时更新DateTime。如果UPDATE导致没有字段更改，则DateTime不会更新！

另外，即使未指定，默认情况下，“第一时间”始终始终为AUTOUPDATE

使用日期时，我几乎总是迷恋朱利安日期，因为出于同样的原因，数据数学是一个简单的问题，即添加或减去整数，以及自午夜以来的秒数。我很少需要比秒更细粒度的时间设定。

这两个都可以存储为4个字节的整数，并且如果空间真的很紧凑，可以将其合并为UNIX时间（自1970年1月1日以来的秒数），作为一个无符号整数，直到2106年左右为止，它都是有效的：

24小时的秒数= 86400

‘有符号整数最大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：

有一点要注意的是，二进制数据 CAN 常规查询中被发送，如果你逃避它，记住MySQL的要求 仅
是反斜线和引号字符转义。因此，这是插入较短的二进制字符串（例如，加密/腌制密码）的简单方法。

主服务器：

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

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

授予复制从属 。 到slave_user IDENTIFIED BY’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/zh-CN/storage-
requirements.html

http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2

http://bitfilm.net/2008/03/24/saving-bytes-efficiency-data-storage-mysql-
part-1/

需要注意的一件事是，在同时具有CHAR和VARCHAR的混合表上，mySQL会将CHAR更改为VARCHAR

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT，PRIMARY KEY（RecNum）

根据标准SQL和ISO 8601规范，MySQL始终以第一年表示日期

其他：

关闭某些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备份功能（还原也是如此）

诀窍是制作这些文件的副本，然后删除表。当您将文件放回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字节

MySQL 5.0.3及更高版本中VARCHAR的有效最大长度=最大行大小（65,535字节）

存储VARCHAR值时不会对其进行填充。根据标准SQL，在存储和检索值时保留尾随空格。

比较MySQL中的CHAR和VARCHAR值，而不考虑尾随空格。

如果整个记录都是固定大小的，那么使用CHAR只会加快访问速度。也就是说，如果使用任何可变大小的对象，则最好使它们全部变为可变大小。通过在还包含VARCHAR的表中使用CHAR不会加快速度。

自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

virtualcolumn从mytable ORDER BY virtualcolumn 选择SELECT max（namecount）AS

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-
fields.html

http://dev.mysql.com/doc/refman/5.1/zh-CN/information-
functions.html#function_last-insert-
id last_insert_id（）

获取您当前线程中插入的最后一行的PK。max（pkcolname）获取您整体的最后PK。

注意：如果表为空，max（pkcolname）返回1 mysql_insert_id（）将本机MySQL C
API函数mysql_insert_id（）的返回类型转换为long类型（在PHP中命名为int）。

如果您的AUTO_INCREMENT列的列类型为BIGINT，则mysql_insert_id（）返回的值将不正确。而是在SQL查询中使用内部MySQL
SQL函数LAST_INSERT_ID（）。

http://dev.mysql.com/doc/refman/5.0/zh-CN/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/zh-CN/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