分享Mysql优化思绪
第一构建足本调查查询数,连贯数等数据,肯定环境缘由以及内部SQL施行缘由,然后依据概括缘由做概括处置。
举荐:《mysql视频教程》
二、构建足本调查状态
mysqladmin -uroot -p ext \G
该下令可猎取目前查询数目等信息,按时轮询并将效果重定向到文本中,然后处置成图表。
三、处置对策
1.若是纪律性涌现查询慢,考虑缓存雪崩题目。
关于该题目只需将缓存的失效工夫处置成不要附近工夫同时失效,失效工夫尽量离散化,或者集中到午夜失效。
2.若非纪律性查询迟缓,考虑设计缺乏优化
处置办法:
a:开启profiling记载查询操纵,并猎取语句施行细致信息
show variables like '%profiling%'; set profiling=on; select count(*) from user; show profiles; show profile for query 1; >>> +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000060 | | Executing hook on transaction | 0.000004 | | starting | 0.000049 | | checking permissions | 0.000007 | | Opening tables | 0.000192 | | init | 0.000006 | | System lock | 0.000009 | | optimizing | 0.000005 | | statistics | 0.000014 | | preparing | 0.000017 | | executing | 0.001111 | | end | 0.000006 | | query end | 0.000003 | | waiting for handler commit | 0.000015 | | closing tables | 0.000011 | | freeing items | 0.000085 | | cleaning up | 0.000008 | +--------------------------------+----------+
b:运用explain 查看语句施行状况,索引运用,扫描范畴等等
mysql> explain select count(*) from goods \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods partitions: NULL type: index possible_keys: NULL key: gid key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using index
c:相干优化手法
表的优化与列类型选中
列选中准则:
1:字段类型优先级 整型 > date,time > char,varchar > blob
缘由:整型,time运算快,节俭空间
char/varchar要考虑字符集的转换与排序时的校对集,速度慢
blob没法运用内存暂时表
2:够用就行,不要大方(如 smallint,varchar(N))
缘由:大的字段浪费内存,影响速度
以varchar(10), varchar(300)存储的内容雷同,但在表联查时,varchar(300)要花更多内存
3:尽量以免运用NULL
缘由:NULL不利于索引,要用特别的字节来标注.
在磁盘上占领的空间其实更大
索引优化战略
1.索引类型
1.1 B-tree索引(排好序的迅速查寻构造)
注:Myisam,innodb中,默许用的是B-tree索引
1.2 hash索引
在memory表里,默许是hash索引,hash的理论查询工夫复查度为O(1)
疑难:既然hash索引如此高效,为什么不都用他?
a.hash函数盘算后的效果是随机的,要是是在磁盘上放置数据,以主键为id为例,那么随着id的增长,id对应的行,在磁盘上随机放置。
b.没法对范畴查询进行优化
c.没法应用前缀索引,比方在b-tree中,field列的值为“helloworld”,索引查询xx=hello/xx=helloworld都可以应用索引(左前缀索引),但hash索引没法做到,由于hash(hello)与hash(helloworld)并无关联关系。
d.排序也没法优化
e.必需回行,通过索引拿到数据位置,必需回到表中取数据.
2.b-tree索引的常见误区
2.1 在where前提常用的列上都加上索引
例:where cat_id=3 and price>100; //查询第3个栏目,100元以上的商品
误:cat_id和price上都加上索引。其实只能用上一个索引,他们都是独立索引.
2.2 在多列上创立索引后,查询哪个列,索引都将发挥作用
2.2 在多列上创立索引后,查询哪个列,索引都将发挥作用
正解:多列索引上,索激发挥作用,需要知足左前缀请求(层层索引)
以index(a,b,c)为例:
语句 索引可否发挥作用 where a=3 是 where a=3 and b=5 是 where a=3 and b=5 and c=4 是 where b=3 or where c=4 否 where a=3 and c=4 a列能发挥索引作用,c列不克不及 where a=3 and b>10 and c=7 a,b能发挥索引作用,c列不克不及
高机能索引战略
1.关于innodb而言,由于节点下有数据文件,因而节点的破裂将会变得比拼慢,关于innodb的主键,尽量用整型,并且是递增的整型。
2.索引的长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。
3.针对列中的值,从左往右截取局部来建索引。
a.截的越短,反复度越高,区分越小,索引结果越欠好
b.截的越长,虽然区分度提高,但索引文件变大影响速度
所以尽量在长度上寻到一个均衡点使机能最大化,习用手法:截取不一样长度来测试索引区分度
区分度测试:
select count(distinct left(word, 1)) / count(*) from table;
测试完成后可以按测试得出的最优长度创立索引
alter table table_name add index word(word(4));
志愿的索引
1.查询频繁
2.区分度高
3.长度小
4.尽量遮盖常用查询字段
以上就是分享Mysql优化思绪的细致内容,更多请关注 百分百源码网 其它相干文章!