39.MySQL索引

发布于：2025-08-01 ⋅ 阅读:(17) ⋅ 点赞:(0)

1.一个实例

假设我们现在某一个数据库中创建一个有1000万条记录的数据库表，它的查询效率是怎样的呢？

-- 创建无主键和索引的表
CREATE TABLE user_data_raw (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100)
) ENGINE=InnoDB;

-- 创建存储过程（先检查是否存在，避免重复创建导致的错误）
DROP PROCEDURE IF EXISTS GenerateUserData;
DELIMITER $$
CREATE PROCEDURE GenerateUserData()
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_name VARCHAR(50);
    DECLARE v_email VARCHAR(100);
    DECLARE v_domain VARCHAR(20);
    
    START TRANSACTION;
    WHILE v_counter <= 10000000 DO
        SET v_name = CONCAT(
            ELT(FLOOR(1 + RAND() * 26), '张', '王', '李', '赵', '刘', '陈', '杨', '黄', '周', '吴', '徐', '孙', '马', '朱', '胡', '林', '郭', '何', '高', '罗', '郑', '梁', '谢', '宋', '唐'),
            ELT(FLOOR(1 + RAND() * 26), '伟', '芳', '娜', '秀英', '敏', '静', '强', '磊', '军', '洋', '勇', '艳', '杰', '娟', '涛', '明', '超', '云', '玲', '琳', '飞', '宇', '鹏', '丽', '建国')
        );
        
        SET v_domain = ELT(FLOOR(1 + RAND() * 5), 'gmail.com', 'yahoo.com', 'hotmail.com', 'qq.com', '163.com');
        SET v_email = CONCAT(SUBSTRING(v_name, 1, 1), FLOOR(RAND() * 10000), '@', v_domain);
        
        -- 显式插入 ID 值
        INSERT INTO user_data_raw (id, name, email) VALUES (v_counter, v_name, v_email);
        
        IF v_counter % 100000 = 0 THEN
            COMMIT;
            START TRANSACTION;
        END IF;
        
        SET v_counter = v_counter + 1;
    END WHILE;
    COMMIT;
END$$
DELIMITER ;

-- 执行存储过程生成数据
CALL GenerateUserData();

下面是执行结果：

可以看到sql脚本单单执行就花了两分钟，生成的数据库文件就有500多MB：

假设我们现在要查询id为122997的李秀英这条记录，一条查询语句需要执行多久呢？

SELECT * FROM user_data_raw WHERE id = 122997;

可见，该查询语句花了3秒钟多（根据电脑配置而定）。

现在为ID字段添加一个索引：

-- user_index 索引名称
-- ON user_data (id) : 表示在 user_data_raw表的 id列创建索引
CREATE INDEX user_index ON user_data_raw (id);

创建索引花了10秒多钟：

原先的数据库文件大小也增加到了700多MB：

再来使用带索引的字段查询一下刚才的记录：

SELECT * FROM user_data_raw WHERE id = 122997;

时间缩短到了0.001秒：

可见使用索引之后我们的查询效果翻倍。

总结一下：

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调 sql，查询速度就可能提高百倍千倍。

但并不是说使用索引就可以解决所有问题，其提高的查询效率只对带索引字段起效果，并且生成索引需要占用一大部分磁盘空间。

例如我们再次执行一下不使用索引的查询语句：依旧会花费很长时间。

SELECT * FROM user_data_raw WHERE `name` = '李秀英' AND `email` = '李7766@hotmail.com';

2.索引的原理

2.1没有索引为什么会慢？

在没有索引的情况下，执行查询语句所做的是全表扫描：

数据量多了，自然就慢了。

2.2有了索引为什么会快？

索引使用了特殊的数据结构来存储记录：

假设将id这个字段添加一个索引，该索引使用二叉搜索树这样的树型结构存储：

查询ID为9的记录只需要2次查询，与不适用索引的全表扫描相比，查询快非常多。

那假如我们使用索引查询30次，最低能覆盖多少条记录呢？

30 层的满二叉树有 2^30 - 1 个结点（计算得 1073741823 个）。

2.3索引的代价

1.磁盘的占用

索引的建立需要引入数据结构对记录的存储，会占用大量磁盘空间。在前一个示例中可见。

2.对 dml (update delete insert) 语句的效率影响

这些数据库语句执行时还需要对索引的结构进行修改，需要花费时间。

例如：删除id=9的这条记录

3.索引分类

3.1主键索引

主键自动的为主索引（类型 Primary key）

create table t1 (
  id int primary key, -- 主键，同时也是索引，称为主键索引.

3.2唯一索引

唯一索引（UNIQUE）

create table t2(
  id int unique, -- id 是唯一的，同时也是索引，称为 unique 索引.

3.3普通索引

普通索引（INDEX）

一般为那些普通的可以重复的字段建立的索引，例如NAME字段。

-- user_index 索引名称
-- ON user_data (id) : 表示在 user_data_raw表的 id列创建索引
CREATE INDEX user_index ON user_data_raw (id);

3.4全文索引

全文索引（FULLTEXT）[适用于 MyISAM引擎]

全文索引（FULLTEXT Index）是一种专门针对 长文本内容 的索引技术，核心作用是 让“关键词搜索文本”的效率大幅提升，解决传统 LIKE '%关键词%' 全表扫描的性能问题。下面从 概念、原理、实际例子 展开解释：

一、全文索引解决的核心问题

想象一个场景：你有一张表存了10万篇文章，现在要搜“人工智能”相关的内容。

  • 如果用 WHERE content LIKE '%人工智能%'：数据库会逐行扫描content字段，数据量大时慢到“无法接受”（时间复杂度O(N)，N是行数）。
  • 如果给content建全文索引：数据库会先把文本拆成关键词（如“人工智能”“技术”等），建立“关键词→出现位置”的映射，搜索时直接通过映射定位结果，速度呈数量级提升（类似字典的“拼音索引”，而非逐页翻找）。

二、全文索引的工作原理（倒排索引）

全文索引的核心是 倒排索引（Inverted Index），步骤如下：

  1. 分词：把文本拆成一个个“词”（英文按空格，中文需分词器，如MySQL的ngram会把“人工智能”拆成“人工”“智能”“人工智”“工智能”等片段）。
  2. 建索引：记录每个词出现在哪些行（文档）里。例如：
    • 文章1：“人工智能改变世界” → 词：人工智能、改变、世界
    • 文章2：“人工智能的技术突破” → 词：人工智能、技术、突破
    • 倒排索引：人工智能→[1,2]，改变→[1]，世界→[1] ...
  1. 搜索时：先对搜索词分词，再通过倒排索引快速找到包含这些词的行，还能按 相关性排序（比如词出现次数多的行排前面）。

三、实际例子：MySQL中的全文索引

以“博客系统搜文章”为例，步骤如下：

1. 创建表并添加全文索引

CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200),  -- 文章标题
  content TEXT,       -- 文章内容（长文本）
  -- 给title和content字段建全文索引
  FULLTEXT (title, content)  
) ENGINE=InnoDB;  -- MySQL 5.6+版本，InnoDB支持全文索引

2. 插入测试数据

INSERT INTO articles (title, content) VALUES
('AI发展趋势', '人工智能在医疗领域的应用越来越广泛'),
('编程技巧', 'Python和Java的语法差异分析'),
('科技前沿', '人工智能如何改变未来生活？');

3. 用全文索引搜索

场景1：搜“人工智能”相关文章

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('人工智能' IN NATURAL LANGUAGE MODE);
  • 结果：会返回标题或内容包含“人工智能”的行（第1、3条），且按 相关性排序（比如第3条多次提“人工智能”，可能排更前）。
  • 对比LIKE：如果数据量是10万条，全文索引可能毫秒级返回，LIKE可能需要几秒甚至更久。

场景2：复杂搜索（布尔模式，支持+必须包含、-必须排除）
比如，搜**包含“人工智能”但不包含“医疗”**的文章：

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+人工智能 -医疗' IN BOOLEAN MODE);
  • 结果：只返回第3条（“科技前沿”那篇，含“人工智能”但不含“医疗”）。

四、全文索引的适用场景

  1. 内容搜索：论坛帖子、新闻文章、商品描述的关键词搜索。
  2. 数据分析：从日志、报告中提取关键词（如从用户评论里搜“体验差”）。
  3. 简单场景：如果是复杂搜索（如精准中文分词、分布式），建议用Elasticsearch/Solr，但MySQL的全文索引胜在“和业务库一体，部署简单”。

五、关键注意事项

  1. 引擎和字段限制：仅支持InnoDB（5.6+）和MyISAM引擎，且字段必须是char、varchar、text类型。
  2. 中文分词问题：MySQL默认英文分词（按空格），中文需用ngram分词器（需手动配置，比如拆成2字或3字片段），否则分词效果差（比如“人工智能”会被拆成“人工”“智能”等，可能漏匹配）。
  3. 写入性能影响：建全文索引会增加写入耗时（因为要分词建索引），大数据量时需评估。

简单来说，全文索引是文本内容的“加速地图”，让你在海量文字里快速定位关键词，就像给书做了“目录”——不用逐页翻，直接找目录里的关键词跳转到对应内容。


一般开发，不使用 mysql 自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch（ES）：

Solr 是 “为企业级全文搜索而生的专业框架”，Solr 是 Apache 基金会开源的企业级搜索平台，基于 Apache Lucene 构建，专注解决海量文本数据的高效检索、分析与管理问题，为电商、媒体、金融等场景提供专业搜索能力。

ElasticSearch是一款分布式全文搜索引擎。

4.索引的使用

4.1创建索引

先创建一个表：

CREATE TABLE t01 (
    id INT ,
    `name` VARCHAR(32));

查看一下表中的索引信息：

SHOW INDEXES FROM t01;

可以看到表中的索引信息是没有的：

创建一个唯一索引：当我们认定某个字段的值不可以重复时优先采用唯一索引

CREATE UNIQUE INDEX id_index ON t01 (id);

再次查看一下表中索引：可以看到多出了一个唯一索引

创建一个普通索引（方式1）：优先为可以重复值的字段添加

CREATE INDEX name_index ON t01 (`name`);

再次查看一下表中索引情况：可以发现又多出了一个普通索引

再次创建一个新表：

CREATE TABLE t02 (
    id INT,
    `name` VARCHAR(32)
);

创建一个普通索引（方式2）：

ALTER TABLE t02 ADD INDEX name_index (`name`);

查看一下表2的索引情况：可以看到多出一个普通索引

创建一个主键索引：

ALTER TABLE t02 ADD PRIMARY KEY (id);

查看一下表2的索引情况：可以看到多出一个主键索引

4.2查看索引

使用以下语句可以查看表中的索引情况：

方式1：

SHOW INDEXES FROM table_name;

方式2：

SHOW INDEX FROM table_name;

方式3：

SHOW KEYS FROM table_name;

方式4：

DESC table_name;

例如这里的name中的key显示为mul就是指name这个字段存在普通索引：

4.3删除索引

例如删除表t01中的索引：

首先一般需要查看一下索引的名字：

SHOW INDEXES FROM t01;

再用命令指定删除对应名字的普通索引：例如删除name_index这个普通索引

DROP INDEX name_index ON t01;

查看以下结果：会发现t01这张表中只剩下一个以前创建的主键索引

由于主键索引的删除方式不一样，我们再来演示以下删除主键索引，以t02这张表为例子：

先查看一下t02中的索引情况：可以看到是存在一个主键索引的

SHOW INDEXES FROM t02;

ALTER TABLE t01 DROP PRIMARY KEY;

执行一下看结果：可以看到t02这张表中的主键索引被删除了。

5.创建索引的规则

小结：哪些列上适合使用索引？

  1. 较频繁的作为查询条件字段应该创建索引
    select * from emp where empno = 1
  2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
    select * from emp where sex = ' 男 '
  3. 更新非常频繁的字段不适合创建索引
    select * from emp where logincount = 1
  4. 不会出现在 WHERE 子句中字段不该创建索引

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布