InnoDB 存储字段补充说明
【FAQ-1-01】 库名、表名、字段名必须使⽤小写字母,并采⽤下划线分割。 a)MySQL 有配置参数 lower_case_table_names,不可动态更改,linux 系统默认为 0,即库表名以实际情况存储,⼤小写敏感。如果是 1,以⼩写存储,⼤小写不敏感。如果是 2,以实际情况存储,但以小写⽐较。 b) 如果⼤小写混合使用,可能存在 abc,Abc,ABC 等多个表共存,容易导致混乱。 c) 字段名显⽰区分⼤⼩写,但实际使用不区分,即不可以建立两个名字⼀样但大小写不一样的字段。 d) 为了统⼀规范,库名、表名、字段名使⽤⼩写字母。
【FAQ-1-02】 库名、表名、字段名禁止超过 32 个字符。 库名、表名、字段名⽀持最多 64 个字符,但为了统一规范、易于辨识以及减少传输量,禁⽌超过 32 个字符。
【FAQ-1-03】 库名、表名、字段名禁止使用 MySQL 保留字。 当库名、表名、字段名等属性含有保留字时,SQL 语句必须⽤反引号引用属性名称,这将使得 SQL 语句书写、SHELL 脚本中变量的转义等变得非常复杂。
【FAQ-2-01】 使⽤用 InnoDB 存储引擎。InnoDB 引擎是 MySQL5.5 版本以后的默认引擘,⽀持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、⼤内存、SSD 等硬件⽀持更好,⽀持数据热备份等,因此 InnoDB 相比 MyISAM 有明显优势。
【FAQ-2-02】 库表字符集使用 UTF8MB4,特殊情况可以使用 UTF8 等其他字符集。 a)UTF8 字符集存储汉字占用 3 个字节,UTF8MB4 字符集存储汉子占用 4 个字节,存储英⽂字符均只占⽤一个字节。 b)UTF8 和 UTF8MB4 统一而且通用,不会出现转码出现乱码风险。 c)EMOJ 等表情符号的存储需求,只能使用 UTF8MB4 字符集。 d) 不同字符集的字段关联时无法使用索引。
【FAQ-3-01】 禁⽌使⽤分区表。分区表对分区键有严格要求;分区表在表变大后,执⾏行 DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使⽤分区表,并建议业务端手动 SHARDING。
【FAQ-3-02】 将⼤字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。有利于有效利用缓存,防止读入⽆用的冷数据,较少磁盘 IO,同时保证热数据常驻内存提高缓存命中率。
【FAQ-3-03】 采⽤合适的分库分表策略。例如千库十表、⼗库百表等。采用合适的分库分表策略,有利于业务发展后期快速对数据库进⾏⽔平拆分,同时分库可以有效利用 MySQL 的多线程并行复制特性。
【FAQ-4-01】 建议使用 UNSIGNED 存储⾮负数值。同样的字节数,⾮负存储的数值范围更大。如 TINYINT 有符号为 -128~127,无符号为 0~255。
【FAQ-4-02】 建议使用 INT UNSIGNED 存储 IPV4。 ⽤ UNSINGED INT 存储 IP 地址占用 4 字节,CHAR(15) 则占用 15 字节。另外,计算机处理整数类型比字符串类型快。使用 INT UNSIGNED ⽽不是 CHAR(15) 来存储 IPV4 地址,通过 MySQL 函数 inet_ntoa 和 inet_aton 来进行转化。IPv6 地址⽬前没有转化函数,需要使用 DECIMAL 或两个 BIGINT 来存储。例如:
Shell
SELECT INET_ATON('209.207.224.40'); 3520061480 SELECT INET_NTOA(3520061480); 209.207.224.40
12 | SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480); 209.207.224.40 |
---|
【FAQ-4-03】 INT 类型固定占用 4 字节存储,例如 INT(4)仅代表显示字符宽度为 4 位,不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如 INT(3)默认显示 3 位,空格补齐,超出时正常显示。python、java 等客户端不具备这个功能。
【FAQ-4-04】 强烈建议使用 TINYINT 来代替 ENUM 类型。ENUM 类型在需要修改或增加枚举值时,需要在线 DDL,成本较高;ENUM 列值如果含有数字类型,可能会引起默认值混淆。【ENUM 类型说明】
【FAQ-4-05】 禁⽌在数据库中存储明⽂密码。采用加密字符串存储密码,并保证密码不可解密,同时采用随机字符串加密保证密码安全。防⽌数据库数据被公司内部人员或黑客获取后,采用字典攻击等方式暴⼒破解用户密码。
【FAQ-4-06】 使⽤用 VARBINARY 存储⼤小写敏感的变⻓字符串或二进制内容。VARBINARY 默认区分⼤小写,没有字符集概念,速度快。
【FAQ-4-07】 区分使⽤ DATETIME 和 TIMESTAMP。存储年使⽤ YEAR 类型。存储⽇期使⽤用 DATE 类型。存储时间 (精确到秒甚至微妙) 建议使用 TIMESTAMP 类型。 DATETIME 和 TIMESTAMP 都可以精确到秒,优先选择 TIMESTAMP(可精确到微妙),因为 TIMESTAMP 只有 4 个字节,⽽ DATETIME8 个字节。同时 TIMESTAMP 具有⾃动赋值以及自动更新的特性。【TIMESTAMP 字段类型初始化】
【FAQ-4-08】 所有字段均定义为 NOT NULL。 a)对表的每⼀行,每个为 NULL 的列都需要额外的空间来标识。 b)B+ 树索引不会存储 NULL 值,所以如果索引字段可以为 NULL,索引效率会下降。 c)建议用 0、特殊值或空串代替 NULL 值。
【FAQ-5-01】 表必须有主键,推荐使⽤ UNSIGNED 自增列作为主键。表没有主键,INNODB 会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。
【FAQ-5-02】 禁⽌冗余索引。索引是双刃剑,会增加维护负担,增⼤ IO 压力。(a,b,c)、(a,b),后者为冗余索引。可以利⽤前缀索引来达到加速目的,减轻维护负担。
【FAQ-5-03】 禁⽌重复索引。 primary key a;uniq index a; 重复索引增加维护负担、占⽤磁盘空间,同时没有任何益处。
【FAQ-5-04】 不在低基数列上建⽴索引,例如“性别”。大部分场景下,低基数列上建立索引的精确查找,相对于不建⽴索引的全表扫描没有任何优势,⽽且增⼤了 IO 负担。
【FAQ-5-05】 合理使用覆盖索引减少 IO,避免排序。覆盖索引能从索引中获取需要的所有字段,从⽽避免回表进行⼆次查找,节省 IO。InnoDB 存储引擎中,secondary index(⾮主键索引,又称为辅助索引、⼆级索引)没有直接存储行地址,⽽是存储主键值。如果⽤户需要查询 secondary index 中所不包含的数据列,则需要先通过 secondary index 查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率较高。主键查询是天然的覆盖索引。例如 SELECT email,uid FROM user_email WHERE uid=xx,如果 uid 不是主键,适当时候可以将索引添加为 index(uid,email),以获得性能提升。
【FAQ-6-01】 ⽤ IN 代替 OR。SQL 语句中 IN 包含的值不应过多,应少于 1000 个。IN 是范围查找,MySQL 内部会对 IN 的列表值进行排序后查找,⽐ OR 效率更⾼。
【FAQ-6-02】 减少与数据库交互次数,尽量采用批量 SQL 语句。使⽤下面的语句来减少和 DB 的交互次数:
【FAQ-6-03】 拆分复杂 SQL 为多个小 SQL,避免大事务。 简单的 SQL 容易使用到 MySQL 的 QUERY CACHE;减少锁表时间特别是 MyISAM;可以使用多核 CPU。
【FAQ-6-04】 ⽤ UNION ALL 代替 UNION。 UNION ALL 不需要对结果集再进行排序。
【FAQ-6-05】 SELECT 只获取必要的字段,禁⽌使⽤ SELECT *。 减少网络带宽消耗;能有效利用覆盖索引;表结构变更对程序基本⽆影响。
【FAQ-6-06】 SQL 中避免出现 now()、rand()、sysdate()、current_user()等不确定结果的函数。 语句级复制场景下,引起主从数据不一致;不确定值的函数,产⽣的 SQL 语句⽆法利用 QUERY CACHE。
【FAQ-6-07】 避免使⽤存储过程、触发器、视图、自定义函数等。 这些⾼级特性有性能问题,以及未知 BUG 较多。业务逻辑放到数据库会造成数据库的 DDL、SCALE OUT、SHARDING 等变得更加困难。
【FAQ-6-08】 建议使用合理的分页⽅式以提⾼分⻚效率。 假如有类似下⾯分⻚语句:
Shell
SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;
1 | SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10; |
---|
这种分页方式会导致⼤量的 IO,因为 MySQL 使用的是提前读取策略。 推荐分页⽅式:
Shell
SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10; SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id);
12 | SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10;SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id); |
---|
【FAQ-6-09】 禁止使⽤ order by rand()。 order by rand() 会为表增加⼀个伪列,然后⽤ rand() 函数为每一行数据计算出 rand() 值,然后基于该行排序,这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用 rand() 函数获得随机的主键值,然后通过主键获取数据。
【FAQ-7-01】 禁⽌有 SUPER 权限的应用程序账号存在。 安全第⼀。SUPER 权限会导致 read only 失效,导致较多诡异问题而且很难追踪。
【FAQ-7-02】 提交线上建表改表需求,必须详细注明涉及到的所有 SQL 语句 (包括 SELECT、INSERT、DELETE、UPDATE),便于 DBA 进⾏审核和优化。 并不只是 SELECT 语句需要⽤到索引。UPDATE、DELETE 都需要先定位到数据才能执行变更。因此需要业务提供所有的 SQL 语句便于 DBA 审核。
【FAQ-7-03】 对同⼀个表的多次 ALTER 操作必须合并为一次操作。 MySQL 对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次 ALTER 操作合并为⼀次操作。例如,要给表 t 增加⼀个字段 b,同时给已有的字段 aa 建⽴索引,通常的做法分为两步:
Shell
alter table t add column b varchar(10); 然后增加索引: alter table t add index idx_aa(aa);
123 | alter table t add column b varchar(10);然后增加索引:alter table t add index idx_aa(aa); |
---|
正确的做法是:
Shell
alter table t add column b varchar(10),add index idx_aa(aa);
1 | alter table t add column b varchar(10),add index idx_aa(aa); |
---|
【FAQ-7-04】 不要在MySQL数据库中存放业务逻辑。数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,⽽把数据库作为存储层,实现逻辑与存储的分离。
Shell
CREATE TABLE `house_showing_house` ( `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `showing_record_id` int(10) unsigned NOT NULL COMMENT '带看表主键', `house_code` char(15) NOT NULL COMMENT '房源SE编号', `agent_code` int(10) unsigned NOT NULL COMMENT '经纪人SE编号', `agent_ucid` int(10) unsigned NOT NULL COMMENT '经纪人UC编号', `agent_name` varchar(64) NOT NULL COMMENT '经纪人姓名', `customer_code` varbinary(16) NOT NULL COMMENT '客户编号, 如果有的话', `customer_ucid` int(10) unsigned NOT NULL COMMENT '客户UC编号', `customer_name` varchar(32) NOT NULL COMMENT '客户姓名', `hdic_city_id` smallint(5) unsigned NOT NULL COMMENT '楼盘字典 城市ID', `hdic_resblock_id` int(10) unsigned NOT NULL COMMENT '楼盘字典 楼盘ID', `see_time` datetime NOT NULL DEFAULT '1990-01-01 00:00:00' COMMENT '带看时间', `appid` int(11) NOT NULL COMMENT '信息来源', `app_pkid` bigint(20) NOT NULL COMMENT 'app的主键', `satisfaction` tinyint(1) unsigned NOT NULL COMMENT '客户是否满意', `reciprocal_frame` tinyint(4) unsigned DEFAULT '0' COMMENT '1看的本户型,2看的反户型,3看的本房', `feedback` varchar(512) NOT NULL COMMENT '反馈内容', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态值', `audit_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '审核状态:0待审,1通过,2拒绝', `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_customer_code` (`customer_code`), KEY `idx_customer_ucid` (`customer_ucid`), KEY `idx_appid_pkid` (`appid`,`app_pkid`), KEY `idx_customer_name_4` (`customer_name(4)`), KEY `idx_update_time` (`update_time`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='房源带看信息表';
1234567891011121314151617181920212223242526272829 | CREATE TABLE `house_showing_house` ( `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT, `showing_record_id` int(10) unsigned NOT NULL COMMENT '带看表主键', `house_code` char(15) NOT NULL COMMENT '房源SE编号', `agent_code` int(10) unsigned NOT NULL COMMENT '经纪人SE编号', `agent_ucid` int(10) unsigned NOT NULL COMMENT '经纪人UC编号', `agent_name` varchar(64) NOT NULL COMMENT '经纪人姓名', `customer_code` varbinary(16) NOT NULL COMMENT '客户编号, 如果有的话', `customer_ucid` int(10) unsigned NOT NULL COMMENT '客户UC编号', `customer_name` varchar(32) NOT NULL COMMENT '客户姓名', `hdic_city_id` smallint(5) unsigned NOT NULL COMMENT '楼盘字典 城市ID', `hdic_resblock_id` int(10) unsigned NOT NULL COMMENT '楼盘字典 楼盘ID', `see_time` datetime NOT NULL DEFAULT '1990-01-01 00:00:00' COMMENT '带看时间', `appid` int(11) NOT NULL COMMENT '信息来源', `app_pkid` bigint(20) NOT NULL COMMENT 'app的主键', `satisfaction` tinyint(1) unsigned NOT NULL COMMENT '客户是否满意', `reciprocal_frame` tinyint(4) unsigned DEFAULT '0' COMMENT '1看的本户型,2看的反户型,3看的本房', `feedback` varchar(512) NOT NULL COMMENT '反馈内容', `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态值', `audit_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '审核状态:0待审,1通过,2拒绝', `create_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uniq_customer_code` (`customer_code`), KEY `idx_customer_ucid` (`customer_ucid`), KEY `idx_appid_pkid` (`appid`,`app_pkid`), KEY `idx_customer_name_4` (`customer_name(4)`), KEY `idx_update_time` (`update_time`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='房源带看信息表'; |
---|