【SQL】MySQL 索引使用的数据结构

MySQL 使用的索引数据结构主要有以下几种：

1. B-Tree（B 树）

这是 MySQL 中最常用的索引数据结构。大多数存储引擎（如 InnoDB 和 MyISAM）都使用 B-Tree 索引。B-Tree 索引适用于大部分的查找操作，特别是范围查询。

• InnoDB：

  • InnoDB 使用 B+ 树结构来实现其主键索引（聚簇索引）和辅助索引（非聚簇索引）。
  • 聚簇索引中，数据存储在叶子节点，且叶子节点按主键顺序排序。
  • 辅助索引中，叶子节点存储的是主键值，必须通过主键进行二次查找。

• MyISAM：

  • MyISAM 使用 B+ 树结构来实现索引。
  • 数据和索引是分开存储的，叶子节点存储的是数据记录的地址。

2. Hash（哈希）

哈希索引只适用于精确查找，不适合范围查询。只有 Memory 存储引擎支持哈希索引。

• Memory：
  • Memory 存储引擎的默认索引类型是哈希索引。
  • 适用于只需要精确匹配查询的场景，不支持范围查询。

3. R-Tree（R 树）

R-Tree 索引主要用于地理数据存储和查询。MySQL 的 SPATIAL 索引使用 R-Tree 数据结构。

• MyISAM：
  • 支持 SPATIAL 索引，适用于空间数据类型（如 GEOMETRY）。

4. Full-Text（全文）

全文索引用于全文搜索，通常适用于大文本字段的搜索。MySQL 中 InnoDB 和 MyISAM 存储引擎都支持全文索引。

• InnoDB 和 MyISAM：
  • 实现全文索引，用于加速对文本数据的全文搜索。

示例：B-Tree 索引的使用

假设我们有一个用户表 users，并创建 B-Tree 索引：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

-- 创建 B-Tree 索引
CREATE INDEX idx_username ON users (username);
CREATE INDEX idx_email ON users (email);

示例：哈希索引的使用

假设有一个使用 Memory 引擎的表 cache，并创建哈希索引：

CREATE TABLE cache (
    cache_id INT PRIMARY KEY,
    cache_key VARCHAR(50),
    cache_value TEXT
) ENGINE=MEMORY;

-- 创建哈希索引
CREATE INDEX idx_cache_key USING HASH ON cache (cache_key);

示例：R-Tree 索引的使用

假设有一个地理数据表 locations，并创建 SPATIAL 索引：

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    geom GEOMETRY NOT NULL,
    SPATIAL INDEX(geom)
) ENGINE=MyISAM;

示例：全文索引的使用

假设有一个包含文章的表 articles，并创建全文索引：

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
);

-- 使用全文索引进行搜索
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database');

Java 示例：创建和查询索引

使用 Java 和 JDBC 创建索引并执行查询：

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

public class MySQLIndexExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement stmt = connection.createStatement()) {

            // 创建表和索引
            stmt.executeUpdate("CREATE TABLE users (user_id INT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), created_at TIMESTAMP)");
            stmt.executeUpdate("CREATE INDEX idx_username ON users (username)");
            stmt.executeUpdate("CREATE INDEX idx_email ON users (email)");

            // 插入数据
            stmt.executeUpdate("INSERT INTO users (user_id, username, email) VALUES (1, 'Alice', 'alice@example.com')");
            stmt.executeUpdate("INSERT INTO users (user_id, username, email) VALUES (2, 'Bob', 'bob@example.com')");

            // 查询数据
            ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username = 'Alice'");
            while (rs.next()) {
                System.out.println("User ID: " + rs.getInt("user_id") + ", Username: " + rs.getString("username"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

通过了解 MySQL 使用的不同索引数据结构，可以更好地选择适合的索引类型来优化查询性能。