原创

MySQL 使用规范

  

MySQL 使用规范

以下规范适用在线交易(OLTP)系统的数据库。数据仓库与分析系统也可以参考。

命名规范

  • 表名、字段名、索引名使用小写字母、数字,采用下划线分割
  • 表名采用模块名3个缩小字符_前缀,之后顺序为表名,最后_分表后缀
  • 表名、字段名不超过 32 个字符
  • 存储实体数据的表,名称使用名词,单数
  • 索引名称采用 idx_ 前缀,之后顺序跟随索引的字段名,字段名直接以下划线分割
  • 不使用保留字
  • 存储实体表间多对多对应关系的表,名称建议采用 noun_verb_noun 这样的模式。例如: member_like_propertyproperty_has_tag
SQL 语句中,
  • 保留字使用全大写
  • 字符串使用单引号('
sql
-- 正确
SELECT id, title FROM xiaoqu WHERE id = 1
SELECT id, title FROM xiaoqu WHERE areacode = '000100010001'

-- 错误
select ID, title from XiaoQu where id = 1
SELECT id, title FROM xiaoqu WHERE areacode = "000100010001"

表的设计

MySQL 存储引擎使用 InnoDB

不用纠结,没有特殊原因的情况下,作为 OLTP 的 MySQL 使用 InnoDB 引擎。

字符集使用 UTF-8

Charset 为 utf8;Collation 为 utf8_general_ci

平衡是门艺术

效率优先,提升性能 没有绝对的对与错 适当牺牲范式,加入冗余 会增加代码复杂度

正确使用时间类型

MySQL 应当正确设置 time_zone
  • 精确到秒的时间采用 TIMESTAMP
  • 精确到日期使用 DATE
  • 一般不使用 DATETIME 类型
  • 每个表都含有ins_timeedt_time
  • 不允许使用字符串类型存储时间

字段定义为 NOT NULL

真的需要 NULL 值吗?如果不确定,就将字段设置为 NOT NULL

避免使用NULL字段

很难进行查询优化 NULL列加索引,需要额外空间 含NULL复合索引无效

字段设置 DEFAULT 值

设置为 NOT NULL 的字段,需要设置一个缺省值。

优先使用ENUM或SET

字符串 可能值已知且有限 ENUM占用1字节,转为数值运算 SET视节点定,最多8字节 比较时需要加‘ 单引号(即使是数值)

用好数值类型

原则:适用就好,越小越好 TINYINT< SMALLINT < MEDIUMINT < INT < BIGINT 1 Byte/2 Bytes/3 Bytes/4 Bytes/8 Bytes FLOAT(4B)/DOUBLE(8B) DECIMAL(M,D) (M+2 B)

不使用浮点类型(FLOAT、DOUBLE)

没有充分的理由,不要使用浮点数。 例如金额可以用分为单位,然后采用 INT。如果依然要以元为单位,可以采用 DECIMAL

数值类型VS字符串类型

更高效 查询更快 占用空间更小

使用 INT UNSIGNED 来存储 IPv4 地址

使用 INET_ATON 将 IP 地址的字符串形式转换成数字形式;使用 INET_NTOA 将 IP 地址数字形式转换成字符串形式,以便查看。 当要查询某段的 IP 时,请参考以下示例:
sql
 SELECT user_id FROM user_ip
 WHERE ip > INET_ATON('192.168.0.0') AND ip < INET_ATON('192.168.255.255')

当程序使用自带的函数进行 IP 地址的字符串形式与数字形式之间的转换时,需要注意数字的存储类型至少应为 32 位的无符号整型(如 uint32_t),并注意字节顺。

不直接存储图片、音频、视频等大容量内容

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

少用并拆分TEXT/BLOB

TEXT类型处理性能远低于VARCHAR
  • 强制生成硬盘临时表
  • 浪费更多空间
  • VARCHAR(65535)=> 64K 尽量不用TEXT/BLOB 如需使用则拆分到独立的表存放

字段个数不超过 32 个

一个表有很多很多字段,是坏设计的味道。请再认真考虑设计是否正确。 IO高效/表修复快/Alter快/高并发 以1G Size 500W Rows来评估
  • 顺序读取需N秒
  • 单行不超过200Byte
  • 单表不超50个INT字段,不超20个CHAR(10)字段
  • 单表字段数上限控制在20-50个

控制单表数据量

单表年数据量预估
  • 纯INT单表<1000W Rows
  • 含CHAR单表<500W Rows
  • 合理分表不超载

索引

谨慎合理添加索引

改善查询 减慢更新 索引并非越多越好 能不加的索引就不加
  • 综合评估数据密度和数据分布
  • 最好不要超过字段数20% 结合核心SQL优先考虑覆盖索引

使用数字主键

存储实体数据的表,其主键应该是数字类型。

不使用联合主键

存储实体数据的表,不使用联合主键。 存储实体表间多对多对应关系的表(仅有两个字段)允许例外。

不使用外键

所有的表不建立外键约束。

联合索引字段数不超过 5 个

一个联合索引的字段数太多,很可能是设计得不好,还很难符合命名的规范。

前缀索引长度不超过 8 个字符

对字符串类型的字段建立索引,采用前缀索引,且长度不超过 8 个字符。

字符类添加前缀索引

区分度
  • 单字母区分度:26
  • 4字母区分度:264=456,976
  • 5字母区分度:265=11,881,376
  • 8字母区分度:268=…
举例:字符型字段建前缀索引
Picdesc varchar(255) NOT NULL DEFAULT ‘’
Key `idx_1` (`picdesc`(8)),

SQL 语句

不使用联表查询

OLTP 不使用 JOIN 联合查询。

不使用子查询

没有特别好的理由,OLTP 不允许使用子查询。

不使用负向查询

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

一次查询的结果集不超过 100 行

必要时使用 LIMIT 100

LIMIT m, n,其中 m 应当小于 500

使用 SELECT ... LIMIT offset, row_count 或者 SELECT ... LIMIT row_count OFFSET offset 时,当 offset 小于 500 时,允许使用。
 sql
-- 允许
 SELECT ... FROM property WHERE broker_id=? ORDER BY update_time LIMIT 40, 20
 -- 不允许
 SELECT ... FROM property WHERE areacode=? ORDER BY update_time LIMIT 4000, 20

能够不使用 offset 的情况应当避免,如下面的例子(其中 id 是主键),
sql
-- 建议
SELECT ... FROM property WHERE broker_id=? AND id>? ORDER BY id LIMIT 20
-- 避免
SELECT ... FROM property WHERE broker_id=? ORDER BY id LIMIT 40, 20

避免使用 COUNT() 函数

能不使用就不使用,尽量用其他方法来解决。 例如判断经纪人是否有房源,可以不使用 COUNT() 函数,
 -- 正确
 SELECT 1 FROM propertys WHERE broker_id=? LIMIT 1

 -- 错误
 SELECT COUNT(*) FROM propertys WHERE broker_id=?

一次 COUNT() 可能扫描的行数应当确保小于 500 行

COUNT() 函数需要扫描所有的结果集之后才能得出结果。而结果集的大小需要业务知识来判断(EXPLAIN 方法只能来来检验某一个条件下的当前情况)。因此需要使用 COUNT() 查询的代码应当经过审阅。
 sql

 -- 允许。审阅。经纪人的房源数不允许超过 200 套

 SELECT COUNT(*) FROM property WHERE broker_id=?

 -- 不允许。一个区域板块下的房源数量不定,可能非常多

 SELECT COUNT(*) FROM property WHERE areacode=?

  • 其他聚合函数,例如 SUM()AVG()MAX() 等,同样适用。

统一使用 COUNT(*) 而不是 COUNT(1)

  • 当统计行数时,
  • 统一使用 COUNT(*) 而不是 COUNT(1)
  • 不使用 COUNT(PK)COUNT(column),除非真的是想统计 Nullable 字段的行数。

不在索引列做运算

  • 不在索引列进行数学运算或函数运算
  • 无法使用索引
  • 导致全表扫描
举例:
root:xxx_db> select BrokerId from ajk_propertys where proid-100=101960636; 
1 row in set (5.11 sec)

root:xxx_db> select BrokerId from ajk_propertys where proid=101960736;
1 row in set (0.00 sec)

尽量不在数据库做运算

  • 尽量不在数据库做运算
  • 复杂的运算移到程序端CPU
  • 尽可能简单的来使用Mysql

同数据类型的列值比较

原则:数字对数字,字符对字符 字符列与数值类型比较 数值列于字符列比较
  • 同时转换为双精度进行比较 字符列于数值列比较
  • 字符列整列转数值,不会使用索引
举例:字段UserMobile varchar(15)
root:xxx_db> select BrokerId from ajk_brokerextend where UserMobile = 15863194010 limit 1;           
1 row in set (2.07 sec)
root:xxx_db> select BrokerId from ajk_brokerextend where UserMobile = '18213821864' limit 1;              
1 row in set (0.00 sec)

禁止在查询条件中对字段进行数学运算、函数调用、隐式类型转换

  • 这类查询语句在使用索引时将非常困难。
sql

 -- 禁止

 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

 sql

  -- 禁止

 SELECT id FROM property WHERE CHAR_LENGTH(title) > 20

 sql

 -- 假设字段 property.status 的类型为 TINYINT

 -- 禁止

 SELECT id FROM property WHERE status = '1'

 -- 改为

 SELECT id FROM property WHERE status = 1

禁止隐式类型转换

  • 不仅在查询条件中禁止隐示类型转换,INSERTUPDATE 也不允许隐式类型转换。
 sql
 -- 假设字段 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

禁止使用 % 前导查询

尽量不使用 LIKE 查询,不得不用的情况下也禁止使用 % 前导查询。
  • 使用不了索引
  • 导致全表扫描
sql
 -- 禁止
 SELECT id FROM property WHERE title LIKE '%最%'

禁止SQL语句拼接

  • 应用程序不能有拼接语句出现

禁止使用DELETE语句使用

  • 应用程序不能有DELETE语句出现

禁止全表更新语句使用

  • 应用程序不能执行全表更新语句出现

拒绝3B

BIG SQL (大SQL) BIG Transactions (大事务) BIG Batch (大批量)

SQL语句尽可能简单

大SQL VS 多个简单SQL
  • 传统设计思想,BUT Mysql NOT
  • 一条SQL只能在一个CPU运算
  • 1000+ QPS的高并发中,1秒大SQL可能把数据库都堵死
拒绝大SQL,拆解成多条简单SQL
  • 简单SQL缓存命中率更高
  • 减少锁表时间,特别是MYISAM
  • 用上多CPU

改写OR为IN/UNION

同一字段,将OR改写为IN()
  • OR效率:O(n)
  • IN效率:O(Log n)
  • 当n很大时,OR会慢很多
  • 注意控制IN的个数,建议n小于200
不同字段,将or改为union
  • 减少对不同字段进行 or 查询
  • Merge index 往往很弱智

保持事务(连接)短小

保持事务/DB连接短小精悍
  • 事务/连接使用原则:即开即用,用完即关
  • 与事务无关操作放到事务外面,减少锁资源的占用
  • 不破坏一致性前提下,使用多个短事务代替长事务

约定规范

约定类规范(一)

隔离线上线下 构建数据库生态环境
  • 开发无线上DB权限
  • 原则:线上连线上,线下连线下
统一字符集为UTF8 禁用子查询
  • 大部分情况优化较差
  • 特别是Where中使用IN id的子查询
  • 一般可用JOIN改写

约定类规范(二)

  • 永远不在程序端显示加锁
  • 永远不在程序端对数据库显式加锁
  • 外部锁对数据库不可控
  • 高并发是灾难
  • 极难调试和排查 可采用事务操作
举例:
Select GET_LOCK(‘str’)

约定类规范(三)

  • 请不要使用存储过程
  • 请不要使用trigger
正文到此结束
本文目录