深圳 福田 网站建设,网站建设维护课件ppt,医院网站建设的计划,海外网站空间前言 今天我将站在程序员的角度以MySQL为例探索数据库的奥秘#xff01;数据库基本原理 我对DB的理解 1、数据库的组成#xff1a;存储实例 不必多说#xff0c;数据当然需要存储#xff1b;存储了还不够#xff0c;显然需要提供程序对存储的操作进行封装#xff0c;对外…前言 今天我将站在程序员的角度以MySQL为例探索数据库的奥秘数据库基本原理 我对DB的理解 1、数据库的组成存储实例 不必多说数据当然需要存储存储了还不够显然需要提供程序对存储的操作进行封装对外提供增删改查的API即实例。 一个存储可以对应多个实例这将提高这个存储的负载能力以及高可用多个存储可以分布在不同的机房、地域将实现容灾。 2、按Block or Page读取数据 用大腿想也知道数据库不可能按行读取数据Why?^_^。实质上数据库如Oracle/MySQL都是基于固定大小比如16K的物理块BlockorPage我这里就不区分统一称为Block来实现调度和管理的。要知道Block是数据库的概念如何对应到文件系统呢显然需要指出“这个Block的地址在哪里”当查找到地址后读取固定大小的数据就相当于完成了Block的读取了。 数据库很聪明的它不会仅仅只读取需要读取的Block它还会替我们把附近的Block块都读取加载至内存。实际上这是为了减少IO次数提高命中率。事实上一个Block块的附近Block也是热点数据这种处理方式很有必要 3、磁盘IO是数据库的性能瓶颈 毫无疑问数据在磁盘上少不了磁盘IO。什么磁头旋转定位磁道寻址的过程就不说了我们是程序员也管不了这些。但是这个过程确实是非常耗时的和内存读取不是一个数量级所以后来出现了很多方式来减少IO提升数据库性能。 比如增加内存让数据库把数据更多的加载至内存。内存虽好但也不能滥用为什么这么说呢假设数据库中有100G数据如果都加载至内存也就说数据库要管理100G磁盘数据100G内存数据你说累不累数据库要处理磁盘和内存的映射关系数据的同步还要对内存数据进行清理如果涉及数据库事务又是一系列复杂操作......不过这里需要指出的是为了加快内存查找速度数据库一般对内存进行HASH存放。 比如利用索引索引相比内存是一个性价比非常高的东西后文详细介绍MySQL的索引原理。 比如利用性能更好的磁盘...和咱们就没关系呢 4、提出一些问题思考下 为什么我们说利用delete删除一个表的数据较trancate一个表要慢 【一个按行查找删除多费劲一个基于Block的体系结构删除】 为什么我们说要小表驱动大表 【小表驱动大表会快什么鬼M*N和N*M不是一样的么有鬼的地方就有索引】探索MySQL索引背后的原理 对于绝大数的应用系统读写比例在10:1甚至100:1而且insert/update很难出现性能问题遇到最多的最棘手的就是select了select优化是重中之重显然少不了索引 说起MySQL的索引我们会冒出很多这些东西BTree索引/BTree索引/Hash索引/聚集索引/非聚集索引...这么多晕头索引到底是什么想解决什么问题 老生常谈了官网说MySQL索引是一种数据结构索引的目的就是为了提高查询效率。 说白了不使用索引的话磁盘IO次数比较多要想减少磁盘IO次数怎么办 我们想通过不断缩小想要获取的数据的范围来筛选出最终想要的结果把每次查找数据的磁盘IO次数控制在一个很小的数量级最好是常数数量级。 为了应对上述问题BTree索引出来了HelloBTree 在MySQL中不同存储引擎对索引的实现方式是不同的这里将重点分析MyISAM和Innodb。 MyISAM引擎的BTree索引结构 我们知道对于MyISAM引擎而言数据文件和索引文件是分离的。从图中也可以看出通过索引查找到后就得到了数据的物理地址然后根据地址定位数据文件中的记录即可。这种方式也叫非聚集索引。 而对于Innodb引擎而言数据文件本身是索引文件通俗点说叶子节点上MyISAM存储的是记录的物理地址而Innodb上存储的是数据内容这种方式即聚集索引。 另外一点需要注意的是对于Innodb而言主键索引中叶子节点存储的是数据内容而普通索引的叶子节点中存储的是主键值也就是说对于Innodb的普通索引字段查找先通过普通索引的BTree查找到主键后然后通过主键索引的BTree进行查找。从这里你可以看出对于Innodb而言主键的建立非常重要 而对于MyISAM而言主键索引和普通索引仅仅的区别在于主键只需要查找到一条记录即可停止而普通索引允许重复找到一条记录后需要继续查找在结构上没有区别如上图所示。深入BTree 提几个问题 ①.为什么BTree把真实的数据放到叶子节点而不是内层节点 ②.为什么我们说索引字段要尽可能短最好是单调递增的 ③.为什么复合索引存在最左匹配原则 ④.范围查询,,between,like对最左匹配有什么影响 关于BTree的一些数学理论咱们就不玩了至少一点可以肯定的是数据表的数据量NF(树的高度h每个Block存储的索引的个数m)。在N一定的情况下索引字段越小那么m会越大这意味着h将越小树越低当然查找的更快 如果内层节点存放真实的数据显然m会变小树将变高。 在实际应用中我们应该尽可能采用单调递增的字段作为主键一方面不会使得索引的数据结构变大减小了索引占用的空间另一方面也不会频繁的分裂BTree使得效率下降。 比如复合索引(name,age,sex)BTree会优先比较name来确定下一步的搜索方向。如果突然来了个(age,sex)根本上就无从下手。这也是符合常理的对于一本书我们说“找到第几章第几节的XXX”从没有听说过“找到第几节的XXX”这是复合索引的重要特性即最左匹配特性。 假设存在复合索引(name,age,sex)我们在进行select的时候并没有按照这个顺序进行而是sexmanandnamezfzandage27是否会使用索引呢数据库是很聪明的在SQL优化的时候会自动帮助我们调整但是如果缺失了复合索引的第一列数据库也将无能为力呢。 对于最左匹配MySQL会一直向右匹配直到遇到范围查询就停止匹配。什么意思比如复合索引(name,age,sex)对于namezhangfengzheandage26andsexman实际上只利用到了复合索引的name列。想利用索引就得“干净” 什么叫“干净”就是不要让索引参与计算比如在索引上应用函数很可能导致索引失效。为什么呢 其实不用想BTree上存储的是数据要比较的话需要把所有的数据都应用上函数显然成本太大。想建立索引看看区分度 索引虽然物美价廉但是也别乱来。count(distinctcol)/count(*)可以算一下col的区分度显然对于主键而言就是1。区分度太低的话可以考虑下是否还有必要建立索引呢Hash索引 这里并不是要深入分析Hash索引而是要说明一下Hash的思想真是无处不在 在MySQL的Memory存储引擎中存在hash函数给一个key通过hash函数进行计算得到地址所以通常情况下hash索引查找会非常快O1的速度。但是也存在hash冲突和HashMap一样通过单链表的形式解决。 思考下hash索引是否支持范围查询呢 显然是不支持的它只能给一个KEY去查找。就如同HashMap一样查找key包含zhangfengzhe的会很快么SQL优化神器explain SQL优化的场景很多网上的技巧也很多完全记不住 要想彻底解决这个问题我想只有把索引背后的数据结构和原理做适当的理解遇到书写SQL或者SQL慢查询的时候我们有基础去分析再利用好explain工具去验证就应该问题不大呢。 explain查询的结果可以告诉你哪些索引正在被使用表是如何被扫描的等等。这里我将演示个Demo。 数据表student 注意复合索引(age,address) 符合最左前缀匹配 复合索引失效 OK到这里准备结束了查询容易优化不易且写且珍惜 扩展阅读 一个不可思议的MySQL慢查分析与解决 MySQL大数据量分页查询方法及其优化 MySQL性能优化:索引和查询优化 深入理解synchronized关键字 深入了解Java之虚拟机内存 Redis为何这么快--数据存储角度 来源https://www.jianshu.com/p/aa1f0f29b4f8
