首用MYSQL索引

作者: Dnirta  出自:

MYSQL百万记录效率问题解决了心情舒畅，特上来讲一篇为初学者作贡献。

用了MYSQL一年多，且一直被MYSQL的快速查询所吸引，可今天怎么碰石头了？

原来就是索引在做怪。由于本人是业余爱好自学PHP+MYSQL开发，所以在理论优化方面一般考虑甚少，基本不用字段索引去做MYSQL优化，可能也是我不会用吧。（总看书上说索引的好处好处的，今天着实尝到甜头了。）

讲讲经过：

根据程序逻辑需要，我得用PHP反复去操作这个百万条数据表3-4次，其中有select和update(update的时候需要去生成一个文本文件)，delete（delete的时候需要去把这个文件删除），但把程序逻辑封装，测试程序时，操作后确实让人心痛了，甚至有准备上一个SQL SERVER去测试效率问题的念头。最后经技术总监点了一下，说让把程序每段加个执行时间再看看。我照做，在update这一块去进行测试，发现问题并不是在这个百万条数据表上，我就把另一段生成文件文本文件的程序拿掉，测试一切正常，一次update300条记录时间在0.08秒左右就操作完了。本以为问题是出在生成文本文件上，且在SQL语句上不存在问题，我想即是与MYSQL无关，就把另一段写文件文件的操作拿掉了先。

接着我再测试delete部分是不是也存在着这个问题，最后我按上面的步骤同样也把删除文件的一部分程序拿掉了先，再看测试时间，发现问题依然存在，郁闷了一会，继续测试这段代码。这段代码是一个表关联操作（先查询后删除），测试发现这个关联操作花了7秒钟时间。我想，一个一个拆开试，其中我又拆分测试，把删除的SQL语句部分拿掉，再看查询部分的时间测试，结果仅查询部分花去了5秒。我就发现问题肯定是出在了查询语句上了。

我敢肯定问题出在查询语句上了后，我就直接到mysql-front上去做个查询测试，在这个百万条记录里去查询一条符合条件的记录试试。结果不出所料，查询结果是一条记录，却花了4.76秒时间。我心想：不是吧，这就是传说中的快速查询的MYSQL？？我开始回忆了，以前看书的时候MYSQL优化等内容。突然想到索引？立刻为一个经常需要where的字段建立了一个索引，然后同样的SQL语句再做测试，这次仅只花了0.04秒。顿时头脑清醒了一会，罚自己脸红了3分钟。

由于我的程序一般都上千条记录，就没有为查询效率问题考虑太多，所以我的确也不知道索引的好处，也一直都是不屑于用索引的，但这次我一试，我发现我错了，我真的错了，错得非常厉害，索引这么好的功能却一直被我抛之脑后。

讲讲索引后的探索：

我发现我在建立索引的时候，竟然花了10几秒钟，我心想，一个索引还不至于吧，建立一个索引MYSQL服务器的响应时间怎么这么长？我就又跟着我的好奇心想了想，是不是由于记录数多了的原因？难道建立索引是把所有记录的这个字段都写到某个地方了？想到这里，我的鼠标非常灵活的帮我进入到了MYSQL 数据文件夹（data）里面相对应的库相对应的这个百万条记录的表的三个文件其中有（xx.MYI,xx.MYD,xx.FRM），以前我知道其中有一个是放数据的，另外两个其中有一个是表的结构，还有一个确实不知道是什么，因为大小也一直和表结构文件一样都是40K左右。但今天我知道了，是放表索引的。我刚才建立了一个百万条记录索引后这个文件一下涨到了11M。

再想想MYSQL表文件扩展名：

frm是不是就是frame(结构)；
MYI,其中的I是不是就是index(索引)；
，其中的D是不是就是data(数据)；

我想没有比这更好的解释了吧？

最后根据以上分析后，再一次偶然的操作发现，update比原来慢了至少一倍。所以说天下还真没有免费的午餐啊。不过相比于select来说，update的效率损失一点完全可以忽略不计，因为select的效率可提高了将近100倍啊。