Loading... 本篇内容主要是按照**整体设计**、**字段设计**、**索引设计**以及**SQL书写规范**四个方面讲解了MySQL的开发规范,以及最后的**MySQL关键字**附录。其中规范的后两部分涉及到挺多的**SQL调优**相关内容,将打算再下一篇结合实例详细说明。 <!--more--> ## 一、整体设计 1. 对于范式设计需要考虑**应用类型**以及**数据分布**情况,来灵活选择**范式化**还是**反范式化**。 * 范式化 **优点**:没有或者很少的冗余数据,更新速度快(通常表也比较小)<br>**缺点**:数据量大时,读一次需要涉及到多表关联,代价十分昂贵 * 反范式化 **优点**:数据通常在一张表里,避免多表关联查询。(数据比内存大的多的情况下也会比关联查询效率更高,因为全表扫描基本上是顺序IO)<br>**缺点**:数据冗余 2. 不要让数据库做它不擅长的事情,如:运算、MD5等。 3. 默认使用utf8字符,如果涉及到emoji表情就选用utf8mb4。(8开始最好都选用后者) 4. 默认使用InnoDB引擎。 * **`InnoDB`**(默认):事务优先,适合高并发操作,行锁。 * **`MyISAM`**:性能优先(表锁) 5. 注意**垂直拆分**:大数据字段独立拆分,降低数据页分裂概率,提升查询效率。 * **概念**:把一张表按模块拆分成多张表(或不同库),如:不常用的字段放一张表;text、body等大字段拆分成一张表;经常组合查询的放一起。 垂直拆分一般在数据库设计的时候就执行了,查询的时候用 `join`关联起来即可。 * **场景**:一般和读写分离联动,如:做读写分离后的日志表,也需要拆分成写日志和读日志;支付系统根据上游调用拆分成用户支付和商家支付两大模块。 * **优点**:系统之间整合、扩展容易,且拆分后互不影响;数据维护简单;上游调用降级; * **缺点**:事务处理复杂;几乎完全重复的轮子;可能存在单库性能瓶颈(需要用水平拆分)。 6. 注意**水平拆分**,解决单表数据量大的问题。 * **概念**:把一个表按照某种规则把数据划分到不同表或数据库里。 * **场景**:单表大量数据,通过id取模方法拆分成多个表;总表根据根据地区、年份,定期拆分; * **优点**:解决单表大数据问题,提高系统稳定性和负载。 * **缺点**:拆分规则难以抽象;事务一致性难以解决;跨库join性能差(无论哪种拆分,join都尽量不要跨库) * **拆分原则**:通常用取模的方式进行拆分,进行水平拆分后的表字段和类型与原表保持一致,但是要去掉auto_increment自增长。 如:4000w的user表拆分成 `user[1、2、3、4]`四份。通过id取模 `id%4+1`来确定是查哪张user表,id=17则查user2。<br>**注**:`insert`时还需要提供一张临时表 `uid_temp`来提供自增的id:<br>`insert into uid_temp values(null);` 7. 注意**冷数据拆分**,保障热表数据精简,保证单表数据尽量精简。(这就要考量到范式化和反范式化的问题了,按需决定) 8. 专用数据库,分离线上应用,**切记多个应用部署同一个数据库**。 9. 单个数据库表数量不超过 `500`,单表数据量不超过 `1000w`。 注:单表数据不超过1000W只是一个基准数据,实际情况需要考虑数据长度、服务器配置、数据库配置等等,因此1000W只是一个参考数据,默认情况下我们需要遵循,如果有特殊情况再另外提出说明。 10. MySQL对象(库名、表名、字段名、索引名、函数名等)全部使用小写字母,并用下划线分割。做到见名知意,注意各种如 `idx_xxx`等命名规范,**切记千万不要用拼音或不正当的缩写,以及保留字**。 11. 数据库尽量拒绝大SQL、大事务、大批量。 注:大事务、大批量其实可以用NoSQL去处理。而拒绝大SQL就相当于不推荐存储过程或者存储函数了,从MySQL的角度来说也确实如此,它对存储过程/函数的支持其实很不友好,在之前的Mybatis相关部分就有说明过。至于关于[思考,撸一段 SQL ? 还是写一段代码?](https://mp.weixin.qq.com/s/EmmDI_KMxBF7IF_vXnFtFg)可以看这里。 12. 使用框架开发时需要关闭无实际意义的框架自带的请求,比如在生产环境中发现某些php框架会自带一些比如 `SHOW FULL TABLES FROM ‘BASE_NAME’LIKE ‘TABLE_NAME’`或者查询表结构这样的请求,而且请求量大,都导致了慢查询,如果这些请求无实际生产意义就需要关闭,如果像查询表结构这样的类静态数据就使用redis或者memcached缓存代替 13. **数据库中的表、字段都要做好注释,特别是类型相关的字段,必须标注清楚0、1、2等分别代表的意义。** --- ## 二、字段设计 1. 字段长度尽可能简短,能用 `tinyint`就绝不用 `int`,**能用 `timestamp`就绝不用 `datetime`**。 **注**:实际项目中 `datetime`类型统一用 `int`并且不加无符号的 `unsigned`限制,否则表示出生日期就无法存1970以前的;存储年用 `year`类型;存储日期用 `date`类型;`timestamp`单位用秒而不要用毫秒。 2. `InnoDB`引擎下字符串默认用 `varchar`,长度不变情况下直接使用 `char`,其中 `varchar(M)`中的M指的是字符数。 3. 能用 `varchar`就不用 `text/blob/body`。确实需要的话需要考虑到上文所说的垂直拆分问题,避免出现数据页频繁分裂降低性能。 4. **使用 `decimal`存储精确浮点数,尤其是金钱相关的。**float/double都可能出现精度丢失等问题。 5. 相同用途字段或者是需要关联的字段,需要**保持一致的数据类型**(长度最好也一致)。杜绝隐式转换,这会造成**索引失效**! 6. **字段设置显式约束:`NOT NULL`。** * **原因**: (1)如果字段定义默认NULL的话,则基于该字段的所有复合索引都会失效。<br>(2)浪费存储空间,因为InnoDB需要额外的一个字节存储。<br>(3)表内默认值NULL过多的话会影响优化器选择执行计划。 * **处理方案**:设置业务上的默认值。 7. IPV4地址如果做查询功能使用需要使用int,这种方式只能存储IPv4,存储不了IPv6,如果仅仅只是做展示功能可以使用char(15) 8. 内容明确,不做变更的类型代码可以用 `tinyint`进行转义,不建议用枚举类型。 **注:**<br>(1)这条规则主要是针对PHP,因为PHP是弱类型。如:`insert into ..... set a= 1`,你没法知道你是想 `a= '1'`还是 `a= 1`(前者是插入值1,后者是插入enum的第一个值。)尤其php弱类型的,如果int的,很少有人在sql里加双引号。<br>(2)对于Java来说,类中的Enum在数据库中用 `varchar`与之对应即可。<br>(3)当使用enum或者set类型时需要注意避免与char/varchar关联查询(这点还不是很清楚) 9. 字段长度应适当冗余。 ### 备注 **备注1:整数类型取值范围与存储长度** | 整数类型 | Signed | Unsigned | 长度 | | --------- | ---------------------------------------- | ---------------------- | ------ | | Tinyint | -128~127 | 0~255 | 1byte | | Samllint | -32768~32767 | 0~65535 | 2bytes | | Mediumint | -8388608~8388607 | 0~16777215 | 3bytes | | Int | -2147483648~2147483647 | 0~4294967295 | 4bytes | | Bigint | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 | 8bytes | **备注2:字符类型信息** | 类型 | 填充方式 | 长度 | 适用场景 | 注意事项 | | ------- | -------- | --------------- | ---------------------------------------------------------------------------- | ----------------------------------------------------------------------------- | | char | 定长 | 0-255(字符) | 适合存储短字符串或者长度相同或者接近的字符串。如:身份证号,hash后的密码等等 | 1、数据保存时末尾空格填充2、读取时尾部空格会被截断 | | varchar | 变长 | 0-65535(字节) | 1、最大长度比平均长度大很多2、更新频率低 | 1、额外的1—2个字节存储数据长度记录2、更新时容易产生碎片3、尾部空格不会被截断 | **注释**:当然这不是说varchar是变成就可以随意设置长度,varchar(10)跟varchar(100)使用上区别还是很大的。**MySQL会按照最大长度分配内存块来保持数据,更糟糕的是如果需要使用内存临时表的操作(比如分组,排序等等),性能会有很大的落差**,因此即便是使用varcahr也不能盲目慷慨,按需分配才是王道。 **备注3:`blob` & `text`** | 类型 | 保存形式 | 特点 | 区别 | | ---- | -------------------- | ----------------------------------------------------- | ---------------------------------------- | | BLOB | 二进制(字节字符串) | 1、不能设置默认值2、尾部空格不会截断3、不能做完整索引 | 1、没有字符集跟排序规则2、数据大小写敏感 | | TEXT | 字符(字符字符串) | 1、不能设置默认值2、尾部空格不会截断3、不能做完整索引 | 1、有字符集跟排序规则2、数据大小写不敏感 | | 类型 | 长度计算 | 最大长度 | | ---------------------- | ---------------------------- | -------- | | TINYBLOB, TINYTEXT | L+1个字节,其中L < 2的8次方 | 255B+1B | | BLOB, TEXT | L+2个字节,其中L < 2的16次方 | 64K+2B | | MEDIUMBLOB, MEDIUMTEXT | L+3个字节,其中L < 2的24次方 | 16M+3B | | LONGBLOB, LONGTEXT | L+4个字节,其中L < 2的32次方 | 4G+4B | **注释**:使用中需要注意 `max_sort_length`以及 `max_allowed_packet`的设置,以及不允许使用内存临时表。 **备注4:`datetime` & `timestamp`** | 类型 | 存储范围 | 默认值 | 长度 | 时区 | | --------- | ----------- | -------- | ----- | ------ | | datetime | 1000-9999年 | 空 | 8Byte | 不依赖 | | timestamp | 1970-2037年 | 当期时间 | 4Byte | 依赖 | --- ## 三、索引设计 1. **普通索引**按照 `idx_col`命名,**唯一索引**按照 `unq_idx_col`命名。 例如:`idx_name`、`idx_name_age`、`unq_idx_name`、`unq_idx_name_age` 2. 不使用全文索引(用了也没快多少),需要的时候可以考虑使用第三方索引程序,比如:sphinx、lucene等等。 3. `InnoDB`表主键必须尽量简短,建议设置一个自增主键,杜绝联合主键。(自增前提是不需要做水平拆分) **注**:`InnoDB`中,当联合主键存在无序插入、频繁更新或主键过长都会影响表的读写效率。 4. 索引的建立要适度。提升查询效率是没错,但也加大了写入的开销。 5. 复合索引一般比普通索引更加合适。因为在绝大部分情况下普通的单一字段索引可以看成是复合索引的重复索引。 6. 添加复合索引时,要考虑索引列顺序,正常情况下把选择性最高的字段放在最前面。 **注**:还有一点是组合索引的顺序要与查询的sql顺序对应,所以最好是sql和组合索引中都是按照选择性最高的字段最靠前的规则。 7. 区分度很小的字段没必要建立索引(比如性别这样的字段) 8. 对于较长的 `char/varchar`字段应该建立前缀索引 9. 生产业务的查询SQL必须使用索引,且要小心索引失效。 注:生产业务的查询SQL必须严格按照规范使用索引,特别是 `join`、`distinct`、`group by`、`order by`等类型的SQL 10. 查询SQL时使用运算和函数,会造成索引失效。 11. 使用前模糊匹配,会造成索引失效 12. 相同用途字段或者是需要关联的字段,需要**保持一致的数据类型**(长度最好也一致)。杜绝隐式转换,这会造成**索引失效**! (同**字段设计**的第5点。) 13. SQL存在范围查询时,需要把范围查询字段放在最后一个,如果范围较小可以用 `in()`,尽量避免多个范围查询。 **注**:用 `in()`代替范围查询是为了让优化器使用更多的索引列,但是 `in()`里面过多的条件也会降低查询性能,因为 `in()`条件组合是以指数形式递加的,过多的条件会浪费大量的CPU运算以及内存,特别在老版本的MySQL这种现象更严重。 14. 强制读主库。 有时候在主库中插入数据后,希望立即从主库读出来。在 `kingshard`中由于读写分离的原因,select默认会发送到相应node的从库上。但是只需要在select语句中加入相应的注释项(`/*master*/`),就可以将select语句发送到主库。 ```mysql mysql> select /*master*/ * from kingshard_test_conn; +----+----------+------+-------+------+------+ | id | str | f | e | u | i | +----+----------+------+-------+------+------+ | 1 | a | 3.14 | test1 | NULL | NULL | | 5 | ""''\abc | NULL | NULL | NULL | NULL | | 6 | 中国 | NULL | NULL | NULL | NULL | +----+----------+------+-------+------+------+ 3 rows in set (0.01 sec) ``` --- ## 四、SQL书写规范 1. 禁止 `select *`。这点在阿里的开发手册上明确禁止了。因为这样写不仅加大了数据库的解析成本,不需要的字段还会给网络带宽以及服务器资源都带来不必要的额外开销。 2. 去掉无意义的查询条件,比如 `where 1 = 1`。**尤其是写在前端的话很容易造成SQL注入!** (但是对于老的JDBCTemplate的话还是没办法,除非重做。) 3. 尽量不要子查询。 4. 尽量减少表关联查询。 5. InnoDB引擎表不允许在线实时统计(类似 `select count(*) from table_name;`这样不带检索条件的全表统计),如果要预估全表数量可用 `select max(id) - min(id) from table_name`。 6. 用括号确定 `and`、`or`的优先级,同时去掉不必要的括号。 7. 尽量不使用存储过程、触发器、函数等。 (起码在MySQL下,是如此。) 8. 尽量避免临时表产生。存在一个 `order by`子句跟不同 `group by`子句的SQL会产生临时表,`order by`或 `group by`包含联接查询中第一张表以外的其他表的列也会创建临时表 9. 大部分情况下,一个复杂的SQL可以拆分成几个简单的SQL。也建议这么做(跟**整体设计**第11点的讨论类似)。 **优点如下:**①缓存效率更高;<br>②减少锁竞争;<br>③更容易做到高性能跟可扩展;<br>④查询性能可能提升;<br>⑤减少冗余记录查询 10. 慎用 `FOR UPDATE`和 `LOCK IN SHARE MODE`,防止锁扩大。 11. `UPDATE`、`DELETE`语句不使用 `LIMIT` ,不然容易造成主从不一致。 ### 踩坑场景 * 对于数据库的ddl操作,同一张表的所有操作应该放在同一个SQL完成,而不是拆分成多条SQL。 ```mysql ALTER TABLE `t_test` ADD COLUMN `col1` varchar(20) NOT NULL DEFAULT ''; ALTER TABLE `t_test` DROP COLUMN `col2`; ALTER TABLE `t_test` ADD INDEX `idx_col1` (col1); ALTER TABLE `t_test` DROP INDEX `idx_col3`; 改写成: ALTER TABLE `t_test` ADD COLUMN `col1` varchar(20) NOT NULL DEFAULT '', DROP COLUMN `col2`, ADD INDEX `idx_col1` (col1), DROP INDEX `idx_col3`; ``` **注**:MySQL在5.6以前版本并没有提供online ddl的功能,所有ddl操作都是一个创建临时表+全表X锁的操作,因此需要将多个ddl合并成一个,减少表锁带来的不可用时间 * 求N天以前或者N天以后的日期千万不能使用 `curdate()-/+N`这样的写法,还是要老老实实使用 `INTERVAL`函数,比如说今天是20140801,你使用 `curdate()-1`来获取昨天的日期,那就直接悲剧了。 ```mysql "root@localhost Mon Aug 4 17:53:03 2014 17:53:03 [(none)]>select curdate(); +------------+ | curdate() | +------------+ | 2014-08-04 | +------------+ 1 row in set (0.00 sec) "root@localhost Mon Aug 4 17:53:06 2014 17:53:06 [(none)]>select curdate()-1; +-------------+ | curdate()-1 | +-------------+ | 20140803 | +-------------+ 1 row in set (0.00 sec) "root@localhost Mon Aug 4 17:53:09 2014 17:53:09 [(none)]>select curdate()-10; +--------------+ | curdate()-10 | +--------------+ | 20140794 | +--------------+ 1 row in set (0.00 sec) ``` * 多考虑使用limit n,尽量少用limit m,n,特别是偏移值很大的时候(m的值很大),如果在InnoDB引擎下确实需要用到分页技术,可以考虑使用延迟关联来实现优化: ```mysql select <cols> from table where col1 = xxx order by col2 limit 100000,10; 改写成 Select <cols> from table a inner join (select <pk_col> from table where b.col1=xxx order by b.col2 limit 100000,10) as b using (<pk_col>); ``` * **不同字段的 `or`或者 `in`大于等于3次考虑用 `union all`代替**,同一字段 `or`用 `in`代替,例如: ```mysql select <cols> from test3 where id1 = 1 or id2 = 2 or id3 = 3; 可以用 select <cols> from test3 where id2 = 1 union all select <cols> from test3 where id2 = 2 union all select <cols> from test3 where id2 = 3代替 select <cols> from test3 where id2 = 1 or id2 = 2 or id2 = 3; 可以用 select <cols> from test3 where id2 in (1,2,3)代替 ``` --- ## 附:Mysql关键字 | ADD | ALL | ALTER | | ------------------ | ------------------- | ------------------ | | ANALYZE | AND | AS | | ASC | ASENSITIVE | BEFORE | | BETWEEN | BIGINT | BINARY | | BLOB | BOTH | BY | | CALL | CASCADE | CASE | | CHANGE | CHAR | CHARACTER | | CHECK | COLLATE | COLUMN | | CONDITION | CONNECTION | CONSTRAINT | | CONTINUE | CONVERT | CREATE | | CROSS | CURRENT_DATE | CURRENT_TIME | | CURRENT_TIMESTAMP | CURRENT_USER | CURSOR | | DATABASE | DATABASES | DAY_HOUR | | DAY_MICROSECOND | DAY_MINUTE | DAY_SECOND | | DEC | DECIMAL | DECLARE | | DEFAULT | DELAYED | DELETE | | DESC | DESCRIBE | DETERMINISTIC | | DISTINCT | DISTINCTROW | DIV | | DOUBLE | DROP | DUAL | | EACH | ELSE | ELSEIF | | ENCLOSED | ESCAPED | EXISTS | | EXIT | EXPLAIN | FALSE | | FETCH | FLOAT | FLOAT4 | | FLOAT8 | FOR | FORCE | | FOREIGN | FROM | FULLTEXT | | GOTO | GRANT | GROUP | | HAVING | HIGH_PRIORITY | HOUR_MICROSECOND | | HOUR_MINUTE | HOUR_SECOND | IF | | IGNORE | IN | INDEX | | INFILE | INNER | INOUT | | INSENSITIVE | INSERT | INT | | INT1 | INT2 | INT3 | | INT4 | INT8 | INTEGER | | INTERVAL | INTO | IS | | ITERATE | JOIN | KEY | | KEYS | KILL | LABEL | | LEADING | LEAVE | LEFT | | LIKE | LIMIT | LINEAR | | LINES | LOAD | LOCALTIME | | LOCALTIMESTAMP | LOCK | LONG | | LONGBLOB | LONGTEXT | LOOP | | LOW_PRIORITY | MATCH | MEDIUMBLOB | | MEDIUMINT | MEDIUMTEXT | MIDDLEINT | | MINUTE_MICROSECOND | MINUTE_SECOND | MOD | | MODIFIES | NATURAL | NOT | | NO_WRITE_TO_BINLOG | NULL | NUMERIC | | ON | OPTIMIZE | OPTION | | OPTIONALLY | OR | ORDER | | OUT | OUTER | OUTFILE | | PRECISION | PRIMARY | PROCEDURE | | PURGE | RAID0 | RANGE | | READ | READS | REAL | | REFERENCES | REGEXP | RELEASE | | RENAME | REPEAT | REPLACE | | REQUIRE | RESTRICT | RETURN | | REVOKE | RIGHT | RLIKE | | SCHEMA | SCHEMAS | SECOND_MICROSECOND | | SELECT | SENSITIVE | SEPARATOR | | SET | SHOW | SMALLINT | | SPATIAL | SPECIFIC | SQL | | SQLEXCEPTION | SQLSTATE | SQLWARNING | | SQL_BIG_RESULT | SQL_CALC_FOUND_ROWS | SQL_SMALL_RESULT | | SSL | STARTING | STRAIGHT_JOIN | | TABLE | TERMINATED | THEN | | TINYBLOB | TINYINT | TINYTEXT | | TO | TRAILING | TRIGGER | | TRUE | UNDO | UNION | | UNIQUE | UNLOCK | UNSIGNED | | UPDATE | USAGE | USE | | USING | UTC_DATE | UTC_TIME | | UTC_TIMESTAMP | VALUES | VARBINARY | | VARCHAR | VARCHARACTER | VARYING | | WHEN | WHERE | WHILE | | WITH | WRITE | X509 | | XOR | YEAR_MONTH | ZEROFILL | [1]: http://www.tangsong.fun/usr/uploads/2020/12/1190492039.jpg Last modification:August 19, 2022 © Allow specification reprint Like 0 喵ฅฅ
One comment
如果in超过3次,且是连续的
可以考虑用between
比如上面的例子 select from test3 where id2 between 1 and 3; 代替
୧(๑•̀⌄•́๑)૭