InnoDB Index concept

Author: skate
Time: 2013/04/09

 

Summarize and record the index concept of InnoDB for future reference

 

InnoDB Index category:
Cluster Index)
1) create a clustered index based on the primary key when a primary key exists.
2) if there is no primary key, a unique and not empty index column will be used as the primary key to become the clustered index of this table.
3) If none of the above two conditions are met, InnoDB creates a virtual clustered index.
Secondary Index)
Non-clustered indexes are secondary indexes, such as composite indexes, prefix indexes, and unique indexes.

 

MyISAM index: Because MyISAM indexes and data are stored separately, MyISAM uses key_buffer to cache the indexes in the memory. When data needs to be accessed (data accessed through indexes ), search directly in memory

Index, and then find the corresponding data on the disk through the index, which is why the index is slow when it is not hit by the key buffer.

InnoDB Index: Data and indexes of InnoDB are put together. When an index is found, the data is also found.

 

Adaptive hash index: InnoDB will monitor the index usage on the table. If we observe that creating a hash index can increase the speed, we will create a hash index, the self-adaptive hash index is constructed through the B + tree of the buffer pool,

As a result, the creation speed is very fast. You do not need to create a hash index for the entire table. the InnoDB Storage engine automatically creates a hash index for some pages based on the Access frequency and mode. Adaptive hash index not required

If the disk is stored, the content of the stopped database will be lost, and the database will be created by itself and indexes will be maintained slowly.

 

Clustered index:
MySQL InnoDB will certainly create a clustered index to store the actual data rows and related key values in one partition. This also determines that a table can have only one clustered index, that is, MySQL does not store data rows in two places at a time.

1) InnoDB generally performs clustering based on the primary key value (primary key ).
2) If no primary key is created, a unique and not empty index column will be used as the primary key to become the clustered index of the table.
3) If none of the preceding conditions is met, InnoDB creates a virtual clustered index.

 
Advantages:
The advantage of clustered index is to improve data access performance. Clustered indexes store both indexes and data in the same B + tree data structure, and store the index columns and related data rows together. This means that when you access different rows of records on the same data page, the page has been loaded into the buffer. When you access the page again, the access will be completed in the memory without accessing the disk. Different from the MyISAM engine, the index and data are not stored in one block and stored in different physical files. The index file is cached in key_buffer, and the index corresponds to the disk location, you have to access disk data through the disk location.

Disadvantages:
1) It is very expensive to maintain indexes, especially when a new row is inserted or the primary key is updated to the page split. We recommend that you use optimize table to optimize the table when a large number of new rows are inserted, because the row data that must be moved may cause fragmentation. Use exclusive tablespace to weaken fragments

2) because the table uses UUID as the primary key, data storage is sparse, which may cause the cluster index to be slower than the full table scan. Therefore, we recommend using auto_increment of int as the primary key.

3) if the primary key is large, the secondary index will be larger, because the leaf of the secondary index stores the primary key value; if the primary key value is too long, non-leaf nodes occupy more physical space.
 
Secondary Index
The index created on the clustered index is called a secondary index, and the secondary index always needs a secondary query to access data. Secondary index leaf nodes store primary key values instead of physical locations of rows. The secondary index first finds the primary key value, then finds the data leaf of the Data row through the primary key value, and then finds the data row through the page directory in the Data leaf.

Composite Index
An index created by multiple columns is called an index. The index is used only when the index's leading column must appear in the where condition.
Alter table 'test'. 'users' add index 'idx _ users_id_name '('name' (10) ASC, 'id' ASC );

 

Prefix Index
When the index string column is large, the index will become very large. To reduce the index volume and increase the index scanning speed, we will use the first part of the index string index, in this way, the index space will be greatly reduced, and the index selectivity will not be much lower. In addition, if you want to index blob and text columns or varchar columns that are very long, you must use a prefix index because MySQL does not allow indexing all their lengths.

Usage:
The length of a column prefix is very important. It also saves the index space and ensures that the prefix index selectivity is close to the full length of the index.

 

Unique Index
The unique index is better understood, that is, the index value must be unique, and the selection of such indexes is the best.

 

Primary Key Index
A primary key index is a unique index. However, a primary key index is created when a table is created, and a unique index can be created at any time.

Description
Differences between primary keys and unique Indexes
1) primary key is the primary key constraint + unique index
2) The primary key must contain a unique index, but the unique index is not the primary key.
3) null values are allowed for unique index columns, but null values are not allowed for primary key columns.
4) A table can have only one primary key, but multiple unique indexes.

 

Index scan method:
Compact index scan (dense index ):
In the first place, a right table scan was required to locate the data. In order to increase the scanning speed, the index key value was separately placed in a separate data block, in addition, each key value has a pointer to the original data block. Because the index is relatively small, the index scanning speed is faster than scanning the entire table, this method of scanning all key values is called compact index scanning.

 

Sparse index ):
In order to improve the efficiency of compact index scanning, by sorting indexes and searching algorithms (B + trre), we can find that we only need to match the first row of key values of each data block, you can determine the location or direction of the next data block. Therefore, valid data is the first row of data in each data block. If you create an index for the first row of data in each data block, in this way, you can perform a half-lookup on the newly created index, and the data location will be faster. This index scan method is called loose index scan.

 

Covering Index ):
Indexes that contain all the data that meets the query requirements are called overwrite indexes. That is, fields in the select list are returned using indexes instead of reading data files again based on indexes.

Common Index commands:
1) create a primary key
Create Table 'pk _ tab2 '(
'Id' int (11) not null auto_increment,
'A1' varchar (45) default null,
Primary Key ('id ')
) Engine = InnoDB default charset = utf8;

 

2) create a unique index
Create unique index indexname on tablename (columnname );
Alter table tablename add unique index indexname (columnname );

 

3) create a single column General Index
Create index indexname on tablename (columnname );
Alter table tablename add index indexname (columnname );

 

4) create a single column prefix Index
Create index indexname on tablename (columnname (10); // create a prefix index for the first 10 characters in a single column
Alter table tablename add index indexname (columnname (10); // create a prefix index for the first 10 characters in a single column

 

5) create a composite index
Create index indexname on tablename (columnname1, columnname2); // composite index of multiple columns
Create index indexname on tablename (columnname1, columnname2 (10); // composite index with prefix for multiple columns
Alter table tablename add index indexname (columnname1, columnname2); // composite index of multiple columns
Alter table tablename add index indexname (columnname1, columnname (10); // composite index with prefix for multiple columns

 

6) delete an index
Drop index indexname on tablename ;;
Alter table tablename drop index indexname;

 

7) view Indexes
Show index from tablename;
Show create table pk_tab2;

 

-------- End --------