MySQL Index

Index introduction

What is the index

  • The database index is equivalent to the catalog of a book, which can speed up the search query speed
  • Generally speaking, the index itself is also very large, it is impossible to store all in memory, so the index is often stored in a file on the disk
  • Index: Including clustered index, covering index, composite index, prefix index, unique index, etc. The default index is organized by B+ tree structure

Advantages and disadvantages of indexing

Advantage:

  • It can improve the efficiency of data retrieval and reduce the IO cost of the database, similar to the list of books -retrieval
  • Sorting data through index columns reduces the cost of data sorting and reduces CPU consumption -sorting
    indexed columns will be automatically sorted, including [single column index] and [combined index], but the sorting of composite index requires A little more complicated.
    If you sort according to the order of the index columns, corresponding to the order by statement, the efficiency will be improved a lot.
    Where index columns are processed in the storage engine layer
    .

Disadvantages:

  • Indexes take up disk space
  • Although the index will improve the query efficiency, but it will reduce the efficiency of updating the table. As with each addition, deletion, and modification of a table, MySQL not
    only saves the data, but also saves or updates the corresponding index file

Index classification

Single-column index

  • Ordinary index: There are no restrictions on the basic index type in MySQL. It is allowed to insert duplicate values ​​and null values ​​in the columns that define the index, purely for querying data faster. add index
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;	
  • Unique index: The value in the index column must be unique, but null values ​​are allowed. add unique index
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column);
  • Primary key index: It is a special only index, no null value is allowed. pk

Composite index

An index created on a combination of multiple fields in the table. dd index(col1,col2…)

The use of the composite index needs to follow the principle of the leftmost prefix. (Leftmost matching principle)

In general, it is recommended to use a composite index instead of a single-column index. (Except for the primary key index)

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

Full index

  • It can only be used on the MyISAM engine and InnoDB (after 5.6), and can only be used on CHAR, VARCHAR, and TEXT type fields. fulltext
  • The highest priority will be executed first, no other indexes will be executed
  • The storage engine decides to perform an index
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
ALTER TABLE  table_name ADD FULLTEXT index_name(column);

Index other operations

  • Delete index
DROP INDEX index_name ON table
  • View index
SHOW INDEX FROM table_name \G

Analysis of Indexing Principle

Index storage structure

  • The index is implemented in the storage engine, which means that different storage engines will use different indexes
  • MyISAM and InnoDB storage engines: only support B+ TREE index, which means BTREE is used by default and cannot be replaced
  • MEMORY/HEAP storage engine: supports HASH and BTREE index

B tree and B+ tree

Example of B+ Tree structure.
B-tree icon of website
B-tree is a kind of multi-fork (compared to two-fork, each inner node of B-tree has multiple branches, that is, multi-fork) balance. Find the tree. Multi-pronged balance

Insert picture description here

B+ tree icon

Insert picture description here
  • The height of the B-tree is generally at a height of 2-4, and the height of the tree directly affects the number of IO reads and writes
  • If it is a three-level tree structure-the supported data can reach 20G, if it is a four-level tree structure-the supported data can reach 10,000 T

The difference between B tree and B+ tree

  • B-trees are non-leaf subnodes and leaf subnodes will store data
  • Only the leaf nodes of the B+ tree store data, and the stored data are all on one line, and these data are all pointed to by pointers, that is, in order. Index column order by

Non-clustered index (MyISAM)

  • The B+ leaf sub-node will only store pointers to data lines (data files). Simply put, the data and the index are not at the same time, which is a non-clustered index
  • The non-clustered index contains the primary key index and the auxiliary index will store the value of the pointer

Primary key index

Insert picture description here

This table has three columns. Assuming that we use Col1 as the primary key, the above figure is a primary key of the MyISAM table. It
can be seen that the MyISAM index file only saves the address of the data record.

Secondary index (secondary index)

In MyISAM, there is no difference in structure between the primary index and the secondary index (Secondary key), but the primary index requires the key to be unique, and the key of the secondary index can be repeated

It is also a B+Tree, the address where the data field saves the data record . Therefore, the index retrieval algorithm in MyISAM is to first search the index according to the B+Tree search algorithm. If the specified Key exists, then take out the value of its data field, and then use the value of the data field as the address to read the corresponding data record.

Clustered index (InnoDB)

  • The leaf nodes of the primary key index (clustered index) store data rows, which means that the data and the index are at the same time. This is the clustered index
  • Auxiliary index will only store the primary key value
  • If there is no primary key, use a unique index to create a clustered index; if there is no unique index, MySQL will create a clustered index according to certain rules

Primary key index

  • InnoDB requires that the table must have a primary key (MyISAM may not have it).If it is not explicitly specified, the MySQL system will automatically select a column that can uniquely identify the data record as the primary key. If such a column does not exist, MySQL will automatically be InnoDB expresses a hidden field as the primary key, and the type is integer shaping.
Insert picture description here


Schematic diagram of InnoDB main index (also data file), you can see that the leaf node contains complete data records. This kind of index is called a clustered index. Because InnoDB's data files themselves need to be aggregated by the primary key

Secondary index (secondary index)

  • The second difference from MyISAM index is that InnoDB's secondary index data field stores the value of the primary key of the corresponding record instead of the address. In other words, all of InnoDB's secondary indexes refer to the primary key as the data field
  • The implementation of the clustered index makes the search by the primary key effective, but the secondary index search needs to retrieve the index twice: first retrieve the secondary index to obtain the primary key, and then use the primary key to retrieve the records from the primary index.
select * from user where name='Alice'   --回表查询 检索两次 ⾮主键索引 --- pk---索引--->数据
select id,name from user where name='Alice'  --不需要回表 在辅助索引树上就可以查询到 覆盖索引(多⽤组合索引)

Why is it not recommended to use the used field as the primary key?

  • Because all auxiliary indexes refer to the main index, an excessive main index will make the auxiliary index too large
  • At the same time, please try to use self-incremented fields as the primary key of the table on InnoDB

Other knowledge points

What situations need to create an index

  1. The main key automatically builds the only index
  2. Fields frequently used as query conditions should be indexed
  3. In multi-table related queries, indexes should be created for related fields on both sides.
  4. The sorted fields in the query should be indexed
  5. Frequently look up field coverage index
  6. For statistical or grouping fields in the query, an index group by should be created

What situations do not need to create an index

  1. Too few table records
  2. Tables that are frequently added, deleted, and modified
  3. Frequently updated fields
  4. where conditions are not frequently used fields

Why use a combined index

The rule for mysql to create a composite index is to sort the data of the leftmost part of the composite index, that is, the first name field, and then sort the second field based on the sorting of the first field. The cid field is sorted. In fact, it is equivalent to implementing a sorting rule like order by name cid

In order to save mysql index storage space and improve search performance, a composite index can be built ( combined index can be used instead of single-column index )

E.g:

Create a composite index (equivalent to the establishment of col1, col1 col2, col1 col2 col3 three indexes):

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')

Create 3 indexes on one index tree: Save space

Use the principle of the leftmost prefix

1. Prefix index like a%
2. Match from left to right until a range query is encountered> <between like

The composite index (a, b, c, d)
where a=1 and b=1 and c>3 and d=1
to c>3 is created, so the index is not available for d.
What should I do?
Change the index order: (a,b,d,c)