MySQL性能优化

1.简介 .............................................................................................................................................................................................. 1 2.数据库设计和查询优化 .............................................................................................................................................................. 1 3.服务器端优化 .............................................................................................................................................................................. 3 4.存储引擎优化 .............................................................................................................................................................................. 3 5.缓存 .............................................................................................................................................................................................. 6 6.Reference ................................................................................................................................................................................... 6

1.简介

在Web应用程序体系架构中，数据持久层（通常是一个关系数据库）是关键的核心部分，它对系统的性能有非常重要的影响。MySQL是目前使用最多的开源数据库，但是MySQL数据库的默认设置性能非常的差，仅仅是一个玩具数据库。因此在产品中使用MySQL数据库必须进行必要的优化。

优化是一个复杂的任务，本文描述MySQL相关的数据库设计和查询优化，服务器端优化，存储引擎优化。

2.数据库设计和查询优化

在MySQLServer性能调优中，首先要考虑的就是DatabaseSchema设计，这一点是非常重要的。一个糟糕的Schema设计即使在性能调优的MySQLServer上运行，也会表现出很差的性能；和Schema相似，查询语句的设计也会影响MySQL的性能，应该避免写出低效的SQL查询。这一节将详细讨论这两方面的优化。

2.1SchemaDesign

Schema的优化取决于将要运行什么样的query，不同的query会有不同的Schema优化方案。2.2节将介绍QueryDesign的优化。Schema设计同样受到预期数据集大小的影响。Schema设计时主要考虑：标准化，数据类型，索引。

2.1.1标准化

标准化是在数据库中组织数据的过程。其中包括，根据设计规则创建表并在这些表间建立关系；通过取消冗余度与不一致相关性，该设计规则可以同时保护数据并提高数据的灵活性。通常数据库标准化是让数据库设计符合某一级别的范式，通常满足第三范式即可。也有第四范式（也称为BoyceCodd范式，BCNF)）与第五范式存在，但是在实际设计中很少考虑。忽视这些规则可能使得数据库的设计不太完美，但这不应影响功能。 标准化的特点：

1)所有的“对象”都在它自己的table中，没有冗余。 2)数据库通常由E-R图生成。

3)简洁，更新属性通常只需要更新很少的记录。 4)Join操作比较耗时。

5)Select，sort优化措施比较少。

6)适用于OLTP应用。 非标准化的特点：

1)在一张表中存储很多数据，数据冗余。

2)更新数据开销很大，更新一个属性可能会更新很多表，很多记录。 3)在删除数据是有可能丢失数据。 4)Select，order有很多优化的选择。 5)适用于DSS应用。

标准化和非标准化都有各自的优缺点，通常在一个数据库设计中可以混合使用，一部分表格标准化，一部分表格保留一些冗余数据：

1)对OLTP使用标准化，对DSS使用非标准化

2)使用物化视图。MySQL不直接支持该数据库特性，但是可以用MyISAM表代替。 3)冗余一些数据在表格中，例如将ref_id和name存在同一张表中。但是要注意更新问题。 4)对于一些简单的对象，直接使用value作为键。例如IPaddress等 5)ReferencebyPRIMARY/UNIQUEKEY。MySQL可以优化这种操作，例如： java代码

Selectcity_name fromcity,state

wherestate_id=state.idandstate.code=‘CA’ convertedto：

selectcity_namefromcitywherestate_id=12

2.1.2数据类型

最基本的优化之一就是使表在磁盘上占据的空间尽可能小。这能带来性能非常大的提升，因为数据小，磁盘读入较快，并且在查询过程中表内容被处理所占用的内存更少。同时，在更小的列上建索引，索引也会占用更少的资源。 可以使用下面的技术可以使表的性能更好并且使存储空间最小： 1)使用正确合适的类型，不要将数字存储为字符串。

2)尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。

3)尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。

4)如果可能，声明列为NOTNULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免默认地在所有列上有它。

5)对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。

6)使用samplecharacterset，例如latin1。尽量少使用utf-8，因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。

2.1.3索引

所有MySQL列类型都可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。使用索引应该注意以下几点： 1)MySQL只会使用前缀，例如key(a,b)…whereb=5将使用不到索引。

2)要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。 3)在Unique列上定义Uniqueindex。

4)避免建立使用不到的索引。

5)在Btreeindex中（InnoDB使用Btree），可以在需要排序的列上建立索引。 6)避免重复的索引。

7)避免在已有索引的前缀上建立索引。例如：如果存在index（a，b）则去掉index（a）。 8)控制单个索引的长度。使用key（name（8））在数据的前面几个字符建立索引。 9)越是短的键值越好，最好使用integer。

10)在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。 11)相近的键值比随机好。Auto_increment就比uuid好。 12)Optimizetable可以压缩和排序index，注意不要频繁运行。 13)Analyzetable可以更新数据。

2.2Designingqueries

查询语句的优化是一个Casebycase的问题，不同的sql有不同的优化方案，在这里我只列出一些通用的技巧。 1)在有index的情况下，尽量保证查询使用了正确的index。可以使用EXPLAINselect…查看结果，分析查询。

2)查询时使用匹配的类型。例如select*fromawhereid=5，如果这里id是字符类型，同时有index，这条查询则使用不到index，会做全表扫描，速度会很慢。正确的应该是…whereid=”5”，加上引号表明类型是字符。

3)使用--log-slow-queries–long-query-time=2查看查询比较慢的语句。然后使用explain分析查询，做出优化。

3.服务器端优化

3.1MySQL安装

MySQL有很多发行版本，最好使用MySQLAB发布的二进制版本。也可以下载源代码进行编译安装，但是编译器和类库的一些bug可能会使编译完成的MySQL存在潜在的问题。

如果安装MySQL的服务器使用的是Intel公司的处理器，可以使用intelc++编译的版本，在LinuxWorld2005的一篇PPT中提到，使用intelC++编译器编译的MySQL查询速度比正常版本快30%左右。Intelc++编译版本可以在MySQL官方网站下载。

3.2服务器设置优化

MySQL默认的设置性能很差，所以要做一些参数的调整。这一节介绍一些通用的参数调整，不涉及具体的存储引擎（主要指MyISAM，InnoDB，相关优化在4中介绍）。

--character-set：如果是单一语言使用简单的characterset例如latin1。尽量少用Utf-8，utf-8占用空间较多。 --memlock：锁定MySQL只能运行在内存中，避免swapping，但是如果内存不够时有可能出现错误。

--max_allowed_packet：要足够大，以适应比较大的SQL查询，对性能没有太大影响，主要是避免出现packet错误。 --max_connections：server允许的最大连接。太大的话会出现outofmemory。

--table_cache：MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。 --query_cache_size：用于缓存查询的内存大小。

--datadir：mysql存放数据的根目录，和安装文件分开在不同的磁盘可以提高一点性能。

4.存储引擎优化

MySQL支持不同的存储引擎，主要使用的有MyISAM和InnoDB。 4.1MyISAM

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非配置MySQL默认使用另外一个引擎。

4.1.1MyISAM特性 4.1.1.1MyISAMProperties 1)不支持事务，宕机会破坏表 2)使用较小的内存和磁盘空间

3)基于表的锁，并发更新数据会出现严重性能问题 4)MySQL只缓存Index，数据由OS缓存

4.1.1.2TypicalMyISAMusages 1)日志系统

2)只读或者绝大部分是读操作的应用 3)全表扫描 4)批量导入数据

5)没有事务的低并发读/写

4.1.2MyISAM优化要点

1)声明列为NOTNULL，可以减少磁盘存储。

2)使用optimizetable做碎片整理，回收空闲空间。注意仅仅在非常大的数据变化后运行。 3)Deleting/updating/adding大量数据的时候禁止使用index。使用ALTERTABLEtDISABLEKEYS。 4)设置myisam_max_[extra]_sort_file_size足够大，可以显著提高repairtable的速度。

4.1.3MyISAMTableLocks 1)避免并发insert，update。

2)可以使用insertdelayed，但是有可能丢失数据。 3)优化查询语句。 4)水平分区。 5)垂直分区。

6)如果都不起作用，使用InnoDB。

4.1.4MyISAMKeyCache

1)设置key_buffer_sizevariable。MyISAN最主要的cache设置，用于缓存MyISAM表格的index数据，该参数只对MyISAM有影响。通常在只使用MyISAM的Server中设置25-33%的内存大小。 2)可以使用几个不同的KeyCaches（对一些hotdata）。 a)SETGLOBALtest.key_buffer_size=512*1024; b)CACHE INDEX t1.i1, t2.i1, t3 IN test;

2)Preloadindex到Cache中可以提高查询速度。因为preloadingindex是顺序的，所以非常快。 a)LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES；

4.2InnoDB

InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB提供rowlevellock，并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩

大锁定的需要，因为在InnoDB中rowlevellock适合非常小的空间。InnoDB也支持FOREIGNKEY约束。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。 InnoDB是为在处理巨大数据量时获得最大性能而设计的。它的CPU使用效率非常高。

InnoDB存储引擎已经完全与MySQL服务器整合，InnoDB存储引擎为在内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何大小，即使在文件尺寸被限制为2GB的操作系统上。 许多需要高性能的大型数据库站点上使用了InnoDB引擎。著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix,Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。

4.2.1InnoDB特性 4.2.1.1InnoDBProperties 1)支持事务，ACID，外键。 2)Rowlevellocks。 3)支持不同的隔离级别。

4)和MyISAM相比需要较多的内存和磁盘空间。 5)没有键压缩。

6)数据和索引都缓存在内存hash表中。

4.2.1.2InnoDBGoodFor 1)需要事务的应用。 2)高并发的应用。 3)自动恢复。

4)较快速的基于主键的操作。

4.2.2InnoDB优化要点

1)尽量使用short，integer的主键。

2)Load/Insert数据时按主键顺序。如果数据没有按主键排序，先排序然后再进行数据库操作。

3)在Load数据是为设置SETUNIQUE_CHECKS=0，SETFOREIGN_KEY_CHECKS=0，可以避免外键和唯一性约束检查的开销。 4)使用prefixkeys。因为InnoDB没有key压缩功能。

4.2.3InnoDB服务器端设定

innodb_buffer_pool_size：这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点，在内存容量允许的情况下面设置比InnoDBtablespaces大10%的内存大小。

innodb_data_file_path：指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如：innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。如果磁盘满了，需要在另外的磁盘上面增加一个数据文件。

innodb_autoextend_increment:默认是8M,如果一次insert数据量比较多的话,可以适当增加.

innodb_data_home_dir：放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。

innodb_log_file_size：该参数决定了recoveryspeed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度。

innodb_log_buffer_size：磁盘速度是很慢的，直接将log写道磁盘会影响InnoDB的性能，该参数设定了logbuffer的大小，一般4M。如果有大的blob操作，可以适当增大。

innodb_flush_logs_at_trx_commit=2：该参数设定了事务提交时内存中log信息的处理。

1)=1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。TrulyACID。速度慢。 2)=2时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。

3)=0时，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务。

innodb_file_per_table：可以存储每个InnoDB表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED:如果应用程序可以运行在READ-COMMITED隔离级别，做此设定会有一定的性能提升。

innodb_flush_method：设置InnoDB同步IO的方式： 1)Default–使用fsync（）。

2)O_SYNC以sync模式打开文件，通常比较慢。

3)O_DIRECT，在Linux上使用DirectIO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和doublebuffering（mysqlbuffering和OSbuffering）。

innodb_thread_concurrency：InnoDBkernel最大的线程数。 1)最少设置为(num_disks+num_cpus)*2。 2)可以通过设置成1000来禁止这个限制

5.缓存

缓存有很多种，为应用程序加上适当的缓存策略会显著提高应用程序的性能。由于应用缓存是一个比较大的话题，所以这一部分还需要进一步调研。

6.Reference

1) http://www.mysqlperformanceblog.com/

2) Advanced MySQL Performance Optimization, Peter Zaitsev, Tobias Asplund, MySQL Users Conference 2005 3) Improving MySQL Server Performance with Intel C++ Compiler，Peter Zaitsev，Linux World 2005 4) MySQL Performance Optimization, Peter Zaitsev, Percona Ltd, OPEN SOURCE DATABASE CONFERENCE 2006

5) MySQL Server Settings Tuning, Peter Zaitsev, co-founder, Percona Ltd, 2007 6) MySQL Reference Manual