Mysql-clustered index slow sorting case analysis

Why should the mysiam engine be used when executing a lot of selects? Especially when there is an index

This article relies on a practical application and analyzes it.

1. Foreword:

I saw an interesting phenomenon on the Internet. A table with 1W data volume executes different orderby conditions and query time. Very big. Is this a real problem in practical applications? ? why?

2. Analysis

a). Situation description:

1. There is primary key id, joint index (id, ver); Using the former for orderby query is slow, while using the latter for orderby query will be very fast;

2. The amount of data in each row is quite large

3.id is the main index, and the fields for select query are also If there is only an ID, then the index is covering it. You don't need to go to the physical disk to retrieve the data. You can get the data you want on the index, but the query that should be faster is slower. Mysql-index coverage

b). Analysis:

is definitely not using the mysiam engine. If so, the speed of query using these two indexes is almost the same, because What is stored in the index is the address of a physical row, and the actual amount of data occupied is not large. But it's different if it's innodb. All the data of the row is stored under its main index.

c). Conclusion:

1. Main reason: The innodb engine

used is a clustered index, and the primary key ID index also hangs the row in the dragnet. Other data, so when sorting along the ID, you have to cross many small blocks to query and traverse each ID; (there is not so much data under mysiam, it will be faster to cross the same data block and traverse more rows)

2. Reason: The amount of data in several fields is relatively large, that is, there are many people dragging their families with their families, and the amount of data is relatively large. The amount of data in each row is large, and it takes up many blocks when storing on disk

3. This problem did not exist in the mysiam engine at that time

d). Mapping conclusion:

When a lot of selects are executed, the mysiam engine should be used.

When a lot of inserts and updates are executed, the innodb engine should be used.

For more conclusions, please see: Mysql-Index Summary

3. Simulation test

Restore the conditions mentioned above, create two tables, control variables, except for the different engines, the other conditions are the same, primary key ID primary index, joint index (id, ver).

1. Create a new table t7, mysiam engine

2. Randomly insert 10,000 pieces of data

3. Execute the query statement and check the time

Obviously, the time difference is not too big , are all of the same magnitude.

4. Create a new table t8, innodb engine

5. Randomly insert 10,000 pieces of data

Interlude, when executing the statement according to the above script, the waiting time is very long. Why? Because it is a clustered index with a primary key index ID, when the primary key index is created, a large number of row data blocks are moved, and there is time for splitting and moving.

The operation is to first delete the primary key index ID, insert the data and then add primary key (id), and then create the primary key index structure

6. Execute the query statement , check the time

Obviously, the time difference is very different.

Reason: Both statements use clustered indexes, but the primary key spans too many blocks, while the joint index is a secondary index with no data underneath, few blocks, and fast traversal.

7. Overall analysis, only the t8 table (innodb) takes a long time to sort according to the primary key index, the rest are okay

Time sorting conclusion: innodb. Primary index> innodb. Secondary index> mysiam

The efficiency is nearly 27 times worse. Where is the problem?

1. The main reason is to do order by sorting along the primary key. The query will span many blocks across pages, and the time will increase

2. If there are not several long char fields, the data block will not be large. , it will not cause such a big difference.

For example, if you delete the str1, str2, str3 fields in the table, the query time will be greatly reduced, and the difference will not be obvious

The above is the content of Mysql-clustered index slow sorting case analysis. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!