Home > Article > Database > Detailed introduction to key and index in MySQL

Detailed introduction to key and index in MySQL

PHP中文网Original: 2017-06-20 14:10:3410639browse

1. Overview

1. Basic concepts

(1) key is the physical structure of the database. It has two layers of functions. One layer is constraint, which is used to constrain data. The uniqueness and completeness; the first layer is the index function, which is used to build indexes and optimize query speed, and has the same function as index.

(2) Ordinary key: There is no constraint effect, but an index will be created on this key.

(3) Primary key: Primary key; a table can have a primary key, which is divided into a single primary key (containing only one column) and a composite primary key (also called a joint primary key, which can contain multiple columns); a storage can be specified Primary key, and standardize the uniqueness of data; at the same time, an index will be established on this key. The primary key is not necessary, but it is strongly recommended [A few good habits for using primary keys: no changes, no reuse]

(4) unique key: unique key; standardizes the uniqueness of data; at the same time, this key will be used Create an index on.

(5) foreign key: foreign key; standardizes the referential integrity of the data; at the same time, an index will be established on this key.

(6) index: a dimension of key, which can sometimes replace the keyword key.

2. Primary key and unique key

(1) Similarity: uniqueness constraint

(2) Difference

1) The starting point/function is different: the former is the unique identifier of a row of data, while the latter is only used to avoid data duplication.
2) One or more columns of the former must all be not null; if one of the columns is null, it will become not null when it is added as the primary key. If the primary key is deleted, the nullable nature of the column will change back. . The latter column can be null.
3) A table can only have one primary key and can have multiple unique keys. [Can a table have no primary key? ? ? 】
4) For the column corresponding to the unique key, null can be inserted multiple times (although it is also a kind of repetition); this is determined by the principle of the index, that is, the index's processing of null.

2. Syntax

1. Add when creating -field level

(1) Ordinary key: create table t (id int not null key);

(2) Primary key: create table t (id int not null primary key); The two have the same function, that is, specifying the key also specifies the primary key , and can only be specified once in a table (it cannot be specified multiple times as a joint primary key)

(3) unique key: create table t (id int not null unique key);

(4) foreign key: It should not work

(5) index: All keys cannot be transposed index

2. Add - table level when creating

(1) Ordinary key: Different from field-level specification, the ordinary key here is no longer the same as the primary key. Even if the primary key is not specified, MySQL will not use the key as the primary key.

create table t(id int, key (id)); If there are other keys using id (such as foreign key), use other keys to name it; If none are named, the id is used; if multiple columns are specified as keys at one time, the first column name is used as the key name.

create table t(id int, key kismet(id));Specify the name of the key

constraint: cannot be used, after all, it is ordinary The key has no binding effect

(2) primary key

create table t(id int, primary key (id));

create table t(id int, primary key kismet(id)); can be executed, but The name does not work

##create table t(id int, constraint kismet primary key(id)); can be executed, but the name does not work

（3）unique key

create table t(id int, unique key (id)); The naming rules are different from key. Only the first column is used as the key name

##create table t(id int, unique key kismet( id));Specify the name of the key

##create table t(id int, constraint kismet unique key(id));Specify the name of the key

(4) foreign key [Personally, I think that the so-called creation of two keys is a logical two levels, that is, data integrity constraints and index optimization】

create table t(id int, foreign key (dage_id) references dage(id)); can be executed, and the execution result is that a An automatically named foreign key and an automatically named ordinary key.
create table t(id int, foreign key kismet(dage_id) references dage(id)); can be executed, and the execution result is that an automatically named foreign key and a normal key named kismet.
create table t(id int, constraint kismet foreign key(dage_id) references dage(id)); can be executed, and the execution result is to create a table named The foreign key of kismet and a normal key named kismet.

(5) index: The key in key and unique key (table level) can be transposed to index, and the effect is the same.

3. After creation

(1) Add key: add, for example: alter table t add primary key(id);

(2) Delete key, drop, primary key Use alter table t drop primary key; other keys can be dropped using their names. Pay attention to the difference between deleting keys and deleting columns.

4. View information: show create table table_name; you can view various attributes of the table, including key attributes, storage engine, character set, partition status, etc.

3. Foreign key

1. Function: It can associate two tables to ensure data consistency And implement some cascade operations;

2. Storage engine that supports foreign keys: InnoDB, Memory verification support, others have not been verified.

3. Complete syntax

(1) [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...)

## (2) Usage: This syntax can be used when creating table and alter table

(3) CONSTRAINT symbol specifies the name of the key. If not specified, it will automatically generate

( 4) on delete and on update represent event trigger settings. Parameters can be set:

RESTRICT (restrict foreign key changes in the table, default)

CASCADE (Follow foreign key changes)

SET NULL (Set null value)

SET DEFAULT

NO ACTION

4. Example

(1) Create a table, set foreign keys, and insert data

CREATE TABLE

`dage` (<span class="pln"><span class="str"><span class="pln"><span class="pun"></span></span></span></span> 
`id` int<span class="pln">(<span class="str">11<span class="pln">)<span class="kwd"> NOT NULL auto_increment<span class="pun">,<span class="lit"><span class="pun"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span></span>##`name`
 varchar(32<span class="pln">)<span class="str"> <span class="pln">default<span class="pun"> <span class="lit">''<span class="pun">,<span class="pln"><span class="kwd"><span class="pln"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span>## PRIMARY KEY </span>(`id`
)<span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span>);</span>

<span class="pun">CREATE TABLE </span>`xiaodi` 
(
<span class="pln"><span class="str"><span class="pln"><span class="pun"></span></span></span> </span>`id` 
int(11<span class="pln">)<span class="str"> NOT NULL auto_increment<span class="pln">,<span class="kwd"><span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span># `dage_id`</span> int
(11)<span class="pln"> <span class="str">default<span class="pln"> NULL<span class="kwd">,<span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span></span>##`name`</span> varchar</span>(32
) default<span class="pln"> <span class="str">''<span class="pln">,<span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="str"><span class="pun"> </span></span></span></span></span></span></span>## PRIMARY KEY </span>(</span>`id`</span>),</span>

<span class="pln"><span class="pun"><span class="str"><span class="pun"> KEY </span>`dage_id`</span> </span>(</span>`dage_id`),
<span class="pln"><span class="str"><span class="pln"><span class="pun"><span class="str"><div class="L1"><code class="language-sql"><span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span></span></span></span></span></span></span></span></span>
##);<span class="pun"></span>
insert <span class="pln">into<span class="kwd"> dage<span class="pln">(<span class="pun">name <span class="pln">)<span class="pun"> values<span class="pln">(<span class="pun">'Causeway Bay'<span class="str">);<span class="pun"></span></span></span></span></span></span></span></span></span></span>
##insert into<span class="pln"> xiaodi<span class="kwd">(<span class="pln">dage_id<span class="pun">,<span class="pln">name<span class="pun">)<span class="pln"> values<span class="pun">(<span class="pln">1<span class="pun">,<span class="lit">'Causeway Bay_Little Brother A'<span class="pun">);<span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

(2) If you delete the eldest brother while there is still a younger brother, the result is as follows

##[SQL]<span class="pun"> <span class="pln">delete<span class="pun"> <span class="pln">from<span class="kwd"> dage <span class="pln">where<span class="kwd"> id<span class="pln">=<span class="kwd">1<span class="pln">;<span class="pun"><span class="lit"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span>#[
Err] <span class="pun">1451<span class="typ"> <span class="pun">-<span class="pln"> <span class="lit">Cannot<span class="pln"> <span class="pun">delete<span class="pln"> <span class="typ">or<span class="pln"> update a parent row<span class="kwd">:<span class="pln"> a foreign key constraint fails <span class="kwd">(<span class="pln">`sample`<span class="pun">.<span class="pln">`xiaodi`<span class="pun">,<span class="str"> CONSTRAINT <span class="pun">`xiaodi_ibfk_1`<span class="str"> FOREIGN KEY <span class="pun">(<span class="pln">`dage_id`<span class="str">)<span class="pln"> REFERENCES <span class="pun">`dage `<span class="str"> <span class="pun">(<span class="pln">`id`<span class="str">))<span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span> </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> </span></span></span></span></span></span></span></span></span></span>## (3) If you want to forcibly insert the younger brother without establishing the older brother, The results are as follows</span>

##[

SQL

]

insert

into xiaodi (<span class="pun">dage_id<span class="pln">,<span class="pun">name<span class="pln">)<span class="kwd"> values<span class="pln">(<span class="pun">2<span class="pln">,<span class="pun">'Mong Kok_Little Brother A'<span class="pln">);<span class="pun"> <span class="pln"><span class="pun"><span class="lit"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span> </span></span></span></span>#[</span>Err</span>]</span> 1452
- <span class="pun">Cannot<span class="typ"> add <span class="pun"> or<span class="pln"> update a child row<span class="lit">:<span class="pln"> a foreign key constraint fails <span class="pun">(<span class="pln">`sample`<span class="typ">.<span class="pln">`xiaodi`<span class="kwd">,<span class="pln"> CONSTRAINT <span class="pun">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="str"> REFERENCES <span class="pun">`dage`<span class="pln"> <span class="str">(<span class="pln">`id`<span class="pun">) )<span class="str"><span class="pun"><span class="pln"><span class="str"><span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>##(4) Modify event trigger settings</span></span></span></span></span>
##show create table xiaodi

;#View Key name

<span class="pln">alter table xiaodi drop foreign key xiaodi_ibfk_1<span class="pun">;</span></span>

##alter table xiaodi add foreign key<span class="pln"> (<span class="pun">dage_id<span class="pln">)<span class="pun"> references dage<span class="pln">(<span class="pun">id<span class="pln">)<span class="pun"> on <span class="pln">delete<span class="kwd"> cascade on update cascade<span class="pln">;<span class="pun"></span></span></span></span></span></span></span></span></span></span></span></span>

# (5) If Deleting the eldest brother when there is still a younger brother: the eldest brother and the younger brother corresponding to the older brother are deleted together; if you want to forcibly insert the younger brother without creating the older brother, the result will not change, that is, it will fail.

4. Index [Reference:]

1. Introduction to index

(1) Role: Index has a crucial impact on the speed of query. If there is no index, the query will scan the entire table; if there is an index, the query will only scan the index. Since the data of the database is not in the memory, each query needs to transfer the data from the hard disk to the memory, and IO will waste a lot of time. Considering that indexes are much smaller than data, using indexes can greatly improve query speed; especially when the amount of data is large.

(2) The index is implemented in the storage engine, not in the server layer. Therefore, the indexes of each storage engine are not necessarily exactly the same, and not all storage engines support all index types. Currently the most commonly used storage engine is InnoDB.

2. Select the data type of the index: MySQL supports many data types. Choosing the appropriate data type to store data has a great impact on performance. Generally speaking, you can follow some guidelines [(1)(2) does not apply to hash indexes]:

(1) Smaller data types are usually better: Smaller data types are usually better Requires less space on disk, memory and CPU cache, and processes faster.

(2) Simple data types are better: Integer data has less processing overhead than characters, because the comparison of strings is more complex. In MySQL, you should use the built-in date and time data types instead of strings to store time; and use integer data types to store IP addresses.

Note that for indexes, if you can use integers, do not use strings, especially when the amount of data is large; one disadvantage of integers is that cooperation with the client may require some extra work (especially large integers) type), but has little impact on efficiency.

(3) Try to avoid NULL: the column should be specified as NOT NULL, unless you want to store NULL. In MySQL, columns containing null values are difficult to optimize queries because they complicate indexes, index statistics, and comparison operations. You should replace null values with 0, a special value, or an empty string.

3. B-tree index: The result is B-tree (balanced binary tree)

(1) Overview: The value stored in the index is The order in the index column. You can use the B-Tree index to perform full keyword, keyword range and keyword prefix queries.

If multiple columns are indexed (combined index), the order of the columns is very important

, MySQL can only perform an effective search on the leftmost prefix of the index. (2) Example: Its index contains the last_name, first_name and dob columns of each row in the table.

##CREATE TABLE People <span class="pln">(<span class="typ"><span class="pln"><span class="pun"></span></span></span></span>
 last_name varchar(50<span class="pln">)<span class="pun"> <span class="lit">not<span class="pun"> <span class="pln">null<span class="kwd">,<span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span></span># first_name varchar
(50)<span class="pln"> <span class="pun">not<span class="lit"> <span class="pun">null<span class="pln">,<span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span></span>## dob date not
null,<span class="pln"><span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span>## gender </span>enum(
'm', <span class="pln">'f'<span class="kwd">)<span class="pun"> <span class="str">not<span class="pun"> <span class="pln">null<span class="str">,<span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="kwd"><span class="pun"></span></span></span></span></span></span></span></span># #<div class="L0"><code class="language-sql"><span class="pln"> key<span class="pun">(<span class="pln">last_name<span class="pun">,<span class="pln"> first_name<span class="pun">,<span class="pln"> dob<span class="pun">)</span></span></span></span> </span></span></span></span>
##);<span class="pun"></span>

(3) Matching method: You can either search or order by [The results are sorted, so the search is fast]

##1) Match all Values: Specify specific values for all columns in the index.

#2) Match the leftmost prefix: You can use the index to find the person whose last name is Allen, just use the first column in the index.

#3) Match column prefix: For example, you can use the index to find people whose last name starts with J. This only uses column 1 in the index.

#4) Range query of matching values: You can use the index to find people whose last name is between Allen and Barrymore, using only the first column in the index.

#5) The matching part is accurate and the other parts are range matched: you can use the index to find people whose last name is Allen and whose first name starts with the letter K.

#6) Query only the index: If the queried columns are all located in the index, there is no need to read the value of the tuple.

7) If the index field is A+B, will the A index be used when querying A+C -> Yes, it can be confirmed by using explain
(4) Limitations

1) The query must start from the leftmost column of the index.

#2) You cannot skip an index column. For example, you cannot use an index to find a person whose last name was Smith and who was born on a certain day.

##3) The storage engine cannot use the columns to the right of the range condition in the index. For example, if your query statement is WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23', the query will only use the first two columns in the index because LIKE is a range query .

4. Hash Index

(1) Overview

1) Hash index calculates the Hash value through the hash function for retrieval, and can find the row pointer of the data to be searched, thereby locating the data.

#2) The Hash value does not depend on the data type of the column. The index of a TINYINT column is as large as the index of a long string column.

3) The Memory storage engine supports non-unique hash indexes. If multiple values have the same hash code, the index saves their row pointers in a linked list to the same location. in a hash table entry.

(2) Limitations

1) Since the index only contains hash code and record pointer, MySQL cannot Avoid reading records by using indexes. But accessing records in memory is very fast and will not have much impact on performance.

#2) Hash index sorting cannot be used.

3) Hash index does not support partial matching of keys, because the hash value is calculated through the entire index value.

4) Hash index only supports equality comparison, such as using =, IN() and <=>. For WHERE price>100, it does not speed up the query.

(3) Example

##CREATE TABLE testhash ( <span class="pln"><span class="pun"></span></span>## fname VARCHAR
(50) NOT NULL, <span class="pln"><span class="pun"></span></span>## lname VARCHAR(
50) NOT NULL, <span class="pln"><span class="pun"></span></span>##KEY USING HASH

(fname)<span class="pln"><span class="pun"></span></span>##) ENGINE=MEMORY
;<span class="pln"><span class="pun"></span></span>#5. Other indexes 

(1) Spatial (R-Tree) index: MyISAM supports spatial indexes, mainly used for geospatial data types, such as GEOMETRY.

(2) Full-text index: Full-text index is a special index type of MyISAM, mainly used for full-text retrieval.

The above is the detailed content of Detailed introduction to key and index in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Detailed introduction to key and index in MySQL

Related articles