MySQL 基本规范和慢查询优化

开发 评论

MySQL 应该是目前PHP语言配合最多的关系型数据库工具了。然而它同时也是我学过的、用过的最自我纠结、最鸡肋的一门技术了。

首先，MySQL 作为目前最流行的关系型数据库工具之一，它的功能无疑是强大的。我们在学校学数据库这门课程的时候，介绍过的关系型数据库的比如外键、连表查询、子查询、负向查询等功能，以及它内置的 sum()/max()/min() 等非常方便的数学运算方法、隐式类型转换，以及模糊查询 like '%xxx%' 等，以及它支持的主键形式（单主键、数字主键、字符串主键、联合主键）等等。这些功能MySQL都是支持的。

然而，在实际项目应用中，尤其是高并发、大数据量的项目中，你会发现项目访问速度的最大瓶颈，就是数据库。所以在多数的线上项目中，以上提到的数据库高级功能，多半不能用！你用一个 count(*) 几乎都已经是极限了。。。

这里整理了一部分搜集的以及自己项目中遇到过的（主要是前者）MySQL优化设计和使用的经验之谈。

一、MySQL 设计规范

1. 命名规范
  • 表名、字段名、索引名使用小写字母、数字，采用下划线分割
  • 表名、字段名不超过 32 个字符
  • 存储实体数据的表，名称使用名词，单数
  • 索引名称采用 idx_ 前缀，之后顺序跟随索引的字段名，字段名直接以下划线分割
  • 不使用保留字
  • 存储实体表间多对多对应关系的表，名称建议采用 noun_verb_noun 这样的模式。例如：
    member_like_property、property_has_tag

SQL 语句中，

  • 保留字使用全大写
  • 字符串使用单引号（'）
1
2
3
4
5
6
7
-- 正确
SELECT id, name FROM user_info WHERE id = 1
SELECT id, name FROM user_info WHERE areacode = '000100010001'

-- 错误
select ID, name from user_info where id = 1
SELECT id, name FROM user_info WHERE areacode = "000100010001"
2. 表设计规范
  • MySQL 存储引擎使用 InnoDB

关于MySQL存储引擎的简单介绍和区分，可参考 这篇文章：MySQL存储引擎

  • 字符集使用 utf8 & utf8mb4

Charset 为 utf8(mb4)；Collation 为 utf8(mb4)_unicode_ci。

注：关于MySQL Charset 和 Collation 的选择说明请参考另一篇文章 MySQL 建表设置选项

MySQL 在 5.5.3 之后增加了这个 utf8mb4 的编码，mb4 就是 most bytes 4 的意思，专门用来兼容四字节的unicode。好在 utf8mb4 是 utf8 的超集，除了将编码改为 utf8mb4 外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

utf8mb4 的其中一个应用场景就是：emoji 表情的存储。

另外记得在PHP中使用对应的编码格式来链接数据库。

  • 正确使用时间类型

MySQL 应当正确设置 time_zone。

精确到秒的时间采用 TIMESTAMP（也可以使用strtotime方法之后，使用int(11) 格式存储对应的时间戳）

精确到日期使用 DATE

一般不使用 DATETIME 类型

不允许使用字符串类型存储时间

  • 字段定义为 NOT NULL，并设置 DEFAULT 值
  • 不使用浮点类型（FLOAT、DOUBLE）

例如金额可以用分为单位，然后采用 INT。如果依然要以元为单位，可以采用 DECIMAL。

DECIMAL(N,M)中M值的是小数部分的位数，若插入的值未指定小数部分或者小数部分不足M位则会自动补到M位小数，若插入的值小数部分超过了M为则会发生截断，截取前M位小数。N值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过N-M位，否则不能成功插入，会报超出范围的错误。

  • 字段个数不超过 32 个
  • 不直接存储图片、音频、视频等大容量内容

请使用分布式文件系统来存储图片、音频、视频等内容。数据库里只存储文件的位置。

二、索引使用规范

1. 使用数字主键
2. 不使用联合主键
3. 不使用外键
4. 联合索引字段数不超过 5 个
5. 前缀索引长度不超过 8 个字符

关于外键：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
外键的优点：
（1）外键的关联和约束，保证数据库数据的完整性和一致性。
（2）有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。
（3）外键在一定程度上说明的业务逻辑，会使设计周到具体全面。

外键的缺点：
（1）过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题
（2）不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在 insert,   update,   delete   数据的时候更快）

另外，在海量的数据库中想都不要去想外键，试想，一个程序每天要insert数百万条记录，当存在外键约束的时候，每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成级数的增长！我的一个程序入库在3个小时做完，如果加上外键，需要28个小时！

结论：
1，在性能要求不高，安全要求高的系统中，使用外键；在性能要求高，安全自己控制的系统中，不用外键；小系统随便，最好用外键。
2，用外键要适当，不能过分追求
3，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。

PPS：MySQL 只有在 InnoDB 引擎下，有完整的外键检验和约束功能。

三、SQL 语句

1. 禁止在查询条件中对字段进行数学运算、函数调用、隐式类型转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 禁止
SELECT id FROM property WHERE NOW() - update_time < 3600
SELECT id FROM property WHERE update_time + 3600 > NOW()

-- 改为
SELECT id FROM property WHERE update_time > NOW() - 3600

-- 禁止
SELECT id FROM property WHERE CHAR_LENGTH(title) > 20

-- 假设字段 property.status 的类型为 TINYINT
-- 禁止
SELECT id FROM property WHERE status = '1'

-- 改为
SELECT id FROM property WHERE status = 1
2. 禁止隐式类型转换

不仅在查询条件中禁止隐示类型转换，INSERT，UPDATE 也不允许隐式类型转换。

1
2
3
4
5
6
7
8
-- 假设字段 property.status 的类型为 TINYINT
-- 禁止
INSERT INTO property (..., status) VALUES (..., '1')
UPDATE property SET status = '1' WHERE id = '43'

-- 改为
INSERT INTO property (..., status) VALUES (..., 1)
UPDATE property SET status = 1 WHERE id = 43
3. 不使用联表查询
4. 避免使用子查询
5. 避免使用负向查询

负向查询是指，如果查询条件描述的是不要什么数据，其余的都要。例如 !=、<>、NOT EXISTS、NOT IN 以及 NOT LIKE 等就是负向查询，它们利用索引将会很辛苦。

6. 禁止使用 % 前导查询

尽量不使用 LIKE 查询，不得不用的情况下也禁止使用 % 前导查询。

1
2
-- 禁止
SELECT id FROM property WHERE title LIKE '%最%'

四、业务处理逻辑优化

这里有点像 ORM 中 的关联模型预加载的概念。

例如，两张关联表 users 和 user_details

要求查询出符合某些条件的 users 表中的数据，以及相关联的 user_details 数据。

错误示范：

1
2
3
4
5
$userList = User::search($conditions)->get();
foreach($userList as $k => $u) {
  	$detail = UserDetail::where(['user_id', $u['user_id'])->first();
    $userList[$k]['detail'] = $detail;
}

如上，假设取出符合要求的 $userList 有 N 条数据，那么这个处理过程就是执行了 1 + N 次查询操作。而前面提到过，互联网项目最大的性能瓶颈就在数据库的操作上，所以这里会消耗非常多的时间，就要对这个逻辑做一些优化，将这个查询的过程缩减，当然代价就是业务代码的相对负责：

推荐示范：

1
2
3
4
5
6
7
8
9
10
11
$userList = User::search($conditions)->get();

$idList = [];
foreach($userList as $k => $u) {
  	$idList[] = $u['user_id'];
}

$detailList = UserDetail::where('user_id', 'in', $idList)->get();
foreach ($userList as $k => $u) {
  	// 找出对应的detail并做关联
}

这种方式，就将 1 + N 次查询，变成了 1 + 1 次查询，大大减小了数据库的查询负载压力，尤其是数据越大，这个优化的提现就越明显。

五、借助缓存 Memcached 以及 Solr 等搜索引擎工具

当数据涉及到多表甚至多库而且查询条件本身也比较分散的时候，单靠编程语言和数据库的能力，虽然也多半能解决问题但是可能会引发十分严重的性能问题，这时候就需要用 Solr 一类的搜索引擎工具来实现复杂条件的查询工作，Solr 返回相应的数据主键之后再从 Memcached 缓存中读取完整数据并组装，理论上可以极大程度的减轻数据库的压力，并提升程序性能。

洋葱_SilovPHP & Go

一个爱看闲书偶尔钻研代码的厨子。