✨✨✨这里是小韩学长yyds的BLOG(喜欢作者的点个关注吧)

✨✨✨想要了解更多内容可以访问我的主页 小韩学长yyds-CSDN博客

 

目录

一、引言

二、MySQL 建表基础

2.1 创建数据库

2.2 选择数据库

2.3 CREATE TABLE 语句详解

2.4 存储引擎选择

三、表字段设计原则

3.1 数据类型选择

3.2 避免 NULL 值字段

3.3 日期和时间类型

3.4 字段命名规范

四、关联表的建立时机与方法

4.1 一对多关系

4.2 多对多关系

4.3 一对一关系

五、索引的创建与优化

5.1 索引的作用与原理

5.2 普通索引创建

5.3 联合索引创建

5.4 索引优化策略

六、大数据量下的查询优化实践

6.1 数据分区

6.2 分库分表

6.3 查询语句优化

 

一、引言

在当今这个大数据时代，数据量正以前所未有的速度增长，对数据的高效管理和查询变得至关重要。作为一名 Java 程序员，在开发项目时，MySQL 数据库是我们常用的工具之一。而其中表的设计、关联表的建立、字段的设计以及索引（包括联合索引）的创建，这些环节对于数据库的性能有着决定性的影响，尤其是在数据量巨大的情况下，更是关乎整个系统的响应速度和稳定性。接下来，就让我们深入探讨如何做好这些关键工作。

二、MySQL 建表基础

2.1 创建数据库

在 MySQL 中，使用CREATE DATABASE语句来创建数据库。其基本语法如下：

CREATE DATABASE database_name;

例如，创建一个名为test_db的数据库：

CREATE DATABASE test_db;

当我们不确定数据库是否已经存在时，如果直接使用上述语句创建数据库，可能会报错。为了避免这种情况，可以使用IF NOT EXISTS子句，如下所示：

CREATE DATABASE IF NOT EXISTS test_db;

这样，只有当test_db数据库不存在时，才会执行创建操作，从而避免了因数据库已存在而导致的错误。

2.2 选择数据库

创建好数据库后，需要使用USE语句来选择要操作的数据库。语法如下：

USE database_name;

例如，选择刚刚创建的test_db数据库：

USE test_db;

执行该语句后，后续的数据库操作（如创建表、插入数据等）都将在test_db数据库中进行。

2.3 CREATE TABLE 语句详解

使用CREATE TABLE语句来创建表，其语法结构较为复杂，主要包括表名、列名、数据类型以及各种约束。基本语法如下：

CREATE TABLE table_name (

column1 data_type constraint,

column2 data_type constraint,

...

PRIMARY KEY (column_list)

);
  • 表名：即要创建的表的名称，需遵循 MySQL 的命名规则，一般以字母开头，由字母、数字和下划线组成，不能使用 MySQL 的保留字。
  • 列名：表中的每一列都需要有一个唯一的名称，同样遵循命名规则。
  • 数据类型：MySQL 提供了丰富的数据类型，常见的有：
    •  整数类型：如INT（4 字节整数）、BIGINT（8 字节整数）等。例如，创建一个存储用户年龄的列，可以使用age INT。
    • 浮点数类型：FLOAT（单精度浮点数）、DOUBLE（双精度浮点数）。若要存储商品价格，可使用price DOUBLE。
    • 字符串类型：CHAR（固定长度字符串）、VARCHAR（可变长度字符串）。比如存储用户姓名，name VARCHAR(50) 较为合适，这里50表示该字段最多可存储 50 个字符。
    • 日期时间类型：DATE（仅存储日期）、DATETIME（存储日期和时间）。记录订单创建时间可使用create_time DATETIME。
  • 约束：用于限制表中数据的完整性和有效性，常见的约束有：
    • NOT NULL：表示该列的值不能为空。例如email VARCHAR(100) NOT NULL，确保email列必须有值。
    • UNIQUE：保证列中的值是唯一的。如phone_number VARCHAR(20) UNIQUE，防止表中出现重复的电话号码。
    • PRIMARY KEY：主键约束，用于唯一标识表中的每一行数据，一个表只能有一个主键。它可以是单个列，也可以是多个列的组合（即复合主键）。例如id INT PRIMARY KEY，将id列设为主键；或者PRIMARY KEY (column1, column2)，以column1和column2的组合作为主键。
    • FOREIGN KEY：外键约束，用于建立表与表之间的关联关系，后面在讲解关联表时会详细介绍。

例如，创建一个users表，包含id（主键）、name、age和email字段：

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

age INT,

email VARCHAR(100) UNIQUE

);

在这个例子中，id列使用AUTO_INCREMENT关键字，使其成为自增长列，每插入一条新记录，id值自动递增。

2.4 存储引擎选择

MySQL 支持多种存储引擎，不同的存储引擎具有不同的特性和适用场景。常见的存储引擎有 InnoDB 和 MyISAM，InnoDB 是 MySQL 5.5.5 版本之后的默认存储引擎 ，下面来对比一下它们的特点：

  • 事务支持：
    • InnoDB：支持事务，具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），即 ACID 特性。这使得它在处理需要保证数据完整性和一致性的操作（如银行转账、电商订单处理等）时非常可靠。例如，在一个涉及多个表更新的转账操作中，InnoDB 能确保要么所有操作都成功执行，要么都回滚，不会出现部分更新的情况。
    • MyISAM：不支持事务，适用于对事务要求不高，主要进行读操作的场景，如一些简单的日志记录、统计报表等。
  • 锁机制：
    • InnoDB：使用行级锁，在并发操作时，只锁定被操作的行，而不是整个表。这大大提高了并发性能，多个事务可以同时对不同的行进行读写操作，减少了锁冲突。例如，在一个高并发的电商系统中，多个用户同时购买不同商品，InnoDB 的行级锁能让这些操作高效进行，互不干扰。
    • MyISAM：使用表级锁，当一个事务对表进行写操作时，会锁定整个表，其他事务只能等待。在高并发写入场景下，容易出现性能瓶颈。比如在一个频繁更新数据的表中，使用 MyISAM 存储引擎，可能会导致大量的等待，降低系统性能。
  • 外键支持：
    • InnoDB：支持外键约束，可以方便地建立表与表之间的关联关系，确保数据的完整性。例如，在一个订单管理系统中，orders表和customers表可以通过外键关联，保证订单中的客户信息与客户表中的信息一致，避免出现无效的客户引用。
    • MyISAM：不支持外键约束，如果需要实现表间关联，需要在应用程序层面进行额外的逻辑处理。
  • 索引结构：
    • InnoDB：使用 B + 树索引结构，这种结构适合大规模数据的存储和查询，能够快速定位数据。其聚集索引将数据和索引存储在一起，主键查询性能非常高；辅助索引则通过主键值来定位数据。
    • MyISAM：使用 B 树索引结构，虽然在某些情况下查询性能也不错，但对于大规模数据的处理能力相对较弱。

综上所述，InnoDB 在事务处理、并发性能和数据完整性方面具有明显优势，因此成为了 MySQL 的默认存储引擎，适用于大多数需要高并发和数据一致性保证的应用场景。而 MyISAM 在一些简单的只读场景或对事务要求不高的情况下，可能因其简单高效的特点仍有一定的应用价值，但随着技术的发展和应用场景的复杂性增加，InnoDB 的使用更为广泛。

三、表字段设计原则

3.1 数据类型选择

在设计表字段时，选择合适的数据类型至关重要，它不仅影响数据的存储效率，还会对查询性能产生影响。

  • 整数类型：对于表示数量、ID 等整数值的数据，应根据数据的范围选择合适的整数类型。如TINYINT（1 字节，范围较小，适用于存储如性别（0 代表男，1 代表女）等取值范围有限的数据）、SMALLINT（2 字节）、MEDIUMINT（3 字节）、INT（4 字节，通常用于一般的整数，如用户 ID、商品数量等）和BIGINT（8 字节，用于存储非常大的整数，如系统运行的总毫秒数）。如果选择的数据类型范围过小，可能会导致数据溢出；而选择过大的数据类型，则会浪费存储空间。例如，在一个统计文章阅读量的字段中，如果预计阅读量不会超过 2147483647（INT的最大值），就可以使用INT类型，而不是BIGINT。
  • 实数类型：当需要存储带有小数的数据时，有FLOAT（单精度浮点数，4 字节，精度有限，适用于对精度要求不高的场景，如存储商品的大致价格）和DOUBLE（双精度浮点数，8 字节，精度更高，适用于对精度要求较高的科学计算、金融计算等场景，如存储股票价格、汇率等）。需要注意的是，浮点数在计算机中是以近似值存储的，可能会存在精度误差。如果对精度要求极高，如涉及金额计算，建议使用DECIMAL类型。DECIMAL类型可以指定精度和小数位数，如DECIMAL(10, 2)表示总共有 10 位数字，其中小数部分占 2 位，常用于财务数据的存储，确保金额计算的准确性。
  • 字符串类型：CHAR和VARCHAR是常用的字符串类型。CHAR是固定长度字符串，它会按照定义的长度分配存储空间，如果存储的字符串长度不足定义长度，会用空格填充；例如CHAR(10)，无论存储的字符串是 “abc” 还是 “abcdefghij”，都会占用 10 个字符的存储空间。因此，CHAR适用于存储长度固定且较短的字符串，如身份证号码（18 位固定长度）、邮政编码（6 位固定长度）等。VARCHAR是可变长度字符串，它根据实际存储的字符串长度分配存储空间，加上额外的 1 - 2 个字节用于记录字符串的长度（当字符串长度小于 255 时，使用 1 个字节记录长度；大于等于 255 时，使用 2 个字节记录长度）。比如VARCHAR(50)，如果存储的字符串是 “hello”，实际占用的存储空间除了 “hello” 这 5 个字符外，还需要 1 - 2 个字节记录长度。所以VARCHAR适用于存储长度不固定的字符串，如用户姓名、文章内容等。另外，对于非常长的文本，可以使用TEXT类型（如TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，它们的存储上限不同），但TEXT类型在某些情况下可能会影响查询性能，因为对TEXT类型字段进行索引时会有一些限制。
  • 日期和时间类型：MySQL 提供了DATE（只存储日期，格式为YYYY - MM - DD，如 “2024 - 10 - 01”）、TIME（只存储时间，格式为HH:MM:SS，如 “12:30:00”）、DATETIME（存储日期和时间，格式为YYYY - MM - DD HH:MM:SS，如 “2024 - 10 - 01 12:30:00”，取值范围从 “1000 - 01 - 01 00:00:00” 到 “9999 - 12 - 31 23:59:59”）、TIMESTAMP（也存储日期和时间，格式与DATETIME相同，但取值范围较小，从 “1970 - 01 - 01 00:00:01” 到 “2038 - 01 - 19 03:14:07”，它会自动根据服务器的时区进行转换，并且在插入或更新数据时，如果没有指定该字段的值，会自动更新为当前时间）等类型。如果只需要记录日期，使用DATE类型即可；如果需要记录精确到秒的时间，DATETIME或TIMESTAMP更合适，具体选择可根据实际需求和业务场景来决定。例如，在记录用户注册时间时，DATETIME或TIMESTAMP都可以满足需求，但如果需要在不同时区下保持时间的一致性，TIMESTAMP可能更方便；而在记录商品的生产日期时，只需要日期信息，使用DATE类型即可。

3.2 避免 NULL 值字段

在 MySQL 中，尽量避免设置字段为NULL，而是使用默认值替代NULL。这是因为：

  • 查询优化困难：当查询中包含可为NULL的列时，MySQL 的查询优化器更难优化查询。因为可为NULL的列使得索引、索引统计和值比较都更复杂。例如，假设有一个users表，其中email字段可为NULL，当执行SELECT * FROM users WHERE email = 'example@test.com'查询时，MySQL 不仅要在email列的索引中查找非NULL值为'example@test.com'的记录，还要处理NULL值的情况，这增加了查询的复杂性和执行时间。
  • 索引效率降低：当可为NULL的列被索引时，每个索引记录需要一个额外的字节来标识该值是否为NULL。在 MyISAM 存储引擎中，甚至可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引，这会增加索引的存储空间和维护成本，降低索引的效率 。比如，创建一个包含name字段（可为NULL）的索引，和一个name字段（不可为NULL）的索引，在存储和查询时，可为NULL字段的索引会占用更多空间，查询时也会更复杂。
  • 数据处理不便：在进行数据处理时，NULL值可能会带来一些意想不到的结果。例如，使用CONCAT函数拼接字符串时，如果其中一个字段为NULL，则返回结果为NULL。在统计函数（如COUNT）中，NULL值不会被计入统计结果。例如，有一个products表，description字段可为NULL，执行SELECT COUNT(description) FROM products时，只会统计description不为NULL的记录数。

因此，在设计表字段时，除非确实需要表示未知或缺失的值，否则应尽量将字段设置为NOT NULL，并为其设置合理的默认值。例如，对于表示状态的字段，可以使用0表示未处理，1表示已处理，而不是使用NULL来表示状态未知；对于日期时间字段，如果没有明确的时间，可以设置一个默认值，如 “1970 - 01 - 01 00:00:00” 。这样不仅可以提高数据库的性能和稳定性，还能使数据处理更加方便和准确。

3.3 日期和时间类型

在 Java 开发中，经常会涉及到日期和时间的处理，而 MySQL 提供了多种日期和时间类型来满足不同的需求。以下是 Java 中的日期和时间类型与 MySQL 中对应类型的关系及使用场景：

  • Java 的java.util.Date和java.sql.Date：java.util.Date是 Java 中表示日期和时间的基础类，它包含了从 1970 年 1 月 1 日 00:00:00 GMT 到当前时间的毫秒数。而java.sql.Date是java.util.Date的子类，主要用于与数据库交互，它只保留日期部分，时间部分会被截断为 00:00:00。在 MySQL 中，java.sql.Date通常对应DATE类型。例如，在 Java 代码中获取当前日期并插入到 MySQL 数据库的DATE类型字段中： 
    import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.Date;

public class DateExample {

public static void main(String[] args) {

Date now = new Date();

java.sql.Date sqlDate = new java.sql.Date(now.getTime());

String url = "jdbc:mysql://localhost:3306/test_db";

String username = "root";

String password = "password";

String sql = "INSERT INTO example_table (date_column) VALUES (?)";

try (Connection connection = DriverManager.getConnection(url, username, password);

PreparedStatement statement = connection.prepareStatement(sql)) {

statement.setDate(1, sqlDate);

statement.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

这种情况下，date_column字段在 MySQL 中应为DATE类型，用于存储只包含日期的信息，如员工的入职日期、商品的生产日期等。

  • Java 的java.util.Calendar和java.time.LocalDateTime：java.util.Calendar是一个抽象类，用于对日期和时间进行操作和计算。java.time.LocalDateTime是 Java 8 引入的日期和时间类，它提供了更丰富和方便的日期时间处理方法。在 MySQL 中，它们通常对应DATETIME或TIMESTAMP类型。DATETIME类型可以存储从 “1000 - 01 - 01 00:00:00” 到 “9999 - 12 - 31 23:59:59” 的日期和时间，而TIMESTAMP类型存储的时间范围较小，从 “1970 - 01 - 01 00:00:01” 到 “2038 - 01 - 19 03:14:07”，并且会自动根据服务器的时区进行转换，在插入或更新数据时，如果未指定该字段的值，会自动更新为当前时间。例如，使用java.time.LocalDateTime获取当前日期和时间并插入到 MySQL 数据库中：
import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.time.LocalDateTime;

public class LocalDateTimeExample {

public static void main(String[] args) {

LocalDateTime now = LocalDateTime.now();

String url = "jdbc:mysql://localhost:3306/test_db";

String username = "root";

String password = "password";

String sql = "INSERT INTO example_table (datetime_column) VALUES (?)";

try (Connection connection = DriverManager.getConnection(url, username, password);

PreparedStatement statement = connection.prepareStatement(sql)) {

statement.setObject(1, now);

statement.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

这里的datetime_column字段可以是DATETIME或TIMESTAMP类型，具体选择取决于业务需求。如果需要存储较大时间范围的数据，且对时间的自动更新和时区转换没有特殊要求，DATETIME更合适；如果需要自动更新时间和考虑时区问题，TIMESTAMP则是更好的选择，常用于记录数据的创建时间、最后更新时间等。

3.4 字段命名规范

良好的字段命名规范有助于提高代码的可读性和可维护性，以下是一些建议：

  • 使用下划线命名：采用下划线分隔单词的命名方式，即蛇形命名法（snake_case），这是 MySQL 中常用的命名风格。例如，user_name、order_id、create_time等，这种命名方式清晰明了，易于理解。避免使用驼峰命名法（camelCase），虽然在 Java 代码中驼峰命名法很常见，但在 MySQL 字段命名中，为了保持一致性和规范性，不建议使用。
  • 遵循项目统一规范：在一个项目中，应制定统一的字段命名规范，并严格遵守。例如，对于表示 ID 的字段，统一使用id作为后缀，如user_id、product_id、category_id等；对于表示创建时间的字段，统一使用create_time，表示更新时间的字段统一使用update_time等。这样在整个项目中，开发人员可以快速识别和理解字段的含义，减少因命名不一致而导致的错误和误解。
  • 见名知义：字段名应能够准确反映其存储的数据内容，具有明确的语义。例如，使用phone_number表示电话号码，email_address表示邮箱地址，product_price表示商品价格等。避免使用过于简单或模糊的命名，如col1、data等，这样的命名在后期维护和阅读代码时，很难理解其具体含义。
  • 避免使用保留字：MySQL 中有一些保留字，如SELECT、INSERT、UPDATE、DELETE、WHERE等，这些保留字具有特定的语法含义，不能直接用作字段名。如果使用保留字作为字段名，可能会导致 SQL 语句解析错误。如果确实需要使用与保留字相同的名称，可以使用反引号（`）将字段名括起来，但这并不是一个好的做法，最好还是选择其他合适的名称。

四、关联表的建立时机与方法

在数据库设计中，建立关联表是非常重要的环节，它能够有效地组织和管理数据之间的关系，提高数据的完整性和查询效率。下面我们来详细探讨在不同关系下如何建立关联表。

4.1 一对多关系

一对多关系是数据库中最常见的关系之一。判断两个表是否存在一对多关系，可以通过换位思考的方式。例如，有员工表employees和部门表departments：

-- 创建部门表

CREATE TABLE departments (

department_id INT AUTO_INCREMENT PRIMARY KEY,

department_name VARCHAR(50) NOT NULL

);

-- 创建员工表

CREATE TABLE employees (

employee_id INT AUTO_INCREMENT PRIMARY KEY,

employee_name VARCHAR(50) NOT NULL,

department_id INT,

-- 外键约束，关联departments表的department_id

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

从员工表角度看，一名员工只能属于一个部门；从部门表角度看，一个部门可以有多名员工。这种情况下，部门表和员工表之间就是一对多关系，外键字段department_id建在多的一方，即员工表中 。通过这种方式，当查询某个部门的所有员工时，可以通过关联条件轻松实现：

4.2 多对多关系

多对多关系相对复杂一些，判断方法同样是换位思考。以图书表books和作者表authors为例：

-- 创建图书表

CREATE TABLE books (

book_id INT AUTO_INCREMENT PRIMARY KEY,

book_title VARCHAR(100) NOT NULL

);

-- 创建作者表

CREATE TABLE authors (

author_id INT AUTO_INCREMENT PRIMARY KEY,

author_name VARCHAR(50) NOT NULL

);

一本书可以有多个作者，一个作者也可以写多本书，两边都 “可以”，这就是多对多关系。对于多对多关系，不能直接在原表上建立外键，需要创建第三张关系表，例如book_authors：

-- 创建关系表

CREATE TABLE book_authors (

id INT AUTO_INCREMENT PRIMARY KEY,

book_id INT,

author_id INT,

FOREIGN KEY (book_id) REFERENCES books(book_id),

FOREIGN KEY (author_id) REFERENCES authors(author_id)

);

这样，通过book_authors关系表，就可以建立图书和作者之间的多对多关系。当查询某本书的所有作者，或者某个作者的所有作品时，就可以通过这三张表的关联查询来实现：

-- 查询《Java核心技术》这本书的所有作者

SELECT a.author_name

FROM books b

JOIN book_authors ba ON b.book_id = ba.book_id

JOIN authors a ON ba.author_id = a.author_id

WHERE b.book_title = 'Java核心技术';

4.3 一对一关系

一对一关系相对较少见，其特点是两个表之间的关联是一一对应的。判断时，同样从两个表的角度思考。例如，用户表users和用户详情表user_details：

-- 创建用户表

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL,

user_detail_id INT UNIQUE,

FOREIGN KEY (user_detail_id) REFERENCES user_details(user_detail_id)

);

-- 创建用户详情表

CREATE TABLE user_details (

user_detail_id INT AUTO_INCREMENT PRIMARY KEY,

address VARCHAR(100),

phone_number VARCHAR(20)

);

从用户表角度，一个用户只能有一份详细信息；从用户详情表角度，一份详细信息也只能对应一个用户，两边都 “不可以”，这就是一对一关系。对于一对一关系，外键字段可以建在任意一方，但通常建议建在查询频率较高的一方，这样在查询时可以减少连接操作，提高查询效率 。例如，如果经常通过用户信息查询用户详情，就将外键建在用户表中；如果经常通过详情信息查询用户，就将外键建在用户详情表中。

五、索引的创建与优化

5.1 索引的作用与原理

索引在数据库中就像是一本书的目录，它能够极大地提高查询效率。在没有索引的情况下，当执行查询操作时，数据库需要对表中的每一条数据进行扫描并对比，即全表扫描，这种方式效率极低，尤其是在数据量巨大时，查询可能会花费很长时间 。而索引通过特定的数据结构（如 B + 树），将数据按照索引字段进行排序，使得数据库系统可以直接定位到满足查询条件的数据，减少了搜索的时间和计算量。

以多表连接查询为例，假设有orders表和customers表，orders表中有customer_id字段关联customers表的id字段。当执行SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.city = '北京'查询时，如果customers表的city字段和orders表的customer_id字段上都没有索引，那么数据库需要对customers表进行全表扫描，找出所有城市为 “北京” 的客户，然后再对orders表进行全表扫描，将每个订单与符合条件的客户进行匹配，这个过程非常耗时。但如果在customers表的city字段和orders表的customer_id字段上创建了索引，数据库就可以利用索引快速定位到customers表中城市为 “北京” 的客户记录，然后通过customer_id索引快速找到orders表中对应的订单记录，大大提高了连接查询的效率。

5.2 普通索引创建

在 MySQL 中，使用CREATE INDEX语句来创建普通索引，其基本语法如下：

CREATE INDEX index_name ON table_name (column_name);
  • index_name：指定索引的名称，命名应遵循一定的规范，通常以idx_开头，后面跟表名和字段名，以便于识别和管理，如idx_users_name表示users表中name字段的索引。
  • table_name：要创建索引的表名。
  • column_name：需要创建索引的字段名。

例如，在users表的email字段上创建一个普通索引：

CREATE INDEX idx_users_email ON users (email);

这样，在对users表进行涉及email字段的查询时，如SELECT * FROM users WHERE email = 'example@test.com'，MySQL 就可以利用这个索引快速定位到匹配的记录，提高查询速度。

5.3 联合索引创建

联合索引是指在多个字段上创建的索引，它的创建语法与普通索引类似，但包含多个字段。假设有一个products表，包含category_id、price和sales字段，经常需要进行按照类别和价格范围查询商品，并按照销量排序的操作，如SELECT * FROM products WHERE category_id = 1 AND price BETWEEN 10 AND 100 ORDER BY sales DESC。为了提高这种查询的效率，可以创建一个联合索引：

CREATE INDEX idx_category_price_sales ON products (category_id, price, sales);

在创建联合索引时，需要注意字段的顺序。联合索引遵循最左匹配原则，即查询条件必须从左到右依次匹配联合索引中的字段，索引才会生效。在上述例子中，category_id、price和sales的顺序是根据查询频率和范围来确定的，因为查询中经常先指定类别，再指定价格范围，最后按照销量排序，所以将category_id放在最左边，能更好地利用索引。如果查询条件是SELECT * FROM products WHERE price BETWEEN 10 AND 100，由于没有最左边的category_id字段匹配，这个联合索引就不会生效。

5.4 索引优化策略

  • 选择合适字段创建索引：应选择经常出现在查询条件（如WHERE子句）、连接条件（如JOIN子句）以及排序（ORDER BY）和分组（GROUP BY）字段上创建索引。例如，在一个电商系统中，orders表经常根据customer_id查询某个客户的订单，那么在customer_id字段上创建索引可以显著提高查询效率；如果经常按照订单金额进行统计分析，对order_amount字段创建索引也很有必要。
  • 避免过多索引：虽然索引能提高查询性能，但并不是索引越多越好。每个索引都会占用额外的磁盘空间，并且在数据插入、更新和删除时，需要维护索引，这会增加写操作的开销。过多的索引还可能导致查询优化器在选择索引时变得复杂，降低查询性能。因此，要根据实际的查询需求，合理创建索引，删除那些不再使用的索引。
  • 使用短索引：对于字符串类型的字段，在满足查询需求的前提下，尽量使用短索引。因为短索引占用的存储空间更小，查询时的 I/O 操作也更少，能够提高查询效率。例如，对于一个存储城市名称的字段，如果城市名称长度一般不超过 20 个字符，那么可以只对前 10 个字符创建索引，即CREATE INDEX idx_city ON table_name (city(10)) ，这样既能满足大部分查询需求，又能减少索引的存储空间。
  • 避免索引列计算：在查询条件中，应避免对索引列进行函数计算或其他表达式运算。因为这样会导致索引失效，数据库无法利用索引快速定位数据，只能进行全表扫描。例如，SELECT * FROM users WHERE YEAR(birthday) = 1990，这种对birthday字段进行YEAR函数计算的查询，索引不会生效；应尽量改为SELECT * FROM users WHERE birthday BETWEEN '1990 - 01 - 01' AND '1990 - 12 - 31'，让查询条件直接作用于索引列。
  • 覆盖索引：尽量使用覆盖索引，即查询所需的字段都包含在索引中，这样可以避免回表操作，进一步提高查询效率。例如，有一个products表，包含id、name和price字段，创建了CREATE INDEX idx_name_price ON products (name, price)索引，如果执行SELECT name, price FROM products WHERE name = '手机'查询，由于查询的字段name和price都在索引中，数据库可以直接从索引中获取数据，而不需要再回表查询完整的记录，减少了 I/O 操作，提高了查询速度。

六、大数据量下的查询优化实践

6.1 数据分区

数据分区是将一个大表按照某种规则划分成多个较小的分区，每个分区可以独立存储和管理。其作用主要体现在以下几个方面：

  • 提高查询性能：当查询条件涉及分区字段时，数据库可以直接定位到相关分区，而不必扫描整个表，大大减少了数据扫描量，从而提高查询速度。例如，在一个存储电商订单的表中，按照订单日期进行分区，当查询某一天或某一段时间内的订单时，数据库只需在对应的日期分区中查找，而不需要扫描整个大表。
  • 便于管理和维护：对大表进行分区后，在进行数据备份、恢复、删除等操作时，可以针对单个分区进行，提高了操作的效率和灵活性。比如，删除历史订单数据时，可以直接删除某个时间分区的数据，而不会影响其他分区的数据。

在 MySQL 中，常见的数据分区类型有：

  • 范围分区：按照某个字段的取值范围进行分区。例如，对orders表按照order_date字段进行范围分区，将不同时间段的订单数据存储在不同分区中：
CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

order_amount DECIMAL(10, 2),

order_date DATE

)

PARTITION BY RANGE (YEAR(order_date)) (

PARTITION p2023 VALUES LESS THAN (2024),

PARTITION p2024 VALUES LESS THAN (2025),

PARTITION p2025 VALUES LESS THAN (2026)

);
  • 哈希分区：根据某个字段的哈希值进行分区，这种方式可以将数据均匀地分布到各个分区中，适用于数据分布较为均匀的场景，能够实现负载均衡。例如，对users表按照user_id字段进行哈希分区： 
    CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

user_name VARCHAR(50),

email VARCHAR(100)

)

PARTITION BY HASH (user_id)

PARTITIONS 4;

这里将users表分为 4 个分区，user_id字段的哈希值决定数据存储在哪个分区。

6.2 分库分表

分库分表是将数据分散存储到多个数据库和表中，以解决单库单表数据量过大和高并发带来的性能问题。其原理是通过一定的规则将数据划分到不同的库和表中，使得单个库和表的数据量保持在一个可接受的范围内，从而提高系统的并发处理能力和性能 。

分库分表主要有以下两种方式：

  • 垂直分库：按照业务模块将不同的表划分到不同的数据库中。例如，在一个电商系统中，将用户相关的表（如users表、user_address表等）放在一个数据库，将订单相关的表（如orders表、order_items表等）放在另一个数据库。这样每个数据库的负载相对均衡，并且不同业务模块之间的耦合度降低，维护起来更加方便。
  • 水平分库分表：根据某个字段（如id、user_id等）按照一定的算法（如哈希算法、范围算法等）将数据分散到不同的数据库和表中。比如，将用户表按照user_id的哈希值进行水平分库，将user_id哈希值为奇数的用户数据存储到一个数据库，哈希值为偶数的用户数据存储到另一个数据库；或者按照user_id的范围进行分表，将user_id在 1 - 10000 之间的用户数据存储在一张表，10001 - 20000 之间的用户数据存储在另一张表。

在高并发场景下，分库分表可以有效地提高系统的性能和可用性。例如，在一个大型社交平台中，用户量和消息量巨大，如果所有数据都存储在一个数据库和表中，当大量用户同时发送消息时，数据库的负载会非常高，导致响应变慢甚至系统崩溃。通过分库分表，将用户数据和消息数据分散存储到多个库和表中，每个库和表的负载降低，系统可以处理更多的并发请求，提高了系统的稳定性和响应速度。

6.3 查询语句优化

在大数据量下，优化查询语句对于提高查询性能至关重要。以下是一些常见的优化建议：

  • 分析查询语句：使用EXPLAIN关键字来分析查询语句的执行计划，查看索引的使用情况、表的连接顺序等信息，从而找出性能瓶颈并进行优化。例如： 
    EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

通过EXPLAIN的输出结果，可以判断是否使用了合适的索引，如果没有使用索引，可能需要调整查询条件或创建合适的索引。

  • 避免全表扫描：尽量避免在查询条件中使用会导致全表扫描的操作，如在WHERE子句中使用!=、<>、LIKE '%...'（前置百分号）、OR连接条件、对索引列进行函数计算或表达式运算等。例如，避免使用SELECT * FROM products WHERE category_id!= 1，可以改为SELECT * FROM products WHERE category_id = 2 OR category_id = 3 OR...（具体根据业务需求）；避免SELECT * FROM users WHERE name LIKE '%test%'，如果需要模糊查询，可以使用全文索引。
  • 优化 JOIN 操作：在进行多表连接查询时，确保连接条件上有合适的索引，并且尽量使用内连接（INNER JOIN），因为内连接的性能通常比外连接（LEFT JOIN、RIGHT JOIN等）要好。如果必须使用外连接，要注意数据量和连接条件，避免产生大量的中间结果集。例如，在orders表和customers表的连接查询中：
-- 优化前，可能存在性能问题

SELECT * FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

-- 优化后，使用内连接，并且确保customer_id字段有索引

SELECT o.order_id, c.customer_name FROM orders o

INNER JOIN customers c ON o.customer_id = c.id;

同时，要注意连接表的顺序，一般将数据量小的表放在前面，这样可以减少中间结果集的大小，提高查询效率。

结语

🔥如果此文对你有帮助的话，欢迎💗关注、👍点赞、⭐收藏、✍️评论，支持一下博主~ 

# 数据库 # mysql
Logo
天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

Understand Anything 多智能体知识图谱管线：从代码扫描到可交互 Dashboard 的实现解析

本文以 Understand Anything 的官方文档为主线，系统拆解它如何用一条「Tree-sitter 静态分析 + LLM 语义理解」的多智能体管线，把任意代码库 / 知识库 / 文档转换成可探索、可检索、可问答的知识图谱，并落到一个可交互的 Web Dashboard。读完你应能掌握：如何安装并在 Claude Code / Codex / Cursor / Copilot / Gem

avatar 天工造物局

graphify 视频音视频转写全流程解析：从 detect 结果到知识图谱文档的 Step 2.5

graphify 的核心能力是把任意语料（代码、文档、PDF、图片）转化为可查询的知识图谱，而视频与音频文件无法被直接阅读，必须先把它们转写成文本。本文围绕 `graphify` 运行手册中的转写参考文档 [transcribe.md](https://link.gitcode.com/i/d3285781588e0f1b84886dfdbc4fe7f6)，完整讲解 "Step 2.5 - Tra

avatar 天工造物局

ML-For-Beginners 终章：经典机器学习在金融、医疗、零售等八大行业中的真实落地场景

本篇技术指南基于开源课程仓库 ML-For-Beginners 的收尾课程《Postscript: Machine learning in the real world》（对应仓库目录 [9-Real-World/1-Applications](https://link.gitcode.com/i/6b23002df80861c20abf34d7db63ef41)）编写。该课此前 11 周的教学带

avatar 天工造物局