Indexes in MySQL

1. Overview of the index

1.1 Index Overview

MySQL’s official indexingdefineFor: Index is a data structure that helps MySQL to fetch data efficiently.

The Nature of Indexing: Indexes are data structures. You can think of them simply as “ordered fast lookup data structures” that satisfy a particular lookup algorithm. These data structures point to the data in a certain way, so that you can build on them to implementAdvanced Finding Algorithms 。

1.2 Advantages

(1) Bibliographic indexing similar to university libraries to improve the efficiency of data retrieval.Reduce database IO costs This is the main reason for creating indexes.

(2) By creating unique indexes, you can ensure that each row of data in a database table has a unique index.uniqueness 。

(3) In terms of realizing the referential integrity of data, it is possible toAccelerated meter and meter connections In other words, the speed of a query can be increased when the child table is joined with the parent table. In other words, the query speed can be increased when the child table and parent table are jointly queried with a dependency.

(4) When using grouping and sorting clauses for data queries, it can be significantlyReduce time spent grouping and sorting in queries , reducing CPU consumption.

1.3 Disadvantages

There are also many disadvantages to adding an index, notably in the following areas:

(1) Create indexes and maintain indexes totake up time and the time taken increases as the amount of data increases.

(2) Indexing needs to account fordisk space In addition to the data space occupied by the data tables, each index takes up a certain amount of physical space.Store on disk If there are a large number of indexes, the index file may reach its maximum file size faster than the data file.

(3) While indexes greatly increase query speed, they canReduce the speed of updating tables The index is maintained dynamically as data is added, deleted, and modified in the table. The indexes are also dynamically maintained as data is added, deleted, and modified in the table, which reduces the speed of data maintenance.

Therefore, when choosing an index, the advantages and disadvantages of the index need to be considered together.

Tip:

Indexes can improve query speed, but they can affect the speed of inserting records. In this case, the best way is to delete the index in the table first, then insert the data, and then create the index after the insertion is complete.

1.6 Common Indexing Concepts

Indexes can be categorized into two types according to their physical implementation: clustered (aggregated) and unclustered (unaggregated) indexes.

We also refer to unaggregated indexes as secondary indexes or auxiliary indexes.

1.6.1 Clustered Indexes

specificities：

1. Sorting records and pages using the size of the record’s primary key value has three implications:
   1. The records within a page are arranged in a unidirectional chained table in the order of the size of the primary key .
   2. The pages holding the user records are also arranged in a bi-directional chained table based on the order of the primary keys of the user records in the page.
   3. The pages storing the catalog entry records are divided into different levels, and the pages in the same level are also arranged in a bidirectional chained table according to the size order of the primary keys of the catalog entry records in the pages .
2. 1. The leaf nodes of the B+ tree store the complete user record. By a complete user record, we mean that the values of all columns (including hidden columns) are stored in this record.

vantage：

• Faster data access Because clustered indexes keep indexes and data in the same B+ tree, it is faster to get data from clustered indexes than non-clustered indexes (non-clustered indexes have to go back to the table)
• Clustered indexes are not useful for primary keySort Search respond in singingrange finder It’s very fast.
• Order by clustered indexWhen the query displays a certain range of data, the database doesn’t have to extract data from multiple data blocks because the data are all tightly connected, so ** it saves a lot of io ** operations.

drawbacks：

• Insertion speed is heavily dependent on insertion order The insertion in the order of the primary key is the fastest way, otherwise there will be page splitting, which will seriously affect performance. Therefore, for InnoDB tables, we generally define aSelf-incremented ID column as primary key
• Updating primary keys is expensive because it will cause the rows being updated to move. Therefore, for InnoDB tables, we generally define thePrimary key is not updatable

limitation：

1.6.2 Secondary indexes (auxiliary indexes, non-clustered indexes)

• Secondary index access requires two index lookups (back to table) The first time the primary key value is found and the second time the row data is found based on the primary key value.

concept：-inning of the game We base this B+-tree ordered by the size of the c2 columns on thishave no other choiceDetermine what we are looking for in the recordprimary key valueSo if we want to find a complete user record based on the value of column c2, we still need to go to the clustered index and look it up again, a process known as table return. This process is called table lookup. That is, to look up a complete user record based on the value of column c2, we need to use 2 B+ trees!

concern: why do we need another-inning of the game What about the operation? Wouldn’t it be OK to just put the complete user record into the leaf node?

responsive：

wrap-up: Difference between clustered and non-clustered indexes:

1. clustered index(used form a nominal expression)leaf node What’s stored is ourData logging， unclustered index(used form a nominal expression)leaf node StoredData location (primary key). Non-clustered indexes do not affect the physical storage order of data tables.
2. a tablehave no other choice there areA clustered indexBecause there’s only one way to sort the storage, but there can beMultiple non-clustered indexes, that is, multiple indexed catalogs provide data retrieval.
3. When using clustered indexes, the querying efficiency of data is high, but if you perform insertion, deletion, and update operations on the data, the efficiency will be lower than that of non-clustered indexes.

1.6.3 Federated indexes

We can also use the size of multiple columns as a sorting rule at the same time, i.e., index multiple columns at the same time, let’s say we want the B+ tree to be ordered byc2andc3This has two meanings: the size of the columns is sorted by the size of the columns:

• Start by sorting the individual records and pages by column c2.
• In case the records have the same c2 column, the c3 column is used for sorting .

A schematic of the indexes created for columns c2 and c3 is shown below:

Note that a B+ tree built with the size of columns c2 and c3 as the ordering rule is called aunion index , which is also essentially a secondary index. Its meaning is different from the formulation of separate indexes for columns c2 and c3, respectively, and the differences are as follows:

• build upunion index Only 1 B+ tree will be created as shown above.
• Creating separate indexes for columns c2 and c3 will create 2 B+ trees using the sizes of columns c2 and c3 as sorting rules, respectively.

1.8 Principles of MyISAM Indexing

The following diagram shows the schematic of a MyISAM index

If we create a secondary index on Col2, the structure of this index is shown below:

1.9 MyISAM vs InnoDB

MyISAM’s indexing methods are all “unclustered”, unlike InnoDB which contains 1 clustered index.

A short summary of the differences between indexes in the two engines：

In InnoDB, we only need to do one lookup on the clustered index based on the primary key value to find the corresponding record, whereas in MyISAM, we need to do a table lookup, which means that the indexes created in MyISAM are all secondary indexes.

② InnoDB data files themselves are index files, while MyISAM index files and data files are separate, the index file only stores the address of the data records.

The data field of a non-clustered index in InnoDB stores the value of the primary key of the corresponding record, while a MyISAM index records the address. In other words, all nonclustered indexes in InnoDB refer to the primary key as the data field.

④ MyISAM’s table return operation is very fast, because it takes the address offset and fetches the data directly from the file. On the contrary, InnoDB fetches the primary key and then goes to the clustered index to find the records, which is not slow, but still not as fast as accessing it directly with the address.

⑤ InnoDB requires tables to have a primary key (MyISAM can have none). If not explicitly specified, the MySQL system automatically selects a column that can be non-null and uniquely identify a data record as the primary key. If no such column exists, MySQL automatically generates an implicit field for the InnoDB table as the primary key, which is six bytes long and of type long integer.

1.10 Cost of Indexing

Indexing is a good thing, but it can’t be built haphazardly; it consumes space and time:

• Spatial costs

Each index is created by building a B+ tree for it, and each node of the B+ tree is a data page.16KB of storage space, a very large B+ tree consists of many data pages, and that is a very large piece of storage space.

• Cost in time

Each time the data in the table isAdd, delete, change The operations all need to go and modify the individual B+ tree indexes. Moreover, as we have said, the nodes at each level of the B+ tree follow the values of the index columnsSort from smallest to largest And it’s made up ofbidirectional linked list . Both the records in the leaf nodes and the records in the inner nodes (i.e., whether they are user records or catalog item records) form a unidirectional chained table by following the values of the index columns from smallest to largest. And add, delete, and change operations may wreak havoc on the ordering of nodes and records, so the storage engine takes extra time to perform someRecord shifting, page splitting, page recycling and other operations to maintain a good ordering of nodes and records. If we build many indexes, each index corresponding to a B+ tree will have to perform the relevant maintenance operations, which will put a drag on performance.

The more indexes you build on a table, the more storage space it will take up, and the worse the performance will be when it comes to additions, deletions, and changes. In order to build good and fewer indexes, we have to learn the conditions under which these indexes work.

2. Principles of index creation and design

2.1 Declaration and Use of Indexes

2.1.1 Classification of indexes

MySQL indexes include general indexes, unique indexes, full-text indexes, single-column indexes, multi-column indexes, and spatial indexes.

• fromfunctional logic There are four main types of indexes: general indexes, unique indexes, primary key indexes, and full-text indexes.
• on the basis ofPhysical realization There are two types of indexes: clustered indexes and unclustered indexes.
• on the basis ofNumber of action fields The indexes are divided into single-column indexes and union indexes.

1. ordinary index

2. Unique Indexes

3. primary key index

4. single-column index

5. Multi-column (combined, union) indexes

6. full text index

7. Supplementary: spatial indexing

wrap-up: Different storage engines support different types of indexes
InnoDB: supports B-tree, Full-text and other indexes, does not support Hash indexes;
MyISAM: Supports B-tree, Full-text and other indexes, does not support hash indexes;
Memory: Support B-tree, Hash indexes, etc. Full-text indexes are not supported;
NDB: supports Hash indexes, does not support B-tree, Full-text and other indexes;
Archive : B-tree, Hash, Full-text indexes are not supported;

2.1.2 Creating indexes

2.1.2.1 Creating indexes when creating a table

CREATE TABLE dept(
  -- Automatic index creation for primary keys
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(20)
);
CREATE TABLE emp(
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  -- Automatic creation of unique indexes
  emp_name VARCHAR(20) UNIQUE,
  dept_id INT,
  -- Automatically create foreign key indexes
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

However, if the index is created when the table is explicitly created, the basic syntax format is as follows:

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

• UNIQUE, FULLTEXT, and SPATIAL are optional parameters that represent unique, full-text, and spatial indexes, respectively;
• INDEX and KEY are synonymous and both serve the same purpose of specifying the creation of an index;
• index_name Specifies the name of the index as an optional parameter. If not specified, then MySQL defaults to col_name for the index name;
• col_name is the field column for which the index needs to be created, which must be selected from multiple columns defined in the data table;
• length is an optional parameter that indicates the length of the index. Only fields of type string can specify the index length;
• ASC or DESC Specifies the index value to be stored in ascending or descending order.

1. Creating General Indexes
   Create a general index on the year_publication field in the book table with the following SQL statement:

CREATE TABLE book(
  book_id INT ,
  book_name VARCHAR(100),
  authors VARCHAR(100),
  info VARCHAR(100) ,
  comment VARCHAR(100),
  year_publication YEAR,
  -- declaration index
  INDEX index_bname(book_name)
);

2. Creating Unique Indexes

CREATE TABLE test1(
  id INT NOT NULL,
  name varchar(30) NOT NULL,
  -- Declare unique indexes
  UNIQUE INDEX uk_index_name(name)
);

After this statement executes, use SHOW CREATE TABLE to view the table structure:

SHOW INDEX FROM test1 \G

3. primary key index
   The database is automatically indexed when set as the primary key. innodb is a clustered index. syntax:

• Indexes are built along with the table:

CREATE TABLE student (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  student_no VARCHAR(200),
  student_name VARCHAR(200),
  PRIMARY KEY(id)
);

• Delete the primary key index:

ALTER TABLE student drop PRIMARY KEY ;

• Modify the primary key index: you must first delete (drop) the original index, and then create a new (add) index.

4. Creating Single-Column Indexes

CREATE TABLE test2(
  id INT NOT NULL,
  name CHAR(50) NULL,
  INDEX single_idx_name(name(20))
);

After this statement executes, use SHOW CREATE TABLE to view the table structure:

SHOW INDEX FROM test2 \G

5. Creating a Union Index
   Example: Create table test3 and build a combined index on the id, name and age fields in the table with the following SQL statement:

CREATE TABLE test3(
  id INT(11) NOT NULL,
  name CHAR(30) NOT NULL,
  age INT(11) NOT NULL,
  info VARCHAR(255),
  INDEX multi_idx(id,name,age)
);

attention (heed): The query should follow the leftmost prefix principle, otherwise the index cannot be in the name.

6. Creating Full-Text Indexes
   Example 1: Create table test4 and create a full-text index on the info field in the table with the following SQL statement:

CREATE TABLE test4(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;

You can leave the last ENGINE unspecified in MySQL 5.7 and later, because InnoDB supports full-text indexing in this version.

Example 2:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;

Example 3:

CREATE TABLE `papers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Queries that are different from the like approach:

SELECT * FROM papers WHERE content LIKE '% Query string %';

The full-text index is queried using match+against:

SELECT * FROM papers WHERE MATCH(title,content) AGAINST (' query string ');

Points to note.

1. Figure out version support before using full-text indexing;
2. Full-text indexing is N times faster than like + %, but may have precision issues;
3. If the need for full-text indexing is a large amount of data, it is recommended that you add the data first and then create the index.

7. Creating a spatial index
   The spatial index creation requires that the fields of the spatial type must benonempty (set) 。
   Example: Create table test5 and create a spatial index on the field with spatial type GEOMETRY with the following SQL statement:

CREATE TABLE test5(
  geo GEOMETRY NOT NULL,
  SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

2.1.2.2 Creating indexes on already existing tables

To create an index on an already existing table you can use the ALTER TABLE statement or the CREATE INDEX statement.

1. Creating an Index Using the ALTER TABLE Statement The basic syntax of the ALTER TABLE statement to create an index is as follows:

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...  [ASC | DESC])

2. Creating an Index with CREATE INDEXThe CREATE INDEX statement adds an index to a table that already exists in MySQL.
   CREATE INDEX is mapped to an ALTER TABLE statement with the basic syntax structure:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

2.1.3 Deleting indexes

1. Use ALTER TABLEDeleting an Index The basic syntax format for ALTER TABLE to delete an index is as follows:

ALTER TABLE table_name DROP INDEX index_name;

2. Use DROP INDEXThe basic syntax format for the DROP INDEX statement to delete an index is as follows:

DROP INDEX index_name ON table_name;

Tip. When you delete a column in a table, if the column to be deleted is a component of the index, the column is also deleted from the index. If the component
All columns of the index are deleted, the entire index will be deleted.