V. MySQL indexes and keys

MySQL indexes and keys
(Different indexes have different functions, different constraints, different rules of use)
Pros: For a table, an index is like a book's directory that can speed up queries
Cons: Occupy physical storage space (index information stored in the file corresponding to the table)
Reduces the speed of inserting and updating table records (insert delete update)
1. Types of indexes
Normal Index: Index
Unique index: Unique
Full-text index: fulltext

2. Description of each index
(1). Index Normal indexes
You can have more than one index field in a table
Set the field that frequently makes query criteria to the index field
The key flag for the index field is Mul
The corresponding field values are allowed to have duplicates
Example 1:
Sets the index field when the table is built. (The default index word is the same as the field name)
CREATE TABLE Tea (
Name Char (TEN) is not NULL,
Age Int (3) is not NULL,
Index (name)
);
Example 2:
Set the field in the existing table as the Index field.
Create index index name on table name (field name);
Create index name on stu_info (name);
Create index name on Stu_info (Name,sex);
Delete the index field for the specified table
Drop index index name on table name;
Drop index name on Stu_info;
Note: Index uses the btree algorithm (binary tree algorithm)
View index information for a table: Show index from Stu_info;

(2) Unique unique index
Field values are not allowed to be duplicated, the value of the unique field is allowed to be null, "this field is limited to the same as the primary key when it is modified to not allow NULL"
There can be more than one unique field in a table
The key flag for the unique field is uni
Examples:
CREATE TABLE Tea23 (
ID int (3),
Name Char (3),
Unique (ID)
);

Create unique index stu_id on stu_info (stu_id);
3. Key
Primary key: Primary key
FOREIGN key: FOREIGN key
(1). Primary key
There can be only one primary field in a table
Field values are not allowed to be duplicated and are not allowed to be null
The key flag of the primary key field is the PRI
If more than one field is primary key, called a composite primary key, it must be created together with the table
Usually used with auto_increment (the value of the field automatically grows +)
Set a field that can uniquely locate a record as the primary key field
Examples:
CREATE TABLE Baitao (LF int (3), primary key (LF));
ALTER TABLE Stu_info add primary key (ID)
ALTER TABLE name drop PRIMARY key;

(2). FOREIGN key * FOREIGN key
field types are consistent
The storage engine for the table must be InnoDB
The referenced field must be one of the index types.

FOREIGN KEY (field name) References Table B (field name) on UPDATE CASCADE
On DELETE CASCADE

Examples:
CREATE TABLE Yg_info (yg_id int (3) primary key auto_increment,name varchar () not Null,index (name)) Engine=innodb;
CREATE TABLE GZ_TAB2 (
ID int (3) primary key auto_increment,
Name varchar (NOT NULL),
gz_id Int (3) is not NULL,
GZ Float (7,2),
FOREIGN KEY (gz_id) References yg_info (yg_id)
On UPDATE cascade on DELETE CASCADE
) Engine=innodb;
Insert into Yg_info (name) VALUES ("Jim");
Insert into Yg_info (name) VALUES ("Tom");
Insert into Gz_tab (NAME,GZ_ID,GZ) VALUES ("Jim", 1,20000);
Insert into Gz_tab (NAME,GZ_ID,GZ) VALUES ("Tom", 2,20000);


Update Yg_info set yg_id=20 where name= "Tom";
Delete from Yg_info where yg_id=20;

(3). Primary key Composite PRIMARY key
(The value of the primary key field cannot be the same at the same time)

IP ser_name Port Status
1.1.1.1 vsftpd Deny
1.1.1.1 sshd
2.2.2.2 sshd
1.1.1.10 sshd to deny

CREATE TABLE Sertab (
IP varchar (15),
Ser_name varchar (15),
Port Int (3),
Status enum ("Deny", "Allow"), default "Deny",
Primary KEY (Ip,port)
);

This article is from the "Zhengerniu" blog, make sure to keep this source http://liufu1103.blog.51cto.com/9120722/1656829

V. MySQL indexes and keys