Mysql应用Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE
《Mysql应用Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE》要点: 场景MYSQL学习 产品中有一张图片表pics,数据量将近100万条,有一条相关的查询语句,由于执行频次较高,想针对此语句进行优化MYSQL学习 表结构很简单,主要字段:MYSQL学习 代码如下: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 MYSQL学习 使用了user_id的索引,并且是const常数查找,表示性能已经很好了MYSQL学习 优化后MYSQL学习 因为这个语句太简单,sql自己没有什么优化空间,就考虑了索引MYSQL学习 修改索引结构,建立一个(user_id,picname,smallimg)的联合索引:uid_picMYSQL学习 重新执行10次,平均耗时降到了30ms左右MYSQL学习 使用explain进行分析MYSQL学习 MYSQL学习看到使用的索引变成了刚刚建立的联合索引,并且Extra部分显示使用了'Using Index'MYSQL学习 总结MYSQL学习 ‘Using Index'的意思是“覆盖索引”,它是使上面sql性能提升的关键MYSQL学习 一个包括查询所需字段的索引称为“覆盖索引”MYSQL学习 MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后进行回表操作,减少IO,提高了效率MYSQL学习 例如上面的sql,查询条件是user_id,可以使用联合索引,要查询的字段是picname smallimg,这两个字段也在联合索引中,这就实现了“覆盖索引”,可以根据这个联合索引一次性完成查询工作,所以提升了性能.MYSQL学习 扩展研究MYSQL学习 一、Mysql缓存,SQL_NO_CACHE和SQL_CACHE 的区别MYSQL学习 上边在进行测试的时候,为了防止读取缓存造成对实验结果的影响使用到了SQL_NO_CACHE这个功能,对于SQL_NO_CACHE的介绍官网如下:MYSQL学习 代码如下: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. 当我们想用SQL_NO_CACHE来禁止结果缓存时发现结果和我们的预期不一样,查询执行的结果仍然是缓存后的结果.其实,SQL_NO_CACHE的真正作用是禁止缓存查询结果,但并不意味着cache不作为结果返回给query.MYSQL学习 在说白点就是,不是本次查询不使用缓存,而是本次查询结果不做为下次查询的缓存.MYSQL学习 还有就是,mysql本身是有对sql语句缓存的机制的,合理设置我们的mysql缓存可以降低数据库的io资源,因此,这里我们有必要再看一下如何控制这个比较安适的功能.MYSQL学习 看图如下:MYSQL学习 MYSQL学习其中各项的含义为:MYSQL学习 1、have_query_cache 2、query_cache_limit 3、query_cache_min_res_unit 4、query_cache_size 5、query_cache_type 1、完整查询的过程如下MYSQL学习 当查询进行的时候,Mysql把查询结果保留在qurey cache中,但是有时候要保留的结果比较大,超过了query_cache_min_res_unit的值,这时候mysql将一边检索结果,一边进行慢慢保留结果,所以,有时候并不是把所有结果全部得到后再进行一次性保留,而是每次分配一块query_cache_min_res_unit 大小的内存空间保留结果集,使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,mysql要进行多次内存分配的操作,而我们应该知道,频繁操作内存都是要耗费时间的.MYSQL学习 2、内存碎片的产生MYSQL学习 当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重复利用.比如,第一次分配4KB,只用了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的1KB+2KB=3KB,不足以做个一个内存单元分配,这时候,内存碎片便产生了.MYSQL学习 3.内存块的概念MYSQL学习 先看下这个:MYSQL学习 MYSQL学习Qcache_total_blocks 表示所有的块MYSQL学习 Qcache_free_blocks 表示未使用的块 Qcache_free_memory 表示查询缓存区现在还有多少的可用内存 优化提示:MYSQL学习 如果Qcache_lowmem_prunes 值比较大,表示查询缓存区大小设置太小,需要增大. 关于query_cache_min_res_unit大小的调优,书中给出了一个计算公式,可以供调优设置参考: query_cache_min_res_unit = (query_cache_size - Qcache_free_memory) /Qcache_queries_in_cache 还要注意一点的是,FLUSH QUERY CACHE 命令可以用来整理查询缓存区的碎片,改善内存使用状况,但不会清理查询缓存区的内容,这个要和RESET QUERY CACHE相区别,不要混淆,后者才是清除查询缓存区中的所有的内容. 可以在 SELECT 语句中指定查询缓存的选项,对于那些肯定要实时的从表中获取数据的查询,或者对于那些一天只执行一次的查询,我们都可以指定不进行查询缓存,使用 SQL_NO_CACHE 选项. 对于那些变化不频繁的表,查询操作很固定,我们可以将该查询操作缓存起来,这样每次执行的时候不实际拜访表和执行查询,只是从缓存获得结果,可以有效地改善查询的性能,使用 SQL_CACHE 选项. 下面是使用 SQL_NO_CACHE 和 SQL_CACHE 的例子: 代码如下: mysql> select sql_no_cache id,name from test3 where id < 2; mysql> select sql_cache id,name from test3 where id < 2; 注意:查询缓存的使用还需要配合相应得服务器参数的设置. 二、覆盖索引(偷懒整理一下,来自百度百科)MYSQL学习 理解方式一:就是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖. 作用:MYSQL学习 如果你想要通过索引覆盖select多列,那么需要给需要的列建立一个多列索引,当然如果带查询条件,where条件要求满足最左前缀原则.MYSQL学习 Innodb的辅助索引叶子节点包括的是主键列,所以主键一定是被索引覆盖的.MYSQL学习 (1)例如,在sakila的inventory表中,有一个组合索引(store_id,film_id),对于只需要拜访这两列的查 询,MySQL就可以使用索引,如下: mysql> EXPLAIN SELECT store_id,film_id FROM sakila.inventoryG (2)再比如说在文章系统里分页显示的时候,一般的查询是这样的: 代码如下: SELECT id,title,content FROM article ORDER BY created DESC LIMIT 10000,10; 通常这样的查询会把索引建在created字段(其中id是主键),不过当LIMIT偏移很大时,查询效率仍然很低,改变一下查询: 代码如下: SELECT id,content FROM article INNER JOIN ( SELECT id FROM article ORDER BY created DESC LIMIT 10000,10 ) AS page USING(id) 此时,建立复合索引”created,id”(只要建立created索引就可以吧,Innodb是会在辅助索引里面存储主键值的),就可以在子查询里利用上Covering Index,快速定位id,查询效率嗷嗷的MYSQL学习 注:本文是参考《Mysql性能优化案例 - 覆盖索引》 的一篇文章借题发挥,参考了原文的知识点,本身做了一点的发挥和研究,原文被多次转载,不知作者何许人也,也不知出处在哪个,如需原文请自行搜索.MYSQL学习 编程之家PHP培训学院每天发布《Mysql应用Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE》等实战技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |