Shulou(Shulou.com)06/01 Report--

Concept:

The primary key (primary key) uniquely identifies the attribute or attribute group of a row in the table. A table can have only one primary key, but can have multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. The primary key can ensure that the record is unique and the primary key field 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 used to establish and strengthen a link between two table data. Foreign key constraints are mainly used to maintain the consistency of data between two tables. In short, the foreign key of a table is the primary key of another table, and the foreign key connects the two tables. In general, to delete a primary key in a table, you must first ensure that there are no identical foreign keys in other tables (that is, the primary key in that table does not have a foreign key associated with it).

Index is used to quickly find records with specific values. Mainly for the convenience of retrieval, in order to speed up the speed of access, created according to certain rules, generally play a sorting role. The so-called unique index, this kind of index is basically the same as the previous "ordinary index", but with one difference: all values of the index column can only appear once, that is, they must be unique.

Summary:

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

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

Null values are not allowed for primary key columns, while null values are allowed for unique index columns.

A primary key can be referenced by other fields as a foreign key, while an index cannot be referenced as a foreign key.

Primary key:

The primary key is the only index of the data table, for example, there are student numbers and names in the student table, and the name may have a duplicate name, but the student number is unique. If you want to search for a record from the student table, such as looking for a person, you can only find the only one according to the student number. This is the primary key. For example: idint (10) not null primary key auto_increment; self-growing type.

Foreign key:

Define the data table

If a computer manufacturer keeps product information about the whole machine and accessories in its database. The table used to store the product information of the whole machine is called Pc;, and the table used to store the supply information of accessories is called Parts.

There is a field in the Pc table that describes the CPU model used on this computer

There is a corresponding field in the Parts table that describes the model of CPU, which we can think of as a list of all CPU models.

Obviously, the CPU used by the computer produced by this factory must be the model that exists in the supply information table (parts). At this point, there is a constraint relationship between the two tables (constraint)-the CPU model in the Pc table is constrained by the model in the Parts table.

First, let's create the parts table:

CREATE TABLE parts (

... Field definition.

Model VARCHAR (20) NOT NULL

... Field definition.

);

Next is the Pc table:

CREATE TABLE pc (

... Field definition.

Cpumodel VARCHAR (20) NOT NULL

... Field definition.

}

Set the index

To set a foreign key, both fields in the reference table (referencing table, that is, Pc table) and the referenced table (referencedtable, that is, parts table) must be indexed (index).

For the Parts table:

ALTER TABLE parts ADD INDEX idx_model (model)

This means to add an index to the parts table, which is based on the model field, and give the name to the idx_model.

The same is true for Pc tables:

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel)

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

Define foreign key

The following is to establish the kind of "constraint" described earlier between the two tables. Because the CPU model of pc must refer to the corresponding model in the parts table, we set the cpumodel field of the Pc table to "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 row says to set a foreign key for the Pc table, and the second row means to set the cpumodel field of this table to the foreign key; the third row says that the foreign key is constrained by the model field of the Parts table.

In this way, our foreign keys will be fine. If we try to CREATE a Pc, which uses a CPU model that does not exist in the Parts table, then MySQL will prevent the PC from being CREATE.

Cascade operation

Consider the following situation:

The technician found that the models of a series of cpu (there may be many) entered into the parts table a month ago all typed a wrong letter and now need to be corrected. What we hope is that when those Referenced Column in the parts table change, the Referencing Column in the corresponding table will also be automatically corrected.

When defining a foreign key, you can add a keyword like this at the end:

ON UPDATE CASCADE; means that when the main table is updated, the child table (s) produces a cascading update action, which some people like to call a "cascade" operation. :)

If you write this sentence 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 also operations such as RESTRICT (forbidding the change of the main table), SET NULL (the corresponding fields of the child table are set to empty) and so on.

Index:

Indexes are used to quickly find records with specific values, and all mysql indexes are saved in the form of a B-tree. If there is no index, MySQL must scan all records of the entire table from the first record when executing the query until a record that meets the requirements is found. The more records there are 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 find the location of the target record without scanning any records. If the table has 1000 records, finding records by index is at least 100 times faster than scanning records sequentially.

Suppose we create a table called people:

CREATE TABLE people (peopleid SMALLINT NOT NULL, name CHAR (50) NOT NULL)

Then we randomly insert 1000 different name values into the people table. The following figure shows a small portion of the data file where the peopletable resides:

As you can see, there is no clear order for the name columns in the data file. If we create an index on the name column, MySQL will sort the name column in the index:

For each item in the index, MySQL internally keeps a "pointer" to the location of the actual record in a data file. Therefore, if we want to find the peopleid of the record with name equal to "Mike" (the SQL command is "SELECTpeopleid FROM people WHEREname='Mike';"), MySQL can look for the "Mike" value in the index of name, and then go directly to the corresponding row in the data file, returning the peopleid of that row exactly. In this process, MySQL only needs to process one row to return the result. If there is no index for the "name" column, MySQL scans all records in the data file, that is, 1000 records! Obviously, the fewer records that need to be processed by MySQL, the faster it will complete the task. Type of index

MySQL provides several index types to choose from:

General index

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

Create an index, such as CREATE INDEX ON tablename (list of columns); modify a 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 index] (list of columns))

Uniqueness index

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

Create an index, such as CREATE UNIQUE INDEX ON tablename (list of columns); modify a table, such as ALTER TABLE tablename ADD UNIQUE [name of index] (list of columns); specify an index when creating a table, such as CREATE TABLE tablename ([...], UNIQUE [name of index] (list of columns))

Primary key

The primary key is a unique index, but it must be specified as "PRIMARYKEY". If you have ever used AUTO_INCREMENT-type columns, you may already be familiar with concepts such as primary keys. The primary key is generally specified when the table is created, such as "CREATETABLE tablename ([...], PRIMARY KEY (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);". Each table can have only one primary key.

Full-text index

MySQL supports full-text indexing and full-text retrieval since 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 column of type VARCHAR or TEXT. It can be created by the CREATETABLE command, or by the ALTER TABLE or CREATE INDEX command. For large datasets, creating a full-text index through the ALTERTABLE (or CREATEINDEX) command is faster than inserting records into an empty table with a full-text index. The following discussion in this article no longer deals with full-text indexing; for more information, see MySQLdocumentation.

Single-column index and multi-column index

An index can be a single-column index or a multi-column index. Let's use specific examples to illustrate the difference between the two indexes. Suppose you have a people table like this:

CREATE TABLE people (peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR (50) NOT NULL, lastname CHAR (50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid))

Here is the data we inserted into the people table:

In this data fragment, there are four people with the first name "Mikes" (two surnames Sullivans and two McConnells), two people aged 17, and one with a different name, JoeSmith.

The main purpose of this table is to return the corresponding peopleid based on the specified user's last name, first name, and age. For example, we might need to find the peopleid of a 17-year-old user named MikeSullivan (the SQL command is SELECT peopleid FROM people WHEREfirstname='Mike' AND lastname='Sullivan' ANDage=17;). Since we don't want MySQL to scan the entire table every time the query is executed, we need to consider using indexes here.

First, consider creating an index on a single column, such as a firstname, lastname, or age column. If we create an index on the firstname column (ALTERTABLE people ADD INDEX firstname (firstname);), MySQL will quickly limit its search to those firstname='Mike' records through this index, and then search for other conditions on this "intermediate result set": it first excludes those records whose lastname is not equal to "Sullivan" and then excludes those records whose age is not equal to 17. When the record meets all the search criteria, MySQL returns the final search results.

Due to the indexing of the firstname column, MySQL is much more efficient than performing a full scan of the table, but we still require MySQL to scan far more records than we actually need. Although we can delete the index on the firstname column and then create an index on the lastname or age column, overall, the search efficiency is similar no matter which column we create an index on.

In order to improve search efficiency, we need to consider the use of multi-column indexes. If you create a multi-column index for the three columns firstname, lastname, and age, MySQL can find the correct results with only one search! Here is the SQL command to create this multi-column index:

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)

Because the index file is saved in B-tree format, MySQL can immediately go to the appropriate firstname, then to the appropriate lastname, and finally to the appropriate age. Without scanning any of the records in the data file, MySQL correctly finds the target record for the search!

So, if you create a single-column index on the three columns firstname, lastname, and age, is it the same as creating a multi-column index of firstname, lastname, and age? The answer is no, the two are completely different. When we execute a query, MySQL can only use one index. If you have three single-column indexes, MySQL will try to choose the most restrictive index. However, even the most restrictive single-column index is certainly much less restrictive than the multi-column indexes on firstname, lastname, and age.

Leftmost prefix

Multi-column indexes have another advantage, which is reflected in a concept called the leftmost prefix (LeftmostPrefixing). Continuing with the previous example, we now have a multi-column index on the firstname, lastname, age column, which we call fname_lname_age. MySQL uses the fname_lname_age index when the search criteria are a combination of the following columns:

Firstname,lastname,age firstname,lastname firstname

On the other hand, it is equivalent to creating an index on the combination of (firstname,lastname,age), (firstname,lastname), and (firstname) columns. You can use this fname_lname_age index for all of the following queries:

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

During performance tuning, choosing which columns to create an index on is one of the most important steps. There are two main types of columns that you can consider using an index: columns that appear in the WHERE clause and columns that appear in the join clause. Take a look at the following query:

SELECT age # # do not use index FROM people WHERE firstname='Mike' # # consider using index AND lastname='Sullivan' # # consider using index

This query is slightly different from the previous query, but it is still 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 on the age column for this query. Here is a more complex example:

SELECT people.age, # # No index town.name # # No index FROM people LEFT JOIN town ON people.townid=town.townid # # consider using index WHERE firstname='Mike' # # consider using index AND lastname='Sullivan' # # consider using index

As in the previous example, because firstname and lastname appear in the WHERE clause, it is still necessary for these two columns to create an index. In addition, since the townid column of the town table appears in the join clause, we need to consider creating an index for that column.

Unique index:

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

If you can be sure that a data column will only contain values that differ from each other, you should define it as a unique index with the keyword UNIQUE when you create an index on that data column. The advantages of this: first, it simplifies the management of the index by MySQL, which makes the index more efficient; second, when a new record is inserted into the data table, MySQL will automatically check whether the value of this field of the new record has already appeared in this field of a record; if so, MySQL will refuse to insert the new record. In other words, the unique index can guarantee the uniqueness of the data record. In fact, in many cases, the purpose of creating unique indexes is not to improve access speed, but to avoid data duplication.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.