1. Database index
1.1 index overview
The index is similar to the table of contents in a book. In a book, you can quickly find the required information without reading the whole book. The table of contents in the book is a word list, which indicates the page number containing each word
The database index is in the database. The index enables the database program to find the required data without scanning the whole table
An index in a database is a collection of one or more column values in a table and a list of logical pointers to the data pages that physically identify these values
1.2 index function
After setting the appropriate index, the database uses location technology to speed up the query rate
When the table is large, or there are many contents, or the query involves multiple tables, the use of indexes can speed up the query to a dry multiple
It can reduce the IO cost of the database, and the index can also reduce the sorting cost of the database
Ensure the uniqueness of data in the data table by creating a unique index
It can speed up the connection between tables
When using grouping and sorting, the grouping and sorting time can be greatly reduced
Additional disk space required
1.3 principles and basis of index creation
Knock on the blackboard, draw the key points, and set the index in the appropriate place, otherwise it will become the burden of the database
The primary key and foreign key of a table must have an index
Tables with more than 300 rows of data should have indexes
For tables that often connect with other tables, an index should be established on the connection field
Fields with poor uniqueness are not suitable for indexing
Fields that are updated too frequently are not suitable for index creation
The fields that often appear in the Where clause, especially the fields of large tables, should be indexed
Indexes should be built on highly selective fields
The index should be built on small fields. For large text fields or even super long fields, do not build an index
2, Classification of indexes
The index is divided into ordinary index, unique index, primary key index, combined index and full-text index.
2.1 general index
2.1.1 general index format
Ordinary index is the most basic index. It has no restrictions and is also used in most cases.
Direct index creation mysql>CREATE INDEX index_name ON table_name (column(length)); column Is the name of the column that specifies the index to be created length Is optional
The length of the index column has a maximum limit of 255 bytes (the maximum limit of MyISAM and InnoDB tables is 1000 bytes). If the length of the index column exceeds this limit, it can only be indexed with the prefix of the column. In addition, columns of type BLOB or TEXT must also use prefix indexes.
2.1.2 creating a general index
Method 1:
mysql> create table info (id int(4) not null,name varchar(10) not null,address varchar(50) default 'unknown'); ##Create a table structure Query OK, 0 rows affected (0.01 sec) mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | | NULL | | | name | varchar(10) | NO | | NULL | | | address | varchar(50) | YES | | unknown | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create index id_index on info(id); ##Create an index for the id in the info table Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | MUL | NULL | | | name | varchar(10) | NO | | NULL | | | address | varchar(50) | YES | | unknown | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Method 2:
Adding indexes by modifying the table structure mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));
mysql> alter table info add index name_index (name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe info; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | MUL | NULL | | | name | varchar(10) | NO | MUL | NULL | | | address | varchar(50) | YES | | unknown | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Method 3:
When you create a table structure, an index is created
mysql> create table num (id int(3),index id_index(id)); Query OK, 0 rows affected (0.01 sec) mysql> describe num; +-------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | id | int(3) | YES | MUL | NULL | | +-------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec)
2.1.3 view index
mysql> show index from info\G;
*************************** 1. row ***************************
Table: info
Non_unique: 1
Key_name: id_index
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: info
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
ERROR:
No query specified
2.2 unique index
A unique index is similar to an ordinary index, except that the value of the index column of a unique index must be unique, but it is allowed to be empty
Value (note that it is different from the primary key). If it is a composite index, the combination of column values must be unique. Unique index creation method and method
The general index is similar.
Add a unique index when modifying the table structure:
mysql>CREATEUNIQUEINDEXindex_nameON table_name(column(length));
Create a new table
mysql> create table info2 (id int(4) not null,name varchar(10) not null,address varchar(50) default 'unknown',primary key(id)); mysql> describe info2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(10) | NO | | NULL | | | address | varchar(50) | YES | | unknown | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create unique index unique_name on info2(name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe info2; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(10) | NO | UNI | NULL | | | address | varchar(50) | YES | | unknown | | +---------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Add a unique index when modifying the table structure:
format mysql>ALTERTABLEtable_nameADD UNIQUEindex_name(column(length));
Create a unique index when creating a table:
CREATETABLE`table`( ... UNIQUE index Name (title(length)) );
2.3 primary key index
Primary key index is a special unique index. A table can only have one primary key, and no null value is allowed. Generally, the primary key index is created at the same time when the table is being created.
The primary key index, that is, the primary key, has been demonstrated in the previous process of creating tables. There are two methods.
First: create outside the field mysql> create table info2 (id int(4) not null,name varchar(10) not null,address varchar(50) default 'unknown',primary key(id)); Second: create in field mysql> create table info2 (id int(4) not null primary key,name varchar(10) not null,address varchar(50) default 'unknown');
2.4 composite index (leftmost prefix)
Generally, the SQL query statements used at ordinary times have many restrictions. Therefore, in order to further extract the efficiency of MySQL, it is necessary to consider establishing a composite index. In the creation of composite index, there are two scenarios: single column index and multi column index
Lead.
characteristic:
Follow the leftmost principle, from left to right;
2.5 full text index
For larger datasets, entering data into a table without FULLTEXT index and then creating an index is faster than entering data into an existing FULLTEXT index. However, keep in mind that for large data tables, generating full-text indexes is a very time-consuming and hard disk space consuming practice.
mysql> alter table info2 add fulltext index addr_ index(address); ## Create full-text index
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> describe info2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(10) | NO | UNI | NULL | |
| address | varchar(50) | YES | MUL | unknown | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from info2\G;
*************************** 1. row ***************************
Table: info2
Non_unique: 0
Key_name: PRIMARY
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: info2
Non_unique: 0
Key_name: unique_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: info2
Non_unique: 1
Key_name: addr_index
Seq_in_index: 1
Column_name: address
Collation: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
3 rows in set (0.00 sec)
ERROR:
No query specified
3, View and delete indexes
3.1 view index
Two ways to view indexes mysql>show index from tablename; mysql>show keys from tablename;
Take show keys from tablename as an example. The usage of the two methods is the same
mysql> show keys from info2; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | info2 | 0 | unique_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | | info2 | 1 | addr_index | 1 | address | NULL | 0 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
You can use MySQL > show keys from info2 \ g; Use to display results more intuitively
mysql> mysql> show keys from info2\G;
*************************** 1. row ***************************
Table: info2
Non_unique: 0
Key_name: PRIMARY
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: info2
Non_unique: 0
Key_name: unique_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: info2
Non_unique: 1
Key_name: addr_index
Seq_in_index: 1
Column_name: address
Collation: NULL
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
3 rows in set (0.00 sec)
ERROR:
No query specified
Table: name of the table.
Non_unique: 0 if the index cannot include duplicate words; 1 if yes.
Key_name: the name of the index.
Seq_in_index: column sequence number in the index, starting from 1.
Column_name: column name.
Collision: how columns are stored in the index. In MySQL, there are values' A '(ascending) or NULL (no classification).
Cardinality: an estimate of the number of unique values in the index. You can update by running analyze table or myisamchk-a. Cardinality counts according to statistics stored as integers, so even for small tables, the value does not need to be accurate. The greater the cardinality, the greater the chance that MySQL will use the index when federating.
Sub_part: if the column is only partially indexed, the number of characters indexed. NULL if the entire column is indexed.
Packed: indicates how the keyword is compressed. NULL if not compressed.
NULL: if the column contains NULL, it contains YES. If not, the column contains NO.
Index_type: used index method (BTREE, FULLTEXT,HASH, RTREE).
Comment: remarks.
3.2 delete index
After the index is created, it will occupy a certain disk space. Therefore, if there are indexes in the table that are no longer used, it is best to delete useless indexes from the perspective of database performance.
Two ways to delete an index DROP INDEX Index name ON Table name; ALTER TABLE Table name DROP INDEX Index name;
mysql> drop index addr_index on info2; ##Delete the full-text index you just created
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show keys from info2\G;
*************************** 1. row ***************************
Table: info2
Non_unique: 0
Key_name: PRIMARY
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: info2
Non_unique: 0
Key_name: unique_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
ERROR:
No query specified