本篇内容主要是按照整体设计字段设计索引设计以及SQL书写规范四个方面讲解了MySQL的开发规范,以及最后的MySQL关键字附录。其中规范的后两部分涉及到挺多的SQL调优相关内容,将打算再下一篇结合实例详细说明。

一、整体设计

  1. 对于范式设计需要考虑应用类型以及数据分布情况,来灵活选择范式化还是反范式化

    • 范式化
      优点:没有或者很少的冗余数据,更新速度快(通常表也比较小)
      缺点:数据量大时,读一次需要涉及到多表关联,代价十分昂贵
    • 反范式化
      优点:数据通常在一张表里,避免多表关联查询。(数据比内存大的多的情况下也会比关联查询效率更高,因为全表扫描基本上是顺序IO)
      缺点:数据冗余
  2. 不要让数据库做它不擅长的事情,如:运算、MD5等。
  3. 默认使用utf8字符,如果涉及到emoji表情就选用utf8mb4。
  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。
      insert时还需要提供一张临时表uid_temp来提供自增的id:
      insert into uid_temp values(null);
  7. 注意冷数据拆分,保障热表数据精简,保证单表数据尽量精简。(这就要考量到范式化和反范式化的问题了,按需决定)
  8. 专用数据库,分离线上应用,切记多个应用部署同一个数据库
  9. 单个数据库表数量不超过500,单表数据量不超过1000w
    注:单表数据不超过1000W只是一个基准数据,实际情况需要考虑数据长度、服务器配置、数据库配置等等,因此1000W只是一个参考数据,默认情况下我们需要遵循,如果有特殊情况再另外提出说明。
  10. MySQL对象(库名、表名、字段名、索引名、函数名等)全部使用小写字母,并用下划线分割。做到见名知意,注意各种如idx_xxx等命名规范,切记千万不要用拼音或不正当的缩写,以及保留字
  11. 数据库尽量拒绝大SQL、大事务、大批量。
    注:大事务、大批量其实可以用NoSQL去处理。而拒绝大SQL就相当于不推荐存储过程或者存储函数了,从MySQL的角度来说也确实如此,它对存储过程/函数的支持其实很不友好,在之前的Mybatis相关部分就有说明过。至于关于思考,撸一段 SQL ? 还是写一段代码?可以看这里。
  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的话,则基于该字段的所有复合索引都会失效。
      (2)浪费存储空间,因为InnoDB需要额外的一个字节存储。
      (3)表内默认值NULL过多的话会影响优化器选择执行计划。
    • 处理方案:设置业务上的默认值。
  7. IPV4地址如果做查询功能使用需要使用int,这种方式只能存储IPv4,存储不了IPv6,如果仅仅只是做展示功能可以使用char(15)
  8. 内容明确,不做变更的类型代码可以用tinyint进行转义,不建议用枚举类型。
    注:
    (1)这条规则主要是针对PHP,因为PHP是弱类型。如:insert into ..... set a= 1,你没法知道你是想a= '1'还是a= 1(前者是插入值1,后者是插入enum的第一个值。)尤其php弱类型的,如果int的,很少有人在sql里加双引号。
    (2)对于Java来说,类中的Enum在数据库中用varchar与之对应即可。
    (3)当使用enum或者set类型时需要注意避免与char/varchar关联查询(这点还不是很清楚)
  9. 字段长度应适当冗余。

备注

备注1:整数类型取值范围与存储长度

整数类型SignedUnsigned长度
Tinyint-128~1270~2551byte
Samllint-32768~327670~655352bytes
Mediumint-8388608~83886070~167772153bytes
Int-2147483648~21474836470~42949672954bytes
Bigint-9223372036854775808~92233720368547758070~184467440737095516158bytes

备注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, TINYTEXTL+1个字节,其中L < 2的8次方255B+1B
BLOB, TEXTL+2个字节,其中L < 2的16次方64K+2B
MEDIUMBLOB, MEDIUMTEXTL+3个字节,其中L < 2的24次方16M+3B
LONGBLOB, LONGTEXTL+4个字节,其中L < 2的32次方4G+4B

注释:使用中需要注意max_sort_length以及max_allowed_packet的设置,以及不允许使用内存临时表。

备注4:datetime & timestamp

类型存储范围默认值长度时区
datetime1000-9999年8Byte不依赖
timestamp1970-2037年当期时间4Byte依赖

三、索引设计

  1. 普通索引按照idx_col命名,唯一索引按照unq_idx_col命名。
    例如:idx_nameidx_name_ageunq_idx_nameunq_idx_name_age
  2. 不使用全文索引(用了也没快多少),需要的时候可以考虑使用第三方索引程序,比如:sphinx、lucene等等。
  3. InnoDB表主键必须尽量简短,建议设置一个自增主键,杜绝联合主键。(自增前提是不需要做水平拆分)
    InnoDB中,当联合主键存在无序插入、频繁更新或主键过长都会影响表的读写效率。
  4. 索引的建立要适度。提升查询效率是没错,但也加大了写入的开销。
  5. 复合索引一般比普通索引更加合适。因为在绝大部分情况下普通的单一字段索引可以看成是复合索引的重复索引。
  6. 添加复合索引时,要考虑索引列顺序,正常情况下把选择性最高的字段放在最前面。
    :还有一点是组合索引的顺序要与查询的sql顺序对应,所以最好是sql和组合索引中都是按照选择性最高的字段最靠前的规则。
  7. 区分度很小的字段没必要建立索引(比如性别这样的字段)
  8. 对于较长的char/varchar字段应该建立前缀索引
  9. 生产业务的查询SQL必须使用索引,且要小心索引失效。
    注:生产业务的查询SQL必须严格按照规范使用索引,特别是joindistinctgroup byorder by等类型的SQL
  10. 查询SQL时使用运算和函数,会造成索引失效。
  11. 使用前模糊匹配,会造成索引失效
  12. 相同用途字段或者是需要关联的字段,需要保持一致的数据类型(长度最好也一致)。杜绝隐式转换,这会造成索引失效
    (同字段设计的第5点。)
  13. SQL存在范围查询时,需要把范围查询字段放在最后一个,如果范围较小可以用in(),尽量避免多个范围查询。
    :用in()代替范围查询是为了让优化器使用更多的索引列,但是in()里面过多的条件也会降低查询性能,因为in()条件组合是以指数形式递加的,过多的条件会浪费大量的CPU运算以及内存,特别在老版本的MySQL这种现象更严重。
  14. 强制读主库。
    有时候在主库中插入数据后,希望立即从主库读出来。在kingshard中由于读写分离的原因,select默认会发送到相应node的从库上。但是只需要在select语句中加入相应的注释项(/*master*/),就可以将select语句发送到主库。
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. 用括号确定andor的优先级,同时去掉不必要的括号。
  7. 尽量不使用存储过程、触发器、函数等。
    (起码在MySQL下,是如此。)
  8. 尽量避免临时表产生。存在一个order by子句跟不同group by子句的SQL会产生临时表,order bygroup by包含联接查询中第一张表以外的其他表的列也会创建临时表
  9. 大部分情况下,一个复杂的SQL可以拆分成几个简单的SQL。也建议这么做(跟整体设计第11点的讨论类似)。
    优点如下:①缓存效率更高;
    ②减少锁竞争;
    ③更容易做到高性能跟可扩展;
    ④查询性能可能提升;
    ⑤减少冗余记录查询
  10. 慎用FOR UPDATELOCK IN SHARE MODE,防止锁扩大。
  11. UPDATEDELETE语句不使用LIMIT ,不然容易造成主从不一致。

踩坑场景

  • 对于数据库的ddl操作,同一张表的所有操作应该放在同一个SQL完成,而不是拆分成多条SQL。
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来获取昨天的日期,那就直接悲剧了。
"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引擎下确实需要用到分页技术,可以考虑使用延迟关联来实现优化:
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代替,同一字段orin代替,例如:
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关键字

ADDALLALTER
ANALYZEANDAS
ASCASENSITIVEBEFORE
BETWEENBIGINTBINARY
BLOBBOTHBY
CALLCASCADECASE
CHANGECHARCHARACTER
CHECKCOLLATECOLUMN
CONDITIONCONNECTIONCONSTRAINT
CONTINUECONVERTCREATE
CROSSCURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMPCURRENT_USERCURSOR
DATABASEDATABASESDAY_HOUR
DAY_MICROSECONDDAY_MINUTEDAY_SECOND
DECDECIMALDECLARE
DEFAULTDELAYEDDELETE
DESCDESCRIBEDETERMINISTIC
DISTINCTDISTINCTROWDIV
DOUBLEDROPDUAL
EACHELSEELSEIF
ENCLOSEDESCAPEDEXISTS
EXITEXPLAINFALSE
FETCHFLOATFLOAT4
FLOAT8FORFORCE
FOREIGNFROMFULLTEXT
GOTOGRANTGROUP
HAVINGHIGH_PRIORITYHOUR_MICROSECOND
HOUR_MINUTEHOUR_SECONDIF
IGNOREININDEX
INFILEINNERINOUT
INSENSITIVEINSERTINT
INT1INT2INT3
INT4INT8INTEGER
INTERVALINTOIS
ITERATEJOINKEY
KEYSKILLLABEL
LEADINGLEAVELEFT
LIKELIMITLINEAR
LINESLOADLOCALTIME
LOCALTIMESTAMPLOCKLONG
LONGBLOBLONGTEXTLOOP
LOW_PRIORITYMATCHMEDIUMBLOB
MEDIUMINTMEDIUMTEXTMIDDLEINT
MINUTE_MICROSECONDMINUTE_SECONDMOD
MODIFIESNATURALNOT
NO_WRITE_TO_BINLOGNULLNUMERIC
ONOPTIMIZEOPTION
OPTIONALLYORORDER
OUTOUTEROUTFILE
PRECISIONPRIMARYPROCEDURE
PURGERAID0RANGE
READREADSREAL
REFERENCESREGEXPRELEASE
RENAMEREPEATREPLACE
REQUIRERESTRICTRETURN
REVOKERIGHTRLIKE
SCHEMASCHEMASSECOND_MICROSECOND
SELECTSENSITIVESEPARATOR
SETSHOWSMALLINT
SPATIALSPECIFICSQL
SQLEXCEPTIONSQLSTATESQLWARNING
SQL_BIG_RESULTSQL_CALC_FOUND_ROWSSQL_SMALL_RESULT
SSLSTARTINGSTRAIGHT_JOIN
TABLETERMINATEDTHEN
TINYBLOBTINYINTTINYTEXT
TOTRAILINGTRIGGER
TRUEUNDOUNION
UNIQUEUNLOCKUNSIGNED
UPDATEUSAGEUSE
USINGUTC_DATEUTC_TIME
UTC_TIMESTAMPVALUESVARBINARY
VARCHARVARCHARACTERVARYING
WHENWHEREWHILE
WITHWRITEX509
XORYEAR_MONTHZEROFILL
Last modification:December 6th, 2020 at 09:03 pm
喵ฅฅ