http://ourmysql.com/archives/262
什么是我们能和应该优化的
Hardware
OS / libraries
SQL server (setup and queries)
API
Application
优化硬件
如果你需要大表(>2G),你应该考虑使用64位硬件,像Alpha,Sparc或者IA64.由于MySQL使用大量内部的64位整型,64位的CPU将有更好的表现.
对于大数据库,优化顺序通常是内存,硬盘,CPU.
更多的内存可以通过把更多的索引页保留在内存中来提高索引更新的速度
如果你不使用事务安全表或者有一个大硬盘并且想避免大文件检查,有一个UPS是个好办法,它能在断电的时候使系统正常关闭.
对于数据库运行在专用的服务器上的系统来说,应该考虑1G的网络,传输延迟和吞吐量同样重要.
优化磁盘
有专用的磁盘来运行系统,程序以及临时文件.如果更改很频繁,把更新日志和事务日志放在专用的磁盘上.
对于数据库磁盘来说短寻道时间很重要;对于大表你可以预估需要:log(row_count) / log(index_block_length/3
2/(key_length + data_ptr_length))+1 次查找来找到一行.对于一个有500000行的表要索引(medium int)log(500,000)/log(1024/3
2/(3+4)) +1 = 4 次.上面的索引将需要500,000
7
3/2 = 5.2M,在实际文件中,大部分数据块将被缓冲,因此大概只需要1-2次查找.
对写入操作你将需要(像上面一样)4次查找请求,为了找到索引存放的地方,通常要2次查找来更新索引和写入行.
对实在大的数据库,你的应用将基于你的磁盘查找的速度,当你获得更多数据的时候,它以NlogN的速度增长.
把数据库和表拆分到不同的磁盘上.在MySQL里你可以使用符号连接来实现.
RAID0将提高读和写
RAID0+1能提供安全性和提高读的速度.写操作将稍慢.
不要在磁盘上使用镜像或者RAID(除了RAID0)用来存放临时文件或者为了数据能够恢复.
在Linux下,的磁盘上启动时使用hdparm -m16 -d1开启多重扇面同时读写能力,和DMA.这可以提高5-50%的响应.
在Linux上,用async(默认)和noatime来mount磁盘.
对一些特殊的应用,有可能希望用一个ram磁盘存放特殊的表,但是通常这个不需要的.
优化OS
不要swap;如果你有内存问题,多加一些RAM来代替或者配置你的系统用更少的内存
不要使用NFS磁盘来存储数据(会遇到NFS锁的问题).
为系统和SQL服务器提高开放文件的数量(添加ulimit -n #在safe_mysqld脚本里).
为系统提高处理器和线程的数量.
如果有相对比较少的大表,告诉文件系统不要把文件分散到不同的柱面上(Solaris).
使用支持大文件的文件系统(Solaris).
选择使用哪个系统;Reiserfs在Linux上打开,读和写非常快.文件检查只需要几秒.
选择API
PERL
OS和数据库之间可移植的程序
对快速的原型设计有好处
应该使用DBI/DBD接口
PHP
比PERL简单
比PERL使用更少的资源,使它更容易在Web服务器上使用.
能通过升级到PHP4来获得更快的速度
C
到MYSQL天然的接口
更快,更容易控制
更底层,因此你要做的更多
C++
更高的级别给你更多的时间来编写你的应用
仍在开发中
ODBC
运行在Windows和Unix上
几乎在不同的SQL服务器上可移植
慢,MyODBC,是一个简单的穿透驱动比天然接口慢19%.
很多途径做同样的事;像很多ODBC驱动一样在不同的区域有不同的bug很难运行.
问题重重;Microsoft经常更改接口.
不稳定的未来(Microsoft在OLE上比ODBC花更多的精力).
JDBC
理论上在OS和数据库之间可移植
可以在web客户端上运行
Python+others
可能很好,但是我们不使用他们.
优化应用程序
应该有人专注于解决问题
写应用程序的时候应该决定什么是最重要的:
速度
OS间可移植
SQL服务器间可移植
使用持续连接
在你的应用程序中使用缓存来减少SQL服务器的压力.
不要在你的应用程序中查询不需要的列.
不要使用SELECT * FROM table_name…
检查你的程序的所有部分,但是把努力放在检查整个应用下面最坏可能"合理"的压力上.
如果一下子做很多改变,使用LOCK TABLES;例如很多UPDATE或者DELETE在一起的时候.
可移植的应用程序应该使用:
Perl DBI/DBD
ODBC
JDBC
Python(或者任何其他有全面的SQL接口的语言)
你应该只使用在所有的目标SQL服务器上存在的或者可以轻易被其他构造模仿的SQL构造.
写封装来为其他的OS/SQL服务器提供缺少的功能.
如果你需要更快的速度,你应该:
找到瓶颈(CPU,磁盘,内存,SQL服务器,OS,API,或者应用程序)并专心解决他们
使用能给你带来更多速度/灵活性的扩展
了解你的SQL服务器以便于你在遇到问题或者避免瓶颈的时候使用可能最快的SQL构造
优化你的表结构和查询
使用备份来获得更多的select速度
如果连接到数据库的网速很慢,使用压缩的客户端/服务器协议.
不要担心你的第一个版本不是完全可移植的,当你解决了难题,优化总是可以稍后进行
优化MySQL
选择编译程序合编译选项
为你的系统找到做好的MySQL启动选项
学习MySQL手册,读Paul DuBois的MySQL著作
使用EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS 和 SHOW PROCESSLIST.
学习查询优化器是如何工作的.
优化你的表结构
维护你的表(myisamchk, CHECK TABLE, OPTIMIZE TABLE).
使用MySQL扩展获得更快的速度.
写一个MySQL UDF函数如果你发现你在很多地方需要这样的函数
如果你不是确实需要,不要在表一级或者列一级使用GRANT
付费给MySQL客服来获得帮助,解决问题:)
编译和安装MySQL
为你的系统选择最好的编译程序,通常可以获得10-30%更好的性能
在Linux/Intel上,用pgcc编译MySQL.但是,二进制文件将只能运行在Pentium CPU上.
对特殊的平台,使用MySQL手册总推荐的优化选项
通常对特别的CPU(像Sparc的Sun工作站)本地的编译器会比gcc提供个个你好的性能,但是不总是这样.
只用你将要使用的字符集编译MySQL
把mysqld编译成静止可执行(使用�with-mysqld-ldflags=-all-static)并使用strip sql/mysqld去掉最终可执行.
注意由于MySQL不使用C++异常,编译MySQL时去掉异常支持将获得很大性能的提升.
如果你的OS支持,那么使用本地线程.
使用MySQL性能测试来测试最终的二进制文件.
维护
如果可能,偶而运行OPTIMIZE table .这对于经常更新的可变大小的行非常重要.
偶而使用myisamchk -a来更新你的表中索引的分布状态;记住做这个之前关闭MySQL.
如果有文件碎片,把所有文件拷贝到另外的磁盘,清理旧磁盘再把文件拷回来或许很有意义.
如果有问题,用myisamchk 或者CHECK table来检查你的表
用 mysqladmin -i10 processlist extended-status 监视MySQL状态
用mysqladmin debug 来获得锁和性能的信息
优化SQL
把SQL用在它擅长的地方,用应用程序做其他事.用SQL服务器来做:
通过where条件找到行
联合表
GROUP BY
ORDER BY
DISTINCT
不要用SQL服务器做:
检查数据(像日期)
作为计算器
Tips
精明的使用索引
索引对搜索有好处,但是对插入/更新索引所在列没好处
保持数据符合第三范式,但是不要怕重复信息或者在需要更快速度的时候建立摘要表.
用建立和查询大表的摘要表来代替在一个大表上进行多次GROUP BY操作.
UPDATE table set count=count+1 where key_column=constant非常快.
对日志表,偶而从他们中生产摘要表可能比让摘要表一直存在要好.
INSERT的时候利用默认值的优点
不同SQL服务器之间的不同速度
Reading 2000000 rows by key: NT Linux
mysql 367 249
mysql_odbc 464
db2_odbc 1206
informix_odbc 121126
ms-sql_odbc 1634
oracle_odbc 20800
solid_odbc 877
sybase_odbc 17614
Inserting (350768) rows: NT Linux
mysql 381 206
mysql_odbc 619
db2_odbc 3460
informix_odbc 2692
ms-sql_odbc 4012
oracle_odbc 11291
solid_odbc 1801
sybase_odbc 4802
在上面的测试中,MySQL运行于8M的缓存,其他数据库运行在默认安装上.
重要的MySQL启动选项
back_log Change if you do a lot of new connections.
thread_cache_size Change if you do a lot of new connections.
key_buffer_size Pool for index pages; Can be made very big
bdb_cache_size Record and key cache used by BDB tables.
table_cache Change if you have many tables or simultaneous connections
delay_key_write Set if you need to buffer all key writes
log_slow_queries Find queries that takes a lot of time
max_heap_table_size Used with GROUP BY
sort_buffer Used with ORDER BY and GROUP BY
myisam_sort_buffer_size Used with REPAIR TABLE
join_buffer_size When doing a join without keys
优化表
MySQL有丰富的类型.你应该为每一列选择最有效的类型
ANALYSE过程能帮助你找到一个表的最佳类型:SELECT * FROM table_name PROCEDURE ANALYSE()
使用NOT NULL 将不保存空值.这对建立索引的列尤为重要.
把你的表由ISAM类型改变为MyISAM类型
如果可能,用固定表格式建立表.
不会使用的时候不要建立索引
记住MySQL可以在索引的前缀上进行搜索;如果你有INDEX (a,b),就不必在(a)上建立索引.
在CHAR/VARCHAR列上建立索引的时候,用建立前缀索引来节省空间.CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
为每个表选择最有效的类型
不同表中的同样信息的列应该同样定义并且有同样的名字.
MySQL如何存储数据
数据按目录存放
表以文件的形式存放
列以变长或者固定尺寸的格式存放.在BDB表中数据按页存放
支持基于内存的表
数据库和表可以从不同的磁盘上做符号连接
在Windows上MySQL支持对数据库通过.sym文件做内部的符号连接
MySQL表类型
HEAP表;只存在于内存中的固定行尺寸表,用哈希索引
ISAM表;MySQL3.22中的老的B树表
MyISAM表;包括大量扩展的ISAM表的新版本:
二进制可移植
空值列上的索引
动态尺寸列上比ISAM表更少的碎片
支持大文件
更好的索引压缩
更好的关键字统计
更好并且更快的auto_increment 处理
从Sleepycat来的Berkeley DB (BDB):事务安全(通过BEGIN WORK / COMMIT | ROLLBACK).
MySQL行类型(有关ISAM/MyISAM表)
如果每列都是固定长度的格式(没有VARCHAR,BLOB或者TEXT列)MySQL将建立固定尺寸的表格式,否则,表被建立成动态尺寸格式
固定尺寸比动态尺寸格式格式更快也更安全
动态尺寸格式的行通常占更少的空间但是如果表经常更新会一直产生碎片
有时候为了提高主表的速度把所有的VARCHAR,BLOB和TEXT列转移到其他表中是值得的.
用myisampack (ISAM中用pack_isam )可以建立一个只读的,打包的表.它最小的利用磁盘空间在慢磁盘时效果很好.打包的表最好用在不会更新的日志表上.
MySQL缓存(一旦分配,所有线程共享)
关键字缓存:key_buffer_size,默认8M
表缓存;table_cache,默认64
线程缓存;thread_cache_size,默认0
域名缓存;在编译时可以更改,默认128
内存地址表;目前只用在压缩表上.
注意MySQL没用行缓存,让OS来处理这个.
MySQL缓冲变量(通过命令分配,不共享)
sort_buffer ; ORDER BY / GROUP BY
record_buffer ;扫描表
join_buffer_size :不通过关键字连接
myisam_sort_buffer_size :REPAIR TABLE
net_buffer_length ;用来读SQL语句和缓冲结果
tmp_table_size;临时结果的HEAP表尺寸
MySQL的表缓存如何工作
MyISAM表的每个打开的实例使用一个索引文件和一个数据文件.如果表被两个线程使用或者在一个查询中使用两次,MyISAM将共享索引文件但是会打开数据文件的另一个实例.
如果缓存中的所有表都在使用缓存将会临时变得比表缓存尺寸更大.如果这样,下一个被释放的表将被关闭.
你可以通过检查mysqld的变量Opend_tables来检查你的表缓存是否太小.如果这个值很高你应该提高你的表缓存.
提高你的速度的MySQL扩展/优化
使用合适的表类型(HEAP,MyISAM,或者BDB表)
为你的数据使用合适的列
如果可能使用固定的行尺寸
使用不同的锁类型(SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)
Auto_increment
REPLACE (REPLACE INTO table_name VALUES (…))
INSERT DELAYED
LOAD DATA INFILE / LOAD_FILE()
使用多行INSERT来一次插入很多行
SELECT INTO OUTFILE
LEFT JOIN, STRAIGHT JOIN
LEFT JOIN 结合IS NULL
ORDER BY 有时候能使用索引
如果你只查询在一个索引中的列,只用索引树会被用来处理查询.
联合查询通常比子查询要快(这适用于大部分SQL服务器)
LIMIT
SELECT
from table1 WHERE a > 10 LIMIT 10,20
DELETE
from table1 WHERE a > 10 LIMIT 10
foo IN (list of constants)很快.
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT和SELECT可以同时运行
UDF函数可以装载进一个运行中的服务器
压缩只读表
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
MyISAM表通过RAID选项把一个文件分散成许多文件来突破有些文件系统上的2G限制
Delayed_keys
备份
MySQL什么时候使用索引
在一个关键字上使用>, >=, =, <, <=, IF NULL 和BETWEEN
SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;
使用不以%开头的like语句的时候
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
连表的时候从其他表检索行
SELECT * from t1,t2 where t1.col=t2.key_part
在一个指定的索引上找到MAX() 或者MIN()值
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
在一个关键字的前缀上ORDER BY或者GROUP BY
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
所有查询中的列都是关键字的一部分
SELECT key_part3 FROM table_name WHERE key_part1=1
MySQL什么时候不使用索引
如果MySQL能计算出扫描整个表可能更快它不使用索引.例如如果key_part1 分布在1到100之间,在下面的查询中使用索引效果就不好:
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
如果你使用HEAP表不要在所有的关键字部分使用=来搜索
在HEAP表上使用ORDER BY的时候
如果你不使用首个关键字部分
SELECT * FROM table_name WHERE key_part2=1
如果你使用以%开头的LIKE
SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
当你在一个索引上查找,然后ORDER BY另一个
SELECT * from table_name WHERE key_part1 = # ORDER BY key2
学习使用EXPLAIN
在你认为慢的每个查询上很实用EXPLAIN
mysql> explain select t3.DateOfAction, t1.TransactionID
-> from t1 join t2 join t3
-> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
-> order by t3.DateOfAction, t1.TransactionID;
+――-+――�+―――――+―――+―――+――――――+――+―――――――――――+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+――-+――�+―――――+―――+―――+――――――+――+―――――――――――+
| t1 | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
| t2 | ref | ID | ID | 4 | t1.TransactionID | 13 | |
| t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 | |
+――-+――�+―――――+―――+―――+――――――+――+―――――――――――+
ALL和range类型表明了潜在的问题
学习使用SHOW PROCESSLIST
使用SHOW PROCESSLIST来找到什么正在运行
+―-+――-+―――�+―-+―――+――+――――�+――――――――――――-+
| Id | User | Host | db | Command | Time | State | Info |
+―-+――-+―――�+―-+―――+――+――――�+――――――――――――-+
| 6 | monty | localhost | bp | Query | 15 | Sending data | select
from station,station as s1 |
| 8 | monty | localhost | | Query | 0 | | show processlist |
+―-+――-+―――�+―-+―――+――+――――�+――――――――――――-+
怎样找出MySQL如何处理一个查询
运行下面的命令努力理解输出:
SHOW VARIABLES;
SHOW COLUMNS FROM …\G
EXPLAIN SELECT …\G
FLUSH STATUS;
SELECT …;
SHOW STATUS;
MySQL特别适合用于:
记录日志
当你运行很多连接的时候;连接非常快
同时使用SELECT和INSERT的时候
当你不把执行很长时间的查找和更新联在一起的时候
当大部分查找/更新使用唯一关键字的时候
当你使用没有冲突锁的大量表的时候
当你有大表(MySQL使用非常紧密的表结构)的时候
不要让MySQL做:
在删除行的时候更新或者插入一个表,联合长时间的SELECT
在WHERE条件中使用HAVING.
不使用关键字的JOIN或者关键字不唯一
在有不同列类型的列上JOIN
使用HEAP表的时候用=匹配而不使用完全的关键字
在MySQL监视器中使用没用WHERE条件的UPDATE或者DELETE.如果你可能这样做,在mysql客户端使用-i-am-a-dummy选项
MySQL中不同的锁
内部表锁
LOCK TABLES(运行在所有表类型上)
GET_LOCK()/RELEASE_LOCK()
页锁(BDB表)
ALTER TABLE在BDB表上也造成一个表锁
LOCK TABLES 允许在一个表上的复合读操作或者一个写操作
通常写锁比读锁在禁止写操作的时候有更高的优先级.对写操作来说,可以使用LOW_PRIORITY关键字来使锁管理器优先进行读操作
UPDATE LOW_PRIORITY SET value=10 WHERE id=10;
给MySQL更多信息来更好解决问题的窍门
注意你通常可以注释掉一个MySQL特性来使查询变得可移植:
SELECT /*! SQL_BUFFER_RESULTS */ …
SELECT SQL_BUFFER_RESULTS …
将强制MySQL来建立一个临时结果集.一旦临时结果集完成,表上的所有锁被释放.当你遇到表锁或者需要很长时间来把结果传递给客户端的时候这个很有帮助.
SELECT SQL_SMALL_RESULT … GROUP BY …
告诉优化器结果只包含很少的行
SELECT SQL_BIG_RESULT … GROUP BY …
告诉优化器结果包含很多行
SELECT STRAIGHT_JOIN …
强制优化器按照FROM条件排列的顺序来联合表
SELECT … FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
强制MySQL使用/忽略列出的索引
处理事务的例子
怎样使用MyISAM表做事务操作:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set totalvalue=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
怎样使用BDB表做事务操作:
mysql> BEGIN WORK;
mysql> select sum(value) from trans where customer_id=some_id;
mysql> update customer set totalvalue=sum_from_previous_statement
where customer_id=some_id;
mysql> COMMIT;
注意你通常可以通过组合避免事务:
UPDATE customer SET value=value+new_value WHERE customer_id=some_id;
使用REPLACE的例子
REPLACE的作用特别像INSERT,除了如果表中存在一个有相同值的旧记录在唯一的索引中作为一个新记录,在新记录插入之前旧记录被删除.下面的语句:
SELECT 1 FROM t1 WHERE key=#
IF found-row
LOCK TABLES t1
DELETE FROM t1 WHERE key1=#
INSERT INTO t1 VALUES (…)
UNLOCK TABLES t1;
ENDIF
可以代替为:
REPLACE INTO t1 VALUES (…)
General tips
用短的主键.使用数字,而不是字符串来连接表
使用联合关键字的时候,第一部分是最有用的关键字
不确定的情况下,使用有很多重复值的列首先最好进行关键字压缩
如果客户端和MySQL服务区运行在同一台机器上,使用socket代替TCP/IP来连接MySQL(能提升7.5%的性能).你可以通过连接到MySQL服务器的时候使用不指定域名或者使用locahost来实现.
如果可能,使用�skip-locking (在有些OS上默认的).它将关闭外部锁提供更好的性能.
使用应用程序级的哈希值代替使用长关键字:
SELECT
FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
col_1='constant' AND col_2='constant'
把你需要的BLOB类型的文件存为文件,只在数据库中保存文件名称.
删除所有行比删除大部分行要快.
如果SQL不够快,看看底层的获得数据的接口
