I. Introduction to Indexes

1. Concept of Indexes

An index is a sorted list that stores index values and the physical addresses of the data rows containing these values (similar to how a linked list in C language points to the memory address of a data record via pointers). With an index, there’s no need to scan the entire table to locate a specific row of data. Instead, the physical address corresponding to the row of data is first found through the index table, and then the corresponding data is accessed, thereby accelerating the database query speed. An index is a method for sorting the values of one or more columns in a table.

Simply put, an index is like the table of contents of a book, allowing you to quickly find the desired content based on the page numbers in the table of contents. The purpose of creating an index is to speed up the search or sorting of records in a table.

2. Functions and Side Effects of Indexes

2.1 Functions of Indexes

• After setting appropriate indexes, the database can utilize various fast positioning technologies to significantly speed up querying, which is the primary reason for creating indexes.
• When the table is large or the query involves multiple tables, using indexes can improve query speed by thousands of times.
• It can reduce the IO cost of the database, and indexes can also reduce the sorting cost of the database.
• By creating a unique index, the uniqueness of each row of data in the data table can be ensured.
• It can speed up the connection between tables.
• When using grouping (GROUP BY) and sorting (ORDER BY), the time for grouping and sorting can be greatly reduced.
• Creating indexes can significantly improve performance when searching and recovering data in the database.

Summary: The function of an index is essentially to speed up the query speed of the table and sort the fields of the table.

2.2 Side Effects of Indexes

Indexes require additional disk space. For the MyISAM engine, the index file and the data file are separate, and the index file is used to store the addresses of data records. While for the InnoDB engine, the table data file itself is the index file.

Updating a table with indexes takes more time than updating a table without indexes because the indexes themselves need to be updated. Therefore, the ideal approach is to create indexes only on columns (and tables) that are frequently searched.

2.3 How Indexes Work

Without an index, to query a row of data, it is necessary to scan the entire table to locate the row. With an index, the physical address of the corresponding row of data can be found through the field value of the search condition, and then the corresponding data can be accessed according to the physical address.

3. Principles for Creating Indexes

Although indexes can improve the query speed of the database, they are not suitable for creation in all cases. Because indexes themselves consume system resources, with indexes, the database will first perform an index query and then locate the specific data row. If indexes are used improperly, they will instead increase the burden on the database.

• The primary key and foreign key of a table must have indexes. Because the primary key is unique, and the foreign key is associated with the primary key of the main table, which can be quickly located during querying.
• Tables with more than 300 records should have indexes. Without an index, each query needs to traverse the entire table, which will seriously affect the performance of the database.
• For tables that are frequently joined with other tables, indexes should be created on the join fields.
• Fields with poor uniqueness (fields with duplicate values) are not suitable for creating indexes, as they will reduce efficiency.
• Fields that are updated too frequently are not suitable for creating indexes.
• Fields that often appear in the WHERE clause, especially those in large tables, should be indexed.
• Indexes should be created on fields that are frequently used for GROUP BY (grouping) and ORDER BY (sorting).
• Indexes should be built on fields with high selectivity (fields with low duplication rates).
• Indexes should be built on small fields. For large text fields or even ultra-long fields, do not build indexes. Since indexes need to record the indexed fields, storing large text fields will occupy more resources and reduce efficiency.

4. Classification and Creation of Indexes

create table member (id int (10),name varchar(10),cardid int(18) , phone int (11),adress varcohar(50),remark text);

4.1 Ordinary Index

The most basic type of index with no uniqueness restrictions.

• Create an index directly

CREATE INDEX index_name ON table_name (column_name[(length)]);

Note:

• (column_name(length)): length is optional. If the value of length is omitted, the entire column value is used as the index. If it is specified that the first length characters of the column are used to create the index, it helps to reduce the size of the index file. Under the condition of not losing accuracy, the shorter the length, the better.
• It is recommended that the index name ends with “_index”.

For example:

create index phone_index on member (phone);select phone from member;show create table member;

• Create by modifying the table

ALTER TABLE table_name ADD INDEX index_name (column_name);

For example:

alter table member add index id_index (id);select id from member;select id,name from member;

• Specify the index when creating the table

CREATE TABLE table_name (field1 data_type, field2 data_type[,...], INDEX index_name (column_name));

For example:

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;

4.2 Unique Index

Similar to an ordinary index, but the difference is that each value in the unique index column is unique.

A unique index allows null values (note the difference from the primary key). If created with a composite index, the combination of column values must be unique. Adding a unique key will automatically create a unique index.

• Create a unique index directly

CREATE UNIQUE INDEX index_name ON table_name(column_name);

For example:

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

• Create by modifying the table

CREATE TABLE table_name (field1 data_type, field2 data_type[,...], UNIQUE index_name (column_name));

For example:

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

• Specify when creating the table

4.3 Primary Key Index

A special type of unique index that must be specified as “PRIMARY KEY”.

A table can have only one primary key, and null values are not allowed. Adding a primary key will automatically create a primary key index.

• Specify when creating the table

CREATE TABLE table_name ([...], PRIMARY KEY (column_name));

• Create by modifying the table

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4.4 Composite Index

It can be created on a single column or multiple columns.

CREATE TABLE table_name (column1 data_type, column2 data_type, column3 data_type, INDEX index_name (column1, column2, column3));select * from table_name where column1='...' AND column2='...' AND column3='...';

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;

4.5 Full-Text Index (FULLTEXT)

Suitable for fuzzy queries and can be used to retrieve text information in an article.

Before MySQL 5.6, FULLTEXT indexes could only be used with the MyISAM engine; after version 5.6, the InnoDB engine also supports FULLTEXT indexes.

Full-text indexes can be created on columns of type CHAR, VARCHAR, or TEXT. Only one full-text index is allowed per table.

• Create an index directly

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

For example:

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

• Create by modifying the table

ALTER TABLE table_name ADD FULLTEXT index_name (column_name);

For example:

alter table member add fulltext index remark (remark);

• Specify the index when creating the table

CREATE TABLE table_name (field1 data_type[,...], FULLTEXT index_name (column_name));

The data type can be CHAR, VARCHAR, or TEXT.

• Query using a full-text index

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('query content');

For example:

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

5. Viewing Indexes

show index from table_name;show index from table_name\G;                 #Display table index information verticallyshow keys from table_name;show keys from table_name\G;

The meanings of each field are as follows:

Field	Meaning
Table	The name of the table
Non_unique	0 if the index cannot include duplicate words; 1 if it can
Key_name	The name of the index
Seq_in_index	The sequence number of the column in the index, starting from 1
Column_name	The column name
Collation	How the column is stored in the index. In MySQL, the value is A (ascending) or NULL (no classification)
Cardinality	An estimate of the number of unique values in the index
Sub_part	If the column is only partially indexed, it is the number of characters indexed. If the entire column is indexed, it is NULL
Packed	Indicates how the key is compressed. If not compressed, it is NULL
Null	Contains YES if the column contains NULL; NO if it does not
Index_type	The index method used (BTREE, FULLTEXT, HASH, RTREE)
Comment	Remarks

6. Deleting Indexes

6.1 Delete an index directly

DROP INDEX index_name ON table_name;

Example:

drop index name_index on member;

6.2 Delete an index by modifying the table

ALTER TABLE table_name DROP INDEX index_name;

For example:

alter table member drop id_index;show index from member;

6.3 Delete a primary key index

ALTER TABLE table_name DROP PRIMARY KEY;

For example:

alter table member drop primary key;show index from member;

7. Analyzing Whether an Index is Used

Use EXPLAIN for analysis and optimization. EXPLAIN shows how MySQL uses indexes to process SELECT statements and join tables.

Relevant parameters of EXPLAIN:

Parameter	Meaning
possible_keys	Shows the indexes that may be applied to this table
key	The actual index used. If it is NULL, no index is used
key_len	The length of the index used. Under the condition of not losing accuracy, the shorter the length, the better
ref	Shows which column of the index is used, and if possible, it is a constant
Extra	Additional information about how MySQL parses the query

explain select statement  #Can be used to analyze the select query statement. Check the key field to determine whether the index is used or used correctly