mysql外键索引的建立（建表时）和查看

最新推荐文章于 2025-06-04 05:58:32 发布

原创最新推荐文章于 2025-06-04 05:58:32 发布 · 3.1k 阅读

12 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了数据库索引的设计原理及其在实际应用中的实现方法，通过具体的学生选课模型案例，讲解了如何利用索引提高查询效率，并探讨了复合索引的最左原则。

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

索引的意义就不说了，参考其他资料，总之就是大大加快查询的速度。牺牲了小部分物理空间，毕竟索引也是要存的。（B+树原理）

所以设计索引的时候尽量做到区分度好，例如默认情况下主键就是一个索引。
经典的多对多学生选课模型，学生表students，课表courses，学生选课表sc
实体联系模型
CREATE TABLE student (
Sno varchar(20) NOT NULL,
Sname varchar(30) DEFAULT NULL,
Ssex varchar(2) DEFAULT NULL,
Sage int(11) DEFAULT NULL,
Sdept varchar(30) DEFAULT NULL,
PRIMARY KEY (Sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这里可以用 show index from student 来查看索引
查询结果

Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。这里是主键，所以肯定是0啦
Seq_in_index 索引中的列序列号，从1开始。这里sno是第一列，所以是1
Collation 列以什么方式存储在索引中。有值‘A’（升序）或NULL（无分类）。
Cardinality 索引中唯一值的数目的估计值。我的表里大概有39个，随时变动。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null 如果列是否含有Null值
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
comment 注释什么的

课程表
CREATE TABLE course (
Cno varchar(10) NOT NULL,
Cname varchar(30) DEFAULT NULL,
Chour int(11) DEFAULT NULL,
Ccredit varchar(30) DEFAULT NULL,
Cpno varchar(10) DEFAULT NULL,
PRIMARY KEY (Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生选课表的建立：

  CREATE TABLE `sc`(
      `Sno` varchar(20) NOT NULL,
      `Cno` varchar(10) NOT NULL,
      `Grade` int(11) DEFAULT NULL,
      PRIMARY KEY (`Sno`,`Cno`), 
      FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`),
      FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看sc表的索引
这里很奇怪，为什么只有外键Cno有索引而Sno没有？研究一番发现复合索引的事。
复合索引有最左原则，例如创建一个(a,b,c)索引，就相当于创建了(a),(a,b),(a,b,c)三个索引，如果我们的where语句中有a = … and b = …时也能用上，但是b = … and c = … 就用不上索引。所以设计复合索引的时候最常用的列名应该放在较前面的位置，这里 a 应该是常用的查询属性。

这里我们的主键是两个列，组成一个复合索引，即(sno,cno)，相当于(sno),(sno,cno)，所以我们看到，外键自动创建的索引只有(cno),因为(sno)已经有过啦

当然你也可以强制加索引，但是不建议，因为索引使用不当有很多缺点。
CREATE TABLE sc (
Sno varchar(20) NOT NULL,
Cno varchar(10) NOT NULL,
Grade int(11) DEFAULT NULL,
PRIMARY KEY (Sno,Cno),
KEY Cno (Cno),
KEY Sno (Sno),
CONSTRAINT sc_ibfk_4 FOREIGN KEY (Sno) REFERENCES student (Sno),
CONSTRAINT sc_ibfk_5 FOREIGN KEY (Cno) REFERENCES course (Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;