Sing Xing interprets MyISAM engine _ mysql in MySQL

In many previous textbooks, MyISAM is the default storage engine of mysql. In fact, since mysql55, the default storage engine has changed to InnoDB, because InnoDB has irreplaceable functions in many aspects, many people prefer to study InnoDB. in many previous textbooks, MyISAM is the default storage engine of mysql, in fact, since mysql5.5, the default storage engine has changed to InnoDB. because InnoDB has irreplaceable functions in many aspects, it is reasonable for many people to study InnoDB, I also like InnoDB. But for MyISAM, let's get to know Dr. Xin Xing.

The first point is that MyISAM does not support transactions and foreign keys. Based on this, many people do not like MyISAM. However, MyISAM is not worried about changing itself, because the engine focuses on performance rather than functions. it is undeniable that MyISAM has excellent performance, especially reading data.

The second point 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 table definitions. when we use alter table, this data will be modified. Therefore, using alter table is faster, the second part is the file whose suffix is myd. In fact, d is the first character of data, used to store the MyISAM data file, and the third part is the file whose suffix is myi, here I is the first letter of the index, used to store the index file of MyISAM. By default, data files and index files are stored in the same directory, but we can also store them in different directories for higher speed.

The third point is that MyISAM supports three different storage formats: static, dynamic, and compressed. Note that the compression format can only be created using myisampack. The static format is also the default storage format of MyISAM. when our table does not contain variable length columns such as varchar and other data types, it will automatically use this format, each row uses a fixed number of bytes for storage. The advantage of static format is that the search speed is very fast, it is easy to cache, and it is easy to fix (I mentioned earlier that optimize table is used to fix data fragments ), the disadvantage is that it takes more disk space. The dynamic format is more complex, because each row has a column indicating the row length. Each record only requires space of the required size. if a record becomes larger, it is split into multiple parts as needed, resulting in record fragmentation. For example, when we update data with extended length information, this row will produce fragments. Because of the dynamics, fragments are more likely to be generated, so we often need to optimize the table multiple times.

For a compressed table, we should first create a table and then use myisampack to compress it. the compressed table will occupy a small disk space, which will minimize the disk usage, in addition, each record is compressed separately, so the access overhead is quite small, and it will modify the corresponding data type. For example, if the value of a column is between-128 and 127, but we use int for storage, it will be automatically converted to tinyint for storage. If a column has only a small set of possible values, the column type is converted to ENUM.

So many people may want to talk about how to compress tables in an instance. well, Xin Xing kids shoes definitely meet everyone's requirements. let's take a look at the examples, but it must be noted that, tables that are too small will not be compressed, for example, 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 look for 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 is compressed. we can see that our table originally stores 483 records, and it also prompts us to use myisamchm for decompression.

The fourth problem is locking and concurrency. MyISAM locks the entire table when locking. this is also widely criticized by MyISAM. However, when processing concurrency and reading data, shared locks can be obtained for all tables, that is, each connection will not interfere with each other, but the exclusive lock will be obtained during data writing, and the entire table will be locked, other requests, including read and write requests, must be in the waiting status.

The fifth point is to mention table repair, which is also a convenient place for MyISAM. it can use [check table name] to detect tables, you can also use [repair table name] to repair tables. of course, you can also use optimize to optimize tables, such as removing data fragments.

The sixth point is also important, that is, MyISAM supports full-text indexing, but InnoDB does not. this is also a few of MyISAM functions that are more than InnoDB functions, however, MyISAM does not currently support Chinese, but we can use third-party technologies to make up for this.

This time, let's write it here. we look forward to your attention.