解析MySQL数据库性能优化的六大技巧
程序员文章站
2024-02-22 10:21:46
数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕。如果你是个数据库管理员,你很有可能...
数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间。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仍将遍历索引列的基础上。那么,就要在索引上动脑筋,注意如何避免大量的全表扫描,并获得更好的结果。不过,这需要经历一个很长的过程。
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仍将遍历索引列的基础上。那么,就要在索引上动脑筋,注意如何避免大量的全表扫描,并获得更好的结果。不过,这需要经历一个很长的过程。