MySQL索引原理：从O(n)到O(log n)的飞跃

-- 用户表,1亿条数据
SELECT * FROM users WHERE id = 50000000;

EXPLAIN SELECT * FROM users WHERE id = 50000000;

+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table | type | key           | ref  | rows      | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | 100000000 | Using where                 |
+----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+

关键信息:
  type: ALL          -- 全表扫描
  key: NULL          -- 未使用索引
  rows: 100000000    -- 扫描1亿行

/**
 * 简化版哈希索引
 */
public class HashIndex {

    // 哈希表,key=索引列值,value=行指针
    private Map<Integer, Long> index = new HashMap<>();

    /**
     * 构建索引
     */
    public void buildIndex(List<User> users) {
        for (User user : users) {
            // key=id, value=行指针（这里简化为user对象的内存地址）
            index.put(user.getId(), user.getRowPointer());
        }
    }

    /**
     * 查询（O(1)）
     */
    public Long search(int id) {
        return index.get(id);  // 直接哈希查找
    }

    /**
     * 范围查询（无法实现）
     */
    public List<Long> rangeSearch(int startId, int endId) {
        // ❌ 哈希表无序,无法进行范围查询
        throw new UnsupportedOperationException("哈希索引不支持范围查询");
    }
}

public class HashIndexTest {

    @Test
    public void testPerformance() {
        HashIndex index = new HashIndex();

        // 构建索引:1亿条数据
        List<User> users = generateUsers(100_000_000);
        long start = System.currentTimeMillis();
        index.buildIndex(users);
        long buildTime = System.currentTimeMillis() - start;
        System.out.println("构建索引耗时: " + buildTime + "ms");
        // 结果: 约60秒

        // 等值查询
        start = System.nanoTime();
        Long rowPointer = index.search(50_000_000);
        long searchTime = (System.nanoTime() - start) / 1000;
        System.out.println("等值查询耗时: " + searchTime + "μs");
        // 结果: 约1μs

        // 范围查询
        try {
            index.rangeSearch(1_000_000, 2_000_000);
        } catch (UnsupportedOperationException e) {
            System.out.println("范围查询失败: " + e.getMessage());
        }
    }
}

-- MySQL的Memory存储引擎支持哈希索引
CREATE TABLE hash_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=Memory;

CREATE INDEX idx_name USING HASH ON hash_table(name);

-- 但InnoDB不支持哈希索引（只支持B+树）
-- InnoDB内部有自适应哈希索引（Adaptive Hash Index）,但对用户透明

/**
 * 二叉搜索树索引
 */
public class BSTIndex {

    private TreeNode root;

    static class TreeNode {
        int key;           // 索引列的值
        long rowPointer;   // 行指针
        TreeNode left;     // 左子树
        TreeNode right;    // 右子树

        TreeNode(int key, long rowPointer) {
            this.key = key;
            this.rowPointer = rowPointer;
        }
    }

    /**
     * 插入节点
     */
    public void insert(int key, long rowPointer) {
        root = insertRecursive(root, key, rowPointer);
    }

    private TreeNode insertRecursive(TreeNode node, int key, long rowPointer) {
        if (node == null) {
            return new TreeNode(key, rowPointer);
        }

        if (key < node.key) {
            node.left = insertRecursive(node.left, key, rowPointer);
        } else if (key > node.key) {
            node.right = insertRecursive(node.right, key, rowPointer);
        }

        return node;
    }

    /**
     * 查询（O(log n)）
     */
    public Long search(int key) {
        TreeNode node = searchRecursive(root, key);
        return node != null ? node.rowPointer : null;
    }

    private TreeNode searchRecursive(TreeNode node, int key) {
        if (node == null || node.key == key) {
            return node;
        }

        if (key < node.key) {
            return searchRecursive(node.left, key);
        } else {
            return searchRecursive(node.right, key);
        }
    }

    /**
     * 范围查询（O(log n + m)）
     */
    public List<Long> rangeSearch(int startKey, int endKey) {
        List<Long> result = new ArrayList<>();
        rangeSearchRecursive(root, startKey, endKey, result);
        return result;
    }

    private void rangeSearchRecursive(TreeNode node, int startKey, int endKey, List<Long> result) {
        if (node == null) {
            return;
        }

        // 中序遍历（左-根-右）,结果自动有序
        if (startKey < node.key) {
            rangeSearchRecursive(node.left, startKey, endKey, result);
        }

        if (startKey <= node.key && node.key <= endKey) {
            result.add(node.rowPointer);
        }

        if (node.key < endKey) {
            rangeSearchRecursive(node.right, startKey, endKey, result);
        }
    }
}

public class BSTDegradationTest {

    @Test
    public void testDegradation() {
        BSTIndex index = new BSTIndex();

        // 插入有序数据（1, 2, 3, ..., 1000000）
        for (int i = 1; i <= 1_000_000; i++) {
            index.insert(i, i * 1000L);
        }

        // 查询最后一个节点
        long start = System.currentTimeMillis();
        Long rowPointer = index.search(1_000_000);
        long searchTime = System.currentTimeMillis() - start;

        System.out.println("查询耗时: " + searchTime + "ms");
        // 结果: 约100ms（退化为链表,需要遍历100万个节点）

        // 如果是平衡树,理论耗时应该是: log₂(1000000) ≈ 20次比较 ≈ 2ms
    }
}

/**
 * B树索引（简化版,m=5）
 */
public class BTreeIndex {

    private static final int M = 5;  // 最大子节点数
    private BTreeNode root;

    static class BTreeNode {
        int n;                    // 当前key的数量
        int[] keys = new int[M-1];      // key数组（最多m-1个）
        long[] values = new long[M-1];  // value数组（行指针）
        BTreeNode[] children = new BTreeNode[M];  // 子节点指针（最多m个）
        boolean isLeaf;           // 是否为叶子节点

        BTreeNode(boolean isLeaf) {
            this.isLeaf = isLeaf;
            this.n = 0;
        }
    }

    /**
     * 查询（O(log m n)）
     */
    public Long search(int key) {
        return searchRecursive(root, key);
    }

    private Long searchRecursive(BTreeNode node, int key) {
        if (node == null) {
            return null;
        }

        // 在当前节点中查找key
        int i = 0;
        while (i < node.n && key > node.keys[i]) {
            i++;
        }

        // 找到key
        if (i < node.n && key == node.keys[i]) {
            return node.values[i];
        }

        // 叶子节点,未找到
        if (node.isLeaf) {
            return null;
        }

        // 递归查找子节点
        return searchRecursive(node.children[i], key);
    }

    /**
     * 范围查询（需要遍历多个节点）
     */
    public List<Long> rangeSearch(int startKey, int endKey) {
        List<Long> result = new ArrayList<>();
        rangeSearchRecursive(root, startKey, endKey, result);
        return result;
    }

    private void rangeSearchRecursive(BTreeNode node, int startKey, int endKey, List<Long> result) {
        if (node == null) {
            return;
        }

        int i = 0;
        for (i = 0; i < node.n; i++) {
            if (!node.isLeaf) {
                rangeSearchRecursive(node.children[i], startKey, endKey, result);
            }

            if (startKey <= node.keys[i] && node.keys[i] <= endKey) {
                result.add(node.values[i]);
            }
        }

        if (!node.isLeaf) {
            rangeSearchRecursive(node.children[i], startKey, endKey, result);
        }
    }
}

/**
 * B+树索引（完整实现）
 */
public class BPlusTreeIndex {

    private static final int M = 5;  // 最大子节点数（实际MySQL中通常为几百到几千）
    private BPlusTreeNode root;

    /**
     * B+树节点
     */
    static class BPlusTreeNode {
        int n;                        // 当前key数量
        int[] keys = new int[M-1];          // key数组
        BPlusTreeNode[] children = new BPlusTreeNode[M];  // 子节点指针（非叶子节点）
        long[] values = new long[M-1];      // value数组（叶子节点）
        BPlusTreeNode next;               // 下一个叶子节点（叶子节点专用）
        BPlusTreeNode prev;               // 上一个叶子节点（叶子节点专用）
        boolean isLeaf;                   // 是否为叶子节点

        BPlusTreeNode(boolean isLeaf) {
            this.isLeaf = isLeaf;
            this.n = 0;
        }
    }

    /**
     * 插入key-value
     */
    public void insert(int key, long value) {
        if (root == null) {
            root = new BPlusTreeNode(true);
            root.keys[0] = key;
            root.values[0] = value;
            root.n = 1;
            return;
        }

        // 如果根节点满了,需要分裂
        if (root.n == M - 1) {
            BPlusTreeNode newRoot = new BPlusTreeNode(false);
            newRoot.children[0] = root;
            splitChild(newRoot, 0, root);
            root = newRoot;
        }

        insertNonFull(root, key, value);
    }

    /**
     * 向非满节点插入
     */
    private void insertNonFull(BPlusTreeNode node, int key, long value) {
        int i = node.n - 1;

        if (node.isLeaf) {
            // 叶子节点:插入key-value
            while (i >= 0 && key < node.keys[i]) {
                node.keys[i + 1] = node.keys[i];
                node.values[i + 1] = node.values[i];
                i--;
            }
            node.keys[i + 1] = key;
            node.values[i + 1] = value;
            node.n++;
        } else {
            // 非叶子节点:找到子节点
            while (i >= 0 && key < node.keys[i]) {
                i--;
            }
            i++;

            // 如果子节点满了,先分裂
            if (node.children[i].n == M - 1) {
                splitChild(node, i, node.children[i]);
                if (key > node.keys[i]) {
                    i++;
                }
            }

            insertNonFull(node.children[i], key, value);
        }
    }

    /**
     * 分裂子节点
     */
    private void splitChild(BPlusTreeNode parent, int index, BPlusTreeNode fullChild) {
        int mid = (M - 1) / 2;
        BPlusTreeNode newChild = new BPlusTreeNode(fullChild.isLeaf);
        newChild.n = M - 1 - mid - 1;

        // 复制后半部分key到新节点
        for (int j = 0; j < newChild.n; j++) {
            newChild.keys[j] = fullChild.keys[mid + 1 + j];
        }

        if (fullChild.isLeaf) {
            // 叶子节点:复制value,并连接链表
            for (int j = 0; j < newChild.n; j++) {
                newChild.values[j] = fullChild.values[mid + 1 + j];
            }

            // 更新链表指针
            newChild.next = fullChild.next;
            if (fullChild.next != null) {
                fullChild.next.prev = newChild;
            }
            fullChild.next = newChild;
            newChild.prev = fullChild;
        } else {
            // 非叶子节点:复制子指针
            for (int j = 0; j <= newChild.n; j++) {
                newChild.children[j] = fullChild.children[mid + 1 + j];
            }
        }

        fullChild.n = mid;

        // 将新节点插入到父节点
        for (int j = parent.n; j > index; j--) {
            parent.children[j + 1] = parent.children[j];
        }
        parent.children[index + 1] = newChild;

        for (int j = parent.n - 1; j >= index; j--) {
            parent.keys[j + 1] = parent.keys[j];
        }
        parent.keys[index] = fullChild.keys[mid];
        parent.n++;
    }

    /**
     * 查询（O(log m n)）
     */
    public Long search(int key) {
        return searchRecursive(root, key);
    }

    private Long searchRecursive(BPlusTreeNode node, int key) {
        if (node == null) {
            return null;
        }

        int i = 0;
        while (i < node.n && key > node.keys[i]) {
            i++;
        }

        if (node.isLeaf) {
            // 叶子节点:返回value
            if (i < node.n && key == node.keys[i]) {
                return node.values[i];
            }
            return null;
        } else {
            // 非叶子节点:递归查找子节点
            return searchRecursive(node.children[i], key);
        }
    }

    /**
     * 范围查询（O(log m n + k)）
     */
    public List<Long> rangeSearch(int startKey, int endKey) {
        List<Long> result = new ArrayList<>();

        // 1. 定位起始叶子节点（O(log m n)）
        BPlusTreeNode leaf = findLeaf(root, startKey);

        // 2. 顺序扫描叶子节点链表（O(k)）
        while (leaf != null) {
            for (int i = 0; i < leaf.n; i++) {
                if (leaf.keys[i] >= startKey && leaf.keys[i] <= endKey) {
                    result.add(leaf.values[i]);
                } else if (leaf.keys[i] > endKey) {
                    return result;  // 超出范围,停止
                }
            }
            leaf = leaf.next;  // 移动到下一个叶子节点
        }

        return result;
    }

    /**
     * 定位叶子节点
     */
    private BPlusTreeNode findLeaf(BPlusTreeNode node, int key) {
        if (node == null) {
            return null;
        }

        if (node.isLeaf) {
            return node;
        }

        int i = 0;
        while (i < node.n && key > node.keys[i]) {
            i++;
        }

        return findLeaf(node.children[i], key);
    }

    /**
     * 打印B+树结构（用于调试）
     */
    public void print() {
        printRecursive(root, 0);
    }

    private void printRecursive(BPlusTreeNode node, int level) {
        if (node == null) {
            return;
        }

        System.out.print("Level " + level + ": ");
        for (int i = 0; i < node.n; i++) {
            System.out.print(node.keys[i] + " ");
        }
        System.out.println(node.isLeaf ? "(叶子)" : "(非叶子)");

        if (!node.isLeaf) {
            for (int i = 0; i <= node.n; i++) {
                printRecursive(node.children[i], level + 1);
            }
        }
    }
}

public class BPlusTreeIndexTest {

    @Test
    public void testPerformance() {
        BPlusTreeIndex index = new BPlusTreeIndex();

        // 插入100万条数据
        System.out.println("开始插入100万条数据...");
        long start = System.currentTimeMillis();
        for (int i = 1; i <= 1_000_000; i++) {
            index.insert(i, i * 1000L);
        }
        long insertTime = System.currentTimeMillis() - start;
        System.out.println("插入耗时: " + insertTime + "ms");
        // 结果: 约5秒

        // 等值查询
        start = System.nanoTime();
        Long value = index.search(500_000);
        long searchTime = (System.nanoTime() - start) / 1000;
        System.out.println("等值查询耗时: " + searchTime + "μs");
        System.out.println("查询结果: " + value);
        // 结果: 约20μs

        // 范围查询（查询1000个连续key）
        start = System.currentTimeMillis();
        List<Long> results = index.rangeSearch(500_000, 501_000);
        long rangeSearchTime = System.currentTimeMillis() - start;
        System.out.println("范围查询耗时: " + rangeSearchTime + "ms");
        System.out.println("结果数量: " + results.size());
        // 结果: 约5ms

        // 打印树结构（前几层）
        System.out.println("\nB+树结构:");
        index.print();
    }
}

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)
);

-- 查询1:需要回表
SELECT * FROM users WHERE name = '张三';

执行流程:
  1. 在idx_name索引中查找'张三' → 得到id=1（1次IO）
  2. 在主键索引中查找id=1 → 得到完整行（1次IO）
  总计: 2次IO

-- 查询2:覆盖索引,无需回表
SELECT id, name FROM users WHERE name = '张三';

执行流程:
  1. 在idx_name索引中查找'张三' → 得到id=1,name='张三'（1次IO）
  2. 无需回表,直接返回
  总计: 1次IO

性能提升: 2倍

-- 需要回表
EXPLAIN SELECT * FROM users WHERE name = '张三';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | key           | ref      | rows  | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | users | ref  | idx_name      | const    |   1   | NULL  |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------+

-- 覆盖索引,无需回表
EXPLAIN SELECT id, name FROM users WHERE name = '张三';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | key           | ref      | rows  | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | idx_name      | const    |   1   | Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

Extra: Using index  -- 覆盖索引

CREATE INDEX idx_user_time ON orders(user_id, created_time, status);

联合索引的B+树结构:
叶子节点按(user_id, created_time, status)排序:
  [user_id=1, created_time=100, status=1, id=10]
  [user_id=1, created_time=200, status=2, id=11]
  [user_id=1, created_time=300, status=1, id=12]
  [user_id=2, created_time=100, status=1, id=13]
  [user_id=2, created_time=200, status=2, id=14]
  ...

排序规则:
  先按user_id排序
  user_id相同时,按created_time排序
  created_time相同时,按status排序

-- 联合索引: (user_id, created_time, status)

-- ✅ 能用到索引（满足最左前缀）
SELECT * FROM orders WHERE user_id = 1;
-- 使用索引: user_id

SELECT * FROM orders WHERE user_id = 1 AND created_time > 100;
-- 使用索引: user_id, created_time

SELECT * FROM orders WHERE user_id = 1 AND created_time > 100 AND status = 1;
-- 使用索引: user_id, created_time, status

-- ❌ 不能用到索引（不满足最左前缀）
SELECT * FROM orders WHERE created_time > 100;
-- 跳过了user_id,无法使用索引

SELECT * FROM orders WHERE status = 1;
-- 跳过了user_id和created_time,无法使用索引

-- ⚠️ 部分使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 1;
-- 只使用user_id,status无法使用（跳过了created_time）

-- 特殊情况:范围查询后的列无法使用索引
SELECT * FROM orders WHERE user_id = 1 AND created_time > 100 AND status = 1;
-- user_id:使用索引
-- created_time:使用索引（范围查询）
-- status:无法使用索引（范围查询后的列）

-- ❌ 索引失效
SELECT * FROM orders WHERE id + 1 = 100;

-- ✅ 应该改写为
SELECT * FROM orders WHERE id = 99;

原理:
  索引中存储的是id的原始值
  id + 1 是计算后的值,无法在索引中快速定位

-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(created_time) = 2024;

-- ✅ 应该改写为
SELECT * FROM orders
WHERE created_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';

原理:
  YEAR(created_time)是计算后的值
  索引中存储的是created_time的原始值

-- ❌ 索引失效（order_no是VARCHAR）
SELECT * FROM orders WHERE order_no = 123;

-- ✅ 应该使用字符串
SELECT * FROM orders WHERE order_no = '123';

原理:
  MySQL会将VARCHAR转换为INT进行比较
  相当于: CAST(order_no AS SIGNED) = 123
  索引列参与了隐式函数,失效

-- ❌ 索引失效
SELECT * FROM orders WHERE order_no LIKE '%123';

-- ⚠️ 索引部分使用
SELECT * FROM orders WHERE order_no LIKE '%123%';

-- ✅ 索引正常使用
SELECT * FROM orders WHERE order_no LIKE '123%';

原理:
  B+树索引是按前缀有序的
  '123%'可以定位到'123'开头的范围
  '%123'无法定位（不知道从哪里开始）

-- ❌ 索引失效（通常情况下）
SELECT * FROM orders WHERE status != 0;

-- ✅ 应该改写为IN
SELECT * FROM orders WHERE status IN (1, 2, 3);

原理:
  !=需要扫描除了0以外的所有值
  如果status=0的行占比很小,优化器可能选择全表扫描
  因为扫描多个不连续的范围,随机IO太多

-- ❌ 索引失效（字段允许NULL时）
SELECT * FROM orders WHERE user_id IS NULL;

-- ✅ 避免NULL值
ALTER TABLE orders MODIFY user_id BIGINT NOT NULL DEFAULT 0;

原理:
  NULL值在B+树中的存储位置不确定
  MySQL需要特殊处理NULL值
  建议设置NOT NULL + DEFAULT值

-- ❌ 索引失效
SELECT * FROM orders WHERE user_id = 1 OR product_id = 2;
-- 如果product_id无索引,整个查询无法使用索引

-- ✅ 应该改写为UNION ALL
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE product_id = 2;

原理:
  OR条件需要扫描两个列的索引并合并
  如果其中一个列无索引,只能全表扫描

-- 联合索引: (user_id, created_time, status)

-- ❌ 跳过user_id
SELECT * FROM orders WHERE created_time > '2024-01-01';

-- ❌ 跳过created_time
SELECT * FROM orders WHERE user_id = 1 AND status = 1;

原理:见3.2节

-- 联合索引: (user_id, created_time, status)

SELECT * FROM orders
WHERE user_id = 1 AND created_time > '2024-01-01' AND status = 1;

使用索引:
  ✅ user_id（等值）
  ✅ created_time（范围）
  ❌ status（范围查询后的列）

原理:
  范围查询后,数据不再有序
  user_id=1, created_time>xxx的数据中,status是无序的

-- 联合索引: (user_id, created_time)

-- ❌ 无法利用索引排序
SELECT * FROM orders ORDER BY created_time;

-- ✅ 可以利用索引排序
SELECT * FROM orders WHERE user_id = 1 ORDER BY created_time;

原理:
  索引本身就是有序的
  如果ORDER BY满足最左前缀,可以直接利用索引的有序性

-- status只有4个值（0,1,2,3）,区分度很低
SELECT * FROM orders WHERE status = 0;

-- 如果status=0的行占比>20%,优化器可能选择全表扫描
-- 因为随机IO的成本高于顺序IO

验证:
EXPLAIN SELECT * FROM orders WHERE status = 0;
-- 如果type=ALL,说明选择了全表扫描

解决方案:
-- 方案1:强制使用索引（不推荐）
SELECT * FROM orders FORCE INDEX(idx_status) WHERE status = 0;

-- 方案2:优化查询,减少返回行数
SELECT * FROM orders WHERE status = 0 LIMIT 1000;

-- 方案3:创建覆盖索引
CREATE INDEX idx_status_id ON orders(status, id);
SELECT id FROM orders WHERE status = 0;

-- 表只有100行
SELECT * FROM small_table WHERE id = 1;

-- 优化器可能选择全表扫描
-- 因为全表扫描只需要1次IO,索引查询也需要1-2次IO

原理:
  全表扫描: 1次IO（100行在1页内）
  索引查询: 1次IO（索引）+ 1次IO（回表）= 2次IO
  全表扫描更快

-- 订单表,5000万行
SELECT * FROM orders
WHERE user_id = 123456 AND created_time > '2024-01-01'
ORDER BY created_time DESC
LIMIT 10;

执行时间: 3.2秒

EXPLAIN SELECT * FROM orders
WHERE user_id = 123456 AND created_time > '2024-01-01'
ORDER BY created_time DESC
LIMIT 10;

+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table  | type | key           | ref  | rows     | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | 50000000 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+

问题:
  1. type=ALL: 全表扫描
  2. key=NULL: 未使用索引
  3. Using filesort: 需要额外排序
  4. rows=50000000: 扫描5000万行

-- 创建联合索引
CREATE INDEX idx_user_time ON orders(user_id, created_time);

-- 再次执行
EXPLAIN SELECT * FROM orders
WHERE user_id = 123456 AND created_time > '2024-01-01'
ORDER BY created_time DESC
LIMIT 10;

+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | key           | ref          | rows | Extra                 |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | range | idx_user_time | NULL         | 1000 | Using index condition |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------+

改进:
  1. type=range: 范围扫描
  2. key=idx_user_time: 使用了索引
  3. 无Using filesort: 利用索引的有序性,无需额外排序
  4. rows=1000: 只扫描1000行

执行时间: 10ms

性能提升: 320倍

-- 查询用户的订单数量
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE created_time > '2024-01-01'
GROUP BY user_id;

执行时间: 5秒

EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE created_time > '2024-01-01'
GROUP BY user_id;

+----+-------------+--------+-------+------------------+------------------+---------+------+---------+-----------------------+
| id | select_type | table  | type  | key              | ref              | rows    | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+---------+-----------------------+
|  1 | SIMPLE      | orders | index | idx_created_time | NULL             | 5000000 | Using where; Using temporary |
+----+-------------+--------+-------+------------------+------------------+---------+------+---------+-----------------------+

问题:
  1. Using temporary: 需要创建临时表进行GROUP BY
  2. 需要回表获取user_id
  3. rows=5000000: 扫描500万行

-- 创建覆盖索引（包含user_id和created_time）
CREATE INDEX idx_time_user ON orders(created_time, user_id);

-- 再次执行
EXPLAIN SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE created_time > '2024-01-01'
GROUP BY user_id;

+----+-------------+--------+-------+---------------+---------------+---------+------+---------+--------------------------+
| id | select_type | table  | type  | key           | ref           | rows    | Extra                    |
+----+-------------+--------+-------+---------------+---------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | orders | range | idx_time_user | NULL          | 5000000 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------------+---------+------+---------+--------------------------+

改进:
  1. Using index: 覆盖索引,无需回表
  2. 无Using temporary: 直接在索引上GROUP BY

执行时间: 500ms

性能提升: 10倍

-- 查询第100万页数据（每页10条）
SELECT * FROM orders
ORDER BY id
LIMIT 10000000, 10;

执行时间: 30秒

-- 先用覆盖索引查出ID,再回表
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders
    ORDER BY id
    LIMIT 10000000, 1
)
ORDER BY id
LIMIT 10;

执行时间: 5秒

原理:
  子查询SELECT id使用覆盖索引,无需回表
  主查询只需要查11行数据

-- 第1页
SELECT * FROM orders
WHERE id > 0
ORDER BY id
LIMIT 10;
-- 返回:id=1~10,最大ID=10

-- 第2页
SELECT * FROM orders
WHERE id > 10
ORDER BY id
LIMIT 10;
-- 返回:id=11~20,最大ID=20

-- 第100万页
SELECT * FROM orders
WHERE id > 9999990
ORDER BY id
LIMIT 10;
-- 返回:id=9999991~10000000

执行时间: 10ms

原理:
  每次只查10行,无需扫描前面的数据
  利用主键索引,性能稳定

-- 限制最大页数
SELECT * FROM orders
ORDER BY id
LIMIT 100000, 10;  -- 最多查到第10000页

-- 或者改用游标翻页
-- 提示用户:"已显示前1000页,如需查看更多,请缩小查询范围"

原理:
  从产品层面避免深分页
  绝大多数用户不会翻到第100万页