这篇文章将为大家详细讲解有关MySQL大数据查询性能优化的示例,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。
MySQL性能优化包括表的优化与列类型选择,表的优化可以细分为什么?,1,定长与变长分离;2,常用字段与不常用字段要分离,,3,在1对多,需要关联统计的字段上添加冗余字段。
<强>一、表的优化与列类型选择
<强>表的优化:
<强> 1,定长与变长分离
如id int,占4个字节,char(4)占4个字符长度,也是定长,时间即每一单元值占的字节是固定的。
核心且常用字段,宜建成定长,放在一张表。
而varchar、文本、blob这种变长字段,适合单放一张表,用主键与核心表关联起来。
<强> 2,常用字段与不常用字段要分离
需要结合网站具体的业务来分析,分析字段的查询场景,查询频率低的字段,单拆出来。
<强> 3,在1对多,需要关联统计的字段上添加冗余字段。
看如下的效果:
每个版块里,有N条帖子,在首页显示了版块信息和版块下的帖子数。
这是如何做的
如果板表只有前2列,则需要取出版块后,
再查表,从帖子group by board_id选择count(*),得出每个版块的帖子数。
<强>二、列类型选择
<强>,1字段类型优先级
整型祝辞日期
time> enum
char> varchar> blob,文本
整型:定长,没有国家/地区之分,没有字符集的差异。比如:
非常小的整数1,2,3,4,5 & lt;——比;char (1) a, b, c, d, e
从空间上,都占1个字节,但是按排的序,前者快。原因,或者需要考虑字符集与校对集(就是排序规则);
时间定长,运算快,节省空间。考虑时区,写sql时不方便,比;“2018-08-08”,
枚举,能起到约束的目的,内部用整型来存储,但与cahr联查时,内部要经历串与值的转化;
字符定长,考虑字符集和(排序)校对集;
varchar不定长,要考虑字符集的转换与排序时的校对集,速度慢;
文本/blob无法使用内存临时表(排序等操作只能在磁盘上进行)
附:关于日期/时间的选择,大师的明确意见,直接选int unsgined not null,存储时间戳。
例如:
性别:以utf8为例
char(1), 3个字长字节
enum(& # 39;男& # 39;& # 39;女& # 39;);内部转成数字来存,多一个转换过程
非常小的整数(),定长1个字节
<强>,2,够用就行,不要慷慨(如smallint varchar (N))
原因:大的字节浪费内存,影响速度。
以年龄为例非常小的整数无符号不是零,可以存储255岁,足够。用int浪费了3个字节;
以varchar (10)、varchar(300)存储的内容相同,但在表联查时varchar(300)要花更多内存。
<强> 3,尽量避免用null()
原因:零不利于索引,要用特殊的字符来标注。
在磁盘上占据的空间其实更大(MySQL5.5已对空做的改进,但查询仍是不便)
<强>三、索引优化策略
<强> 1,索引类型
<强> 1.1 b - tree索引
名叫btree索引,大的方面看,都用的平衡树,但具体的实现上,各引擎稍有不同,比如,严格的说,NDB引擎,使用的是-树。
但抽象一下b -树系统,可理解为“排好序的快速查询结构”。
<强> 1.2哈希索引
表在记忆里默认是哈希索引,散列的理论查询时间复杂度为O (1)。
疑问:既然散列的查找如此高效,为什么不都用哈希索引?
回答:
1,哈希函数计算后的结果,是随机的,如果是在磁盘上放置数据,以主键为id为例,那么随着id的增长,id对应的行,在磁盘上随机放置。
2,无法对范围查询进行优化。
3,无法利用前缀索引,比如来在中,字段列的值“helloworld”,并加索引查询x=helloworld自然可以利用索引,x=你好也可以利用索引(左前缀索引)。
4排序也无法优化。
5,必须回行,就是说通过索引拿到数据位置,必须回到表中取数据。
<强> 2、btree索引的常见误区
2.1在哪里条件常用的列上加索引,例如:
cat_id=3和price> 100;查询第三个栏目,100元以上的商品。
误区:cat_id上和价格上都加上索引。
错:只能用上cat_id或价格索引,因为是独立的索引,同时只能用一个。
