MySQL的预读机制带来的隐患：所谓的预读机制，就是当你从磁盘加载一个数据页的时候，可能会连带着把这个数据页相邻的其它数据页也加载到缓存里去。
例如，现在有两个空闲的缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓存里了，正好每个数据页放入一个空闲的缓存页。但是实际上只有一个缓存页被访问了，另外一个通过预读机制被加载进来的缓存页，其实并没有人访问，但是此时这两个缓存页都是放在LRU链表的前面。这个时候没有空闲的缓存页，如果要加载新的数据，就要把LRU链表队尾的缓存页刷入磁盘，而不是无人访问的那个缓存页。
会触发预读机制的场景：
为了解决简单的LRU链表的问题，MySQL在设计LRU链表的时候，实际上采取的是冷热数据分离的思想；之前的问题都是因为所有缓存页都混在了一个LRU链表中导致的。
真正的LRU链表，会被拆分成热数据和冷数据两个部分，冷热数据的比例是由innodb_old_blocks_pct参数控制的，默认是37，也就是说冷数据占比为37%。这个时候，LRU链表实际上看起来是下面这个样子的：
数据页第一次被加载到缓存的时候，其实是被放在冷数据链表的头部，后面1秒之后，如果你再次访问这个缓存页，那这个缓存页会被移动到热数据链表的头部，这个时间是有innodb_old_blocks_time这个参数控制的，默认1000ms。
也就是说，必须是一个数据页被加载到缓存页之后，在1s之后，你访问这个缓存页，他才会被挪动到热数据区域的链表头部去。
因为假设你加载了一个数据页到缓存去，然后过了1s之后你还访问了这个缓存页，说明你后续很可能会经常访问它，这个时间限制就是1s，因此只有1s后你访问了这个缓存页，他才会给你把缓存页放到热数据区域链表的头部去。
预读机制以及全表扫描加载进来的一大堆缓存页，他们会放在哪里？肯定是放在LRU链表的冷数据区域的前面，假设这个时候热数据区域已经有很多被频繁访问的缓存页了，你会发现热数据区域还是存放被频繁访问的缓存页的，只要热数据区域有缓存页被访问，他还是会被移动到热数据区域的链表头部去。
所以此时预读机制和全表扫描加载进来的一大堆缓存页，此时都在冷数据区域里，跟热数据区域里的频繁访问的缓存页，没有关系。
如果仅仅是全表扫描的查询，此时你肯定是在1s内就把一大堆缓存页加载进来，然后访问了这些缓存页一下，通常这些 *** 作1s内就结束了，所以基于目前的一个机制，可以确定的是，那些缓存页是不会从冷数据区域转移到热数据区域的。
除非在冷数据区域的缓存页，在1s之后还被访问了，那么此时他们就会判定为未来可能会被频繁访问的缓存页，然后移动到热数据区域的链表头部去。
假设此时缓存页不够用了，需要淘汰一些缓存页，此时会怎样？
直接就是可以找到LRU链表中的冷数据区域的尾部的缓存页，他们肯定是之前被加载进来的，而且加载进来1s过后都没人访问过，说明这个缓存页压根儿就没人愿意去访问他，他就是冷数据。所以此时就直接淘汰冷数据区域的尾部的缓存页，刷入磁盘就可以了。
之前提到如果一个缓存页被访问了，就会把他移动到LRU链表的热数据区域首位，这么频繁的移动会导致性能不是很好。所以MySQL对LRU链表热数据区域的访问规则做了优化，只有在热数据区域的后3/4部分的缓存页被访问了，才会被移动到链表头部；链表前面1/4的缓冲页被访问，是不会被移动的。
首先，并不是在缓存页满的时候，才会挑选LRU冷数据区域尾部的几个缓存页刷入磁盘，而是有一个后台线程，每隔一段时间就会把LRU链表的冷数据区域尾部的一些缓存页刷入磁盘，然后清空这几个缓存页，并把他们加入到free链表中。
所以大家会发现，只要有这个后台线程定时运行，可能你的缓存页都还没有用完呢，就给你把一批冷数据的缓存页刷入磁盘，清空出来一批缓存页，那么你就多了一批可以使用的空闲缓存页了。
如果仅仅只是把LRU链表中的冷数据区域的缓存页刷入磁盘，明显是不够的；
LRU链表中的热数据区域里的很多缓存页可能会被频繁的修改，这些数据不可能永远放在内存中，后台线程会在MySQL不繁忙的时候，把flush链表中的缓存页都刷入磁盘中，这样，被修改过的数据就被刷入到磁盘文件中了。
只要flush链表中的一些缓存页被刷入磁盘，那这些缓存页也会从flush链表和lru链表中移除，然后加入到free链表中。
所以，一边不停的加载数据到缓存页中，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。
另外一边，后台线程不停的把LRU链表的冷数据区域的缓存页及flush链表的缓存页刷入到磁盘，来清空缓存页，然后flush链表和LRU链表中的缓存页不停的在减少，free链表中的缓存页在不停的增加。
如果实在没有空闲的缓存页，那就会把LRU链表冷数据区域尾部的缓存页刷入磁盘，然后清空。
目前比较流行的编程语言：
1、Ruby
Ruby于1993年2月24日开始编写至1995年12月才正式公开发布，一种为简单快捷面向对象编程而创的脚本语言，Ruby是一个语法像Smalltalk一样完全面向对象、脚本执行、又有Perl强大的文字处理功能的编程语言。
2、Perl
Perl ，一般被称为“实用报表提取语言”（Practical Extraction and Report Language）。他于1987年12月18日发表。Perl借取了C、sed、awk、shell ing以及很多其他程序语言的特性。其中最重要的特性是它内部集成了正则表达式的功能，以及巨大的第三方代码库CPAN。简而言之，Perl像C一样强大，象awk、sed等脚本描述语言一样方便。
3、SWIFT
SWIFT，苹果于2014年WWDC（苹果开发者大会）发布的新开发语言，可与Object-C共同运行于MAC OS和iOS平台，用于搭建基于苹果平台的应用程序。
4、php
PHP，是英文超文本预处理语言Hypertext Preprocessor的缩写。PHP 是一种 HTML 内嵌式的语言，是一种在服务器端执行的嵌入HTML文档的脚本语言，语言的风格有类似于C语言，被广泛地运用，目前版本已经更新到5.4.23，由于其学习成本低，开发效率高，扩展函数丰富，成为了最流行的服务器端脚本语言。
5、Python
Python（KK 英语发音：/ˈpaɪθən/）是一种面向对象、直译式计算机程序设计语言。也是一种功能强大而完善的通用型语言，已经具有十多年的发展历史，成熟且稳定。Python 具有脚本语言中最丰富和强大的类库，足以支持绝大多数日常应用。 Python语法简捷而清晰，具有丰富和强大的类库。它常被昵称为胶水语言，它能够很轻松的把用其他语言制作的各种模块（尤其是C/C++）轻松地联结在一起。Python的名字来源于一个喜剧。也许最初设计Python这种语言的人并没有想到Python会在工业和科研上获得如此广泛的使用。
6、c#
C#是微软公司发布的一种面向对象的、运行于.NET Framework之上的高级程序设计语言。并定于在微软职业开发者论坛(PDC)上登台亮相。C#是微软公司研究员Anders Hejlsberg的最新成果。C#看起来与Java有着惊人的相似；它包括了诸如单一继承、接口、与Java几乎同样的语法和编译成中间代码再运行的过程。但是C#与Java有着明显的不同，它借鉴了Delphi的一个特点，与COM（组件对象模型）是直接集成的，而且它是微软公司 .NET windows网络框架的主角。
7、java
ava是一ava是一种由Netscape的Live发展而来的原型化继承的面向对象的动态类型的区分大小写的客户端脚本语言，主要目的是为了解决服务器终端语言，比如Perl，遗留的速度问题。当时服务端需要对数据进行验证，由于网络速度相当缓慢，只有28.8kbps，验证步骤浪费的时间太多。于是Netscape的浏览器Navigator加入了Java，提供了数据验证的基本功能。所以，Java 被数百万计的网页用来改进设计、验证表单、检测浏览器、创建cookies，以及更多的应用。
推荐下我的前端群：524262608，不管你是小白还是大牛，小编我都挺欢迎，不定期分享干货，包括我自己整理的一份2017最新的前端资料和零基础入门教程，欢迎初学和进阶中的小伙伴。
8、C++
C++是一种面向对象的计算机程序设计语言。C++这个词在中国大陆的程序员圈子中通常被读做“C加加”，而西方的程序员通常读做“C plus plus”，“CPP”。 它是一种使用非常广泛的计算机编程语言。C++是一种静态数据类型检查的、支持多重编程范式的通用程序设计语言。它支持过程化程序设计、数据抽象、面向对象程序设计、泛型程序设计等多种程序设计风格。
9、C
C语言是一种计算机程序设计语言，它既具有高级语言的特点，又具有汇编语言的特点。它由美国贝尔研究所的D.M.Ritchie于1972年推出，1978年后，C语言已先后被移植到大、中、小及微型机上，它可以作为工作系统设计语言，编写系统应用程序，也可以作为应用程序设计语言，编写不依赖计算机硬件的应用程序。它的应用范围广泛，具备很强的数据处理能力，不仅仅是在软件开发上，而且各类科研都需要用到C语言，适于编写系统软件，三维，二维图形和动画，具体应用比如单片机以及嵌入式系统开发。
10、java
Java是一种可以撰写跨平台应用程序的面向对象的程序设计语言。Java 技术具有卓越的通用性、高效性、平台移植性和安全性，广泛应用于PC、数据中心、游戏控制台、科学超级计算机、移动电话和互联网，同时拥有全球最大的开发者专业社群。
本节课主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。
叶子节点包含了全部数据，其他节点只包含索引列。InnoDB将通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。
如果主键比较大的话，那辅助索引将会变的更大，因为 辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间
所以建议使用int的auto_increment作为主键
主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大值时，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些 *** 作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）
不过，如果涉及到大数据量的排序、全表扫描、count之类的 *** 作的话，还是MyISAM占优势些，因为索引所占空间小，这些 *** 作是需要在内存中完成的。
MyISM使用的是非聚簇索引， 非聚簇索引的两棵B+树看上去没什么不同 ，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于 索引树是独立的，通过辅助键检索无需访问主键的索引树 。
所以说，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。 一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引 ，这个要看实际情况。
聚簇索引和非聚簇索引的数据分布有区别，主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和以外，下面通过一个列子来讲解InnoDB和MyISAM是如何存储数据的：
该表的主键取值1~10000，按照随机顺序插入并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已是最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。
MyISAM的数据分布很简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：
在行的旁边显示行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。
col2上的索引
事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
InnoDB支持聚簇索引，所以使用不同的方式存储同样的数据。
第一眼看上去，感觉和前面的没什么区别，但是该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储，这也是为什么MyISAM索引和数据结构是分开的。
聚簇索引的每一个叶子节点都包含了主键值。事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。
还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针”。
我们在来看一下 col2索引 。
每一个叶子节点包含了索引列（这里是col2），紧接着是主键值（col1），上图我们省略了非叶子节点这样的细节。InnoDB非叶子节点包含了索引列和一个指向下一级节点的指针。
最后，以一张图表示InnoDB和MyISAM保存数据和索引的区别。
前面讲过，最好使用AUTO_INCREMENT自增列来聚集数据，避免随机的、不连续的、值分布范围大的列做聚簇索引，特别是对于I/O密集型的应用。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：他使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
为了演示这一点，我们做两个基准测试：
1、使用证书ID插入userinfo表，和uuid作为主键的userinfo_uuid表
userinfo_uuid表跟userinfo表除了主键给为UUID，其他字段都一样
测试这两个表的设计，首先在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录。然后向两个表继续插入300万数据，使索引的大小超过服务器的内存容量。测试结果如下：
向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。
为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。
自整型主键插入
因为主键是顺序的，所以InnoDB把每一条记录都存在上一条记录的后面。当达到页的最大容量后，下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。
UUID插入
因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间。这会正价很多的额外工作，并导致数据分布不够优化。
缺点：
把这些随机值载入到聚簇索引后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。
结论 ：使用InnoDB时应尽可能地按主键顺序插入数据，并且尽可能地单调增加聚簇键的值来插入新行。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）