MySQL的性能分析

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入 MySQL数据库性能优化是本文的主要核心，将从数据库的优化设计，到具体的操作。好的优化能使服务器性能提升较大的空间，希望本文对大家有所帮助。 1. MySQL性能优化简介 在Web应用程序体系架构中，数

欢迎进入Linux社区论坛，与200万技术人员互动交流 >>进入

　　MySQL数据库性能优化是本文的主要核心，将从数据库的优化设计，到具体的操作。好的优化能使服务器性能提升较大的空间，希望本文对大家有所帮助。

　　1. MySQL性能优化简介

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

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

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

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

　　2.1 Schema Design

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

　　2.1.1 标准化

　　标准化是在数据库中组织数据的过程。其中包括，根据设计规则创建表并在这些表间建立关系；通过取消冗余度与不一致相关性，该设计规则可以同时保护数据并提高数据的灵活性。通常数据库标准化是让数据库设计符合某一级别的范式，通常满足第三范式即可。也有第四范式（也称为 Boyce Codd范式，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作为建。例如IP address等

　　5） Reference by PRIMARY/UNIQUE KEY.MySQL可以优化这种操作，例如：

　　java 代码

　　select city_name from city,state where state_id=state.id and state.code='CA'" converted to "select city_name from city where state_id=12

　　2.1.2 数据类型

　　最基本的优化之一就是使表在磁盘上占据的空间尽可能小。这能带来性能非常大的提升，因为数据小，磁盘读入较快，并且在查询过程中表内容被处理所占用的内存更少。同时，在更小的列上建索引，索引也会占用更少的资源。

　　可以使用下面的技术可以使表的性能更好并且使存储空间最小：

　　1） 使用正确合适的类型，不要将数字存储为字符串。

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

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

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

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

　　6） 使用sample character set,例如latin1.尽量少使用utf-8,因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用 latin1,例如mail,url等。

　　2.1.3 索引

　　所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。使用索引应该注意以下几点：

　　1） MySQL只会使用前缀，例如key（a, b） …where b=5 将使用不到索引。

　　2） 要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。

　　3） 在Unique列上定义Unique index.

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

　　5） 在Btree index中（InnoDB使用Btree），可以在需要排序的列上建立索引。

　　6） 避免重复的索引。

　　7） 避免在已有索引的前缀上建立索引。例如：如果存在index（a,b）则去掉index（a）。

　　8） 控制单个索引的长度。使用key（name（8））在数据的前面几个字符建立索引。

　　9） 越是短的键值越好，最好使用integer.

　　10） 在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。

　　11） 相近的键值比随机好。Auto_increment就比uuid好。

　　12） Optimize table可以压缩和排序index,注意不要频繁运行。

　　13） Analyze table可以更新数据。

　　2.2 Designing queries

　　查询语句的优化是一个Case by case的问题，不同的sql有不同的优化方案，在这里我只列出一些通用的技巧。

　　1） 在有index的情况下，尽量保证查询使用了正确的index.可以使用EXPLAIN select …查看结果，分析查询。

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

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

　　3. 服务器端优化

　　3.1 MySQL安装

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

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

[1] [2] [3]