浅谈mysql的主键和索引

索引的类型

关于索引的类型说法有很多，如聚簇索引、非聚簇索引、主键索引、辅助索引、二级索引、次级索引、唯一索引、单列索引、复合索引等等。我们先引用mysql官方的一段话来解释：

那么，我们思考两个问题：

1、主键一定是聚簇索引，聚簇索引是否一定是主键？

2、聚簇索引不是主键，那二级索引叶子节点存放的主键值是什么？

带着这两个问题，我们来做个实验，先建个测试表：

create table test(

id int not null,

c1 varchar(10) not null,

c2 varchar(10) null);

查看索引情况：

select i.* from information_schema.INNODB_SYS_INDEXES i join information_schema.INNODB_SYS_TABLES t on i.table_id=t.table_id where t.name='ecos/test';

可以看到，未定义主键的情况下，mysql自动创建了一个隐藏的、用户不可见的主键并作为聚簇索引，符合聚簇索引建立的第3条原则。（在INNODB_SYS_INDEXES系统表中type代表索引的类型，0：一般的索引，1:(GEN_CLUST_INDEX)系统生成的隐藏主键索引mysql索引表，2：唯一索引，3：主键索引）

再创建一个唯一索引：

create unique index unx_c1 on test(c1);

再查看索引情况：

可以看到创建的唯一索引自动转变成了主键索引，那么c1是否也转成了主键呢，我们再看表的情况：

c1并没有变成primary key，改变的只是索引的类型，这时候从用户视角主键实际上是不存在的。

我们再给表添加主键：

alter table test add PRIMARY key(id);

再看索引和表的主键情况：

可以看到原来的主键索引已经变成了唯一索引，新加的主键ID成了主键索引。

总结：

1、主键一定是聚簇索引，聚簇索引不一定是主键，但一定是主键索引（我们暂且把系统自动生成的也当主键索引）。

2、二次索引叶子节点存放的主键值准确的说是主键索引值。

3、在mysql的官方文档里面，一直都是以primary key出现的，并且也不支持以create index方式创建，primary key既代表了主键也代表了主键索引，这也造成了我们在理解上的困惑，所以在了解mysql索引结构的时候建议还是把primary key理解为主键索引。

4、在建表的时候就应该定义好主键，并且不再修改，特别是生产环境，主键的修改会影响到所有索引的重建。

5、官方建议主键用自增ID，但在实际业务场景中，应该结合业务实际需要来定义主键，更好的利用主键索引的优势，但要避免用uuid等无序的值。如在订单数据表，订单号本身就按递增规则生成，且具有唯一性，那么用订单号做主键更符合业务需要。

最后回到最开始的问题，count(*)之所以在只有主键的情况下查询效率无法提升，正是由于mysql InnoDB索引结构导致的，count在where条件没有的情况下是要走全表扫描，而主键索引是聚簇索引，包含了整个表的数据，占用空间和分页更多，查询优化器在有二级索引的情况下会优先查找二级索引，二级索引的B+Tree更小，查找效率会更高，覆盖索引优化同样是利用的二级索引的这种优势。

思考：id为主键，下面两个查询返回的结果是一样的吗？

1、select a.* from tbl_order a limit 1;

2、selecta.* from tbl_order a where id=(select b.id from tbl_order b limit 1);

（编辑：南平站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!