搜尋
首頁資料庫mysql教程Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE_MySQL

场景

产品中有一张图片表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'

总结

代码如下:


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本身是有对sql语句缓存的机制的,合理设置我们的mysql缓存可以降低数据库的io资源,因此,这里我们有必要再看一下如何控制这个比较安逸的功能。

看图如下:

其中各项的含义为:

1、have_query_cache
是否支持查询缓存区 “YES”表是支持查询缓存区

2、query_cache_limit
可缓存的Select查询结果的最大值 1048576 byte /1024 = 1024kB 即最大可缓存的select查询结果必须小于 1024KB

3、query_cache_min_res_unit
每次给query cache结果分配内存的大小 默认是 4096 byte 也即 4kB

4、query_cache_size
如果你希望禁用查询缓存,设置 query_cache_size=0。禁用了查询缓存,将没有明显的开销

5、query_cache_type
查询缓存的方式(默认是 ON)

1、完整查询的过程如下

当查询进行的时候,Mysql把查询结果保存在qurey cache中,但是有时候要保存的结果比较大,超过了query_cache_min_res_unit的值 ,这时候mysql将一边检索结果,一边进行慢慢保存结果,所以,有时候并不是把所有结果全部得到后再进行一次性保存,而是每次分配一块query_cache_min_res_unit 大小的内存空间保存结果集,使用完后,接着再分配一个这样的块,如果还不不够,接着再分配一个块,依此类推,也就是说,有可能在一次查询中,mysql要进行多次内存分配的操作,而我们应该知道,频繁操作内存都是要耗费时间的。

2、内存碎片的产生

当一块分配的内存没有完全使用时,MySQL会把这块内存Trim掉,把没有使用的那部分归还以重复利用。比如,第一次分配4KB,只用了3KB,剩1KB,第二次连续操作,分配4KB,用了2KB,剩2KB,这两次连续操作共剩下的1KB+2KB=3KB,不足以做个一个内存单元分配,这时候,内存碎片便产生了。

3.内存块的概念

先看下这个:

Qcache_total_blocks 表示所有的块

Qcache_free_blocks 表示未使用的块
这个值比较大,那意味着,内存碎片比较多,用flush query cache清理后,为被使用的块其值应该为1或0 ,因为这时候所有的内存都做为一个连续的快在一起了.

Qcache_free_memory 表示查询缓存区现在还有多少的可用内存
Qcache_hits 表示查询缓存区的命中个数,也就是直接从查询缓存区作出响应处理的查询个数
Qcache_inserts 表示查询缓存区此前总过缓存过多少条查询命令的结果
Qcache_lowmem_prunes 表示查询缓存区已满而从其中溢出和删除的查询结果的个数
Qcache_not_cached 表示没有进入查询缓存区的查询命令个数
Qcache_queries_in_cache 查询缓存区当前缓存着多少条查询命令的结果

优化提示:

如果Qcache_lowmem_prunes 值比较大,表示查询缓存区大小设置太小,需要增大。
如果Qcache_free_blocks 较多,表示内存碎片较多,需要清理,flush query cache

关于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 mysql> select sql_cache id,name from test3 where id


注意:查询缓存的使用还需要配合相应得服务器参数的设置。

 

二、覆盖索引(偷懒整理一下,来自百度百科)

理解方式一:就是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。
理解方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
理解方式三:是非聚集复合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段,也即,索引包含了查询正在查找的数据)。

作用:

如果你想要通过索引覆盖select多列,那么需要给需要的列建立一个多列索引,当然如果带查询条件,where条件要求满足最左前缀原则。

Innodb的辅助索引叶子节点包含的是主键列,所以主键一定是被索引覆盖的。

(1)例如,在sakila的inventory表中,有一个组合索引(store_id,film_id),对于只需要访问这两列的查 询,MySQL就可以使用索引,如下:

代码如下:


mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G


(2)再比如说在文章系统里分页显示的时候,一般的查询是这样的:

代码如下:


SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;


通常这样的查询会把索引建在created字段(其中id是主键),不过当LIMIT偏移很大时,查询效率仍然很低,改变一下查询:

代码如下:


SELECT id, title, 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性能优化案例 - 覆盖索引》 的一篇文章借题发挥,参考了原文的知识点,自己做了一点的发挥和研究,原文被多次转载,不知作者何许人也,也不知出处在哪个,如需原文请自行搜索。

陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
如何优化MySQL连接速度?如何优化MySQL连接速度?Jun 29, 2023 pm 02:10 PM

如何优化MySQL连接速度?概述:MySQL是一种广泛使用的关系型数据库管理系统,常用于各种应用程序的数据存储和管理。在开发过程中,MySQL连接速度的优化对于提高应用程序的性能至关重要。本文将介绍一些优化MySQL连接速度的常用方法和技巧。目录:使用连接池调整连接参数优化网络设置使用索引和缓存避免长时间空闲连接配置合适的硬件资源总结正文:使用连接池

MySQL数据库备份与恢复性能优化的项目经验解析MySQL数据库备份与恢复性能优化的项目经验解析Nov 02, 2023 am 08:53 AM

在当前互联网时代,数据的重要性不言而喻。作为互联网应用的核心组成部分之一,数据库的备份与恢复工作显得尤为重要。然而,随着数据量的不断增大和业务需求的日益复杂,传统的数据库备份与恢复方案已无法满足现代应用的高可用和高性能要求。因此,对MySQL数据库备份与恢复性能进行优化成为一个亟需解决的问题。在实践过程中,我们采取了一系列的项目经验,有效提升了MySQL数据

如何用PHP的PDO类实现MySQL的性能优化如何用PHP的PDO类实现MySQL的性能优化May 10, 2023 pm 11:51 PM

随着互联网的飞速发展,MySQL数据库也成为了许多网站、应用程序甚至企业的核心数据存储技术。然而,随着数据量的不断增长和并发访问的急剧提高,MySQL的性能问题也愈发突显。而PHP的PDO类也因其高效稳定的性能被广泛运用于MySQL的开发和操作中。在本篇文章中,我们将介绍如何利用PDO类优化MySQL性能,提高数据库的响应速度和并发访问能力。一、PDO类介绍

如何通过使用动态SQL语句来提高MySQL性能如何通过使用动态SQL语句来提高MySQL性能May 11, 2023 am 09:28 AM

在现代应用程序中,MySQL数据库是一个常见的选择。然而,随着数据量的增长和业务需求的不断变化,MySQL性能可能会受到影响。为保持MySQL数据库的高性能,动态SQL语句已经成为了提高MySQL性能的一种重要技术手段。什么是动态SQL语句动态SQL语句是指在应用程序中由程序生成SQL语句的技术,通俗地说就是把SQL语句当成字符串来处理。对于大型的应用程序,

MySQL性能优化实战指南:深入理解B+树索引MySQL性能优化实战指南:深入理解B+树索引Jul 25, 2023 pm 08:02 PM

MySQL性能优化实战指南:深入理解B+树索引引言:MySQL作为开源的关系型数据库管理系统,被广泛应用于各个领域。然而,随着数据量的不断增加和查询需求的复杂化,MySQL的性能问题也越来越突出。其中,索引的设计和使用是影响MySQL性能的关键因素之一。本文将介绍B+树索引的原理,并以实际的代码示例展示如何优化MySQL的性能。一、B+树索引的原理B+树是一

MySQL性能优化:掌握TokuDB引擎的特点与优势MySQL性能优化:掌握TokuDB引擎的特点与优势Jul 25, 2023 pm 07:22 PM

MySQL性能优化:掌握TokuDB引擎的特点与优势引言:在大规模数据处理的应用中,MySQL数据库的性能优化是一个至关重要的任务。MySQL提供了多种引擎,每种引擎都有不同的特点和优势。本文将介绍TokuDB引擎的特点与优势,并提供一些代码示例,帮助读者更好地理解和应用TokuDB引擎。一、TokuDB引擎的特点TokuDB是一种高性能、高压缩率的存储引擎

如何通过垂直分区表来提高MySQL性能如何通过垂直分区表来提高MySQL性能May 10, 2023 pm 09:31 PM

随着互联网的高速发展,数据的规模不断扩大,对于数据库存储和查询效率的需求也越来越高。MySQL作为最常用的开源数据库,其性能优化一直是广大开发者关注的焦点。本文将介绍一种有效的MySQL性能优化技术——垂直分区表,并详细讲解如何实现和应用。一、什么是垂直分区表?垂直分区表是指将一张表根据列的特性进行分割,将不同的列存储在不同的物理存储设备上,从而提高查询效率

MySQL中的数据表大小管理技巧MySQL中的数据表大小管理技巧Jun 15, 2023 am 09:28 AM

MySQL数据库作为一种轻量级关系型数据库管理系统,被广泛应用于互联网应用和企业级系统中。在企业级应用中,随着数据量的增加,数据表的大小也不断增加,因此,对数据表大小进行有效地管理,对于保证数据库的性能和可靠性至关重要。本文将介绍MySQL中的数据表大小管理技巧。一、数据表划分随着数据量的不断增加,数据表的大小也不断增加,会导致数据库性能下降,查询操作变得缓

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序,非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具,幫助Web開發人員更好地理解保護網路應用程式的過程,並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞,難度各不相同。請注意,該軟體中

Atom編輯器mac版下載

Atom編輯器mac版下載

最受歡迎的的開源編輯器

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具

PhpStorm Mac 版本

PhpStorm Mac 版本

最新(2018.2.1 )專業的PHP整合開發工具

SecLists

SecLists

SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合,這些清單在安全評估過程中經常使用,而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表,幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上,他就可以存取所需的每種類型的清單。