提升MySQL查询效率及查询速度优化的方法是什么

一、利用EXPLAIN关键字来评估查询语句中的缺陷

笔者在数据库中执行了一条简单的Select查询语句,以获取一个表格中的所有信息,如下图所示。现在数据库管理员想知道,数据库在执行这条语句时,做了哪些工作或者说想知道,这条查询语句有没有进一步优化的可能。如果要了解这个信息的话,就可以在查询语句中加入一个Explain关键字。

通过Select查询语句可以从数据库中查询某个表中的数据。从这个简单的查询语句中无法得出执行效率和优化余地这些内容。为了了解更加详细的信息,需要加入Explain关键字。如下图所示:

加入Explain关键字之后,系统并没有查询出表格中的数据,而只是显示了查询过程中的一些信息。这些信息对于我们后续进行数据库查询优化非常有帮助。从上面这个信息中我们可以看出,用户只是进行来一个简单的查询。在此查询中,未使用任何索引、关键字或Where条件语句。为此这个查询语句并不是很合理。虽然其可以找到最后正确的结果,不过其查询效率可能并不是很明显。为此数据库专家可以根据上面显示的信息来进行优化。What would be the result if we add a WHERE statement to our query now, as shown in the following image?。

当在最后一个Extra字段中使用Where语句时,系统将显示已经使用。在进行数据库优化中,我们需要抓住结果中的NULL字段或者空白内容的字段。这些地方往往是我们进行优化的重点。可以通过在表中设置关键字或索引来优化这条Select语句,提高查询效率,如图所示。

二、数据比较时采用相同类型的列以提高查询效率

在数据查询时,有时候会在条件语句中加入判断的条件。现在有两个表格:用户基本信息表格和用户权限表格,它们通过用户编号进行关联。要查询每位用户的权限信息,需要以用户编号作为查询条件进行查询。现在假设用户基本信息表中的用户编号字段为CHAR类型的;而用户权限表中的用户编号是VARCHAR类型的。这两个数据类型虽然都是字符型,但是不是同一种类型。现在对这连个表执行关联查询,其查询的效率如何呢首先需要确定的一点是,虽然他们两个是不同类型的字符型数据,不过是相互兼容的。最后仍然可以得到正确的结果。在明确了这一点之后,让我们再考虑一下是否可以优化这个查询语句

我们再假设一下。现在这两个表的用户编号的数据类型都是CHAR。现在再对这两个表进行关联查询,得到的结果是否相同呢我们测试的结果是,查询的结果是相同的,但是其所花费的时间是不同的。而且随着数据量的增加,两个查询所相差的时间会越来越长。从这里可以知道,虽然这两个查询语句是等价的,但是其查询的效率不同。

虽然数据类型在MySQL数据库中相互兼容,但仍可以进行比较。但是其查询的效率会有所影响。为了提高数据库查询效率,笔者建议在查询条件语句中最好比较具有相同类型的列。在同等条件下,相同的列类型比不同类型的列能够提供更好的性能。特别是在数据量比较多的数据库中,这尤其重要。

不过这个优化需要涉及到数据表的列类型。为此在数据表进行设计时,就需要考虑这一点。为了上述案例,我们可以在这两个表格中专门添加一个用户ID列。可以使用整数类型的序列,让系统进行自动编号。然后在查询时通过这个用户ID列来进行比较,而不是通过原来的用户编号列进行比较。相对来说,这么操作查询的效率会更高。

三、在Like关键字的起始处通配符要谨慎使用

在实际工作中,笔者发现不少数据库管理员有一个不好的习惯。他们在使用Like等关键字时,通配符会乱用。如现在用户需要查找所有以“LOOK”为前缀的产品信息。查询时,用户通常会习惯使用类似以下的语句:使用 like “%LOOK%”。此条件语句将获取所有品名中包含LOOK单词的记录,而非仅获取以LOOK为前缀的产品信息。

虽然最终的结果可能是相同的。但是两者的查询效率不同。其实这很大一部分原因是客户端应用程序设计不当所造成的。如在客户端应用程序设计时,系统会默认显示一个%符号。如下图所示。

这么设计的本意是好的,让系统能够支持模糊查询。但是用户在实际操作起来,就可以有问题。如果用户在查询时没有在%前输入单词LOOK,而是在%后输入该单词。因为在查询时,光标会自动定位到%号后面。通常情况下,用户在输入时不会再去调整光标的位置。此时就出现了上面所说的这种情况。

为了避免意外情况,笔者强烈建议在使用Like等关键字后跟通配符时要十分谨慎。特别是从大量数据中查找纪录时,这个通配符的位置一定要用对地方。在起始处能够不同通配符的话,尽量不要使用通配符。

四、尽量使用其它形式来代替Like关键字

上面提到在使用Like关键字时需要注意通配符的位置。在查询效率方面,我们需要注意通配符的位置,并尽可能避免使用“Like”关键字。其实在SQL语句中,可以利用其他方式来代替Like关键字。如现在有一个产品表,其编号为6位。现在需要查询以9开头的产品编号。这该怎么操作呢

一是可以通过使用Like关键字,如LIKE “9%”。注意这个通配符的位置。这个条件语句可以查到所需要的结果。但是从性能优化的角度看,这条语句不是很好的处理方式。我们还可以通过一些折中的方式来实现。

二是通过比较符号来实现。这句话可以重新表达为:可以实现通过使用Value在900000到999999之间的方法。虽然两者的查询的结果是相同的。但是查询的时间这条语句要比上面这个采用Like符号的语句要短的多。

以上就是提升MySQL查询效率及查询速度优化的方法是什么的详细内容,更多请关注www.sxiaw.com其它相关文章!