mysql-笔记-命名、索引规范

1 命名规范

所有数据库对象名称必须使用小写字母并用下划线分割

禁止使用mysql保留关键字 ---如果表名中包含关键字查询时,需要将其有单引号括起来

见名识意,并且最后不要超过32个字符

临时库表以tmp_为前缀并以日期为后缀,备份表以bak_为前缀并以日期为后缀

所有存储相同数据的列名和列类型必须一致--一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低

2 数据库基本设计规范

没有特殊要求下,所有表必须使用innodb存储引擎--支付事务、行级锁、更好的恢复性、高并发下性能更好

数据库和表的字符集统一使用utf8---统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效

所有表和字段都需要添加注释--使用comment从句添加表和列的备注进行数据字典的维护

尽量控制单表数据量的大小,建议控制在500万以内,过大会造成修改表结构、备份、恢复都会有很大的问题。可以用历史数据归档应用于日志数据,分库分表应用于业务数据等手段

谨慎使用mysql分区表--分区表在物理上表现为多个文件,在逻辑上表现为一个表,跨分区查询效率可能更低,建议采用物理分表的方式管理大数据

尽量做到冷热数据分离,减小表的宽度--mysql限制每个表最多存储4096列,并且每一行数据在大小不能超过65535字节 减少磁盘IO--保证热数据的内存缓存命中率,避免读入无用的冷数,经常一起使用的列放到一个表中避免更多的关联操作。

禁止在表中建立预留字段--无法确认存储的数据类型,对预留字段类型的修改会对表进行锁定

禁止在数据库中存储图片,文件等大的二进制数据---IO操作耗时,通常存储于文件服务器,数据库只存储文件地址信息

禁止在线上做数据库压力测试

禁止从开发环境、测试环境直接连接生成环境数据库

2 数据库字段设计规范

优先选择符合存储需要的最小的数据类型-- 字段大,建立索引空间大,IO次数多,索引性能差

  1 将字符串转换成数字类型存储如:IP地址-插入数据前先用inet_aton把ip地址转为整型,节省空间,显示数据时使用inet_ntoa把整型ip地址转为地址显示即可

  2 对于非负型的数据 如 自增id IP 要优先使用无符号整型来存储,无符号相对于有符号可以多出一倍的存储空间

    SIGNED INT -2147483648~2147483647
    UNSIGNED INT 0~4294967295
    VARCHAR(N)中的N代表的是字符数,而不是字节数
    使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存
避免使用text、blob数据类型,最常见的text类型可以存储64K的数据---可以分离到单独的扩展表中
mysql内存临时表不支持text/blob大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。mysql还要进行二次查询,会使sql性能变得很差,不需要text列的数据时不要对该列进行查询
text/blob类型只能使用前缀索引,并且text列上是不能有默认值的
避免使用enum类型--修改enum值需要使用alter语句-enum类型的order by 操作效率低,需要额外操作,禁止使用数值作为enum的枚举值
尽可能把所有列定义为not null--索引null列需要额外的空间来保存,所以要占用更多的空间;进行比较和计算时要对null值做特别的处理
使用timestamp 4个字节 或 datetime类型8个字节 存储时间
  TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
  TIMESTAMP 占用4字节和INT相同,但比INT可读性高
  超出TIMESTAMP取值范围的使用DATETIME类型存储

同财务相关的金额数据必须使用decimal类型

  非精准浮点:float,double

  精准浮点:decimal
  Decimal类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

4 索引设计规范

限制每张表上的索引数量,不超过5个,索引可以增加查询效率,同样也会降低插入和更新的效率,有些情况下会降低查询效率

因为mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,同样会降低查询性能

禁止给表中的每一列都建立单独的索引---使用联合索引查询

每个索引组织表innodb必须有个主键--数据的存储的逻辑顺序和索引的顺序是相同的,每个表都可以有多个索引,但是表的存储顺序只能有一种innodb是按照主键索引的顺序来组织表的。

不要使用更新频繁的列作为主键,不要使用uuid md5 hash 字符串列作为主键--无法保证数据的顺序增加

主键建议使用自增ID值

5 常见索引列建议

出现在select update delete 语句的where 从句中的列

包含在order by  group by   distinct中的字段

多表join的关联列

建立联合索引效果更好

6 索引列的顺序 -区分别最高的放在联合索引的最左侧 ,区分度=列中不同值的数量/列的总行数

尽量把字段长度小的列放在联合索引的左侧

7 避免建立冗余索引和重复索引

  重复索引示例:primary key(id)、index(id)、unique index(id)
  冗余索引示例:index(a,b,c)、index(a,b)、index(a)
8 优先考虑覆盖索引--就是包含了所有查询字段(where select order bjy group by )的索引
避免lnnodb表进行索引的二次查询
9 索引规范
尽量避免使用外键约束,但要在表与表之间的关联键上建立索引,外键建议在业务端实现参照完整性
外键会影响父表和子表的写操作从而降低性能
10 数据库开发规范
建议使用预编译语句进行数据库操作-减少编译所需要的时间,还可以解决动态sql所带来的sql注入问题 只传参数,比传递sql语句更高效,相同语句可以一次解析,多次使用,提高处理效率
避免数据类型的隐式转换 id=''
充分利用表上已经存在的索引-避免使用双%号的查询条件
一个sql只能利用到复合索引中的一列进行范围查询-如:有 a,b,c列的联合索引,在查询条件中有a列的范围查询,则在b,c列上的索引将不会被用到,在定义联合索引时,如果a列要用到范围查找的话,就要把a列放到联合索引的右侧
  使用left join或 not exists来优化not in操作  因为not in 也通常会使用索引失效。
数据库设计进,应要对以后扩展进行考虑
程序连接不同的数据库使用不同的账号,跨库查询
为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险
 
禁止使用select * 使用select 字段 查询
禁止使用不含字段一表的insert语句
避免使用子查询,可以把子查询优化为join操作 通用子查询在in子句中,且子查询中为简单sql进才可以转化为关联查询进行优化
子查询结果信无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引。
 
避免使用JOIN关联太多的表-关联缓存大小可以由join_buffer_size参数进行设置,最多允许关联61个表,建议不超过5个。
 
减少同数据库的交互次数-批量操作合交多个相同的操作到一起,可以提高处理效率
 
对应同一列进行or判断时,使用in代替or,in 的值不要超过500个,可以更有效的利用索引,or很少能利用到索引
 
禁使用order by rand()进行随机排序
 
where从句中禁止对列进行函数转换和计算:无法使用索引
 
在明显不会有重复值时使用union all 而不是union
union 会把两个结果集的所有数据放到临时表中后再进行去重操作,union all 不会再对结果集进行去重操作
 
拆分复杂的大sql为多个小sql:大sql逻辑上比较复杂,需要占用大量cpu进行计算;mysql一个sql只能使用一个cpu进行计算,拆分的可能通过并行执行来提高处理效率
 
11 数据库操作行为规范
超100万行的批量写操作,要分批多次进行操作;大批量写操作产生大量日志。特别是对于row格式。
大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞
对于大表使用pt-online-schema-change修改表结构:避免大表修改产生的主从延迟,避免在对表字段进行修改时进行锁表,pt-online-schema-change首先建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器。把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原表删除掉。
 
禁止为程序使用的账号赋予super权限-只能留给DBA处理问题账号使用。
对于程序连接数据库账号,遵循权限最小原则
上一篇:MySQL存储引擎MyISAM与InnoDB


下一篇:EXCEL 基本函数