Hyman的博客

自数据库诞生之后，数据库优化就是一个各家公司绕不开的课题，也是面试造飞机时逃不开的知识点。因为当一个系统发展到了一定的规模，瓶颈往往都在数据库上，也因此催生了各种数据库的优化技术。某里的子柳就在《淘宝技术这十年》中提到，最初淘宝的系统架构中数据库部分就是1主2备的mysql，顶住了日均30W个PV（Page View）。再以后随着用户规模越来越大，使用连接池+单个oracle，然后就是在单个oracle基础之上的硬件的向上拓展，机器从服务器到小型机，存储从普通硬盘到SAN，再到NAS，总而言之...

今天我们来聊一下全文检索，想必做搜索相关业务朋友对这个概念不会陌生，尤其是做搜索引擎，或者类似CSDN、知乎类的社区网站，全文检索是逃不开的业务。文，即文章、文档。全文搜索就是给定关键词，在所有的文档数据中找到符合关键词的文档。不管是哪种业务模式下的全文检索功能，其实大体的实现思路类似，如下所示： 使用文字进行描述，就是：（1）获取原始文档数据。（2）对文档进行分析，分词（所为分词，就是按照分词符，如空格，将一句话分隔成若干的单词）（3）存档存入数据库，并通...

Postgresql对模糊查询的支持，主要有三种方法：传统的like操作符、SQL99新增的SIMILAR TO操作符以及POSIX正则表达式。除了前面两种SQL标准的模糊查询手段，Postgresql对正则表达式具有强大的支持，这就使得Postgresql可以支持任意字符串的匹配查找，下面我们分别对这三种方法进行学习。一、LIKE操作符1.1 “%”代表0个或任意个数的任意字符 %代表了0个以上的任意字符，为了验证%的使用，下面先看下测试表里面的数据：stock...

究竟什么样的索引才算是好索引（本文中的索引是指Oracle、SQLServer、Postgresql使用的基于BTree结构的普通索引，并非指Postgrsql GIN、Gist此类特色索引）？一、针对索引数据顺序性的优化 我们知道BTree索引的数据结构本质是一个多路平衡查找树，这种结构也就意味着，对数据的查找效率的和数据顺序紧密相关。比如对于一个多列索引（A\B\C），首先保证索引按照A排序，在A相等的前提下按照B再进行排序，接着在B相等的前提下，又按照C进行排序。这种有...

本文中，我们共同学习下Postgresql的WAL日志。WAL，是Write Ahead Log的简称，翻译过来就是预写日志，或者叫做重做日志。相信大家对数据库事务的四大特性ACID（原子性、一致性、隔离性和持久性）已经非常熟悉了，今天我们先从数据库数据的持久性说起。数据的持久性是指，数据库中的事务中的数据一旦提交，便可以做到持久化，只要你不删除，那么数据将会永远存在数据库中。一、有关CheckPoint 现在我们就来讨论下这个提交的过程。我们直到数据库中数据的存放位置最终...

MVCC（Multi-Version Concurrency Conctrol，多版本控制并发），是数据库中一种常见的存在并发时保证数据一致性的方法。包括Oracle、Mysql、Postgresql在内许多主流数据库都有一套自己的MVCC方法实现。从实现逻辑上来讲，大体分为两种： 第一种，就是Oracle和Mysql采用的，在更新数据时，把原来的数据放到回滚段中，如果有其它事务需要读取原来的数据，则需要从回滚段中进行读取。 第二种，是Postgresql采用的...

上一篇文章中，我们主要学习了Postgresql的逻辑备份和恢复，接着上一篇的内容，今天我们介绍下Postgresql的物理备份。所谓物理备份，就是针对数据库的数据文件或者目录进行备份，物理备份的好处就是简单高效，不需要针对每个数据库对象进行备份，只需要将所有数据一次性的打包。不足之处在于，它不支持部分备份只能进行全量备份。物理备份有冷备份和热备份的说法，冷备份是指备份时需要先停止当前数据库的运行，保证在备份期间不会有新的数据库的改动。而热备份是线上备份，备份是不影响数据库的访问。 ...

本文主要介绍Postgresql的数据库备份和还原。数据库备份是防止数据丢失的一种最简单有效的方式。通过数据库的备份和还原，我们可以将数据库回退到历史上任意一个时间点，进而排除错误的数据。也可以快速克隆线上的数据库环境，协助我们在线下进行缺陷的复现和回归。Postgresql中数据库的备份的方式大体分为两种——逻辑备份和物理备份,下面我们分别进行介绍。 在介绍备份的方法之前，笔者先介绍下用到的需要进行备份的数据库test，在test数据库中有两个模式如下：test=# \...

今天，我们学习下Postgresql中的锁机制。锁是数据库事务的基础，通过锁才能保证数据库在并发时能够保证数据的安全和一致，才能够达到事务的一致性和隔离性。但是任何事物都有它的两面性，引入锁同样会增加性能开销和可能的死锁问题，不过好在都存在解决方法。Postgresql和其它数据库一样，从锁作用的对象上来说可以分为表锁、行锁两种，从排他性来说可以大体分为共享锁和排他锁，但是由于Postgresql是通过多版本的方式对数据库进行更新，它也引入了Access锁。接下来我们就对这些内容分类进行学习。...

本文主要学习下Postgresql中事务。事务是数据库中非常重要的一个逻辑结构，可以说是数据库数据安全和准确的一个最重要的保证。同时，它也大大提高了数据库的易用性，因为通过数据库事务，我们在操作数据库进行增删改查时不必再考虑并发引起的问题，而只要专注地实现自己的功能。与其它关系型数据库一样，Postgresql的 事务也具有ACID（原子性、一致性、隔离性和持久性）四大特性，通过这四大特性，Postgresql才有了上述功能特点。但除了这些通用的特性之外，Postgresql的事务也有自己的特...

一、全表扫描 全表扫描，也叫顺序扫描，扫描时把表中所有的数据块从头到尾遍历一边，找到复合条件的数据块。全表扫描在在explain中使用Seq Scan表示：stock_analysis_data=# explain (analyze,verbose,buffers,costs,timing) select * from t1 where id=10; QUERY PLAN ...

一、BRIN索引原理 本文我们继续学习下Postgresql中另外一个比较有特色的索引——BRIN索引。BRIN索引是Block Range Index索引的简写，它将数据在磁盘上的block按照一定的数目进行分组，这个数目可以通过创建BRIN时的参数pages_per_range进行设置，默认是128。分组之后，计算每组的取值范围。在 查找数据时，会遍历这些取值范围，排除掉不在范围之内的分组。 与其它索引不同：（1）BTree等其它索引在查找数据时是根据数...

今天，我们深入学习下Potgresql的GIN索引。GIN索引时Generalized Inverted Index的缩写，意思是广义的倒排索引。GIN索引和Gist索引类似，都是一个通用的索引框架，我们可以基于此框架开发自定义的GIN索引。一、GIN索引的内部结构 了解Java内部HashMap结构的应该了解，Java8之后的HashMap内部结构是Hash Table+链表或者Hash Table+红黑树。其实GIN也采用了类似的思想，只不过是Entry Tree+Po...

本文，我们深入学习下Posgresql中的SP-Gist索引。SP-Gist是Space-Partition Gist（空间分区Gist索引）的简写。和Gist索引一样，它也是一个索引框架，但是相比较于Gist，它优化了索引算法，提高了索引的性能。在此框架下，可以实现如下三种索引：quad-tree（四叉树） k-d tree（K维树） radix tree（基数树） 同时，Postgresql也实现了以下几种类型的SP-Gist索引的操作类，我们可以在这些类型上直接建立...

一、关于分区表 表分区是在大数据优化中的一种常见的分表方案，通过将大数据按照一定的规则（最常见的是按照时间）进行分表处理，将逻辑上的一个大表分割成物理上的几块表，插入数据时，数据会自动插入到不同的分区表中，从而实现查询或者其它操作的性能优化。相比于一个大表，分区表具有以下优点：（1）当查询或者更新一个分区的大部分记录时，采用顺序扫描而不是随机扫描，可以获得巨大的性能提升。（2）使用不频繁的历史数据可以转移到一些低廉的存储介质上，而热数据放到性能较好的存储介质上，可以最大限度的减少成本...

本文，我们进一步学习下Gist索引。Gist是Generalized Search Tree的意思，意思是通用搜索树，底层结构也是一种平衡树，它是一套索引模板，可以支持用户实现自定义的索引。相比于BTree索引，BTree索引可以建立在任意类型之上，但是BTree只支持<、=、>操作符，而Gist索引可以支持@>、&&等复杂运算的操作符。一、Gist索引的存储结构 在《Postgresql杂谈 04—Postgresql中的四种常规索引》一...

一、关于BTree 本文我们深入学习下Postgresql的BTree索引。我们知道，BTree是一种多路平衡查找树，被广泛应用于数据库的索引之中。与我们之前接触过的最多的二叉树不同，多路树的意思是每个节点不止一个子节点。而查找树是一种顺序树，也就是说对于树种每一个节点来说，它的左子树上所有的节点都不大于它，而它的右子树上所有的节点都不小于它。由于这种特性，理论上B树查找的时间复杂度可以达到二分查找的时间复杂度O(logn)。BTree的查询效率和树的高度有关，为了降低树的高度，提高查询效率...

本文笔者主要介绍下Postgresql中两种比较特殊的索引——函数索引和部分索引。函数索引，顾名思义，索引字段经过了某个函数的处理，以达到在某种特殊的场景下走索引的目的。而部分索引，就是针对表中的部分字段建立索引，而达到查询其中这部分数据走索引的目的。一、函数索引 函数索引，一种典型的应用场景，就是用表中的某一字符串类型的字段构建查询条件进行查询时，期望忽略大小写，笔者通过下面的例子来介绍下函数索引的用法。 首先，创建一个users表，用来作为数据源：...

本文主要介绍下Postgresql的另外两种特殊的类型Range类型（范围类型）和数组类型。两种类型，适用于不同的场景，但是最终的目的相同，就是使用传统的数据类型，建立常规的索引无法满足查询的性能要求。而使用范围类型(通过使用gist索引)、使用数组类型（通过使用Gin索引）可以大幅度的加快特定场景下数据的查询速度。下面笔者就分别介绍下这两种数据类型的使用。一、Range类型1.1 Range类型的使用 在使用Range类型之前，我们先建立一些测试数据，用于Range类...

一、JSON和JSONB Postgresql相比较与其它关系型数据库，很大的一个优势在于它内置了很多中特殊的数据类型，像inet、MAC，本文所介绍的json和jsonb类型也是Postgresql特有的2种数据类型，其实他们的本质都是存储jon格式的数据，但是不同的是json格式是按照原样的json字符串进行存储，包括字符串中的重复键、空格、顺序也都会原样保存；而jsonb格式会对原样的json字符串进行解析，会删除重复的键以及空格，而且也不会保证键的顺序，在存储时会转换成二进制。因此，...