一、表的优化与列范例挑选
表的优化:
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网别的相干文章!