MySql数据库开发规范及优化,面试必备

发布时间:2018-12-13 20:39:14编辑:丝画阁阅读(317)

数据库命名规范

  1. 所有的数据库对象名称(包括库名、表名、列名等等)必须以小写字母命名,每个单词之间用下划线分割
  2. 所有的数据库对象名称禁止使用MySQL保留关键字
  3. 数据库对象的命名要能做到见名知意,并且不要超过32个字符
  4. 数据库中用到的临时表以tmp为前缀并以日期为后缀
  5. 数据库中用到的备份表以bak为前缀并以日期为后缀
  6. 在不同的库或表中,要保证所有存储相同数据的列名和列类型必须一致

数据库基本设计规范

  1. 所有表如果没有特殊需求,都要使用Innodb存储引擎。Innodb存储引擎支持事务、行级锁、更好的恢复性、高并发下性能更好。
  2. 数据库和表的字符集统一使用UTF8字符集,避免由于字符集的转换产生乱码。
  3. 所有的表和字段都需要添加注释。使用comment从句添加表和列的备注,从一开始就把数据字典维护好
  4. 控制单表数据量的大小,建议控制在500万行以内。
  5. 可以采用历史数据归档(常见于日志表)和分库分表的方式控制单表数据的大小。
  6. 谨慎使用MySQL分区表,避免跨分区查询,否则效率很低。
  7. 分区表在逻辑上表现为一个表,但是在物理上将数据存储在多个文件。最好能将分区表的不同分区文件存储在不同的磁盘阵列上。
  8. 表中的列不要太多,尽量做到冷热数据分离,减小表的宽度。
  9. 减少表的宽度,可以让一页内存中容纳更多的行,进而减少磁盘IO,更有效的利用缓存。
  10. 经常一起使用的列尽量放到一个表中,避免过多的关联操作。
  11. 禁止在表中建立预留字段。
  12. 修改列的类型会所锁表。修改一个字段类型的成本要高于增加一个字段。
  13. 禁止在数据库中存储图片、文件等

数据库字段设计规范

  1. 优先为表中的每一列选择符合存储需要的最小的数据类型
  2. 列的字段类型越大,建立索引占据的空间就越大,导致一个页中的索引越少,造成IO次数增加,影响性能。
  3. 每个字段尽可能具有NOT NULL属性
  4. 避免使用Blob、Text及ENUM类型
  5. 使用datetime、timestamp存储时间类型,禁止使用字符串替代
  6. 使用数字类型存储IP地址,用INET_ATON、INET_NTOA可以在IP地址和数字类型间转换。
  7. VARCHAR类型的长度应该尽可能短。
  8. VARCHAR类型虽然在硬盘上是动态长度的,但是在内存中占用的空间是固定的最大长度。

数据库索引设计规范

  1. 限制每张表上的索引数量,建议单张表索引不超过5个
  2. 索引并不是越多越好。索引可以提高查询的效率,但会降低写数据的效率。有时不好的索引还会降低查询的效率。
  3. 禁止给表中的每一列都建立单独的索引。设计良好的联合索引比每一列上的单独索引效率要高出很多。
  4. 每个Innodb表都必须有一个主键,且不使用更新频繁的列作为主键,不使用多列主键。不使用UUID、MD5、字符串列作为主键。最好选择值的顺序是连续增长的列作为主键,所以建议选择使用自增ID列作为主键
  5. 建议在下面的列上建立索引:
  6. 在SELECT,UPDATE,DELETE语句的WHERE从句上的列。在ORDER BY,GROUP BY,DISTINCT上的列。多表JOIN的关联列。
  7. 区分度最高的列放在联合索引的最左侧。使用频繁的列放在联合索引的最左侧。
  8. 避免冗余的索引,如:primary key(id),index(id),unique index(id)
  9. 避免重复的索引,如:index(a,b,c),index(a,b),index(a)
  10. 重复的和冗余的索引会降低查询效率,因为MySQL查询优化器会不知道该使用哪个索引。
  11. 不要依靠外键保证参照完整性,在业务端实现参照完整性的要求。建立外键约束后的表在插入数据时要进行数据参照完整性检查,这会导致消耗数据库性能。虽然不使用MySql自带的外键,但一定在表与表之间的关联键上建立索引。

数据库SQL开发规范

  1. 在程序中,建议使用预编译语句进行数据库操作。预编译只编译一次,多次使用,比SQL效率高。
  2. 避免数据类型的隐式转换。隐式转换会导致索引失效。
  3. 避免使用双%号或前置%号的查询条件,这样无法利用到索引。
  4. 禁止在查询中使用select *
  5. 避免使用子查询,子查询会产生临时表,临时表没有任何索引,数据量大时严重影响效率。建议把子查询转化成关联查询。
  6. 避免使用JOIN关联太多的表。
  7. 尽量减少同数据库的交互次数,数据库更适合处理批量操作。
  8. 使用IN代替OR
  9. 禁止在where从句中对列进行函数转换和计算,会导致索引无效。
  10. 在不需要去重的情况下,要使用UNION ALL代替UNION。

关键字