1.数据库中的库名,表名和字段名的命名规则
采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;
命名简洁明确(长度不能超过30个字符);
例如:user, stat, log, 也可以wifi_user, wifi_stat, wifi_log给数据库加个前缀;
每个表中必须有自增主键,create_time(系统时间)
表与表之间的相关联字段名称要求尽可能的相同;
2.数据库表字段类型规范
用尽量少的存储空间来存数一个字段的数据;
例如:能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);
IP地址最好使用int类型;
固定长度的类型最好使用char,例如:邮编;
能使用tinyint就不要使用smallint,int;
最好给每个字段一个默认值,最好不能为null;
- 数据库字符集默认使用utf8,如果存储emoji表情等四字节使用utf8mb4字符集
- 禁止在线上生产环境做数据库压力测试
- 禁止从测试、开发环境、本机直连线上生产数据库
- 禁止在数据库中存储明文密码
- 禁止在数据库中存储图片、文件等大数据
- 禁止将业务日志实时保存到数据库,建议保存到日志文件,对于统计后的结果再存放到mysql中
- 禁止线上核心业务使用mysql存储过程、视图、触发器、Event、InnoDB外键约束等,这些容易将业务逻辑和db耦合在一起,而且在MySQL的这些特性中存在严重BUG
- 业务部门的推广活动,请提前通知dba进行服务和访问评估。
- 库名、表名、字段名必须使小写字母,并采用下划线分割;对相关功能的表应当使用相同前缀,如job_xxx,前缀通常为库名或依赖主实体对象:
数据库名称约定:dbgm_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只能在一个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分页优化
- 假设有一个千万量级的表,取1到10条数据;
select * from table limit 0,10; select * from table limit 1000,10;
这两条语句查询时间应该在毫秒级完成;
select * from table limit 3000000,10;
你可能没想到,这条语句执行之间在5s左右;
为什么相差这么大?
可能mysql并没有你想的那么智能,比如你要查询 300w开始后面10条数据;mysql会读取300w加10条这么多的数据,只不过 过滤后返回最后10条而已!!!
那么如果解决这个问题呢;这里总结三种常用方法;
第一种简单粗暴,就是不允许查看这么靠后的数据,比如百度就是这样的
最多翻到76页就不让你翻了,这种方式就是从业务上解决;
第二种方法,在查询下一页时把上一页的行id作为参数传递给客户端程序,然后sql就改成了
select * from table where id>3000000 limit 10;
这条语句执行也是在毫秒级完成的,id>300w其实就是让mysql直接跳到这里了,不用依次在扫描全面所有的行
如果你的table的主键id是自增的,并且中间没有删除和断点,那么还有一种方式,比如100页的10条数据
select * from table where id>100*10 limit 10;
最后第三种方法:延迟关联
我们在来分析一下这条语句为什么慢,慢在哪里。
select * from table limit 3000000,10;
玄机就处在这个 * 里面,这个表除了id主键肯定还有其他字段 比如 name age 之类的,因为select * 所以mysql在沿着id主键走的时候要回行拿数据,走一下拿一下数据;
如果把语句改成
select id from table limit 3000000,10;
你会发现时间缩短了一半;然后我们在拿id分别去取10条数据就行了;
语句就改成这样了:
select table.* from table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;
这三种方法最先考虑第一种 其次第二种,第三种是别无选择