MySQL进阶系列：总结篇

站长

2023年03月25日 11:55 · 阅读数 21

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）需要特别注意剩余空间问题和主从复制延迟问题。