PostgreSQL vs MySQLvs Sqlite_sqlite pgsql

一、SQLite

1.1 SQLite概述

SQLite，是一款轻量级的关系型数据库，是一个用C语言开发库。它的设计目标是用于嵌入式系统中的，很多嵌入式产品中使用了它，它占用资源很低，可能只需要几百K的内存就够了。它支持Windows、Linux、Unix等主流的操作系统，因为它是用C语言开发的，可以和很多程序语言相结合，比如 C++、C#、PHP、Java、Python、Ruby等

使用SQLite数据库的方式很简单，只需要将之集成到程序中，直接调用SQLite提供的API接口即可完成数据库和表的创建、数据的增删改查等操作。整个数据库(定义、表、索引和数据本身)都是存储在宿主主机上的一个或多个db文件。

1.2 SQLite优缺点

优点：
​

1. SQLite是完全免费开源的，是一种轻量级的数据，运行时占用很少的内存资源，只需要集成到程序中即可使用。
2. 支持多个操作系统。支持C、C++、Java和Python多种开发语言。SQLite支持大部分SQL语句和数据库特性。

缺点：

1）并发访问的锁机，SQLite在并发（包括多进程和多线程）读写方面的性能一直不太理想。数据库可能会被写操作独占，从而导致其它读写操作阻塞或出错。

踩坑！！！：采用异步线程池操作时候，会进行表的锁定。报错：[SQLITE_BUSY] The database file is locked (database is locked)。将异步清掉之后，采用单线程模式不会锁表。

将表的模式改为正常模式：直接在dbeaver中执行如下语句：
PRAGMA locking_mode = NORMAL 
1
2

1. sqlite3支持多线程同时读操作，但不支持多线程同时写操作。
2. 同一时刻只能有一个线程去进行写操作，并且在一个线程进行写操作的时候，其他线程是不能进行读操作的。
3. 当一个线程正在写操作时，其他线程的读写都会返回操作失败的错误，显示数据库文件被锁住。
4. 对于多线程写数据库的情况，Sqlite3不能实现同时写，但是可以实现串行写数据，也就是一个线程在写的时候，其他线程等待，第一个线程写完的时候，另一个线程获得数据库文件锁开始写。Sqlite3提供了接口(C语言的)sqlite3_busy_handler()，来实现多线程串行写数据。BusyHandler其实是一个回调函数。也就是当A线程正在写操作时，其他线程写失败时进行的重试操作，其他线程不断地调用BusyHandler来进行一些处理，直到自己获得写权限之后。（这个测试没有成功）

2）SQL标准支持不全，在它的官方网站上，具体列举了不支持哪些SQL92标准。我个人感觉比较不爽的是不支持外键约束。

3）有时候需要访问其它机器上的SQLite库文件，就会把数据库文件放置到网络共享目录上。这时候你就要小心了。当SQLite文件放置于NFS时，在并发读写的情况下可能会出问题（比如数据损坏）。原因据说是由于某些NFS的文件锁实现上有Bug。

不支持多线程同时写操作

1.3 SQLite多个线程读操作锁表

当多线程写数据库时/同一个写操作调用了2次，在上一次还未执行成功的时候又调用。这些情况下，控制台报错【database is locked】：

### Cause: org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
; uncategorized SQLException; SQL state [null]; error code [5]; [SQLITE_BUSY]  The database file is locked (database is locked); nested exception is org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy124.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:173)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy136.selectDeviceInfoList(Unknown Source)
	at com.ruoyi.umc.server.deviceInfo.service.impl.DeviceInfoServiceImpl.selectDeviceInfoList(DeviceInfoServiceImpl.java:58)
	at com.ruoyi.umc.server.deviceInfo.service.impl.DeviceInfoServiceImpl$$FastClassBySpringCGLIB$$3c4061d.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386)
	at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704)
	at com.ruoyi.umc.server.deviceInfo.service.impl.DeviceInfoServiceImpl$$EnhancerBySpringCGLIB$$80d5f688.selectDeviceInfoList(<generated>)
	at com.ruoyi.umc.websocket.WebsocketTimer.queryDevInfoList(WebsocketTimer.java:98)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:95)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
	at org.sqlite.core.DB.newSQLException(DB.java:1012)
	at org.sqlite.core.DB.newSQLException(DB.java:1024)
	at org.sqlite.core.DB.execute(DB.java:863)
	at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:54)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:151)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
	at com.sun.proxy.$Proxy244.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	... 29 common frames omitted
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

java下解决方法：
1、给方法加锁synchronized，同一时刻，写操作只有一个线程执行
2、利用@Aroud的aop思想，通过对方法名判断是否包含写操作（insert、save、update、delete等），在切面添加读锁或者写锁。代码见下：

java全局读写锁
在Java中，全局读写锁可以通过ReentrantReadWriteLock类来实现。该类提供了读写锁的功能，允许多个线程同时对资源进行读操作，但在写操作时要求独占资源。

@Aspect
@Component
@Slf4j
public class SqlLiteSyncAspect {

    ReadWriteLock readWriteLock = new ReentrantReadWriteLock();

   // @Pointcut("execution(* com.ruoyi.umc.manage.impl.*.*(..))")
    @Pointcut("execution(* com.ruoyi.umc.*.*(..))")
    public void pointCut() {}

    @Around("pointCut()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        String name = pjp.getSignature().getName();
        log.debug("方法环绕proceed，class - {}, 方法是 - {}", pjp.getTarget().getClass().getName(), name);
        if (name.contains("save") || name.contains("insert")|| name.contains("del") || name.contains("update")) {
            readWriteLock.writeLock().lock();
            try {
                log.error("写锁保护代码~~~~~~~~");
                Object proceed = pjp.proceed();
                return proceed;
            } catch (Exception e) {
                log.error("写锁保护代码发生异常", e);
                throw e;
            } finally {
                readWriteLock.writeLock().unlock();
            }
        } else {
            try {
                readWriteLock.readLock().lock();
                Object proceed = pjp.proceed();
                return proceed;
            } catch (Exception e) {
                log.error("读锁保护代码发生异常", e);
                throw e;
            } finally {
                readWriteLock.readLock().unlock();
            }
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

3、方法2有一个弊端：过一个方法名(不带insert、save、update、delete)中调用了另一个带（insert、save、update、delete）的子方法，那里aop是失效的。这里建议使用全局变量锁。提一个公共类，里面放置一把写锁，所有写操作将去获取同一把写锁。也就是当A线程正在写操作时，其他线程写失败时进行的重试操作，其他线程不断地调用BusyHandler来进行一些处理，直到自己获得写权限之后。记得设定超时时间。
4、事务操作：将多个写操作放在同一个事务中，减少文件锁的竞争。
5、批量插入：将多个插入合并成一个单一的事务，可以使用sqlite的executemany方法来实现批量插入。

1.4 SQLite应用场景

SQLite是一种轻量级的数据库，主要用于存储一些轻量级的数据，由于其占用的资源小，常常被用在嵌入式系统和移动设备中。SQLite无法处理大型系统中的海量数据，其实时性和性能不够，无法满足海量数据的高效存储及查询的需求。

​ 可以在客户端软件中使用SQLite去存储一些数据，比如IM聊天软件中可以使用他来存放聊天数据，也可以使用它去存储一些配置信息。嵌入式设备的资源有限，需要使用数据库时，优先选择SQLite数据库。

​ 有些数据我们可以直接将内存中的二进制数据以二进制流的方式直接写到文件中，但是这种二进制数据在增删改查时非常不方便，要重写将更新后的二进制数据重写覆盖写到文件中。使用SQLite去存储数据，在增删改查时会非常方便。

二、MySQL

2.1 MySQL概述

其免费开源，目前是使用最广泛的开源关系数据库系统，是众多IT厂商的第一选择。

2.2 MySQL优缺点

优点：

1. 它使用的核心线程是完全多线程
2. 有多种列类型：1、2、3、4、和8字节长度自有符号／无符号整数、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、
   TIMESTAMP、YEAR、和ENUM类型。
3. 它通过一个高度优化的类库实现SQL函数库并像他们能达到的一样快速，通常在查询初始化后不该有任何内存分配。没有内存漏洞。
4. 支持ANSI SQL的LEFT 0UTER JOIN和ODBC。
5. MySQL可以工作在不同的平台上。支持C、C＋＋、Java、Perl、PHP、Python和TCL API。
6. 全面支持SQL的GROUP BY和ORDER BY子句，支持聚合函数(COUNT()、COUNT(DISTINCT)、AVG()、STD()、SUM()、MAX()和MIN())。你可以在同一查询中混来自不同数据库的表。
7. 所有列都有缺省值。你可以用INSERT插入一个表列的子集，那些没用明确给定值的列设置为他们的决省值。

缺点：

1）MySQL最大的缺点是其安全系统，主要是复杂而非标准，另外只有到调用mysqladmin来重读用户权限时才发生改变。
2）MySQL的另一个主要的缺陷之一是缺乏标准的RI（Referential Integrity-RI）机制；Rl限制的缺乏(在给定字段域上的一种固定的范围限制)可以通过大量的数据类型来补偿。
3）MySQL直到5.0版本才支持存储过程，对存储过程支持的并不是很好。

2.3 MySQL应用场景

MySQL用一种简单的方式为数据访问（和使用）提供了可靠的保护。大多数网站和Web应用程序，可以忽视约束性地简单工作在MySQL上。另外，如果你工作在一个需要高度定制的解决方案上，可以使用MySQL的丰富配置设置和操作模式，能够很容易地执行你的规则。如果你在开发的应用需要被多用户访问，而且这些用户都用同一个数据库，则需要选择客户 - 服务器模式数据库MySQL。

三、PostgreSQL

3.1 PostgreSQL概述

PostgreSQL是一个功能强大的开源数据库系统，它诞生于美国加州大学伯克利分校，PostgreSQL于1996年首次以开源软件的形式发布。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。

​ 目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris和Tru64）和Windows。PostgreSQL是完全的事务安全性数据库，完整地支持外键、联合、视图、触发器和存储过程（并支持多种语言开发存储过程）。它支持了大多数的SQL:2008标准的数据类型，包括整型、数值型、布尔型、字节型、字符型、日期型、时间间隔型和时间型，它也支持存储二进制的大对像，包括图片、声音和视频。PostgreSQL对很多高级开发语言有原生的编程接口，如C/C++、Java、.Net、Perl、Python、Ruby、Tcl 和ODBC以及其他语言等，也包含各种文档。

最重要的一点，PostgreSQL的源代码可以自由获取，它的授权是在非常自由的开源授权下，这种授权允许用户在各种开源或是闭源项目中使用、修改和发布PostgreSQL的源代码。用户对源代码的可以按用户意愿进行任何修改、改进。 因此，PostgreSQL不仅是一个强大的企业级数据库系统，也是一个用户可以开发私用、网络和商业软件产品的数据库开发平台。

3.2 PostgreSQL的优势

• 高级查询功能：PostgreSQL 支持复杂的 SQL 查询，包括联接、子查询、聚合函数、窗口函数等。同时它还支持全文搜索、地理空间数据处理和图形数据分析等扩展功能。
• 完整性约束：PostgreSQL 允许定义各种完整性约束，例如主键、唯一约束、外键和检查约束，以保证数据的完整性和一致性。
• 触发器和存储过程：PostgreSQL 支持触发器和存储过程，允许在插入、更新或删除数据时执行自定义的业务逻辑。
• 并发控制：借助多版本并发控制（MVCC）技术，PostgreSQL 实现了高度并发的读写操作，避免数据锁定和读写冲突。
• 复制和高可用性：PostgreSQL 支持数据复制和流复制，可以创建热备份和实现高可用性架构。
• 扩展性：通过提供扩展机制，用户可以自定义和使用各种插件和扩展，从而增强 PostgreSQL 的功能。

3.3 PostgreSQL应用场景

如果你需要你的数据库执行一些定制操作，灵活可扩展的PostgreSQL是更好的选择。如果可能要把整个数据库系统迁移到另一个数据库系统（例如Oracle）中，PostgreSQL对于这种切换将是最兼容和易于操作的。PostgreSQL是完全开源的，不受任何商业公司的控制，与受甲骨文控制的MySQL相比，PostgreSQL更加值得信赖。比如在国产化系统中，为了安全起见，会优先选择更加自主可控的PostgreSQL。

应用系统中的选择

• 需要处理大量的数据信息，需要较高的安全性，以及支持多用户并发，可以选择MySQL或PostgreSQL。在嵌入式设备和移动设备中，业内使用的都是轻量级数据库SQLite，实际运行时需要频繁直接读/写磁盘文件。
• 在国产化的系统中，为了实现完全的自主可控，会选择完全开源且不受商业控制的PostgreSQL。

PostgreSQL vs MySQL

一、数据类型：PostgreSQL支持更多的数据类型，例如数组，json，hstore等，而MySQL则支持空间数据类型（GIS）。

二、扩展性：PostgreSQL相比MySQL具有更强的扩展性，支持自定义数据类型，函数和存储过程等。它还提供了一些高级功能，如异步复制，流复制，热备等。

三、ACID：PostgreSQL具有更严格的ACID（原子性，一致性，隔离性和持久性）兼容性。在默认情况下，PostgreSQL默认的隔离级别是"Read Committed"(已提交读)），MySQL默认使用更高的隔离级别（“可重复读”(Repeatable Read)）。

四、性能：MySQL比PostgreSQL更适用于大型的数据集，因为它的性能更好，特别是在读写和并发方面。而PostgreSQL在处理复杂的查询和更大数据集方面的性能表现更优秀。

五、开源协议：MySQL的开源协议是GPL（通用公共许可证），这意味着对MySQL进行修改的衍生产品也必须使用同一协议进行发布。而PostgreSQL的开源协议是BSD，这意味着PostgreSQL可以被商业软件使用，并且修改后的代码可以私有化。

六、跨平台支持：MySQL支持更多的操作系统，如Windows，Linux，macOS，FreeBSD等。PostgreSQL虽然也支持这些操作系统，但它的最初目标是在UNIX操作系统上运行。
MySQL更适合简单的Web应用和小规模数据集

总的来说，PG更适合复杂的数据结构、高级应用和大规模数据集____国产化，而MySQL更适合简单的Web应用和小规模数据集。但这并不是绝对的规则，因为两种数据库都可以用于各种类型的应用程序。
————————————————
————————————————

最后说明下，在PostgreSQL中你虽然可以请求四种标准事务隔离级别中的任意一种，但是内部只实现了三种不同的隔离级别，即 PostgreSQL 的读未提交模式的行为和读已提交相同。 这是因为把标准隔离级别映射到 PostgreSQL 的多版本并发控制架构的唯一合理的方法。