索引篇
MySQL 执行优化器
-
问题引出：假设我现在有一个数据表，表中包含 5 个字段：
id,processId,status,create_time,update_time -
建表语句如下：
CREATE TABLE table_name ( id INT AUTO_INCREMENT PRIMARY KEY, processId INT, status ENUM('0','1','2') comment '状态 0: 执行失败 1：执行成功 2：未执行', create_time DATETIME, update_time DATETIME);-
问题描述：我现在要查询所有状态为
执行失败的记录，同时我要根据create_time排序,请问，此时我在status字段上建索引是否有意义？MySQL 是否会用到这个索引？ -
问题背景：记录大约有
50w条，其中三种状态的占比为 执行失败：10%，执行成功：80%，未执行：10%
SELECT * FROM table_name WHERE status = '0' ORDER BY create_time;问题分析
对于只有 3 个离散值、分布极不均匀（8-1-1）的状态列，即使你为它单独建了索引，MySQL 优化器大概率不会使用该索引，而是选择全表扫描。
原因在于选择性太低，索引带来的收益远小于回表或直接扫描的开销。
为什么优化器会“放弃”索引
-
选择性不足
索引的选择性 = 不重复索引值 / 总行数。理想索引应接近 1，而你的状态列选择性只有
3/10 = 0.3，优化器会认为“走索引再回表”比直接全表扫描更慢。 -
数据分布倾斜
占比 80 % 的状态值意味着即使使用索引定位，仍需回表 80 % 的数据行，随机 I/O 反而比顺序扫描更耗时。
-
优化器成本模型
InnoDB 的统计信息会让优化器估算“使用索引的代价”>“全表扫描的代价”，于是直接选择全表扫描。
总结就是：当索引的选择性太低，同时还需要回表去获取数据（回表挨个取数据磁盘 IO 陈本会很高很高），还不如直接全表扫描将数据一次性加载到内存中，然后在内存中进行选择和排序。
但是! 在某些场景下，索引还是有意义的
-
查询条件只有状态列
建议不加索引，让其全表扫描；或者考虑分区表按状态拆分，把 80 % 的大分区单独存放，减少扫描量。
-
查询条件包含状态列 + 其他高选择性列
把状态列放在复合索引的后置位置，前置高选择性列（如 id、create_time）。这样既能利用前置列快速过滤，又避免单独为低选择性列建索引。
-
只查询状态列本身（覆盖索引）
若 SQL 只返回状态列（如 SELECT status FROM t WHERE status = 1），可建立单列索引，此时索引本身就能覆盖查询，无需回表，优化器可能选择索引扫描。
总结
“低选择性 + 数据倾斜”会让单列索引失效，优化器宁可全表扫描；把状态列放到复合索引里、或用分区/覆盖索引，才是有效的优化方向。
大量字符串列索引优化篇
问题描述
我有一个表，表中包含 5 个字段：id,title,content,create_time,update_time 建表语句如下：
CREATE TABLE table_name ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT, create_time DATETIME, update_time DATETIME);问题背景
该表中的 content 字段是一个大文本字段内容 >=1000 字符，包含了文章的详细内容。表中的记录数为 10w 条，如果此时我需要根据 content 字段进行查询，例如查询所有包含MySQL关键词的文章，该如何优化？你能针对哪些不同的场景进行怎样的优化？
各场景的优化方式
1. 如果业务允许只匹配前缀（如 URL、文件路径），用前缀索引：
CREATE INDEX idx_col_prefix ON t(col(20));20 字符通常就能把选择性拉到 0.8 以上，索引体积只有完整索引的 1/20。
2. 若必须全文匹配，考虑把完整字符串哈希成 32 位或 64 位 INT，再建索引：
ALTER TABLE t ADD col_hash BIGINT UNSIGNED;
UPDATE t SET col_hash = CRC32(col);
CREATE INDEX idx_hash ON t(col_hash);具体流程是：
-
在建表时，表后面添加一列额外的字段表示内容的哈希值，然后对这列字段添加索引。
-
在插入一条记录时，同时对 content 的内容做一次全文 hash （MD5 输出 128 位（16 字节），通常表示为 32 位十六进制字符串 or SHA-256 输出 256 位 32 字节）放到 col_hash 字段中
-
在查询时，先对内容进行一次 hash，然后再用 hash 值走索引去查找记录。
3. 通过关键字查询
考虑使用 MySQL 自带的全文索引和中文分词插件。
添加全文索引
-- 添加全文索引（MySQL 5.6+ 支持InnoDB）ALTER TABLE table_name ADD FULLTEXT(content);
-- 查询示例SELECT id, title FROM table_nameWHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);
-- 布尔模式（更精确控制）SELECT id, title FROM table_nameWHERE MATCH(content) AGAINST('+MySQL' IN BOOLEAN MODE);添加中文分词插件
-- 对于中文，需要使用ngram分词器-- 在my.cnf中配置：-- [mysqld]-- ngram_token_size=2
-- 创建ngram全文索引ALTER TABLE table_name ADD FULLTEXT(content) WITH PARSER ngram;原理：
MySQL 从 5.6 版本开始支持 InnoDB 引擎的全文索引（FULLTEXT Index），其底层使用 倒排索引（Inverted Index） 结构。
倒排索引基本原理：
将文档（如表中的一行）中的词（term）作为索引键； 每个词对应一个倒排列表（posting list），记录包含该词的所有文档 ID（DocID）及可选的位置、频率等信息； 查询时，将查询词映射到倒排列表，再做交集/并集等操作。
例如：
文档 1: "我喜欢 MySQL"文档 2: "MySQL 很强大"
倒排索引："我" → [1]"喜欢" → [1]"MySQL" → [1, 2]"很" → [2]"强大" → [2]中文分词器
MySQL 的默认全文索引使用 基于空格和标点的分词器（n-gram 除外），对英文有效，但对中文无效，因为中文没有天然的词边界。
“我喜欢 MySQL” 被当作一个整体 token，无法拆分为“我”、“喜欢”、“MySQL”； 导致中文查询几乎无法命中。
MySQL 官方并未提供智能中文分词插件（如结巴、HanLP），但提供了 n-gram 全文解析器（ngram parser），作为对 CJK（中文、日文、韩文）语言的折中方案。
n-gram 分词原理：
- 将文本按固定长度 n（如 n=2）切分为连续子串；
- 例如：“我喜欢” → “我喜”、“喜欢”（n=2）；
- 这些子串作为倒排索引的 term。
倒排索引在 MySQL 中的实现细节
MySQL 的全文索引在 InnoDB 中通过 辅助表（Auxiliary Tables） 实现倒排索引，主要包括：
- DOC*ID → 文档映射表：每行分配唯一 DOC_ID；
- 倒排词典表（FTS_INDEX）：存储 term → DOC_ID 列表；
- 缓存与合并机制：新插入的全文数据先写入 FTS_DELETED 和 FTS_INDEX_CACHE，后台线程定期合并到主倒排索引。
- 这些表对用户透明，可通过
information_schema.INNODB_FT_INDEX_*查看。
4.冗余字段 + 普通索引
-- 添加关键词字段ALTER TABLE table_name ADD COLUMN keywords VARCHAR(500);
-- 创建索引CREATE INDEX idx_keywords ON table_name(keywords);
-- 应用层维护keywords字段（存储提取的关键词）UPDATE table_name SET keywords = 'MySQL,数据库,优化' WHERE id = 1;5. 用 redis 维护
Redis 缓存关键词映射
- 建立关键词到文章 ID 的映射
r = redis.Redis()r.sadd('keyword:mysql', 1, 5, 10, 15) # 文章 ID 包含 MySQL 关键词r.sadd('keyword:database', 1, 3, 7, 12)- 查询包含 MySQL 的文章
article_ids = r.smembers('keyword:mysql')6. 实在不行咱上 ES
这个时候到这个地步已经不是简单的 MySQL 能解决的事情了，专人办专事，你数据库数据实在太大了，已经不是数据库该干的活了。
