在几乎是与Oracle宣布收购SUN的同时，MySQL团队宣布发布MySQL5.4版本，按照官方文档：A quick look at MySQL 5.4里写的，MySQL 5.4并没有带来像Falcon引擎这样大的改进，但在MySQL可扩展性和更快的查询速度方面做出了不少改进：
1、可伸缩性能的提升。支持InnoDB存储引擎扩展至16路x86服务器和64路CMT服务器，性能提升了一倍。
2、子查询和JOIN的性能提升。这点也是这次系列升级中，我最关注的部分。在下面这条SQL语句中，官方文档给出的性能提升相当可观。
SQL代码
SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
l_orderkey IN
(SELECT o_orderkey FROM orders
WHERE o_totalprice > 1000 AND
o_custkey IN
(SELECT c_custkey FROM customer
WHERE c_address LIKE ’Le%’));
Time
Number of Reads
MySQL 5.0/5.1
12 Minutes
9,000,000
MySQL 5.4
1.8 seconds
153,000
SubQuery也正是我前些天遇到问题的语句，原来版本中(我使用的是5.0)，MySQL无法对SubQuery实现优化，导致前端应用中速度特别慢。
而同样一条JOIN查询，时间则由原来的4 Minutes 15.39 secs提高到44.41 seconds，这个提升，新查询只用了原时间的17%左右。按照文档里的说法，这个性能的提升等正式版本发布的时候，不仅限于InnoDB引擎，而是所有引擎均。
其他的都是现在没有特别关注的，比如：改进了存储过程，完善了prepared statements等。
期待，过几天有时间了，Down个MySQL 5.4来试试看。到时再把实测结果发出来分享一下。
If you use pgcc and compile everything with -O6, the mysqld server is 1% faster than with gcc 2.95.2.
If you link dynamically (without -static), the result is 13% slower on Linux. Note that you still can use a dynamically linked MySQL library for your client applications. It is the server that [...]
From: http://blog.csdn.net/wngvo/archive/2007/10/10/1818436.aspx
发现主机又出错了，错误是这样。ERROR 1129 (00000): Host ’XXXXXX’ is blocked because of many connection errors; unblock with ’mysqladmin flush-hosts’
查了下资料
这说明mysqld已经得到了大量(max_connect_errors)的主机’hostname’的在中途被中断了的连接请求。在 max_connect_errors次失败请求后，mysqld认定出错了(象来字一个黑客的攻击)，并且阻止该站点进一步的连接，直到某人执行命令 mysqladmin flush-hosts。
缺省地，mysqld在10个连接错误后阻塞一台主机。你可以通过象这样启动服务器很容易地调整它： set global max_connect_errors=1000;
注意，对给定的主机，如果得到这条错误消息，你应该首先检查该主机的TCP/IP连接有没有问题。如果你的TCP/IP连接不在运行，增加max_connect_errors变量的值对你也不会有帮助！
例:
/usr/local/mysql/bin/mysqladmin flush-host -h 192.168.50.1 -uroot
cd /usr/ports/databases/mysql51-server
#编译安装（FreeBSD默认不带入所有字符集支持，需要声明打开）
make WITH_XCHARSET=all install clean
或自定义参数：
#make WITH_CHARSET=utf8 WITH_XCHARSET=all WITH_PROC_SCOPE_PTH=yes BUILD_OPTIMIZED=yes BUILD_STATIC=yes SKIP_DNS_CHECK=yes WITHOUT_INNODB=yes install clean
rehash
#复制配置文件（#服务器内存4G，但是与apache在一起）：
cp /usr/local/share/mysql/my-large.cnf /etc/my.cnf
#设置随机启动：
vi /etc/rc.conf
mysql_enable="YES"
#初始化：
/usr/local/bin/mysql_install_db –user=mysql
#更改数据库权限（若所有者不是mysql）：
chown -R mysql /var/db/mysql/
#立即启动：
/usr/local/bin/mysqld_safe -user=mysql &
#修改mysql root密码：
/usr/local/bin/mysqladmin -u root password ‘newpasswd’
#测试：
mysql -u root -p
mysql> show databases;
#启动/重启/停止mysqld
/usr/local/etc/rc.d/mysql-server start (等于/usr/local/bin/mysqld_safe -user=mysql &)
/usr/local/etc/rc.d/mysql-server restart
/usr/local/etc/rc.d/mysql-server stop
# end
(本文是Monty在O’Reilly Open Source Convention 2000大会上的演讲)
MySQL的优化(本文是Monty在O’Reilly Open Source Convention 2000大会上的演讲)
[晏子 编译整理]
一、我们可以且应该优化什么？
硬件
操作系统/软件库
SQL服务器(设置和查询)
应用编程接口(API)
应用程序
——————————————————————————–
二、优化硬件
如果你需要庞大的数据库表(>2G)，你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。
对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。
更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。
如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。
对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。延迟与吞吐量同样重要。
——————————————————————————–
三、优化磁盘
为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。低 寻道时间对数据库磁盘非常重要。对与大表，你可以估计你将需要log(行数)/log(索引块长度/3*2/(键码长度 + 数据指针长度))+1次寻到才能找到一行。对于有500000行的表，索引Mediun int类型的列，需要log(500000) / log(1024/3*2/(3 + 2))+1=4次寻道。上述索引需要500000*7*3/2=5.2M的空间。实际上，大多数块将被缓存，所以大概只需要1-2次寻道。然而对于写入（如上），你将需要4次寻道请求来找到在哪里存放新键码，而且一般要2次寻道来更新索引并写入一行。对于非常大的数据库，你的应用将受到磁盘寻道速度的限制，随着数据量的增加呈N log N数据级递增。将数据库和表分在不同的磁盘上。在MySQL中，你可以为此而使用符号链接。条列磁盘(RAID 0)将提高读和写的吞吐量。带镜像的条列(RAID 0+1)将更安全并提高读取的吞吐量。写入的吞吐量将有所降低。不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID(除了RAID 0)。在Linux上，在引导时对磁盘使用命令hdparm -m16 -d1以启用同时读写多个扇区和DMA功能。这可以将响应时间提高5~50%。在Linux上，用async (默认)和noatime挂载磁盘(mount)。对于某些特定应用，可以对某些特定表使用内存磁盘，但通常不需要。
——————————————————————————–
四、优化操作系统
不要交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存。不要使用NFS磁盘(会有NFS锁定的问题)。增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。增加系统的进程和线程数量。如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道上(Solaris)。使用支持大文件的文件系统(Solaris)。选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。
——————————————————————————–
五、选择应用编程接口
PERL可在不同的操作系统和数据库之间移植。适宜快速原型。应该使用DBI/DBD接口。PHP比PERL易学。使用比PERL少的资源。通过升级到PHP4可以获得更快的速度。CMySQL的原生接口。较快并赋予更多的控制。低层，所以必须付出更多。C++较高层次，给你更多的时间来编写应用。仍在开发中ODBC运行在Windows和Unix上。几乎可在不同的SQL服务器间移植。较慢。MyODBC只是简单的直通驱动程序，比用原生接口慢19%。有很多方法做同样的事。很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。问题成堆。Microsoft偶尔还会改变接口。不明朗的未来。(Microsoft更推崇OLE而非ODBC)ODBC运行在Windows和Unix上。几乎可在不同的SQL服务器间移植。较慢。MyODBC只是简单的直通驱动程序，比用原生接口慢19%。有很多方法做同样的事。很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。问题成堆。Microsoft偶尔还会改变接口。不明朗的未来。(Microsoft更推崇OLE而非ODBC)JDBC理论上可在不同的操作系统何时据库间移植。可以运行在web客户端。Python和其他可能不错，可我们不用它们。
——————————————————————————–
六、优化应用
应该集中精力解决问题。在编写应用时，应该决定什么是最重要的：速度操作系统间的可移植性SQL服务器间的可移植性使用持续的连接。.缓存应用中的数据以减少SQL服务器的负载。不要查询应用中不需要的列。不要使用SELECT [...]
2005-11-09 09:42 am
作者：linux宝库 (http://www.linuxmine.com)
来自：linux宝库 (http://www.linuxmine.com)
现存：http://www.linuxmine.com/1835.html
联系：linuxmine#gmail.com
不明白？欢迎到 linux论坛 (http://bbs.linuxmine.com) 参加讨论!
使用mysqldump工具，基本用法是：
shell> mysqldump [OPTIONS] database [tables]
如果你不给定任何表，整个数据库将被导出。
通过执行mysqldump –help，你能得到你mysqldump的版本支持的选项表。
注意，如果你运行mysqldump没有–quick或–opt选项，mysqldump将在导出结果前装载
整个结果集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。
mysqldump支持下列选项：
–add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
–add-drop-table
在每个create语句之前增加一个drop table。
–allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
-c, –complete-insert
使用完整的insert语句(用列名字)。
-C, –compress
如果客户和服务器均支持压缩，压缩两者间所有的信息。
–delayed
用INSERT DELAYED命令插入行。
-e, –extended-insert
使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句）
-#, –debug[=option_string]
跟踪程序的使用(为了调试)。
–help
显示一条帮助消息并且退出。
–fields-terminated-by=…
–fields-enclosed-by=…
–fields-optionally-enclosed-by=…
–fields-escaped-by=…
–fields-terminated-by=…
这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。
LOAD DATA INFILE语法。
-F, –flush-logs
在开始导出前，洗掉在MySQL服务器中的日志文件。
-f, –force,
即使我们在一个表导出期间得到一个SQL错误，继续。
-h, –host=..
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。
-l, –lock-tables.
为开始导出锁定所有表。
-t, –no-create-info
不写入表创建信息(CREATE TABLE语句）
-d, –no-data
不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！
–opt
同–quick –add-drop-table –add-locks –extended-insert –lock-tables。
应该给你为读入一个MySQL服务器的尽可能最快的导出。
-pyour_pass, –password[=your_pass]
与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。
-P port_num, –port=port_num
与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用 Unix套接字。）
-q, –quick
不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。
-S /path/to/socket, –socket=/path/to/socket
与localhost连接时（它是缺省主机)使用的套接字文件。
-T, –tab=path-to-some-directory
对于每个给定的表，创建一个table_name.sql文件，它包含SQL CREATE 命令， 和一
个table_name.txt文件，它包含数据。 注意：这只有在mysqldump运行在mysqld守护
进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和
–lines–xxx选项来定。
-u user_name, –user=user_name
与服务器连接时，MySQL使用的用户名。缺省值是你的Unix登录名。
-O var=option, –set-variable var=option设置一个变量的值。可能的变量被列在下面。
-v, –verbose
冗长模式。打印出程序所做的更多的信息。
-V, –version
打印版本信息并且退出。
-w, [...]
下面要写的是一篇非常无聊的东西，充斥了大量各式各样的编码、转换、客户端、服务器端、连接……呃，我自己都不愿意去看它，但想一想，写下来还是有点意义的，原因有四：
MySQL 4.1 对多语言的支持有了很大变化 (这导致了问题的出现)； 尽管大部分的地方 (包括个人使用和主机提供商)，MySQL 3 仍然占主导地位；但 MySQL 4.1 是 MySQL 官方推荐的数据库，已经有主机提供商开始提供并将会越来越多； 许多 PHP 程序以 MySQL 作为默认的数据库管理软件，但它们一般不区分 MySQL 4.1 与 4.1 以下版本的区别，笼统地称“MySQL 3.xx.xx 以上版本”就满足安装需求了； 因为 latin1 在许多地方 (下边会详细描述具体是哪些地方) 作为默认的字符集，成功的蒙蔽了许多 PHP 程序的开发者和用户，掩盖了在中文等语言环境下会出现的问题； 简单的说，MySQL 自身的变化和使用 MySQL 的 PHP 程序对此忽略，导致了问题的出现和复杂化，而由于大部分用户使用的是英文，使这种问题不被重视。这里提到的 PHP 程序，主要就 WordPress 而言。
MySQL 4.1 字符集支持的原理MySQL 4.1 对于字符集的指定可以细化到一台机器上安装的 MySQL，其中的一个数据库，其中的一张表，其中的一栏，应该用什么字符集。但是，传统的 Web 程序在创建数据库和数据表时并没有使用那么复杂的配置，它们用的是默认的配置，那么，默认的配置从何而来呢？
编译 MySQL 时，指定了一个默认的字符集，这个字符集是 latin1； 安装 MySQL [...]
又是bigint。这次居然发现当value为0时不能-1，否则就会出错。哪像int/mediumint/smallint一样听话啊。居然还给我搞出来个18446744073709551615。弄的分页出现了科学计数。sigh。
mark一下下。以后不要再出现这种错误了。
（有机会了，试试mssql和Oracle，看看这两位会不会像MySql一样不好用。）
数据列类型 存储空间 描述
TINYINT 1字节 非常小的正整数，带符号：-128~127，不带符号：0~255
SMALLINT 2字节 小整数，带符号：-32768~32767，不带符号：0~65535
MEDIUMINT 3字节 中等大小的整数，带符号：-8388608~8388607，不带符号：0~16777215
INT 4字节 标准整数，带符号：-2147483648~2147483647，不带符号：0~4294967295
BIGINT 8字节 大整数，带符号：-9223372036854775808~9233372036854775807，不带符号：0~18446744073709551615
FLOAT 4字节 单精度浮点数，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38
DOUBLE 8字节 双精度浮点数，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308
DECIMAL M+2字节 以字符串形式表示的浮点数，它的取值范围可变，由M和D的值决定。
当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。
当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL执行它的速度都会非常快：
SELECT * FROM tbl_name WHERE 0;
在这个例子中，MySQL查看WHERE子句，认识到没有符合查询条件的数据行，因此根本就不考虑搜索数据表。你可以通过提供一个EXPLAIN语句看到这种情况，这个语句让MySQL显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN，只需要在EXPLAIN单词放在SELECT语句的前面：
mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE
通常情况下，EXPLAIN返回的信息比上面的信息要多一些，还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空（NULL）信息。
优化器是如何工作的
MySQL查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行，而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。假设你的查询检验了两个数据列，每个列上都有索引：
SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’;
假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只得到了30个数据行。先测试Col1会有900个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘I/O更少。其结果是，优化器会先测试col2，因为这样做开销更小。
你可以通过下面一个指导帮助优化器更好地利用索引：
尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候，请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如，INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10)，但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同，那么可以使用ALTER TABLE来修改其中一个，使它们的类型相匹配。
尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免，但是很多情况下你可以重新编写一个查询让索引列独立地出现。
下面的WHERE子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：
WHERE mycol < 4 / 2
WHERE mycol * 2 < 4 [...]
RSS
Sina微博
twitter