MySQL优化.docx

1、MySQL优化目录MySQL优化 11. 我们可以且应该优化什么？ 22. 优化硬件 23. 优化磁盘 24. 优化操作系统 35. 选择应用编程接口 36. 优化应用 37. 应该使用可移植的应用 48. 如果你需要更快的速度 49. 优化MySQLD 410. 编译和安装MySQL 511. 维护 512. 优化SQL 513. 不同SQL服务器的速度差别（以秒计） 614. 重要的MySQL启动选项 715. 优化表 716. MySQL如何次存储数据 717. MySQL表类型 818. MySQL行类型（专指IASM/MyIASM表） 819. MySQL缓存 820. MySQL表

2、高速缓存工作原理 921. MySQL扩展/优化-提供更快的速度 922. MySQL何时使用索引 1023. MySQL何时不使用索引 1024. 学会使用EXPLAIN 1025. 学会使用SHOW PROCESSLIST 1126. 如何知晓MySQL解决一条查询 1127. MySQL非常不错 1228. MySQL应避免的事情 1229. MySQL各种锁定 1230. 给MySQL更多信息以更好地解决问题的技巧 1231. 事务的例子 1332. 使用REPLACE的例子 1333. 一般技巧 1434. 使用MySQL 3.23的好处 1435. 正在积极开发的重要功能 14My

3、SQL优化(本文是Monty在OReilly Open Source Convention 2000大会上的演讲)11. 我们可以且应该优化什么？ 硬件 操作系统/软件库SQL服务器(设置和查询)应用编程接口(API)应用程序 12. 优化硬件 如果你需要庞大的数据库表(2G)，你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。如果不使用事务安全(transaction-sa

4、fe)的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。延迟与吞吐量同样重要。13. 优化磁盘 为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。低寻道时间对数据库磁盘非常重要。对与大表，你可以估计你将需要：log(行数)/log(索引块长度/3*2/(键码长度 + 数据指针长度)+1 次寻到才能找到一行。对于有500000行的表，索引Mediun int类型的列，需要：log(500000) / log(1024/3*2/(3 + 2)+1=4次寻道。上

5、述索引需要500000*7*3/2=5.2M的空间。实际上，大多数块将被缓存，所以大概只需要1-2次寻道。然而对于写入（如上），你将需要4次寻道请求来找到在哪里存放新键码，而且一般要2次寻道来更新索引并写入一行。对于非常大的数据库，你的应用将受到磁盘寻道速度的限制，随着数据量的增加呈N log N数据级递增。将数据库和表分在不同的磁盘上。在MySQL中，你可以为此而使用符号链接。条列磁盘(RAID 0)将提高读和写的吞吐量。带镜像的条列(RAID 0+1)将更安全并提高读取的吞吐量。写入的吞吐量将有所降低。不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID(除了RAID 0)。

6、在Linux上，在引导时对磁盘使用命令hdparm -m16 -d1以启用同时读写多个扇区和DMA功能。这可以将响应时间提高550%。在Linux上，用async (默认)和noatime挂载磁盘(mount)。对于某些特定应用，可以对某些特定表使用内存磁盘，但通常不需要。14. 优化操作系统 不要交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存。不要使用NFS磁盘(会有NFS锁定的问题)。增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。增加系统的进程和线程数量。如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道

7、上(Solaris)。使用支持大文件的文件系统(Solaris)。选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。15. 选择应用编程接口PERL：可在不同的操作系统和数据库之间移植。适宜快速原型。应该使用DBI/DBD接口。PHP：比PERL易学。使用比PERL少的资源。通过升级到PHP4可以获得更快的速度。C：MySQL的原生接口。较快并赋予更多的控制。低层，所以必须付出更多。C+：较高层次，给你更多的时间来编写应用。仍在开发中ODBC：运行在Windows和Unix上。几乎可在不同的SQL服务器间移植。较慢。MyODBC只是简单的直通驱动

8、程序，比用原生接口慢19%。有很多方法做同样的事。很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。问题成堆。Microsoft偶尔还会改变接口。不明朗的未来。(Microsoft更推崇OLE而非ODBC) JDBC：理论上可在不同的操作系统何时据库间移植。可以运行在web客户端。Python和其他：可能不错，可我们不用它们。16. 优化应用应该集中精力解决问题。在编写应用时，应该决定什么是最重要的：速度操作系统间的可移植性SQL服务器间的可移植性使用持续的连接。缓存应用中的数据以减少SQL服务器的负载。不要查询应用中不需要的列。不要使用SELECT * FROM table_n

9、ame. 测试应用的所有部分，但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。通过以一种模块化的方式进行，你应该能用一个快速“哑模块”替代找到的瓶颈，然后很容易地标出下一个瓶颈。如果在一个批处理中进行大量修改，使用LOCK TABLES。例如将多个UPDATES或DELETES集中在一起。17. 应该使用可移植的应用Perl DBI/DBDODBCJDBCPython(或其他有普遍SQL接口的语言)你应该只使用存在于所有目的SQL服务器中或可以很容易地用其他构造模拟的SQL构造。上的Crash-me页可以帮助你。为操作系统/SQL服务器编写包装程序来提供缺少的功能。18. 如果你需要

10、更快的速度应该找出瓶颈(CPU、磁盘、内存、SQL服务器、操作系统、API或应用)并集中全力解决。使用给予你更快速度/灵活性的扩展。逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。优化表布局和查询。使用复制以获得更快的选择(select)速度。如果你有一个慢速的网络连接数据库，使用压缩客户/服务器协议。不要害怕时应用的第一个版本不能完美地移植，在你解决问题时，你总是可以在以后优化它。19. 优化MySQLD 挑选编译器和编译选项。为你的系统寻找最好的启动选项。通读MySQL参考手册并阅读Paul DuBios的MySQL一书。(已有中文版-译注)多用EXPLAIN SE

11、LECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。了解查询优化器的工作原理。优化表的格式。维护你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE) 使用MySQL的扩展功能以让一切快速完成。如果你注意到了你将在很多场合需要某些函数，编写MySQL UDF函数。不要使用表级或列级的GRANT，除非你确实需要。购买MySQL技术支持以帮助你解决问题:) 110. 编译和安装MySQL通过位你的系统挑选可能最好的编译器，你通常可以获得10-30%的性能提高。在Linux/Intel平台上，用pgcc(gcc的奔腾芯片优化

12、版)编译MySQL。然而，二进制代码将只能运行在Intel奔腾CPU上。对于一种特定的平台，使用MySQL参考手册上推荐的优化选项。一般地，对特定CPU的原生编译器(如Sparc的Sun Workshop)应该比gcc提供更好的性能，但不总是这样。用你将使用的字符集编译MySQL。静态编译生成mysqld的执行文件(用-with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最终的执行文件。注意，既然MySQL不使用C+扩展，不带扩展支持编译MySQL将赢得巨大的性能提高。如果操作系统支持原生线程，使用原生线程(而不用mit-pthreads)。用

13、MySQL基准测试来测试最终的二进制代码。111. 维护如果可能，偶尔运行一下OPTIMIZE table，这对大量更新的变长行非常重要。偶尔用myisamchk -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。 如果有碎片文件，可能值得将所有文件复制到另一个磁盘上，清除原来的磁盘并拷回文件。如果遇到问题，用myisamchk或CHECK table检查表。用mysqladmin -i10 precesslist extended-status监控MySQL的状态。用MySQL GUI客户程序，你可以在不同的窗口内监控进程列表和状态。使用mysqladmin debug获得有关锁定

14、和性能的信息。112. 优化SQL扬SQL之长，其它事情交由应用去做。使用SQL服务器来做： 找出基于WHERE子句的行。JOIN表 GROUP BY ORDER BYDISTINCT 不要使用SQL来做：检验数据(如日期)成为一只计算器 技巧： 明智地使用键码。键码适合搜索，但不适合索引列的插入/更新。保持数据为数据库第三范式，但不要担心冗余信息或这如果你需要更快的速度，创建总结表。在大表上不做GROUP BY，相反创建大表的总结表并查询它。UPDATE table set count=count+1 where key_column=constant非常快。对于大表，或许最好偶尔生成总结表

15、而不是一直保持总结表。充分利用INSERT的默认值。113. 不同SQL服务器的速度差别（以秒计）+-+-+-+ |通过键码读取2000000行： | NT | Linux | +-+-+-+ |mysql | 367 | 249 | +-+-+-+ |mysql_odbc | 464 | | +-+-+-+ |db2_odbc | 1206 | | +-+-+-+ |informix_odbc | 121126 | | +-+-+-+ |ms-sql_odbc | 1634 | | +-+-+-+ |oracle_odbc | 20800 | | +-+-+-+ |solid_odbc |

16、877 | | +-+-+-+ |sybase_odbc | 17614 | | +-+-+-+ +-+-+-+ |插入350768行： | NT | Linux | +-+-+-+ |mysql | 381 | 206 | +-+-+-+ |mysql_odbc | 619 | | +-+-+-+ |db2_odbc | 3460 | | +-+-+-+ |informix_odbc | 2692 | | +-+-+-+ |ms-sql_odbc | 4012 | | +-+-+-+ |oracle_odbc | 11291 | | +-+-+-+ |solid_odbc | 1801 |

17、| +-+-+-+ |sybase_odbc | 4802 | | +-+-+-+ 在上述测试中，MySQL配置8M高速缓存运行，其他数据库以默认安装运行。 114. 重要的MySQL启动选项back_log 如果需要大量新连接，修改它。thread_cache_size 如果需要大量新连接，修改它。key_buffer_size 索引页池，可以设成很大。bdb_cache_size BDB表使用的记录和键吗高速缓存。table_cache 如果有很多的表和并发连接，修改它。delay_key_write 如果需要缓存所有键码写入，设置它。log_slow_queries 找出需花大量时间的查

18、询。max_heap_table_size 用于GROUP BY sort_buffer 用于ORDER BY和GROUP BYmyisam_sort_buffer_size 用于REPAIR TABLEjoin_buffer_size 在进行无键吗的联结时使用。115. 优化表MySQL拥有一套丰富的类型。你应该对每一列尝试使用最有效的类型。ANALYSE过程可以帮助你找到表的最优类型：SELECT * FROM table_name PROCEDURE ANALYSE()。对于不保存NULL值的列使用NOT NULL，这对你想索引的列尤其重要。将ISAM类型的表改为MyISAM。如果可能，

19、用固定的表格式创建表。 不要索引你不想用的东西。利用MySQL能按一个索引的前缀进行查询的事实。如果你有索引INDEX(a,b)，你不需要在a上的索引。不在长CHAR/VARCHAR列上创建索引，而只索引列的一个前缀以节省存储空间。CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)对每个表使用最有效的表格式。在不同表中保存相同信息的列应该有同样的定义并具有相同的列名。116. MySQL如何次存储数据数据库以目录存储。表以文件存储。列以变长或定长格式存储在文件中。对BDB表，数据以页面形式存储。支持基

20、于内存的表。数据库和表可在不同的磁盘上用符号连接起来。在Windows上，MySQL支持用.sym文件内部符号连接数据库。MySQL表类型HEAP表：固定行长的表，只存储在内存中并用HASH索引进行索引。ISAM表：MySQL 3.22中的早期B-tree表格式。MyIASM：IASM表的新版本，有如下扩展： 二进制层次的可移植性。 NULL列索引。 对变长行比ISAM表有更少的碎片。 支持大文件。 更好的索引压缩。 更好的键吗统计分布。 更好和更快的auto_increment处理。来自Sleepcat的Berkeley DB(BDB)表：事务安全(有BEGIN WORK/COMMIT|RO

21、LLBACK)。117. MySQL行类型（专指IASM/MyIASM表）如果所有列是定长格式(没有VARCHAR、BLOB或TEXT)，MySQL将以定长表格式创建表，否则表以动态长度格式创建。定长格式比动态长度格式快很多并更安全。动态长度行格式一般占用较少的存储空间，但如果表频繁更新，会产生碎片。在某些情况下，不值得将所有VARCHAR、BLOB和TEXT列转移到另一个表中，只是获得主表上的更快速度。-利用myiasmchk（对ISAM，pack_iasm），可以创建只读压缩表，这使磁盘使用率最小，但使用慢速磁盘时，这非常不错。压缩表充分地利用将不再更新的日志表118. MySQL缓存1、

22、MySQL高速缓存（所有线程共享，一次性分配）键码缓存：key_buffer_size，默认8M。表缓存：table_cache，默认64。线程缓存：thread_cache_size，默认0。主机名缓存：可在编译时修改，默认128。内存映射表：目前仅用于压缩表。注意：MySQL没有运行高速缓存，而让操作系统处理。2、MySQL缓存区变量（非共享，按需分配）sort_buffer：ORDER BY/GROUP BYrecord_buffer：扫描表。join_buffer_size：无键联结 myisam_sort_buffer_size：REPAIR TABLEnet_buffer_leng

23、th:对于读SQL语句并缓存结果。tmp_table_size：临时结果的HEAP表大小。119. MySQL表高速缓存工作原理 每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件。如果表被两个线程使用或在同一条查询中使用两次，MyIASM将共享索引文件而是打开数据文件的另一个实例。如果所有在高速缓存中的表都在使用，缓存将临时增加到比表缓存尺寸大些。如果是这样，下一个被释放的表将被关闭。你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小。如果该值太高，你应该增大表高速缓存。120. MySQL扩展/优化-提供更快的速度 使用优化的表类型（

24、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可在某些情况下使用键码。 如果

25、只查询在一个索引中的列，将只使用索引树解决查询。联结一般比子查询快（对大多数SQL服务器亦如此）。LIMIT SELECT * from table1 WHERE a 10 LIMIT 10,20 DELETE * from table1 WHERE a 10 LIMIT 10foo IN (常数列表) 高度优化。 GET_LOCK()/RELEASE_LOCK()LOCK TABLESINSERT和SELECT可同时运行。UDF函数可装载进一个正在运行的服务器。压缩只读表。CREATE TEMPORARY TABLECREATE TABLE . SELECT带RAID选项的MyIASM表将文

26、件分割成很多文件以突破某些文件系统的2G限制。Delay_keys复制功能 121. MySQL何时使用索引 对一个键码使用, =, =, , 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

27、(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=1122. 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 k