在互联网公司中,MySQL是使用最多的数据库,那么在并发量大、数据量大的互联网业务中,如果高效的使用MySQL才能保证服务的稳定呢?根据本人多年运维管理经验的总结,梳理了一些核心的开发规范,希望能给大家带来一些帮助。
一、基础规范
二、库表设计
问题:使用VARCHAR(5) 和VARCHAR(200) 存储’hello’的磁盘空间开销是一样的,使用更短的列有什么优势吗?
更大的定义列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或操作时会特别糟糕
三、索引设计
基本规则:索引不是越多越好,能不添加的索引尽量不要添加,过多的索引会严重降低数据插入和更新的效率,并带来更多的读写冲突和死锁!
示例:假设在表tab中id建立了索引
- Select col_A,col_B from tab where id + 1 > 10001 不会使用索引
- Select col_A,col_B from tab where id > 10001 – 1 会使用索引
四、SQL优化
-
线上尽量少使用大SQL,可能一条大SQL就把整个数据库堵死,将复杂SQL拆分为多条简单SQL,化繁为简
-
一条SQL只能在一个CPU上运算,如果SQL比较复杂执行效率会非常低【8.0之后开始支持单SQL多CPU核执行,但是效果有限】
- 简单SQL缓存命中率更高
- 减少锁表时间
-
充分利用多核CPU,提高并发效率
-
减少MySQL端的数学运算和逻辑判断,避免SQL语句出现md5()、order by rand()等
- 尽量少用SELECT * ,只取需要的数据列, 避免无谓的IO、CPU和网络开销
- WHERE条件中,同一字段改写OR为IN(),IN包含的值不应过多,默认不超过200个,IN里禁止使用子查询
- 过滤表记录合并且不去重的情况,改写UNION为UNION ALL
- 减少使用拼接SQL,使用预编译语句,降低SQL注入概率
- WHERE条件中的非等值条件(IN、BETWEEN、
- WHERE条件比较,字段类型和传入值必须保证类型一致,避免隐式转换
示例:
字段: code varchar(50) NOT NULL COMENT ‘编码’ #code上建立了索引
SELECT id,name,addr from tab_name where code=10001; 不会使用索引
SELECT id,name,addr from tab_name where code=’10001′; 会使用索引
-
Limit分页优化
-
传统分页:
Select * from table limit 10000,10;
LIMIT原理:
Limit 10000,10 偏移量越大则越慢
-
推荐分页:
-
分页方式一:
Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条)
Select * from table WHERE id>=23434 limit 11;
- 分页方式二:
Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
- 分页方式三:
Select * from table INNER JOIN (SELECT id from table limit 10000,10) USING(id)
-
分页方式四:
-
程序取ID: Select id from table limit 10000,10;
- Select * from table WHERE ID in(123,456…);
最后说明:
上述规范是多年MySQL数据库使用的经验总结,希望能给大家带来一些启发和帮助!
如果你还想看更多优质原创文章,欢迎关注我的公众号「数据库架构师」,提升数据库技能。
Original: https://www.cnblogs.com/databasepub/p/16478997.html
Author: 数据库架构师
Title: 一文总结高并发大数据量下MySQL开发规范【军规】
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/715285/
转载文章受原作者版权保护。转载请注明原作者出处!