基于MySQL的查询优化技术

豆利

摘要：随着网络数据量的增大，用户对数据库查询的要求也越来越高，普通的查询有时很难满足要求，迫切需要对于MySQL语句实现优化，以提高查询效率。其中最常用的是创建索引。本文介绍了常用的SQL语句优化查询方式。

关键词：查询;优化;索引;SQL语句

中图分类号：TP311        文献标识码：A

文章编号：1009-3044（2021）15-0035-02

1查询速度慢的原因

数据库查询的首先是客户端发出查询请求，服务端接受请求，服务端处理后相关数据，再把结果返回给客户端，从而完成查询的过程。在整个查询过程中，涉及很多相关的参数，比如网络速度、内存容量、I/O吞吐率等等。其中查询的数据量比较大，查询语句没有设计好，查询语句没有优化好，返回不必要的行和列，产生死锁等，也常常是查询慢的原因。

2优化查询速度

对于MySQL查询优化最主要的是需要建立高性能的索引。索引对于查询达到良好的性能起到关键的作用，尤其是当数据表中的数据量越来越大，越来越多的时候，索引的作用就尤其重要，当然在数据量比较小的时候，不恰当的索引对于查询性能还不明显，当数据量增大的时候，性能就会急剧下降，效果较为显著。所以，就利用了索引，数据库的索引有助于加快查询速度。

为了对数据库索引速度进行验证，需要数据库及相应的数据表。

首先创建数据库educ，在educ数据库中创建表student，其创建代码如下：

CREATE DATABASE educ;--创建数据库

CREATE TABLE student（sid int， sname char（8），gender char（8），email varchar（56），depid char（10））;--创建数据表student

CREATE TABLE department（depid char（10），depName varchar（40），num int）;--创建数据表department

创建存储过程，实现批量数据的插入，代码如下：

DELIMITER $$         --声明存储过程的结束符为$$

CREATE PROCEDURE proc1（）    --创建存储过程

BEGIN

DECLARE i INT DEFAULT 1;

WHILE（i<=100000） DO

INSERT INTO student VALUES（i，Sophie，female，CONCAT（‘Sophie，i，@hffe.cn））;

SET i=i+1;

END WHILE;

END$$

DELIMITER;   ---重新声明MySQL的结束符为;

查看存储过程：SHOW CREATE PROCEDURE proc1;

调用存储过程：CALL proc1;可以实现批量插入数据。

（1）使用聚合函数查询出相应的结果，添加索引后验证执行效率

未创建索引时：SELECT MAX（sid） FROM student;

结果耗时为：1 row in set <0.02 sec>。

创建索引后 CREATE INDEX index_sid cON  student（sid）后，先使用命令SET QUERY CACHE清除缓存信息，重新执行SQL命令，结果耗时为：1 row in set <0.00 sec>。以下执行语句之前，首先用SET QUERY CACHE清除缓存信息，再重新执行命令。

（2）在分组（GROUP BY）、排序（ORDER BY）之后的字段进行添加索引。

未创建索引时：SELECT sid，COUNT（*） FROM student  GROUP BY sid;

结果耗时：5 row in set <0.03 sec>。

创建索引后：CREATE INDEX index_sid ON student（sid）;重新执行SQL命令，结果耗时：

5 row in set <0.02 sec>。

（3）多表连接优化

对于多表连接查询，不管几张表实现查询，都需要在连接字段上建立索引，以加快查詢速度。

未创建索引时：SELECT sid，sname，gender，depname from student inner join department on stuent.depid=department.depid;

结果耗时：51 row in set <0.02 sec>。

创建索引后： 51 row in set <0.00 sec>。

对于子查询中的IN子查询会扫描整张表，需使用EXISTS子查询代替使用，当然不是所有的索引都对查询起显著效果，MySQL是根据数据表中的数据进行优化的，当索引中有大量重复数据时，索引就失去了其显著的作用，假设在gender字段中值为male和female各占一半的时候，即使在gender上建立索引也不起作用。当然索引并不是越多越好，索引在加速查询的同时，也有其弊端。索引是以文件的形式存储的，索引文件需要占有磁盘空间。

若数据表中的索引很多的时候，查询会很浪费时间，索引会降低增加、删除、修改等相关数据操作，数据表中索引越多，索引更新的时间会越长。应尽量避免更新聚集索引数据列，因为聚集索引数据列的顺序和表的物理记录保持一致，一旦聚集索引的数据进行更新，将导致整张表的列值的改变，会增加很大的系统开销。复合索引在使用的时候，一般以索引的第一个列值作为条件查询，如果值相等，接着选择第二个字段列值进行排序，以此类推，否则建立的复合索引将不起作用。

如果索引定义的列值过长，这样会让索引变得很慢。通常以字段列值开始的部分的字符作为索引，可以大大节约索引空间，提高索引效率。其中前缀索引指的是把字段值的一部分作为索引，对于BLOB、TEXT、VARCHAR等类型必须使用前缀索引，毕竟索引需要存储空间，索引太长，维护起来也相对困难。

对于MySQL语句中WHERE中的子查询IN（），如希望搜索某个员工的工资情况，可以按照下面的方式实现查询：

msql>SELECT *FROM  employees

->WHERE  员工编号 IN（

->SELECT 员工编号 FROM salary WHERE 员工编号=2020002）;

对于IN子查询，我们一般认为，存储引擎是先执行里面的子查询，把查询的结果返回，作为外查询的条件，即外层查询后执行。但是实际情况不是这样的，MySQL会把外层表直接压入子查询中，MySQL认为这样执行的效率会更高，也就是说MySQL实际把查询改写了这样的形式：

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 员工编号=2020002

->AND employees.员工编号=salary.员工编号）;

这时子查询需要根据员工编号来关联外部表employees。通过explain 可以看出来，MySQL对employees表进行全表扫描，然后根据返回的员工编号逐一执行子查询。

如果外部表是个很小的表，结果可能不会引起注意，但是如果外层的表是一个非常大的表，那么这个in子查的性能就会比较糟糕。

当然我们还可以用下面的查询重新改写这个查询：

mysql->SELECT *FROM employees

->INNER JOIN salary USING（员工编号）

->WHERE 员工编号=‘2020002;

另一个优化办法是通过连接函数GROUP_CONCAT（）在IN（）子查询语句中构造一个由逗号分隔开来的列表序列，当然有的时候比使用关联写的效率更好些。一般情况下，不建议使用IN子查询，毕竟执行效率相对低些，建议使用EXISTS（）子查询获取更高的执行效率。下面是对于IN子查询改写的情况：

mysql->SELECT *FROM employees

->WHERE EXISTS（

->SELECT *FROM salary WHERE 员工编号=2020002

->AND employees.员工编号=salary.员工编号）;

（4）需要在经常搜索的条件WHERE中涉及查询的字段中添加索引

在查询的时候，尽量避免使用SELECT *FROM  abc;这样的语句，尽量不要使用*，需要使用具体的字段来显示结果，这样可以提高查询效率。在查询语句中，尽量避免在WHERE中使用空值进行判断，比如SELECT *FROM abc WHERE 备注 IS NULL，如果执行这个查询，就需要搜索abc整张表，可以修改为：把备注的空值利用默认值（DEFAULT）修改为0，即查询语句为：SELECT *FROM abc WHERE 备注=0。

在WHERE条件中避免使用！=或<>等操作符，否则的话，就会导致存储引擎扫描整张数据表。在WHERE子句中尽量避免使用OR进行连接条件，否则，也会导致存储引擎扫描整张数据表。例如：SELECT *FROM employees WHERE 姓名=章三 OR 姓名=张宏，可以使用UNION联合查询，改进代码如下：SELECT *FROM employees WHERE 姓名=章三UNION  SELECT

*FROM  employees WHERE 姓名=张宏。

在WHERE 条件查询中，对于模糊查找，会导致扫描整张数据表，例如：SELECT id  FROM employees WHERE 姓名 LIKE‘%adb%，若要考慮提高效率，可以用全文索引实现。

在SQL语句中也要避免局部变量的使用，会扫描整张数据表。因为局部变量的解析是在查询的时候才进行的。例如：SELECT num FROM  a WHERE name=@name 就可以进行转换为：SELECT num FROM a WITH（index（索引名字）） WHERE name=@name。

在查询中，避免在WHERE条件中使用函数，函数的使用也会扫描整张数据表，例如：SELECT sid FROM a WHERE SUBSTRING（名称，1，4）=abdc，字段名称的列值以abdc开头的sid应改写为：SELECT sid FROM a WHERE 名称 LIKE ‘abdc%，执行这样的操作，查询效率会相对提高些。

对于查询优化，最根本的是表的结构设计要合理，在设计表中字段时，若某些字段能使用数值型的话，就尽量避免使用字符类型，若该字段的列值中只含有数字信息，该字段的数据类型就不要设计为字符型，这样会降低查询和连接的性能，增加开销成本，这是因为存储引擎在处理查询连接时，会逐个字符进行比较，而对于数字型的，比较一次就足够了。

3结束语

索引是加快查询的重要方法，如果数据量很小，索引的作用不大，当数据量很大的时候，尤其涉及多个表连接时，索引的作用更大。当然索引也有弊端，就是占用磁盘空间，但这些弊端并不妨碍索引的应用，索引在数据库中通常是必不可少的。

参考文献：

[1] 周德伟，覃国蓉.MySQL数据库技术[M].2版.北京：高等教育出版社，2019.

[2] Baron Scbwartz，Peter Zaitsev，Vadim Tkacbenko，等.高性能MySQL[M]. 北京：电子工业出版社，2013.

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

[4] 武洪萍，马桂婷，等.MySQL数据库原理及应用[M].北京：人民邮电出版社，2014.

[5] 张士军，陆海伦，等. 索引在MySQL查询优化中的应用[J]. 计算机与数字工程，2007，35（1）：37-39，8.

[6] 王珊，萨师煊.数据库系统概论[M].4版.北京：高等教育出版社，2006.

【通联编辑：王力】