在互联网大厂必须遵守的MySql开发军规

发表于:2019-10-28 10:03

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:esrever    来源:简书

分享:
  核心
  不在数据库做运算
  单表数据量:一年内单表纯INT不超过1000W,含CHAR不超500W。单库不超过300~400表
  表字段尽量少,上限控制在20~50个
  适当可以冗余(平衡范式和冗余)
  拒绝大sql,大事务,大批量
  
  字段
  数值类型的字节和运用范围
  如果可以,将字符串转化为数字存储。可以加快查询速度和节省空间,举例用INT代替CHAR(15)来存储IP
  优先使用SET和ENUM...(可能有问题!)
  避免使用NULL
  少用TEXT/BLOB,如果必须使用(超过varchar最大限制64k)则必须拆分到单独的表
  不在数据库存图片
  索引
  能不加的索引尽量不加,最好不超过字段数的20%(如:性别不加),结合核心SQL优先考虑覆盖索引
  字符字段必须建前缀索引。由于字符串很长,通常可以索引开始的几个字符,而不是全部值,以节约空间并得到好的性能。
  不在索引列进行数学运算和函数运算(会导致无法使用索引 => 全表扫描),如where id+1 = 100 和 id = 100 - 1,效率差很远
  自增列或全局ID做INNODB的主键
  尽量不用外键(由程序保证约束),高并发的时候容易死锁
  SQL
  SQL语句尽可能简单,因为一条SQL只能在一个CPU运算,在高并发的情况下,可能一条大SQL就把整个数据库堵死。而简单的SQL缓存命中率更高,减少锁表的时间(特别是MyISAM),用上多CPU
  保持事务、DB连接足够短,即开即用、用完就关。与事务无关操作放到事务外面,减少锁资源的占用;在不破坏一致性前提下,使用多个短事务代替长事务(如:发帖时的图片上传等待)
  尽可能少用存储过程,少用触发器,减用MySQL函数对结果进行处理(交由客户端程序负责)
  尽量少用select *,只取需要数据列,为使用覆盖索引提供可能性,减少临时表生成,更安全
  用in()代替or,因为or的效率是O(n),而in()的效率是O(Log n)。如:where a = 1 OR a = 100 与 where a IN (1, 100)
  merge index往往很弱智,所以用union代替对多字段的or查询。如:select * from t where a = 1 OR b = 2 与 select * from t where a = 1 UNION select * from t where b = 2
  尽量避免负向查找,如NOT、!=等
  尽量避免%前缀模糊查询,由于使用的是B+ Tree,前缀模糊使用不了索引,导致全表扫描(后缀模糊速度相对快很多)
  减少COUNT(*),使用COUNT(col),前者资源开销大,尽量少用。MyISAM不带WHERE COUNT()而INNODB带WHERE COUNT()。 计数的统计可以采用的方法:实时统计可以使用memcache,双向更新,凌晨跑基准;非实时统计尽量用单独统计表,定期重算
  LIMIT高效分页:传统的方法是select * from t limit 10000, 10,推荐的方法是select * from t where id > 23423 limit 10。LIMIT的偏移量越大则越慢。还有一些高效的方法有:先取id来LIMIT偏移,减少整体的数据偏移;取到需要的id,与原表JOIN;程序取ID,然后用IN来填写。select * from t where id >= (select id from t limit 10000, 1) limit 10 , select * from t INNER JOIN (select id from t limit 10000, 10) USING (id) , select id from t limit 10000, 10; select * from t where id in (123, 456...)
  若无需对结果进行去重,则用UNION ALL而非UNION(UNION有去重开销)
  分解JOIN联接来保证高并发。高并发DB不建议进行两个表以上的JOIN
  group by会默认自动升序排序,如果需要去掉排序,需要指定order by NULL
  比较原则:数字对数字、字符对字符。如果数值列与字符类型作比较,同时转换成双精度;如果字符列与数值类型作比较,字符列整列转数值,且不会使用索引查询
  load data导入数据比insert快约20倍(不需要刷新缓存)
  尽量不使用insert...select(延迟、同步出错)
  大批量更新凌晨操作,避开高峰
  SQL的一些命令:explain, show profile, mysqlsla, mysqldumpslow, show slow log, show processlist, show QUERY_RESPONSE_TIME(Percona)
  约定
  数据库在不同时期使用不同的:实时数据用real库,模拟环境用sim库,测试用qa库,开发用dev库
  禁止未经DBA确认的子查询(大部分情况优化较差,特别是WHERE中使用IN id的子查询,一般可以用JOIN改写)
  不要在程序上加锁数据库,因为外部锁对数据库不可控,高并发时是灾难,并且极难调试排查(可以采用事务来解决)
  统一字符集:UTF-8,校对规则:utf8_general_ci
  库和表的名称统一用小写(大小写敏感、且不同操作系统都有不同的限制);字段名大小写不敏感;索引名默认为idx_字段名;库名用缩写,尽量在2~7个字母;避免用保留字命名

     本文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号