Why index

Suppose there is a table , Table has 100 Ten thousand data , this 100 Ten thousand pieces of data are stored on the data page on the hard disk , The data size of one page is 16k. Storage 100 10000 pieces of data, then you need data pages , Suppose one of the data is “id by 7900” Of , So if you want to query this data , among SQL yes SELECT * FROM Table name WHERE id = 7900. In the execution of this SQL At the time of statement ,MySQL You need to scan the whole table to query id = 7900 The record of .
Full table scanning is from “ Data pages 1” Start , Backward page by page query . For a small amount of data , The query speed will be very fast , however , As the amount of data increases , Performance can degrade dramatically .100 The time for 10000 pieces of data to be queried page by page cannot be accepted by users .

What is the index

Index is help MySQL Efficient access to data data structure .
It's a data structure that is arranged in order and searched quickly .

An index is a table of contents similar to a Book .

The database is outside the storage data itself , And maintain a data structure that meets a specific search algorithm , These data structures point to data in some way , In this way, advanced search algorithms can be realized on the basis of these data structures , This data structure is Indexes .

The principle of indexing

The purpose of indexing is to improve query efficiency , It's the same reason as the catalogue we use to query books ： First, I'll go to chapter , Then go to the next section of the chapter , Then find the number of pages . Similar examples include ： consult a dictionary 、 Check the train number , Plane flight, etc .

It's all... In essence ： Filter out the final desired results by constantly narrowing the range of data you want to obtain , And turn random events into sequential events , in other words , With this indexing mechanism , We can always use the same search method to lock the data .

The index is similar to the table of contents of a book , Add a table of contents in front of a Book , When searching for content, you can quickly find the content you want to find without flipping page by page . With the help of the index , When executing a query, you can quickly find the required data without scanning the entire table .

advantage

The efficiency of indexing data is improved , Reduces database costs IO cost .

Sort data through index columns , Reduce the cost of data sorting , To reduce the CPU Consumption of .

shortcoming

actually , An index is essentially a table , This table holds the fields between the primary key and the index , And points to the record of the entity table , Therefore, index columns also occupy disk space .

Although the index greatly improves the speed of narration , But it also reduces the speed of updating table data . for example ：INSERT、UPDATE and DELETE, Because when updating tables ,MySQL Not only to save data , Save the index file again , Each update adds the field of the index column , Will adjust the index information after the key value changes due to the update .

The principle of index creation

The index is good , But you can't use it indiscriminately .

When index is needed

1. Primary key automatically creates unique index .
2. Fields that are frequently used as query criteria should be indexed （WHERE Subsequent statements ）.
3. Fields associated with other tables in the query , Index foreign key relationship .
4. Fields sorted in the query , If the sorting fields are accessed through the index, the sorting speed will be greatly improved .

When you don't need an index

1. Too few records in the table
2. Frequently added, deleted and modified tables . Although it has improved the speed of query , But at the same time, it will reduce the speed of updating the table , Such as on the table INSERT、UPDATE and DELETE, Because when updating tables ,MySQL Not only to save data , Also save the index file .（ So in this case , Will divide the table , Sub table is to read 、 Separate the written data ）
3. WHERE Fields that are not used in the condition will not be indexed
4. Duplicate and evenly distributed table fields , Therefore, only the most frequently queried and sorted data lists should be indexed , A data column contains many duplicates , Indexing doesn't have much practical effect .

Classification of indexes

primary key

After setting the primary key, the database will automatically establish an index , A table can only have one primary key .

ALTER TABLE Table name ADD PRIMARY KEY Table name （ Name ）;

Delete primary key index ：

ALTER TABLE Table name DROP PRIMARY KEY;

Single value index

An index contains only a single column , A table can have multiple single-column indexes .
Adding this index must be faster than not adding an index .

Create a single value index ：

CREATE INDEX Index name ON Table name （ Name ）;

Delete index ：

DROP INDEX Index name ;

unique index

The value of the index column must be unique , Allow for null;

CREATE UNIQUE INDEX Index name ON Table name （ Name ）;

Delete index ：

DROP INDEX Index name ON Table name ;

Composite index （ Composite index ）

That is, an index contains multiple columns , During database operations , The overhead of composite index is less than that of single value index （ For the same multiple column key indexes ）.

A composite index can be used when the number of rows in a table is much larger than the number of indexed columns .

Create composite index

CREATE INDEX Index name ON Table name （ Column 1, Column 2,…）;

Delete index

DROP INDEX Index name ON Table name ;

The leftmost prefix principle of Composite Index
For example, there are a,b,c 3 Column , by a,b Create a composite index with two columns , Then the leftmost index principle should be met when using . That is, when using the column of the composite index as the condition , Yes, the leftmost column must appear as a condition , Otherwise, the index will not take effect .

for example ：

SELECT * FROM Table name WHERE a = ' ' AND b = ' ' ; # The index works
SELECT * FROM Table name WHERE b = ' ' AND b = ' ' ; # The index works
SELECT * FROM Table name WHERE a = ' ' AND b = ' ' ; # The index works
SELECT * FORM Table name WHERE b = ' ' AND c = ' ' ; # Index does not work

Full-text index ( Only in MySQL8 After a )

It needs fuzzy query , General index is invalid , At this time, you can use full-text index .

such as ： WHERE name LIKE %J%, When you do this , Even if name The column is indexed , But it will also invalidate the index . Therefore, it is not recommended to use , stay MySQL8 It is suggested to use full-text index .

Create full-text index columns ：

CREATE FULLTEXT INDEX Index name ON Table name （ Name ） WITH TARSER ngram;

Use full text indexing ：

SELECT * FROM Table name WHERE MATCH( Name ) AGAINST(‘ Input parameters ’);

The insertion efficiency of full-text index is higher than that of LIKE To inquire about the faster , So in MySQL8 After that, it is suggested to use full-text index .

Search index ：

SHOW INDEX FROM Table name ;

The data structure of the index

stay MySQL In the database InnoDB The engine uses B+ Tree for data storage .

B+ The tree is a binary search tree 、 Balanced binary trees （AVLTree） And balanced multi drop search tree （B-Tree） Gradually optimized . Make it more suitable for the implementation of external memory index structure .

B+ Characteristics of number ：

1. It's in order , A node can store multiple data .
2. Non leaf nodes do not store data , Store index only , Yes, you can put more indexes .
3. Data is stored in leaf nodes .
4. There is a chain pointer between all leaf nodes .

Is the use of B+ The tree makes the tree expand horizontally , It reduces the height of the tree . Multiple data can also be stored in the leaf node .

MySQL So use B+ Trees , Because indexes are used to speed up queries , and B+ Number can improve the query speed by sorting the data , Then multiple elements can be stored through one node , So that B+ The height of the tree will not be too high . And there are pointers between leaf nodes , It can well support full table scanning , Scope search, etc SQL sentence .

Clustered index and non clustered index

Cluster index
When you find the index, you find the data you need , So this index is a cluster index , So the primary key is the clustered index .

Nonclustered index
The storage of indexes and data are separated , That means that the index is found but the data is not found , According to the value on the index （ Primary key ） Go back to the table again , A nonclustered index is also called a secondary index .

give an example ：
First create a table , It is used to illustrate that clustered indexes and non clustered indexes ：

CREATE TABLE student (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ' Primary key id',
  `name` VARCHAR (50) COMMENT ' The student's name ',
  `age` INT NOT NULL DEFAULT 0 COMMENT ' Student age ',
  KEY `idx_name` (`name`)
)

In the created database table , Primary key id It's an index , to age Added index .

① Get all field data directly according to the primary key query , At this point, the primary key is the cluster index . Because the index leaf node corresponding to the primary key stores id All field values for .

SELECT * FROM student WHERE id = 1;

② according to name Look up all the information ,name Itself is a unique index , But the query data includes all the data , So when you hit name When indexing , The data of the index node is stored in the primary key ID, And then you need to ID Check again .
This is the non clustered index .

SELECT * FROM student WHERE name = “ Zhang San ”;

Index is to create a separate table , Look up data in this table , And then to get ID Query all data in the table that returns all data .

③ according to name When querying , We only inquire about name Value , Don't query other information . This kind of query is hit name Indexes , Go straight back to name Value , Because the data needed is the index , There is no need to query the table at this time . This scenario is a non clustered index .

SELECT name FROM student WHERE name = “ Zhang San ”;

---

expand
stay MySQL in InnoDB The index and file of the engine are stored together , If you find the index, you can find the data , It's a clustered design .（ The default is cluster design , When you add other indexes , According to the change, it becomes non clustered ）

and MyISAM The engine is a non clustered design , Index file and data file are not in the same file .（ Create anyway , It is essentially a non clustered design ）

Because in MyISAM among , It's in MYD Data stored in the file ; stay MYI The index of the file stored in ; stay sdi The structure of the storage table in .