MySQL的优化(本文是Monty在O'Reilly Open Source Convention 2000大会上的演讲)(摘自老叶的博客,有删改)
2010年08月24日
1、我们可以且应该优化什么?
硬件
操作系统/软件库
SQL服务器(设置和查询)
应用编程接口(API)
应用程序
-------------------------------------------------- ------------------------------
2、优化硬件
如果你需要庞大的数据库表(>2G),你应该考虑使用64位的硬件结构,像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数,64位的CPU将提供更好的性能。
对大数据库,优化的次序一般是RAM、快速硬盘、CPU能力。
更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。
如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查,一台UPS就能够在电源故障时让系统安全关闭。
对于数据库存放在一个专用服务器的系统,应该考虑1G的以太网。延迟与吞吐量同样重要。
-------------------------------------------------- ------------------------------
3、优化磁盘
为系统、程序和临时文件配备一个专用磁盘,如果确是进行很多修改工作,将更新日志和事务日志放在专用磁盘上。
低寻道时间对数据库磁盘非常重要。对与大表,你可以估计你将需要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)。
对于某些特定应用,可以对某些特定表使用内存磁盘,但通常不需要。
-------------------------------------------------- ------------------------------
4、优化操作系统
不要交换区。如果内存不足,增加更多的内存或配置你的系统使用较少内存。
不要使用NFS磁盘(会有NFS锁定的问题)。
增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。
增加系统的进程和线程数量。
如果你有相对较少的大表,告诉文件系统不要将文件打碎在不同的磁道上(Solaris)。
使用支持大文件的文件系统(Solaris)。
选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。
-------------------------------------------------- ------------------------------
5、选择应用编程接口
PERL
可在不同的操作系统和数据库之间移植。
适宜快速原型。
应该使用DBI/DBD接口。
PHP
比PERL易学。
使用比PERL少的资源。
通过升级到PHP4可以获得更快的速度。
C
MySQL的原生接口。
较快并赋予更多的控制。
低层,所以必须付出更多。
C++
较高层次,给你更多的时间来编写应用。
仍在开发中
ODBC
运行在Windows和Unix上。
几乎可在不同的SQL服务器间移植。
较慢。MyODBC只是简单的直通驱动程序,比用原生接口慢19%。
有很多方法做同样的事。很难像很多ODBC驱动程序那样运行,在不同的领域还有不同的错误。
问题成堆。Microsoft偶尔还会改变接口。
不明朗的未来。(Microsoft更推崇OLE而非ODBC)
JDBC
理论上可在不同的操作系统何时据库间移植。
可以运行在web客户端。
Python和其他
可能不错,可我们不用它们。
-------------------------------------------------- ------------------------------
6、优化应用
应该集中精力解决问题。
在编写应用时,应该决定什么是最重要的:
速度
操作系统间的可移植性
SQL服务器间的可移植性
使用持续的连接。.
缓存应用中的数据以减少SQL服务器的负载。
不要查询应用中不需要的列。
不要使用SELECT * FROM table_name...
测试应用的所有部分,但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。通过以一种模块化的方式进行,你应该能用一个快速"哑模块"替代找到的瓶颈,然后很容易地标出下一个瓶颈。
如果在一个批处理中进行大量修改,使用LOCK TABLES。例如将多个UPDATES或DELETES集中在一起。
-------------------------------------------------- ------------------------------
7、如果你需要更快的速度,你应该:
找出瓶颈(CPU、磁盘、内存、SQL服务器、操作系统、API或应用)并集中全力解决。
使用给予你更快速度/灵活性的扩展。
逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。
优化表布局和查询。
使用复制以获得更快的选择(select)速度。
如果你有一个慢速的网络连接数据库,使用压缩客户/服务器协议。
不要害怕时应用的第一个版本不能完美地移植,在你解决问题时,你总是可以在以后优化它。
-------------------------------------------------- ------------------------------
8、优化MySQL
挑选编译器和编译选项。
位你的系统寻找最好的启动选项。
通读MySQL参考手册并阅读Paul DuBios的《MySQL》一书。(已有中文版-译注)
多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。
了解查询优化器的工作原理。
优化表的格式。
维护你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)
使用MySQL的扩展功能以让一切快速完成。
如果你注意到了你将在很多场合需要某些函数,编写MySQL UDF函数。
不要使用表级或列级的GRANT,除非你确实需要。
购买MySQL技术支持以帮助你解决问题:)
-------------------------------------------------- ------------------------------
9、编译和安装MySQL
通过位你的系统挑选可能最好的编译器,你通常可以获得10-30%的性能提高。
在Linux/Intel平台上,用pgcc(gcc的奔腾芯片优化版)编译MySQL。然而,二进制代码将只能运行在Intel奔腾CPU上。
对于一种特定的平台,使用MySQL参考手册上推荐的优化选项。
一般地,对特定CPU的原生编译器(如Sparc的Sun Workshop)应该比gcc提供更好的性能,但不总是这样。
用你将使用的字符集编译MySQL。
静态编译生成mysqld的执行文件(用--with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最终的执行文件。
注意,既然MySQL不使用C++扩展,不带扩展支持编译MySQL将赢得巨大的性能提高。
如果操作系统支持原生线程,使用原生线程(而不用mit-pthreads)。
用MySQL基准测试来测试最终的二进制代码。
-------------------------------------------------- ------------------------------
10、维护
如果可能,偶尔运行一下OPTIMIZE table,这对大量更新的变长行非常重要。
偶尔用myisamchk -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。
如果有碎片文件,可能值得将所有文件复制到另一个磁盘上,清除原来的磁盘并拷回文件。
如果遇到问题,用myisamchk或CHECK table检查表。
用mysqladmin -i10 precesslist extended-status监控MySQL的状态。
用MySQL GUI客户程序,你可以在不同的窗口内监控进程列表和状态。
使用mysqladmin debug获得有关锁定和性能的信息。
-------------------------------------------------- ------------------------------
11、优化SQL
扬SQL之长,其它事情交由应用去做。使用SQL服务器来做:
找出基于WHERE子句的行。
JOIN表
GROUP BY
ORDER BY
DISTINCT
不要使用SQL来做:
检验数据(如日期)
成为一只计算器
技巧:
明智地使用键码。
键码适合搜索,但不适合索引列的插入/更新。
保持数据为数据库第三范式,但不要担心冗余信息或这如果你需要更快的速度,创建总结表。
在大表上不做GROUP BY,相反创建大表的总结表并查询它。
UPDATE table set count=count+1 where key_column=constant非常快。
对于大表,或许最好偶尔生成总结表而不是一直保持总结表。
充分利用INSERT的默认值。
-------------------------------------------------- ------------------------------
12、优化表
MySQL拥有一套丰富的类型。你应该对每一列尝试使用最有效的类型。
ANALYSE过程可以帮助你找到表的最优类型:SELECT * FROM table_name PROCEDURE ANALYSE()。
对于不保存NULL值的列使用NOT NULL,这对你想索引的列尤其重要。
将ISAM类型的表改为MyISAM。
如果可能,用固定的表格式创建表。
不要索引你不想用的东西。
利用MySQL能按一个索引的前缀进行查询的事实。如果你有索引INDEX(a,b),你不需要在a上的索引。
不在长CHAR/VARCHAR列上创建索引,而只索引列的一个前缀以节省存储空间。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
对每个表使用最有效的表格式。
在不同表中保存相同信息的列应该有同样的定义并具有相同的列名。
-------------------------------------------------- ------------------------------
13、MySQL如何次存储数据
数据库以目录存储。
表以文件存储。
列以变长或定长格式存储在文件中。对BDB表,数据以页面形式存储。
支持基于内存的表。
数据库和表可在不同的磁盘上用符号连接起来。
在Windows上,MySQL支持用.sym文件内部符号连接数据库。
-------------------------------------------------- ------------------------------
14、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表大小。
-------------------------------------------------- ------------------------------
15、MySQL扩展/优化-提供更快的速度
使用优化的表类型(HEAP、MyIASM或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 (常数列表) 高度优化。
GET_LOCK()/RELEASE_LOCK()
LOCK TABLES
INSERT和SELECT可同时运行。
UDF函数可装载进一个正在运行的服务器。
压缩只读表。
CREATE TEMPORARY TABLE
CREATE TABLE .. SELECT
带RAID选项的MyIASM表将文件分割成很多文件以突破某些文件系统的2G限制。
Delay_keys
复制功能
-------------------------------------------------- ------------------------------
16、MySQL何时使用索引
对一个键码使用>, >=, =, 1 and key_part1 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和范围类型提示一个潜在的问题。
-------------------------------------------------- ------------------------------
18、学会使用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或mysqladmin中用KILL来杀死溜掉的线程。
-------------------------------------------------- ------------------------------
19、MySQL应避免的事情
用删掉的行更新或插入表,结合要耗时长的SELECT。
在能放在WHERE子句中的列上用HAVING。
不使用键码或键码不够唯一而进行JOIN。
在不同列类型的列上JOIN。
在不使用=匹配整个键码时使用HEAP表。
在MySQL监控程序中忘记在UPDATE或DELETE中使用一条WHERE子句。如果想这样做,使用mysql客户程序的--i-am-a-dummy选项。
-------------------------------------------------- ------------------------------
20、MySQL各种锁定
内部表锁定
LOCK TABLES(所有表类型适用)
GET LOCK()/RELEASE LOCK()
页面锁定(对BDB表)
ALTER TABLE也在BDB表上进行表锁定
LOCK TABLES允许一个表有多个读者和一个写者。
一般WHERE锁定具有比READ锁定高的优先级以避免让写入方干等。对于不重要的写入方,可以使用LOW_PRIORITY关键字让锁定处理器优选读取方。
UPDATE LOW_PRIORITY SET value=10 WHERE id=10;
-------------------------------------------------- ------------------------------
21、给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使用/忽略列出的索引。
-------------------------------------------------- ------------------------------
22、一般技巧
使用短主键。联结表时使用数字而非字符串。
当使用多部分键码时,第一部分应该时最常用的部分。
有疑问时,首先使用更多重复的列以获得更好地键码压缩。
如果在同一台机器上运行MySQL客户和服务器,那么在连接MySQL时则使用套接字而不是TCP/IP(这可以提高性能7.5%)。可在连接MySQL服务器时不指定主机名或主机名为localhost来做到。
如果可能,使用--skip-locking(在某些OS上为默认),这将关闭外部锁定并将提高性能。
使用应用层哈希值而非长键码:
SELECT *
FROM table_name
WHERE hash=MD5(concat(col1,col2))
AND col_1='constant'
AND col_2='constant'
在文件中保存需要以文件形式访问的BLOB,在数据库中只保存文件名。
删除所有行比删除一大部分行要快。
如果SQL不够快,研究一下访问数据的较底层接口。
发表评论
-
9-socket的实践到内核--client调用connect
2012-01-20 10:50 8519-socket的实践到内核--client调用connect ... -
Kobject相关
2012-01-20 10:49 495Kobject相关 2010年06月24日 作者:李强, ... -
一个操作系统的实现实验之3.2.1
2012-01-20 10:49 754一个操作系统的实现实 ... -
基于at91rm9200的arm平台 kgdb+linux内核调试
2012-01-20 10:49 1213基于at91rm9200的arm平台 kgdb+linux内核 ... -
mysql (error based) blind sqli cheat sheet_2012到了!!!
2012-01-19 15:48 710mysql (error based) blind sqli ... -
FastCGI Error 2147467259 (0x80004005)
2012-01-19 15:48 1837FastCGI Error 2147467259 (0x800 ... -
复制slave-skip-errors及error查看
2012-01-19 15:48 1400复制slave-skip-errors及error查看 20 ... -
error C2332: “struct”: 缺少标记名
2012-01-19 15:48 4947error C2332: “struct”: 缺少标记名 2 ... -
HTTP Error 503. The service is unavailable
2012-01-19 15:48 1029HTTP Error 503. The service is ... -
<改文>拽少爷的校花女友<33>
2012-01-17 05:30 630拽少爷的校花女友 2012 ... -
如果允许.
2012-01-17 05:30 579如果允许. 2012年01月12日 来自喧嚣世界的少女 ... -
―挑战你的极限―(短篇医院感人鬼故事两则)
2012-01-17 05:30 1087―挑战你的极限―(短篇 ... -
中国人的用餐礼仪~~~有空大家看看,蛮受用的哦
2012-01-16 04:26 631中国人的用餐礼仪~~~有空大家看看,蛮受用的哦 2009年0 ... -
汉服宣传资料
2012-01-16 04:26 635汉服宣传资料 2011年12月28日 什么是汉服? ... -
jjk
2012-01-16 04:26 521jjk 2011年12月25日 ... -
中秋节的来历
2012-01-16 04:25 730中秋节的来历 2011年12 ... -
儒家思想
2012-01-16 04:25 723儒家思想 2012年01月14日 ...
相关推荐
MySQL was one of the first open source programs to show the world the potential of developing a business around open source, and was a pioneer of the dual licensing business model. The book has been ...
基本指南和大量的库和工具带有前缀“my”已经有10年以上,而且不管怎样,MySQL AB创始人之一的Monty Widenius的女儿也叫My。这两个到底是哪一个给出了MySQL这个名字至今依然是个迷,包括开发者在内也不知道。 ...
MySQL History MySQL history goes back to 1979 when Monty Widenius, working for a small companycalled TcX, created a reporting tool written in BASIC that ran on a 4 Mhzcomputer with 16 KB RAM....
monty
Good probability problem
资源分类:Python库 所属语言:Python 资源全名:monty-3.0.3.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
目前这本手册有Texinfo、普通文本、Info、HTML、PostScript和 PDF 等格式的版本。... <br>本手册由David Axmark, Michael (Monty) Widenius, Paul DuBois and Kim Aldale维护。 而其他的贡献者,见C MySQL贡献者。
monty hall soln is here
Monty Hall Problem.py
Monty Program works to hire the best and brightest developers in the industry, work closely and cooperatively with the larger community of users and developers in the true spirit of Free and open ...
Monty Hall问题:有三扇门,门后分别放有一辆汽车和两只羊。你有机会任意选择其中一扇门,并得到门后的礼物。当你选择了一扇门后,主持人并不直接打开这扇门,而是打开另外一扇放有羊的门给你看。此时,给你一次改变...
一个程序,用于验证在 Monty Hall 问题中开关门在统计上是有益的。 ###尝试一下! 要尝试一下,请在终端窗口中运行以下命令行(“$”除外): $ git clone https://github.com/mikost/monty-hall-simulation ...
MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。MySQL 是以公司创始人 Monty Widenius’s daughter...
在此阶段,您可以在MySQL和SQL Server数据库上保存/删除/搜索。 特征 少SQL,少xml的表到类的映射 参加课程 关系1-1和1-n 级联效果进行保存/删除 MySQL和SQL服务器支持 程式码范例 // Class Definition and ...
蒙蒂Monty是python模块。 它提供了用于持久数据存储的机制,可以将数据保存为纯文本文件或sqlite DB中的数据。 以下是montydb和montyfile模块的测试文件: 测试数据库模块: testmontydb.py 从montydb导入Montydb...
一个mvc模式的实验,模拟monty hall游戏
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’ IDENTIFIED BY ‘some_pass’ WITH GRANT OPTION; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’%’ IDENTIFIED BY ‘some_pass’ WITH ...
在被甲骨文公司收购后,现在属于甲骨文公司(Oracle) 旗下产品。Oracle 大幅调涨MySQL商业版的售价,因此导致自由软件社区们对于Oracle是否还会持续支持MySQL社区版有所隐忧。 MySQL 的创始人就是之前那个叫 Monty ...
Monty Mole这是一个2D平台街机游戏,基于ZX Spectrum上编写的原始游戏。 矿工们罢工了,你需要热量。 您的目标是通过避开矿井中的所有讨厌生物,帮助蒙蒂收集所有煤炭。