一、MySQL索引执行流程图

关键特性解析

1、B+树结构优势：

• 平衡树结构：所有叶子节点深度相同
• 叶子节点链表：支持高效范围查询（age BETWEEN 25-30）
• 节点大小=磁盘页：减少I/O次数（典型16KB/页）

2、性能对比：

3、代价分析：

• 空间代价：索引约占原数据10%-20%空间
• 维护代价：

INSERT → 插入新键并分裂节点
DELETE → 标记删除或重组树
UPDATE → 删除旧键+插入新键

索引优化实践

1、最左前缀原则

复合索引(last_name, first_name)支持：

• WHERE last_name='Smith'
• WHERE last_name='Smith' AND first_name='John'
• WHERE first_name='John'

2、覆盖索引技巧

当索引包含查询所需全部字段时，避免访问数据文件：

-- 创建覆盖索引
CREATE INDEX idx_cover ON users(age) INCLUDE (name);

-- 查询可直接从索引获取数据
SELECT name FROM users WHERE age = 25;

黄金法则：索引不是越多越好！维护成本随索引数量指数级增长，通常建议单表不超过5个索引。

注：通过合理使用索引，可使查询速度提升10-100倍，是数据库性能优化的核心手段。实际应用中需结合查询模式、数据分布和存储引擎特性进行设计。

索引下推（ICP）工作原理详解

索引下推（ICP）核心机制

1、解决的问题

传统索引查询的瓶颈：

• 即使索引中的记录不满足WHERE条件，也会执行回表操作
• 导致大量不必要的磁盘I/O和CPU消耗

2、适用场景示例

表结构：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_age (name, age)
);

查询：

SELECT * FROM users 
WHERE name LIKE '张%' AND age > 25 AND city = '北京';

ICP优化过程：

1. 在idx_name_age索引中扫描所有name LIKE '张%'的记录
2. 在存储引擎层直接应用age > 25条件过滤（下推条件）
3. 仅对通过过滤的记录执行回表操作
4. Server层再应用city = '北京'条件

3、性能提升对比

索引下推（ICP）适用条件深度解析

一、具体可下推条件类型

1、复合索引的非首列条件

-- 索引: (last_name, first_name, age)
-- 可下推:
WHERE last_name = 'Smith' AND first_name = 'John' 
WHERE last_name = 'Smith' AND age > 25
-- 不可下推:
WHERE first_name = 'John'  -- 缺少首列

2、范围扫描后的附加条件

-- 索引: (order_date, product_id)
-- 可下推:
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' 
  AND product_id = 1005  -- 可下推
-- 不可下推:
WHERE product_id = 1005  -- 单独条件

3、前缀索引条件

-- 索引: (title)
-- 可下推:
WHERE title LIKE 'Database%'  -- 前缀匹配
-- 不可下推:
WHERE title LIKE '%Systems'   -- 后缀匹配

4、多列组合条件

-- 索引: (dept_id, salary)
-- 可下推:
WHERE dept_id = 10 AND salary < 5000
WHERE (dept_id, salary) IN ((10,4000), (20,6000))

二、禁止下推的条件类型

1、非索引列条件

-- 索引: (email)
-- 不可下推:
WHERE phone = '13800138000'  -- 非索引列

2、函数包装的列

-- 索引: (birth_date)
-- 不可下推:
WHERE YEAR(birth_date) = 1990  -- 函数包装
WHERE UPPER(first_name) = 'JOHN'

3、类型不匹配的列

-- 索引: (phone varchar(20))
-- 不可下推:
WHERE phone = 13800138000  -- 数字vs字符串

4、OR连接的条件

-- 索引: (category, price)
-- 不可下推:
WHERE category = 'Electronics' OR price < 100

5、子查询相关条件

-- 不可下推:
WHERE product_id IN (SELECT id FROM new_products)
WHERE EXISTS (SELECT 1 FROM orders WHERE ...)

6、全文索引条件

-- 全文索引: (description)
-- 不可下推:
WHERE MATCH(description) AGAINST('database')