思路

MySQL服务器调优思路

第一步要做的是确定问题的症结，而不是一上来就看表结构和查询语句。

开启profiling

MySQL默认会开启缓存池，本地调试的时候清除缓存的命令是：reset query cache。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
MariaDB [(none)]> show variables like '%profi%';  
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

MariaDB [(none)]> set profiling=on;

# 打开之后就可以查看每一次查询了
MariaDB [(none)]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00033615 | show variables like '%profi%' |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)

# 查看查询每一步的耗时
MariaDB [(none)]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000034 |
| Opening tables       | 0.000027 |
| After opening tables | 0.000003 |
| System lock          | 0.000002 |
| Table lock           | 0.000002 |
| After table lock     | 0.000004 |
| init                 | 0.000007 |
| optimizing           | 0.000010 |
| statistics           | 0.000014 |
| preparing            | 0.000006 |
| executing            | 0.000002 |
| Filling schema table | 0.000185 |
| executing            | 0.000002 |
| Sending data         | 0.000012 |
| end                  | 0.000003 |
| query end            | 0.000002 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.000004 |
| closing tables       | 0.000002 |
| freeing items        | 0.000003 |
| updating status      | 0.000008 |
| cleaning up          | 0.000003 |
+----------------------+----------+
22 rows in set (0.00 sec)

一般的优化就是 profiling 结合 explain。explain中几个注意点如下：

  • possible_keys: 可能用到的索引, 系统估计可能用的几个索引, 但最终只能用1个
  • key : 最终用的索引
  • key_len: 使用的索引的最大长度

重点关注的是：type，是分析查数据过程的重要基于

  • all: 意味着从表的第1行,往后,逐行做全表扫描,运气不好扫描到最后一行.
  • index: 比 all 性能稍好一点，通俗的说: all 扫描所有的数据行, 相当于data_all，index 扫描所有的索引节点,相当于 index_all，两种情况下可能出现：
    • 索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描；
    • 利用索引来进行排序,但取出所有的节点。
  • range: 能根据索引，做范围扫描
  • ref：通过索引列可以直接引用到某些数据行
  • eq_ref：指通过索引列直接引用某1行数据（常见于连接查询中）
  • const, system, null 这3个分别指查询优化到常量级别, 甚至不需要查找时间。一般按照主键来查询时,易出现 const,system；直接查询某个表达式,不经过表时, 出现 NULL

ref 列：连接查询的时候前表和后表的引用关系

rows 列：表示预估扫描的行数

extra 列：

  • using index:是指用到了索引覆盖,效率非常高
  • using where 是指光靠索引定位不了,还得where判断一下
  • using temporary 是指用上了临时表, group by 与order by 不同列时,或group by,order by 别的表的列.
  • using filesort : 文件排序(文件可能在磁盘,也可能在内存)

select sum(shop_price) from goods group by cat_id(???? 这句话,用到了临时表和文件排序)

SQL性能优化的目标，至少要达到 range 级别，要求是 ref 级别，最好是 const。

MySQL状态监控收集

定时收集MySQL的指标绘制成图表，便于查询分析问题。

  • show status;可以查看服务器状态，重点观察以下几个指标：Queries,Threads_connected,Threads_running。可以根据这个指标计算出QPS。
  • show proceslist;显示了哪些线程在运行，重点关注：State列。

几个很耗时的状态如下,尽量避免：

  • Convert HEAP to MyISAM:查询结果太大内存放不下放在磁盘，show variables like '%tmp_table%'，少取数据或者调整服务器配置临时表的大小
  • Create tmp table:如group的时候创建临时表
  • Copying to tmp table on disk:
  • Sending data:发送数据过多
  • Sorting result
  • Locked:被其他查询锁住

表优化和列类型选择

  • 字段类型选择优先级：整数 > data,time > char,vchar > blob，字符串类型要考虑字符集的转换和排序的时候的校对集
  • 字段类型够用就行，能用smallint都不要用int
  • 避免用NULL:不利于索引，要用特殊字节标注，在磁盘上的空间其实更大

一般而言group by需要按照分组字段进行排序，排序结果放在临时表，然后根据临时表做聚合操作。因此explain的时候会出现Using temporary和Using filesort。

MyISAM会缓存count(*)，一旦有where查询count(*)速度就不快了，尤其是where的列没加索引的情况下。
union all不过滤效率较高，去重应该放在程序里面。

SQL优化

sql优化

判断是否存在使用EXISTS而不要使用COUNT(*)，例如：select exists(select * from account0 where uid =1);可以替代select count(*) from account0 where uid = 1。

尽量不要使用非参数化的负向查询，因为这个无法利用索引，例如<>,not in,not like,not exists,not between,is not null。

不要在where子句中对字段进行运算或者函数。例如where amount / 2 > 100，即使amount字段上有索引也不会用到，改成where amount > 200即可利用索引。

在min，max，distinct，order by，group by操作的列上建立索引可以避免额外的开销。

千万不要 ORDER BY RAND()

想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）。

1
2
3
4
5
6
7
8
// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
 
// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

把IP地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。
而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。

1
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

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

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

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

垂直分割

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

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

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

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

拆分大的 DELETE 或 INSERT 语句

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

Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务Crash，还可能会让你的整台服务器马上挂了。

所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例： 

1
2
3
4
5
6
7
8
9
10
while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了，退出！
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

索引优化

索引优化

参考资料：