MySQL进阶系列:总结篇
MySQL进阶系列总结篇
mysql系列写到这基本已经结束了,此篇便是最终篇了,后续如果还有可能就是一些补充或者自己在实际开发中遇到的问题,之前的历史文章已经写了19篇,这些内容让你做一个高级开发是够够的了(DBA就别看了)。
表结构设计规范(来源携程的规范)
1 创建表的存储引擎必须是InnoDB,不能选择其他引擎。
2 每张表必须有主键且不能使用联合主键:每行数据都能被唯一区分。
3 默认使用utf8mb4字符集:utf8mb4字符集支持emoji表情符。
4 每张表必须有modifytime 字段:该字段的定义为 “ ‘modifytime' TIMESTAMPS)
NOT NULL DEFAULT CURRENT TIMESTAMP(3) ON UPDATE CURRENT
TIMESTAMP(3) COMMENT ’更新时间‘ ”,并强制对该字段创建索引。
5 推荐使用 createtime 字段:默认值设置为 CURRENT TIMESTAMP,该字段用手记录行创建时间。
6 不允许使用外键:表之间的关联应该通过应用层进行保证。
7 自增字段必须是主键或唯一索引:避免复杂表结构。
8 所有声明为 NOT NULL 的字段必须显式指定默认值。
9 使用 TEXT 字段需要审批:TEXT 字段对 MysQL 主从复制、网络带宽、数据库性能影响都比较大,是数据库不稳定的重要因素之一,如果必须使用TEXT 字段,并且数据量不大,就需要走审批流程。
10 禁止使用视图、触发器和存储过程:应用逻辑应当放在应用上。
11 表和字段必须添加备注说明,以利于被数据字典采集和展现。
总的来讲,数据库表结构设计规范的限制并不多。表结构设计需要经过设计流程,在流程中进行控制,才能把这些规则落实。只有保持一致的规范,后续的数据库自动化运维才能顺畅。
---《携程架构实践》
高效查询的技巧
- 避免使用select * 查询数据,明确查询需要的字段。
- 单表的字段列不宜太多。
- 避免一次查询大量数据,可以分页或者分次查询。
- 注意避免索引失效的场景:如以%开头的like查询,存在类型转换,索引列有计算,组合索引不使用第一列索引字段等。
- 尽量使用主键索引,避免回表。
- 熟悉前缀索引,索引覆盖,索引扫描。
- 单表索引不宜太多,避免索引重复,比如 单字段索引和组合索引。
- 使用区分度高而且更新不频繁的字段创建索引。
- 能使用limit 尽量使用limit。
- 联表查询的时候关联表不宜太多,最多保持3个。
- 善于使用explain,可以了解是否正常使用预想的索引。
表结构变更的几种方式:
-
使用原生的语法,直接改结构,操作简单 但是风险高,操作期间容易导致服务器负载上升。
-
使用开源的pt-osc工具,其原理是对要变更的表设置触发器,收集语句的变化,保证临时表和变更表的数据一致性,触发器对服务器的性能影响较大,尤其是对热表,一般情况也可以使用的。具体的使用可以参考历史文章pt-online-schame-change
-
推荐使用gh-ost工具,,其原理是通过binlog来复制数据,并应用到临时表上,然后进行表名的操作。具体使用方式请参考GitHub地址:github.com/github/gh-o… gh-ost发布对服务器性能的影响远远小于前面两种方案,使用gh-ost有两个限制:
(1)数据库的主从复制必须是行模式;
(2)需要特别注意剩余空间问题和主从复制延迟问题。
我是纪先生,用输出倒逼输入而持续学习,持续分享技术系列文章,以及全网值得收藏好文,欢迎关注公众号,做一个持续成长的技术人。
mysql进阶系列历史文章
3. MySQL进阶系列:mysql中MyISAM和InnoDB有什么区别;
4. MySQL进阶系列:mysql中表设计如何更好的选择数据类型;
5. MySQL进阶系列:数据库设计中的范式究竟该如何使用;
6. MySQL进阶系列:一文详解explain各字段含义;
7. MySQL进阶系列:为什么mysql使用B+作为索引的数据结构;
15. MySQL进阶系列:你需要了解的几种MySQL日志;
转载自:https://juejin.cn/post/7134890534371328014