MySQLMySQL建表规范以及大问题

 

一、 表设计

  1. 库名、表名、字段名必须以小写字母,“_”分割。
  2. 库名、表名、字段名必须休越12个字符。
  3. 库名、表名、字段名见名知意,建议用名词而休是动词。
  4. 建议以InnoDB存储引擎。
  5. 积存精确浮点数必须采用DECIMAL替代FLOAT和DOUBLE。
  6. 提议使用UNSIGNED存储非负数值。
  7. 建议利用INT UNSIGNED存储IPV4。
  8. 整形定义着不加加长度,比如使INT,而无是INT(4)。
  9. 下短数据类型,比如取值范围为0-80时时,使用TINYINT UNSIGNED。
  10. 勿建议利用ENUM类型,使用TINYINT来顶替。
  11. 尽可能不利用TEXT、BLOB类型。
  12. VARCHAR(N),N表示的凡字符数不是配节数,比如VARCHAR(255),可以无限酷但存储255独字,需要根据实际的升幅来摘取N。
  13. VARCHAR(N),N尽可能小,因为MySQL一个表中所有的VARCHAR字段最老尺寸是65535单字节,进行排序和创办临时表一近乎的内存操作时,会使用N的尺寸申请内存。
  14. 发明字符集选择UTF8。
  15. 应用VARBINARY存储变长字符串。
  16. 囤年利用YEAR类型。
  17. 囤日期使用DATE类型。
  18. 存储时(精确到秒)建议采取TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8只字节。
  19. 提议字段定义为NOT NULL。
  20. 拿过那个字段拆分到外表中。
  21. 禁在数据库被行使VARBINARY、BLOB存储图片、文件等。
  22. 说明结构改变需要通知DBA审核。

二、 索引

  1. 莫唯一索引必须按“idx_字段名称_字段名称[_字段名]”进行命名。
  2. 唯索引必须依照“uniq_字段名称_字段名称[_字段名]”进行命名。
  3. 目名称必须用小写。
  4. 目中之配段往往建议不超过5单。
  5. 单张表的目数量控制以5独里头。
  6. 唯键由3单以下字段组成,并且字段都是整形时,使用唯一键作为主键。
  7. 无唯一键或者唯一键不合乎5受的准绳时,使用自增(或者经发号器获取)id作为主键。
  8. 唯一键不与主键重复。
  9. 觅引字段的逐一需要考虑配段值去还以后的个数,个数多之在面前。
  10. ORDER BY,GROUP BY,DISTINCT的字段需要补加于目的末端。
  11. 使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using
    File Sort,UsingTemporary。
  12. UPDATE、DELETE语句需要基于WHERE条件添加索引。
  13. 非建议使用%前缀模糊查询,例如LIKE “%weibo”。
  14. 本着长过长之VARCHAR字段建立目录时,添加crc32或者MD5
    Hash字段,对Hash字段建立目录。
  15. 合理创建同索引(避免冗余),(a,b,c) 相当给 (a) 、(a,b) 、(a,b,c)。
  16. 理所当然运用覆盖索引。
  17. SQL变更需要肯定索引是否用转移并通知DBA。

三、 SQL语句

  1. 应用prepared statement,可以供性并且避免SQL注入。
  2. SQL语句被IN包含的价不应允了多。
  3. UPDATE、DELETE语句不使LIMIT。
  4. WHERE条件中务必动合适的色,避免MySQL进行隐式类型转化。
  5. SELECT语句只取需要的字段。
  6. SELECT、INSERT语句必须显式的指明字段名称,不利用SELECT
    *,不使用INSERTINTO table()。
  7. 使 用SELECT column_name1, column_name2 FROM table
    WHERE[condition]而不是SELECT column_name1 FROM table
    WHERE[condition]和SELECT column_name2 FROM table WHERE
    [condition]。
  8. WHERE条件中的非等值条件(IN、BETWEEN、<、<=、>、>=)会导致后面的尺度使无了目录。
  9. 避免在SQL语句进行数学运算或者函数运算,容易用事情逻辑和DB耦合在一起。
  10. INSERT语句以batch提交(INSERT INTO
    tableVALUES(),(),()……),values的个数不应允了多。
  11. 避下存储过程、触发器、函数等,容易用工作逻辑与DB耦合在一起,并且MySQL的存储过程、触发器、函数中设有一定之bug。
  12. 避免用JOIN。
  13. 采用合理之SQL语句减少及数据库的并行次数。
  14. 不采用ORDER BY RAND(),使用任何艺术替换。
  15. 提议使用合理之分页方式为加强分页的效率。
  16. 统计表中记录数时行使COUNT(*),而不是COUNT(primary_key)和COUNT(1)。
  17. 禁绝以起仓库上执行后台管理和统计类型功能的QUERY。

四、 散表

  1. 各级张表数据量建议控制在5000w以下。
  2. 可组合使用hash、range、lookup table进行散表。
  3. 散表如果采用md5(或者类似的hash算法)进行散表,表名后缀使用16进制,比如user_ff。
  4. 推荐以CRC32求余(或者类似之算术算法)进行散表,表名后缀使用数字,数字要从0开始连等极富,比如排100张表,后缀从00-99。
  5. 采取时散表,表名后缀必须使用一定格式,比如按日散表user_20110209、按月度散表user_201102。

五、 其他

  1. 批量导入、导出数据要DBA进行审批,并当履过程被观察服务。
  2. 批量创新数据,如update,delete
    操作,需要DBA进行核对,并当履行过程中观察服务。
  3. 产品出现非数据库平台运维导致的题目同故障时,如前端被缉拿站,请即通报DBA,便于维护服务稳定性。
  4. 业务部门程序出现bug等影响数据库服务之题目,请即通知DBA,便于维护服务稳定性。
  5. 业务部门推广活动,请提前通知DBA进行服务与看评估。
  6. 假定起业务部门人为误操作导致数据丢失,需要恢复数据,请以第一时间通知DBA,并提供规范时间,误操作语句等重点线索。

 


FAQ

1-1.库名、表名、字段名必须运用小写字母,“_”分割。

a)MySQL来配备参数lower_case_table_names,不可动态更改,linux系统默认为0,即库表名以实际情形存储,大小写敏感。如果是1,以多少写存储,大小写不灵敏。如果是2,以实际情形存储,但为小写于。

b) 如果大小写混合用,可能在abc,Abc,ABC等大多独说明共存,容易招乱。

c)
字段名显示区分轻重缓急写,但骨子里利用无区分,即未得以起两单名字同样只是大小写不雷同的字段。

d) 为了统一标准, 库名、表名、字段名以小写字母。

 

1-2.库名、表名、字段名必须休越12只字符。

库名、表名、字段名支持太多64单字符,但为统一标准、易于辨识和减少污染输量,必须不越12字符。

 

1-3.库名、表名、字段名见名知意,建议采用名词而不是动词。

a) 用户评价可用表名usercomment或者comment。

b) 库表是一致种客观存在的东西,一栽对象,所以建议利用名词。

 

1-4.提议采用InnoDB存储引擎。

a)
5.5以后的默认引擘,支持工作,行级锁,更好之恢复性,高并发下性能更好,对大多对,大内存,ssd等硬件支撑再次好。

b) 具体于可见附件的合法白皮书。

 

1-5.囤精确浮点数必须采用DECIMAL替代FLOAT和DOUBLE。

a) mysql中之数值类(不包括整型):
    IEEE754浮点数:float  (单精度) , double  或real  (双精度)
    定点数: decimal或 numeric
   单精度浮点数的卓有成效数字二进制是24各项,按十进制来说,是8各;双精度浮点数的管用数字二进制是53个,按十进制来说,是16

   一个实数的实惠数字越8各类,用单精度浮点数来表示的话,就见面发误差!同样,如果一个实数的行数字超过16员,用对精度浮点数来代表,也会见产生误差
b)IEEE754标准的微处理器浮点数,在里头是因此二进制表示的,但在用一个十进制数转换为第二上制浮点数时,也会招误差,原因是勿是具的数都能更换成稀长度的亚上前制数。
   即一个二进制可以准确转换成十进制,但一个带来小数的十进制不肯定能够规范地用二进制来代表。

实例:
drop table if exists t;

create table t(value float(10,2));

insert into t values(131072.67),(131072.68);

select  value  from t;

+———–+

|value    |

+———–+

| 131072.67 |

| 131072.69 |

+———–+

 

1-6.建议下UNSIGNED存储非负数值。

一律的字节数,存储的数值范围重新不行。如tinyint 有记号为
-128-127,无符号为0-255

 

1-7. 怎么行使INT UNSIGNED存储ip?

使用INTUNSIGNED而不是char(15)来存储ipv4地址,通过MySQL函数inet_ntoa和inet_aton来展开转账。Ipv6地址时尚无转化函数,需要利用DECIMAL或者个别只bigINT来囤积。例如:

SELECT INET_ATON(‘209.207.224.40’);

3520061480

SELECT INET_NTOA(3520061480);

209.207.224.40

 

1-8. INT[M],M值代表什么意义?

留意数值类括号后面的数字只是表示宽度而与存储范围没有关联,比如INT(3)默认显示3各,空格补齐,超出时正常显示,Python、java客户端等无有所这个职能。

 

1-10.未建议用ENUM、SET类型,使用TINYINT来代替。

a)ENUM,有三单问题:添加新的值如果召开DDL,默认值问题(将一个地下值插入ENUM(也就是说,允许的值列之外的字符串),将插入空字符串因作为特别错误值),索引值问题(插入数字其实是插索引对应之值)

实例:

drop table if exists t;

create table t(sex enum(‘0′,’1’));

insert into t values(1);

insert into t values(‘3’);

select * from t;

+——+

| sex  |

+——+

| 0    |

|     |

+——+

2 rows in set (0.00 sec)

 

1-11.尽可能不下TEXT、BLOB类型。

a) 索引排序问题,只能采用max_sort_length的长短要手工指定ORDER BY
SUBSTRING(column,length)的长度来排序

b) Memory引擘不支持text,blog类型,会当磁盘上颇成临时表

c) 可能浪费更多的空间

d) 可能无法以adaptive hash index

e) 导致使用where没有索引的说话变慢

 

1-13. VARCHAR中会产生额外存储吗?

VARCHAR(M),如果M<256时会动用一个字节来存储长度,如果M>=256尽管运用有限单字节来囤长度。

 

1-14.表字符集选择UTF8。

a) 使用utf8字符集,如果是汉字,占3单字节,但ASCII码字符还是1只字节。
b) 统一,不见面起换产生乱码风险
c)
其他地面的用户(美国、印度、台湾)无需安装简体中文支持,就可知健康看你的仿,并且不会见产出乱码
d)ISO-8859-1编码(latin1)使用了单字节内之具备空中,在支持ISO-8859-1之体系面临传和存储其他任何编码的字节流都不见面吃丢掉。即把其余任何编码的字节流当作ISO-8859-1编码看待都尚未问题,保存之是原先封不动的字节流。

 

1-15.采取VARBINARY存储变长字符串。

第二上制字节流,不存在编码问题

 

1-18. 干什么建议以TIMESTAMP来储存时使未是DATETIME?

DATETIME和TIMESTAMP都是可靠到秒,优先挑选TIMESTAMP,因为TIMESTAMPMySQL只来4只字节,而DATETIME8只字节。同时TIMESTAMP具有活动赋值以及自动更新的风味。

怎利用TIMESTAMP的活动赋值属性?

a)  将手上时刻作为ts的默认值:ts TIMESTAMP DEFAULTCURRENT_TIMESTAMP。

b)  当行更新时,更新ts的值:ts TIMESTAMP DEFAULT 0 ONUPDATE
CURRENT_TIMESTAMP。

c)  可以用1暨2结合起来:ts TIMESTAMP DEFAULTCURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP。

 

1-19.提议字段定义也NOT NULL。

a)如果null字段被索引,需要格外的1字节

b)使索引,索引统计,值的比较变得重复杂

c)可用0,”代替

d)如果是寻觅引字段,一定要是定义为not null

 

1-21.禁止在数据库中行使VARBINARY、BLOB存储图片、文件等。

应用分布式文件系统更速

 

2. 怎么MySQL的性能依赖让索引?

MySQL的询问速度依赖良好的目录设计,因此索引对于大性能至关重要。合理之索引会加快查询速度(包括UPDATE和DELETE的速度,MySQL会用含有该行的page加载到内存中,然后开展UPDATE或者DELETE操作),不客观之索引会降低速度。

MySQL索引查找类似于新华字典的拼音和部首查找,当拼音和部首索引不设有时时,只能通过平等页一页的翻页来探寻。当MySQL查询不可知用索引时,MySQL会进展全表扫描,会吃大量的IO。

 

2-5. 胡同样布置表中不可知在了多之目?

InnoDB的secondaryindex使用b+tree来储存,因此在UPDATE、DELETE、INSERT的时光要对b+tree进行调整,过多的索引会减慢更新的快。

 

2-11. EXPLAIN语句

EXPLAIN语句(在MySQL客户端挨实践)可以收获MySQL如何履行SELECT语句之音讯。通过对SELECT语句执行EXPLAIN,可以知晓MySQL执行该SELECT语句时是否采用了目录、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。详见合法文档。

 

2-13.休建议使用%前缀模糊查询,例如LIKE “%weibo”。

会晤造成全表扫描

2-14. 如何对长超过50之VARCHAR字段建立目录?

脚的表明增加一排列url_crc32,然后对url_crc32建立目录,减少索引字段的长度,提高效率。

  • CREATE TABLE url(

       ……

       url VARCHAR(255) NOT NULL DEFAULT 0,
       url_crc32INT UNSIGNED NOT NULL DEFAULT 0,

       ……

       index idx_url(url_crc32)

    )

 

2-16. 什么是覆盖索引?

InnoDB
存储引擎中,secondaryindex(非主键索引)中没直接存储行地址,存储主键值。如果用户用查询secondaryindex中所不带有的数据列时,需要事先经secondaryindex查找到主键值,然后再通过主键查询及其他数据列,因此待查询两次。

蒙索引的定义就是询问好由此以一个索引中完成,覆盖索引效率会比强,主键查询是自然之盖索引。

理所当然之创建索引以及合理的下查询语句,当以及覆盖索引时可以拿走属性提升。

比如SELECT email,uid FROM user_email
WHEREuid=xx,如果uid不是主键,适当时候可以以索引添加为index(uid,email),以博得属性提升。

 

3-3.UPDATE、DELETE语句不应用LIMIT。

a) 可能导致基本数据未雷同

b) 会记录到不当日志,导致日志占用大量空中

3-4. 为什么要避免MySQL进行隐式类型转化?

因MySQL进行隐式类型转化后,可能会见以搜引字段类型转化成=号右侧边值的类别,导致使用无顶目录,原因及免以找引字段被运用函数是相仿的。

 

3-6. 为何非建议下SELECT *?

增加多不必要的淘(cpu、io、内存、网络带来富);增加了用覆盖索引的可能性;当表结构来变更时,前段也得更新。

 

3-13. 什么压缩与数据库的竞相次数?

应用下的告知句来减少和db的互相次数:

INSERT … ON DUPLICATE KEY UPDATE

REPLACE

INSERT IGNORE

INSERT INTO values(),()如何做使用多独纬度进行散表散库?

诸如微博message,先以crc32(message_id)将message散到16独仓库中,然后对每个库中之发明,一天特别成一摆放新表。

 

3-14. 为何不能够用ORDER BY rand()?

因为ORDER
BYrand()会以数据由磁盘中读取,进行排序,会吃大量底IO和CPU,可以以程序中得到一个rand值,然后经过当由数据库被赢得相应之值。

 

3-15. MySQL中什么进行分页?

如若有近似下面分页语句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;

这种分页方式会招大气的io,因为MySQL使用的凡提前读取策略。

推介分页方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BYTIME DESC LIMIT
10.

SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BYTIME LIMIT
10000,10) as t USING(id)

 

 3-17.为什么避免下复杂的SQL?

驳回下复杂的SQL,将十分的SQL拆分成多漫长简单SQL分步实施。原因:简单的SQL容易用到MySQL的querycache;减少锁表时间专门是MyISAM;可以用多核cpu。

 

 

  1. InnoDB存储引擎为什么避免使用COUNT(*)?

InnoDB表避免采用COUNT(*)操作,计数统计实时要求比强得使用memcache或者redis,非实时统计可以应用单独统计表明,定时更新。

网站地图xml地图