MySQL 的性能（下篇）—— 性能优化方法

概述简介 文中内容均为阅读前辈的文章所整理而来，参考文章已在最后全指明 本文分为上下两篇： 上篇： "MySQL 的 SQL 执行分析" 下篇： "MySQL 性能优化&qu 简介

文中内容均为阅读前辈的文章所整理而来，参考文章已在最后全指明

本文分为上下两篇：

上篇：MySQL 的 SQL 执行分析下篇：MySQL 性能优化

下面为下篇内容，分为以下部分：

一、创建表时的性能优化二、设计表时的性能优化三、优化 sql 语句四、其它一、创建表时的性能优化1. 永远为每张表设置一个 ID

每张表都应该设置一个 ID 字段为主键，该主键应为 INT 或 UNSIGNED 类型，并设置上自动增加的 auto_INCREMENT 标志。因为使用 VARCHAR 类型的主键，会使得性能下降。

这里，只有一个情况是例外，那就是 “关联表” 的 “外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做 “外键”。比如：有一个 “学生表” 有学生的 ID，有一个 “课程表” 有课程 ID，那么，“成绩表” 就是 “关联表” 了，其关联了学生表和课程表，在成绩表中，学生 ID 和课程 ID 叫 “外键” 其共同组成主键。

2. 为搜索字段建索引

详情见我之前的文章：MySQL 索引

3. 使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 tinyint，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如 “国家”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

ENUM 是 MysqL 数据库特有的字段类型，使用后会影响迁移到其它数据库。所以，如果以后又改数据库的情况，一定要慎用。

4. 尽可能的使用 NOT NulL

应该总是让你的字段保持 NOT NulL，因为这样节省空间（NulL 也是需要空间的）。

5. 把IP地址存成 UNSIGNED INT

如果使用整形来存放 IP 而不是 VARCHAR(15) 字段，节省了很多的空间（需要写一个 IP 转换的函数）。

二、设计表时的性能优化1. 选择正确的存储引擎

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写 *** 作并不是很好。甚至你只是需要 update 一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法 *** 作直到读 *** 作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写 *** 作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

2. 固定长度的表会更快

表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MysqL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为 MysqL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。

3. 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库 *** 作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

三、优化 sql 语句1. 使用查询缓存1.1 查看是否开启缓存：

MysqL> select @@query_cache_type;+--------------------+| @@query_cache_type |+--------------------+| ON                 |+--------------------+

开启缓存，修改 my.cnf，在末尾加入，重启MysqL生效：

query_cache_type = 1query_cache_size = 600000

1.2 为查询缓存优化你的查询

// 查询缓存不开启r = MysqL_query("SELECT username FROM user WHERE signup_date >= CURDATE()");// 开启查询缓存today = date("Y-m-d");r = MysqL_query("SELECT username FROM user WHERE signup_date >= '%s'" % today);

上面两条 sql 语句的差别就是 CURDATE()，MysqL 的查询缓存对这个函数不起作用。所以，像 Now() 和 RAND() 或是其它的诸如此类的 sql 函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替 MysqL 的函数，从而开启缓存。

2. 当只要一行数据时使用 liMIT 1

在这种情况下，加上 liMIT 1 可以增加性能。这样一样，MysqL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

3. 在 JOIN 表的时候使用相当类型的例，并将其索引

如果有很多 JOIN 的 *** 作，JOIN 的字段应该加索引，同时保证这些字段的类型一致。

4. 避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。所以，应该养成需要什么就取什么的好的习惯。

5. 拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大量的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的 *** 作让你的整个网站停止相应。因为这两个 *** 作是会锁表的，表一锁住了，别的 *** 作都进不来了。

执行这种大量的 DELETE 和 INSERT，可以分成几部分执行，没执行一部分就暂停一下再执行。

四、其它1. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道 MysqL 是如何处理你的 sql 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

查看 rows 列可以让我们找到潜在的性能问题。

2. 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MysqL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议（只是建议）。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

MysqL> select * from charac procedure analyse()*************************** 1. row ***************************FIEld_name: world.charac.characMin_value: AMax_value: EMin_length: 1Max_length: 1EmptIEs_or_zeros: 0Nulls: 0Avg_value_or_avg_length: 1.0000Std: NulLOptimal_fIEldtype: ENUM('A','B','C','D','E') NOT NulL1 row in set (0.00 sec)

3. 使用连接池和 ORM参考MYSQL性能优化的最佳20+条经验MySQL索引原理及慢查询优化 总结

以上是内存溢出为你收集整理的MySQL 的性能（下篇）—— 性能优化方法全部内容，希望文章能够帮你解决MySQL 的性能（下篇）—— 性能优化方法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。