Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE

2024-07-24 12:45:08

字体：大中小

来源：转载

供稿：网友

场景

产品中有一张图片表pics，数据量将近100万条，有一条相关的查询语句，由于执行频次较高，想针对此语句进行优化

表结构很简单，主要字段：

复制代码代码如下:

user_id 用户ID
picname 图片名称
smallimg 小图名称

一个用户会有多条图片记录，现在有一个根据user_id建立的索引：uid，查询语句也很简单：取得某用户的图片集合：

复制代码代码如下:

select picname, smallimg from pics where user_id = xxx;

优化前

执行查询语句（为了查看真实执行时间，强制不使用缓存，为了防止在测试时因为读取了缓存造成对时间上的差别）

复制代码代码如下:

select SQL_NO_CACHE picname, smallimg from pics where user_id=17853;

执行了10次，平均耗时在40ms左右

使用explain进行分析：

复制代码代码如下:

explain select SQL_NO_CACHE picname, smallimg from pics where user_id=17853

使用了user_id的索引，并且是const常数查找，表示性能已经很好了

优化后

因为这个语句太简单，sql本身没有什么优化空间，就考虑了索引

修改索引结构，建立一个(user_id,picname,smallimg)的联合索引：uid_pic

重新执行10次，平均耗时降到了30ms左右

使用explain进行分析

看到使用的索引变成了刚刚建立的联合索引，并且Extra部分显示使用了'Using Index'

总结

‘Using Index'的意思是“覆盖索引”，它是使上面sql性能提升的关键

一个包含查询所需字段的索引称为“覆盖索引”

MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率

例如上面的sql，查询条件是user_id，可以使用联合索引，要查询的字段是picname smallimg，这两个字段也在联合索引中，这就实现了“覆盖索引”，可以根据这个联合索引一次性完成查询工作，所以提升了性能。

扩展研究

一、Mysql缓存，SQL_NO_CACHE和SQL_CACHE 的区别

上边在进行测试的时候，为了防止读取缓存造成对实验结果的影响使用到了SQL_NO_CACHE这个功能，对于SQL_NO_CACHE的介绍官网如下：

复制代码代码如下:

SQL_NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query.
You may use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect if you change the table, because this makes all cached queries invalid.

上一篇：MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法

下一篇：MySQL Workbench的使用方法(图文)

学习交流

硬盘分区如何设置准确的分区空间

硬盘分区如何设置准确的分区空间...

热门图片

猜你喜欢的新闻

猜你喜欢的关注