开源免费关系数据库哪个最好？

1
-- PostgreSQL高级功能示例
2
-- JSON支持
3
CREATE TABLE users (
4
    id SERIAL PRIMARY KEY,
5
    name VARCHAR(100),
6
    profile JSONB
7
);
8

9
INSERT INTO users (name, profile) VALUES
10
('张三', '{"age": 25, "skills": ["Java", "Python"], "address": {"city": "北京"}}');
11

12
-- 数组支持
13
CREATE TABLE products (
14
    id SERIAL PRIMARY KEY,
15
    name VARCHAR(100),
16
    tags TEXT[]
17
);
18

19
-- 全文搜索
20
SELECT * FROM articles
21
WHERE to_tsvector('chinese', content) @@ plainto_tsquery('chinese', '搜索关键词');

1
-- MySQL性能优化示例
2
-- 索引优化
3
CREATE INDEX idx_user_email ON users(email);
4
CREATE INDEX idx_order_date ON orders(order_date);
5

6
-- 分区表
7
CREATE TABLE orders (
8
    id INT,
9
    order_date DATE,
10
    amount DECIMAL(10,2)
11
) PARTITION BY RANGE (YEAR(order_date)) (
12
    PARTITION p2023 VALUES LESS THAN (2024),
13
    PARTITION p2024 VALUES LESS THAN (2025),
14
    PARTITION p2025 VALUES LESS THAN (2026)
15
);
16

17
-- 存储过程
18
DELIMITER //
19
CREATE PROCEDURE GetUserOrders(IN user_id INT)
20
BEGIN
21
    SELECT o.*, p.name as product_name
22
    FROM orders o
23
    JOIN products p ON o.product_id = p.id
24
    WHERE o.user_id = user_id;
25
END //
26
DELIMITER ;

1
-- SQLite使用示例
2
-- 创建数据库（自动创建文件）
3
CREATE TABLE users (
4
    id INTEGER PRIMARY KEY AUTOINCREMENT,
5
    name TEXT NOT NULL,
6
    email TEXT UNIQUE,
7
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
8
);
9

10
-- 插入数据
11
INSERT INTO users (name, email) VALUES ('李四', '[email protected]');
12

13
-- 查询数据
14
SELECT * FROM users WHERE email LIKE '%@example.com';

1
# Python中使用SQLite
2
import sqlite3
3

4
# 连接数据库（自动创建）
5
conn = sqlite3.connect('myapp.db')
6
cursor = conn.cursor()
7

8
# 创建表
9
cursor.execute('''
10
    CREATE TABLE IF NOT EXISTS users (
11
        id INTEGER PRIMARY KEY AUTOINCREMENT,
12
        name TEXT NOT NULL,
13
        email TEXT UNIQUE
14
    )
15
''')
16

17
# 插入数据
18
cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)',
19
               ('张三', '[email protected]'))
20

21
# 查询数据
22
cursor.execute('SELECT * FROM users')
23
users = cursor.fetchall()
24

25
# 提交并关闭
26
conn.commit()
27
conn.close()

1
-- 配置优化
2
-- postgresql.conf
3
shared_buffers = 256MB
4
effective_cache_size = 1GB
5
work_mem = 4MB
6
maintenance_work_mem = 64MB
7

8
-- 索引优化
9
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
10
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, order_date);
11

12
-- 分区表
13
CREATE TABLE orders (
14
    id SERIAL,
15
    user_id INTEGER,
16
    order_date DATE,
17
    amount DECIMAL(10,2)
18
) PARTITION BY RANGE (order_date);
19

20
CREATE TABLE orders_2024 PARTITION OF orders
21
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

1
-- 配置优化
2
-- my.cnf
3
[mysqld]
4
innodb_buffer_pool_size = 1G
5
innodb_log_file_size = 256M
6
innodb_flush_log_at_trx_commit = 2
7
query_cache_size = 128M
8

9
-- 索引优化
10
CREATE INDEX idx_users_email ON users(email);
11
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
12

13
-- 查询优化
14
EXPLAIN SELECT u.name, COUNT(o.id) as order_count
15
FROM users u
16
LEFT JOIN orders o ON u.id = o.user_id
17
WHERE o.order_date >= '2024-01-01'
18
GROUP BY u.id, u.name;

1
-- PostgreSQL性能查询
2
-- 查看慢查询
3
SELECT query, mean_time, calls, total_time
4
FROM pg_stat_statements
5
ORDER BY mean_time DESC
6
LIMIT 10;
7

8
-- 查看表大小
9
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
10
FROM pg_tables
11
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
12

13
-- MySQL性能查询
14
-- 查看慢查询
15
SHOW VARIABLES LIKE 'slow_query_log';
16
SHOW VARIABLES LIKE 'long_query_time';
17

18
-- 查看表状态
19
SHOW TABLE STATUS;