" />
文章

JDBC-sql

Posted recently Updated recently
By Administrator
73~94 min read

数据库核心概念详解

1. SQL语言基础

1.1 DDL (数据定义语言)

  • CREATE: 创建数据库对象
    sql CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE );
  • ALTER: 修改数据库对象
    sql ALTER TABLE users ADD COLUMN age INT; ALTER TABLE users MODIFY email VARCHAR(150);
  • DROP: 删除数据库对象
    sql DROP TABLE users; DROP DATABASE test_db;
  • TRUNCATE: 清空表数据
    sql TRUNCATE TABLE users;

1.2 DML (数据操作语言)

  • SELECT: 查询数据
    sql SELECT * FROM users WHERE age > 18; SELECT username, email FROM users ORDER BY username;
  • INSERT: 插入数据
    sql INSERT INTO users (username, email) VALUES ('张三', 'zhangsan@example.com'); INSERT INTO users VALUES (1, '李四', 'lisi@example.com', 25);
  • UPDATE: 更新数据
    sql UPDATE users SET age = 26 WHERE username = '张三';
  • DELETE: 删除数据
    sql DELETE FROM users WHERE age < 18;

1.3 DCL (数据控制语言)

  • GRANT: 授权
    ```sql
    -- MySQL示例
    GRANT SELECT, INSERT ON database.users TO 'user1'@'localhost';

-- PostgreSQL数据库级别授权示例
-- 数据库信息：
-- 地址: 43.143.222.161
-- 端口: 5432
-- 用户: root
-- 授权用户: abc

-- 1. 数据库级别授权
-- 创建用户（如果不存在）
CREATE USER abc WITH PASSWORD 'your_password';

-- 授权thought数据库的INSERT权限
\c thought root
GRANT CONNECT ON DATABASE thought TO abc;
GRANT USAGE ON SCHEMA public TO abc;
GRANT INSERT ON ALL TABLES IN SCHEMA public TO abc;
-- 对未来创建的表也自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO abc;

-- 2. Schema级别授权
-- 连接到think数据库并授权think schema
\c think root
GRANT USAGE ON SCHEMA think TO abc;
GRANT INSERT ON ALL TABLES IN SCHEMA think TO abc;
-- 对未来创建的表也自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA think GRANT INSERT ON TABLES TO abc;

-- 3. 表级别授权
-- 授权think schema下的特定表
\c think root
GRANT INSERT ON TABLE think.specific_table1 TO abc;
GRANT INSERT ON TABLE think.specific_table2 TO abc;

-- 4. 查看权限
\du abc -- 查看用户权限
\z think.specific_table1 -- 查看特定表权限

-- 连接字符串示例
-- thought数据库连接
-- postgresql://abc:your_password@43.143.222.161:5432/thought
-- think数据库连接
-- postgresql://abc:your_password@43.143.222.161:5432/think
- REVOKE: 撤销权限sql
-- MySQL示例
REVOKE INSERT ON database.users FROM 'user1'@'localhost';

-- PostgreSQL撤销权限示例
-- 1. 撤销数据库级别权限
\c thought root
REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM abc;
REVOKE USAGE ON SCHEMA public FROM abc;
REVOKE CONNECT ON DATABASE thought FROM abc;

-- 2. 撤销Schema级别权限
\c think root
REVOKE INSERT ON ALL TABLES IN SCHEMA think FROM abc;
REVOKE USAGE ON SCHEMA think FROM abc;

-- 3. 撤销表级别权限
REVOKE INSERT ON TABLE think.specific_table1 FROM abc;
REVOKE INSERT ON TABLE think.specific_table2 FROM abc;

-- 4. 撤销默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE INSERT ON TABLES FROM abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA think REVOKE INSERT ON TABLES FROM abc;

-- 5. 完全删除用户（如果需要）
DROP USER IF EXISTS abc;
- COMMIT: 提交事务sql
COMMIT;
- ROLLBACK: 回滚事务sql
ROLLBACK;
```

1.4 常用SQL函数

  • 聚合函数
  • COUNT(): 计数
  • SUM(): 求和
  • AVG(): 平均值
  • MAX(): 最大值
  • MIN(): 最小值
  • 字符串函数
  • CONCAT(): 字符串连接
  • SUBSTRING(): 子字符串
  • UPPER()/LOWER(): 大小写转换
  • 日期函数
  • NOW(): 当前日期时间
  • DATE_FORMAT(): 日期格式化
  • DATEDIFF(): 日期差值

2. 事务处理

2.1 事务特性 (ACID)

  • 原子性(Atomicity): 事务要么全部执行，要么全部不执行
    sql BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
  • 一致性(Consistency): 事务执行前后数据库状态保持一致
  • 隔离性(Isolation): 事务之间相互隔离
  • 持久性(Durability): 事务提交后数据永久保存

2.2 事务隔离级别

  1. 读未提交(Read Uncommitted)
    - 最低隔离级别
    - 可能发生脏读、不可重复读、幻读
    sql SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  2. 读已提交(Read Committed)
    - 防止脏读
    - 可能发生不可重复读、幻读
    sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

  3. 可重复读(Repeatable Read)
    - 防止脏读、不可重复读
    - 可能发生幻读
    sql SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

  4. 串行化(Serializable)
    - 最高隔离级别
    - 完全防止脏读、不可重复读、幻读
    - 性能最差
    sql SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2.3 事务并发问题

  • 脏读：读取到未提交的数据
  • 不可重复读：同一事务中多次读取同一数据得到不同结果
  • 幻读：同一事务中多次查询返回的结果集不一致
  • 丢失更新：多个事务同时更新导致更新丢失

3. 数据库连接池

3.1 连接池优势

  • 减少连接创建和销毁的开销
  • 提高系统响应速度
  • 控制连接数量，防止系统过载
  • 统一管理数据库连接

3.2 常见连接池实现及对比

3.2.1 HikariCP

  • 优势：
  • 性能最佳，号称"光速"连接池
  • 代码精简，仅仅14KB
  • 零配置，可以直接使用
  • 可靠性高，在高并发下表现稳定
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10);
HikariDataSource dataSource = new HikariDataSource(config);

3.2.2 Druid

  • 优势：
  • 强大的监控功能
  • 防SQL注入
  • 详细的统计信息
  • 扩展性好
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("password");
dataSource.setInitialSize(5);
dataSource.setMaxActive(10);

3.2.3 C3P0

  • 优势：
  • 成熟稳定
  • 文档齐全
  • Spring框架集成良好
  • 劣势：
  • 性能相对较差
  • 更新较慢
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
cpds.setUser("root");
cpds.setPassword("password");

3.2.4 DBCP

  • 优势：
  • Apache出品
  • 配置简单
  • 支持JNDI
  • 劣势：
  • 性能一般
  • 不支持高并发
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/test");
ds.setUsername("root");
ds.setPassword("password");

3.2.5 PostgreSQL内置连接池

  • PgJDBC (PostgreSQL JDBC Driver) 本身不提供连接池
  • PostgreSQL服务器端提供连接池功能：
    ```sql
    -- 查看当前连接池设置
    SHOW max_connections;
    SHOW superuser_reserved_connections;

-- 设置连接池参数（需要在postgresql.conf中配置）
max_connections = 100 # 最大连接数
superuser_reserved_connections = 3 # 为超级用户保留的连接数
```

  • 推荐在应用层使用专业连接池：
    1. HikariCP（推荐）：
    java HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:postgresql://localhost:5432/dbname"); config.setUsername("postgres"); config.setPassword("password"); config.setMaximumPoolSize(10); HikariDataSource ds = new HikariDataSource(config);
  1. PgBouncer（PostgreSQL专用连接池）：
    ```ini
    [databases]
    * = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = localhost
auth_type = md5
pool_mode = session
max_client_conn = 100
default_pool_size = 20
```

3.3 连接池性能对比

连接池 并发性能 功能特性 监控支持 使用难度 框架集成
HikariCP ★★★★★ ★★★★ ★★★ ★★★★★ ★★★★★
Druid ★★★★ ★★★★★ ★★★★★ ★★★ ★★★★
C3P0 ★★★ ★★★ ★★ ★★★★ ★★★★
DBCP ★★★ ★★★ ★★ ★★★★ ★★★

3.4 连接池配置参数

  • 基础配置
  • 初始连接数：池启动时创建的连接数
  • 最大连接数：池中最多可容纳的连接数

  • 最小空闲连接：池中最少应保持的空闲连接数

  • 性能相关

  • 获取连接超时时间：等待可用连接的最长时间
  • 空闲连接超时时间：空闲连接被回收前的最长时间
  • 最大生命周期：连接被强制关闭前的最长时间

  • 验证查询：用于测试连接是否可用的SQL

  • 监控相关

  • 是否开启JMX监控
  • 是否记录慢SQL
  • 是否开启监控页面（Druid特有）

4. 事务管理

4.1 编程式事务管理

  • 通过代码显式控制事务
    java TransactionTemplate template = new TransactionTemplate(transactionManager); template.execute(new TransactionCallback<Object>() { public Object doInTransaction(TransactionStatus status) { // 事务操作 return null; } });
  • 使用 TransactionTemplate
  • 灵活但代码侵入性强

4.2 声明式事务管理

  • 通过注解或XML配置
    java @Transactional(propagation = Propagation.REQUIRED) public void transferMoney(String fromAccount, String toAccount, double amount) { // 转账操作 }
  • 使用 @Transactional
  • 代码侵入性小，易于维护

4.3 Spring事务实现原理

4.3.1 事务实现机制

  1. 代理方式：
    ```java
    // Spring使用AOP实现事务管理
    @Service
    public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;

    @Transactional
    public void method1() {
    // 有事务
    method2(); // 内部调用，method2的@Transactional注解无效
    }

    @Transactional
    public void method2() {
    // 如果是内部调用（即从method1调用），注解无效
    // 如果是外部调用（即通过代理对象调用），注解生效
    }

    // 通过代理对象调用的正确方式
    public void method3() {
    UserService proxy = AopContext.currentProxy();
    proxy.method2(); // 通过代理调用，method2的事务会生效
    }
    }
    ```

  2. 事务生效条件：
    - 方法必须是public
    - 必须通过代理对象调用（外部调用或显式获取代理对象调用）
    - 类内部直接调用不会触发新事务（因为绕过了代理）
    - 同一个类中，如果A方法有事务，直接调用同类的B方法，B方法的事务注解会失效

  3. 调用方式对比：
    ```java
    @Service
    public class UserService {
    // 场景1：外部调用
    @Transactional
    public void methodA() {
    // 有事务，因为是通过代理对象调用
    }

    // 场景2：内部调用
    @Transactional
    public void methodB() {
    methodC(); // methodC的事务注解无效，因为是内部调用
    }

    @Transactional
    public void methodC() {
    // 如果从methodB调用，注解无效
    // 如果从外部调用，注解生效
    }

    // 场景3：通过代理对象调用
    @Transactional
    public void methodD() {
    UserService proxy = AopContext.currentProxy();
    proxy.methodC(); // methodC的事务注解生效，因为通过代理调用
    }
    }

// 使用示例
@Service
public class OrderService {
@Autowired
private UserService userService; // 注入的是代理对象

   public void test() {
       userService.methodA();  // 事务生效，外部调用
       userService.methodC();  // 事务生效，外部调用
   }

}
```

  1. 事务生效的关键点：
    - 外部调用：通过其他类注入的Service调用方法，会走代理
    - 内部调用：同一个类中的方法直接调用，不会走代理
    - 代理对象调用：显式获取代理对象后调用，会走代理
    ```java
    @Service
    public class UserService {
    @Autowired
    private UserService self; // 注入代理对象

    @Transactional
    public void method1() {
    // 方式1：注入自身代理对象
    self.method2(); // 事务生效

       // 方式2：通过AopContext获取代理对象
   UserService proxy = AopContext.currentProxy();
   proxy.method2();  // 事务生效

   // 方式3：直接调用（错误方式）
   method2();  // 事务失效

    }

    @Transactional
    public void method2() {
    // 业务逻辑
    }
    }
    ```

  2. 最佳实践：
    ```java
    @Service
    public class UserService {
    @Autowired
    private UserService self; // 注入代理对象

    // 外层方法负责事务控制
    @Transactional
    public void businessMethod() {
    // 1. 使用注入的代理对象调用
    self.method1();
    self.method2();

       // 2. 或者使用AopContext（需要额外配置）
   UserService proxy = AopContext.currentProxy();
   proxy.method1();
   proxy.method2();

    }

    // 内层方法专注业务逻辑
    public void method1() {
    // 业务逻辑
    }

    public void method2() {
    // 业务逻辑
    }
    }
    ```

4.3.2 事务失效场景

  1. 方法不是public：
    java @Service public class UserService { @Transactional private void method() { // 事务无效 // ... } }

  2. 自调用问题：
    ```java
    @Service
    public class UserService {
    @Transactional
    public void method1() {
    method2(); // method2的事务不会生效
    }

    @Transactional
    public void method2() {
    // ...
    }
    }
    ```

  3. 解决自调用：
    ```java
    @Service
    public class UserService {
    @Autowired
    private UserService self; // 注入代理对象

    @Transactional
    public void method1() {
    self.method2(); // method2的事务会生效
    }

    @Transactional
    public void method2() {
    // ...
    }
    }
    ```

4.3.3 事务传播实现

@Service
public class UserService {
    @Autowired
    private OrderService orderService;

    @Transactional(propagation = Propagation.REQUIRED)
    public void createUserWithOrder() {
        // REQUIRED：使用当前事务，如果没有则创建新事务
        saveUser();
        orderService.createOrder();  // 使用当前事务
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void createUserWithNewOrder() {
        // REQUIRES_NEW：总是创建新事务
        saveUser();
        try {
            orderService.createOrder();  // 新事务
        } catch (Exception e) {
            // 订单创建失败不影响用户创建
        }
    }
}

4.3.4 事务源码实现

  1. 事务代理创建：
    java // Spring通过AOP创建事务代理 @EnableTransactionManagement // 启用注解事务管理 public class TransactionConfig { @Bean public PlatformTransactionManager transactionManager() { return new DataSourceTransactionManager(dataSource()); } }

  2. 事务拦截器：
    java // 简化的事务拦截器逻辑 public class TransactionInterceptor implements MethodInterceptor { @Override public Object invoke(MethodInvocation invocation) throws Throwable { TransactionInfo txInfo = createTransactionIfNecessary(); try { Object retVal = invocation.proceed(); txInfo.getTransactionManager().commit(); return retVal; } catch (Exception ex) { txInfo.getTransactionManager().rollback(); throw ex; } } }

4.3.5 最佳实践

  1. 正确使用事务：
    ```java
    @Service
    public class UserService {
    @Autowired
    private UserService self;

    // 外部方法控制事务
    @Transactional
    public void businessMethod() {
    self.method1();
    self.method2();
    }

    // 内部方法专注业务
    public void method1() {
    // 业务逻辑
    }

    public void method2() {
    // 业务逻辑
    }
    }
    ```

  2. 避免事务失效：
    - 确保方法是public
    - 使用代理对象调用
    - 合理设置传播行为
    - 正确处理异常

  3. 性能优化：
    - 精确控制事务范围
    - 避免长事务
    - 合理设置隔离级别
    - 使用只读事务优化查询

5. 存储过程

5.1 存储过程优势

  • 提高性能
  • 减少网络传输
  • 增强安全性
  • 便于维护
  • 可重用性高

5.2 存储过程示例

DELIMITER //

CREATE PROCEDURE get_employee(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE id = emp_id;
END //

CREATE PROCEDURE calculate_bonus(
    IN emp_id INT,
    IN base_salary DECIMAL(10,2),
    OUT bonus DECIMAL(10,2)
)
BEGIN
    DECLARE performance_score DECIMAL(3,2);

    SELECT score INTO performance_score
    FROM employee_performance
    WHERE employee_id = emp_id;

    SET bonus = base_salary * performance_score;
END //

DELIMITER ;

5.3 存储过程参数类型

  • IN: 输入参数
  • OUT: 输出参数
  • INOUT: 输入输出参数

6. 数据类型

6.1 数值类型

  • 整数类型
  • TINYINT: -128 到 127
  • SMALLINT: -32768 到 32767
  • INT: -2^31 到 2^31-1
  • BIGINT: -2^63 到 2^63-1
  • 浮点类型
  • FLOAT: 单精度浮点数
  • DOUBLE: 双精度浮点数
  • DECIMAL(M,D): 精确小数

6.2 字符串类型

  • 定长字符串: CHAR(n)
  • 固定长度，最大255字符
  • 右侧填充空格
  • 变长字符串: VARCHAR(n)
  • 可变长度，最大65535字符
  • 实际长度+1或2字节存储长度
  • 文本类型
  • TEXT: 最大65535字节
  • MEDIUMTEXT: 最大16MB
  • LONGTEXT: 最大4GB

6.3 日期时间类型

  • DATE: 日期 'YYYY-MM-DD'
  • TIME: 时间 'HH:MM:SS'
  • DATETIME: 日期时间 'YYYY-MM-DD HH:MM:SS'
  • TIMESTAMP: 时间戳
    sql CREATE TABLE logs ( id INT PRIMARY KEY, action VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

6.4 二进制类型

  • BINARY: 定长二进制
  • VARBINARY: 变长二进制
  • BLOB: 二进制大对象
  • TINYBLOB: 最大255字节
  • BLOB: 最大65KB
  • MEDIUMBLOB: 最大16MB
  • LONGBLOB: 最大4GB

7. 索引

7.1 索引类型

  • 普通索引
    sql CREATE INDEX idx_username ON users(username);
  • 唯一索引
    sql CREATE UNIQUE INDEX idx_email ON users(email);
  • 主键索引
    sql ALTER TABLE users ADD PRIMARY KEY (id);
  • 复合索引
    sql CREATE INDEX idx_name_age ON users(name, age);
  • 全文索引
    ```sql
    -- MySQL全文索引（仅InnoDB和MyISAM引擎支持）
    -- 1. 创建表时指定
    CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_content(content)
    ) ENGINE=InnoDB;

-- 2. 在已有表上创建
ALTER TABLE articles ADD FULLTEXT INDEX idx_title_content(title, content);
-- 或
CREATE FULLTEXT INDEX idx_title_content ON articles(title, content);

-- 3. 使用全文索引查询
-- 自然语言模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('搜索关键词' IN NATURAL LANGUAGE MODE);

-- BOOLEAN模式（支持更复杂的查询）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+必须包含 -不能包含 >提升权重' IN BOOLEAN MODE);

-- 查询扩展模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('关键词' WITH QUERY EXPANSION);
```

MySQL全文索引注意事项：
1. 支持的存储引擎：
- MySQL 5.6以前只有MyISAM支持
- MySQL 5.6及以后InnoDB也支持

  1. 限制条件：

    • 最小索引长度（默认3个字符）：innodb_ft_min_token_size
    • 最大索引长度（默认84个字符）：innodb_ft_max_token_size
    • 停用词：默认会过滤掉一些常见词

  2. 中文支持：

    • 需要安装ngram解析器插件
      ```sql
      -- 创建支持中文的全文索引
      CREATE TABLE articles (
      id INT PRIMARY KEY,
      title VARCHAR(200),
      content TEXT,
      FULLTEXT INDEX idx_content(content) WITH PARSER ngram
      ) ENGINE=InnoDB;

    -- 设置ngram分词长度（默认2）
    SET GLOBAL ngram_token_size = 2;
    ```

  3. 性能考虑：

    • 全文索引会占用较大空间
    • 更新开销大
    • 不适合频繁更新的列
    • 建议针对大文本字段使用

  4. 查询示例：
    ```sql
    -- 简单匹配
    SELECT * FROM articles
    WHERE MATCH(content) AGAINST('关键词');

-- 布尔模式示例
SELECT * FROM articles
WHERE MATCH(content) AGAINST('+必须包含 -排除词 >提升权重' IN BOOLEAN MODE);

-- 短语匹配
SELECT * FROM articles
WHERE MATCH(content) AGAINST('"完整短语"' IN BOOLEAN MODE);

-- 通配符匹配
SELECT * FROM articles
WHERE MATCH(content) AGAINST('关键词*' IN BOOLEAN MODE);
```

  • PostgreSQL全文搜索
    ```sql
    -- 使用tsvector和tsquery
    CREATE INDEX idx_content_fts ON articles
    USING GIN (to_tsvector('chinese', content));

-- 查询
SELECT * FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '关键词');
```

7.2 索引匹配顺序

MySQL在执行查询时，会按照以下优先级顺序选择索引：

  1. 主键索引（PRIMARY KEY）
    sql -- 主键索引优先级最高 SELECT * FROM users WHERE id = 1; -- 使用主键索引

  2. 唯一索引（UNIQUE）
    sql -- 如果有多个唯一索引，优先选择区分度高的 SELECT * FROM users WHERE email = 'test@example.com'; -- 使用唯一索引

  3. 普通索引（INDEX/KEY）
    - 单列索引
    sql SELECT * FROM users WHERE username = 'test'; -- 使用普通索引
    - 联合索引（遵循最左前缀原则）
    ```sql
    -- 假设有索引：idx_name_age_city(name, age, city)
    -- 完全匹配，使用索引
    SELECT * FROM users WHERE name = 'Tom' AND age = 20 AND city = 'Beijing';

-- 匹配前缀，使用部分索引
SELECT * FROM users WHERE name = 'Tom' AND age = 20;

-- 跳过中间列，只能使用name列的索引
SELECT * FROM users WHERE name = 'Tom' AND city = 'Beijing';

-- 不符合最左前缀，不使用索引
SELECT * FROM users WHERE age = 20 AND city = 'Beijing';
```

  1. 前缀索引
    sql -- 对字符串列的前N个字符建立的索引 CREATE INDEX idx_name_prefix ON users(name(10));

  2. 全文索引（FULLTEXT）
    sql -- 全文索引只在MATCH AGAINST语句中使用 SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword' IN NATURAL LANGUAGE MODE);

索引选择的影响因素：

  1. 区分度（Selectivity）
    sql -- 查看索引的区分度 SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

  2. 查询条件
    ```sql
    -- 范围查询可能导致索引失效
    SELECT * FROM users WHERE age > 20; -- 索引可能不被使用

-- 等值查询更容易使用索引
SELECT * FROM users WHERE age = 20; -- 索引更可能被使用
```

  1. 数据分布
    sql -- 如果数据分布不均匀，优化器可能选择全表扫描 SELECT * FROM users WHERE status = 'active'; -- 如果大部分用户都是active，可能不使用索引

  2. 查询类型
    ```sql
    -- ORDER BY 可能影响索引选择
    SELECT * FROM users WHERE age > 20 ORDER BY created_at;

-- JOIN 条件也会影响索引选择
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 20;
```

索引使用建议：

  1. 经常用于查询的列优先建立索引
    sql -- 如果经常按用户名查询 CREATE INDEX idx_username ON users(username);

  2. 区分度高的列优先建立索引
    ```sql
    -- email通常区分度高，适合建立索引
    CREATE UNIQUE INDEX idx_email ON users(email);

-- status可能只有几个值，区分度低，不适合单独建立索引
-- 但可以作为联合索引的一部分
CREATE INDEX idx_status_created ON users(status, created_at);
```

  1. 索引字段的顺序
    sql -- 把选择性高的列放在联合索引的前面 CREATE INDEX idx_email_status ON users(email, status); -- 好 CREATE INDEX idx_status_email ON users(status, email); -- 不推荐

  2. 避免冗余索引
    ```sql
    -- 已经有了(name, age)的联合索引
    CREATE INDEX idx_name_age ON users(name, age);

-- 不需要再创建单独的name索引，因为联合索引的第一列可以单独使用
CREATE INDEX idx_name ON users(name); -- 冗余索引
```

8. 性能优化

8.1 查询优化

  • 使用 EXPLAIN 分析查询
    ```sql
    -- MySQL EXPLAIN示例
    EXPLAIN SELECT * FROM users WHERE username = 'test';

-- PostgreSQL EXPLAIN ANALYZE示例
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'test';
```
- 避免 SELECT *
- 使用合适的索引
- 优化 JOIN 查询
- 合理使用子查询

8.2 数据库设计优化

  • 合理的表结构设计
  • 适当的字段类型选择
  • 规范的命名约束
  • 合理的索引设计
  • 适当的数据冗余

8.3 配置优化

  • 缓冲池大小设置
  • 连接池参数调优
  • 日志级别配置
  • 临时表空间设置
  • 并发参数调整

8.4 SQL问题排查方法

8.4.1 慢查询日志分析

  • MySQL慢查询配置：
    sql -- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; -- 设置慢查询阈值（秒） SET GLOBAL long_query_time = 1; -- 查看慢查询日志位置 SHOW VARIABLES LIKE '%slow_query%';

  • PostgreSQL慢查询配置：
    ```sql
    -- postgresql.conf配置
    log_min_duration_statement = 1000 -- 记录执行时间超过1秒的查询
    log_destination = 'csvlog' -- 设置日志格式为CSV
    logging_collector = on -- 开启日志收集
    log_directory = 'log' -- 日志目录
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' -- 日志文件名格式

-- 查看当前设置
SHOW log_min_duration_statement;
SHOW logging_collector;
SHOW log_directory;

-- 查看慢查询的几种方法：

-- 1. 直接查看日志文件
-- 日志通常位于数据目录的pg_log或log目录下
-- 可以通过以下命令查看数据目录位置：
SHOW data_directory;

-- 2. 使用pg_stat_statements扩展（推荐）
-- 首先需要安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看慢查询统计
SELECT
query,
calls,
total_exec_time,
min_exec_time,
max_exec_time,
mean_exec_time,
stddev_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 重置统计信息
SELECT pg_stat_statements_reset();

-- 3. 通过系统视图查看当前活动查询
SELECT
pid,
usename,
application_name,
client_addr,
query_start,
state,
wait_event_type,
wait_event,
query
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < current_timestamp - interval '1 second'
ORDER BY query_start;

-- 4. 使用管理视图查看统计信息
-- 查看表的访问统计
SELECT
schemaname,
relname,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch
FROM pg_stat_user_tables
WHERE (seq_scan > 0 OR idx_scan > 0)
ORDER BY seq_scan DESC;
```

注意事项：
1. 日志配置修改需要重启PostgreSQL服务才能生效
2. pg_stat_statements是最推荐的慢查询分析工具，因为它：
- 提供详细的执行时间统计
- 可以看到SQL语句的具体执行次数
- 能够分析SQL语句的性能波动
- 支持实时监控
3. 日志文件的位置可能因安装方式不同而异：
- Ubuntu: /var/log/postgresql/
- CentOS: /var/lib/pgsql/data/pg_log/
- Docker: 需要映射日志目录
4. 为了更好地进行慢查询分析，建议同时配置以下参数：
```sql
-- 记录锁等待超过1秒的语句
log_lock_waits = on
log_min_duration_lock = 1000

-- 记录自动清理和分析的相关信息
log_autovacuum_min_duration = 1000

-- 记录执行计划信息
auto_explain.log_min_duration = 1000
```

8.4.2 执行计划分析

  • MySQL执行计划：
    ```sql
    -- 基本执行计划
    EXPLAIN SELECT * FROM users WHERE id > 1000;

-- 详细执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id > 1000;

-- 分析实际执行情况
EXPLAIN ANALYZE SELECT * FROM users WHERE id > 1000;
```

  • PostgreSQL执行计划：
    ```sql
    -- 显示执行计划（不执行）
    EXPLAIN SELECT * FROM users WHERE id > 1000;

-- 显示执行计划并实际执行
EXPLAIN ANALYZE SELECT * FROM users WHERE id > 1000;

-- 显示缓冲区使用情况
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id > 1000;
```

8.4.3 性能监控工具

  1. MySQL性能监控：
    ```sql
    -- 查看当前执行的SQL
    SHOW PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看表的统计信息
SHOW TABLE STATUS;
```

  1. PostgreSQL性能监控：
    ```sql
    -- 查看活动查询
    SELECT * FROM pg_stat_activity;

-- 查看表统计信息
SELECT * FROM pg_stat_user_tables;

-- 查看索引使用情况
SELECT * FROM pg_stat_user_indexes;
```

8.4.4 常见问题排查步骤

  1. SQL语句层面：
    - 检查WHERE条件是否合理
    - 确认是否使用了适当的索引
    - 检查JOIN条件和顺序
    - 分析是否存在全表扫描
    - 评估子查询性能

  2. 数据库配置层面：
    - 检查数据库参数配置
    - 评估内存使用情况
    - 分析磁盘I/O状况
    - 检查连接池状态

  3. 应用程序层面：
    ```java
    // 使用PreparedStatement防止SQL注入
    PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM users WHERE id = ?"
    );
    pstmt.setInt(1, userId);

// 批量处理提高性能
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO users (name, age) VALUES (?, ?)"
);
for (User user : users) {
pstmt.setString(1, user.getName());
pstmt.setInt(2, user.getAge());
pstmt.addBatch();
}
pstmt.executeBatch();
```

8.4.5 性能优化建议

  1. SQL优化：
    - 使用覆盖索引
    - 避免使用SELECT *
    - 控制JOIN表数量
    - 使用合适的WHERE条件
    - 适当使用索引提示

  2. 数据库优化：
    - 定期更新统计信息
    - 合理设置缓冲池大小
    - 优化日志配置
    - 定期维护索引
    - 适当进行分区

  3. 应用优化：
    - 使用连接池
    - 实现数据缓存
    - 合理使用批处理
    - 优化事务范围
    - 实现读写分离

8.4.6 监控指标

  1. 关键性能指标：
    - 查询响应时间
    - TPS（每秒事务数）
    - QPS（每秒查询数）
    - 缓存命中率
    - 连接池使用率

  2. 系统资源指标：
    - CPU使用率
    - 内存使用情况
    - 磁盘I/O负载
    - 网络带宽使用率

  3. 数据库指标：
    - 活动连接数
    - 锁等待情况
    - 临时表使用
    - 慢查询数量
    - 索引使用率

9. 搜索技术对比

9.1 向量搜索 vs Elasticsearch搜索

9.1.1 搜索准确性比较

  1. 向量搜索（Vector Search）
    - 优势：
    • 更好的语义理解能力
    • 能捕捉上下文和概念相似性
    • 支持跨语言搜索
    • 处理同义词和相关概念更准确
      ```python
      # 向量搜索示例（使用FAISS）
      import faiss
      import numpy as np

# 创建向量索引
dimension = 128 # 向量维度
index = faiss.IndexFlatL2(dimension)

# 添加向量数据
vectors = np.random.random((1000, dimension)).astype('float32')
index.add(vectors)

# 搜索最相似的向量
query_vector = np.random.random(dimension).astype('float32')
D, I = index.search(query_vector.reshape(1, -1), k=5)
```

  1. Elasticsearch搜索
    - 优势：
    • 精确的文本匹配
    • 强大的分词和分析能力
    • 支持复杂的布尔查询
    • 良好的过滤和聚合能力
      json // Elasticsearch查询示例 { "query": { "bool": { "must": [ { "match": { "title": "搜索关键词" } }, { "match_phrase": { "content": "精确短语" } } ], "filter": [ { "term": { "status": "active" } } ] } } }

9.1.2 适用场景对比

  1. 向量搜索更适合：
    - 语义搜索需求
    - 图像相似度搜索
    - 推荐系统
    - 跨语言搜索场景
    - 概念相似性匹配

  2. Elasticsearch更适合：
    - 精确文本匹配
    - 结构化数据搜索
    - 聚合分析
    - 实时搜索
    - 全文检索

9.1.3 准确性影响因素

  1. 向量搜索准确性影响因素：
    - 向量模型质量
    - 向量维度选择
    - 训练数据质量
    - 相似度计算方法
    - 向量索引类型

  2. Elasticsearch准确性影响因素：
    - 分词器选择
    - 索引配置
    - 查询策略
    - 相关性算法
    - 字段权重设置

9.1.4 性能对比

特性 向量搜索 Elasticsearch
语义理解 ★★★★★ ★★★
精确匹配 ★★★ ★★★★★
跨语言能力 ★★★★★ ★★
计算复杂度 较高 较低
存储需求 较大 中等
实时性 较好 很好

9.1.5 选择建议

  1. 使用向量搜索的情况：
    - 需要深度语义理解
    - 处理跨语言搜索
    - 相似内容推荐
    - 多模态搜索（文本+图像）
    - 需要处理同义词和相关概念

  2. 使用Elasticsearch的情况：
    - 精确文本匹配需求
    - 结构化数据查询
    - 需要复杂的过滤和聚合
    - 实时性要求高
    - 全文检索场景

  3. 混合使用的情况：
    ```python
    # 混合搜索示例
    def hybrid_search(query, weights=(0.7, 0.3)):
    # 向量搜索结果
    vector_results = vector_search(query)

    # ES搜索结果
    es_results = es_search(query)

    # 结果融合
    final_results = merge_results(
    vector_results,
    es_results,
    weights
    )

    return final_results
    ```

总的来说，向量搜索在语义理解和相似性匹配方面更准确，而Elasticsearch在精确文本匹配和结构化查询方面更准确。选择哪种技术取决于具体的应用场景和需求。在实际应用中，往往采用混合方案来综合两者的优势。

技术分享
License:  CC BY 4.0