MySQL大数据查询性能优化教程(图)_MySQL_青云站长教程网
欢迎来到站长教程网!
  • 秒到短信接口 免费试用
  • 微信支付宝接口 秒结算
  • 中文网站排名|申请加入
  • 搜索引擎全站查询
  • 查询移动网站权重数据
  • 海外主机 台湾公司直销
  • MySQL

    当前位置:主页 > 数据库 > MySQL >

    MySQL大数据查询性能优化教程(图)

    时间:2019-09-11|栏目:MySQL|点击:
  • MySQL性能优化包括表的优化与列类型选择,表的优化可以细分为什么? 1、定长与变长分离;2、常用字段与不常用字段要分离; 3、在1对多,需要关联统计的字段上添加冗余字段。

    一、表的优化与列类型选择

    表的优化:

    1、定长与变长分离

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

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

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

    2、常用字段与不常用字段要分离

    需要结合网站具体的业务来分析,分析字段的查询场景,查询频率低的字段,单拆出来。

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

    看如下的效果:

    22.png

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

    这是如何做的

    33.png

    如果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) 为例,(注意和顺序有关)

    44.png

    四、索引实验

    例如: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

    如下:

    55.png

    注:(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

    下一篇:带进度的SQL Server FileStream如何存取

    本文标题:MySQL大数据查询性能优化教程(图)

    本文地址:http://www.jh-floor.com/shujuku/MySQL/50136.html

    广告投放 | 联系我们 | 版权申明

    重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

    如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

    联系QQ:888888 | 邮箱:888888#qq.com(#换成@)

    Copyright © 2002-2017 青云站长教程网 版权所有 琼ICP备xxxxxxxx号