When you say:
CREATE TABLE Table1...MySQL creates files named Table1.MYD (" MySQL Data "),Table1.MYI (" MySQL Index "), and Table1.frm (" Format ").These files will be in the directory: /< datadir > /< database >/
For example,if you use Linux, you might find the files in the /usr/local/var/test directory(assuming your database name is test).if you use Windows,you might find the files in the mysqldatatest directory.
Let's look at the .MYD Data file (MyISAM SQL Data file) more closely. There are three possible formats — fixed, dynamic, and packed. First, let's discuss the fixed format.
Unlike most DBMSs, MySQL doesn't store on disk using pages. Therefore you will not see filler space between rows.(Reminder: This does not refer to BDB and InnoDB tables, which do use pages).
The minimal record header is a set of flags:
The length of the record header is thus:
(1 + number of NULL columns + 7) / 8 bytes
After the header, all columns are stored in the order that they were created, which is the same order that you would get from SHOW COLUMNS.
Here's an example. Suppose you say:
A CHAR(1) column takes precisely one byte (plus one bit of overhead that is assigned to every column — I'll describe the details of column storage later). So the file Table1.MYD looks like this:
Hexadecimal Display of Table1.MYD file
F1 61 62 63 00 F5 64 00 66 00 ... .abc..d e.
Here's how to read this hexadecimal-dump display:
(It's probably easier to understand the flag setting if you restate F5 as 11110101 binary, and (a) notice that the third flag bit from the right is on, and (b) remember that the first flag bit is the X bit.)
There are complications — the record header is more complex if there are variable-length fields — but the simple display shown in the example is exactly what you'd see if you looked at the MySQL Data file with a debugger or a hexadecimal file dumper.
So much for the fixed format. Now, let's discuss the dynamic format.
The dynamic file format is necessary if rows can vary in size. That will be the case if there are BLOB columns, or "true" VARCHAR columns. (Remember that MySQL may treat VARCHAR columns as if they're CHAR columns, in which case the fixed format is used.) A dynamic row has more fields in the header. The important ones are "the actual length", "the unused length", and "the overflow pointer". The actual length is the total number of bytes in all the columns. The unused length is the total number of bytes between one physical record and the next one. The overflow pointer is the location of the rest of the record if there are multiple parts.
For example, here is a dynamic row:
In the example, the actual length and the unused length are short (one byte each) because the table definition says that the columns are short — if the columns were potentially large, then the actual length and the unused length could be two bytes each, three bytes each, and so on. In this case, actual length plus unused length is 10 hexadecimal (sixteen decimal), which is a minimum.
As for the third format — packed — we will only say briefly that:
For details, see the source files /myisam/mi_statrec.c (for fixed format), /myisam/mi_dynrec.c (for dynamic format), and /myisam/mi_packrec.c (for packed format).
Note: Internally, MySQL uses a format much like the fixed format which it uses for disk storage. The
main differences are:
Physical Attributes of Columns
Next I'll describe the physical attributes of each column in a row. The format depends entirely on the data type and the size of the column, so, for every data type, I'll give a description and an example.
Important: MySQL almost always stores multi-byte binary numbers with the low byte first. This is called "little-endian" numeric storage; it's normal on Intel x86 machines; MySQL uses it even for non-Intel machines so that databases will be portable.
Warning: Because TINYBLOB's preceding length is one byte long (the size of a TINYINT) and MEDIUMBLOB's preceding length is three bytes long (the size of a MEDIUMINT), it's easy to think there's some sort of correspondence between the BLOB and INT types. There isn't — a BLOB's preceding length is not four bytes long (the size of an INT).
Where to Look For More Information
Most of the formatting work for MyISAM columns is visible in the program /sql/field.cc in thesource code directory. And in the MyISAM directory, the files that do formatting work for different record formats are:
MySQL Related Interview Questions
|PHP Interview Questions||MySQL Interview Questions|
|PHP+MySQL Interview Questions||Drupal Interview Questions|
|MYSQL DBA Interview Questions||PHP5 Interview Questions|
|WordPress Interview Questions||Joomla Interview Questions|
|CakePHP Interview Questions||CodeIgniter Interview Questions|
|PHP7 Interview Questions|
A Guided Tour Of The Mysql Source Code
Important Algorithms And Structures
How Mysql Performs Different Selects
How Mysql Transforms Subqueries
Mysql Client/server Protocol
Prepared Statements And Stored Routines
Myisam Storage Engine
Innodb Storage Engine
Writing A Custom Storage Engine
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.