表的优化与列类型选择
表的优化
1、定长与变长分离
定长:id int 占 4 个字节,char(4)占 4 个字符长度等等
核心且常用字段,应该建成一个定长,并放在一张表。
变长字段:varchar、text 等变长字段,适合单放一张表,用主表与核心表关联起来。
2、常用字段与不常用字段分离
需要结合网站的具体业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来。
3、在一对多的情况下,需要关联“统计”字段,添加冗余字段。比如统计文章个数。
列类型选择
1、字段类型优先级:整型 > date, time > enum, char > varchar > blob, text
整型:定长的,没有国家、地区之分,没有字符集的差异。
例如:tinyint 1, 2, 3, 4, 5 对比 char(1) a, b, c, d, e 在空间上都是占 1 个字节,但在 order by 排序方面,整型更快。原因是字符集需要考虑字符集与校对集(就是排序规则)
time: 运算快,节省空间,考虑时区,写sql时不方便,where > ‘2015-05-15’
enum:具有约束值的作用,内部用整型来存储,但与 char 联查时,内部要经过串与值的转化。
char:定长,考虑字符集与(排序)校对集。
varchar:不定长,要考虑字符集的转换与排序时的校对集,速度慢
text/blob:无法使用内存临时表(排序等操作只能在磁盘上进行)
2、够用就行,不要慷慨(如smallint、varchar(N)等)
原因:大的字段浪费内存,影响速度
以年龄为例,tinyint unsigned not null,可以存储255岁足够,用 int 浪费 3 个字节。
以 varchar(10)、 varchar(300)存储的内容相同,但在表联查时, varchar(300)花费更多的内存。
3、尽量避免用 null
原因:NULL 不利于索引,要用特殊的字节标注。在磁盘上占据的空间其实更大。
查询大原则
sql 语句优化
1、sql语句的时间花在哪儿?
答:等待时间和执行时间。注意:这两个时间并非孤立的,如果单条语句执行地快了,对其他语句的锁定的也就少了。所以,我们来分析如何降低执行时间。
2、sql语句的执行时间,又花在哪儿了?
答:查找(沿着索引查找,慢者可能全表扫描)、取出(查到行后,把数据取出来)