Mysql的简单优化

参见：http://www.phpnotes.top/2017/11/24/mysql/32/

定义字段

表结构的拆分,如核心字段都用int,char,enum等定长结构

1:字段类型优先级 整型 > date,time > enum,char>varchar > blob

列的特点分析:

整型: 定长,没有国家/地区之分,没有字符集的差异

time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;

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不利于索引,要用特殊的字节来标注.

在磁盘上占据的空间其实更大.

索引优化策略

1:索引类型

1.1 B-tree索引

注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,

比如,严格的说,NDB引擎,使用的是T-tree

Myisam,innodb中,默认用B-tree索引

 

但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.

 

1.2 hash索引

在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

疑问: 既然hash的查找如此高效,为什么不都用hash索引?

答:

1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,

比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.

2: 不法对范围查询进行优化.

3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引

查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)

因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机

4: 排序也无法优化.

5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

2: btree索引的常见误区

2.1 在where条件常用的列上都加上索引

例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品

误: cat_id上,和, price上都加上索引.

错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

 

2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用

误: 多列索引上,索引发挥作用,需要满足左前缀要求.

以 index(a,b,c) 为例,

语句索引是否发挥作用Where a=3是,只使用了a列Where a=3 and b=5是,使用了a,b列Where a=3 and b=5 and c=4是,使用了abcWhere b=3  /  where c=4否Where a=3 and c=4a列能发挥索引,c不能Where a=3 and b>10 and c=7A能利用,b能利用, C不能利用同上,where a=3 and b like ‘xxxx%’ and c=7A能用,B能用,C不能用

 

假设某个表有一个联合索引（c1,c2,c3,c4）index(c2)一下——只能使用该联合索引的c1,c2,c3部分

A where c1=x and c2=x and c4>x and c3=x

B where c1=x and c2=x and c4=x order by c3

C where c1=x and c4= x group by c3,c2

D where c1=x and c5=x order by c2,c3

E where c1=x and c2=x and c5=? order by c2,c3

 

Where c2=X;

 

聚簇索引与非聚簇索引

Myisam与innodb引擎,索引文件的异同

 

sql语句优化

1:  sql语句的时间花在哪儿?

答: 等待时间 , 执行时间.

这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.

所以,我们来分析如何降低执行时间.

 

2: sql语句的执行时间,又花在哪儿了?

答：

a: 查 ----> 沿着索引查,甚至全表扫描

b: 取 ----> 查到行后,把数据取出来(sending data)

 

3: sql语句的优化思路？

答:

 

少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.

 

必须要查,尽量走在索引上查询行.

 

取时, 取尽量少的列.

比如  select * from tableA,  就取出所有列, 不建议.

比如  select * from tableA,tableB, 取出A,B表的所有列.

 

4: 如果定量分析查的多少行,和是否沿着索引查?

答: 用explain来分析