Classification, creation and deletion of MySQL indexes

1, Index overview

1. Concept of index

  • An index is a sorted list in which the index value and the physical address of the row containing the data containing the value are stored (similar to the linked list of C language, which points to the memory address of the data record through a pointer).
  • After using the index, you can not scan the whole table to locate the data of a row, but first find the corresponding physical address of the row data through the index table, and then access the corresponding data, so it can speed up the query speed of the database.
  • An index is like a table of contents of a book. You can quickly find the required content according to the page numbers in the table of contents.
  • An index is a method of sorting the values of one or more columns in a table.
  • The purpose of indexing is to speed up the search or sorting of records in the table.

2. Function of index

  • After setting the appropriate index, the database can greatly speed up the query speed by using various fast positioning technologies, which is the main reason for creating the index.
  • When the table is large or the query involves multiple tables, using indexes can improve the query speed thousands of times.
  • It can reduce the IO cost of the database, and the index can also reduce the sorting cost of the database.
  • By creating a unique (key) index, you can ensure the uniqueness of each row of data in the data table.
  • You can speed up the connection between tables.
  • When using grouping and sorting, the time of grouping and sorting can be greatly reduced.

PS: side effects of indexing
The index needs to occupy additional disk space;
For MyISAM engine, index file and data file are separated, and index file is used to save the address of data record. The table data file of InnoDB engine itself is an index file;
It takes more time to insert and modify data because the index changes with it.

3. Principles and basis of index creation

   although index can improve the speed of database query, it is not suitable to create index under any circumstances. Because the index itself will consume system resources, when there is an index, the database will first query the index and then locate the specific data row. If the index is not used properly, it will increase the burden of the database.

The following principles should be followed when creating an index

  • The primary key and foreign key of a table must have an index. Because the primary key is unique, the foreign key is associated with the primary key of the child table, which can be quickly located during query
  • Tables with more than 300 rows of records should have indexes. If there is no index, you need to traverse the table, which will seriously affect the performance of the database
  • 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 and creation of indexes

1. Create a template

mysql -u root -p

create database info;
use info;
create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
desc member;

insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');

select * from member;

Example:

MySQL root@localhost:(none)> create database info;
Query OK, 1 row affected
Time: 0.001s
MySQL root@localhost:(none)> use info;
You are now connected to database "info" as user "root"
Time: 0.000s
MySQL root@localhost:info> create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
Query OK, 0 rows affected
Time: 0.010s
MySQL root@localhost:info> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | <null>  |       |
| name    | varchar(10) | YES  |     | <null>  |       |
| cardid  | varchar(18) | YES  |     | <null>  |       |
| phone   | varchar(11) | YES  |     | <null>  |       |
| address | varchar(50) | YES  |     | <null>  |       |
| remark  | text        | YES  |     | <null>  |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set
Time: 0.008s
MySQL root@localhost:info> insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
Query OK, 1 row affected
Time: 0.003s
MySQL root@localhost:info> insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
Query OK, 1 row affected
Time: 0.003s
MySQL root@localhost:info> insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
Query OK, 1 row affected
Time: 0.004s
MySQL root@localhost:info> insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
Query OK, 1 row affected
Time: 0.003s
MySQL root@localhost:info> insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
Query OK, 1 row affected
Time: 0.004s
MySQL root@localhost:info> select * from member;
+----+----------+---------+--------+----------+----------------+
| id | name     | cardid  | phone  | address  | remark         |
+----+----------+---------+--------+----------+----------------+
| 1  | zhangsan | 123     | 111111 | nanjing  | this is vip    |
| 2  | wangwu   | 12345   | 222222 | benjing  | this is normal |
| 3  | qianqi   | 1234567 | 333333 | shanghai | this is vip    |
| 4  | lisi     | 1234    | 444444 | nanjing  | this is normal |
| 5  | zhaoliu  | 123456  | 555555 | nanjing  | this is vip    |
+----+----------+---------+--------+----------+----------------+
5 rows in set
Time: 0.006s

2. General index

Ordinary index is the most basic index type, and there are no restrictions such as uniqueness.

(1) Create index directly

CREATE INDEX Index name ON Table name (Listing[(length)]);
#(column name [(length)]): length is optional. If the value of length is ignored, the value of the whole column is used as the index.
#If you specify to use the length characters before the column to create the index, it is helpful to reduce the size of the index file.
#It is recommended that index names end with "_index".
_______________________________________________
create index phone_index on member (phone);
select phone from member;
show create table member;

Example:

MySQL root@localhost:info> create index phone_index on member (phone);
Query OK, 0 rows affected
Time: 0.030s
MySQL root@localhost:info> select phone from member;
+--------+
| phone  |
+--------+
| 111111 |
| 222222 |
| 333333 |
| 444444 |
| 555555 |
+--------+
5 rows in set
Time: 0.005s
MySQL root@localhost:info> show create table member\G;
***************************[ 1. row ]***************************
Table        | member
Create Table | CREATE TABLE "member" (
  "id" int(10) DEFAULT NULL,
  "name" varchar(10) DEFAULT NULL,
  "cardid" varchar(18) DEFAULT NULL,
  "phone" varchar(11) DEFAULT NULL,
  "address" varchar(50) DEFAULT NULL,
  "remark" text,
  KEY "phone_index" ("phone")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s

(2) Modify table creation

ALTER TABLE Table name ADD INDEX Index name (Listing);
-------------------------
alter table member add index id_index(id);
select id from member;
select id,name from member;

Example:

MySQL root@localhost:info> alter table member add index id_index(id);
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.014s
MySQL root@localhost:info> select id from member;
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 5  |
+----+
5 rows in set
Time: 0.008s
MySQL root@localhost:info> select id,name from member;
+----+----------+
| id | name     |
+----+----------+
| 1  | zhangsan |
| 2  | wangwu   |
| 3  | qianqi   |
| 4  | lisi     |
| 5  | zhaoliu  |
+----+----------+
5 rows in set
Time: 0.007s

(3) Specify the index when creating the table

CREATE TABLE Table name (Field 1 data type,Field 2 data type,[...],INDEX Index name(Listing));
_______________________________________________________________________
create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));
show create table test\G;

Example:

MySQL root@localhost:info> show create table test\G;
***************************[ 1. row ]***************************
Table        | test
Create Table | CREATE TABLE "test" (
  "id" int(4) NOT NULL,
  "name" varchar(10) NOT NULL,
  "cardid" varchar(18) NOT NULL,
  KEY "id_index" ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set
Time: 0.001s

3. Unique index

  • It is similar to a normal index, but the difference is that each value of a unique index column is unique
  • Null values are allowed for unique indexes (note that they are different from the primary key)
  • If you are creating with a composite index, the combination of column values must be unique
  • Adding a unique key automatically creates a unique index

(1) Create unique index directly

CREATE UNIQUE INDEX Index name ON Table name(Listing);

Example:

select * from member;
create unique index address_index on member (address);
create unique index name_index on member (name);                             1
show create table member;

(2) Modify table creation

ALTER TABLE Table name ADD UNIQUE Index name (Listing);

Example:

alter table member add unique cardid_index (cardid);

(3) Specify when creating a table

CREATE TABLE Table name (Field 1 data type,Field 2 data type,[...],UNIQUE Index name (Listing));

Example:

create table amd2 (id int,name varchar(20),unique id_index (id));
show creat table amd2;

create table test2 (id int,name varchar(40),age int(5),primary key (id));

4. Primary key index

  • Is a special unique index and must be specified as "PRIMARY KEY"
  • A table can only have one primary key. Null values are not allowed. Adding a primary key will automatically create a primary key index

(1) Specify when creating a table

CREATE TABLE Table name ([...],PRIMARY KEY (Listing));

Example:

create table test1 (id int primary key,name varchar(20));
create table test2 (id int,name varchar(20),primary key (id));

show create table test1;
show create table test2;

(2) Modify table creation

ALTER TABLE Table name ADD PRIMARY KEY (Listing);

5. Combined index (single column index and multi column index)

   it can be an index created on a single column or an index created on multiple columns. It needs to meet the leftmost principle, because the where condition of the select statement is executed from left to right, so when using the select statement to query, the field order used by the where condition must be consistent with the sorting in the composite index, otherwise the index will not take effect.

CREATE TABLE Table name (Column name 1 data type,Column name 2 data type,Column name 3 data type,INDEX Index name (Column name 1,Column name 2,Column name 3));

select * from Table name where Column name 1='...' AND Column name 2='...' AND Column name 3='...';

Example:

create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
select * from amd1 where name='zhangsan' and id=1;

Summary:
The order of fields created by a composite index is the order in which it triggers the query of the index

create table "test3" (id int(11) not null,name varchar(50) default null,age int(5) default null,key index_idname (id,name)) engine=innodb default charset=utf8;

> `Compare the above table select query`
select id,name from test3;		#The composite index is triggered
select name,id from test3;		#If the index is retrieved from left to right, the combined index will not be triggered

6. Full text index (FULLTEXT)

   it is suitable for fuzzy query and can be used to retrieve TEXT information in an article. Before MySQL version 5.6, FULLTEXT index can only be used in MyISAM engine. After version 5.6, innodb engine also supports FULLTEXT index. Full TEXT index can be created on CHAR, VARCHAR or TEXT columns. Only one full-TEXT index is allowed for each table.

(1) Create index directly

CREATE FULLTEXT INDEX Index name ON Table name (Listing);

Example:

select * from member;
create fulltext index remark_index on member (remark);

(2) Modify table creation

ALTER TABLE Table name ADD FULLTEXT Index name (Listing);

(3) Specify the index when creating the table

CREATE TABLE Table name (Field 1 data type 1,[...],FULLTEXT Index name (Listing));

The data type can be CHAR, VARCHAR or TEXT

(4) Using full-text index queries

SELECT * FROM Table name WHERE MATCH(Listing) AGAINST('Query content');

Example:

select * from member where match(remark) against('this is vip');
or 
select * from member where remark='this is vip';

7. Summary

The index is divided into:

  • General index: there are no special requirements / rules for all fields
  • Unique index: for unique fields, only one null value is allowed
  • Composite index: an index in the form of multi column / multi field combination
  • Full text index: varchar char text
  • Primary key index: it refers to unique fields and cannot be empty. At the same time, a table can only contain one primary key index

Create index:

  • When creating a table, specify the index directly
  • When alter modifies the table structure, add the index
  • Create index directly

3, View index

show index from Table name;
show index from Table name\G;		#Vertical display of table index information
show keys from Table name;
show keys from Table name\G;

Example:

MySQL root@localhost:info> show index from member;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| member | 0          | cardid_index | 1            | cardid      | A         | 5           | <null>   | <null> | YES  | BTREE      |         |               |
| member | 1          | phone_index  | 1            | phone       | A         | 5           | <null>   | <null> | YES  | BTREE      |         |               |
| member | 1          | id_index     | 1            | id          | A         | 5           | <null>   | <null> | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set
Time: 0.010s

The meaning of each field is as follows:

fieldmeaning
TableName of the table
Non_unique0 if the index content is unique; 1 if it can not be unique
Key_nameThe name of the index
Seq_in_indexColumn ordinal in index, starting from 1. limit 2,3
Column_nameColumn name
CollationHow columns are stored in the index. In MySQL, there are values' A '(ascending) or NULL (no classification)
CardinalityAn estimate of the number of unique values in the index
Sub_partIf the column is only partially indexed, the number of characters indexed (zhangsan). NULL if the entire column is indexed
PackedIndicates how keywords are compressed. NULL if not compressed
NullIf the column contains NULL, it contains YES. If not, the column contains NO
Index_typeUsed indexing methods (BTREE, FULLTEXT, HASH, RTREE)
Commentremarks

4, Delete index

1. Delete index directly

DROP INDEX Index name ON Table name;

Example:

drop index name_index on member;

2. Delete index by modifying table method

ALTER TABLE Table name DROP INDEX Index name;

Example:

alter table member drop index id_index;
show index from member;

3. Delete primary key index

ALTER TABLE Table name DROP PRIMARY KEY;

Tags: Database MySQL cloud computing index

Posted on Sat, 23 Oct 2021 22:26:08 -0400 by don_s

Hot Tags

©2023 Programmer Help