旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

MySQL大数据查询机能优化教程(图)【MySQL教程】,MySQL性能优化

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:55评论:0


导读:MySQL机能优化包含表的优化与列范例挑选,表的优化能够细分为何?1、定长与变长星散;2、经常运用字段与不经常运用字段要星散;3、在1对多,须要关联统计的字段上增加冗余字段。...
MySQL机能优化包含表的优化与列范例挑选,表的优化能够细分为何? 1、定长与变长星散;2、经常运用字段与不经常运用字段要星散; 3、在1对多,须要关联统计的字段上增加冗余字段。

一、表的优化与列范例挑选

表的优化:

1、定长与变长星散

如 id int,占4个字节,char(4)占4个字符长度,也是定长,time即每一单位值占的字节是牢固的。

中心且经常运用字段,宜建成定长,放在一张表。

而varchar,text,blob这类变长字段,合适单放一张表,用主键与中心表关联起来。

2、经常运用字段与不经常运用字段要星散

须要连系网站细致的营业来剖析,剖析字段的查询场景,查询频次低的字段,单拆出来。

3、在1对多,须要关联统计的字段上增加冗余字段。

看以下的效果:

每一个版块里,有N条帖子,在首页显现了版块信息和版块下的帖子数。

这是怎样做的

假如board表只要前2列,则须要掏出版块后,

再查post表,select count(*) from post group by board_id,得出每一个版块的帖子数。

二、列范例挑选

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 > `2018-08-08`;

enum,能起到束缚的目标,内部用整型来存储,但与cahr联查时,内部要阅历串与值的转化;

char定长,斟酌字符集和(排序)校正集;

varchar不定长,要斟酌字符集的转换与排序时的校正集,速率慢;

text/blob 没法运用内存暂时表(排序等操纵只能在磁盘上举行)

附:关于date/time的挑选,巨匠的明白看法,直接选 int unsgined not null,存储时候戳。

比方:

性别:以utf8为例

char(1) ,3个字长字节

enum('男','女'); 内部转成数字来存,多一个转换历程

tinyint(), 定长1个字节

2、够用就行,不要激昂大方(如 smallint varchar(N))

缘由:大的字节糟蹋内存,影响速率。

以岁数为例 tinyint unsigned not null,能够存储255岁,充足。用int糟蹋了3个字节;

以varchar(10),varchar(300)存储的内容雷同,但在表联查时varchar(300)要花更多内存。

3、只管避免用NULL()

缘由:NULL不利于索引,要用特别的字符来标注。

在磁盘上占有的空间实在更大(MySQL5.5已对null做的革新,但查询还是不方便)

三、索引优化战略

1、索引范例

1.1 B-tree索引

名叫btree索引,大的方面看,都用的均衡树,但细致的完成上,各引擎稍有差别,比方,严厉的说,NDB引擎,运用的是T-tree.

但笼统一下 B-tree体系,可理解为“排好序的疾速查询构造”。

1.2 hash索引

在memory内外默许是hash索引,hash的理论查询时候复杂度为O(1)。

疑问:既然hash的查找云云高效,为何不都用hash索引?

回覆:

1、hash函数盘算后的效果,是随机的,假如是在磁盘上安排数据,以主键为id为例,那末跟着id的增进,id对应的行,在磁盘上随机安排。

2、没法对局限查询举行优化。

3、没法应用前缀索引,比方在btree中,field列的值“helloworld”,并加索引查询 x=helloworld天然能够应用索引,x=hello也能够应用索引(左前缀索引)。

4、排序也没法优化。

5、必需回行,就是说经由过程索引拿到数据位置,必需回到表中取数据。

2、btree索引的罕见误区

2.1 在where前提经常运用的列上加索引,比方:

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

误区:cat_id 上和price上都加上索引。

错:只能用上cat_id 或 price索引,由于是自力的索引,同时只能用一个。

2.2 在多列上竖立索引后(团结索引),查询哪一个列,索引都会将发挥作用

误区:多列索引上,索引发挥作用,须要满足左前缀请求。

以 index(a,b,c) 为例,(注重和递次有关)

四、索引试验

比方:select * from t4 where c1=3 and c2 = 4 and c4>5 and c3=2;

用到了哪些索引:

explain select * from t4 where c1=3 and c2 = 4 and c4>5 and c3=2 \G

以下:

注:(key_len : 4 )

五、聚簇索引与非聚簇索引

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

Myisam:由news.myd和new.myi两个文件,索引文件和数据文件是离开的,叫非聚簇索引。主索引和次索引都指向物理行(磁盘的位置)

innodb:索引和数据是聚在一起的,所以是聚簇索引。innodb的主索引文件上直接寄存该行数据,次索引指向对主键索引的援用。

注重:innodb来讲:

1、主键索引 即寄存索引值,又在叶子中存储行的数据。

2、假如没有主键(primary key),则会unique key做主键。

3、假如没有unique,则体系生成一个内部的rowid做主键。

4、像innodb中,主键的索引构造中,即存储了主键值又存储了行数据,这类构造称为聚簇索引。

聚簇索引

上风:依据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势:假如遇到不划定规矩数据插入时,形成频仍的页破裂

相干文章:

Mysql 机能优化

相干视频:

MySQL优化视频教程

以上就是MySQL大数据查询机能优化教程(图)的细致内容,更多请关注ki4网别的相干文章!

标签:MySQL性能优化


欢迎 发表评论: