Shulou(Shulou.com)06/01 Report--

I. Analysis of resource consumption of index

1. Three characteristics of the index.

1. Small: index only on one or more columns

2. Order: the end point can be located quickly

3. There is a tree: the starting point can be located, and the height of the tree is generally less than or equal to 3

2. Resource consumption point of the index

1. The height of the tree, the data pages of the index are accessed sequentially, and the index is established on the column. The amount of data is very small, in memory.

2. Skip access between data

1. If the index jumps to the table, you may need to access a lot of data pages of the table.

2. when there is a serious inconsistency between the order of the primary key column and the index to access the table through the index, a large number of physical reads may occur.

The most resource consuming: access multiple rows through the index, need to fetch multiple rows from the table, if out of order, jump back and forth to find, jump access, physical reading will be very serious.

Second, the principle of adaptive hash index.

1. Principle process

The Innodb storage engine monitors the lookup of the secondary index on the table. If it is found that a secondary index is accessed frequently and the secondary index becomes hot data, the establishment of a hash index can improve the speed:

1) the adaptive hash indexing function is turned on

Mysql > show variables like'% ap%hash_index'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_adaptive_hash_index | ON |

+-+ +

1 row in set (0.01 sec)

2) the frequently accessed secondary index data is automatically generated into the hash index (the data recently accessed three times in a row), and the adaptive hash index is constructed through the B + tree of the buffer pool, so the speed of establishment is very fast.

2. Characteristics

1. Disorder, no tree height

2. Reduce the frequent access to the secondary index tree.

Index tree height show engine innodb status\ G

……

Hash table size 34673, node heap has 0 buffer (s)

0.00 hash searches/s, 0.00 non-hash searches/s

1. 34673: total memory space occupied in bytes

2. Calculate the benefits and costs of adaptive hash index by hash searches and non-hash searches, and determine whether to turn on adaptive hash index.

2. Restrictions

1. It can only be used for equivalent comparison, such as =, in.

2. Cannot be used for sorting

3. There may be conflicts.

4. MySQL is managed automatically and cannot be intervened by human.

3. Control of adaptive hash index

Because innodb does not support hash index, but in some cases hash index is very efficient, so there is an adaptive hash index function, but through the above status monitoring, you can calculate its benefits and efforts, and control whether the function is turned on or not.

On by default, it is recommended to turn it off, it doesn't make much sense. You can turn this feature off and on through set global innodb_adaptive_hash_index=off/on.

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.