MySQL数据库性能优化六大技巧

Wesley13
• 阅读 172

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕。 如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况。因此,本文将介绍对MySQL进行性能优化的技巧和窍门。

1.存储引擎的选择

如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性。如果不需要事务处理,使用默认存储引擎MyISAM是比较明智 的。并且不要尝试同时使用这两个存储引擎。思考一下:在一个事务处理中,一些数据表使用InnoDB,而其余的使用MyISAM。结果呢?整个 subject将被取消,只有那些在事务处理中的被带回到原始状态,其余的被提交的数据转存,这将导致整个数据库的冲突。然而存在一个简单的方法可以同时 利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表,但如果你选择MyISAM,你仍然可以单独下载InnoDB,并把 它作为一个插件。很简单的方法,不是吗?

2.计数问题

如果数据表采用的存储引擎支持事务处理(如InnoDB),你就不应使用COUNT(*)计算数据表中的行数。这是因为在产品类数据库使用 COUNT(*),最多返回一个近似值,因为在某个特定时间,总有一些事务处理正在运行。如果使用COUNT(*)显然会产生bug,出现这种错误结果。

3.反复测试查询

查询最棘手的问题并不是无论怎样小心总会出现错误,并导致bug出现。恰恰相反,问题是在大多数情况下bug出现时,应用程序或数据库已经上线。的 确不存在针对该问题切实可行的解决方法,除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试,才能被认可。

4.避免全表扫描

通常情况下,如果MySQL(或者其他关系数据库模型)需要在数据表中搜索或扫描任意特定记录时,就会用到全表扫描。此外,通常最简单的方法是使用 索引表,以解决全表扫描引起的低效能问题。然而,正如我们在随后的问题中看到的,这存在错误部分。

5.使用”EXPLAIN”进行查询

当需要调试时,EXPLAIN是一个很好的命令,下面将对EXPLAIN进行深入探讨。

首先,创建一个简单的数据表:

CREATETABLE'awesome_pcq'(

'emp_id'INT(10)NOTNULL

DEFAULT'0',

'full_name'VARCHAR(100)NOTNULL,

'email_id'VARCHAR(100)NOTNULL,

'password'VARCHAR(50)NOTNULL,

'deleted'TINYINT(4)NOTNULL,

PRIMARYKEY('emp_id')

) COLLATE='utf8_general_ci'

ENGINE=InnoDB

ROW_FORMAT=DEFAULT 

这个数据表一目了然,共有五列,最后一列“deleted”是一个Boolean类变量flag来检查帐号是活动的还是已被删除。接下来,您需要用 样本记录填充这个表(比如,100个雇员记录)。正如你看到的,主键是“emp_id”。因此,使用电子邮件地址和密码字段,我们可以很容易地创建一个查 询,以验证或拒绝登录请求,如下(实例一):

SELECTCOUNT(*)FROMawesome_pcqWHERE

email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0 

之前我们提到,要避免使用COUNT(*)。代码纠正如下(实例二):

SELECTemp_idFROMawesome_pcqWHERE

email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

现在回想一下,在实例一中,代码查询定位并返回“email_id”和“password”等于给定值的行数。在实例二中,进行了同样的查询,不同 的是明确要求列出“emp_id”所有满足给定的标准的值。哪个查询更费时?

很显然,这两个实例都是同样费时的数据库查询,因为无意间,两个实例查询都进行了全表扫描。为了更好地读懂指令,执行如下代码:

EXPLAINSELECTemp_idFROMawesome_pcqWHERE

email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

在输出时,集中在倒数第二列:“rows”。假设我们已经将表填充了100个记录,它会在第一行显示100,这是MySQL需要进行扫描用来计算查询的结 果的行数。这说明了什么?这需要全表扫描。为了克服这个弊端,则需要添加索引。

6.添加索引

先从重要的说起:给每一个可能遇到的次要问题创建索引并不明智。过多的索引会导致效能减慢和资源占用。在进一步讨论之前,在实例中创建一个样本索 引:

ALTERTABLE'awesome_pcq'ADDINDEX'LoginValidate'('email_id')

接下来,再次运行该查询:

EXPLAINSELECTemp_idFROMawesome_pcqWHERE

email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

请注意运行后的值。不是100,而是1。因此,为了给出查询结果,MySQL只扫描了1行,多亏先前创建的索引。你可能会注意到,索引只在电子邮件 地址字段创建,而查询对其他字段同样进行了搜索。这表明MySQL先执行了一个cros-check,检查是否有在WHERE子句中的定义的值有索引指 定,如果有这样的值就执行相应的操作。

但是,它不是每次重复将减少到一个。例如,如果不是唯一的索引字段(如employee names列可以有两行相同的值),即使创建索引,也将有多个记录留下。但它仍然比全表扫描好。并且,在WHERE子句中指定列的顺序没有在这个过程中发 挥作用。例如,如果在上面的查询中,改变字段的顺序,使电子邮件地址出现在最后,MySQL仍将遍历索引列的基础上。那么,就要在索引上动脑筋,注意如何 避免大量的全表扫描,并获得更好的结果。不过,这需要经历一个很长的过程。

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
技术小男生 技术小男生
5个月前
linux环境jdk环境变量配置
1:编辑系统配置文件vi/etc/profile2:按字母键i进入编辑模式,在最底部添加内容:JAVAHOME/opt/jdk1.8.0152CLASSPATH.:$JAVAHOME/lib/dt.jar:$JAVAHOME/lib/tools.jarPATH$JAVAHOME/bin:$PATH3:生效配置
初识DevOps
基本概念和延伸的思考DevOps,是Development(开发)和Operations(运维)组成的复合词,一般译为“开发运维一体化”。看到这个概念,首先会产生几个问题:开发是什么,哪些环节是开发?运维是什么,哪些环节是运维?开发人员写好代码在本地调试,环境出问题了自己来调整,这是开发工作还是运维工作?系统故障后,运维人员发现是配置文件内容出错了就改成了正
SPDK QOS机制解析
本文关键词:intelspdkbdevqos序:intelspdk软件在存储领域应用广泛。因其可以高效管理linux系统的nvmessd盘,又支持vhostuser协议可以对接qemu虚拟机,在云计算领域通常被用来做本地盘云主机的存储管理软件。如此优秀的一款软件,有必要仔细分析其内部的实现机制,本篇文章主要介绍spdkqos机制。spdk
天翼云高可用虚拟IP(HAVIP)实践
(一)产品概述天翼云高可用虚拟IP(HighAvailabilityVirtualIPAddress,简称HAVIP)是一种可用独立创建和删除的私有网络IP地址资源。通过在VIPCIDR中申请一个私有网络IP地址,然后与高可用软件(如高可用软件Keepalived)配合使用,可用在VPC中搭建高可用的主备集群服务,提高VPC中服务的可用性。限制和说明
一个关于SDWAN单臂部署方案验证的实验
假设有这样一张网络,其中RTA和PCA表示某公司的A分支,通过中国电信CT路由器接入互联网ISP;RTB和PCB表示某公司的B分支,通过中国联通CU路由器接入互联网ISP。DNS(8.8.8.8)表示某互联网应用。为实现A分支私网192.168.2.0/24和B分支私网192.168.3.0/24的互通,现计划使用某厂商的SDWAN方案进打通两个内网,像下图
高性能API网关Kong介绍
本文关键词:高性能、API网关、Kong、微服务1.Introduction是随着微服务(Microservice)概念兴起的一种架构模式。原本一个庞大的单体应用(Allinone)业务系统被拆分成许多微服务(Microservice)系统进行独立的维护和部署,服务拆分带来的变化是API的规模成倍增长,API的管理难度也在日益增加,使用API网关发布和管
SPDK对接Ceph性能优化
关键词:SPDK、NVMeOF、Ceph、CPU负载均衡SPDK是intel公司主导开发的一套存储高性能开发套件,提供了一组工具和库,用于编写高性能、可扩展和用户态存储应用。它通过使用一些关键技术实现了高性能:1.将所有必需的驱动程序移到用户空间,以避免系统调用并且支持零拷贝访问2.IO的完成通过轮询硬件而不是依赖中断,以降低时延3.使用消息传递,以避免IO
3A网络 3A网络
5个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
3A网络 3A网络
5个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习