基于MySQL的查询优化技术研究

王丽娟++靳继红

摘要：随着网络数据库应用的发展，数据库用户对数据查询的速度要求也在日益提高，当MySQL数据表的数据量达到百万级时，普通SQL查询语句的效率直线下降，因此如何提高SQL语句查询效率，显得十分重要。该文介绍了目前广泛使用的多种查询语句优化方法。

关键词：MySQL；索引；查询优化

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2017）30-0035-02

MySQL数据库是以“客户端/服务器”模式实现的，是一个多用户、多线程的小型数据库，MySQL因其稳定、可靠、快速、管理方便以及支持众多系统平台的特点，成为世界范围内最流行的开源数据库之一。随着网络数据库应用的日益发展，数据查询的速度也越来越成为整个应用的性能瓶颈了，也成为数据库用户和设计者都极其关心的问题，因此，为了提高数据库系统的性能，对查询进行优化是必不可少的。

为了测试查询速度，首先创建数据库mydb，在mydb数据库中创建两张表，一个department（部门表），一个employee（员工表），其中employee表采用MyISAM数据存储引擎，为了在批量插入数据时速度更快。

CREATE TABLE department （id INT，deptname VARCHAR（50））；

CREATE TABLE employee （id INT，deptid INT，ename VARCHAR（20）

）ENGINE=MYISAM；

定义存储过程p1（），并调用该存储过程将100001条记录批量插入employee表中。

DELIMITER // 将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号“；”，为了避免与存储过程中的SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕后再使用“DELIMITER ；”恢复默认结束符。

REATE PROCEDURE p1（）

BEGIN

DECLARE i INT DEFAULT 0；

WHILE （i <= 100000） DO

INSERT INTO empolyee （id，ename） VALUES （i，CONCAT（'aaa'，i））；

SET i = i + 1；

IF MOD（i，1000）=0 THEN COMMIT；

END IF；

END WHILE；

END；//

调用存储过程P1，批量插入数据记录。

CALL P1（）；

1 创建索引，优化查询速度

在数据库操作中，经常需要查找特定的数据，而创建索引是提高查询速度的很重要的一种手段。没有创建索引的数据表是一个无序的数据行集合，如果执行查询时，数据库必须从第1条记录开始遍历，直到找到特定的数据，如果数据表很大，而符合条件的记录只有少量的话，这样的效率就会显然非常低，而创建索引则可以优化查询，提高查询速度。

1） 在使用min（）或max（）函数查询时，创建索引后，可以很快找到数据表中的最小值或最大值，无需检索所有记录。

未创建索引耗时情况：

reset query cache；—清空查询缓存，以免影响测试结果，下同。

select min（id） from employee；

结果耗时：1 row in set（0.17 sec）。

創建索引（create index ix_id on employee （id））后，先使用reset query cache清空查询缓存，再重新执行上述SQL语句，结果耗时：1 row in set（0.00 sec）。

2） 在group by关键词和order by关键词后的字段一般也要创建索引，以提升查询速度（执行下面所有的SQL语句前，都是用reset query cache清空查询缓存，以确保测试结果的有效性）。

在deptid上没有创建索引的耗时情况：

Select deptid，count（*） from employee group by deptid； 4 rows in set（0.03 sec）。

创建索引（Create index ix_deptid on employee （deptid）；）后：再执行上述SQL语句的耗时情况：4 rows in set（0.02 sec）。

3） 在连接查询时，无论时几张表的连接查询，都要在子表的关联字段上创建索引，同样可以提升查询速度。

Select deptid，deptname，ename from department， employee where department.id=employee.deptid and deptid=3；

未加索引耗时统计：51 rows in set（0.01 sec），加索引后耗时统计：51 rows in set（0.00 sec）。

4） 经常出现在where子句中的字段，一般要创建索引，以提高数据查询的速度。

创建索引时，尽量使用长度短的数据类型，创建索引可以分为单列索引（即在表中单个字段上创建索引，可以是普通索引、唯一索引或全文索引）和多列索引（在表中多个字段上创建索引），如果创建的是多列索引，那么只有在查询条件中使用了这些字段组合的第一个字段时，该多列索引才会被使用。endprint

但是索引并不是越多越好，所以不能在数据表的每个列上都添加索引，因为索引在数据的查询优化中发挥着极其重要的作用，但是同时会降低数据更新（添加数据（insert）、修改数据（update）、删除数据（delete））的速度，同时创建索引会花费磁盘空间，并且创建的索引越多所花费的磁盘空间也越多。

在执行SQL语句时，可以使用EXPLAIN语句来查看索引是否被使用，从执行结果的possible_keys和key的值可以看出，如果都为某个字段，则说明索引存在并且已经开始被使用了。

尽量避免where子句中“=”的左边进行函数、算数运算或其他表达式的运算，否则系统将可能无法正确使用索引从而进行全表扫描。在每条SQL语句执行前，都用reset query cache；语句清空查询缓存，以确保测试结果有效。

1） 尽量避免在where子句中对字段进行表达式操作

Where子句中“=”的左边使用表达式的耗时统计：

select id from employee where id/10=500； 1 row in set（0.03 sec）。

Where子句中“=”的左边未使用表达式，但功能一样的SQL语句的耗时统计：

select id from employee where id=500*10；1 row in set（0.00 sec）。

2） 在where子句中尽量避免对字段进行函数操作，否则将会降低数据查询的速度。

在where子句中使用函数操作的耗时统计：

Select id from employee where substring（ename，1，3）=abc 2 rows in set（0.02 sec）。

实现同样的查询结果但在where子句中未使用函数操作的耗时统计：

Select id from employee where ename like ‘abc% rows in set（0.01 sec）。

1） 尽可能使用varchar、nvarchar数据类型代替char和nchar数据类型，因为变长字段的存储空间小，在节省存储空间的同时，也会加快搜索效率。

2） 尽量不使用select * from tablename，只返回具体用到的字段，用用到的字段列表代替*，不返回用不到的字段，以提高数据查询的速度。

3） 如果字段中只包含数字信息就尽量使用数字型字段，如果设计成字符型，将会降低查询和连接的性能，并且增加存储开销，因为数据存储引擎在处理查询和连接时会逐个比较字符串中的每一个字符，而对于数字型而言只需要比较一次就够了。

2 结束语

在数据库表中，索引是提高查询速度的一个关键因素，如果数据表中的数据记录很少，索引提升的查询速度并不是很明显，数据量越大，查询优化的性能越明显。如果数据库表中的数据量很大，那就要认真分析通过索引进行查询优化，并在写SQL语句时，要注意上述提到的查詢语句的优化方法。

参考文献：

[1] 韩兵，王照清，廖联军.基于MySQL多表分页查询优化技术[J].计算机系统应用，2016（8）.

[2] 钱文波，谢金宝.SQL数据库性能优化技术[J].微型机与应用，2009（18）.

[3] 孙辉.MySQL查询优化的研究与改进[D].武汉：华中科技大学，2007（5）.

[4] 吴沧舟，兰逸正，张辉.基于MySQL数据库的优化[J].电子科技，2013，26（9）.endprint