基于MySQL查询优化技术研究.docVIP

基于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