Shulou(Shulou.com)06/01 Report--

Clustered index (InnoDB, using B+Tree as the index structure)

B-tree indexes and data rows are saved in a structure; stored on leaf pages in the order of primary keys

Primary key index: leaf node stores key-value as (primary key data: all remaining column data)

Secondary index (non-clustered index): leaf node stores key-value as (index column data: primary key data)

Non-leaf nodes store only index columns

Advantages:

You can save the relevant data together, such as aggregating email information according to the user's id, and you only need to read a few data pages to get all the emails of an id user.

Faster data access, saving indexes and data in the same b-tree

Queries that use override index scanning can directly use the primary key values in the leaf node

Disadvantages:

The insertion speed depends heavily on the insertion order, and inserting in the order of primary keys is the fastest way to load data into the innodb table.

Inserting a new row may face the problem of page splitting, which causes the table to take up more disk space

Two searches are needed through the secondary index, and the storage engine finds the leaf node of the secondary index to get the corresponding primary key value, and then clusters the corresponding rows in the index according to this value.

Primary key:

If the table does not have any data to be aggregated (such as the mail user id above), you can define a surrogate key as the primary key and use auto_increment to augment the column

Nonclustered index (MyISAM uses B+Tree as the index structure)

It is stored on disk according to the order of data insertion, and a system call is required to access the data.

Primary key index / secondary index: leaf node storage (index column data: the row number of the data on disk)

Contrast:

InnoDB provides transaction support for transactions, foreign keys and other functions; MyISAM does not support it.

InnoDB supports row-level locks; MyISAM only supports table-level locks

InnoDB requires that there must be a primary key; MyISAM allows a table to exist without any indexes and primary keys, and the index is the address where the rows are saved.

Overlay index

An index contains (or overrides) the values of all the fields to be queried

Override index can only use b-tree index to store the value of index column (hash index, full-text index, etc.)

Advantages

1. The MyISAM storage engine only stores indexes in memory, and overwriting indexes does not require system calls.

2. The clustering index mechanism of innodb storage engine. If the secondary primary key can override the query, the secondary query of the primary key index can be avoided.

Full-text index

You want to filter queries through keyword matching, rather than through regular numerical comparisons and range filtering operations

MyISAM's full-text index is a special kind of B-Tree index, which has two layers. The first layer is all keywords. For the second layer of each keyword, it contains a set of related "document pointers".

Filter criteria for all words in the document object:

1. None of the words in the stop word list will be indexed.

two。 Words whose length is greater than and smaller than the specified range will not be indexed.

In addition, the full-text index does not store the exact column in which the keyword matches.

1. Natural language full-text index

Distinguish whether to use full-text index according to the MATCH AGAINST in the where clause

Eg. Establish a full-text index of fulltext on the field title,description of table file_text

Select film_id,title,right (description,25)

Match (title,description) against ('factory casualties') as relevance

From file_text where Match (title,description) against ('factory casualties')

Results:

Film_id title right (description,25) relevance

831 spirited casualties a car is a baloon factory 8.4692449702

126 casualties encino face a boy in a monastery 5.2615661621

......

The function match () returns the matching relevance of the keyword, which is a floating point number.

two。 Boolean full-text index

Users can customize the relevance of the words to be searched; users can customize the search through some premodifiers:

Example meaning

The row rank value of Dinosaur containing dinosaur is higher

-dinosaur rows that contain dinosaur have lower rank values

+ dinosaur line records must contain dinosaur

-dinosaur row records cannot contain dinosaur

Dino* lines that contain words that begin with dino have a higher rank value

Eg. Select film_id,title,right (description,25)

From file_text where Match (title,description) against ('+ factory + casualties' in boolean mood)

Results:

Film_id title right (description,25)

831 spirited casualties a car is a baloon factory

When search keywords are uncommon words, they are much faster than LIKE operations, because records are filtered directly from the index.

Restrictions on full-text indexing:

1. There is only one way to judge the relevance of mysql's full-text index: word frequency. There are no other correlation sorting algorithms, such as the location of storage.

two。 Only when the full-text index is all in memory can the performance be very good.

3. Other where conditions can only be carried out after mysql completes the full-text search and returns a record.

4. A full-text index cannot store the actual value of a column and cannot be used as an override scan

5. Cannot be used as a sort other than correlation sort

Configuration and optimization

1. Deactivate the word list

two。 Allow minimum word length

Find the right balance between the accuracy and efficiency of the search.

Indexes and locks

Indexes allow queries to lock fewer rows, and innodb locks rows only when they are accessed, while indexes reduce the number of rows accessed by innodb, thereby reducing the number of locks

However, innodb is effective only if it is able to filter unwanted rows at the storage engine layer. If it cannot be filtered, then the where statement can be used to filter the data in innodb and returned to the server layer, and innodb has locked these rows until the lock is released after the server layer filtering is complete.

For example: select actor_id from sakila.actor where actor_id < 5 (range) and actor_id 1 (filter) for update

Execute the explain command to show that type is range, indicating that the execution plan selected by mysql for the query is an index range query, that is, only the condition of actor_id < 5 is executed in the storage engine layer, and the query results are: 2Jing 3Jing 4; while the locked data rows: 1Jing 2Jing 3Jing 4

Even if you use an index, it is possible to lock some unwanted rows, but without index lookup, mysql scans the entire table and locks all rows.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.