Navicat 索引实战：MySQL 原理、EXPLAIN 与失效排查

索引这两个字在 Navicat 里就是一个页签点几下就能加上但加上之后到底有没有被用上、什么时候会失效、加多了要付出什么代价才是把人和人拉开的地方。我见过太多人打开 Navicat 的设计表窗口在索引页签里把 where 条件里出现过的字段一股脑全勾上结果上线当天写库延迟从 5ms 涨到 80ms最后还是老老实实删掉一半索引才恢复。所以这篇不谈虚的就把在 Navicat 里如何对一张表使用索引这件事从原理、界面入口、字段取舍、实操流程、失效排查到跨库差异一路讲透MySQL 为主Oracle、达梦、GBase 的差异也会带上。刚入行做增删改查的朋友可以照着走一遍写过几年 SQL 但一直靠感觉加索引的同学看完应该能省下不少试错成本。1. 索引不是加个勾的事先把原理捋直1.1 一本书的目录就是最好懂的索引模型想象你手里有一本 800 页的书要找第 37 章提到的某个概念有两种翻法。第一种是从第一页开始逐行扫扫到为止第二种是先翻目录定位到页码直接跳过去。数据库里的全表扫描就是第一种索引就是那个目录。区别在于书只有一本、目录只有一份而数据库的目录是可以有多个版本的——你可以按字段 A 建一份目录再按字段 B 建一份目录也可以按 A、B 组合建一份目录。InnoDB 的主键索引本身就是数据本身叶子节点挂着整行数据这叫聚簇索引次级索引也就是我们平时说的普通索引、唯一索引的叶子节点挂的是主键值拿到主键值之后还要再回主键索引里捞一次完整行数据这一步业内叫回表。理解回表这件事非常关键因为它直接决定了两个常见的优化手段覆盖索引查的列全在索引里不用回表和联合索引的字段顺序先等值、后范围让索引能被连续地扫过去。Navicat 的界面上只会显示索引名 字段 类型它不会告诉你回表不回表这部分得靠你自己在脑子里过一遍。还有一点经常被忽略InnoDB 的次级索引在存储时会自动把主键列追加到索引末尾。也就是说你在user_id上建了索引实际存储的键值是(user_id, id)。这个细节带来两个实际后果一是联合索引(user_id, status)排序时如果user_id相同会按id排二是如果你把主键设成UUID这种又长又随机的字符串所有次级索引都会被撑大页分裂也会变严重。这就是为什么生产库我基本都建议自增整型主键。1.2 为什么 Navicat 里那个索引页签值得你花十分钟搞懂Navicat 是图形化客户端它的价值在于把复杂的 DDL 变成了可视化操作但它本身不负责判断你加的索引合不合理。你填了字段、点了保存它就老老实实拼一条ALTER TABLE ... ADD INDEX ...发过去数据库执行完返回 OKNavicat 显示成功。它不会告诉你这条 SQL 会锁表 20 分钟也不会告诉你这个索引压根不会被优化器选中。我踩过的坑里最常见的就是在千万级表上用 Navicat 的设计表窗口顺手改了字段类型保存的瞬间整表重建业务侧连接池瞬间打满。所以正确的姿势是把 Navicat 当作执行器 观察器而不是决策器。决策靠三样东西——EXPLAIN的输出、索引的区分度计算、以及你对这张表读写比例的判断。Navicat 恰好把这三样东西都提供了入口查询窗口里能写EXPLAIN并可视化展示执行计划树表节点下能直接看到索引占用空间结构同步工具能帮你在测试库和生产库之间比索引差异。会用这三个入口效率比纯命令行高不少。另外提一句版本的事。Navicat 15、16、17 各版本在设计表里的措辞略有出入比如索引类型有的版本写成索引种类索引方法有的版本叫索引方式表节点下索引这个子目录在部分版本里要展开两层才看得到。下面我描述路径时会尽量把两种叫法都带上你照着找到对应位置就行。日常连接数据库、设计表、看执行计划这些操作Navicat 官方提供的免费版本已经完全够用没必要去折腾来路不明的安装包——补丁包夹带东西的概率远高于它能帮你省下的那点事真出了问题排查成本才是大头。1.3 加索引的代价写放大、磁盘空间与优化器负担索引不是白送的。每加一个次级索引这张表的每一次INSERT都多一次索引维护每一次UPDATE只要改了索引列就多一次删旧插新每一次DELETE也要在索引里打标记。业内粗略的说法是一个索引大概会让写入开销增加 10% 到 20%一张表上挂五六个索引写入性能掉一半并不夸张。所以读多写少的表可以大胆加索引写多读少的日志表、埋点表索引数量要压到最低。磁盘空间这笔账也要算。索引本身也要占页、占 buffer pool。我见过一张 2000 万行的订单表数据 6GB索引加起来 9GB索引比数据还大——原因就是建了一堆长字符串字段的组合索引。这种情况在 Navicat 里很容易自查右键表 → 表信息/对象信息或者直接查information_schema.TABLES看data_length和index_length两个值谁大。索引超过数据本身基本说明索引设计有问题。最后是优化器的负担。索引不是越多越好选择越多优化器估算代价的时间越长选错索引的概率也越大。MySQL 里有个不算罕见的场景明明有合适的联合索引优化器偏偏选了个区分度很差的单列索引结果扫描行数反而更多。这种时候通常要靠FORCE INDEX或者干脆把没用的索引删掉来纠偏。单表索引数量我个人建议控制在 5 个以内超过 6 个就该回头审视了除非有非常明确的业务理由。2. Navicat 里给表加索引的三条路分别适合谁2.1 图形化设计表最快也最容易踩坑路径在左侧对象树里找到目标库 → 展开表 → 右键目标表 →设计表快捷键 CtrlD→ 切到索引 / Indexes页签 → 点左下角加号新增一行 → 依次填写名索引名、字段点右侧省略号选列可以加多列并调整顺序、索引类型、索引方法、注释 → 点保存。这个页签里几个字段的含义解释一下。名就是索引名同一张表内不能重复重复会报Error 1061: Duplicate key name字段里那个小三角可以下拉选择字段排序方向ASC/DESCMySQL 8.0 才开始真正支持降序索引8.0 以前写 DESC 也是当 ASC 建的索引类型通常有 Normal普通、Unique唯一、Full Text全文、Spatial空间、Primary主键几种索引方法多数情况下只有 BTREE 可选HASH 只在 MEMORY 引擎上才有意义InnoDB 填了也是白填。这个入口最大的问题在于不可控。你点保存的时候Navicat 会弹出表结构已改变是否保存确认之后它就直接把 DDL 发到数据库执行了默认不带ALGORITHMINPLACE, LOCKNONE这类在线 DDL 参数。小表无所谓百万行以上的表这个动作有可能让这张表在整个执行期间处于不可写状态。我的习惯是保存前先点SQL 预览有些版本叫显示 SQL或DDL 预览把生成的语句复制到查询窗口手动补上在线 DDL 参数再执行。注意在设计表窗口里一次性改了索引又改了字段类型Navicat 可能把这两件事合并成一条 ALTER。字段类型变更本身就会重建索引等于你的索引也白改了一遍。分两次操作会稳一点。2.2 表节点下的索引目录查看和清理的利器路径对象树里展开某张表 → 你会看到列索引外键触发器这几个子节点部分版本要展开对象才看得到→ 展开索引里面列的就是这张表当前所有索引。右键单条索引有设计索引删除索引等选项某些版本还能直接打开索引分析。这个入口我更常用来做两件事。第一件是盘点点开看看有几个索引、字段顺序对不对、有没有前缀长度写错的。第二件是清理把明显不会用到的索引删掉。判定不会用到有个很硬的依据就是sys.schema_unused_indexes这个视图它基于performance_schema.table_io_waits_summary_by_index_usage统计出来的SELECT object_schema, object_name, index_name FROM sys.schema_unused_indexes WHERE object_schema NOT IN (mysql, sys, information_schema, performance_schema);注意这个统计是从实例启动或者统计重置之后开始算的如果业务有明显的周期特性比如月末结算才走那条查询一定要跨过完整周期再判断否则会把偶尔才用一次的索引误删。删之前还有个保险做法MySQL 8.0 支持不可见索引先把索引设成INVISIBLE观察一两周确认业务无感知再真删。ALTER TABLE t_order ALTER INDEX idx_city INVISIBLE; -- 先隐藏 -- 观察一段时间后 ALTER TABLE t_order ALTER INDEX idx_city VISIBLE; -- 后悔了就改回来 DROP INDEX idx_city ON t_order; -- 确认无用再删不可见索引这个功能Navicat 的图形界面上通常没有对应开关我做过的几个版本都没有得在查询窗口手写 SQL。这也是为什么我不建议只会用图形界面的同学就此停下——SQL 是底座界面只是外壳。2.3 查询窗口手写 DDL可控性最高生产环境首选生产环境加索引我一律走这条路。在 Navicat 里新建一个查询CtrlQ手写语句先跑EXPLAIN验证再执行 DDL再跑一次EXPLAIN对比。这样每一步都留痕出问题能立刻回滚。MySQL 里创建索引的几种写法记住这几种就够用-- 建表时直接定义 CREATE TABLE t_demo ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, col_a VARCHAR(64) NOT NULL, col_b INT NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_col_a (col_a), KEY idx_col_b (col_b) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; -- 表建好之后追加 CREATE INDEX idx_col_b ON t_demo (col_b); ALTER TABLE t_demo ADD INDEX idx_col_a_b (col_a, col_b); ALTER TABLE t_demo ADD UNIQUE INDEX uk_col_a_b (col_a, col_b); -- 指定在线 DDL尽量不阻塞读写MySQL 5.6 ALTER TABLE t_demo ADD INDEX idx_col_b (col_b), ALGORITHM INPLACE, LOCK NONE; -- 删除与重命名 ALTER TABLE t_demo DROP INDEX idx_col_b; ALTER TABLE t_demo RENAME INDEX idx_col_b TO idx_col_b_new; -- 5.7三种入口的取舍我整理成一张表你可以对着自己的场景挑入口上手难度可控性适合场景主要风险设计表 → 索引页签最低低本地开发、小表、随手验证大表直接执行无在线参数可能长时间锁表表节点 → 索引目录低中盘点已有索引、删除无用索引、观察空间改字段顺序时容易误删误建查询窗口手写 DDL中高生产环境、批量变更、需要留痕拼错语句报错需自己备份与回滚预案提示ALGORITHMINPLACE, LOCKNONE不是万能的加全文索引、改主键、加空间索引这些操作在某些版本上仍然会退化成拷贝表。执行前用EXPLAIN无法判断得靠经验或者先在从库上试。3. 索引类型怎么挑从主键到联合索引的取舍逻辑3.1 主键索引、唯一索引与普通索引别混着用主键索引有两个硬约束不能为 NULL且必须唯一一张表只能有一个。InnoDB 里它还兼任聚簇索引的角色表数据就存在它的叶子节点上。所以主键的选择其实是在选物理存储顺序——自增主键会让新数据永远追加到最右边页利用率高随机主键UUID、随机字符串会让插入点在整棵树里乱跳导致频繁页分裂这也是为什么很多团队宁愿多一个业务无关的自增 ID。唯一索引是加了唯一约束的普通索引它的作用是拿写入时的校验开销换业务上的数据一致性。像手机号、订单号这种业务上不允许重复、查询又高频的字段上唯一索引很划算。但要注意一点MySQL 里 NULL 值不参与唯一性判断也就是UNIQUE列上可以插入多条 NULL如果你的业务逻辑依赖非空且唯一还得再加NOT NULL约束光靠唯一索引是不够的。普通索引就是最纯粹的加速结构没有额外约束。它的区分度决定了它值不值得存在计算方式很直白SELECT COUNT(*) AS total_rows, COUNT(DISTINCT city) AS distinct_city, ROUND(COUNT(DISTINCT city) / COUNT(*), 4) AS selectivity FROM t_order;区分度selectivity越接近 1 越好。像性别是否删除这种只有两三个取值、区分度接近 0 的字段单独建索引意义很小优化器经常直接放弃它去全表扫。这种字段的正确用法是放进联合索引里当辅助列用来收窄扫描范围而不是单独拎出来建索引。3.2 联合索引与最左前缀字段顺序决定成败联合索引(a, b, c)的存储顺序是先按 a 排a 相同再按 b 排b 相同再按 c 排。所以它能被用上的条件是从最左边开始连续匹配。这就是最左前缀原则。下面几种情况用同一套索引效果天差地别-- 索引idx_abc (a, b, c) SELECT * FROM t WHERE a 1; -- 用上 a SELECT * FROM t WHERE a 1 AND b 2; -- 用上 a、b SELECT * FROM t WHERE a 1 AND b 2 AND c 3; -- 全用上 SELECT * FROM t WHERE b 2; -- 用不上跳过了 a SELECT * FROM t WHERE a 1 AND c 3; -- 只用到 ac 用不上 SELECT * FROM t WHERE a 1 AND b 2 AND c 3; -- 用到 a、bc 用不上b 是范围最后一条是新手最容易翻车的地方索引里只要出现了范围条件它右边的列就用不上索引排序能力了。所以字段顺序的通用口诀是等值条件在前范围条件在后排序字段尽量跟在等值条件后面。假设业务里最常见的查询是按用户查某个状态的订单按时间倒序取前 20 条那索引就应该建成ALTER TABLE t_order ADD INDEX idx_user_status_created (user_id, status, created_at);这样 where 里的user_id、status两个等值条件把范围收窄ORDER BY created_at DESC LIMIT 20就能直接借用索引的最后一段有序性不需要额外的排序动作——在EXPLAIN的 Extra 列里会看到Using index condition而没有Using filesort这就是成功信号。3.3 前缀索引、全文索引与函数索引各管一摊前缀索引针对的是长字符串列。像VARCHAR(255)的邮箱、URL全字段建索引又大又慢只取前 N 个字符就够了。长度取多少不是拍脑袋而是算出来的SELECT COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel_8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12, COUNT(DISTINCT LEFT(email, 16)) / COUNT(*) AS sel_16, COUNT(DISTINCT email) / COUNT(*) AS sel_full FROM t_user;取区分度基本逼近全字段区分度的最短长度比如 12 就到了 0.997、16 也是 0.997那就用 12能省下不少空间。建的时候写成ADD INDEX idx_email (email(12))。代价是前缀索引不能用于覆盖索引也不能用于ORDER BY全字段排序只负责过滤。全文索引用在长文本的模糊匹配上替代LIKE %关键词%。InnoDB 从 5.6 起支持ALTER TABLE t_article ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;中文必须带 ngram 解析器否则分词结果不符合中文习惯。查询用MATCH(content) AGAINST(关键词 IN BOOLEAN MODE)。它的维护成本比普通索引高不少写入时开销明显数据量不大或者查询模式不复杂的话直接用外部检索组件更省事。函数索引MySQL 8.0.13解决的是where 里对列用了函数导致索引失效的问题可以给你用到的表达式直接建索引ALTER TABLE t_order ADD INDEX idx_ymd ((DATE(created_at))); -- 或者用生成列的方式5.7 也支持 ALTER TABLE t_order ADD COLUMN created_ymd DATE AS (DATE(created_at)) STORED; ALTER TABLE t_order ADD INDEX idx_created_ymd (created_ymd);我更偏向后一种因为生成列是真实的列Navicat 的界面上能直接看到别人接手代码也好理解函数索引在 Navicat 里显示成一段表达式容易看懵。3.4 覆盖索引让查询不回表的那点事覆盖索引不是一个独立的索引类型而是一种索引恰好够用的状态。当查询需要的所有列都能从索引里拿到InnoDB 就不必再回主键索引捞数据EXPLAIN的 Extra 列会出现Using index性能提升往往是成倍的。举个具体例子索引是(user_id, status, created_at)那么-- 不回表Extra 显示 Using index SELECT user_id, status, created_at FROM t_order WHERE user_id 10086 AND status 1; -- 要回表因为 amount 不在索引里 SELECT user_id, amount FROM t_order WHERE user_id 10086 AND status 1;这解释了一个非常常见的争论到底该不该写SELECT *。SELECT *除了会多传无用字段、影响网络和内存之外最大的问题就是任何索引都无法覆盖它全部查询都要回表。反过来如果你为了让某个高频查询走覆盖索引把三四个字段都塞进联合索引索引体积会迅速膨胀写入代价也跟着涨。这笔账得具体算读的次数远大于写、且这个查询在接口里出现在关键路径上那就值得为覆盖索引让路。提示在 Navicat 里判断有没有走覆盖索引看执行计划最直观。点查询窗口工具栏上的解释/Explain按钮执行计划树里每个节点都会标出访问类型和额外信息切到表格视图能看到完整的 Extra 列文字。4. 完整实操一张 50 万行订单表从全表扫描到走索引4.1 建表与造数据先用递归 CTE 铺一批像样的数据光讲理论没感觉我们直接在 Navicat 里走一遍完整流程。先建一张结构和真实业务接近的表字段包括订单号、用户、状态、金额、城市、备注、创建时间CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00, city VARCHAR(32) DEFAULT NULL, remark VARCHAR(255) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;造 50 万行数据。MySQL 8.0 用递归 CTE 最快一条语句搞定5.7 只能写存储过程循环慢一些但不影响结论SET SESSION cte_max_recursion_depth 600000; INSERT INTO t_order (order_no, user_id, status, amount, city, remark, created_at) WITH RECURSIVE seq(n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 500000 ) SELECT CONCAT(NO, LPAD(n, 10, 0)), FLOOR(1 RAND() * 20000), FLOOR(RAND() * 5), ROUND(RAND() * 1000, 2), ELT(1 FLOOR(RAND() * 5), 北京, 上海, 广州, 深圳, 杭州), CONCAT(备注内容, n), NOW() - INTERVAL FLOOR(RAND() * 365) DAY FROM seq;跑完记得ANALYZE TABLE t_order;让统计信息刷新不然优化器拿到的行数估算可能是错的后面的EXPLAIN结果会失真。这一步我在实际工作中强调过很多次——统计信息不准导致的索引失效占了我遇到的所有索引问题的两成以上。4.2 先看慢查询长什么样EXPLAIN 逐列解读现在假设业务有个高频查询查某个用户已支付的订单按下单时间倒序取前 20 条。此刻表上只有主键索引执行计划会是这样EXPLAIN SELECT id, order_no, amount, created_at FROM t_order WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20;结果里type大概率是ALLpossible_keys是NULLkey是NULLrows接近 500000Extra 里带着Using where; Using filesort。这就是典型的全表扫描 额外排序50 万行的表在测试机上通常要几百毫秒放到千万行级别就是秒级。EXPLAIN的列不少但真正需要盯的就这么几个我做了张表方便你对照列名含义该看什么type访问类型从好到坏system const eq_ref ref range index ALL。出现 ALL 或 index 就要警惕possible_keys可能用到的索引有值但 key 为 NULL说明优化器算完觉得不划算要考虑区分度或统计信息key实际选中的索引和你的预期不一致时重点排查key_len索引实际使用长度联合索引用到了几个字段这里能看出来rows预估扫描行数越小越好和实际差距过大说明统计信息过期filtered过滤后剩余比例百分比配合 rows 估算最终结果集Extra额外信息Using index 是好事Using filesort / Using temporary 要优化关于key_len有个小技巧InnoDB 里BIGINT是 8 字节、TINYINT是 1 字节、允许 NULL 还要加 1 字节、VARCHAR(n)是3n2utf8mb4 下最多占 4 字节/字符实际按最大字节数算。用key_len反推联合索引用到了第几列比一个个试快得多。比如索引(user_id, status, created_at)user_id BIGINT NOT NULL是 8 字节status TINYINT NOT NULL是 1 字节created_at DATETIME NOT NULL是 5 字节8.0 之前是 8 字节。如果key_len显示 9说明只用到前两列显示 14说明三列全用上了。4.3 在 Navicat 里建联合索引并做前后对比确定了索引方向接下来动手。我还是建议走查询窗口把 DDL 和验证写在一起一条条执行-- 第一步建联合索引字段顺序 等值条件 排序字段 ALTER TABLE t_order ADD INDEX idx_user_status_created (user_id, status, created_at), ALGORITHM INPLACE, LOCK NONE; -- 第二步刷新统计信息 ANALYZE TABLE t_order; -- 第三步再跑一次同样的 EXPLAIN EXPLAIN SELECT id, order_no, amount, created_at FROM t_order WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20;理想的结果是type变成refkey显示idx_user_status_createdkey_len覆盖到三列rows掉到几十或几百Extra 里Using filesort消失换上Using index condition或者去掉Using where。这就是索引真正生效的样子。如果你想知道真实执行时间和真实行数别止步于EXPLAINMySQL 8.0.18 之后可以用EXPLAIN ANALYZE它会把语句真的跑一遍并给出各节点的实际耗时EXPLAIN ANALYZE SELECT id, order_no, amount, created_at FROM t_order WHERE user_id 10086 AND status 1 ORDER BY created_at DESC LIMIT 20;这一步我个人非常推荐在 Navicat 里做因为估算值和实际值偏差大的时候问题基本都出在统计信息或者数据分布倾斜上看EXPLAIN是看不出来的。顺便看一眼索引带来的空间开销心里有个数SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.TABLES WHERE table_schema demo AND table_name t_order;50 万行、每行几百字节的表数据大概几十 MB一个 B 树索引大概十几 MB这个比例是健康的。要是你看到一个索引的体积接近甚至超过数据体积八成是索引列太长或者前缀没截。4.4 索引的日常维护重命名、删除、重建与可见性索引建完不是终点。业务演变之后原来高频的查询可能下线了索引就成了纯粹的负担。日常维护动作有这么几个都能在 Navicat 里完成重命名在 MySQL 5.7 之后有专门的语法比删了再建安全得多因为不涉及数据重建ALTER TABLE t_order RENAME INDEX idx_user_status_created TO idx_us_created;删除索引很简单但必须先确认没有查询依赖它。最稳的流程是先用sys.schema_unused_indexes和慢查询日志双重确认再把索引设成INVISIBLE跑一周最后才DROP INDEXALTER TABLE t_order ALTER INDEX idx_city INVISIBLE; DROP INDEX idx_city ON t_order;重建索引这个动作要特别谨慎。MySQL 里没有ALTER INDEX ... REBUILD这种语法那是 Oracle 的想重建只能先删再建或者用ALTER TABLE t_order ENGINEInnoDB;让整表重建——后者会锁表并重建所有索引大表上非常危险。碎片整理更安全的替代方案是OPTIMIZE TABLE本质也是重建但同样不建议在业务高峰做。我个人的做法是碎片率不高就不折腾真到了必须重建的程度用工具在从库做完再切主。还有一种情况是索引被临时禁用。MySQL 8.0 用INVISIBLE表达这个语义Oracle 用UNUSABLE两者思路一致但语法完全不同下一节讲。5. 索引失效的 8 个高频场景与排查清单5.1 SQL 写法踩的坑函数、隐式转换、前导通配符索引建好了、EXPLAIN也验证过但线上还是慢八成是代码里换了写法。下面这几个场景我几乎在每家公司都遇到过。在索引列上套函数。WHERE DATE(created_at) 2024-05-01直接让created_at上的索引用不上。正确写法是改写成范围WHERE created_at 2024-05-01 00:00:00 AND created_at 2024-05-02 00:00:00。同理WHERE YEAR(created_at) 2024、WHERE UPPER(name) ABC都是一个毛病。隐式类型转换。字段是VARCHAR但参数传了数字MySQL 会把列转成数字再比较索引失效。最典型的是订单号WHERE order_no 20240501000123order_no是VARCHAR(32)加上引号才是对的。反过来字段是数字类型传字符串同样可能出问题。这个坑隐蔽性极高因为结果集看起来是对的只是慢。前导通配符。LIKE %关键字%和LIKE %关键字都用不上索引只有LIKE 关键字%这种右模糊才行。这个属于索引的物理结构决定的B 树只能按前缀有序找没法从中间找起。真要做全文模糊搜索要么上全文索引要么交给外部检索组件。联合索引不满足最左前缀。前面提过这里再强调一个变体WHERE a 1 AND c 3这种跳过中间列的情况索引只能用上a。很多团队为了兼容各种查询组合会建(a)、(a, b)、(a, b, c)三个索引其实只要(a, b, c)一个就够了因为(a)和(a, b)都是(a, b, c)的前缀优化器可以直接用长索引代替短索引。OR 连接不同列。WHERE a 1 OR b 2如果a和b上各有索引优化器有可能走索引合并但代价通常不低而且一旦某一列没索引就直接退化成全表扫。这种情况改成UNION ALL拆成两条语句往往更快。对索引列做运算。WHERE id 1 100这种写法索引列参与了运算同样用不上。改成WHERE id 99就行。排序方向与索引不一致。索引(a ASC, b ASC)查询ORDER BY a ASC, b DESCMySQL 8.0 之前一定会走 filesort。要么把索引建成(a ASC, b DESC)8.0 支持降序索引要么调整排序逻辑。!、NOT IN、IS NOT NULL这类否定条件。它们往往需要扫描大量数据优化器会判断走索引再回表还不如直接全表扫。这不是索引坏了而是它本来就不适合这种查询。真遇到这种需求考虑换个思路——比如用标记删除 只查未删除替代WHERE deleted_at IS NULL。5.2 优化器不想用索引的几种情况写法没问题索引还在但优化器就是不选这类问题更费神。常见原因有四个。区分度太低。某列只有两三个取值索引扫出来的行数接近全表优化器算完代价直接放弃。这种情况你没写错是索引本身不该单独建。要么把它合并进联合索引当辅助列要么接受全表扫描。统计信息过期或失真。大批量导入、删除之后索引的基数cardinality统计可能还是旧的。SHOW INDEX FROM t_order;看Cardinality列如果明显偏离实际跑一次ANALYZE TABLE t_order;。Navicat 的索引目录页里也能看到基数这一列只是不同版本位置不太一样。索引列参与了隐式排序。当查询的ORDER BY和索引顺序不一致、又加上LIMIT时优化器可能觉得走索引再排序不如全表扫完排序取前 N 条。这种情况用FORCE INDEX强制走索引有时反而更快但要用EXPLAIN ANALYZE实测对比再决定别凭感觉。大范围查询的代价估算。查最近一年的数据、扫描行数占全表 30% 以上时走索引需要大量随机回表优化器选全表顺序扫描是合理决策。这时候该考虑的就不是索引了而是分区表、归档冷数据、或者预先做汇总表。5.3 一份可以直接照着走的排查流程写了这么多场景实际排查时容易乱。我整理了一个固定顺序从快到慢、从便宜到贵步骤动作判断依据1在 Navicat 查询窗口跑EXPLAIN看 type、key、rows、Extra 四项2跑EXPLAIN ANALYZE对比估算与实际偏差超过一个量级先怀疑统计信息3SHOW INDEX FROM 表名看 Cardinatlity基数远小于实际行数执行 ANALYZE TABLE4检查 SQL 写法函数、隐式转换、前导通配符、OR、最左前缀5检查 SQL 参数类型与字段类型必须一致字符串别少引号6检查是否存在隐式字符集/排序规则转换关联字段的字符集与排序规则必须一致7用FORCE INDEX做对照实验强制走索引是否真的更快用实测说话8检查索引是否可用MySQL 看IS_VISIBLEOracle 看STATUS是否 UNUSABLE第 6 条值得单说一句。两个表 JOIN 时如果关联字段一个是utf8mb4_general_ci、一个是utf8mb4_unicode_ci会产生隐式转换索引直接失效。这类问题用SHOW CREATE TABLE对比两张表的字符集设置就能看出来但很多人在排查时完全不会往这个方向想。注意FORCE INDEX是诊断工具不是长期方案。它能让优化器照你的意思执行但数据分布一旦变化它可能从救星变成灾难。用它验证完结论之后优先考虑调整索引结构或 SQL 写法本身。6. 跨库差异Oracle、达梦、GBase 在 Navicat 里的索引操作6.1 Oracle 的索引禁用与重建图形界面给不了你Oracle 和 MySQL 在索引这块的差异挺大Navicat 连 Oracle 时设计表窗口里的索引页签能配的选项也不一样除了普通、唯一还会多出位图Bitmap、函数索引Function-based这类选项因为 Oracle 对索引类型的支持面更宽。位图索引适合低区分度的列比如性别、状态但它的锁粒度较大并发写入场景要慎用——这一点和 MySQL 的取舍逻辑是反的别照搬经验。Oracle 里有个 MySQL 完全没有的能力把索引设为不可用。这不只是优化器看不见而是索引本身不维护了好处是能立刻停掉一个没用的索引带来的写入开销同时保留索引定义随时恢复-- 禁用索引DDL会短暂持锁 ALTER INDEX idx_order_user UNUSABLE; -- 恢复索引会重建大表耗时较长建议加 ONLINE 减少阻塞 ALTER INDEX idx_order_user REBUILD ONLINE; -- 查看索引状态 SELECT index_name, status, visibility, num_rows, distinct_keys FROM user_indexes WHERE table_name T_ORDER;STATUS为UNUSABLE就是被禁用了为VALID是正常。Oracle 还支持不可见索引INVISIBLE语义和 MySQL 8.0 的一样——索引正常维护但优化器默认看不见它用来做上线前的灰度验证很顺手ALTER INDEX idx_order_user INVISIBLE; ALTER INDEX idx_order_user VISIBLE;Navicat 连 Oracle 时这些操作在图形界面上基本找不到对应按钮得在查询窗口手写。另外Oracle 里执行 DDL 会隐式提交当前事务这一点和 MySQL 的 DDL 行为不太一样在 Navicat 里连着跑一串 DDL 之前记得确认没有未提交的数据修改。6.2 达梦与 GBase 的索引存储属性国产库里有些特殊的索引属性值得单独提一下。达梦在创建索引时可以指定存储子句比如STORAGE(CLUSTERBTR)这类写法用来控制索引的物理组织方式。CLUSTERBTR 属于聚簇 B 树结构主要价值在于把索引和数据在物理上尽量靠近减少随机回表的 IO 开销——某些点查场景下它比普通 B 树索引的响应更稳定。它在 Navicat 里同样没有可视化的勾选项只能在建索引的 SQL 里带上存储子句CREATE INDEX idx_order_user ON t_order(user_id) STORAGE(CLUSTERBTR);要注意的是聚簇索引对写入的影响比普通索引大因为物理位置需要维护。如果你的表写入很频繁、查询又主要是范围扫那老老实实用默认的 B 树就好别盲目追聚簇。GBase 这边要分清楚产品线。GBase 8s 的语法风格接近传统关系型数据库用CREATE INDEX ... ON ...这类标准写法就能建Navicat 连接后按常规方式操作即可。而 GBase 8a 是列存分析型数据库它的索引概念和行存库完全不是一回事走的是智能索引粗粒度数据块过滤那一套你按 MySQL 的思路去建 B 树索引效果会很有限。这点在选型阶段就该搞清楚别等上了生产才发现索引怎么建都提不上速。跨库操作还有两个通用注意点。第一Navicat 的结构同步能把测试库的索引定义同步到另一个库但不同数据库之间的语法差异它处理得并不完美同步完一定看一遍生成的 SQL特别是 Oracle 的表空间子句、达梦的存储子句这种很容易被漏掉或改错。第二不管是哪个库加索引前先在非生产环境验证一遍执行计划这个习惯比记多少条语法都有用。7. 实操心得与避坑记录7.1 在 Navicat 里改结构的几条铁律第一条改结构之前一定先导出一份结构 SQL。右键表 → 转储 SQL 文件 → 仅结构几秒钟的事但能在你把索引改坏时救你一命。我给自己的规矩是任何涉及 ALTER 的操作手上必须有一份可回滚的建表语句和一个能回滚的窗口期。第二条批量改别一次改一个。MySQL 执行一次 ALTER 就要重建或修改索引结构同一个表上连着跑五条ADD INDEX等于干了五遍活。把变更合并成一条ALTER TABLE t_order ADD INDEX idx_a (col_a), ADD INDEX idx_b (col_b, col_c), DROP INDEX idx_old, ALGORITHM INPLACE, LOCK NONE;第三条大表加索引用在线 DDL 参数并且要有超时预案。LOCKNONE不保证一定成功如果某个版本不支持该操作类型它会直接报错退出这其实是好事比默默锁表强。在 Navicat 里跑 DDL 之前先把该会话的lock_wait_timeout看一眼——默认值是 31536000 秒也就是一年等于不超时一旦被阻塞会一直挂着并堵住后续所有请求。执行前临时调小是一种保护SHOW VARIABLES LIKE lock_wait_timeout; SET SESSION lock_wait_timeout 30;第四条别在业务高峰期做结构变更。这一点没有技术含量但踩坑的人最多。窗口期的选择有时候比索引设计本身更影响线上体验。7.2 索引数量与空间的自查方法长期维护一张表得定期体检。我在 Navicat 里常跑的几条自查 SQL 如下建议存成查询收藏隔一段时间过一遍-- 1. 哪些索引从来没被用过跨过完整业务周期再看 SELECT object_schema, object_name, index_name FROM sys.schema_unused_indexes WHERE object_schema demo; -- 2. 表数据与索引的体量对比 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND(index_length / NULLIF(data_length index_length, 0) * 100, 2) AS idx_pct FROM information_schema.TABLES WHERE table_schema demo ORDER BY index_length DESC; -- 3. 重复或可被替代的索引前缀重复 SELECT table_name, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols FROM information_schema.STATISTICS WHERE table_schema demo AND table_name t_order GROUP BY table_name, index_name;第 3 条查出来的是每个索引的列组合肉眼扫一遍就能发现(a, b, c)和(a)这种冗余关系——后者是前者的前缀可以直接删掉短的优化器会用长的替代。这一招清理索引特别高效我一般半年做一次能清掉三成左右的冗余索引写入性能提升肉眼可见。idx_pct这个值也值得盯。我的经验阈值是索引总量占表总大小的比例长期超过 60%就要开始审视有没有长字段索引、可被前缀索引替代的索引、以及已经完全没人用的历史索引。7.3 常见问题速查表下面这些是我在实际工作中被问得最多的 Navicat 索引相关问题整理成表格遇到直接对号入座现象可能原因处理方式Navicat 保存索引时报 1061索引名与已有索引重名换个索引名或先看索引目录确认保存时卡住很久没反应大表 ALTER 无在线参数正在拷贝表等到执行完或 kill 会话下次改用手写 DDL 加 LOCKNONEEXPLAIN里 key 一直是 NULL区分度低、统计信息过期、写法不匹配先 ANALYZE TABLE再逐条检查 5.1 的写法清单加了索引反而更慢索引过多导致写入放大或优化器选错索引看sys.schema_unused_indexes清理用 EXPLAIN ANALYZE 对比联合索引明明建了却用不上字段顺序不对跳过了最左列按等值在前、范围在后、排序靠后重建前缀索引建完查询还是慢前缀长度选太短过滤能力不足用 LEFT() 区分度计算重新选长度索引占的空间比数据还大长字段全列索引、冗余索引过多改前缀索引删冗余索引索引删了之后查询变慢误删了还在用的索引立刻重建后续删除前先设 INVISIBLE 观察一周Navicat 里看不到不可见索引开关该功能图形界面未提供查询窗口执行ALTER INDEX ... INVISIBLEOracle 里索引状态异常索引被设为 UNUSABLE常见于大批量导入后执行ALTER INDEX ... REBUILD ONLINE恢复最后再分享一个我自己的小习惯每建一个索引都在索引注释里写清楚为哪个接口、哪条查询服务。Navicat 的索引页签有注释这一栏MySQL 里对应的是COMMENT子句这样半年后回头看或者移交给同事的时候不用再去翻代码找索引的来龙去脉。这个动作花不了十秒钟但能省掉很多次这个索引能不能删的会议讨论。索引这东西用得好是加速器用不好就是隐形的负债关键从来不在点几下鼠标而在于你有没有想清楚它到底替谁干活。