mysql-Cluster cable sequencing slow case analysis

Why should I use the Mysiam engine when more select is executed? Especially in the case of an index , this chapter relies on a practical application, analysis.

I. Preface:Online to see there is an interesting phenomenon, a 1W data volume of the table, the execution of different conditions, query time is very large, this is the actual application of the problem does occur?? Why is it? Two. Analysis a). Description of the situation:

1. There is a primary key ID, a federated index (Id,ver), the former when the query is slow, with the latter, the query will be quick; 2. The amount of data in each row is very large. 3.id is the primary index, and the field of the select query has only the ID, so it is not an index overlay , not to the physical disk back to the data, the index can get the data, but it should be faster and slower to query. mysql-Index Overlay

b). Analysis:

certainly use is not the Mysiam engine, if the words use these two indexes to query, in fact the speed is similar, because the index is stored is a physical line address, actually occupies the amount of data is not big. But if it's innodb, it's not the same, it's under the main index but telework holds all the data for that row.

c). Conclusion:

1. Main cause: the InnoDB engine used

is a clustered index, the primary key ID index also telework the other data hanging on the row, resulting in sorting along the ID, to cross a lot of small blocks to query the traversal of each ID, (and Mysiam not so much data, across the same block will be faster, traverse more rows)

2. From: There are several fields of data volume is relatively large, that is, telework with more people, the amount of data is larger. The volume of data per row is large, and the number of blocks used in disk storage is 3. There was no problem with the Mysiam engine at the time.

D). Mapping Conclusion:

When

More select is executed, the Mysiam engine should be used, while the InnoDB engine is used when executing insert,update

more conclusions See: mysql-Index summary

three. Simulation TestRestore the above mentioned conditions, create a table, Control Variables, except for engine differences, the remaining conditions are the same, primary key ID primary index, Federated Index (id,ver). 1. New Table T7,mysiam engine
2. Randomly inserting 10,000 data
3. Execute the query statement to see the time

Obviously, the time difference is not too big, is a magnitude.

4. New Table T8,innodb engine

5. Randomly inserting 10,000 dataepisode, follow the script above to execute the statement, wait for a very long time, why? Because it is a clustered index, there is a primary key index ID, when the primary key index is created, the data block of the row is moved a lot, there is split movement time inside. The operation is to first delete the primary key index ID, insert the data after the Add primary key (ID), and then create the primary key index structure
6. Execute the query statement to see the time

Obviously, the time difference is not too big, is a magnitude.

Cause: Two statements are used to clustered index, but the primary key is too much cross-block, and the Joint Index is a secondary index, the bottom of no data, fewer blocks, fast traversal.

7. General analysis, only T8 table (INNODB) in accordance with the primary key index sorting time is much, the rest is OK

Time Sequencing Conclusion: InnoDB. Primary index > InnoDB. Secondary index > Mysiam

The efficiency is nearly 30 times times worse, where is the problem?

1. The main reason, the order by ordering along the primary key, the query will spread a lot of blocks, the time increased by 2. If there are not a few long char fields, the data block is small, and it doesn't make such a big difference.

For example, delete the Str1,str2,str3 field in the table, the query time will be greatly reduced, the difference is not obvious

mysql-Cluster cable sequencing slow case analysis