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

MyISAM features:

B-tree

Full-text retrieval index

Index cache

Data compression

Copy

Query cache

3D geographic data type

Geographic data index

data encryption

Statistical information

Backup, point-in-time recovery

Three storage formats of MyISAM

1FIXED is also known as static format storage: the length of data occupied by each column saved is fixed. The query speed is the fastest

2DYNAMIC is also known as dynamic format storage: variable length data columns store data according to the actual length of the saved data. Save space, the performance will be relatively poor

3Compact is also known as compressed format storage: it is created with the myisampack command and decompressed with the myisamchk command. Read only. Save space, and read very fast.

MyISAM tool

1meme myisamchk-ei runstats similar to oracle/db2

2myisamchk-ed similar to oracle/db2 's reorgchk

3meme myisamchk-r similar to oracle/db2 's similar reorg

MyISAM usage scenario

1, set the appropriate index

2. Because MyISAM is a table-level lock, it is necessary to adjust the write order when writing, so as to distinguish the important and secondary order as far as possible.

3, minimize the number of insert statement execution, can merge the execution of the merge execution.

4, for the query statement, try to query separately and run in a serial way. Reduce the number of concurrency.

5Jing MyISAM caches the number of rows of the entire table, so count is fast for the whole table. But the conditional statement count will take a full table scan, which will be very slow, so try to avoid it.

MyISAM key related variables

Key_buffer_size is the size of the index block buffer. Shared cache.

2Magnesia KEY threshold, which controls the threshold of the hot zone in the buffer where the cache is located. The higher the threshold, the longer the holding time.

3Magnesia keyword cacheblocks blocksize, the byte size of blocks in the key value cache

4 the percentage of non-hot spots in the key cache

Myisam key related state variables

1the number of times that the data in key has been changed but has not been written to disk

2the number of unused key blocks that are not used

3The number of key blocks used by the Keyboard blocks

4the number of requests for key data blocks

5 the number of key data blocks actually read from the hard disk and written to the cache

6 the number of requests for writing key data blocks to the hard disk

7 the number of IO times that key blocks are actually written to the hard disk

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.