Mysql建表时的一些建议

2019独角兽企业重金招聘Python工程师标准>>>

1&＃xff0c;单库表别太多&＃xff0c;一般保持在200以下为宜

2&＃xff0c;尽量避免SQL中出现运算&＃xff0c;例如select a&＃43;6 from t&＃xff0c;让DB功能单一化

3&＃xff0c;表设计尽量小而精&＃xff0c;能用5个字段就不要用6个&＃xff08;不绝对&＃xff0c;取决于业务&＃xff0c;该冗余时坚决不要手软&＃xff09;

4&＃xff0c;SQL事务不能设计太大&＃xff0c;比如一次性提交10W条insert&＃xff0c;当然这个不仅仅是性能问题了&＃xff0c;可能直接内存溢出了

一般来说insert事务的话&＃xff0c;5K-1W来做批处理就可以了&＃xff08;字段不能太大&＃xff09;

5&＃xff0c;设计表的时候尽量用”小数据类型”&＃xff0c;比如尽量避免text&＃xff0c;blob等这些大家伙&＃xff0c;优先使用ENUM和SET&＃xff08;小而美&＃xff0c;范围有限&＃xff0c;百益无一害&＃xff09;

6&＃xff0c;设计表字段能用数字类型就千万别用字符类型&＃xff0c;比如存IP地址&＃xff0c;用int&＃xff0c;别用varchar&＃xff08;博客有参考&＃xff09;

7&＃xff0c;尽量避免null字段&＃xff0c;定义时尽量使用 not null。原因是允许null时不方便查询优化&＃xff0c;复合索引也会失效&＃xff0c;而且如果列有索引时会额外占用空间&＃xff1a; a int(10) NOT NULL DEFAULT 0

8&＃xff0c;图片等大家伙不要存DB。

A&＃xff0c;大SQL尽量拆分&＃xff0c;多核CPU每个CPU只能执行一个SQL&＃xff0c;所以并发时&＃xff0c;一堆小的可能效率更高一些&＃xff0c;并且容易命中缓存&＃xff0c;而且不容易长时间锁表&＃xff08;无论什么锁都是时间越短越好&＃xff09;&＃xff0c;当然这个要结合实际情况分析了&＃xff0c;一大堆小的万一增加IO负担呢。

B&＃xff0c;事务尽可能的小&＃xff0c;代码别偷懒&＃xff0c;全加到一个transaction中&＃xff0c;道理不多说了

C&＃xff0c;存储过程&＃xff0c;触发器之类的能避就全避免了吧&＃xff0c;维护不方便&＃xff0c;人员变动时&＃xff0c;很多时候就忘了&＃xff0c;时间一长全是定时炸弹

D&＃xff0c;禁止select *&＃xff0c;不用问为啥了&＃xff0c;禁止就是禁止&＃xff01;需要啥就取啥是王道

E&＃xff0c;update时&＃xff0c;where语句尽量要走索引&＃xff0c;不然会全表扫描&＃xff0c;一般情况下&＃xff0c;1G的数据至少10S&＃xff08;想想这可是update啊&＃xff0c;锁住10S意味着啥&＃xff09;

F&＃xff0c;or尽量不用&＃xff0c;改为in()&＃xff0c;当然in的范围太多也不行&＃xff0c;尽量别超100

G&＃xff0c;还是or&＃xff0c;如果&＃xff1a;select a from A where b&＃61;1 or c&＃61;1这种where里面不同字段进行or&＃xff0c;这种尽量改为union。

select a from A where b&＃61;1 union select a from A where c&＃61;1

H&＃xff0c;避免 “% 前缀”模糊查询。因为会导致索引失效&＃xff0c;大数据量下是灾难

I&＃xff0c;分页时&＃xff1a;Select a from A limit 10000,10; 这种大偏移量下效率非常低。可以考虑如下几个方案&＃xff1a;

select a from A WHERE id>&＃61;xxxx limit 11;(将上一页的最大值通过where id> 进行预处理&＃xff0c;然后分页)

select a from A WHERE id >&＃61; ( select a from A limit 10000,1 ) limit 10;

select a from A inner join (select a from A limit 10000,10) using (id) ;

J&＃xff0c;避免使用count(*)&＃xff0c;不知道为什么mysql优化这么个东西有那么难么&＃xff0c;但是实际上大数量下这个东西真心慢&＃xff0c;1000W以上至少几秒&＃xff0c;作为替代方案&＃xff0c;考虑使用nosql例如redis&＃xff0c;memcached存下来&＃xff0c;但是要定时校对。还有一个办法&＃xff0c;直接做一个表存下来&＃xff0c;每次增加或者减少都在这个表做update增减

K&＃xff0c;UNION ALL 而非 UNION &＃xff0c;看需要啦&＃xff0c;一般不用去重的业务的话去重压力不小&＃xff0c;能省则省

L&＃xff0c;尽量不用 INSERT SELECT&＃xff0c;数据量大有延迟&＃xff0c;同步完了可能有错误