基于MySQL数据库的查询性能优化研究

石怡

（江苏信息职业技术学院 物联网工程学院，江苏 无锡 214153）

0 引言

大数据时代，传统关系型数据库管理系统（RDBMS）在处理海量数据时经历着严峻的性能考验。数据库性能的提升与数据查询的执行效率密切相关。查询操作是一个数据库系统运行时最主要的负载。事实上，对数据的增删改操作也常常建立在对所需数据检索的基础之上。因此，对查询语句的优化是数据库性能优化至关重要的手段之一。

MySQL数据库因其开放源码、运行速度快、磁盘空间占用少等优点，得到了广泛的应用，特别是在中小型WEB网站的后台应用。

1 MySQL查询执行过程

SQL查询语句在MySQL数据库中的执行过程主要划分为“SQL输入->词法扫描->语法分析->语义检查->优化->执行”6个阶段[1]，具体步骤如下：

（1）根据应用系统业务逻辑要求编写并输入相关SQL语句。

（2）由词法扫描器识别出SQL语句所包含的操作符、操作字符串和空格等单词。

（3）由语法分析器根据SQL语法规则，判断诸如关键字拼写、关键字出现顺序、引号匹配等是否正确，生成得到一棵语法分析树。

（4）由预处理器检验第（3）步语法树的合法性。通过对树中各节点进行逻辑判断，以生成新的解析树，但树的结构保持不变。如出现所需数据库对象不存在，或别名重名等语义错误将报告反馈。

（5）由查询优化器进行SQL优化，包括逻辑和物理优化。逻辑优化以关系代数为基础，对语法分析树的节点调整后生成关系代数语法树。物理优化以选取最小代价为原则，进一步对查询的连接顺序、扫描方式、连接算法等进行评估与调整，最终得到查询树，即查询执行计划。

（6）执行器根据优化器生成的执行计划，调用合适的存储引擎API，比如InnoDB、MyISAM等，完成查询的执行并返回结果。

简单执行过程如图1所示。其中每个阶段的输入均为下一阶段的输出。

图1 MySQL查询执行过程

2 查询效率影响因素

一条SQL语句可以被解析成多种不同的执行策略，MySQL查询优化器从查询成本的角度出发，计算并判断包括CPU利用率、I/O等待时间、网络传输等在内的查询总开销是否最低，最终选择执行一个最佳的执行计划。尽管查询优化器通过结合数据库系统的配置参数，数据字典等信息实现自动优化，但是DBA不应该仅仅依赖于查询优化模块，特别是在目标数据量较大的情况下，如在系统设计过程中忽视了下列因素都有可能影响到查询性能，导致查询响应变慢。

（1）没有为数据表关键列创建索引，或是在WHERE、HAVING、ON及ORDER BY子句中没有用到索引列。这将导致查询引擎无法利用索引，被迫执行全表扫描，增加了磁盘的I/O开销。

（2）查询结果集中包含了多余的数据行或数据列，对非必要数据的遍历会造成访问响应时间的延迟。

（3）在检索条件中使用了可能引起全表扫描的操作符，比如<>或!=、or、in、not等，或是将属性列与空值nul l进行判断，导致无法使用索引扫描[2]。

（4）表设计时使用了不合适的数据类型，造成存储空间的浪费，很显然查询相对较小字段内的数据速度会更快[3]。尤其是字符类数据，使用var char/nvar char便优于使用char/nchar类型。

（5）在检索条件中对不兼容的数据类型进行匹配，导致在后续的查询优化器阶段无法完成进一步优化操作。

（6）在检索条件中使用了局部变量或是对属性列进行函数操作时，都将导致可用索引失效而进行全表扫描。

（7）多表查询时，数据表连接顺序不合理。不同的连接顺序生成的中间关系也各不相同，因此CPU和IO开销也有所不同。

3 优化方法

3.1 创建有效索引

索引优化属于物理查询优化技术手段。索引是一张存储有索引列值及该值所在行的存储位置的简单物理表格。使得数据库应用程序能像书的目录为读者提供快速找到想看的内容一样，不必扫描整个表而找到想要的数据。如果在搜索条件列上存在索引，那么当表数据量大时，借助索引扫描优于全表顺序扫描。在数据库设计阶段，可遵循下列规则创建索引。

（1）分别为主、外键属性列创建索引；

（2）为经常出现在检索条件中的属性列创建索引；

（3）为经常出现在or der by后需要排序的属性列创建索引。

由于创建与维护索引有时间与存储空间的消耗[4]，特别是聚集索引，在更新表数据时，会进行动态的维护，同步完成数据的重新排列。因此，不要在非必须的数据列上创建索引，特别是一张经常插入、更新、删除记录的表。

如有测试数据表t 1，由3列整型数据构成，包含10万条数据，使用语句SELECT c1,c2,c3 FROM t 1 WHERE c1=50001;查询第50001条记录。未使用索引时查询用时0.06秒，在查询条件c1上建立主键索引后，查询时间降至0.00秒，查询速度得到了显著的提升。如图2所示。

图2 查询结果对比

3.2SQL语句优化

SQL语句优化属于逻辑查询优化技术手段。以关系代数为理论基础，依据查询重写规则，完成对SQL语句的等价转换。注意在SQL编写中要能够充分利用索引，避免出现因语句不合理使得系统无法引用索引的情况[5]。

（1）等价运算符转换

在MySQL数据库中某些运算符如LIKE、BE‐TWEEN…AND、IN等不支持索引扫描，如果在条件判断列上存在索引，可运用等价规则重写该语句。转换规则如表1所示。

表1 等价运算符转换规则

（2）条件表达式化简

可利用等式或不等式性质对查询条件进行化简，化简规则如表2所示。

表2 条件表达式化简规则

（3）子查询消除

对于没有分组或排序等复杂格式的SQL语句可以实现子查询展开处理，即将子查询重写为等价的多表连接语句。这样做的好处是将子查询的连接条件和过滤条件上拉至父查询，消除内部查询语句的层次，减少子查询的执行次数。有利于优化器做进一步优化，查询效率可能会是数量级的提高。子查询消除需要满足外层查询与内层查询的结果没有重复记录行。如有下列子查询语句：

SELECT*FROM t 1 WHERE id=ANY(SELECT id FROM t 2 WHERE id=10);

可重写为：

SELECT*FROM t 1,t 2 WHERE t 1.id=t2.id AND t 2.id=10;

（4）内连接优化

如有测试数据表t1和t 2，对它们进行内连接操 作，表 示 为σcondition1×conditon2(t1×t2)，其 中 条 件condition1只作用在t1表，条件condit ion2只作用在t 2表，可将条件下推至对应的关系上，通过先完成选择再进行连接操作，以减少中间元组的记录数目。满足如下等式。

σcondition1×conditon2(t1×t2)=σcondition1(t1)×σcondition2(t2)

如果仅条件condit ion1作用在t 1表，条件condition2作用在连接结果上，则条件下推后可转换为如下等式。

σcondition1×conditon2(t1×t2)=σcondition2(σcondition1(t1)×t2)

（5）外连接消除

外连接语句的执行时间往往高于内连接。由于外连接中左右表的顺序必须保持不变，因而限制了查询优化器阶段的优化方式。外连接优化的思路是将其转换为等价内连接，这样优化器便可更加灵活地选择表的连接顺序，加快查询执行的速度。

在外连接查询结果集中允许出现不匹配的数据行，由空值NULL来表示。当WHERE条件可以确保结果集中不存在值为NULL的数据行时，即在语义上等同于内连接。如有下列左外连接语句：

SELECT*FROM t 1 LEFT JOIN t 2 ON t 1.id=t 2.id WHERE t 1.id IS NOT NULL;

可重写为：

SELECT*FROM t 1 INNER JOIN t 2 ON t 1.?

id=t 2.id;

或

SELECT*FROM t 1,t 2 WHERE t 1.id=t2.id;

（6）嵌套连接优化

在执行多表连接操作时，连接表达式可能存在嵌套，即有括号限制了数据表的连接顺序。如果连接形式只包括内连接，可直接将括号省略，这样做并不会影响原来的语义。如有下列嵌套连接语句：

SELECT*FROM t 1 JOIN(t 2 JOIN t 3 ON t 2.id=t 3.id)ON t 1.id=t 2.id WHERE t 1.id>10;

可重写为：

SELECT*FROM t 1 JOIN t 2 ON t 1.id=t 2.id JOIN t 3 ON t 2.id=t 3.id WHERE t 1.id>10;

（7）DISTINCT优化

DISTINCT关键字的作用是去除重复记录。在查询处理完SQL列表后会对最终结果集完成一次排序，产生较高的排序成本。因此数据量大时尽量避免使用。如果是在主健列或是唯一列上执行DISTINCT操作，可直接删除DISTINCT。

3.3 合理利用MySQL查询缓存

MySQL查询缓存Quer y Cache是一种有效的查询重用优化技术。该技术能够保存已分析并执行的查询语句的完整结果。当相同的查询语句再次提交后，MySQL会首先从查询缓存中检索结果，如有命中，便会直接返回查询结果，省略后续的解析、优化与执行阶段。

可以使用“show var iabl es l ike'%que‐r y_cache%';”查看查询缓存参数设置情况。参数信息如图3所示，参数含义如表3所示。

图3 查看查询缓存情况

表3 quer y_cache参数及含义

Quer y Cache会产生Hash计算，在检查是否命中缓存时也有一定的资源消耗。如果表中数据，或是表结构频繁地被修改，则会造成查询缓存失效。因此，必须合理利用MySQL查询缓存。根据应用程序的需求，正确设置相关参数，并在需要执行大量相同的，且结果数据不常更新的查询语句时使用。

4 结语

MySQL数据库查询性能优化的目标是要减少SQL语句执行的响应时间。查询性能的提升除了借助MySQL自身提供的优化机置之外，实践证明建立适当的索引，并通过高效的SQL语句充分引用索引能够得到较好的执行效率。