一、索引下推
1. 基础概念
索引下推 (Index Condition Pushdown, ICP) 是 MySQL 5.6 引入的查询优化策略。
在没有 ICP 的情况下，存储引擎根据索引定位到记录后，需要回表读取完整记录，然后返回给 Server 层进行 WHERE 条件过滤。而 ICP 将部分 WHERE 条件下推到存储引擎层，在索引遍历时直接过滤不满足条件的记录，减少回表次数和 Server 层处理的数据量。
通过下述语句查询当前状态，其中 index_condition_pushdown 即为状态标识。
SHOW VARIABLES LIKE 'optimizer_switch';
ICP 优化默认为开启状态，也可通过下述方式手动开启或关闭。
-- 关闭 ICP
SET optimizer_switch = 'index_condition_pushdown=off';
-- 开启 ICP
SET optimizer_switch = 'index_condition_pushdown=on';
2. 示例剖析
让我们以具体的示例说明 ICP 的效果，假设有表 users，且存在联合索引 (age, city)：
CREATE TABLE users (
id INT PRIMARY KEY,
age INT,
city VARCHAR(50),
name VARCHAR(100),
INDEX idx_age_city (age, city)
);
-- 查询：年龄大于 20 且城市以 Beijing 开头的用户
SELECT * FROM users WHERE age > 20 AND city LIKE 'Beijing%';
执行计划显示 Using index condition 表示使用了 ICP，存储引擎在索引层面直接过滤 city LIKE 'Beijing%'，只对满足条件的记录回表。
对比没有 ICP 的情况，索引定位在到 age > 20 的所有记录后回表读取完整记录，再在 Server 层过滤 city 条件。反之在 ICP 下索引中可直接过滤 city 条件，只对满足条件的记录回表。
二、全文索引
1. 基础概念
全文索引 (Fulltext Index) 是 MySQL 专门用于文本搜索的索引类型，支持自然语言搜索和布尔搜索。
对于模糊搜索通常会选择 like 的方式，而由于最左前缀匹配机制的原因，对于文本搜索尤其是大文本字段全文索引往往表现更佳。
全文索引创建语法如下：
ALTER TABLE <table_name> ADD FULLTEXT INDEX <index_name> (<column_name>);
在 MySQL 中全文索引核心参数参考下表，可通过 SHOW VARIABLES LIKE xxx 查询。
| 参数 | 描述 |
|---|---|
| ft_min_word_len | 最小词长，InnoDB 中默认 3，长度大于等于该值的词才会进入 FULLTEXT 索引。 |
| ft_max_word_len | FULLTEXT 索引允许的最大词长度。 |
| innodb_ft_enable_stopword | 是否启用停用词。 |
| ft_stopword_file | 指定 FULLTEXT 的停用词文件（stopwords）。 |
2. 查询模式
在全文索引查询中，存在下表中三类模式：
| 模式 | 说明 |
|---|---|
| NATURAL LANGUAGE MODE | 默认，自然语言搜索。 |
| BOOLEAN MODE | 布尔搜索。 |
| WITH QUERY EXPANSION | 自动扩展搜索词。 |
那么三种模式最终查询的效果又有何区别？
(1) NATURAL LANGUAGE MODE
默认自然语言搜索 (NATURAL LANGUAGE MODE) 类似 Google 搜索，自动分词匹配相关度，但可能会存在文案匹配但相关度较低从而结果不匹配的情况。
同时需注意若针对中文场景下查询，需搭配 WITH PARSER ngram 以避免中文拆词失败。
SELECT * FROM tb_articles WHERE MATCH(content) AGAINST('database');
SELECT * FROM tb_articles WHERE MATCH(content) AGAINST('数据库') WITH PARSER ngram;
(2) BOOLEAN MODE
布尔搜索 (BOOLEAN MODE) 则更像 like 关键字查询，支持运算符以实现关键字匹配，常用示例如下：
-- 必须同时包含 apple
AGAINST ('+apple' IN BOOLEAN MODE)
-- 必须同时不包含 apple
AGAINST ('-apple' IN BOOLEAN MODE)
-- 匹配包含 flow 的如：flow、flowing、workflow
AGAINST ('flow*' IN BOOLEAN MODE)
-- 短语匹配，关键字必须连续出现
AGAINST ('"审批流程"' IN BOOLEAN MODE)
(3) WITH QUERY EXPANSION
扩展搜索词 WITH QUERY EXPANSION 核心即：先搜索一次，然后从结果里自动提取相关词二次搜索。
-- 自动扩展搜索词
SELECT * FROM tb_articles WHERE MATCH(content) AGAINST('database' QUERY EXPANSION);
三、索引选择
1. 基础概念
在 MySQL 中为表创建索引时，索引选择性直接决定了索引的效率。
索引的选择性（区分度）是指不重复的索引值与表记录总数的比值，选择性越高，索引的查询效率越高。主键和唯一索引的选择性为 1，是最理想的索引，选择性低的字段（如状态、性别）单独建立索引效果不佳。
对于区分度的计算，可以取字段类型与总数的比值作为大致的参考。若比值小于 0.3 说明选择性低则单独建索引效果差，若比值 0.3-0.8 则可考虑联合索引，若比值大于 0.8 则适合单独建立索引。
SELECT
COUNT(DISTINCT status) / COUNT(*) AS selectivity_status,
COUNT(DISTINCT city) / COUNT(*) AS selectivity_city,
COUNT(DISTINCT email) / COUNT(*) AS selectivity_email
FROM tb_users;
2. 示例剖析
同样，让我们以具体的示例进行分析低选择性字段索引的负面影响。
假设 users 表有 100w 条记录，status 只有 0 和 1 两个值。即使有 status 索引，优化器可能选择全表扫描，因为过滤后仍存在大量的数据，索引回表带来的随机 IO 成本甚至更高，而全表扫描顺序 IO 更高效：
因此更推荐通过联合索引优化，同时字段顺序很重要，尽量将将选择性高的字段放在联合索引前面以尽可能满足最左前缀匹配。
-- 高选择性字段在前
CREATE INDEX idx_email_status ON users(email, status);
