Sinsing interpreting the MyISAM engine in MySQL

Many of the previous textbooks were written MyISAM is the default storage engine for MySQL, In fact, since the beginning of mysql5.5, the default storage engine has changed to become InnoDB, because innodb in many aspects have irreplaceable function, so many people like to study InnoDB also in the sense, I also like InnoDB. But, for MyISAM, follow Dr. Sinsingsin to meet him.

The 1th is that MyISAM does not support transactions and foreign keys, and it is on this point that many people do not like MyISAM. But MyISAM is not anxious to change itself, because the center of gravity of the engine is performance, not functionality, admittedly, MyISAM performance is excellent, especially in reading data.

The 2nd is its file format, a MyISAM table is usually divided into three parts, the first part is XXX.FRM, this file is used to store the definition of the table, we use ALTER TABLE when the data will be modified, so the use of ALTER TABLE speed is relatively fast, The second part is the suffix name is myd file, in fact, here D is the first letter of data, used to store the MyISAM file, the third part is the suffix is myi file, where I is the first letter of index, for storing MyISAM index file. By default, data files and index files are placed in the same directory, but we can also put them in different directories to get a higher speed.

3rd, MyISAM supports three different storage formats, which are static, dynamic, and compressed, noting that the compression format can only be created using the Myisampack tool. First of all, the static format, which is also the default storage format of MyISAM, when our table does not contain variable length columns such as varchar data types, it will automatically use this format, each row will choose a fixed number of bytes stored. The advantage of the static format is that it is very fast to find, easy to cache, easy to fix (I mentioned using optimize table to fix the table in front of the data fragment), and the disadvantage is that it takes up more disk space. Then the dynamic format is more complicated because each row has a column that indicates how long the row is. Each record requires only a space of a required size, and if a record becomes larger, it is separated into multiple slices as needed, resulting in fragmented records. For example, when we update data with extended-length information, the row is fragmented. Because of the dynamic reason, it is also more likely to produce fragments, so we often need to optimize the table multiple times.

For a compressed table, we should first establish a table, and then use Myisampack to compress, the compressed table will occupy a small amount of disk space, which will minimize the use of the disk, and it is each record is compressed separately, so the cost of access is quite small, it will also modify the corresponding data type. For example, if the value of a column is between 128 and 127, but we use an int to store it, it is automatically converted to tinyint for storage. If a column has only a small set of possible values, the type of the column is converted to an enum.

So many people may want to say that we operate the compression of the table, OK, Sinshing shoes absolutely meet the requirements of everyone, we come to the example operation, but it must be noted that for too small table, is not to compress, such as the following two examples:

C:\users\administrator>myisampack user. Myiuser.myi is too small to compressc:\users\administrator>myisampack db. Myidb.myi is too small to compress

Let's find a slightly larger table to do the experiment:

C:\users\administrator>myisampack Help_keyword. Myicompressing Help_keyword. MYD: (483 Records)-Calculating statistics-compressing File95.15%remember to run MYISAMCHK-RQ on compressed tables

At this point, the table compression is complete, you can see that our table originally stored 483 records, and it also prompts us to use Myisamchm to decompress.

The 4th problem is locking and concurrency, MyISAM in the lock when the whole table is locked, which is MyISAM widely criticized a point, but in processing concurrency, when reading data, all tables can be shared lock, that is, each connection will not interfere with each other, while writing data, An exclusive lock is obtained, the entire table is locked, and other requests, including both read and write, must be in a wait state.

The 5th mentioned the repair of the table, this is also myisam convenient place, it can use "Check table name" To detect the table, you can also use "Repair table name" To repair the table, of course, you can also use optimize to optimize the table, such as the removal of data fragmentation and so on.

The 6th is also more important, that is, MyISAM is supported full-text indexing, but InnoDB does not support, which is myisam a few more than InnoDB features more features, but the current MyISAM seems to still do not support Chinese, But we can use third-party technology to compensate for this.

This time first write here, look forward to your attention.

Sinsing interpreting the MyISAM engine in MySQL