我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL大数据查询性能优化的示例

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL大数据查询性能优化的示例

这篇文章将为大家详细讲解有关MySQL大数据查询性能优化的示例,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。

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

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

表的优化:

1、定长与变长分离

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

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

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

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

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

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

看如下的效果:

MySQL大数据查询性能优化的示例

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

这是如何做的

MySQL大数据查询性能优化的示例

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

MySQL大数据查询性能优化的示例

四、索引实验

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

如下:

MySQL大数据查询性能优化的示例

注:(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大数据查询性能优化的示例”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL大数据查询性能优化的示例

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL数据库查询性能优化策略

优化查询使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句。 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询
2022-05-25

mysql-查询性能优化

1、不要取出全部列,取出全部列,会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。应该严格禁止SELECT * 的写法。MySQL使用如下三种方式应用WHERE条件,从好到坏依次为:  1.1 在索引中使用WHERE条件
mysql-查询性能优化
2021-02-18

MySQL数据库性能优化的示例分析

小编给大家分享一下MySQL数据库性能优化的示例分析,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!为什么做优化??因为数据量太多了,项目部署上线再到用户使用,每天
2023-06-22

SQL查询优化-MySQL 性能调优

  在进行库表结构设计时,我们要考虑到以后的查询要如何的使用这些表,同样,编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引,或是如何增加新的索引才能提高查询的性能。  想要对存在性能问题的查询进行优化,需要能够找到这些查询,下面先看下如何获取有性能问题的SQL。  如何设计最优的数据库表结构,如何建立最好
SQL查询优化-MySQL 性能调优
2024-04-18

如何优化MySQL的查询性能?

如何优化MySQL的查询性能?MySQL是一款广泛应用于Web开发的关系型数据库管理系统。然而,在处理大量数据和复杂查询时,MySQL的查询性能可能会受到影响,从而导致应用程序的响应时间变慢。为了提高MySQL的查询性能,我们可以采取以下几
2023-10-22

数据库性能优化之IN子查询优化

PawSQL Cloud,在线自动化SQL优化工具,支持SQL审查,智能查询重写、基于代价的索引推荐,适用于数据库管理员及数据应用开发人员。
PawSQL数据库2024-11-30

mysql大数据量查询怎么优化

在处理大数据量查询时,可以通过以下几种方法来优化查询性能:使用合适的索引:通过在查询字段上创建索引,可以大大减少查询所需的时间。确保为经常用于查询的字段创建索引,并避免在太多字段上创建索引。分批查询:如果需要查询大量数据,可以考虑将查询结果
mysql大数据量查询怎么优化
2024-04-12

数据库查询性能优化指南

数据库查询性能优化一直是程序员绕不开的话题,当我们遇到业务刷新报表缓慢或者查询获取结果延迟太大,可以采用提问法来思考如何进行优化。

Atlas如何优化大数据处理和查询性能

Atlas可以通过以下几种方式优化大数据处理和查询性能:数据分区:将数据按照一定的规则进行分区存储,可以减少查询时需要扫描的数据量,提高查询效率。索引优化:使用索引可以加快查询速度,特别是针对经常被查询的字段进行索引优化。数据压缩:对于大数
Atlas如何优化大数据处理和查询性能
2024-03-11

MySQL的查询优化器与HBase的查询优化策略在大数据查询中的协同

MySQL和HBase是两种不同类型的数据库,分别适用于不同的使用场景和需求。MySQL是一个关系型数据库,适用于事务处理和数据一致性要求较高的应用;而HBase是一个分布式的、面向列的NoSQL数据库,适用于大数据的实时读写和存储。它们在
MySQL的查询优化器与HBase的查询优化策略在大数据查询中的协同
2024-10-22

怎么优化NoSQL数据库的查询性能

要优化NoSQL数据库的查询性能,可以考虑以下几个方面:数据模型设计:设计合理的数据模型可以减少查询的复杂度,提高查询性能。可以根据实际需求对数据进行合理划分和建模。索引优化:在NoSQL数据库中,可以通过创建合适的索引来加快查询速度。确保
怎么优化NoSQL数据库的查询性能
2024-05-07

编程热搜

目录