Method and introduction of table index definition in MySQL

Overview

Index is the corresponding relationship table between column values and record rows established by DBMS according to one column or several columns in the table in the order determined by one, which is convenient for DBA to manage

Indexes are stored in the form of files. DBMS saves all of a table in the same index file, which takes up disk space. If there are a large number of indexes, it may reach the maximum file size faster than data files. Indexes improve the query speed, but at the same time, they reduce the speed of updating tables. When you update data on an index column in a table, the index is automatically updated, ensuring that the index tree is exactly the same as the contents of the table, so the more indexes, the longer the update.

According to the purpose, indexes are logically divided into three categories

General Index (INDEX): The most basic index type without any restrictions. Usually use the keyword INDEX or KEY Uniqueness index (UNIQUE): All values in the index can only appear once, and must be only 1. Usually use keyword UNIQUE Primary key (PRIMARY KEY): A primary key is a uniqueness index. When creating a primary key, you must specify the keyword PRIMARY KEY, and no null value is allowed. 1 can be specified when creating a table, or can be added by modifying a table. Each table can only have one primary key

Create an index

There are three ways to create indexes

CREATE INDEX

CREATE [UNIQUE] INDEX index_name
NO tbl_name(index_col_name,...)

UNIQUE: Specifies that a uniqueness index is created, that multiple indexes can be created on a table, and each index has a uniqueness name in the table tabl_name: Table name of database index_col_name: Description of the index. The format is col_name [(length)] [ASCDESC]

Three syntax elements of index description

col_name lenght ASC|DESC

mysql>CREATE INDEX index_customers
-> NO mysql_test.customers (cust_name(3)ASC)
Query OK, 0 rows affected (0.20 sec)
Records:0 Duplicates:0 Warning:0

CREATE TABLE

[CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...): Create the primary key of the new table at the same time {INDEXKEY} [index_name] (index_col_name,...): Create the table index at the same time [CONSTRAINT [symbol]] UNIQUE [INDEXKEY] [index_name] (index_col_name,...): Used to create a uniqueness index when creating a table [CONSTRATIN [symbol]] FOREIGN KEY [index_name] (index_col_name,...): Create a foreign key while creating a table KEY: Synonyms for the keyword INDEX CONSTRAINT: One name is defined for primary key, UNIQUE key and foreign key. When using CREATE TABLE to define column options, you can add primary key by adding PRIMARY KEY directly after a column definition. This method cannot be used when the primary key is a multi-column index composed of multiple columns.

mysql> USE mysql_test
Database changed
mysql> CREATE TABLE seller
->(
-> seller_id int NOT NULL AUTO_INCREMENT
-> seller_name char(50) NOT NLULL,
-> seller_address char(50) null,
-> product_type int(5) NULL
-> sales int NULL
-> PRIMARY KEY (seller_id,product_type)
-> INDEX index_seller(salse)
->)
Query OK, 0 rows affected (0.20 sec)

ALTER TABLE

Summarize