Java面试黄金宝典30

1. 请详细列举 30 条常用 SQL 优化方法

• 定义

SQL 优化是指通过对 SQL 语句、数据库表结构、索引等进行调整和改进，以提高 SQL 查询的执行效率，减少系统资源消耗，提升数据库整体性能的一系列操作。

• 要点

从索引运用、查询语句结构优化、数据库配置调整等多方面着手，避免全表扫描，降低磁盘 I/O 和 CPU 计算量，减少不必要的数据传输。

• 应用

在实际的数据库开发和维护中，针对不同的业务场景和数据库性能瓶颈，灵活运用这些优化方法，提升系统的响应速度和稳定性。

• SQL 代码举例及说明

1. 合理使用索引

sql

-- 为 users 表的 username 列创建索引
CREATE INDEX idx_username ON users (username);

说明：当经常根据 username 列进行查询时，该索引可加快查询速度。

1. 避免在索引列上使用函数

sql

-- 不推荐
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 推荐
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

说明：在索引列 order_date 上使用 YEAR 函数会使索引失效，而使用范围查询可利用索引。

1. 使用覆盖索引

sql

-- 创建覆盖索引
CREATE INDEX idx_user_info ON users (user_id, username);
-- 查询仅使用索引中的信息
SELECT user_id, username FROM users WHERE user_id < 100;

说明：查询的列都包含在索引中，无需回表查询数据行，提高查询效率。

1. 优化 LIKE 查询

sql

-- 不推荐
SELECT * FROM products WHERE product_name LIKE '%keyword';
-- 推荐
SELECT * FROM products WHERE product_name LIKE 'keyword%';

说明：以通配符开头的 LIKE 查询会导致全表扫描，而以固定字符开头可利用索引。

1. 使用 EXISTS 代替 IN

sql

-- 不推荐
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'China');
-- 推荐
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'China');

说明：EXISTS 效率更高，特别是子查询结果集较大时。

1. 使用 UNION ALL 代替 UNION

sql

-- 不推荐
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
-- 推荐
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

说明：UNION 会对结果去重，UNION ALL 直接合并结果，节省去重的开销。

1. 避免 SELECT *

sql

-- 不推荐
SELECT * FROM employees;
-- 推荐
SELECT employee_id, employee_name FROM employees;

说明：只查询需要的列，减少数据传输量。

1. 合理使用 JOIN

sql

-- 创建表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT
);
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);
-- 为连接列创建索引
CREATE INDEX idx_department_id ON employees (department_id);
-- 进行连接查询
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

说明：确保 JOIN 条件上有索引，可提高连接查询的效率。

1. 优化子查询

sql

-- 不推荐
SELECT * FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
-- 推荐
SELECT o.*
FROM orders o
JOIN (SELECT AVG(order_amount) AS avg_amount FROM orders) sub ON o.order_amount > sub.avg_amount;

说明：将子查询转换为 JOIN 查询，可避免子查询的重复计算。

1. 使用 LIMIT 限制结果集

sql

SELECT * FROM products LIMIT 10;

说明：减少不必要的数据传输，适用于只需要部分结果的场景。

1. 对大表进行分区

sql

-- 创建按范围分区的表
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

说明：按时间范围分区，可提高查询效率，例如查询 2024 年的销售数据只需在 p2024 分区中查找。

1. 定期清理无用数据

sql

DELETE FROM logs WHERE log_date < '2024-01-01';

说明：减少表的数据量，降低查询时的扫描行数。

1. 优化 GROUP BY 和 ORDER BY

sql

-- 为分组和排序的列创建索引
CREATE INDEX idx_group_order ON orders (customer_id, order_date);
-- 查询并分组排序
SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id
ORDER BY order_date;

说明：确保分组和排序的列上有索引，可提高分组和排序的效率。

1. 使用索引提示

sql

SELECT * FROM products USE INDEX (idx_product_name) WHERE product_name = 'iPhone';

说明：在某些情况下可以指定使用的索引，让查询优化器按照指定的索引进行查询。

1. 优化 OR 条件

sql

-- 不推荐
SELECT * FROM users WHERE user_id = 1 OR user_id = 2;
-- 推荐
SELECT * FROM users WHERE user_id IN (1, 2);

说明：OR 条件可能导致索引失效，使用 IN 可提高查询效率。

1. 使用临时表

sql

-- 创建临时表存储中间结果
CREATE TEMPORARY TABLE temp_orders AS
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
-- 查询临时表
SELECT * FROM temp_orders WHERE total_amount > 1000;

说明：对于复杂查询，先将中间结果存储在临时表中，可简化后续查询。

1. 优化 UPDATE 和 DELETE 语句

sql

-- 为更新条件列创建索引
CREATE INDEX idx_user_status ON users (status);
-- 更新数据
UPDATE users SET status = 'inactive' WHERE status = 'active';

说明：使用索引来定位要更新或删除的记录，减少扫描行数。

1. 调整数据库参数

sql

-- 修改 innodb_buffer_pool_size 参数
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

说明：根据服务器内存大小调整数据库参数，如 innodb_buffer_pool_size 可提高数据库的缓存能力。

1. 避免使用 HAVING 子句过滤数据

sql

-- 不推荐
SELECT customer_id, SUM(order_amount)
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;
-- 推荐
SELECT customer_id, SUM(order_amount)
FROM orders
WHERE order_amount > 0
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

说明：尽量在 WHERE 子句中过滤数据，减少分组和聚合的计算量。

1. 对频繁查询的结果进行缓存
   此条主要是代码层面结合缓存工具实现，如 Java 中使用 Redis 缓存，SQL 层面不直接体现，以下为简单示意。

sql

-- 假设缓存键为 "user_list"
-- 先从缓存中获取数据
-- 如果缓存中没有，则执行查询
SELECT * FROM users;
-- 将查询结果存入缓存

说明：使用 Redis 等缓存工具，减少对数据库的频繁查询。

1. 优化字符串比较

sql

-- 不推荐
SELECT * FROM products WHERE UPPER(product_name) = 'IPHONE';
-- 推荐
SELECT * FROM products WHERE product_name = 'iPhone';

说明：避免在字符串比较中使用函数，可利用索引。

1. 合理设计表结构

sql

-- 避免数据冗余，将经常一起查询的列放在一个表中
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

说明：合理设计表的字段，使用合适的数据类型，避免数据冗余。

1. 使用索引前缀

sql

-- 为较长的字符串列使用索引前缀
CREATE INDEX idx_product_name_prefix ON products (product_name(10));

说明：对于较长的字符串列，使用索引前缀可减少索引的存储空间和查询开销。

1. 优化 CASE 语句

sql

-- 不推荐复杂嵌套的 CASE 语句
SELECT 
    CASE 
        WHEN condition1 THEN 
            CASE 
                WHEN sub_condition1 THEN result1
                ELSE result2
            END
        ELSE result3
    END
FROM table;
-- 推荐简化的 CASE 语句
SELECT 
    CASE 
        WHEN condition1 AND sub_condition1 THEN result1
        WHEN condition1 THEN result2
        ELSE result3
    END
FROM table;

说明：避免复杂的嵌套 CASE 语句，提高查询性能。

1. 定期重建索引

sql

-- 重建 users 表的所有索引
ALTER TABLE users ENGINE = InnoDB;

说明：防止索引碎片化，提高索引的查询效率。

1. 使用批量操作

sql

-- 批量插入数据
INSERT INTO users (user_id, username) VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');

说明：批量插入、更新等操作可减少与数据库的交互次数，提高效率。

1. 优化 JOIN 顺序

sql

-- 小表驱动大表
SELECT *
FROM small_table s
JOIN large_table l ON s.id = l.small_table_id;

说明：让小表驱动大表，可减少中间结果集的大小，提高连接效率。

1. 避免在 WHERE 子句中进行类型转换

sql

-- 不推荐
SELECT * FROM users WHERE CAST(user_id AS CHAR) = '1';
-- 推荐
SELECT * FROM users WHERE user_id = 1;

说明：在 WHERE 子句中进行类型转换会导致索引失效。

1. 对表进行统计信息更新

sql

-- 更新 users 表的统计信息
ANALYZE TABLE users;

说明：让查询优化器有更准确的判断，生成更优的执行计划。

1. 使用存储过程

sql

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL get_user_orders(1);

说明：将复杂的业务逻辑封装在存储过程中，减少客户端与数据库的交互次数。

 

2. 请详细说明实践中如何优化 MySQL 数据库

 

• 定义

MySQL 数据库优化是指通过对 MySQL 数据库的硬件资源、配置参数、表结构设计、查询语句等方面进行调整和改进，以提高数据库的性能、稳定性和可扩展性的一系列操作。

• 要点

综合考虑数据库的各个方面，根据实际业务需求和数据库的运行情况，从硬件、配置、表结构、查询语句等多个维度进行优化。

• 应用

在实际的数据库开发和运维中，针对不同规模和业务特点的 MySQL 数据库，灵活运用各种优化方法，提升数据库的整体性能。

• 硬件优化

硬件优化主要是在服务器层面进行，SQL 层面不直接体现，以下为简单说明。

• 增加内存，提高 innodb_buffer_pool_size 等参数，减少磁盘 I/O。

sql

-- 修改 innodb_buffer_pool_size 参数
SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB

说明：增大 innodb_buffer_pool_size 可将更多的数据和索引缓存到内存中，减少磁盘读取。

• 使用高速磁盘，如 SSD，可提高数据读写速度。
• 增加 CPU 核心数，提高并发处理能力。

配置参数优化

sql

-- 根据服务器内存大小调整参数
SET GLOBAL key_buffer_size = 134217728; -- 128MB
SET GLOBAL max_connections = 500;
SET GLOBAL query_cache_size = 67108864; -- 64MB

说明：key_buffer_size 用于 MyISAM 表的索引缓存，max_connections 控制最大连接数，query_cache_size 开启查询缓存（但要注意缓存失效问题）。

表结构优化

sql

-- 合理设计表的字段，使用合适的数据类型
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT
);
-- 对大表进行分区
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);
-- 为经常用于查询、排序和连接的列创建索引
CREATE INDEX idx_product_name ON products (product_name);

说明：合理设计表结构可减少数据冗余，提高查询效率；分区可提高大表的查询性能；索引可加快查询速度。

查询语句优化

sql

-- 避免 SELECT *
SELECT product_id, product_name FROM products;
-- 优化 JOIN 语句
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;
-- 避免在索引列上使用函数
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

说明：避免 SELECT * 可减少数据传输量；优化 JOIN 语句可提高连接效率；避免在索引列上使用函数可利用索引。

数据库维护

sql

-- 定期清理无用数据
DELETE FROM logs WHERE log_date < '2024-01-01';
-- 定期重建索引
ALTER TABLE products ENGINE = InnoDB;
-- 更新表的统计信息
ANALYZE TABLE products;

说明：清理无用数据可减少表的数据量；重建索引可防止索引碎片化；更新统计信息可让查询优化器生成更优的执行计划。

 

3. 什么是 left join， right join， inner join， full join， cross join

 

• 定义

1. left join（左连接）：返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，则右表的列值为 NULL。
2. right join（右连接）：返回右表中的所有记录，以及左表中匹配的记录。如果左表中没有匹配的记录，则左表的列值为 NULL。
3. inner join（内连接）：只返回两个表中匹配的记录。
4. full join（全连接）：返回左表和右表中的所有记录，如果某表中没有匹配的记录，则对应列值为 NULL。
5. cross join（交叉连接）：返回两个表的笛卡尔积，即左表的每一行与右表的每一行都组合一次。

• 要点

不同的连接方式根据连接条件返回不同的结果集，需要根据业务需求选择合适的连接方式，以获取所需的数据。

• 应用

在实际的数据库查询中，根据不同的业务场景，使用不同的连接方式来关联多个表的数据，如在订单系统中关联订单表和用户表。

• SQL 代码举例

sql

-- 创建表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employee_department (
    employee_id INT,
    department_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 插入数据
INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'), (2, 'IT');
INSERT INTO employee_department (employee_id, department_id) VALUES (1, 1), (2, 2);

-- left join
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN employee_department ed ON e.employee_id = ed.employee_id
LEFT JOIN departments d ON ed.department_id = d.department_id;

-- right join
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN employee_department ed ON e.employee_id = ed.employee_id
RIGHT JOIN departments d ON ed.department_id = d.department_id;

-- inner join
SELECT e.employee_name, d.department_name
FROM employees e
JOIN employee_department ed ON e.employee_id = ed.employee_id
JOIN departments d ON ed.department_id = d.department_id;

-- full join（MySQL 不直接支持，可通过 UNION 模拟）
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN employee_department ed ON e.employee_id = ed.employee_id
LEFT JOIN departments d ON ed.department_id = d.department_id
UNION
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN employee_department ed ON e.employee_id = ed.employee_id
RIGHT JOIN departments d ON ed.department_id = d.department_id;

-- cross join
SELECT e.employee_name, d.department_name
FROM employees e
CROSS JOIN departments d;

 

4. 什么是数据库范式

 

• 定义

数据库范式是为了规范数据库设计，减少数据冗余，提高数据的一致性和可维护性而提出的一系列规则和标准。通过将数据库表按照一定的范式进行设计，可以使数据库结构更加合理、高效。

• 要点

不同的范式有不同的要求，通常数据库设计需要满足一定的范式，但也不是越高的范式越好，需要根据实际情况进行权衡，在数据冗余和查询性能之间找到平衡。

• 应用

在数据库设计阶段，根据业务需求和数据特点，遵循相应的范式进行表结构设计，提高数据库的质量和可维护性。

• SQL 代码举例及说明

第一范式（1NF）

sql

-- 不满足 1NF 的表
CREATE TABLE orders (
    order_id INT,
    product_names VARCHAR(255) -- 包含多个产品名称，不满足原子性
);
-- 满足 1NF 的表
CREATE TABLE orders (
    order_id INT,
    product_name VARCHAR(50)
);

说明：第一范式要求每个列都具有原子性，即不可再分。

第二范式（2NF）

sql

-- 不满足 2NF 的表
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(50),
    product_price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);
-- 满足 2NF 的表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    product_price DECIMAL(10, 2)
);
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

说明：第二范式要求在满足 1NF 的基础上，非主键列完全依赖于主键，而不是部分依赖。

第三范式（3NF）

sql

-- 不满足 3NF 的表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    department_name VARCHAR(50)
);
-- 满足 3NF 的表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

说明：第三范式要求在满足 2NF 的基础上，非主键列之间不存在传递依赖。

巴斯 - 科德范式（BCNF）

sql

-- 不满足 BCNF 的表
CREATE TABLE suppliers (
    supplier_id INT,
    product_id INT,
    supplier_name VARCHAR(50),
    PRIMARY KEY (supplier_id, product_id),
    UNIQUE (supplier_name, product_id)
);
-- 满足 BCNF 的表
CREATE TABLE supplier_products (
    supplier_id INT,
    product_id INT,
    PRIMARY KEY (supplier_id, product_id)
);
CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(50)
);

说明：巴斯 - 科德范式要求在满足 3NF 的基础上，每个非平凡的函数依赖的左边必须包含候选键。

 

5. 什么是数据库连接池

 

• 定义

数据库连接池是一种管理数据库连接的技术，它预先创建一定数量的数据库连接并存储在连接池中。当应用程序需要访问数据库时，从连接池中获取一个可用的连接，使用完后将连接归还到连接池中，而不是每次都创建和销毁连接。

• 要点

减少了频繁创建和销毁数据库连接的开销，提高了数据库的访问效率和应用程序的性能，同时可以对连接进行统一管理和监控。

• 应用

在 Java 等编程语言开发的数据库应用中广泛使用，如 Web 应用、企业级应用等，以提高系统的响应速度和并发处理能力。

以下为结合 Java 使用 HikariCP 连接池的示例

java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class DatabaseConnectionPoolExample {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(10);

        HikariDataSource dataSource = new HikariDataSource(config);

        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM users")) {
            while (resultSet.next()) {
                System.out.println(resultSet.getString("username"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

说明：通过 HikariCP 连接池获取数据库连接，执行查询操作，使用完后连接会自动归还到连接池中。

 

6. 什么是 DDL DML DCL

 

• 定义

1. DDL（数据定义语言）：用于定义数据库、表、视图、索引等数据库对象的结构，主要负责数据库对象的创建、修改和删除操作。
2. DML（数据操作语言）：用于对数据库中的数据进行插入、更新和删除操作，实现对数据的增删改功能。
3. DCL（数据控制语言）：用于控制用户对数据库对象的访问权限，确保数据的安全性和完整性。

• 要点

不同的操作类型有不同的用途，DDL 主要关注数据库对象的结构，DML 主要处理数据的内容，DCL 主要管理用户的权限。

• 应用

在数据库开发和管理中，根据不同的需求使用不同的操作类型，如创建表使用 DDL，插入数据使用 DML，授予用户权限使用 DCL。

SQL 代码举例

DDL（数据定义语言）

sql

-- 创建数据库
CREATE DATABASE mydb;
-- 使用数据库
USE mydb;
-- 创建表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);
-- 修改表结构
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
-- 删除表
DROP TABLE users;

DML（数据操作语言）

sql

-- 插入数据
INSERT INTO users (user_id, username, email) VALUES (1, 'john', 'john@example.com');
-- 更新数据
UPDATE users SET email = 'new_john@example.com' WHERE user_id = 1;
-- 删除数据
DELETE FROM users WHERE user_id = 1;

DCL（数据控制语言）

sql

-- 授予用户查询权限
GRANT SELECT ON users TO 'user1'@'localhost';
-- 授予用户所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'localhost';
-- 撤销用户查询权限
REVOKE SELECT ON users FROM 'user1'@'localhost';

 

7. 什么是 explain，举实例说明

• 定义

EXPLAIN 是 MySQL 提供的一个用于分析查询语句执行计划的工具，它可以显示查询语句如何执行，包括使用的索引、扫描的行数、连接的顺序等信息，帮助开发人员了解查询的性能瓶颈。

• 要点

通过分析 EXPLAIN 的结果，可以找出查询语句的性能瓶颈，从而进行针对性的优化，如调整索引、优化查询语句结构等。

• 应用

在数据库开发和优化过程中，对复杂或性能较差的查询语句使用 EXPLAIN 进行分析，以提高查询效率。

SQL 代码举例

sql

-- 创建表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    category VARCHAR(50)
);

-- 创建索引
CREATE INDEX idx_category ON products (category);

-- 查询语句
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

执行上述 EXPLAIN 语句后，会返回一个结果集，包含以下重要信息：

• id：查询的标识符。
• select_type：查询的类型，如 SIMPLE 表示简单查询。
• table：查询涉及的表。
• type：连接类型，如 ref 表示使用索引查找。
• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：索引使用的字节数。
• ref：哪些列或常量被用于查找索引列上的值。
• rows：估计要扫描的行数。
• Extra：额外的信息，如 Using where 表示使用了 WHERE 子句。

 

8. 如何进行分库，分表

 

• 定义

分库分表是指当数据库的数据量和访问量达到一定程度时，将数据库的数据分散存储到多个数据库（分库）或多个表（分表）中，以提高数据库的并发处理能力和可扩展性的技术手段。

• 要点

需要根据业务需求和数据特点选择合适的分库分表策略，同时要考虑数据的一致性、查询的复杂性以及后续的数据迁移和维护问题。

• 应用

在大型互联网应用、电商系统等数据量和访问量较大的场景中广泛应用，以应对高并发和大数据量的挑战。

SQL 代码举例及说明

垂直分库

sql

-- 原数据库中有用户表和订单表
-- 用户数据库
CREATE DATABASE user_db;
USE user_db;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);
-- 订单数据库
CREATE DATABASE order_db;
USE order_db;
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_amount DECIMAL(10, 2)
);

说明：将一个数据库按照业务功能拆分成多个数据库，如将用户信息和订单信息分别存储在不同的数据库中。

水平分库

sql

-- 假设按照用户 ID 范围进行水平分库
-- 数据库 1
CREATE DATABASE db1;
USE db1;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY RANGE (user_id) (
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000)
);
-- 数据库 2
CREATE DATABASE db2;
USE db2;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
) PARTITION BY RANGE (user_id) (
    PARTITION p3 VALUES LESS THAN (3000),
    PARTITION p4 VALUES LESS THAN (4000)
);

说明：将一个数据库中的数据按照一定的规则（如按用户 ID 范围）拆分到多个数据库中。

垂直分表

sql

-- 原表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    address TEXT,
    description TEXT
);
-- 垂直分表
CREATE TABLE user_basic_info (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);
CREATE TABLE user_extra_info (
    user_id INT PRIMARY KEY,
    address TEXT,
    description TEXT
);

说明：将一个表按照列进行拆分，将经常一起查询的列放在一个表中，不经常一起查询的列放在另一个表中。

水平分表

sql

-- 按照订单日期进行水平分表
CREATE TABLE orders_202401 (
    order_id INT PRIMARY KEY,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);
CREATE TABLE orders_202402 (
    order_id INT PRIMARY KEY,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);

说明：将一个表中的数据按照一定的规则（如按时间）拆分到多个表中。

 

9. 如何解决分库分表带来的坏处

 

• 定义

分库分表在提高数据库性能和可扩展性的同时，会带来一些问题，如分布式事务、跨库查询、数据迁移等，解决这些问题的一系列技术和策略即为解决分库分表带来坏处的方法。

• 要点

针对不同的问题，采用不同的解决方案，同时要考虑方案的可行性、性能开销和对业务的影响。

• 应用

在实施分库分表后，当遇到分布式事务、跨库查询等问题时，运用相应的解决方法来保证系统的正常运行和数据的一致性。

分布式事务问题

可使用分布式事务框架 Seata 来解决，以下为简单的 Java 代码示例结合 SQL 示意，SQL 层面主要是业务操作。

java

import io.seata.spring.annotation.GlobalTransactional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class OrderService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GlobalTransactional
    public void createOrder() {
        // 插入订单数据
        jdbcTemplate.update("INSERT INTO orders (order_id, user_id, order_amount) VALUES (1, 1, 100.0)");
        // 扣减库存，可能涉及另一个数据库
        jdbcTemplate.update("UPDATE products SET stock = stock - 1 WHERE product_id = 1");
    }
}

说明：Seata 通过全局事务注解 @GlobalTransactional 来管理分布式事务，确保多个数据库操作的一致性。

 

跨库查询问题

可使用中间件 ShardingSphere 来解决，以下为简单的配置和 SQL 示例。

yaml

# ShardingSphere 配置
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://localhost:3306/db0
        username: root
        password: password
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://localhost:3306/db1
        username: root
        password: password
    rules:
      sharding:
        tables:
          users:
            actual-data-nodes: ds$->{0..1}.users
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: database-inline
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: table-inline
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: users

sql

-- 跨库查询
SELECT * FROM users;

说明：ShardingSphere 会根据配置的规则对跨库查询进行处理，将查询路由到相应的数据库和表中。

数据迁移问题

采用双写迁移的方式，以下为简单的 Java 代码示例结合 SQL 示意。

java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class DataMigrationService {

    @Autowired
    private JdbcTemplate oldJdbcTemplate;
    @Autowired
    private JdbcTemplate newJdbcTemplate;

    public void migrateData() {
        // 从旧数据库查询数据
        String sql = "SELECT * FROM users";
        oldJdbcTemplate.query(sql, (rs, rowNum) -> {
            int userId = rs.getInt("user_id");
            String username = rs.getString("username");
            String email = rs.getString("email");
            // 同时写入新数据库
            newJdbcTemplate.update("INSERT INTO users (user_id, username, email) VALUES 
            (?,?,?)", userId, username, email);
            return null;
        });
    }
}

sql

-- 旧数据库
CREATE DATABASE old_db;
USE old_db;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);
-- 新数据库
CREATE DATABASE new_db;
USE new_db;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

说明：双写迁移时，在业务代码里，从旧数据库查询数据后，同时将数据写入新数据库。这样能保证在迁移期间新旧数据库的数据一致。等数据迁移完成，并且验证无误后，就可以把业务切换到新数据库。

 

10. 什么是封锁

 

• 定义

封锁是数据库管理系统中用于控制并发访问的一种机制。它通过对数据对象（如表、行等）加锁，来限制不同事务对这些数据对象的并发操作，以此保证数据的一致性和完整性，避免并发访问可能引发的数据冲突问题。

• 要点

不同的锁类型具有不同的特点和用途，需要依据实际情况来选择合适的锁类型。同时，要合理运用封锁机制，避免出现死锁等问题，因为死锁会导致事务无法正常执行，降低数据库的性能。

• 应用

在高并发的数据库环境中，合理使用封锁机制可以提高数据库的并发处理能力。例如，在电商系统的订单处理、银行系统的转账操作等场景中，需要使用封锁机制来保证数据的一致性和准确性。

SQL 代码举例

• 共享锁（S 锁）

sql

-- 开启事务
START TRANSACTION;
-- 对 users 表的某一行加共享锁
SELECT * FROM users WHERE user_id = 1 LOCK IN SHARE MODE;
-- 可以继续执行其他查询操作
SELECT * FROM orders WHERE user_id = 1;
-- 提交事务
COMMIT;

说明：共享锁也叫读锁，多个事务可以同时对一个数据对象加共享锁，用于并发读取数据。在上述示例中，使用 LOCK IN SHARE MODE 对 user_id 为 1 的行加了共享锁，在事务提交之前，其他事务也可以对该行加共享锁进行读取操作，但不能加排他锁进行修改操作。

• 排他锁（X 锁）

sql

-- 开启事务
START TRANSACTION;
-- 对 users 表的某一行加排他锁
SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
-- 修改数据
UPDATE users SET username = 'new_username' WHERE user_id = 1;
-- 提交事务
COMMIT;

说明：排他锁也叫写锁，一个数据对象只能被一个事务加排他锁，用于修改数据。在加排他锁期间，其他事务不能对该数据对象加任何锁，直到持有排他锁的事务提交或回滚。

• 意向锁

意向锁用于表示事务对某个数据对象的子对象（如行）有某种类型的锁。虽然在 SQL 中一般不需要显式使用意向锁，但在数据库内部会自动处理。例如，当一个事务要对表中的某一行加排他锁时，数据库会先对表加意向排他锁。以下是一个隐示意图向锁存在的示例：

sql

-- 事务 1：对表中的某一行加排他锁
START TRANSACTION;
SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
-- 此时数据库会自动对 users 表加意向排他锁
-- 事务 2：尝试对表加共享锁，会被阻塞
START TRANSACTION;
SELECT * FROM users LOCK IN SHARE MODE;
-- 直到事务 1 提交或回滚
COMMIT;

说明：事务 1 对 user_id 为 1 的行加排他锁时，数据库会自动对 users 表加意向排他锁。此时事务 2 尝试对表加共享锁会被阻塞，因为意向排他锁和共享锁不兼容。

• 行锁

sql

-- 开启事务
START TRANSACTION;
-- 对 users 表的某一行加行锁（使用 InnoDB 存储引擎时，默认的行级锁）
UPDATE users SET email = 'new_email@example.com' WHERE user_id = 1;
-- 提交事务
COMMIT;

说明：行锁对表中的某一行数据加锁，粒度较小，并发性能较高。在上述示例中，只对 user_id 为 1 的行加了锁，其他行的数据可以被其他事务并发访问。

• 表锁

sql

-- 对 users 表加表锁
LOCK TABLES users WRITE;
-- 执行数据操作
INSERT INTO users (user_id, username) VALUES (2, 'user2');
-- 释放表锁
UNLOCK TABLES;

说明：表锁对整个表加锁，粒度较大，并发性能较低。在加表锁期间，其他事务不能对该表进行任何操作，直到表锁被释放。

 友情提示：本文已经整理成文档，可以到如下链接免积分下载阅读

https://download.youkuaiyun.com/download/ylfhpy/90560627