Several concepts of MySQL: Primary key, foreign key, index, unique index

Concept:

A primary key (primary key) uniquely identifies a property or group of properties for a row in a table. A table can have only one primary key, but there may be multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys, preventing data inconsistencies. The primary key guarantees that the record is unique and the primary key domain is not empty, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index .

A foreign key (foreign key) is one or more columns that are used to establish and strengthen links between two table data. FOREIGN key constraints are primarily used to maintain data consistency between two tables. In short, the foreign key of the table is the primary key of the other table, and the foreign key ties the two tables together. In general, to delete a primary key in a table you must first make sure that no other table has the same foreign key (that is, the primary key in the table does not have a foreign key associated with it).

index is used to quickly look for records that have a specific value. Mainly for the convenience of retrieval, is to speed up access, according to certain rules created, generally play the role of sorting. The so-called Uniqueness Index , which is basically the same as the previous "normal index", has one difference: all the values of an indexed column can only occur once, that is, they must be unique.

Summarize:

The primary key must be a unique index, and the uniqueness index is not necessarily the primary key.

There can be multiple unique indexes in a table, but only one primary key.

The primary key column does not allow null values, and the uniqueness index column allows null values.

The primary key can be referenced by another field as a foreign key, and the index cannot be referenced as a foreign key.

Primary key:

The primary key is the unique index of the data table, for example, students have a school number and name, names may have the same name, but the study number is unique, you want to search a record from the student table such as find a person, you can only according to the study number to find, this can find the only one, this is the primary key; Idint Primary key auto_increment; self-growing type;

FOREIGN key:

Defining Data Tables

If a computer manufacturer, its database is stored in the machine and accessories product information. The table used to store the product information is called a PC, and the table used to store the parts supply information is called parts.

There is a field in the PC table that describes the CPU model used by the computer;

There is a field in the parts table that describes exactly the CPU model, and we can think of it as a list of all CPU models.

Obviously, the manufacturer of the computer, its use of the CPU must be the supply information table (parts) exists in the model. In this case, there is a constraint relationship in two tables (constraint) The CPU models in the--PC table are constrained by the models in the Parts table.

Let's start by creating the parts table:

CREATE TABLE parts (
... 字段定义 ...,
model VARCHAR(20) NOT NULL,
... 字段定义 ...
);

Next is the PC table:

CREATE TABLE pc (
... 字段定义 ...,
cpumodel VARCHAR(20) NOT NULL,
... 字段定义 ...
};

Set Index

To set a foreign key, the corresponding two fields must be indexed (index) in the reference table (referencing table, which is the PC table) and the referenced table (referencedtable, the Parts table).

On the Parts table:

ALTER TABLE Parts ADD INDEX Idx_model (model);

To add an index to the Parts table, the index is built on the model field, and the call is named Idx_model.

Also similar to PC tables:

ALTER TABLE pc ADD INDEX Idx_cpumodel (Cpumodel);

In fact, these two indexes can be set when the table is created. Here just to highlight its necessity.

Defining foreign keys

The following two tables establish the "constraints" described earlier. Because the CPU model of the PC must refer to the corresponding model in the Parts table, we set the Cpumodel field of the PC table to "foreign key" (ForeignKey), that is, the reference value of this key comes from other tables.

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model);

The first line is to set the foreign key for the PC table, give the foreign key a name called Fk_cpu_model; the second line is to set the Cpumodel field of the table as a foreign key; the third line is that the foreign key is constrained by the model field of the Parts table.

In this way, our foreign key is OK. If we try to create a PC, it uses a CPU model that does not exist in the parts table, then MySQL prevents the PC from being created.

Cascade Operations

Consider the following scenario:

The technician found that the one-month-old number of CPUs (many of which may have lots of models) entered into the parts table had a wrong letter and now needs to be corrected. We hope that when those referenced column in the Parts table changes, the referencing column in the corresponding table can be corrected automatically.

You can add such a keyword at the end of a foreign key definition:

On update CASCADE, that is, when the main table is updated, the sub-tables (we) generate a chain update action, and it seems that some people like to call this "cascade" operation. :)

If this statement is written out in its entirety, it is:

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model)
ON UPDATE CASCADE;

In addition to CASCADE, there are actions such as RESTRICT (prohibit primary table changes), set NULL (the corresponding field of the child table is set to null), and so on

Index:

Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.

Let's say we've created a table named people:

CREATE TABLE people (Peopleid SMALLINT not NULL, name CHAR (a) not null);

Then, we completely randomly insert 1000 different name values into the People table. Shows a small portion of the data file where the people table resides:
As you can see, the Name column in the data file does not have any definite order. If we create the index of the name column, MySQL will sort the name column in the index:

For each item in the index, MySQL internally holds a pointer to the location of the actual record in the data file. So if we want to find the name equals "Mike" Record of Peopleid (the SQL command is "Selectpeopleid from people wherename= ' Mike ';" ), MySQL can find the "Mike" value in the index of name, then go directly to the corresponding line in the data file and return exactly the line's Peopleid (999). In this process, MySQL only has to process one row to return the results. If there is no index to the "name" column, MySQL will scan all records in the data file, that is, 1000 records! Obviously, the fewer records that require MySQL to process, the faster it can complete the task. type of index

MySQL offers a variety of index types to choose from:

Normal index

This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:

Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns); Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns); Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));

Uniqueness Index

This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:

Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns); Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns); Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns));

Primary key

A primary key is a unique index, but it must be specified as "PRIMARYKEY". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when the table is created, such as "CreateTable tablename ([...], PRIMARYKEY (List of columns)". However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

Full-Text Indexing

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created with the CreateTable command or by the ALTER TABLE or the CREATE INDEX command. For large datasets, it is faster to create full-text indexes with altertable (or createindex) commands than to insert records into empty tables with full-text indexes. The following discussion in this article no longer involves full-text indexing, see mysqldocumentation for more information.

single-column and multicolumn indexes

The index can be a single-column index or a multicolumn index. Let's take a concrete example to illustrate the differences between the two indexes. Suppose there is such a people table:

CREATE TABLE people (Peopleid SMALLINT NOT NULL auto_increment, FirstName char (a) NOT NULL, LastName char (.) NOT NULL, Age SMALLINT isn't null, Townid SMALLINT NOT NULL, PRIMARY KEY (Peopleid));

Here is the data we inserted into this people table:



The data fragment contains four people named "Mikes" (two of them Sullivans, two surname McConnells), two are 17 years of age, and a joesmith with a distinctive name.

The primary purpose of this table is to return the corresponding Peopleid based on the specified user name, first name, and age. For example, we may need to find the Peopleid (SQL command for select Peopleid from people wherefirstname= ' Mike ' and lastname= ' of a Mikesullivan, age 17-year-old user Sullivan ' andage=17;). Since we don't want MySQL to scan the entire table every time it executes a query, we need to consider using an index.

First, we can consider creating an index on a single column, such as FirstName, LastName, or the Age column. If we create an index of the FirstName column (altertable people add index FirstName (firstname), MySQL will quickly limit the search to those firstname= ' Mike ' records through this index , and then search for other criteria on this "intermediate result set": It first excludes records whose lastname are not equal to "Sullivan", and then excludes records whose age is not equal to 17. When the record satisfies all search criteria, MySQL returns the final search results.

Because of the FirstName column index, MySQL is much more efficient than performing a full scan of the table, but we require that the number of logs scanned by MySQL still far exceeds what is actually needed. Although we can delete the index on the FirstName column, and then create an index of the LastName or age column, it seems that the efficiency of creating an index search is still similar, regardless of which column.

To improve search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for the three columns of FirstName, LastName, and age, MySQL can find the correct results with a single search! Here is the SQL command to create this multi-column index:

ALTER TABLE People ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

So, if you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age? The answer is no, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.

Leftmost prefix

A multi-column index has another advantage, by calling Leftmost prefix(leftmostprefixing) concept is reflected. Continuing to consider the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

Firstname,lastname,age Firstname,lastname FirstName

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan ' and age= ' 17 '; SELECT Peopleid from people WHERE firstname= ' Mike ' and lastname= ' Sullivan '; SELECT Peopleid from people WHERE firstname= ' Mike '; The following queries cannot use the index at All:select Peopleid from people WHERE lastname= ' Sullivan '; SELECT Peopleid from people WHERE age= ' 17 '; SELECT Peopleid from people WHERE lastname= ' Sullivan ' and age= ' 17 ';

Select Index Column

In the performance optimization process, choosing which columns to create indexes on is one of the most important steps. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause. Consider the following query:

SELECT Age # # does not use the index from people WHERE firstname= ' Mike ' # # Consider using the index and lastname= ' Sullivan ' # # consider using the index

This query is slightly different from the previous query, but it still belongs to a simple query. Because age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, it is not necessary to create an index of the age column for this query. The following is a more complex example:

SELECT people.age, # #不使用索引 Town.name # #不使用索引 from people the left JOIN town on People.townid=town.townid # #考虑使用索引 WHERE firstn Ame= ' Mike ' # #考虑使用索引 and Lastname= ' Sullivan ' # #考虑使用索引

As in the previous example, because FirstName and LastName appear in the WHERE clause, these two columns still have the necessary to create an index. In addition, because the Townid of the town table is listed in the JOIN clause now, we need to consider creating the index of the column.

Unique index:

A pass index allows the indexed data column to contain duplicate values. For example, because a person might have the same name, it may occur two or more times in the same employee profile data table.

If you can determine that a data column will contain only values that are different from each other, you should define it as a unique index with the keyword unique when creating an index for that data column. The advantage of this is that it simplifies MySQL's management of the index, which makes it more efficient, and that MySQL automatically checks to see if the value of this field in the new record has already appeared in the field of a record when a new record is inserted into the data table; MySQL will refuse to insert that new record. In other words, a unique index guarantees the uniqueness of the data record. In fact, in many cases, the goal of creating a unique index is often not to improve access speed, but to avoid duplication of data

Several concepts of MySQL: Primary key, foreign key, index, unique index