mysql索引概念以及索引底层数据结构

一、什么是 MySQL 索引

索引是数据库管理系统中一种用于提高数据检索效率的数据结构。通过在表的一个或多个列上创建索引，可以显著加快数据查询的速度，但会增加插入、删除和更新操作的开销。

MySQL 中索引的核心作用是快速定位数据位置，减少磁盘 I/O 操作，从而提高查询效率。索引在 MySQL 中有以下几种主要类型：

1. 主键索引 (Primary Key Index)
   唯一标识表中的一条记录，不允许为 NULL。

2. 唯一索引 (Unique Index)
   保证索引列中的值唯一，但允许存在 NULL。

3. 普通索引 (Index)
   用于加速查询，但无唯一性约束。

4. 全文索引 (Fulltext Index)
   专用于全文搜索。

5. 复合索引 (Composite Index)
   在多个列上创建的索引。

二、索引的底层数据结构

MySQL 的索引底层主要采用以下两种数据结构：

1. B+树
   B+树是 MySQL 默认的索引数据结构，尤其是 InnoDB 引擎使用的主索引（聚簇索引）和二级索引（非聚簇索引）均基于 B+树。

   B+树的特点：

   • 所有数据存储在叶子节点，内部节点仅存储索引值和指向子节点的指针。
   • 叶子节点通过双向链表相连，支持范围查询。
   • 插入和删除操作会保持树的平衡，具有良好的稳定性。

   B+树索引工作流程：

   • 查询从根节点开始，逐层搜索直到叶子节点。
   • 利用树的高度和分支因子，使得查询复杂度接近 (O(\log N))。

   聚簇索引 (Clustered Index)：
   数据存储和索引紧密耦合，主键索引即为聚簇索引。

   非聚簇索引 (Non-clustered Index)：
   索引存储和数据存储分离，叶子节点保存指向主键的引用。

2. 哈希索引 (Hash Index)
   通过哈希函数将键值映射为固定长度的哈希码，适用于等值查询，但不支持范围查询。

三、B+树索引与哈希索引对比

四、MySQL 索引使用的最佳实践

1. 选择合适的列创建索引：

   • 高选择性列适合作为索引列（选择性 = 唯一值数量 / 总记录数）。
   • 经常出现在 WHERE 条件或 JOIN 的列优先考虑创建索引。

2. 复合索引与最左前缀原则：

   • 对多列查询创建复合索引时，应按照查询频率从高到低排列。
   • 查询条件需要包含复合索引的最左边列才能使用索引。

3. 避免创建冗余索引：

   • 同一列上的多个相似索引会导致性能下降和存储浪费。

4. 定期维护索引：

   • 对经常修改的数据表，定期使用 OPTIMIZE TABLE 命令优化索引结构。

五、Java 示例代码

以下是使用 Java 和 MySQL 结合的索引查询示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class MySQLIndexDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            // 创建表和索引
            createTableAndIndex(connection);

            // 插入数据
            insertData(connection);

            // 使用索引进行查询
            queryWithIndex(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createTableAndIndex(Connection connection) throws Exception {
        String createTableSQL = """
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                email VARCHAR(50) UNIQUE,
                age INT
            );
        """;

        String createIndexSQL = """
            CREATE INDEX idx_age ON users(age);
        """;

        try (PreparedStatement stmt = connection.prepareStatement(createTableSQL)) {
            stmt.execute();
        }

        try (PreparedStatement stmt = connection.prepareStatement(createIndexSQL)) {
            stmt.execute();
        }
    }

    private static void insertData(Connection connection) throws Exception {
        String insertSQL = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = connection.prepareStatement(insertSQL)) {
            for (int i = 1; i <= 100; i++) {
                stmt.setString(1, "User" + i);
                stmt.setString(2, "user" + i + "@example.com");
                stmt.setInt(3, 20 + (i % 10)); // age ranges from 20 to 29
                stmt.executeUpdate();
            }
        }
    }

    private static void queryWithIndex(Connection connection) throws Exception {
        String querySQL = "SELECT * FROM users WHERE age = ?";
        try (PreparedStatement stmt = connection.prepareStatement(querySQL)) {
            stmt.setInt(1, 25); // Query users with age = 25

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Email: %s, Age: %d%n",
                            rs.getInt("id"), rs.getString("name"), rs.getString("email"), rs.getInt("age"));
                }
            }
        }
    }
}

六、总结

MySQL 索引是提升数据库性能的重要工具，但需要合理设计和使用。B+树作为主流索引结构，既支持高效查询，又能保证数据的有序性。Java 代码示例展示了如何结合 MySQL 创建索引、插入数据并执行索引查询，实践了理论与应用的结合。