基于索引的MySQL数据库查询优化技术研究及在网站开发的应用

韩雪娇

摘 要：查询优化技术是网站设计与开发中重点考虑问题，本文深入研究MySQL数据库中的索引技术的优点和代价，以及在MySQL数据库中创建索引的方法。最后以中国钢管网为例，说明索引在本网站查询优化的实际应用。

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

1 查询优化的必要性

随着门户类网站及业务类网站的广泛应用，海量的数据需要存储以及查询。此时，数据库查询效率的优劣将直接影响网站的响应速度及用户体验[1]，甚至当数据库的数据量达到数千万条甚至更多时，就会出现数据库查询的瓶颈问题。因此，优化数据库的查询性能是十分必要的。而MySQL作为一种开源的轻量级数据库应用十分广泛[2]。当前MySQL查询优化技术主要有数据库配置参数优化、查询重用方法优化、索引技术等。

2 MySQL索引技术的优缺点

在MySQL中不带索引的表是一个无序数据行的集合。以tb_news表为例，如表1所示，若查找NewsID=3的新闻，需扫描表中的每个数据行并进行匹配，最终找到NewsID為3的新闻，共扫描1000条数据。

若在该表的NewsID数据列上建立索引，如表2所示。那么在查找NewsID=3的数据时将直接扫描索引找到NewsID为3的新闻。由此可见，索引的优点一是找到匹配的数据行并终止查询。优点二是使用定位算法查找第一条匹配的条目，而无需从索引的头部开始执行线性扫描，节省搜索时间。

当查询涉及多个表时，索引的作用更大。假设有3个包含1000条记录的数据表t1、t2和t3，每个表都包含数据列i1、i2和i3。执行查询SELECT t1.i1，t2.i2，t3.i3 FROM t1，t2，t3 WHERE t1.i1=t2.i2 AND t2.i1=t3.i3，结果为1000条有3个相等数值的记录。若不带索引扫描的数据量为1000×1000×1000。创建索引后的扫描过程为：首先选择表t1中的第一行并查看该数据行的值；然后使用表t2上的索引，直接定位到与t1的值匹配的数据行，表t3同理直接匹配数据行；最后处理表t1的下一行并重复前两步，直到表t1中的数据行被扫描完毕。表t1执行全扫描，而表t2和t3上执行索引查找，扫描数据量为1000，理论上查询效率提高了一百万倍。

虽然索引技术可提高查询效率，但也存在缺点。首先索引技术降低了增删改操作的执行速度。当执行增删改操作时需要更新数据表的索引。其次，索引技术会耗费存储数据库的磁盘空间。所以在MySQL数据表中并非创建的索引越多越好，一般仅在数据表的主键、排序、分组以及经常出现在WHERE和JOIN等子句中的数据列上创建索引。以中国钢管网（http：//www.cspa-cn.org.cn/）为例，在网站运营中发现index.jsp页面加载缓慢。开启浏览器的开发模式发现index_news.action请求花费了3.90s。分析该Action中执行的SQL查询发现SELECT x.* FROM xnews_info x WHERE x.newsxxlx='12' ORDER BY newsid DESC limit 0，9查询最为耗时。研究得出该网站新闻表数据庞大，而类别为12的新闻只有数条故需全表扫描。针对该问题，在主键newsid上建立唯一索引和外键newsxxlx建立普通索引。再次执行该SQL语句由原来的3.173ms缩减到0.003ms，大大缩短了index.jsp页面的加载时间。

3 结语

在MySQL数据库中采用索引技术将增加数据表的写入、刪除和更新操作负担和消耗数据库部分存储空间，但是恰如其分的创建索引可以显著提高查询效率。在网站的实际运营中也证实索引技术可缩短网页的响应时间，为用户提供更为流畅的视觉体验。

参考文献

[1]伍应树，赵志刚，李宪明.关系数据库基于索引查询的优化设计研究[J].电脑编程技巧与维护，2016，（17）：56-58.

[2]蔡代纯.空间数据库查询优化技术研究[J].山东工业技术，2015，（5）：169.

（作者单位：冶金工业信息中心）