MySQL Clustered index && level two index && secondary index

MySQL non-clustered index && level two index && secondary index

Each table in MySQL has a clustered index (clustered index), except that each nonclustered index on the table is a level two index, also known as a secondary index (secondary indexes).

For InnoDB, each InnoDB table has a special index called a clustered index. If you have a primary key defined on your table, the primary key index is a clustered index. If you do not define a primary key for your table, MySQL takes the first unique index (unique) and contains only non-empty columns (not NULL) as the primary key, and InnoDB uses it as a clustered index. Without such a column, InnoDB itself produces an ID value that has six bytes and is hidden as the clustered index.

Cluster index and clustered index (Clustered index)

Speaking of the index, you cannot say B + tree.

Reference: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

The official MySQL definition of an index is: index is the data structure that helps MySQL to get data efficiently. By extracting the skeleton of a sentence, you can get the essence of the index: The index is the data structure.

We know that database query is one of the most important functions of database. We all want to query the data as fast as possible, so the designers of the database system are optimized from the point of view of the query algorithm. The most basic query algorithm, of course, is sequential lookup (linear search), the complexity of the O (n) algorithm is obviously bad when the volume of data is large, fortunately, the development of computer science provides a lot of better search algorithms, such as binary search (binary Search), two binary tree search (binary search) , etc. If you look at it a little bit, you will find that each lookup algorithm can only be applied to a particular data structure, such as a binary lookup requires an orderly retrieval of data, while a binary tree lookup can only be applied to a binary lookup tree, but the data itself cannot be fully organized to meet a variety of data structures (for example, It is theoretically impossible to organize both columns sequentially, so in addition to the data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

MySQL generally uses B+tree to implement its index structure.

A clustered index is not a separate index type, but a way of storing data. The specifics depend on how they are implemented, but the InnoDB clustered index actually holds the B-tree index and data rows in the same structure.

When a table has a clustered index, his data rows are actually stored in the leaf page of the index . the term "clustered" means that the data rows and adjacent key values are tightly stored together (this is not always true).

Because data rows cannot be stored in two different places at the same time, index a table can only have one clustered index.

Note: The leaf page contains the full tuple, while the Inner node page contains only the indexed columns (indexed column integers). Some DBMS allow users to specify clustered indexes, but MySQL's storage engine is not supported so far. InnoDB the clustered index on the primary key. If you do not specify a primary key, InnoDB replaces it with an index that has a unique and non-null value. If such an index does not exist, InnoDB defines a hidden primary key and then establishes a clustered index on it. In general, the DBMS stores the actual data in the form of a clustered index, which is the basis for other two-level indexes.

Index Organization table (Index organized table, IOT)

In fact, and the clustered index is said to be a meaning.

An indexed organization table (Index organized table, IOT) is a table that is stored in an indexed structure. Unlike heap organization table unordered storage,data in the IoT is stored and sorted by primary key.

The Index organization table saves a fraction of the space compared to the heap organization table, because when you use the heap to organize tables, we must make room for the indexes on the primary keys of the table and table. The IoT can eliminate the overhead of primary key indexing because the data is stored sequentially and can be indexed. In other words, if you only access the table through the primary key of a table, this table is suitable for creating indexed organization tables.

Example:

1. A customer has a lot of address information, the customer is a table, the customer address information is another table. When reading a customer address information, if all the address information of this customer is stored in the adjacent place, the reading speed will be faster. At this point, the Customer Address information table is ideal for creating IoT.

2. Frequently check the information of a stock in recent days, stock information is generally tens other data, if you can put the information in recent days together will be much faster.

=========end=========

The following are either two-level indexes, or secondary indexes, except the primary key.

> Show CREATE TABLE article***************1. Row***************Table:articlecreate table:create Table' Article ' (' id ' int (one) not NULL auto_increment, ' title '  varchar (255)  not null,   ShortName '  varchar (255)  not null,   ' authorId '  int (11)  not null,   ' createtime '  datetime NOT NULL,    ' state '  int (one)  not null,   Totalview '  int (one)  DEFAULT NULL,  PRIMARY KEY  ( ' ID ' ),   unique key  ' Idx_short_name_title '   (  ' title ',  ' shortname '),  key   ' idx_author_id '   ( ' Authorid '))  ENGINE=InnoDB  Auto_increment=6 default charset=latin11 rows in set     

=======end=======

It's a little messy.

MySQL Clustered index && level two index && secondary index