MySQL 基本规范和慢查询优化

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

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

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

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

一、MySQL 设计规范

1. 命名规范
  • 表名、字段名、索引名使用小写字母、数字,采用下划线分割
  • 表名、字段名不超过 32 个字符
  • 存储实体数据的表,名称使用名词,单数
  • 索引名称采用 idx_ 前缀,之后顺序跟随索引的字段名,字段名直接以下划线分割
  • 不使用保留字
  • 存储实体表间多对多对应关系的表,名称建议采用 noun_verb_noun 这样的模式。例如:
    member_like_propertyproperty_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. 禁止隐式类型转换

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

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 EXISTSNOT IN 以及 NOT LIKE 等就是负向查询,它们利用索引将会很辛苦。

6. 禁止使用 % 前导查询

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

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

四、业务处理逻辑优化

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

例如,两张关联表 usersuser_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 缓存中读取完整数据并组装,理论上可以极大程度的减轻数据库的压力,并提升程序性能。