MySQL is a widely used database, and InnoDB is the most widely used storage engine. Primary key is one of the most important factors affecting InnoDB performance. Selecting the right primary key can greatly improve performance.
InnoDB has some best practices for selecting columns as primary keys:
(1) columns that cannot be empty;
(2) columns that cannot be repeated;
(3) columns that rarely change;
_ Voiceover: _ rows are physically sorted by clustered index, and if the primary key changes frequently, the physical order changes and performance deteriorates dramatically.
(4) the column where key=XXX is frequently retrieved
If the index is a clustered index, it can avoid back to the table and almost double the performance.
(5) Not too long a column
_ Voiceover: _ Normal index leaf nodes store primary key values. If the primary key values are too long, the normal index size is increased.
Referring to the best practices above, businesses often use columns like this as primary keys:
-
User ID: UID
-
Message ID: msgid
-
Order ID: OID
-
…
These columns, which meet the best practices of non-null, unique, immutable, frequently queried, and long integers, have high performance.
_ Voiceover: _ It is recommended that the service side use Snowflake to generate these ids, which are globally unique and trend increasing.
If there is no such service attribute, you can also use the auto_INC_id as the primary key. The auto_INC_id can meet the best practices such as non-empty, unique, invariant, and long integer with high performance.
Voice-over: Autoincrement ID should not be exposed to upstream, otherwise there will be a pit when branch expansion.
But, above all is the theory, falls to the practical level, we really grasp so thoroughly? Here are five exercises to see how well you know InnoDB primary keys.
Exercise 1: Can I create a table without declaring a primary key?
(1) create table user(
name varchar(10)
)engine=innodb;
(2) insert into user values_(‘shenjian’)__; _
(3) insert into user values_(‘shenjian’)__; _
Voiceover: When creating a table, insert two identical elements without declaring a primary key.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D no error is reported
Exercise 2: When creating a table, can not declare primary key is not empty?
(1) create table user(
id int,
name varchar(10),
primary key(id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user(name) values(‘shenjian’);
Voiceover: When creating a table, insert two identical elements without declaring non-null.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D no error is reported
Exercise 3: when creating a table, can select more than one field primary key?
(1) create table user(
id int not null,
name varchar(10) not null,
primary key(id, name)
)engine=innodb;
(2) insert into user values(1, ‘shenjian‘);
(3) insert into user values(1, ‘zhangsan’);
(4) insert into user values(2, ‘shenjian‘);
Voiceover: When creating a table, declare the joint primary key (a,b), insert several elements, some a duplicate, some B duplicate.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
E does not report errors
Exercise 4: Can I actively insert auto-increment primary keys?
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user_(id, name)_ values_(10,’shenjian’)__; _
(4) insert into user(name) values(‘shenjian’);
Voiceover: When creating a table, increment the ID primary key, insert several elements, some contain the increment ID, some do not.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
E does not report errors
Exercise 5: Can you add a primary key to a table?
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(name, id)
)engine=innodb;
(2) insert into user(name) values(‘shenjian’);
(3) insert into user_(id, name)_ values_(10,’shenjian’); _
(4) insert into user(name) values(‘shenjian’);
Voiceover: when creating a table, declare the joint primary key (a,b), and one of them is the increment ID, insert several elements, including the increment ID, some do not contain.
Question, execute the above statement consecutively, execute the result:
(1)
B Insert statement (2) error
C Insert statement (3) error
D Insert statement (3) error
E does not report errors
What’s your answer? Do you really know it?