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

Source: GitHub·Click here|| GitEE·Click here

1. Introduction to index 1. Basic concepts

Index is a data structure, data structure is the computer storage, organization of data, refers to the existence of one or more specific relationships between the collection of data elements, such as: linked list, stack, queue, binary tree and so on.

Second, we must be clear about the role of the index: the index can make the storage engine quickly find data records, which is the most basic role, the index is the most critical impact on the query speed, good index design can make the query efficiency qualitative flying.

Index usage: MySQL queries the indexed data structure if the query statement uses all, and returns the data row containing the index if the query finds it.

2, the advantages of the index unique or primary key index, to ensure the uniqueness of column data to reduce the amount of data scanning, fast query data; data ordered index, you can turn random IO into sequential IO; effective index query, you can avoid sorting and temporary tables;3, index classification

There are many kinds of indexes. How to classify them depends on many scenes and different angles. The common classifications are as follows:

Function: primary key index, ordinary index, non-empty index, full-text index; coverage field: single column index, composite index; data structure: B-Tree index, hash index, R-Tree index;

Note: The implementation of the index is at the storage engine level. The same index may be implemented differently in different storage engines.

II. Detailed explanation of index usage 1. Characteristics of different indexes

general index

A basic index, with no usage restrictions, primarily used to speed up data queries. Suitable for data columns that often appear in query or sort conditions.

primary key index

Special unique index, no null value allowed, specify the primary key when building the table, the primary key index will be created, the core index in MySQL, a large number of business data are based on the primary key query.

unique index

Ordinary indexes are similar, except that index columns must have unique values, but null values are allowed. If a composite index, the combination of column values must be unique.

full-text index

For full-text search, through the establishment of full-text index, query mode based on word segmentation, can greatly improve the efficiency of retrieval.

combinatorial index

The index created covers two or more columns, which is suitable for combined query scenarios and is also commonly used for element verification services, such as determining whether user ID, mobile phone number and mailbox are the same user.

2. Manage index syntax

base user table

CREATE TABLE user_base ( id INT (11) NOT NULL AUTO_INCREMENT COMMENT 'MAIN KEY ID', user_name VARCHAR (20) NOT NULL COMMENT 'username', VARphone CHAR (20) NOT NULL COMMENT 'mobile number', email VARCHAR (32) DEFAULT NULL COMMENT 'Mailbox', card_id VARCHAR (32) DEFAULT NULL COMMENT 'ID number', create_time datetime DEFAULT COMMENT 'create time', NULL state INT (1) DEFAULT '1' COMMENT 'is available, 0-unavailable, 1-available', PRIMARY KEY (`id`) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'User Base Table';

Create a single-column index

CREATE INDEX card_id_index ON user_base(card_id);

Modify Add Index

ALTER TABLE user_base ADD INDEX state_index(state) ;

Create composite index

CREATE INDEX bind_index ON user_base(phone,card_id);

delete the index

DROP INDEX card_id_index ON user_base ;

modify the index

MySQL does not support syntax specifications for actually modifying indexes, which can be done by deleting old indexes and adding new ones.

3. Query index

Analyze MySQL queries, most of which are used to analyze whether indexes are used in SQL execution statements, and whether performance-related issues such as temporary tables are generated.

Basic Usage

EXPLAIN SELECT * FROM user_base WHERE id='1';

parameter description

id： Same, executed from top to bottom according to table column, different, if it is a subquery, id sequence number will increase, the higher the id value, the higher the priority, the first to be executed;select_type: indicates the type of query, mainly used to distinguish ordinary queries, joint queries, subqueries, etc. complex queries;simple: simple query, query does not contain subqueries or primary: query contains complex subparts, the outermost query is marked as primarysubquery: select or where contains subqueries derived: The subqueries contained in from are marked as derived, mysql recursively executes these subqueries, and generates temporary tables union: the second select appears after union, marked unionunion-result: selectable to get results from union table: refers to the data table in the current execution plan;type: describes which type the query uses, sorted from good to bad;system-const: when optimizing a part of the query and converting it to a constant, the type eq_ref: index scan, which returns all rows matching a single value index scan, which traverses the index structure, index files are usually smaller than data files all, which traverses the entire table to query possible_keys, indexes that may be used in the query key, indexes that are actually used in the query key_len, the maximum possible length of an index field in the query, the shorter the better without loss of precision ref A reference object indicating the object to be operated on in this row. It may be a constant expressed by const, or an object pointed to by the key of another table. rows: estimate the number of rows to be scanned to find the records that meet the requirements, and the fewer the scans, the better. extra: some very important information in the execution plan. Using-Where: indicates that the filter conditionUsing-Join-Buffer: indicates that the join cache is usedImpossible-Where: indicates that the filter conditionUsing-Join-Buffer: indicates that the filter conditionUsing-

MySQL official comparison recommended index structure type, in the actual database development, based on MySQL table structure, most of the use of B-Three index structure, namely binary tree structure. The data access speed can be accelerated. The storage engine no longer needs to scan the whole table to obtain the data. The data is distributed on each index node. The B-Tree index structure is as shown in the figure:

The structure is a typical binary tree structure, characteristics: data values are stored in order, each leaf node to the root distance is the same, note that the description here is the index structure graph.

In the actual storage structure, the data is stored sequentially. Each node contains the index value, the value of the data row pointed to by the index, the pointer to the child page, and the pointer to the leaf page. In this way, the index and the data structure can be organized. The structure is as shown in the figure:

This completely describes the data characteristics of B-Tree index, improves the efficiency of tree-based search, reduces the number of scanned data, and organizes the data sequentially according to the order of index values.

2. Search rules

The fundamental role of index is to reduce the amount of data scanned and improve query efficiency. The query rules based on the structure of B-Tree index are basically as follows:

The query starts from the root node of the index and searches step by step; the slot of the root node stores pointers pointing to child nodes and pointing to lower layers; according to the comparison between the value of the node page and the query value, judge whether the condition is met; and continuously execute the logic until the query is completed.

Note: It must be emphasized that the query must be based on the execution of the index, which is the logic. In normal development, analyze the query statement more. Sometimes you may just feel that the query index is executed, but it may actually be invalid.

3. Index query invalid

Good index design is very important, but it is very likely that SQL statements will not perform index search due to triggering various index failure mechanisms during query, resulting in serious loss of performance. Therefore, based on the characteristics of data query under business, it is very important to design a relatively easy index structure. Many scenarios are involved here, and detailed records will be made later.

IV. Problems Caused by Indexing

Index is sometimes not the best solution. When the data volume is huge, index will also occupy a large storage space. Here is a business test scenario, only parameters: three character type fields in a single table, two fields using index structure, storing data in the order of 700W. In two databases A and B, database A has index structure, database B has no index, database A occupies 1.6 times the space of database B, and the writing speed of ten million data is 9 minutes slower than database B.

Here just want to make a point: although the index is good, use properly to play a role.

V. Source code address GitHub·Address https://github.com/cicadasmile/mysql-data-base GitEE·Address https://gitee.com/cicadasmile/mysql-data-base

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.