高效MySQL开发规范
基础规范
- 数据库字符集默认使用utf8,如果存储emoji表情等四字节使用utf8mb4字符集
- 禁止在线上生产环境做数据库压力测试
- 禁止从测试、开发环境、本机直连线上生产数据库
- 禁止在数据库中存储明文密码
- 禁止在数据库中存储图片、文件等大数据
- 禁止将业务日志实时保存到数据库,建议保存到日志文件,对于统计后的结果再存放到mysql中
- 禁止线上核心业务使用mysql存储过程、视图、触发器、Event、InnoDB外键约束等,这些容易将业务逻辑和db耦合在一起,而且在MySQL的这些特性中存在严重BUG
- 业务部门的推广活动,请提前通知dba进行服务和访问评估。
库表设计
- 库名、表名、字段名必须使小写字母,并采用下划线分割;对相关功能的表应当使用相同前缀,如job_xxx,前缀通常为库名或依赖主实体对象:
数据库名称约定:db_xxx dbgj_xxx dbchr_xxx - 数据库表的所有引擎默认都是InnoDB,新业务不支持MyISAM引擎
- 所有的表及字段都必须有备注,详细说明表及字段的含义
- 涉及货币金额或其他精度敏感的数据必须使用定点数DECIMAL替代FLOAT和DOUBLE
- 库名、表名、字段名禁止使用MySQL保留字,如date、like、desc、return等
- 控制表字段数,单表不超过50个纯INT/20个VARCHAR(10)字段等同存储体积的字段数,上限控制在20~50
- 字段长度只分配真正需要的空间
问题:使用VARCHAR(5) 和VARCHAR(200) 存储’hello’的磁盘空间开销是一样的,使用更短的列有什么优势吗?
更大的定义列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排序或操作时会特别糟糕
索引设计基本规则:索引不是越多越好,能不添加的索引尽量不要添加,过多的索引会严重降低数据插入和更新的效率,并带来更多的读写冲突和死锁!
- 索引名称必须使用小写,普通索引按照“idx_字段名_字段名[_字段名]”进行命名,唯一索引按照“uniq_字段名_字段名[_字段名]”进行命名”
- 表必须有主键,推荐使用独立于业务的AUTO_INCREMENT列或全局ID生成器做主键,禁止使用多字段做联合主键
- 不使用UUID/MD5/HASH等函数生成的无规则值做主键,效率极差
- 索引数量控制
- 单张表中索引数量不超过5个
- 单个索引中的字段数不超过5个
- 对字符串使用前缀索引,前缀索引长度不超过10个字符
- 索引字段的顺序需要考虑每个字段去重之后的数量,区分度最大的【个数最多的】放在前面。
- 合理创建联合索引(避免冗余),符合最左前缀原则:(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
- 可能需要添加索引的字段:
- ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面
- UPDATE、DELETE语句需要根据WHERE条件添加索引
- 对于JOIN操作,需要在JOIN字段上建立索引
- 线上慎用FORCE INDEX,使用前需要和DBA沟通,并得到DBA的测试允许
- 线上OLTP系统中禁止使用外键,高并发时极易引起死锁等问题
- 索引使用禁忌
- 不使用%前导的查询,如like “%ab”
- 不使用负向查询,如not in/not like/<>
- 不在低区分度的列上建立索引,例如“性别”
- 不在索引列进行数学运算和函数运算
示例:假设在表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比较复杂执行效率会非常低
- 简单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…);
- 分页方式一:
- 传统分页:
Finasteride Order https://bbuycialisss.com/ – buy cialis canada Cialis Boutique Buy Cialis Order Tab Acticin Permetrina Overseas
Zithromax Dosage Children Dualspoups https://acialisd.com/# – Cialis Reertevews Get Cialis From Canada Bemolymn buy cialis online reddit unoche Efectos De Cialis Foros
Achat Kamagra Nantes Wicmymmele https://cialisse.com/ – Cialis gaideleplete Buy Zithromax In The Uk affextop buy cialis online forum adoreakder Zithromax Rx Dosage
Buy Levothyroxine Online Without Script Wicmymmele https://cialiser.com/ – cialis online prescription gaideleplete Can You Buy Generic Plavix affextop buy cialis pills adoreakder Tipos De Propecia
Zithromax Affect Birth Control Pills Wicmymmele buying cialis online reviews gaideleplete Sildenafil Tadalafil And Vardenafil
http://vskamagrav.com/ – kamagra uk next day delivery paypal 306
viagra
My coder is trying to persuade me to move to .net from PHP.
I have always disliked the idea because of the costs.
But he’s tryiong none the less. I’ve been using Movable-type on numerous websites
for about a year and am concerned about switching to
another platform. I have heard great things about
blogengine.net. Is there a way I can transfer all my wordpress content into it?
Any kind of help would be greatly appreciated!
Heya i am for the first time here. I came across
this board and I in finding It truly helpful & it helped me out a lot.
I hope to present one thing again and aid others like you
helped me.