How to understand MySQL InnoDB index transactions

Mysql > select * from ‘Mysql’

Definition of index

An index is a structure that sorts the values of one or more columns of a database table. Using an index allows quick access to specific information in a database table. If you want to look up a particular employee by his or her last name, an index helps you get information faster than if you searched all the rows in a table.

One of the main purposes of indexing is to speed up the retrieval of data in the table, that is, to assist information searchers to find the record ID that meets the restriction conditions as soon as possible.

Type of index

Primary Key Indicates the primary key index

It is a special kind of unique index that does not allow null values. A table can have only one primary key

Unique index

Unique indexed columns must have unique values, but null values are allowed. If it is a composite index, the combination of column values must be unique.

Index Common index

This is the most basic index, it has no restrictions.

C/C++, Linux, Golang technology, Nginx, ZeroMQ, MySQL, Redis, Fastdfs, MongoDB, ZK, Streaming media, CDN, P2P, K8S, Docker, TCP/IP, coroutine, DPDK, FFMPEG, etc.)

Fulltext Fulltext index

Full-text indexing (also known as full-text retrieval) is a key technology used by search engines at present. It can use segmentation technology and other algorithms to intelligently analyze the frequency and importance of keywords in the text, and then select the search results we want intelligently according to certain algorithm rules.

Composite index

Composite indexes, in which an index contains multiple columns.

Index structure

Mysql commonly uses B+Tree as an index, but the implementation is different according to the clustered index and non-clustered index.

The test data

We now have an order table with the following structure:

5. Left-most prefix matching is a very important principle, mysql will match from left to right until it encounters a range query (>, <, between, like). Create an index

CREATE INDEX IDX_T_1 USING BTREE ON xa87_v2.t_xa87_order_info (store,delivery_msg,food_fee,delivery_fee);

We execute an SQL

There is no store in where, so the IDX_T_1 index is not used.

Let’s add store to the query and see what happens:

Explain the select * from t_xa87_order_info where store = 'f9fd2705ad1d740a4bef42833b487cea' and delivery_msg = 'Gao Yue static, 17836031' and food_fee=1000;

` + – + — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — — – + — — — — — — — — — — — – + — — — — — – + — — — — — — — — — — — — — — — + + — — — — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — — – + — — — —+———-+——-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +—-+————-+——————-+————+——+—————+———+———+——————-+—- –+———-+——-+ | 1 | SIMPLE | t_xa87_order_info | NULL | ref | IDX_T_1 | IDX_T_1 | 267 | const,const,const | 1 | 100.00 | NULL | +—-+————-+——————-+————+——+—————+———+———+——————-+—- –+———-+——-+

The above two queries both use the IDX_T_1 index. The first is more efficient because the second query does not continue to match food_fee because it uses like on the delivery_msg condition

Select a highly differentiated column as the index

High differentiation means fewer identical values. For example, the gender field, which has only two values, male and female, is not suitable for indexing.

Expand indexes and try not to create new ones

For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index.

Second, query optimization

Do not invalidate indexes

Arguments to Like do not start with a wildcard character

The Where condition matches the leftmost prefix

It has been explained above and will not be repeated

Don’t use it! = and < >

Do not judge with NULL

Don’t use or
The columns in = and in can be out of order

Indexed columns do not participate in the calculation

Create an index on created_date

CREATE INDEX IDX_T_2 USING BTREE ON xa87_v2.t_xa87_order_info (created_date);

You can see that the first query uses an index and the second does not.

Query SQL optimization

Avoid the select ‘*’

During SQL parsing, ‘*’ is converted to all column names in sequence, which is done by querying the data dictionary, which means more time. Therefore, we should form the good habit of taking what we need.

The order by optimization

Rewrite the ORDER BY statement to use the index;

Create another index for the used column;

Absolutely avoid expressions in the Order by clause;

Group by optimization

Improve the efficiency of GROUP BY statements BY filtering out unwanted records before GROUP BY

The exists instead of the in
Use varchar/nvarchar instead of char/nchar
A. DISTINCT B. GROUP C. DISTINCT D. GROUP
If you can use UNION ALL, don’t use UNION

The UNION ALL does not execute the SELECT DISTINCT function, which reduces unnecessary resources.

Use a similar type of example when joining a table and index it

If there are many Join queries, you should make sure that the Join fields in both tables are indexed. In this way, MySQL internally starts the SQL statement that optimizes the Join for you.

The fields used to Join should be of the same type. For example, if you want to Join DECIMAL fields with an INT field, MySQL cannot use their indexes. For those strings, the same character set is required. (The character set of the two tables may be different)

3. Transaction

Characteristics of transactions

Atomicity (A) : All operations in A transaction are either completed or not completed, and do not end up somewhere in between. If an error occurs during the execution of a transaction, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.

Consistency (C) : The integrity of the database is not compromised before and after a transaction. This means that the data to be written must fully conform to all preset rules, including the accuracy of the data, the concatenation of the data, and the ability of the subsequent database to perform predetermined work spontaneously;

Isolation (I) : the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation prevents data inconsistency caused by cross execution when multiple transactions are executed concurrently;

Persistence (D) : Changes to data are permanent after a transaction is completed;

Isolation level

Read Uncommitted: Read Uncommitted: Read COMMITTED Repeatable Read serialization: serialIZABLE

Read Uncommitted

Object A and object B, the uncommitted data of object A, and object B can Read the data that is Read here is called “dirty data”. This is the lowest isolation level, and this level usually exists in theory, and the database isolation level is usually higher than this level

In order for thing A and thing B, the data that thing A commits, thing B can read this isolation level higher than reading uncommitted data in other words, after the other thing commits, This isolation level will result in “Repeatable Read” Oracle default isolation level 3. Repeatable Read

Transaction A and transaction B, the data after transaction A commits, transaction B can’t read and transaction B is repeatable and that’s A higher level of isolation than reading committed data in other words, I still can’t read the data after the other party commits and this level of isolation prevents “unrepeatable reads,” For example, the default MySQL level can be repeatable, but will result in “magic read” 4. Serializable

This isolation level is rarely used because the throughput is too low and the user experience is poor. This level can avoid “phantom reads”. Each read is real data in the database

How to understand MySQL InnoDB index transactions

Related Posts

Remember a server was hacked

Lesson 5 — Variables and Constants

Notes on how to Improve Code Readability