作者:手机用户2502921281_649 | 来源:互联网 | 2020-09-28 22:43
减少与数据库的交互次数
- INSERT … ON DUPLICATE KEY UPDATE
- REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
- UPDATE … WHERE ID IN(10,20,50,…)
合理的使用分页
限制分页展示的页数只能点击上一页、下一页采用延迟关联
如何正确的使用分页?
假如有类似下面分页语句:SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里对LIMIT OFFSET的处理方式是取出OFFSET+LIMIT的所有数据,然后去掉OFFSET,返回底部的LIMIT。所以,在OFFSET数值较大时,MySQL的查询性能会非常低。可以使用id > n 的方式进行解决:
使用id > n 的方式有局限性,对于id不连续的问题,可以通过翻页的时候同时传入最后一个id方式来解决。
http://example.com/page.php?last=100
select * from table where id<100 order by id desc limit 10
//上一页
http://example.com/page.php?first=110
select * from table where id>110 order by id desc limit 10
这种方式比较大的缺点是,如果在浏览中有插入/删除操作,翻页不会更新,而总页数可能仍然是根据新的count(*) 来计算,最终可能会产生某些记录访问不到。为了修补这个问题,可以继续引入当前页码以及在上次翻页以后是否有插入/删除等影响总记录数的操作并进行缓存
select * from table where id >= (select id from table order by id limit #offset#, 1)
- 拒绝大SQL,拆分成小SQL
- 充分利用QUERY CACHE
- 充分利用多核CPU
- 使用in代替or,in的值不超过1000个
- 禁止使用order by rand()
- 使用EXPLAIN诊断,避免生成临时表
EXPLAIN语句(在MySQL客户端中执行)可以获得MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN,可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。详见官方文档。
用union all而不是union
union all与 union有什么区别?
union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。
union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。如:
select * from test_union1
union select * from test_union2
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
而union all只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
从效率上说,union all要比union快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用union all,如下:
select * from test_union1 union all select * from test_union2
- 程序应有捕获SQL异常的处理机制
- 禁止单条SQL语句同时更新多个表
- 不使用select * ,SELECT语句只获取需要的字段
- 消耗CPU和IO、消耗网络带宽
- 无法使用覆盖索引
- 减少表结构变更带来的影响
- 因为大,select/join 可能生成临时表
- UPDATE、DELETE语句不使用LIMIT
- INSERT语句必须显式的指明字段名称,不使用INSERT INTO table()
- INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不超过500
- 统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1) 备注:仅针对Myisam
- 数据更新建议使用二级索引先查询出主键,再根据主键进行数据更新
- 禁止使用跨库查询
- 禁止使用子查询,建议将子查询转换成关联查询
- 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度;
分表规范
单表一到两年内数据量超过500w或数据容量超过10G考虑分表,需提前考虑历史数据迁移或应用自行删除历史数据,采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略
- 用HASH进行散表,表名后缀使用十进制数,下标从0开始
- 按日期时间分表需符合YYYY[MM][dd][HH]格式
- 采用合适的分库分表策略。例如千库十表、十库百表等
- 禁止使用分区表,分区表对分区键有严格要,分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
- 拆分大字段和访问频率低的字段,分离冷热数据
行为规范
- 批量导入、导出数据必须提前通知DBA协助观察
- 禁止在线上从库执行后台管理和统计类查询
- 禁止有super权限的应用程序账号存在
- 产品出现非数据库导致的故障时及时通知DBA协助排查
- 推广活动或上线新功能必须提前通知DBA进行流量评估
- 数据库数据丢失,及时联系DBA进行恢复
- 对单表的多次alter操作必须合并为一次操作
- 不在MySQL数据库中存放业务逻辑
- 重大项目的数据库方案选型和设计必须提前通知DBA参与
- 对特别重要的库表,提前与DBA沟通确定维护和备份优先级
- 不在业务高峰期批量更新、查询数据库其他规范
- 提交线上建表改表需求,必须详细注明所有相关SQL语句
其他规范
日志类数据不建议存储在MySQL上,优先考虑Hbase或OceanBase,如需要存储请找DBA评估使用压缩表存储。
相关图文教程:mysql数据库图文教程
以上就是史上最全MySQL使用规范分享的详细内容,更多请关注 第一PHP社区 其它相关文章!