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

Overview of Mysql Index

Index introduction

An index is a method of sorting multiple fields of a recordset.

A catalogue similar to a book

Index types include: Btree,B+tree,hash

Advantages and disadvantages of index

Advantages of index

-by creating a uniqueness index, you can ensure the uniqueness of each row of data in the database.

-can speed up the retrieval of data

Index shortcoming

When the data in the table is added, deleted and modified, the index should also be maintained dynamically to reduce the maintenance speed of the data.

-the index needs to take up physical space

MySQL key value type

Key value type

INDEX: general index

UNIQUE: unique index

FULLTEXT: full-text index

PRIMARY KEY: primary key

POREIGN KEY: foreign key

INDEX General Index

instructions

-there can be multiple INDEX fields in a table

-the value of the field allows duplicates and can be assigned a null value

-often set the field for query conditions to the INDEX field

-key flag bit MUL of the INDEX field

Create a table to specify the index field

-index (Field 1), index (Field 2)...

Mysql > create table test1 (- > id char (6) not null,-> name varchar (4) not null,-> age int (3) not null,-> gender enum ('male','female'),-> index (id), index (name)->); Query OK, 0 rows affected (0.06 sec) mysql > desc test1 +-+-- + | Field | Type | Null | Key | Default | Extra | +-+ -+ | id | char (6) | NO | MUL | NULL | name | varchar (4) | NO | MUL | NULL | | age | int (3) | NO | | NULL | | gender | enum ('male' 'female') | YES | | NULL | | +-+-- + 4 rows in set (0.00 sec)

Set the index field in an existing table

-create index index name on table name (field name)

Mysql > create index age on test1 (age); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test1 +-+-- + | Field | Type | Null | Key | Default | Extra | +-+ -+ | id | char (6) | NO | MUL | NULL | name | varchar (4) | NO | | NULL | | age | int (3) | NO | MUL | NULL | | gender | enum ('male' 'female') | YES | | NULL | | +-+-- + 4 rows in set (0.00 sec)

Delete the index field of the specified table

-drop index index name on table name

Mysql > drop index name on test1;Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test1 +-+-- + | Field | Type | Null | Key | Default | Extra | +-+ -+ | id | char (6) | NO | MUL | NULL | name | varchar (4) | NO | | NULL | | age | int (3) | NO | | NULL | | gender | enum ('male' 'female') | YES | | NULL | | +-+-- + 4 rows in set (0.00 sec)

View the index information of the table

-show index from table name

Mysql > show index from test1\ G * * 1. Row * * Table: test1 Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type : BTREE Comment: Index_comment: * * 2. Row * * Table: test1 Non_unique: 1 Key_name: age Seq_in_index: 1 Column_name: age Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)

Primary key primary key

Basic concept

-there can be only one primary key field in a table

-the corresponding field values cannot be duplicated, and NULL values are not allowed to be assigned.

-if multiple fields are used as primary key, called compound primary keys, they must be created together

-the KEY flag of the primary key field is PRI

-usually used with AUTO_INCREMENT

-often set the field in the table that uniquely identifies the record as the primary key field [record number field]

Specify the primary key field when creating the table

-primary key (field name)

Mysql > create table test2 (- > id int (3) auto_increment,-> name varchar (4) not null,-> age int (2) not null,-> primary key (id)->); Query OK, 0 rows affected (0.05 sec) mysql > desc test2 +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | int (3) | NO | PRI | NULL | auto_increment | | name | varchar (4) | NO | | NULL | | age | int (2) | NO | | NULL | | + -- + 3 rows in set (0.00 sec

Set the primary key field in an existing table

-alter table table name add primary key (field name)

Mysql > desc test2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (3) | NO | | NULL | | name | varchar (4) | NO | | NULL | | age | int (2) | NO | | NULL | | +-+-+ 3 rows in Set (0.01 sec) mysql > alter table test2 add primary key (name) Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (3) | NO | | NULL | | name | varchar (4) | NO | PRI | NULL | | age | int (2) | NO | | NULL | | +-+-+ 3 rows in set (0.00 sec)

Remove the PRIMARY KEY field from the table

-alter table table name drop primary key; (remove the primary key first and then remove the primary key if there is an auto_increment. Remove auto_incrememt to reassign the field type)

Ysql > desc test2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (3) | NO | PRI | NULL | name | varchar (4) | NO | | NULL | | age | int (2) | NO | | NULL | | +-+-+ 3 rows in set (0.00 sec) mysql > alter table test2 drop primary key Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test2 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (3) | NO | | NULL | | name | varchar (4) | NO | | NULL | | age | int (2) | NO | | NULL | | +-+-+ 3 rows in set (0.01sec)

Foreign key foreign key

Basic concept

-lets the value of the current table field be selected within the worthy range of the field in another table.

Conditions for using foreign keys

-the storage engine for the table must be innodb

-the field type should be consistent

-the referenced field must be one of the index types (primary key)

Basic usage

Mysql > create table yuangong (- > yg_id int (4) auto_increment,-> name char (16) not null,-> primary key (yg_id)->) Query OK, 0 rows affected (0.31 sec) mysql > create table gongzi (- > gz_id int (4) not null,-> name char (15) not null,-> gz float (6 not null default 2) not null default 0,-> index (name),-> foreign key (gz_id) references yuangong (yg_id)-> on update cascade on delete cascade->); Query OK, 0 rows affected (0.32 sec)

Delete foreign key field

-alter table table name drop foreign key constraint name

Mysql > show create table gongzi\ G * * 1. Row * * Table: gongziCreate Table: CREATE TABLE `gongzi` (`gongzi` int (4) NOT NULL, `name` char (15) NOT NULL, `gz` float (6) NOT NULL DEFAULT '0.005), KEY `name` (`name`), KEY `gz_ id` (`gz_ id`) CONSTRAINT `sec 1` FOREIGN KEY (`gz_ id`) REFERENCES `yuangong` (`yg_ id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.01sec) mysql > alter table gongzi drop foreign key gongzi_ibfk_1 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table gongzi\ G * * 1. Row * * Table: gongziCreate Table: CREATE TABLE `gongzi` (`gz_ id` int (4) NOT NULL, `name` char (15) NOT NULL, `gz` float (6) NOT NULL DEFAULT '0.005, KEY `name` (`name`) KEY `gz_ id` (`gz_ id`) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

UNIQUE index

Basic concept

-UNIQUE indicates uniqueness. Multiple fields in the same field can be unique.

Specify the UNIQUE index field when creating the table

Looking at the field structure of the newly created test3 table, we can find that the KEY flag of the UNIQUE field is UNI;. In addition, because the field name must meet the non-empty constraint of "NOT NULL", it will automatically become the PRIMARY KEY primary key field when it is set to UNIQUE:

Mysql > create table test3 (- > id char (6),-> name varchar (4) not null,-> age int (3) not null,-> unique (id), unique (name), index (age)->); Query OK, 0 rows affected (0.36 sec) mysql > desc test3 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | char (6) | YES | UNI | NULL | | name | varchar (4) | NO | PRI | NULL | | age | int (3) | NO | MUL | NULL | +-+-+ 3 rows in set (0.00 sec)

Delete the UNIQUE index and set the UNIQUE index field in the existing table

Mysql > drop index name on test3;Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test3 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | char (6) | YES | UNI | NULL | name | varchar (4) | NO | | NULL | | age | int (3) | NO | MUL | NULL | | +-+-+ 3 rows in set (0.00 sec)

Create an UNIQUE index in an existing table

Mysql > create unique index name on test3 (name); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > desc test3 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | char (6) | YES | UNI | NULL | | name | varchar (4) | NO | PRI | NULL | | age | int (3) | NO | MUL | NULL | +-+-+ 3 rows in set (0.00 sec)

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.