mysql内部机制与sql优化

《mysql内部机制与sql优化》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、MySQL优化齐萌影响MYSQL的主要因素磁盘IO：主要是查询扫描的行数内存：keycache、Qcache、临时表、内存表等CPU：groupby、orderby等运算网络连接数：通常表被锁定时，连接数瞬间冲到峰值如何定位vmstat,iostat,top等系统级别的工具explainslowqueryshowstatus/showprocesslist/showengineinnodbstatus其他，如mysqlreport，profiling怎样解决？硬件、网络、软件MySQL参数设置应用程序、架构优化查询优化、索引1、了解MYS

2、QL结构与执行机制（MyISAM存储结构、锁、索引）1.1MyISAM存储结构1.2MyISAM索引MyISAM的索引不论是PrimaryKey还是普通Index，存储结构都基本一样，基本结构都是BalanceTree（简称为B-Tree），所有的键值详细信息和行“指针”信息都存放于B-Tree的LeafNodes上面。由于MyISAM存储引擎中数据行的存储分为固定长度和动态长度两种，所以在MyISAM存储引擎的数据文件中定位一行数据所需要信息也存在两种方式：一种是直接通过行号（rownumber）来定位固定长度表数据的行；一种是通过其

3、他一些相对的文件位置标识信息来定位动态长度表数据的行RID（RowID）。1.2.1MyISAM索引结构B-tree1.2.2.1MyISAM如何使用KeyCache当MySQL请求(读或写)MyISAM索引文件中某个IndexBlock时，首先会看KeyCache队列中是否已经缓存了对应block。如果有，就直接在KeyCache队列中进行读写了，不再需要请求磁盘。如果是写请求，那么KeyCache中的对应Block就会被标记为Dirty(和磁盘不一致)。在MyISAM在KeyCache成功请求(读写)某个Block后，会将该Block

4、放到KeyCache队列的头部。如果KeyCache中没有待请求(读或写)的Block，MyISAM会向磁盘请求对应的Block，并将其放到KeyCache的队列头部。队列如果满了，会将队列尾部的Block删除，该Block如果是Dirty的，会将其Flush到磁盘上。我们看到MyISAM维护了一个LRU(LeastRecentlyUsed)的KeyCache队列。队列中的DirtyBlock会在Block被踢出队列时Flush到磁盘上。1.2.2.2MyISAMKeyCache原理图1.2.3多列索引(classid_type_date

5、)中索引字段查找顺序classidtypedate升序情况102009-02-112010-01-152010-01-1712009-03-122010-01-1622009-04-202010-03-05502009-05-122010-05-1112009-01-232010-04-25A.SELECT*FROMcmsWHEREclassid=1ANDtype=2ORDERBYdateLIMIT10B.SELECT*FROMcmsWHEREclassid=1ANDtype>0ORDERBYdateLIMIT10C.SELECT*FRO

6、McmsWHEREclassid=1ANDtypein(0,2)ORDERBYdateLIMIT10通过上表不难发现，如果执行A语句时通过查找索引返回的已经是有序的了，但B,C需要额外进行一次数据扫描并对数据结果重新进行排序，其效率和通过where条件所筛选的行数的多少有很大关系MYSQL查询优化器1.3MySQL锁分类·表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。·行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。·页面锁：开销和加锁时间界于表锁和行锁之间；会出

7、现死锁；锁定粒度界于表锁和行锁之间，并发度一般。1.3.1MyISAM表锁及兼容性MyISAM存储引擎只支持表锁，分为：表共享读锁（TableReadLock）和表独占写锁（TableWriteLock）。MySQL中的表锁兼容性1.3.23个线程执行SQL各时序状态时间线程1线程1状态线程2线程2线程3线程3状态发送select语句发送select1无1设置读锁执行select释放读锁query、Sendingdata等设置读锁执行select1释放读锁query、Sendingdata等无Sleep发送update发送select2发

8、送select32设置写锁、执行、释放写锁等待获得锁LOCK等待获得锁LOCK3Sleep设置读锁、执行select2、释放读锁query、Sendingdata等设置读锁执行select3（