MySQL 索引学习_索引学习-CSDN博客

本文链接：https://blog.csdn.net/qq_46039856/article/details/128687705

本文介绍了MySQL中的索引类型，包括主键索引、唯一索引和全文索引的使用，并通过示例展示了如何创建和使用全文索引。同时，对比了InnoDB和MyISAM存储引擎在索引上的差异，强调了索引设计的原则以及B+树在索引结构中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 索引学习

- 索引

索引

主键索引（PRIMARY KEY）
- 唯一标识，主键不可重复，只能有一个主键
唯一索引（UNIQUE KEY）
- 索引列
常规索引（KEY/INDEX）
全文索引（FullText）
- 可以快速定位数据

ALTER TABLE `account` ADD FULLTEXT INDEX `name`;

不使用全文索引

 EXPLAIN SELECT * FROM account WHERE name = 'B';

使用全文索引

 EXPLAIN SELECT * FROM account WHERE MATCH(name) AGAINST('B');

编写sql函数插入百万数据

DELIMITER $$ -- 写函数之前必须要写的标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
    DECLARE num INT DEFAULT 1000000;
    DECLARE i INT DEFAULT 0;
    WHILE i < num DO
        INSERT INTO account(`name`,`money`)
        VALUES(CONCAT('用户',i),i);
        SET i = i + 1;
    END WHILE;
    RETURN i;
END;

运行函数

SELECT mock_data();

插入百万数据后测试

创建索引

 CREATE INDEX name_idx ON account(`name`);

再查

imaage-20230114114937303

索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量表不需要加索引
一般加在常用来查询到字段上

回表：通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程。
以非主键列的大小为排序规则而建立的B+树需要执行回表操作才可以定位到完整的用户记录，这种B+树也称为二级索引或辅助索引。
以两个或两个以上个列的大小为排序规则建立的B+树称为复合索引，本质也是二级索引。
一个B+树索引的根节点自创建之日起便不会再移动（页号不会改变）
要保证B+树同一层内节点的目录项记录除页号这个字段以外是唯一的。
二级索引的内节点的目录项记录的内容分为三部分（索引列的值、主键值、页号）。
先按照二级索引列的值进行排序，在二级索引列值相同的情况下，再按照主键值进行排序。故为 c 列建立的索引相当于为（c，主键）列建立了一个联合索引。
唯一二级索引：某个列被声明为UNIQUE属性时，便会建立一个唯一二级索引。

MyISAM 和 InnoDB 索引的区别

InnoDB 中索引和数据的存储格式相同，有record_type标识来区分用户记录和目录记录，而 MyISAM 中索引与数据存储格式不同，索引是索引，数据是数据。
MyISAM 中所有索引都是二级索引，它的主键索引叶子节点存的是主键值与行号（定长记录），而变长记录存储的是主键值与地址偏移量。

9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ssssssssss99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ssssssssss99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ssssssssss99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ssssssssss99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ssssssssss99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999