MySQL优化之——设计最佳实践1: 设计原则

客官°小女子只卖身不卖艺 2024-03-23 14:38 206阅读 0赞

前面介绍了MySQL的最重要的一个内存参数。这次介绍设计方面的最佳实践

9de7bb31d0644e7393039222babfed10

### 强调 强调 强调 ###

**自增列主键**

每个表都有主键, 主键要求用自增列 (推荐使用 bigint 类型, 数据量不多可以用 int ),主键不应该被修改,字符串不应该做主键, 不要使用GUID做主键

**引擎**

数据库和表推荐使用 innodb引擎, 如果是只读数据可以使用 MyISAM 不要在事务里混用, 所以不推荐 MyISAM还是一律使用 innodb引擎比较好

**字符集**

统一为 utf8mb4, 排序规则采用 utf8mb4\_bin(区分大小写), 不要使用utf8, 前端程序字符集或者环境变量中的字符集,与数据库, 表的字符集必须一致

**命名原则**

数据库: 考虑操作系统对大小写的区分,推荐统一为大写, 仅包含字母,数字和下划线, 控制在32个字符以内

表: 考虑操作系统对大小写的区分, 推荐统一为大写, 仅包含字母,数字和下划线, 控制在32个字符以内

**数据量设计**

单表数据量建议控制在 3000 万以内, 推荐500万以内,列的数量30以内

不适合在数据库中存放的数据

二进制多媒体数据,图片, 超大文本数据

**不适合执行的语句**

拒绝3B (拒绝大sql语句: big sql, 拒绝大事务: big transaction, 拒绝大批量: big batch)

**计算业务**

不在数据库做运算 计算务必移至业务层

**数据量**

mysql单表建议500-600w,不要超过1kw

**数据转换避坑**

mysql自动转换,字符可以转数值则转数值,不可以则默认转为0 例如: select “a”+4

### 数据库设计原则 ###

以下所有规范会按照【高危】,【强制】,【建议】三个级别进行标注,遵守优先级从高到低

#### 1.数据库 ####

【强制】创建数据库时必须显式指定字符集为 utf8mb4 排序规则 utf8mb4\_bin

【强制】库名均为小写或大写,下划线,数字风格,不超过 32 个字符

【强制】库的名称格式: 业务系统名称\_子系统名,同一模块使用的表名尽量使用统一前缀.

【强制】一般分库名称命名格式是库通配名\_编号,编号从0开始递增,比如 wenda\_001 以时间进行分库的名称格式是 “库通配名\_时间”

#### 2.表 ####

【强制】创建表时如无特殊需求,一律为 InnoDB . 可以不指定, 继承库的设置.

【强制】显式指定字符集utf8mb4, 排序规则 utf8mb4\_bin, 或继承数据库字符集和排序规则

【强制】表名要求模块名强相关,如师资系统采用 sz 作为前缀, 公共模块采用 comm 作为前缀

【强制】表名统一为大写,下划线,数字风格,不超过 32 个字符

【强制】表必须有注释

【强制】表列数目必须小于 30,若超过则应该考虑将表拆分 \#解读:单表列数太多使得 MySQL服务器处理 InnoDB 返回数据之间的映射成本太高

【强制】禁止使用外键,如果有外键完整性约束,需要应用程序控制 \#解读: 外键会导致表与表之间耦合,UPDATE 与 DELETE 操作都会涉及相关联的表,十分影响 SQL 的性能,甚至会造成死锁.

【强制】主键: bigint 或 int 类型自增主键, 标识表里每一行主体的字段不要设为主键,建议设为其他字段如 user\_id,order\_id 等,并建立 unique key 索引 \#解读: 如果主键为随机插入,且能修改,会导致innodb内部page分裂和大量随机I/O,性能下降

【强制】表中所有字段必须都是 not null 属性,并提供 默认值 \#解读 因为:1. NULL 的列使索引/索引统计/值比较都更加复杂更难查询优化 2.NULL 这种类型 MySQL内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多 3. NULL 字段的索引需要额外空间,无论是表还是索引中每行中的 NULL 的列都需要额外的空间来标识 4. NULL 字段的复合索引无效, 数据迁移容易出错, 聚合函数计算结果有偏差

【建议】核心表 (如用户表,金钱相关的表)必须有行数据的创建时间字段 create\_time 和最后更新时间字段 update\_time,便于查问题

【建议】建议对表里的 blob, text 等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去查询

【建议】反范式设计: 把经常需要 join 查询的字段,在其他表里冗余一份. 如 user\_name 属性在 user\_account,user\_login\_log 等表里冗余一份,减少 join 查询

【强制】中间表用于保留中间结果集,名称必须以 tmp\_开头. 备份表用于备份或抓取源表快照,名称必须以 bak\_开头. 中间表和备份表定期清理

【强制】对于超过100W行的大表进行 alter table,必须经过DBA审核,并在业务低峰期执行. \#解读: alter table会产生表锁,期间阻塞对于该表的所有写入,对于业务可能会产生极大影响

#### 3.列数据类型优化 ####

【强制】显式指定字符集 utf8mb4, 排序规则 utf8mb4\_bin, 或继承表的字符集和排序规则

【强制】列名统一为大写,下划线,数字风格,不超过 32 个字符

【强制】列必须有注释

【建议】表中的自增列,推荐使用 bigint 类型. 无符号 int 存储范围为 -2147483648~2147483647 (大约21亿左右),溢出后会导致报错. 满足条件可以选用 int

【建议】推荐数值类型(合适的字段类型节约空间); 业务中选择性很少的状态 status, 类型 type 等字段推荐使用 tinyint 或者 smallint 类型节省存储空间.

【建议】字符最好转化为数字,节约空间, 提高查询性能; \#解读: 例如: 业务中IP地址字段推荐使用int类型,不推荐用char(15). 因为int只占4字节,而char(15)占用至少15字节. 1亿数据行要多用1.1G存储空间

\#转换函数: select inet\_aton(‘192.168.55.44’); select inet\_ntoa(3232236044)

\#PHP转换函数: ip2long( ‘192.168.55.44’ ); long2ip(3530427185)

【建议】不推荐使用 enum,set. 因为它们浪费空间,且枚举值写死了,变更不方便. 推荐使用 tinyint 或 smallint

【建议】不推荐使用 blob,text 等类型. 它们都比较浪费硬盘和内存空间. 在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能. 建议和PM, RD沟通,是否真的需要这么大字段. Innodb中当一行记录超过 8098 字节时,会将该记录中选取最长的一个字段将其 768 字节放在原始 page 里,该字段余下内容放在 overflow-page 里. 不幸的是在 compact 行格式下,原始 page 和 overflow-page 都会加载

【建议】与金额相关的数据类型一定要使用 decimal 类型, 也可以用 int,程序端乘以 100 和除以 100 进行存取

【建议】文本数据尽量用 varchar 存储. 因为 varchar 是变长存储,比 char 更省空间. server层规定一行所有文本最多存 65535 字节,因此在 utf8 字符集下最多存 21844 个字符,超过会自动转换为 mediumtext 字段. 而 text 在 utf8 字符集下最多存 21844 个字符,mediumtext 最多存 2^24/3 个字符,longtext 最多存 2^32 个字符. 一般建议用 varchar 类型,字符数不要超过 2700

【强制】如果存储的字符串长度几乎相等,使用 char 定长字符串类型 \#解读: 能够减少空间碎片,节省存储空间.

【建议】时间类型尽量选取 timestamp. \#解读: datetime占用8字节,timestamp仅占用4字节,但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00

\#更为高阶的方法,选用int来存储时间,使用SQL函数unix\_timestamp()和from\_unixtime()来进行转换.

【强制】禁用保留字,如 DESC, RANGE, MARCH 等

#### 4.索引 ####

【强制】InnoDB表必须主键为 id bigint auto\_increment, 且主键值禁止被更新

【建议】主键的名称以 “pk\_” 开头,唯一键以 “uk\_” 或 “uq\_” 开头,普通索引以 “ix\_” 开头,一律使用大写格式,以表名/字段的名称或缩写作为后缀

【强制】InnoDB 和 MyISAM 存储引擎表,索引类型必须为 BTREE; MEMORY 表可以根据需要选择 HASH 或者 BTREE 类型索引

【强制】单个索引中每个索引记录的长度不能超过64KB

【建议】单个表上的索引个数不能超过7个

【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面. 如列 userid 的区分度可由 select count(distinct userid) 计算出来

【强制】多考虑建立联合索引,建立联合索引时,将区分度更高的字段放在左边 \#解读: 区分度更高的列放在左边,能够在一开始就有效的过滤掉无用数据. 提高索引的效率,相应我们在 Mapper 中编写 SQL 的 WHERE 条件中有多个条件时,需要先看看当前表是否有现成的联合索引直接使用,注意各个条件的顺序尽量和索引的顺序一致. 如列 userid 的区分度可由 select count(distinct userid) 计算出来

【强制】在一个联合索引中,若第一列索引区分度等于 1,那么则不需要建立联合索引 \#解读: 索引通过第一列就能够完全定位的数据,所以联合索引的后边部分是不需要的

【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高

【建议】建表或加索引时,保证表里互相不存在冗余索引. 对于MySQL来说,如果表里已经存在 key(a,b),则 key(a) 为冗余索引,需要删除

【建议】不在索引做列运算

【建议】不用外键(由程序保证约束)

【建议】免在更新比较频繁, 区分度不高的列上单独建立索引 \#解读: 区分度不高的列单独创建索引的优化效果很小,但是较为频繁的更新则会让索引的维护成本更高

【强制】JOIN 的表不允许超过五个. 需要 JOIN 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引 \#解读: 太多表的 JOIN 会让优化器更难权衡出一个 “最佳” 的执行计划 (可能性为表数量的阶乘),同时要注意关联字段的类型,长度,字符编码等等是否一致.

【建议】利用覆盖索引来进行查询操作,避免回表 \#解读: 覆盖查询即是查询只需要通过索引即可拿到所需 DATA,而不再需要再次回表查询,所以效率相对很高. 我们在使用 EXPLAIN 的结果,extra 列会出现: “using index”. 要强调一下不要使用 "SELECT \* " 否则几乎不可能使用到覆盖索引

【建议】在较长 VARCHAR 字段,例如 VARCHAR(100) 上建立索引时,应指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可 \#解读: 索引的长度与区分度是一对矛盾体,一般对字符串类型数据,若长度为 20 的索引,区分度会高达 90% 以上,则可以考虑创建长度例为 20 的索引,而非全字段索引. 例如可以使用 SELECT COUNT(DISTINCT LEFT(lesson\_code, 20))/COUNT(\*) FROM lesson;来确定 lesson\_code 字段字符长度为 20 时文本区分度

【建议】如果有 ORDER BY 的场景,请注意利用索引的有序性 ORDER BY 最后的字段是联合索引的一部分,并且放在索引组合顺序的最后,避免出现 file\_sort 的情况,影响查询性能. \#解读: 假设有查询条件为 WHERE a=? and b=? ORDER BY c;存在索引: a\_b\_c,则此时可以利用索引排序. 反例: 在查询条件中包含了范围查询,那么索引有序性无法利用,如: WHERE a>10 ORDER BY b;索引 a\_b 无法排序

【建议】在 Where 中索引的列不能某个表达式的一部分,也不能是函数的参数 \#解读: 即是某列上已经添加了索引,但是若此列成为表达式的一部分, 或者是函数的参数,MySQL无法将此列单独解析出来,索引也不会生效

【建议】我们在 Where 条件中使用范围查询时,索引最多用于一个范围条件,超过一个则后边的不走索引 \#解读: MySQL能够使用多个范围条件里边的最左边的第一个范围查询,但是后边的范围查询则无法使用.

【建议】多个表进行外连接时,表之间的关联字段类型必须完全一致 \#解读: 当两个表进行 Join 时,字段类型若没有完全一致,则加索引也不会生效,这里的完全一致包括但不限于字段类型, 字段长度, 字符集, Collection 等等.

#### 5.分库分表, 分区表 ####

【强制】分区表的分区字段 (partition-key)必须有索引,或者是组合索引的首列

【强制】单个分区表中的分区 (包括子分区)个数不能超过1024

【强制】上线前RD或者DBA必须指定分区表的创建, 清理策略

【强制】访问分区表的SQL必须包含分区键

【建议】单个分区文件不超过2G,总大小不超过50G. 建议总分区数不超过20个

【强制】对于分区表执行alter table操作,必须在业务低峰期执行

【强制】采用分库策略的,库的数量不能超过 1024

【强制】采用分表策略的,表的数量不能超过 4096