淘宝指数网站,网站开发需要投入多少时间,注册公司取名字大全,sem 优化软件MySQL数据库开发设计规范总结 概述MySQL数据库设计规范设计规范-库设计规范-表、列设计规范-索引跟索引相关的SQL优化设计规范-视图设计规范-存储过程设计规范-触发器设计规范-安全规范 数据库架构设计原则一、高可用架构选择二、扩展性三、安全性策略四、数据完整性策略五、规… MySQL数据库开发设计规范总结 概述MySQL数据库设计规范设计规范-库设计规范-表、列设计规范-索引跟索引相关的SQL优化设计规范-视图设计规范-存储过程设计规范-触发器设计规范-安全规范 数据库架构设计原则一、高可用架构选择二、扩展性三、安全性策略四、数据完整性策略五、规范化设计与性能之间的权衡策略六、数据生命周期管理七、数据备份策略 SQL 语句 概述
从数据库的设计原则、命名规范等方面论述数据库设计的规范思想及命名规则。参照以下原则进行数据库设计
方便业务功能实现、业务功能扩展方便设计开发、增强系统的稳定性和可维护性保证数据完整性和准确性提高数据存储效率在满足业务需求的前提下使时间开销和空间开销达到优化平衡
MySQL数据库设计规范
设计规范-库
1.【强制】库的名称必须控制在30个字符以内只能使用英文字母、数字和下划线建议以英文字母开头。
2.【强制】库名中英文一律小写不同单词采用下划线分割。须见名知意。
3.【强制】库的名称格式业务系统名称_子系统名。
4.【强制】库名禁止使用关键字如type,order等。
5.【强制】创建数据库时必须显式指定字符集并且字符集只能是utf8或者utf8mb4。 创建数据库SQL举例MySQLCREATE DATABASE crm_fundDEFAULT CHARACTER SET ‘utf8’
6.【建议】对于程序连接数据库账号遵循权限最小原则。 使用数据库账号只能在一个DB下使用不准跨库。程序使用的账号原则上不准有drop权限 。
7.【建议】临时库以 tmp_ 为前缀并以日期为后缀备份库以bak_为前缀并以日期为后缀。
设计规范-表、列
字符和备注
1.【强制】创建表时必须显式指定字符集为utf8或utf8mb4。
2.【强制】创建表时必须显式指定表存储引擎类型。如无特殊需求一律为InnoDB。
3.【强制】建表必须有注释comment。
4.【强制】禁止在数据库中存储明文密码。
5.【推荐】如果修改字段含义或对字段表示的状态追加时需要及时更新字段注释。
命名
1.【强制】表名、字段名必须使用小写字母或数字禁止出现数字开头禁止两个下划线中间只出现数字。数据库字段名的修改代价很大因为无法进行预发布所以字段名称需要慎重考虑。 说明MySQL 在 Windows 下不区分大小写但在 Linux 下默认是区分大小写。因此数据库名、表名、字段名都不允许出现任何大写字母避免节外生枝。 正例aliyun_adminrdc_configlevel3_name 反例AliyunAdminrdcConfiglevel_3_name
2.【强制】表名不使用复数名词。 说明表名应该仅仅表示表里面的实体内容不应该表示实体数量对应于 DO 类名也是单数形式符合表达习惯。
3.【建议】表名要求有模块名强相关同一模块的表名尽量使用统一前缀。比如crm_fund_item。
4.【推荐】表的命名最好是遵循“业务名称_表的作用”。 正例alipay_task / force_project / trade_config / tes_question
5.【推荐】库名与应用名称尽量一致。
6.【强制】表达是与否概念的字段必须使用 is_xxx 的方式命名数据类型是 unsigned tinyint1 表示是0 表示否。 注意POJO 类中的任何布尔类型的变量都不要加 is 前缀所以需要在设置从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值使用 tinyint 类型坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。 说明任何字段如果为非负数必须是 unsigned。 正例表达逻辑删除的字段名 is_deleted1 表示删除0 表示未删除。是否为enabled的会员的字段命名为is_enabled。
7.【强制】表名、列名禁止使用关键字如 type、order、desc、range、match、delayed 等请参考 MySQL 官方保留字。
8.【强制】字段命名应尽可能使用表达实际含义的英文单词或缩写。如公司ID不要使用corporation_id, 而用corp_id 即可。
9.【强制】主键索引名为 pk_字段名唯一索引名为 uk_字段名普通索引名则为 idx_字段名。 说明pk_即 primary keyuk_即 unique keyidx_即 index 的简称。
10.【建议】 中间表或临时表用于保留中间结果集名称以tmp_开头。备份表用于备份或抓取源表快照名称以bak_开头。中间表和备份表定期清理。
字段
1.【强制】表必备三字段idcreate_timeupdate_time。 说明其中 id 必为主键类型为 bigint unsigned、单表时自增、步长为 1。create_timeupdate_time 的类型均为datetime 类型前者现在时表示主动式创建后者过去分词表示被动式更新。
2.【强制】在数据库中不能使用物理删除操作要使用逻辑删除。 说明逻辑删除在数据删除后可以追溯到行为操作。不过会使得一些情况下的唯一主键变得不唯一需要根据情况来酌情解决。
3.【推荐】字段允许适当冗余以提高查询性能但必须考虑数据一致。冗余字段应遵循 1不是频繁修改的字段。 2不是唯一索引的字段。 3不是 varchar 超长字段更不能是 text 字段。
4.【强制】禁止在数据库中存储图片、文件等大的二进制数据。 通常文件很大短时间内造成数据量快速增长数据库进行数据库读取时通常会进行大量的随机IO操作文件很大时IO操作很耗时。通常存储于文件服务器数据库只存储文件地址信息。
5.【强制】建表时关于主键表必须有主键强制要求主键为id类型为int或bigint且为auto_increment 建议使用unsigned无符号型。标识表里每一行主体的字段不要设为主键建议设为其他字段如user_idorder_id等并建立unique key索引。因为如果设为主键且主键值为随机插入则会导致innodb内部页分裂和大量随机I/O性能下降。
6.【建议】表中所有字段尽量都是NOT NULL属性业务可以根据需要定义DEFAUL值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
数据格式
1.【强制】小数类型为 decimal禁止使用 float 和 double。 说明在存储的时候float 和 double 都存在精度损失的问题很可能在比较值的时候得到不正确的结果。如果存储的数据范围超过 decimal 的范围建议将数据拆成整数和小数并分开存储。
2.【强制】如果存储的字符串长度几乎相等使用 char 定长字符串类型。
3.【强制】varchar 是可变长字符串不预先分配存储空间长度不要超过 5000如果存储长度大于此值定义字段类型为 text独立出来一张表用主键来对应避免影响其它字段索引率。
4.【推荐】单表行数超过 500 万行或者单表容量超过 2GB才推荐进行分库分表。 说明如果预计三年后的数据量根本达不到这个级别请不要在创建表时就分库分表。
5.【参考】合适的字符存储长度不但节约数据库表空间、节约索引存储更重要的是提升检索速度。 正例无符号值可以避免误存负数且扩大了表示范围
对象年龄区间类型字节表示范围人150岁之内tinyint unsigned1无符号值0 到 255龟数百岁smallint unsigned2无符号值0 到 65535恐龙化石数千万年int unsigned4无符号值0 到约 43 亿太阳约50亿年bigint unsigned8无符号值0 到约 10 的 19 次方
6.【强制】所有存储相同数据的列名和列类型必须一致避免隐式转换一般作为关联列如果查询时关联列类型不一致会自动进行数据类型隐式转换会造成列上的索引失效导致查询效率降低。
7.【建议】表要有预留字段。可评估预留1-3个字段以防后期表扩容变更。
8.【建议】定长和非定长数据类型的选择。decimal不会损失精度存储空间会随数据的增大而增大。double占用固定空间较大数的存储会损失精度。非定长的还有varchar、text。
9.【建议】优先选择符合存储需要的最小的数据类型、避免使用ENUM类型。
10.【建议】使用TIMESTAMP存储时间、DECIMAL代替FLOAT和DOUBLE存储精确浮点数、使用UNSIGNED存储非负整数、使用INT UNSIGNED存储IPV4。
11.【建议】尽可能不使用TEXT、BLOB类型。对于报文之类的大文本可以用TEXT、BLOB类型建议将该列单独设计为一张表并通过关联字段与主表关联进行查询或其他操作。
12.【建议】特定字段类型的选择建议。 1仅存储年使用YEAR类型日期使用DATE类型。 2时间类型使用datetime不要使用timestmp。 3钱币等精确浮点类型使用DECIMAL类型。 4数值字段增长上限不大不使用BIGINT。
--【示范】一个较为规范的建表语句CREATE TABLE user_info (
id int unsigned NOT NULL AUTO_INCREMENT COMMENT 自增主键,
user_id bigint(11) NOT NULL COMMENT 用户id,
username varchar(45) NOT NULL COMMENT 真实姓名,
email varchar(30) NOT NULL COMMENT 用户邮箱,
nickname varchar(45) NOT NULL COMMENT 昵称,
birthday date NOT NULL COMMENT 生日,
sex tinyint(4) DEFAULT 0 COMMENT 性别,
short_introduce varchar(150) DEFAULT NULL COMMENT 一句话介绍自己最多50个汉字,
user_resume varchar(300) NOT NULL COMMENT 用户提交的简历存放地址,
user_register_ip int NOT NULL COMMENT 用户注册时的源ip,
create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,
update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP COMMENT 修改时间,
user_review_status tinyint NOT NULL COMMENT 用户资料审核状态1为通过2为审核中3为未 通过4为还未提交审核,PRIMARY KEY (id),
UNIQUE KEY uniq_user_id (user_id),
KEY idx_username(username),
KEY idx_create_time_status(create_time,user_review_status)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT网站用户基本信息设计规范-索引
1.【强制】InnoDB表必须主键为id int/bigint auto_increment且主键值禁止被更新。
2.【强制】InnoDB和MyISAM存储引擎表索引类型必须为BTREE。
3.【强制】业务上具有唯一特性的字段即使是组合字段也必须建成唯一索引。 说明不要以为唯一索引影响了 insert 速度这个速度损耗可以忽略但提高查找速度是明显的另外即使在应用层做了非常完善的校验控制只要没有唯一索引根据墨菲定律必然有脏数据产生。
4.【强制】超过三个表禁止 join。需要 join 的字段数据类型保持绝对一致多表关联查询时保证被关联的字段需要有索引。 说明即使双表 join 也要注意表索引、SQL 性能。
5.【强制】在 varchar 字段上建立索引时必须指定索引长度没必要对全字段建立索引根据实际文本区分度决定索引长度。 说明索引的长度与区分度是一对矛盾体一般对字符串类型数据长度为 20 的索引区分度会高达 90%以上可以使用 count(distinct left(列名索引长度)) / count(星) 的区分度来确定。
6.【强制】页面搜索严禁左模糊或者全模糊如果需要请走搜索引擎来解决。 说明索引文件具有 B-Tree 的最左前缀匹配特性如果左边的值未确定那么无法使用此索引。
7.【推荐】建组合索引的时候区分度最高的在最左边。 正例如果 where a ? and b ?a 列的几乎接近于唯一值那么只需要单建 idx_a 索引即可。 说明存在非等号和等号混合判断条件时在建索引时请把等号条件的列前置。如where c ? and d ? 那么即使 c 的区分度更高也必须把 d 放在索引的最前列即建立组合索引 idx_d_c。
8.【推荐】防止因字段类型不同造成的隐式转换导致索引失效。
9.【参考】创建索引时避免有如下极端误解 1索引宁滥勿缺。认为一个查询就需要建一个索引。 2吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。 3抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。
10.【建议】主键的名称以pk_开头唯一键以uni_或uk_开头普通索引以idx_开头一律使用小写格式以字段的名称或缩写作为后缀。
11.【建议】多单词组成的columnname取前几个单词首字母加末单词组成column_name。如: sample 表 member_id 上的索引idx_sample_mid。
12.【强制】单个表上的索引个数不能超过5个。
13.【建议】在建立索引时多考虑建立联合索引并把区分度最高的字段放在最前面。
14.【建议】在多表 JOIN 的SQL里保证被驱动表的连接列上有索引这样JOIN 执行效率最高。
15.【强制】建表或加索引时保证表里互相不存在冗余索引。比如如果表里已经存在key(a,b)则key(a)为冗余索引需要删除。
16.【强制】不使用更新频繁的列作为主键如无特殊要求使用自增id作为主键。对于并发插入量较大且需要物理主键的表可以通过类似JAVA里的guid键值来代替。
17.【强制】索引创建选择唯一性较强的字段。
18.【建议】索引选择数据类型较短的字段。
19.【建议】尽量避免使用外键容易产生死锁由上层应用程序保证约束。
20.【建议】筛选text 或较长varchar类型字段需使用全文索引。
21.【建议】全文索引必须使用match函数 AGAINST函数不支持通配符匹配
例:select * from articles where match(title, body) against(database in natural language mode);22.【强制】重要的SQL语句必须被索引例如update\delete语句的where条件列order by \group by\distinct字段。
23.【建议】索引列建议 1出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列 2包含在ORDER BY、GROUP BY、DISTINCT中的字段 3多表JOIN的关联列 注意并不要将符合1和2中的字段的列都建立一个索引通常将1、2中的字段建立联合索引效果更好。
24.【建议】索引列顺序建议 1区分度最高的放在联合索引的最左侧区分度列中不同值的数量/列的总行数 2尽量把字段长度小的列放在联合索引的最左侧因为字段长度越小一页能存储的数据量越大IO性能也就越好 3使用最频繁的列放到联合索引的左侧这样可较少的建立一些索引
跟索引相关的SQL优化
1.【强制】如果有 order by 的场景请注意利用索引的有序性。order by 最后的字段是组合索引的一部分并且放在索引组合顺序的最后避免出现 filesort 的情况影响查询性能。 正例where a ? and b ? order by c索引a_b_c 反例索引如果存在范围查询那么索引有序性无法利用如WHERE a 10 ORDER BY b索引 a_b 无法排序。
2.【强制】利用覆盖索引来进行查询操作避免回表。 说明如果一本书需要知道第 11 章是什么标题会翻开第 11 章对应的那一页吗目录浏览一下就好这个目录就是起到覆盖索引的作用。
3.【推荐】利用延迟关联或者子查询优化超多分页场景。 说明MySQL 分页并不是跳过 offset 行而是取 offsetN 行然后返回放弃前 offset 行返回 N 行那当 offset 特别大的时候效率就非常的低下要么控制返回的总页数要么对超过特定阈值的页数进行 SQL 改写。 正例先快速定位需要获取的 id 段然后再关联
select t1.*
from 表1 as t1 , (select id from 表1 where 条件 LIMIT 100000, 20) as t2
where t1.id t2.id4.【推荐】SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别如果可以是 const 最好。 说明 1consts 单表中最多只有一个匹配行主键或者唯一索引在优化阶段即可读取到数据。 Java 开发手册黄山版 2ref 指的是使用普通的索引normal index。 3range 对索引进行范围检索。 反例explain 表的结果type index索引物理文件全扫描速度非常慢这个 index 级别比较 range 还低与全表扫描是小巫见大巫。
设计规范-视图
1.【强制】数据库不能包含具有相同名称的表和视图。
2.【强制】定义中不能引用TEMPORARY表不能创建TEMPORARY视图。
3.【强制】不能将触发程序与视图关联在一起。
4.【建议】视图以v_name命名。
设计规范-存储过程
在MySQL数据库中尽量避免使用存储过程Oracle数据库中午要求。
在存储过程中MySQL禁止使用下述语句
CHECK TABLES
LOCK TABLES, UNLOCK TABLES
LOAD DATA, LOAD TABLE
OPTIMIZE TABLE
SQL预处理语句PREPARE、EXECUTE、DEALLOCATE PREPARE目前仅适用于存储过程;不适用于存储函数和触发器;也不适用于在存储函数或者触发器里面调用含有预处理语句的存储过程。
创建存储过程以proc_开头命名函数以 func_开头命名。
设计规范-触发器
建议禁止使用触发器触发器可以理解为是一个隐藏的存储过程它不需要调用不需要显示调用维护起来容易被忽略。可以使用存储过程来替代。
设计规范-安全规范
用户权限管理按照最小权限原则分配用户权限避免过多权限导致的安全风险。
加密敏感数据对于敏感数据建议进行加密处理。
防范SQL注入使用预编译查询并避免动态生成SQL语句。
审计和日志记录记录关键操作日志确保可追溯性。
数据库架构设计原则
一、高可用架构选择
设计数据库时考虑实现高可用性方案如数据库镜像、复制和集群技术避免单节点故障引起的业务影响。采用主从复制或集群技术确保数据的高可用性和实时同步。配置自动故障切换机制如主备切换保证系统在故障发生时能迅速恢复。
二、扩展性
对于系统来说扩展性很重要尽量做到水平扩展。避免过度依赖纵向扩展同时具备纵向横向扩展的能力例如无状态应用应该多套负载均衡多活部署数据库分库架构。架构应该尽可能地分散负载减少单点故障的风险。
三、安全性策略
访问控制严格控制数据库访问权限遵循最小权限原则。
加密存储采用合适的加密算法加密核心数据防止黑客攻击和数据泄露。
日志审计启用数据库日志记录用户操作和系统事件以便安全审计和问题追溯。
四、数据完整性策略
必须遵循数据库设计的第二范式根据业务需要尽量满足第三范式
数据完整性尽量通过业务逻辑实现数据库设计应尽量避免使用大量的外键约束避免使用触发器。
五、规范化设计与性能之间的权衡策略
数据的标准化有助于消除数据库中的数据冗余。如果数据几余低数据的一致性容易得到保证如无特殊理由OLTP系统的设计应当遵循第三范式对于 OLAP 系统为了减少表间连接查询的操作提高系统的响应时间合理的数据几余是必要的。
六、数据生命周期管理
数据归档对历史数据和不常使用的数据进行归档处理减少主库的负担。
数据清理定期清理过期和无用的数据保持数据库的整洁。
七、数据备份策略
备份策略制定完善的备份策略包括全量、增量备份及异地备份。
SQL 语句
1.【强制】不要使用 count(列名) 或 count(常量) 来替代 count(※)count(※) 是 SQL92 定义的标准统计行数的语法跟数据库无关跟 NULL 和非 NULL 无关。 说明count(※) 会统计值为 NULL 的行而 count(列名) 不会统计此列为 NULL 值的行。
2.【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数注意 count(distinct col1 , col2) 如 果其中一列全为 NULL那么即使另一列有不同的值也返回为 0。
3.【强制】当某一列的值全是 NULL 时count(col) 的返回结果为 0但 sum(col) 的返回结果为 NULL因此使用 sum() 时需注意 NPE 问题。 正例可以使用如下方式来避免 sum 的 NPE 问题select ifnull(sum(column) , 0) from table;
4.【强制】使用 ISNULL() 来判断是否为 NULL 值。 说明NULL 与任何值的直接比较都为 NULL。 1NULLNULL 的返回结果是 NULL而不是 false。 2NULLNULL 的返回结果是 NULL而不是 true。 3NULL1 的返回结果是 NULL而不是 true。 反例在 SQL 语句中如果在 null 前换行影响可读性。 select * from table where column1 is null and column3 is not null而 ISNULL(column) 是一个整体简洁易懂。 从性能数据上分析ISNULL(column) 执行效率更快一些。
5.【强制】代码中写分页查询逻辑时若 count 为 0 应直接返回避免执行后面的分页语句。
6.【强制】不得使用外键与级联一切外键概念必须在应用层解决。 说明概念解释学生表中的 student_id 是主键那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id同时触发成绩表中的 student_id 更新即为级联更新。外键与级联更新适用于单机低并发不适合分布式、高并发集群级联更新是强阻塞存在数据库更新风暴的风险外键影响数据库的插入速度。
7.【强制】禁止使用存储过程存储过程难以调试和扩展更没有移植性。
8.【强制】数据订正特别是删除或修改记录操作时要先 select避免出现误删除的情况确认无误才 能执行更新语句。
9.【强制】对于数据库中表记录的查询和变更只要涉及多个表都需要在列名前加表的别名或表名进行限定。 说明对多表进行查询记录、更新记录、删除记录时如果对操作列没有限定表的别名或表名并且操作列在多个表中存在时就会抛异常。 正例select t1.name from first_table as t1 , second_table as t2 where t1.id t2.id; 反例在某业务中由于多表关联查询语句没有加表的别名或表名的限制正常运行两年后最近在某个表中增加一个同名字段在预发布环境做数据库变更后线上查询语句出现出 1052 异常 Column ‘name’ infield list is ambiguous。
10.【推荐】SQL 语句中表的别名前加 as并且以 t1、t2、t3、…的顺序依次命名。 说明 1别名可以是表的简称或者是依照表在 SQL 语句中出现的顺序以 t1、t2、t3 的方式命名。 2别名前加 as 使别名更容易识别。 正例select t1.name from first_table as t1 , second_table as t2 where t1.id t2.id;
11.【推荐】in 操作能避免则避免若实在避免不了需要仔细评估 in 后边的集合元素数量控制在1000 个之内。
12.【参考】因国际化需要所有的字符存储与表示均采用 utf8 字符集那么字符计数方法需要注意。 说明 select length(“轻松工作”)–返回为 12 select character_length(“轻松工作”)–返回为 4 如果需要存储表情那么选择 utf8mb4 来进行存储注意它与 utf8 编码的区别。
13.【参考】truncate table 比 delete 速度快且使用的系统和事务日志资源少但 truncate 无事务且不触发 trigger有可能造成事故故不建议在开发代码中使用此语句。 说明truncate table 在功能上与不带 where 子句的 delete 语句相同
14.【强制】程序端 select 语句必须指定具体字段名称禁止写成*。
15.【强制】禁止like “%abs”做where条件会全表扫描且不能用索引。
16.【强制】Where条件里不要对列使用函数不会引用索引除非谓词列已有函数索引。
17.【强制】禁止单条语句同时更新多个表。
18.【强制】杜绝大事务事务要尽量简单整个事务的时间长度不要太长。
19.【建议】能确定返回结果只有一条时使用 limit 1LIMIT分页注意效率LIMIT越大效率越低。
20.【建议】少用子查询改用JOIN子查询要在内存里建临时表。
21.【建议】多表JOIN的字段区分度最大的字段放在前面。
22.【建议】只读查询语句不要显式开启事务例如不要加begin或start transaction。
23.【建议】多条INSERT语句使用bulk insert提交INSERT INTO table VALUES(),(),()……。
24.【建议】避免大表join。
25.【建议】SQL语句不可以出现隐式转换比如 select id from 表 where id‘1’其中id列为非字符类型。
26.【建议】除非必要避免使用!等非等值操作符会导致用不到索引。
27.【建议】程序端insert语句指定具体字段名称不建议写成INSERT INTO t1 VALUES(…)。
28.【建议】除静态表或小表100行以内DML语句必须有WHERE条件且使用索引查找。
29.【建议】INSERT INTO…VALUES(XX),(XX),(XX)… 这里XX的值不要超过5000个。 值过多虽然上线很快但会引起主从同步延迟。
30.【建议】SELECT语句避免使用UNION推荐使用UNION ALL并且UNION子句个数限制在5个以内。在明显不会有重复值时使用UNION ALL而不是UNION。UNION会把两个结果集的所有数据放到临时表中后再进行去重操作UNION ALL不会再对结果集进行去重操作。
31.【建议】线上环境多表JOIN不要超过5个表。
32.【建议】减少使用ORDER BY和业务沟通能不排序就不排序或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU数据库的CPU资源是极其宝贵的。
33.【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句WHERE 条件过滤出来的结果集请保持在1000行以内否则SQL会很慢。
34.【建议】对单表的多次alter操作必须合并为一次。对于超过100W行的大表进行alter table必须经过DBA审核并在业务低峰期执行多个alter需整合在一起。 因为alter table会产生表锁 期间阻塞对于该表的所有写入对于业务可能会产生极大影响。
35.【建议】批量操作数据时需要控制事务处理间隔时间进行必要的sleep。
36.【建议】事务里包含SQL不超过5个。因为过长的事务会导致锁数据较久MySQL内部缓存、连接消耗过多等问题。
37.【建议】事务里更新语句尽量基于主键或UNIQUE KEY如 UPDATE… WHERE idXX。否则会产生间隙锁内部扩大锁定范围导致系统性能下降产生死锁。
38.【建议】避免使用insert into …on duplicate key update…在高并发环境下会造成主从不一致。
39.【建议】多表JOIN时需要注意以下 1区分度最大的字段放在前面 2核心SQL优先考虑覆盖索引 3避免冗余和重复索引 4索引要综合评估数据密度和分布以及考虑查询和更新比例。
40.【建议】超100万行的批量写UPDATE、DELETE、INSERT操作要分批多次进行操作。
41.【建议】对于大表使用pt-online-schema-change修改表结构。可避免大表修改产生的主从延迟、在对表字段进行修改时进行锁表。
