MyISAM表的缓存_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MyISAM表的缓存_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:35 PM

friend

bitsCN.com

MyISAM表的缓存

前言：

今天一个朋友问了一个问题，原文如下：mysql5.1，myisam的表，select count(*) as total FROM m_bff WHERE from_uid='73149293' AND isdeleted=0。from_uid上有索引，第一次执行这个句子速度慢，1秒多，用show profile看都慢在Sending data上。但是紧接着我加上sql_no_cache，执行只需要0.01秒了，有好多句子都是类似的情况第一次慢，后来加上sql_no_cache也不慢，Key_blocks_unused也很多，请教下这种是什么原因呢？

问题总结一下就是：对于MyISAM表的查询，为何第一次会比第二次慢非常多？

回想innodb和MyISAM的区别，其中非常重要的一个就是MyISAM只缓存索引内容，而innodb不仅缓存索引还缓存数据。那照理说MyISAM每次的数据查询都应该是Disk Access不会有很大的速度差距吧。

其实在MyISAM的文件和操作系统之间还有一层OS级别的文件缓存。猜测就是OS文件缓存捣的鬼。

验证：

验证方式其实很简单，在shell下以root权限运行 cat 3 > /proc/sys/vm/drop_caches 把OS的文件缓存清空，再运行SQL看看是不是和第一次运行的速度一样就可以知道了。

结果：

恩，echo 3 /proc/sys/vm/drop_caches 之后就又慢了，看来就是你说的问题了

引申：

知道了MyISAM这个特性后，可以用文件预读的方法来做数据预热。

假设某个表是热点表，且操作系统又有较多的内存空余。我们可以用cat table.MYD >> /dev/null 的方式来把文件加载到OS文件缓存中。这样当某些用户的数据第一次访问时就不会出现慢查询了

bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

What Are the Limitations of Using Views in MySQL?May 14, 2025 am 12:10 AM

MySQLviewshavelimitations:1)Theydon'tsupportallSQLoperations,restrictingdatamanipulationthroughviewswithjoinsorsubqueries.2)Theycanimpactperformance,especiallywithcomplexqueriesorlargedatasets.3)Viewsdon'tstoredata,potentiallyleadingtooutdatedinforma

Securing Your MySQL Database: Adding Users and Granting PrivilegesMay 14, 2025 am 12:09 AM

ProperusermanagementinMySQLiscrucialforenhancingsecurityandensuringefficientdatabaseoperation.1)UseCREATEUSERtoaddusers,specifyingconnectionsourcewith@'localhost'or@'%'.2)GrantspecificprivilegeswithGRANT,usingleastprivilegeprincipletominimizerisks.3)

What Factors Influence the Number of Triggers I Can Use in MySQL?May 14, 2025 am 12:08 AM

MySQLdoesn'timposeahardlimitontriggers,butpracticalfactorsdeterminetheireffectiveuse:1)Serverconfigurationimpactstriggermanagement;2)Complextriggersincreasesystemload;3)Largertablesslowtriggerperformance;4)Highconcurrencycancausetriggercontention;5)M

MySQL: Is it safe to store BLOB?May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

MySQL: Adding a user through a PHP web interfaceMay 14, 2025 am 12:04 AM

Adding MySQL users through the PHP web interface can use MySQLi extensions. The steps are as follows: 1. Connect to the MySQL database and use the MySQLi extension. 2. Create a user, use the CREATEUSER statement, and use the PASSWORD() function to encrypt the password. 3. Prevent SQL injection and use the mysqli_real_escape_string() function to process user input. 4. Assign permissions to new users and use the GRANT statement.

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

See all articles