High performance index

1. Query performance problems

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

2. Index usage principles

The use of indexes is not the more the better, but for business query scenarios, continuous improvement and optimization, such as the scenario of user orders in the e-commerce system, assuming the following table structure:

CREATE TABLE `ds_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary 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 'primary key id',
  `user_id` int(11) NOT NULL COMMENT 'user ID',
  `order_no` varchar(60) NOT NULL COMMENT 'Order Number',
  `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 'price',
  `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 for ',
  `order_remark` varchar(50) DEFAULT NULL COMMENT 'Order Notes',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Order sheet';
Copy the code

The user and order management table is very common in the business of e-commerce. By analyzing the business, we can see the common index structure:

User:

  • User-based queries, mostly based on user ID(user_id);
  • View logistics information based on order number (order_NO);

Operator:

  • Time-based running details (create_time) or sorting;
  • Order state-based filtering (order_state) and statistics;
  • Data statistical analysis based on product_name;

Such a process analysis, that is, in the early stage of development, determine which structures are necessary for the query, do a good index structure in advance, avoid the large amount of data to affect the performance of the index to consider using.

Sometimes some query conditions will be considered to give up, such as data statistics based on product name, take the way of scheduled task, used to relieve the query pressure of the table, the way of processing is diverse.

Excellent index design is based on the understanding of service data, considering the query mode of service data to improve the query efficiency.

Index creation

1. Single-column index

A single-column index is a table whose index is based on a single field. A table can have multiple single-column indexes, which are relatively simple to use:

CREATE INDEX user_id_index ON ds_order(user_id) USING BTREE;
Copy the code

Primary key indexes, or user_ID_index above, are single-column indexes.

Service scenario: Based on the user’s order query and the associated query between the management system, order and user, the order table user_id needs an index.

2. Composite indexes

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 business association.

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

Create_time (create_time,order_state); create_time (create_time,order_state); create_time (create_time,order_state);

Business scenario: Firstly, the combined index is mentioned here. In business development, the statistics of common order status are used to conduct operation analysis based on the statistical results. In addition, in the operation system, the screening conditions based on the creation time period exist by default, avoiding real-time scanning of all data. Some other common queries are also conditional plus time queries.

3. Prefix index

If the column that needs to be indexed is a long string, the index will become bloated and the effect may not be obvious. In this case, the first part of the column can be cut to create an index to save space. In this case, 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));Copy the code

Here, because the order number is too long, the first 30 digits are selected as the prefix index for the order number query. Of course, this involves a very classic business scenario, the order number mechanism.

Business scenario: Prefix index A typical application scenario is dealing with order numbers. A seemingly long order number contains a lot of information:

  • Time point: is the time of order generation, year month day hour minute second;
  • Identification bit: that is, a unique UID to ensure that the order number is unique;
  • Buried point 1: in many businesses, record product categories in the order number;
  • Buried point 2: product attributes, such as color and taste, are usually identified;
  • Dislocation symbol: to prevent the order number from being analyzed, a random paragraph of dislocation symbol;

After such analysis, the actual order number is very long, so we need to introduce the prefix index mechanism, the expected index length can filter the cardinality of the entire column, such as the order number above:

  • Most services filter enough nodes based on time, that is, the index length is 14 bits;
  • If the service is concurrent and many time nodes are the same, the index length is point in time + identifier bit.

Note: If business allows, it is generally required that the length of the prefix index be unique, such as the time and identifier bits above.

4. Other indexes

Such as full-text index, which is used in a few scenarios, if the data is huge, and need to retrieve, etc., usually choose powerful search middleware to deal with. Explicitly unique indexes, which also provide procedural circumvention, prevent unfriendly exceptions from being thrown.

Index query

It is not easy to create the optimal index, and it is also very difficult to use the index when querying. Experience says: Most of the time when performance problems are exposed, we will look back at the SQL statement of the query and optimize the corresponding query for performance problems.

1, single column query

MySQL > query primary key index (primary key);

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;
Copy the code

MySQL > select * from primary key where id=1 and id=1; MySQL > select * from primary key where id=1 and id=1; There is a basic strategy here. If you have to operate on a single column index, you can put that logic in your program. At the MySQL level, the SQL statement is as clean as possible.

2, prefix index query

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

EXPLAIN SELECT * FROM ds_order WHERE order_no LIKE '202008011314158723628732871625%';
EXPLAIN SELECT * FROM ds_order WHERE order_no='20200801131415872362873287162572367';
Copy the code

3, composite index query

The most troublesome aspect of a query is the combination of indexes, or the combination of query conditions that use indexes:

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

The above uses the composite index: state_create_TIME_index, based on the order of the columns in the composite index.

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

The above uses only the create_time column, as well as the index structure.

EXPLAIN SELECT * FROM ds_order WHERE order_state='1';
Copy the code

Using only the ORDER_state condition 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%';
Copy the code

Above, the create_time column of the composite index and the single-column index order_NO guarantee that the query condition uses the index.

Through the above query cases, the following points should be noted when using index combination indexes:

  • A composite index must start the query at the left-most column of the index;
  • Combined field queries cannot be skipped so that indexes cannot be used;

Other instructions for index

1. Advantages of indexes

  • Ensure uniqueness of data in database tables based on annotations or unique indexes;
  • Index improves query efficiency by reducing the number of rows scanned in the table.

2. Disadvantages of indexes

  • Creating and maintaining indexes costs space and practicality.
  • Query operations, such as adding, deleting and changing, need dynamic maintenance index;

3. Index use summary

Index mechanism in MySQL is really very complex, non-professional DBA(that is, developers), basic need to be familiar with the common index structure, after two years of so-called big factory, each version of the development involved in the core SQL table is accepted by professional DBA, complex queries are submitted requirements, DBA directly output query SQL. Of course, in most companies, there is no DBA, so it is necessary to keep thinking and gradually optimize the development process, which requires a certain sensitivity to business data and monitoring of the execution of core interfaces. When a little time consuming is found, continuous optimization can be made. This accumulation is a boring and progressive process.

Five, source code address

Making address GitEE, https://github.com/cicadasmile/mysql-data-base, https://gitee.com/cicadasmile/mysql-data-baseCopy the code