[置顶] 查询速度提升300倍？MySQL的这个小技巧一定要掌握！

原创 IT168企业级2017-05-06 17:02

MySQL内置了慢查询日志，默认情况下慢查询日志是禁用的，首先你要打开my.cnf文件并将slow_query_log变量设置为“On”，这其中还有两个很重要的参数需要设置，long_query_time表示超过多少秒的查询就写入日志，而slow_query_log_file表示日志文件的保存路径。

如果你想要探究为什么查询会缓慢，可以使用MySQL提供的工具—EXPLAIN关键字。它适用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。

执行完上图语句，你可以获得一个数据访问的说明，包含了查询语句涉及到的每个表的每一行：

下图显示了表名、密钥以及执行查询期间扫描的行数。

从上图中我们可以看出它一共扫描了2000000张照片，其中每张照片都需要扫描20000个相册，所以实际上它对album表进行了40亿行的扫描，这个工作量其实是特别大的，所以你需要让它变得更有效率。

索引

索引可以显著提高查询性能。索引是什么呢，你可以简单的把它理解成地址簿中的地名，想要从中找到一个地名你可以有两种方法，一种是查找所有页面，另一种是利用字母选项卡，快速找到所需地名。

使用索引可以避免对不相关的表进行访问。如，你可以在picture.album_id上添加一个索引，如下所示：

现在，你在查询的时候就不会扫描整个picture表，首先它会扫描所有相册找到属于用户的相册，然后使用album_id快速找到图片。通过这种方式扫描行数可以减少到20万行，查询速度也比以前快了317倍。

你也可以同时为两个表添加索引：

这次album也不用全部扫描，直接利用user_id快速找出正确的相册，然后在使用album_id精确定位相关图片，每个表都实现了最佳性能，查询速度比之前快了380倍。

虽然上例中通过使用索引查询速度得到了显著提升，但是索引不能随意添加，因为索引写入数据库的时间很长，索引在数据读取时会让你受益良多，但是在写入时可能会造成数据丢失，所以只有能够实际提升读取性能的索引才允许被添加，使用EXPLAIN可以确认并删除查询中未使用的任何索引。