Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use MySQL index structure and query reasonably". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the idea of Xiaobian slowly and deeply to study and learn "how to use MySQL index structure and query reasonably" together!

1. High performance index 1. Query performance problems

In the process of using MySQL, the so-called performance problem refers to the performance of the query in most scenarios. The root cause of the slow query is the increasing amount of data. The most common means to solve the query performance is to design a reasonable index structure for the query business scenario.

2. Principles of index use

The use of index is not the more the better, but for the query scenario under the business, continuous improvement and optimization, such as the user order scenario in the e-commerce system, assuming that there is the following table structure:

CREATE TABLE `ds_user`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'MAIN KEY id', `user_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT ='USER TABLE'; CREATE TABLE `ds_order`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primaryid',`user_id` int(11) NOT NULL COMMENT ' userID',`order_no` varchar(60) NOT NULL COMMENT 'ordernumber',`product_name` varchar(50) DEFAULT NULL COMMENT 'Product name',`number` int(11) DEFAULT '1' COMMENT ' number',`unit_price` decimal(10,2) DEFAULT '0.00' COMMENT 'unit',`total_price` decimal(10,2) DEFAULT '0.00' COMMENT ' total',`order_state` int(2) DEFAULT '1' COMMENT '1 To be paid, 2 paid, 3 shipped, 4 signed',`order_remark` varchar(50) DEFAULT NULL COMMENT 'order note',`create_time` datetime DEFAULT NULL COMMENT ' create time', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT ='order table';

The user and order management table is very common in e-commerce business. You can analyze this business and see the commonly used index structure:

User side:

user-based queries, mostly based on user ID(user_id);

View logistics information based on order number (order_no);

Operator:

Create_time or sort based on time period;

filtering (order_state) and statistics based on order status;

statistical analysis of data based on product_name;

Such a process analysis goes down, that is, in the early stage of development, determine which structures must be used for queries, and prepare index structures in advance to avoid using indexes when the amount of data is large enough to affect performance.

Sometimes, we will consider giving up some query conditions, such as data statistics based on product names, and take the way of timed tasks to relieve the query pressure of tables. The processing methods are various.

Excellent index design is based on the understanding of business data, considering the query method of business data and improving query efficiency.

2. Index creation 1. Single column index

Single column index, that is, the index is established on one field of the table. A table can have multiple single column indexes, which is relatively simple to use:

CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;

The primary index, or user_id_index above, is a single column index.

Business scenario: Based on the user's own order query, and the management system, the order and the user's associated query, so the user_id of the order table needs an index.

2. Combined index

A composite index contains two or more columns. Compared with a single column index, a composite index is much more complex. How to establish a composite index has a high degree of relevance to the business. When using a composite index, you also need to consider the order of query conditions.

CREATE INDEX state_create_time_index ON `ds_order`(`create_time`,`order_state`);

The above is the combination index, which actually contains 2 indexes (create_time) (create_time,order_state), so the query involves the principle of the leftmost prefix, which must be queried in order, which is described in detail below.

Business scenarios: First of all, let's talk about the combination index here. In business development, statistics of common order status are performed, and operation analysis is performed based on statistical results. In addition, in the operation system, filtering conditions based on creation time period exist by default to avoid real-time scanning of all data. Some other common queries are also query modes of condition plus time period.

3. Prefix index

If the column to be indexed is a long string, the index becomes bloated and the effect may not be obvious. At this time, you can intercept the front part of the column and create an index to save space. In this way, the selectivity of the index may decrease, that is, the similar data queried based on the prefix index may be many:

ALTER TABLE ds_order ADD KEY (order_no(30)) ;

Here, because the order number is too long, the first 30 digits are selected as the prefix index, which is used as the query of the order number. Of course, a very classic business scenario is involved here, the order number mechanism.

Business Scenario: Prefix Index A typical application scenario is to process an order number. A seemingly long order number actually contains a lot of information:

Time point: is the order generation time, month, day, hour, minute, second;

Identification bit: namely a unique UID to ensure that the order number is unique;

Buried Point 1: In many businesses, product categories are recorded in the order number;

Buried Point 2: Usually identifies product attributes, such as color, taste, etc.;

Misplacement symbol: prevent order number from being analyzed, a random misplacement symbol;

After such an analysis, the actual order number is very long, so it is necessary to introduce a prefix index mechanism. The index length expected to be used by the prefix index can filter the cardinality of the entire column, for example, the order number above:

Most services are sufficiently filtered based on time nodes, i.e. the index length is 14 bits;

If it is concurrent service and many time nodes are the same, the index length is time point + identification bit;

Note: If the service allows, the prefix index length is generally required to be unique, such as the time and flag bits above.

4. Other indexes

For example, full-text indexing, etc., these scenarios are not used much, if the data is huge, and need to be retrieved, etc., usually choose a powerful search middleware to handle. Explicit unique indexing, which is also circumvented on the program to avoid unfriendly exceptions being thrown.

III. Index query

How to create an optimal index is not easy. Similarly, when querying, whether to use an index is also a very difficult thing. Experience: Most of the time when performance problems are exposed, they will look back at the SQL statements of the query and do corresponding query optimization for performance problems.

1. Single query

Here you can query the primary key index directly. MySQL's primary key is generally self-increasing, so the speed is very fast.

EXPLAIN SELECT * FROM ds_order WHERE id=2;EXPLAIN SELECT * FROM ds_order WHERE id=1+1;EXPLAIN SELECT * FROM ds_order WHERE id+1=1;

Here, id=2,id=1+1, MySQL can be automatically resolved, but id+1 is to perform operations on the index column, directly causing the primary key index to fail. Here is a basic strategy, if you have to do operations on a single column index, you can put the logic in the program, to MySQL level, SQL statements as clean as possible.

2. Prefix index query

Prefix index queries can be filtered based on Like for a specific length, or full order number queries.

EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%'; EXPLAIN SELECT * FROM ds_order WHERE order_no=' 20200801131415872362873287162572367'; 3. Combined index query

The most troublesome part of the query is the combination index, or the combination of query conditions, all using the index:

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00' AND order_state='1';

The above uses a composite index based on the order of columns in the composite index: state_create_time_index.

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00';

The above uses only the create_time column, and also uses the index structure.

EXPLAIN SELECT * FROM ds_order WHERE order_state='1';

If only the order_state condition is used above, the result shows a full table scan.

EXPLAIN SELECT * FROM ds_order WHERE create_time>'2020-08-01 00:00:00' AND order_no LIKE '20200801%';

The above is based on the create_time column of the composite index and the single-column index order_no to ensure that the query condition uses the index.

Through the above several query cases, the precautions for the use of index combination index are as follows:

Composite indexes must start with the leftmost column of the index;

Composite field queries cannot be skipped so that indexes cannot be used.

Thank you for your reading, the above is "how to use MySQL index structure and query reasonably" content, after the study of this article, I believe we have a deeper understanding of how to use MySQL index structure and query reasonably, and the specific use needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.