第 10 章 条条大路通罗马——单表访问方法

准备工作：

1. 建表

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

2. 插入1000条数据

package com.cui.db;

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

public class BatchInsertTest {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/learning";
        String user = "root";
        String password = "123456";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false); // Turn off auto commit
            Statement stmt = conn.createStatement();

            for (int i = 0; i < 1000; i++) {
                String key1 = "key1_" + i;
                int key2 = i;
                String key3 = "key3_" + i;
                String key_part1 = "key_part1_" + i;
                String key_part2 = "key_part2_" + i;
                String key_part3 = "key_part3_" + i;
                String common_field = "common_field_" + i;

                String sql = "INSERT INTO single_table (key1, key2, key3, key_part1, key_part2, key_part3, common_field) " +
                        "VALUES ('" + key1 + "', " + key2 + ", '" + key3 + "', '" + key_part1 + "', '" + key_part2 + "', '" + key_part3 + "', '" + common_field + "')";
                stmt.executeUpdate(sql);
            }

            conn.commit(); // Commit the transaction
            stmt.close();
            conn.close();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback(); // Rollback the transaction in case of an error
            } catch (SQLException se) {
                se.printStackTrace();
            }
            e.printStackTrace();
        }
    }
}

10.1 访问方法（access method）的概念

MySQL查询的执行方式分为两种：

1. 使用全表扫描进行查询
2. 使用索引进行查询

10.2 const

const，常数级别，代价可以忽略不计。通过主键或者唯一二级索引列来定位一条记录的访问方法。

通过主键列来定位一条记录的查询：

SELECT * FROM single_table WHERE id = 1438;

通过唯一二级索引列来定位一条记录的查询：

SELECT * FROM single_table WHERE key2 = 3841;

10.3 ref

ref，搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法。

SELECT * FROM single_table WHERE key1 = 'abc';

10.4 ref_or_null

当使用二级索引而不是全表扫描的方式的访问方法就称为ref_or_null。

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

10.5 range

利用索引进行范围匹配的访问方法。

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

10.6 index

遍历二级索引记录的访问方法

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

key_part2作为查询条件时，无法使用索引。但查询的列表只包含key_part1, key_part2, key_part3三个列，与联合索引idx_key_part相同，且查询条件key_part2也在idx_key_part中。

所以可以直接遍历idx_key_part。此时由于二级索引比聚簇索引小得多，I/O效率高且不存在回表操作，所以比直接遍历聚簇索引成本要小得多。

10.7 all

直接扫描聚簇索引的访问方式。

10.8 注意事项

10.8.1 重温二级索引+回表

一般情况下只能利用单个二级索引执行查询。

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

优化器一般会根据single_table表的统计数据来判断使用哪个条件到对应的二级索引中查询扫描的行数会更少。

10.8.2 明确range访问方法使用的范围区间

对于B+树而言，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=或者LIKE操作符连接，就产生一个所谓的区间。

10.8.3 索引合并

10.8.3.1 Intersection 合并

查询可以使用多个二级索引时，将从多个二级索引中查询到的结果取交集。

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

MySQL 在某些特定的情况下才可能会用到 Intersection 合并：

1. 二级索引列的等值匹配的
2. 主键列可以是范围匹配

10.8.3.2 Union 合并

查询可以使用多个二级索引时，将从多个二级索引中查询到的结果取并集。

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

MySQL 在某些特定的情况下才可能会用到 Union合并：

1. 二级索引列的等值匹配的
2. 主键列可以是范围匹配
3. 使用 Intersection 合并的搜索条件

10.8.3.3 Sort-Union 合并

比单纯的 Union 合并多了一步对二级索引记录的主键值排序的过程。

10.9 总结

1. 不同的场景使用不同的访问方法。
2. 有的查询可以使用索引合并的方式利用多个索引。