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

Wesley13
• 阅读 292

数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。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
2年前
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
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这