The index is introduced

What is the index

  • Database index is equivalent to a book table of contents, which can speed up the search speed
  • Indexes themselves are generally too large to be stored in memory, so they are often stored in files on disk
  • Index: includes clustered index, overwrite index, composite index, prefix index, unique index, etc. By default, all indexes are organized in B+ tree structure

Advantages and disadvantages of indexes

Advantage:

  • Can improve the efficiency of data retrieval, reduce the IO cost of database, similar to the book catalog – retrieval
  • Sorting data by index columns reduces the cost of sorting data and CPU consumption. The sorted columns are automatically sorted, including single-column indexes and composite indexes, but the sorting of composite indexes is more complicated

If the order of index columns is sorted, the efficiency of the ORDER BY statement is improved by many WHERE index columns that are overridden at the storage engine layer, without the need to go back to the table to query

Disadvantage:

  • Indexes take up disk space
  • Indexes improve query efficiency, but reduce the efficiency of updating tables. For example, MySQL does not add, delete, or alter a table

Save only the data, and save or update the corresponding index file

Classification of indexes

Single index

  • Common index:The basic index type in MySQL, with no restrictions, allows you to insert duplicate and null values in the column where the index is defined, purely for the sake of querying data faster. add index 
    CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;	
Copy the code
  • Unique index:Values in index columns 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);
Copy the code
  • Primary key index: a special unique index that does not allow null values. pk

Composite index

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

  • The use of composite indexes must follow the left-most prefix principle. (Left-most matching principle)

  • In general, it is recommended to use composite indexes instead of single-column indexes. (except for primary key indexes)

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

The full text indexing

  • Only on MyISAM, InnoDB (after 5.6), and only on CHAR,VARCHAR,TEXT fields. fulltext
  • Indexes with the highest priority are executed first and no other indexes are 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);
Copy the code

Index other operations

  • Remove the index 
    DROP INDEX index_name ON table
Copy the code
  • View index 
    SHOW INDEX FROM table_name \G
Copy the code

Analysis of index Principle

Index storage structure

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

B tree and B plus tree

B+ Tree structure sample site B tree hereA B-tree is a multi-fork balanced lookup tree designed for disks or other storage devices. Many forks balance

B + tree here

  • The height of a B tree is usually 2-4. The height of a B tree directly affects the I/O read and write times
  • If it is a three-layer tree structure, the data supported can reach 20GB, if it is a four-layer tree structure, the data supported can reach tens of terabytes

B tree and B+ tree

  • B tree both non-leaf and leaf nodes store data
  • Only leaf nodes of a B+ tree store data, and the data is stored on a row, and the data is pointed to, that is, in order. Index column order by

Non-clustered index (MyISAM)

  • B+ tree leaf nodes only store Pointers to rows of data (data files). In simple terms, data and index are not together, which is a non-clustered index
  • Both non-clustered indexes that contain primary keys and secondary indexes store the value of the pointer

The primary key index

MyISAM Primary key (Col1); MyISAM Primary key (Col1); MyISAM Primary key

Secondary index (secondary index)

In MyISAM, there is no difference in structure between primary and Secondary keys, except that the primary index requires a unique key, while Secondary keys can be repeated

Also a B+Tree,data field holds the address of the data record. Therefore, the index retrieval algorithm in MyISAM is to search the index according to THE B+Tree search algorithm. If the specified Key exists, the value of its data field is extracted, and then the corresponding data record is read with the value of the data field as the address.

Clustered Index (InnoDB)

  • The leaf node of a primary key index (clustered index) stores rows of data, meaning that the data and index are together, which is called a clustered index
  • Secondary indexes only store primary key values
  • If there is no primary key, a unique index is used to create a clustered index. If there is no unique index, MySQL creates clustered indexes according to certain rules

The primary key index

  • InnoDB requires tables to have primary keys (MyISAM can’t). If not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key. If no such column exists, MySQL automatically generates an implied field for the InnoDB table as the primary key, of type long integer.

A diagram of the Main index (which is also a data file) of InnoDB shows that the leaf node contains the complete data record. This kind of index is called a clustered index. Because InnoDB’s data files themselves are 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 rather than the address. In other words, all InnoDB secondary indexes refer to the primary key as the data field
  • Clustered index implementations make searching by primary key very efficient, but secondary index searches require retrieving the index twice: first, retrieving the secondary index for the primary key, and then using the primary key to retrieve records from the primary index. 
    select * from user where name='Alice'   -- Back table query retrieves non-primary key index -- pk-- index --> data twice
select id,name from user where name='Alice'  Select * from secondary index tree; select * from secondary index tree;
Copy the code

Why not recommend using too long fields as primary keys?

  • Because all secondary indexes reference the primary index, an excessively long primary index can make the secondary index too large
  • At the same time, try to use auto-increment fields as primary keys for tables in InnoDB

Other knowledge points

In which case you need to create an index

  1. The primary key automatically creates a unique index
  2. Fields that are frequently queried should be indexed
  3. In a multi-table associative query, the associated field should be indexed
  4. The sorted fields in the query should be indexed
  5. Frequent lookup field coverage index
  6. Index Group by should be created for statistics or grouped fields in the query

Which cases do not need to create indexes

  1. Too few table records
  2. A table that is frequently added, deleted, or modified
  3. Fields that are frequently updated
  4. Infrequently used fields in the WHERE condition

Why use composite indexes

Select * from cid (select * from cid); select * from CID (select * from CID); select * from CID (select * from CID); You can implement a sort like order by Name CID

Mysql > create index (s); create index (s); create index (s);

Such as:

Create index (col1, COL1, col2,col1, col2, col3);

ALTER TABLE 'table_name' ADD INDEX index_name('col1'.'col2'.'col3')Copy the code

Create three indexes on an index tree to save space

Use the left-most prefix rule

1, prefix index like a% 2, left to right until range > < between like

Select * from (a,b,c,d) where a=1 and b=1 and C >3 and d=1 and c>3 Change index order :(a,b,d,c)