MySQL 最特殊的索引 primary key

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《MySQL 最特殊的索引 primary key》，文章讲解的知识点主要包括MySQL、数据库，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

我们习惯在建表的时候给这个表指定一个主键并设置这个主键自增长.这基本上是一种习惯,至少我有这个习惯.但是为什么我们会养成这个习惯,依据是什么,很长一段时间我是道听途说,并未严格查看MySQL官方的有关文档.

而我讲primary key是最特殊的索引是MySQL的文档中,经常会对

primary key

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.20, “CREATE TABLE Statement”. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file

大意是: 你经常会在创建表的时候顺手创建该表的全部索引,这条准则对于InnoDB表尤其重要,因为在InnoDB表中,主键决定了数据文件中行的物理布局.

CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes

一旦一个没有指定

primary key

primary key

primary key

alter

结合这2句话就是: 建表的时候primary决定了表中数据的物理结构,全部的数据在机器物理磁盘的存放规则和内存索引结构都被primary key所约束起来. 如果后面你要添加/更改某一表的primary key,由于这个表创建后就是有数据结构的,那么你就应该走alter表语句,重新组织这张表的结构.

这里就是下面面试点:

1. MySQL的primary key为什么特殊,特殊在哪里,请以MySQL内部的索引结构和磁盘存储结构角度分析
2. MySQL在建表的时候如果不指定primary key的话,那么这张表是不是就没有primary key了
3. 如果MySQL默认会给一个primary key的话,那么它会怎么给?

第一个问题前文已经讲了

第二个问题: MySQL是数据库,既然是数据库那么为了快速的检索数据,MySQL必须以一定的数据结构来组织数据,既然数据需要被合理的组织起来那么必须要有一些规则来决定数据怎么组织,这个规则就是primary key. 所以primary key必不可少.如果你不指定primary key,MySQL肯定会以某种方式去尝试自己建一个primary key. 如果你后面想起来建primary key了,那么请走alter语句,这个操作会重新组织数据结构.对于当前没有建primary key并且数据量非常大的表,重新组织数据结构,要考虑时间问题,可能时间会比较长.

第三个问题接着第二个,请看下面的正式分析.

所以我们抱着这3个问题出发,解读Primary Key Optimization

首先,primary key我们一般是建立在单列数据上的,实际上primary key可以建立在多列数据上

The primary key for a table represents the column or set of columns that you use in your most vital queries

其次, primary key 天生被设计为unique且not null的,同时数据结构依照primary key进行组织并预先进行了排序,所以使用primary key做索引是超快的.

It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

有些人喜欢在建立primary key的时候,画蛇添足的指导主键为unique,not null.其实是完全不懂主键.

经过上面的分析,如此重要以至于MySQL强调对于大数据量的关键性表,即使你没找到明显的可以做为primary key的列,也不妨建立一个auto increment的类型为整数的主键先顶一顶,这时primary key就是一个UUID. 当你使用外键关联这个表时,这些UUID可以作为指向其他表中相应行的指针。

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

上面这句话就是我从入行开始就被教育'建表时不忘了建一个自增主键,哪怕这个主键没有实际业务意义'的来源.

我们回到前面留下的第三个问题,如果建表的时候没有指定主键会怎么样?

If a table contains no primary key, InnoDB automatically promotes the first UNIQUE NOT NULL index to the primary key. This is not supported for UNIQUE NOT NULL indexes that have functional key parts.

UNIQUE is supported for indexes that include functional key parts. However, primary keys cannot include functional key parts. A primary key requires the generated column to be stored, but functional key parts are implemented as virtual generated columns, not stored generated columns.

答案是如果没有指定主键,MySQL会这样做:

• 首先尝试在当前表中找到一个非空的唯一的索引,隐式的作为primary

  • 对这个非空唯一索引,需要它的索引值不包括Functional Key Parts,否则无法充当primary key.
• 如果没有找到满足条件的非空唯一索引,那么它会自己隐式的创建一个自增索引.依据是Clustered and Secondary Indexes. 这个索引的名字有点特殊叫做

  GEN_CLUST_INDEX

  .

本文原创链接:

• MySQL 最特殊的索引 primary key

本文参考:

• Why Tables need a Primary Key in MariaDB and MySQL
• Can a database table be without a primary key?

以上就是《MySQL 最特殊的索引 primary key》的详细内容，更多关于mysql的资料请关注golang学习网公众号！