首页 » mysql » 秘籍-26条MySQL性能优化的最佳经验

秘籍-26条MySQL性能优化的最佳经验

 

今天,数据库操作越来越成为应用的性能瓶颈。

对于Web应用尤其明显。

关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情

当我们去设计数据库表结构、查询表数据时,都应该注意性能问题。

这里,我们不会讲过多的SQL语句的优化,这里我们主要针对MySQL数据库的设计优化

1 使用查询缓存

大多数的MySQL服务器都开启了查询缓存(QueryCache)。

这是提高性最有效的方法之一,查询缓存由MySQL数据库引擎自动处理。

当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,

这样,后续的相同的查询就不用操作表,而直接访问缓存结果了

程序员很容易忽略这个功能,不经意间就会写了一些用不上缓存的SQL语句。

如下示例:

// 查询缓存不被启用
$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 >= \'$today\'");

上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。

所以,像 NOW() RAND() 或是其它的诸如此类的SQL函数,

都不会开启查询缓存,因为这些函数的返回是变数。

2 为查询加上 LIMIT

即使确定查询表只会有1条结果,加上 LIMIT 1 可以增加性能。

因为这样加上LIMIT后, MySQL数据库引擎在找到一条数据后就会停止搜索,而不是继续往查找。

如下示例,我们要查找是否有“中国”的用户。

// 没有效率的:
$r = mysql_query("SELECT * FROM user WHERE country = \'China\'");
if (mysql_num_rows($r) > 0) {
    // ...
}

// 有效率的:
$r = mysql_query("SELECT 1 FROM user WHERE country = \'China\' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

很明显,后面的会比前面的更有效率。

请注意,第一条中是select *,第二条是select 1

3 使用索引

索引是提高数据库查询性能最常用的方法。

索引可以令数据库查询快得多。

尤其是在查询语句当中包含有MAX()MIN()ORDER BY这些命令的时候,性能提高更为明显。

那该对哪些字段建立索引呢?

一般说来,索引应建立在那些将用于JOINWHERE判断和ORDER BY排序的字段上。

也就是说明,如果某个字段经常用来做搜索,那么,请为其建立索引吧。

注意,尽量不要对数据库中某个含有大量重复的值的字段建立索引。

对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况,

例如 :客户表中的province(省份)字段,在这样的字段上建立索引将不会有什么帮助。

相反,还有可能降低数据库的性能。

我们可以在创建表的时候同时创建合适的索引,也可以在之后使用ALTER TABLECREATE INDEX创建索引。

如下是LIKE条件查询有无索引的性能比较:

从上图你可以看到,搜索字串是:last_name LIKE \'a%\',创建索引的性能高处4倍左右。

另外,从MySQL5.6开始,INNODB和MyISAM存储引擎都支持全文索引和搜索。

全文索引在MySQL 中是一个FULLTEXT类型索引,可以根据实际情况使用。

需要注意的是,索引会使得INSERTUPDATE的变慢,

所以,在需要经常更改的字段,请考虑精简索引或者分表处理。

4 EXPLAIN 查询语句

使用 EXPLAIN 关键字可以通过了解SQL语句的执行过程,来分析性能瓶颈的缘由。

EXPLAIN 的查询结果还会展示索引主键如何被利用、数据表如何被搜索和排序……等等。

挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。

然后,我们会得到一张表格。

如下示例,没有为 group_id加索引的情况:

group_id 字段加上索引后的情况: