增加 table_definition_cache

MySQL 索引核心知识点总结

一、索引基本概念

• ‌定义‌：索引是帮助 MySQL 高效获取数据的数据结构（如 B+树、Hash），通过减少磁盘 I/O 次数提升查询效率‌。
• ‌类比‌：类似于书籍的目录，通过快速定位数据行实现高效检索‌。
• ‌存储引擎差异‌：
  • InnoDB/MyISAM 默认使用 B+树索引‌。
  • Memory 引擎使用 Hash 索引（仅支持等值查询）‌。

二、索引类型

1. ‌按功能分类‌：
   • ‌主键索引（PRIMARY KEY）‌：唯一且非空，InnoDB 中为聚簇索引（数据与索引存储在一起）‌。
   • ‌唯一索引（UNIQUE）‌：列值唯一，允许空值‌。
   • ‌普通索引（INDEX）‌：无唯一性限制，仅加速查询‌。
   • ‌复合索引‌：包含多列，需遵循最左前缀匹配原则‌。
   • ‌全文索引（FULLTEXT）‌：适用于文本类型字段的模糊匹配（如 LIKE '%keyword%'）‌。
   • ‌前缀索引‌：对文本字段前 N 个字符建立索引，节省空间‌。
2. ‌按存储结构分类‌：
   • ‌聚簇索引‌：数据行与索引存储在一起（如 InnoDB 主键索引）‌。
   • ‌非聚簇索引‌：索引与数据行分离（如 MyISAM 索引）‌17

三、索引的优缺点

• ‌优点‌：
  • 降低查询的 I/O 成本，提升检索效率‌。
  • 减少排序和分组操作的 CPU 消耗‌。
• ‌缺点‌：
  • 占用额外磁盘空间，索引文件可能比数据文件更大‌。
  • 增删改操作需维护索引，降低写性能‌。

四、索引底层结构（B+树）

• ‌特点‌：
  • 多路平衡搜索树，树高度低，适合磁盘存储‌。
  • 叶子节点存储实际数据或主键值（非聚簇索引需回表查询）‌。
  • 支持范围查询和排序操作‌。

五、索引失效场景

1. ‌违反最左前缀法则‌：复合索引未从第一列开始使用‌。
   • 例：索引 (a, b, c)，查询条件为 b=1 或 c=1 时失效。
2. ‌范围查询右侧列失效‌：范围查询后的索引列无法使用‌。
   • 例：索引 (a, b)，条件 a>1 AND b=2 中 b 可能失效。
3. ‌对索引列进行运算或函数操作‌：如 WHERE YEAR(date_column)=2025‌。
4. ‌头部模糊匹配‌：如 LIKE '%abc'‌。
5. ‌OR 连接非索引列‌：若 OR 条件中包含未索引字段，全表扫描‌。
6. ‌隐式类型转换‌：如字符串字段未加引号（WHERE id='123' vs WHERE id=123）‌。

六、使用建议

1. ‌适用场景‌：
   • 频繁作为查询条件的字段（WHERE、JOIN）。
   • 需要排序或分组的字段（ORDER BY、GROUP BY）。
2. ‌避免滥用‌：
   • 数据量小的表无需索引。
   • 频繁更新的字段谨慎建索引。
3. ‌优化策略‌：
   • 优先选择区分度高的字段（如唯一性高的列）。
   • 控制复合索引的列数（一般不超过 5 列）‌。
   • 定期分析慢查询，调整索引策略‌。

七、操作语法示例

sqlCopy Code-- 创建索引  
CREATE INDEX idx_name ON table_name(column1, column2);  
-- 查看索引  
SHOW INDEX FROM table_name;  
-- 删除索引  
DROP INDEX idx_name ON table_name;

        td {
            background-size: 100% 100%;
            background-image: url("data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMTAwJSIgaGVpZ2h0PSIxMDAlIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KICA8bGluZSB4MT0iMCIgeTE9IjAiIHgyPSIxMDAlIiB5Mj0iMTAwJSIgc3Ryb2tlPSJibGFjayIgLz4NCjwvc3ZnPg==");
        }

    private function fullScanDir($dir): \Generator
    {
        $list = scandir($dir);
        foreach ($list as $filename) {
            if ($filename === '.' || $filename === '..') continue;
            if ($filename === '.git') continue;
            $aDir = sprintf("%s/%s", $dir, $filename);
            if (is_dir($aDir)) {
                foreach ($this->fullScanDir($aDir) as $_filename) {
                    yield $_filename;
                }
            } else {
                yield $aDir;
            }
        }
    }

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

继续阅读 →