MySQL 开发准则

电玩女神 2022-12-02 13:57 177阅读 0赞

## 总结之阿里巴巴开发规范，如需看原版，请移步：[https://www.php.cn/java/java-alibaba-guifan.html][https_www.php.cn_java_java-alibaba-guifan.html] ##

## 命名规范 ##

【强制】对象名称必须用小写或者小写、下划线、数字组成。

`name;user_name;`

【强制】对象名称禁止使用 MySQL 保留关键字。

如 `ORDER` 等

【强制】对象名称要见名知其意，不要超过 32 个字符。

`nick_name`,`student_age`,`create_time`

【强制】临时表要以 tmp 为前缀，日期为后缀。

`tmp_export_user_20200721`

【强制】备份表要以 bak 为前缀，日期为后缀。

`bak_user_20200721`

【强制】表名不使用负数名词。

【强制】表字段表示是否概念，用 is\_xxx 表示。

`is_default`

【强制】索引名称，用特定\_字段表示。

普通索引：`idx_xxx`

联合索引：`un_xxx_xxx`

唯一索引：`uk_xxx`

## 表设计规范 ##

【强制】如无特殊需求，存储引擎使用 **InnoDB**。

1.  支持事务
2.  行级锁
3.  并发性能好

【强制】数据库和表的字符集统一使用 utf8 或者 utf8mb4。

不同字符集转化可能会产生乱码。

不同字符集比较前会进行字符转换，索引失效。

UTF8 每个字符占用3字节，占用空间小，但是不能存储 emoj，emoj 占用4字节。

UTF8MB4 每个字符占用4字节，是真正的 UTF8，推荐使用。

【强制】数据库表和字段都需要添加备注，更好理解建表思路。

【建议】数据库表考虑分库分表细节，推荐使用snowflake作为ID主键。

单表设计存储数据少于 500 万条或单表容量超过 2G。

不建议使用分区表，容易造成全表死锁，跨分区查询效率低。

【强制】表每一行中的每列数据大小相加不能大于 65535 byte。

【强制】不要设置预留字段，更改会锁表。

【强制】不要保存文件等大的二进制数据。应放到文件服务器中。

【建议】InnoDB 字符集默认排序使用 \_general\_ci 和 \_unicode\_ci，推荐使用 \_general\_ci。

ci不区分大小写

cs区分大小写

general速度更快，准确性稍低

【建议】表必备三个字段，id、create\_time、modify\_time。

id：unsigned bigint。单表时主键单表时自增1，需要分表使用snowflake。

create\_time：datatime。创建时间。

modify\_time：datatime。修改数据更新时间。

【强制】存储过程设计要合理，尽量少用。

过度复制逻辑容易死锁。

可以替换为在后端业务层或者脚本实现。

【强制】不要使用触发器。

高并发情况下不理想。

可以用事务替代。

【强制】数据量大的表要使用pt工具修改表结构。

pt-online-schema-change。

原理是新建一张表并复制原表结构与数据，最终删除原表，可以有效避免行锁及表锁。

字段设计规范

【强制】表字段表示是否概念，即is\_xxx。

1表示是，0表示否

使用 unsigned tinyint

【强制】小数类型都使用decimal型。

decimal精确。

如果超出decimal范围建议分两个字段存储。

【强制】ip及手机号类固定长度字段，要用char。

【强制】选择合适的存储长度。

可以减少表存储空间。

可以减少索引长度，增加索引效率。

【建议】避免使用TEXT、BLOB数据类型。

内存临时表不支持TEXT和BLOB。会使用磁盘临时表，降低查询速度。

TEXT和BLOB需要单独成表，提高查询效率。

【建议】避免使用ENUM数据类型。

枚举类型order by效率低。

禁止使用数字作为枚举值。

在与php使用上1和’1’差别大，PHP是弱引用很容易把’1’写为1，1为key，’1’为内容。

【建议】尽可能把列定义设置为 NOT NULL。

索引NULL列，会额外增加开销，占用更多表空间。

要做计算或者比较时，会对 NULL 做特别处理。

在 SQL 中对 NULL 进行判断会全表扫描。

【强制】时间类型不要使用 varchar 或 int 等。

使用 timestamp，占用4字节与int相同，查询计算比int快。

timestamp 取值范围，1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。

使用 datatime，占用8字节，明确日期时间，超出timestamp用datatime。

索引设计规范

【强制】不要使用外键和级联，应放在应用层去做。

外键与级联更适合单机及低并发，不适合分布式和高并发集群。

外键即外键约束，会影响写操作（插入速度），降低性能。

级联更新是强阻塞，存在更新风暴的风险。

【强制】一张表不要超过5个索引。

索引过多会降低性能。

合理分配索引会提高性能。

【强制】联合索引的左侧原则可以减少每个字段单独建立索引。

避免每个字段都建立索引。

联合索引区分度高的放在最左边。

联合索引如果存在非等号和等号混合时，把等号的索引放在最左边。

联合索引左侧原则，一定要注意顺序。

【强制】InnoDB 必须有主键。

结合建表四个必要字段，id作为主键。

InnoDB 属于索引组织表，逻辑顺序和索引的顺序相同。

单表时主键自增1。

预计三年内达到500万条，需要使用 snowflake 等分布式id生成主键。

不要使用 uuid、hash、md5 等作为主键，要有顺序概念。

【建议】查询想走特定索引时可以用force index。

MySQL的 optimizer 会执行它认为最优索引，但是往往不是我们需要或者最优的。

使用 force index 可以强制使用索引，结合 explain 使用，确认为最优。

【强制】有唯一索引需求，该字段就应设置唯一索引。

即使该字段是在联合索引内，也要单独设置唯一索引。

唯一索引对insert速度影响可以忽略，但是提高查询速度和唯一性是明显的。

应用层也建议做校验控制，但是根据墨菲定律，只要有可能就会出现脏数据。

【强制】varchar型设置索引要设置索引长度。

不设置默认是全部长度。

建议索引长度为20，区分度可以达到90%。

区分度计算公式：select count(distinct left(列名, 索引长度))/count(\*) FROM 表名。可以查出区分度百分比。

【强制】模糊查询最好用搜索引擎。

禁止使用like %str和like %str%。因为不走索引。

可以使用like str%。走索引。

也可以走全文索引，但是需要看配置，还是推荐搜索引擎。

【强制】order by 需要注意索引的有序性。

order by后接索引应该索引的一部分，如果是联合索引，应该是联合索引的最后，避免出现file\_sort，影响查询性能。where a=? and b=? order by c那么索引是（a,b,c）。

file\_sort出现是没有走索引或者联合索引。出现情况：where a=? order by b索引是a。改进优化：where a=? order by b索引是（a,b）。

【强制】避免冗余索引。

重复索引：primary key(id)、index(id)、unique index(id)。

冗余索引：key(a,b,c)、key(a,b)、key(a)。

【强制】查询频率较高的sql语句，应该使用覆盖索引。

覆盖索引不是真正的索引，是一种使用索引方式。

原理是从索引中查询出想要内容，而不用回表查询，提高查询效率。

表现是explain的extra为Using index。

例如select user\_no from user order user\_age = 28索引为user\_no时效率低，索引为（user\_no,user\_age）时为覆盖索引，查询效率高。

【强制】避免隐式类型转换。

定义和使用不同数据会造成隐式转换。

隐式转换会不走索引，降低查询效率。

如select user\_age from user where user\_no='111'

【强制】避免在字段位置写表达式，不走索引。

反例：`select user_no from user where user_age*2 = 36`。

正例：`select user_no from user where user_age = 36/2`。

查询优化

【强制】SQL性能优化目标，由高到低。

const。基本是只有一行匹配。

ref。基本是走普通索引。

range。基本是走范围索引。

index。走索引最差，和全表查询相似。

NULL。不走索引，全表查询。

【强制】不适用索引的几种情况。

不等式：!=、<>。

null判断：is null、is not null。

like模糊查询：like %a、like %a%

not in。

【建议】避免使用IN操作，如果避免不了，需小于1000条。

多表查询IN会影响查询效率。

可以用between替代。

IN(select \* from)索引会失效，可以使用join（left、right、inner、full）来实现。

【建议】join优化。

最好在三张表之内，最多不要超过5个，理论可以61个。

on关联字段类型要相同。

每关联一个表就会多分配一个关联缓存，和join\_buffer\_size设置相关。占用内存过大会形成溢出，影响性能和稳定性。

left join的驱动表是左侧表。

inner join的驱动表是数据少的表。

right join的驱动表是右侧表。

MySQL没有full join，可以用SQL实现。例如：`select * from A left join B on B.name = A.name where B.name is null union all select * from B`。

尽量利用小表驱动大表，可以减少循环嵌套次数。

straight join的使用。前提是inner join内连接。inner join优先查询小表，但有group by、order by等file\_sort,Using temporary时会想改变优先查询表顺序，这时可以使用straight join。straight join强制优先查询表为左侧表。

一定要是内连接才能使用straight join，否则数据可能不准确。

【强制】禁止select \* 出现。

select \* 增加额外解析成本。

增减字段对前端映射不一致。

无用字段增加网络消耗。

无法使用覆盖索引。

【强制】禁止使用不带字段的insert出现。

正例：insert into user(user\_no,user\_age) values (123,18)

反例：insert into user values (123,18)

【强制】尽量避免子查询。

子查询一般在IN中。

子查询会创建临时表，不会存在索引。

结果集大的子查询，性能越差。

可以使用join替代。

【强制】查询一条或者是否有数据时，要使用limit 1。

索引效率最高。

explain的type为const。

【建议】order by字段没有索引就不要排序。

order by字段有索引会按索引排序。没有索引影响效率。

可以设置索引，或者覆盖索引。

【建议】尽量不使用or。

同一字段用IN、between等替代or，因为很多情况不会走索引。

多字段下or两边都需要是索引且其他条件也是索引，才会走索引。

最好使用union、union all来替换。

【建议】尽量用union all替代union。

union会集合后进行唯一性去重，涉及到排序，加大资源开销。

在没有重复数据情况强制使用union all。

【建议】拆分大且复杂的SQL。

一条SQL只会使用一个CPU。

拆成多个小SQL可以通过并行提高查询效率。

【强制】禁止使用ORDER BY RAND()

随机排序性能差。

可以用其他SQL替换。

原：`select id from 'dynamic' order by rand() limit 1;，`

新：`select id from 'dynamic' t1 join (select rand() * (select max(id) from 'dynamic') as nid) t2 on t1.id > t2.nid limit 1`;注意，此查询只能随机一条id，并连续查询该id的顺序条数，具体情况具体分析，适用随机取一条，不应用随机取多条。

随机取多条解决方案：先查询所有id->在后端业务层做随机id->IN该id组。

rand()取值范围：\[ 0 , 1 )。

【强制】禁止对where条件字段进行函数转换。

不走索引。

正例：`select user_age from user where create_time>'20190320'`

反例：`select user_age from user where date(create_time)>'20190320'`

【建议】in、exists、not in、not exists。

in是子查询，优先查询驱动表为内表，所以适合内表数据小的情况。

exists优先查询驱动表为外表，适合外表数据小的情况。

不建议使用not in和not exists，不走索引且容易混淆。

建议用其他SQL替代。

反例：`select a.user_age from user a where a.user_no not in (select b.user_no from user_info b)`。