1.单库表不超过300-400,如果快要达到,则考虑分库。
2.单表数据量,纯INT的不超过1000w,纯char的不超过500W.
3.单表字段数控制在20-50个,字段数过多,表越难维护。单表1G体积 500W行评估:
顺序读1G文件需N秒
单行不超过200Byte
单表不超过50个纯INT字段
单表不超过20个CHAR(10)字段
4.拒绝3B:大SQL,大事务,大批量。
5.尽量不在数据库做运算。
6.字段类型推荐:tinyint,smallint,mediumint,int,bigint,float,double,decimal
数字类型比字符串类型更搞效,查询更快,占用空间更小。
7.避免使用null字段,null字段很难进行查询优化,nulll列建索引,会需要额外的存储空间。含null的复合索引无效。
Eg:
`a` char(32) DEFAULT NULL ×
`b` int(10) NOT NULL ×
`c` int(10) NOT NULL DEFAULT 0 √
8.尽量不适用text/blob字段类型。如果必须使用,则将该字段拆分到其他单表。text类型处理性能低于varchar,会强制磁盘临时表,浪费更多的空间。
9.不在数据库中存储照片信息
10.谨慎合理添加索引,改善查询,减慢更新。能不加的索引尽量不要添加,综合评估数据密度和数据分布进行添加,索引数最好不超过字段的20%。结合核心SQL优先考虑覆盖索引。
11.字符字段必须建立前缀索引,区分度:单字母区分度:26,4字母区26*26*26*26=456,976,5字母区分度:26*26*26*26*26=11,881,376,6字母区分26*26*26*26*26*26=308,915,776。
12.不在索引列进行数学运算或者函数运算。运算将导致无法使用索引,导致全表扫描。
13.自增列或者全局ID做innodb主键。对主键建立聚簇索引,二级索引存储主键值,主键不应更新修改,女子增顺序插入值,忌用字符串做主键,聚簇索引分裂,推荐用独立亍业务的AUTO_INCREMENT列或全局ID生成器做代理主键,若丌指定主键,InnoDB会用唯一且非空值索引代替。
14.尽量不适用外键,外键可节省开发量,但是有额外的开销,对数据进行逐行操作,可“到达”其他表,以为着可能产生锁表操作,高并发时容易死锁。尽量由程序保证约束。
SQL军规
15.简单SQL缓存命中率更高,锁表时间更少,特别是对myisam引擎,用上多CPU。
16.保证事务连接短小:即开即用,用完即关,与事务无关的操作放到事务外面,减少锁资源的占用,不破坏一致性的前提下,使用多个短事务代替长事务。
17.尽量减少使用存储过程,触发器,减少MYSQL函数对结果进行处理。原因?
18.尽量不适用select * ,只取需要的列,select * 会更多的消耗CPU,内存,I/O,网络带宽,会先向数据库请求所有列,然后丢弃不需要的列。只取需要的列可以减少表变化带来的影响,未使用覆盖索引带来可能性。减少了磁盘临时表的生成,特别适用text/bolb时。
19. 同一字段,将or改写为in()。 OR效率:O(n);IN 效率:O(Log n);当n很大时,OR会慢很多,注意控制IN的个数,建议n小亍200。
20.改写OR为UNION,不同字段,将or改为union;减少对不同字段进行 “or” 查询Merge index往往很弱智; 如果有足够信心:set globaloptimizer_switch=’index_merge=off’;
21.避免负向查询和% 前缀模糊查询;避免负向查询:NOT、 !=、 <>、 !<、 !>、 NOT EXISTS、 NOT IN、NOT LIKE等。
22.避免 % 前缀模糊查询,B+ Tree;使用不了索引;导致全表扫描。
23.COUNT(*)的资源开销大,尽量不用少用。计数统计: 实时统计:用memcache,双向更新,凌晨跑基准;非实时统计:尽量用单独统计表,定期重算。
24.用UNION ALL 而非 UNION,若无需对结果进行去重,则用UNION ALL,UNION有去重开销。
25.高并发DB不建议进行两个表以上的JOIN。适当分解联接保证高幵发:可缓存大量早期数据,使用了多个MyISAM表,对大表的小ID IN(),联接引用同一个表多次。
26.同数据类型的列值比较:数字对数字,字符对字符。数字与字符对比,同时转换为双精度对比。字符与数值比对,字符整列转数值,不会使用索引查询。
27.尽量丌用 INSERT … SELECT,容易造成延迟,同步出错。
28.大批量更新凌晨操作,避开高峰
29.MySQL子查询大部分情况优化较差,特别WHERE中使用IN id的子查询,一般可用JOIN改写

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

Captcha Code