互联网时代,数据库的操作越来越成为整个应用的性能瓶颈。当我们去设计数据库表结构,操作数据库时(尤其是查表时的SQL语句),我们都需要注意性能问题

SQL语句分类

SQL 语句主要可以划分为以下 3 个类别。

  • DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。

  • DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查)

  • DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

1. 使用查询缓存

大多数MySQL服务器都开启了查询缓存,当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了

某些查询语句会让MySQL不使用缓存。请看下面的示例:

// 查询缓存不开启
$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'");

MySQL对CURDATE()、 NOW() 和 RAND() 等SQL函数都不会开启查询缓存,因为函数的返回是易变的,所以需要使用变量来代替MySQL的函数,从而开启缓存

2. 使用EXPLAIN分析SELECT查询

使用EXPLAIN关键字可以让你知道MySQL如何处理SQL语句的,例如索引主键被如何利用的,数据表如何被搜索和排序的……等等,帮你分析你的查询语句或是表结构的性能瓶颈。

3. 当只要一行数据时,使用LIMIT 1

某些场景下,你知道查询只会有一条结果,或者只需要判断是否存在符合条件的结果,在这种情况下,加上LIMIT 1可以增加性能,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查找下一条符合条件的数据

4. 千万不要ORDER BY RAND()

当你想打乱返回的数据时,这种方式会让数据库性能呈指数级的下降。这里的问题:MySQL会执行RAND函数(耗CPU),并且是对每一行记录进行,然后再排序

5. 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议,一定要注意,这只是建议,只有当表的数据越来越多时,建议才会变得准确

6. 固定长度的表会更快

  • 如果表中所有字段都是“固定长度”的,例如,没有VARCHAR,TEXT,BLOB等类型
  • 固定长度的表会提高性能,很容易计算下一个数据的偏移量,读取也会更快
  • 如果字段不是定长,每次找下一条,需要程序找到主键
  • 固定长度的表更容易被缓存和重建
  • 缺点:固定长度字段会浪费空间,无论用不用,都要分配那么多空间

7. 垂直分割

“垂直分割”是把表按列变成几张表的方法,降低表的复杂度和字段的目的 - 经常修改的字段和很少改动的字段,进行分表 - 注意:分出去的字段所形成的表,不要经常去join它们,否则性能会比不分割时还要差,而且是数量级的下降

8. 拆分大的DELETE或INSERT语句

这两个操作会锁表,高访问量的站点,大的DELETE或INSERT会让整个数据库无法响应,最后导致整台服务器挂掉

使用LIMIT条件,进行拆分:

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了,退出!
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

9. 选择正确的存储引擎

  • MyISAM适合大量查询,有大量写操作不是很好,表级锁,MyISAM对于SELECT COUNT(*)超快
  • InnoDB支持行锁,写操作多时更优秀,支持事务,支持外键