SQL Getting Started Classic (fifth edition) Ryan Stephens study notes follow-up-storage engine

First, the Engine foundation

1 Viewing system-supported storage engines

　　Show engines;

2 View the storage engine used by the table in two ways:

A, Show table status from database_name where name= ' table_name ';

b, show create TABLE table_name;

To view the default engines used:

　　Show variables like ' default_storage_engine '; //mysql5.5 later uses the InnoDB storage engine by default,

If the displayed format is not good-looking, you can use \g instead of the end of line semicolon

Some people say that using the second method is not accurate, I tried to shut down the original default InnoDB engine is not able to execute the show CREATE TABLE table_name instruction, because it was built InnoDB table, after the default with the MyISAM engine, Causes the InnoDB table data to not be read correctly.

3 Modifying the Table engine method

ALTER TABLE table_name ENGINE=INNODB;

4 shutting down the InnoDB engine method

Turn off MySQL service: net stop MySQL

Locate the My.ini file in the MySQL installation directory:

Find Default-storage-engine=innodb instead of Default-storage-engine=myisam

Find #skip-innodb instead of Skip-innodb

Dynamic MySQL service: net start MySQL

Two or three common storage engine introductions

A MyISAM
It does not support transactions, does not support foreign keys, especially fast access, there is no requirement for transactional integrity, or a SELECT, insert-based application can basically use this engine to create tables.
Each myisam is stored as 3 files on disk, with the same file name and table names, but with the following extensions:

• . frm (save table definition)
• MYD (MYData, storing data)
• MYI (myindex, storage index)

　data files and index files can be placed in different directories, evenly allocated Io, to get faster speeds. to specify the path to the data file and the index file, you need to create the table by using data DIRECTORY

and the index directory statement specify that the file path needs to use an absolute path. Each MyISAM table has a flag that is set by the server or MYISAMCHK program when it checks the MyISAM data table.

The MyISAM table also has a flag indicating whether the data table has been shut down properly since it was last used. This flag can be used to determine if a data table needs to be checked and repaired if the server thinks it is a crash or a machine.

If you want this check to occur automatically, you can use the--myisam-recover behavior when you start the server. This causes the server to automatically check the data table flags and perform the necessary repair processing each time a MyISAM data table is opened.

Tables of type MyISAM may be corrupted, you can use the Check Table statement to check the health of the MyISAM table, and fix a corrupt to MyISAM table with the Repair table statement.

The MyISAM table also supports 3 different storage formats:

• static (fixed-length) table
• Dynamic tables
• Compression table

Where the static table is the default storage format. The fields in the static table are non-variable length fields, so that each record is fixed length, the advantage of this storage method is that the storage is very fast, easy to cache, the failure is easy to recover;

The disadvantage is that the space occupied is usually more than the dynamic table. A static table complements the spaces defined by the column definition when the data is stored, but does not receive these spaces when accessed, and these spaces are removed before they are returned to the app.

It is also important to note that, in some cases, you may need to return a space after the field, and the following space will be automatically processed when using this format.

Dynamic tables contain variable-length fields, records are not fixed lengths, so storage has the advantage of less space, but frequent updates to delete records can result in fragmentation, requiring periodic execution of optimize table statements

or MYISAMCHK-R commands to improve performance, and recovery is relatively difficult in the event of a failure.
Compressed tables are created by the Myisamchk tool and occupy very little space because each record is individually compressed, so there is very little access expense.

Two InnoDB
The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared to the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes.
1) Auto-Grow column:
　　The auto-grow column of the InnoDB table can be inserted manually, but if it is empty or 0 inserted, then the actual insert is automatically increased to the value. You can pass the ALTER TABLE ... Auto_increment=n; " Statement Force setting Automatic

The starting value of the growth value, which defaults to 1, but the force to default value is saved in memory, and the value will be lost after the database restarts. You can use LAST_INSERT_ID () to query the value used by the last inserted record for the current thread. If you insert more than one

Record, the auto-grow value used for the first record is returned.

For InnoDB tables, the autogrow column must be an index. If it is a composite index, it must also be the first column of the combined index, but for the MyISAM table, the autogrow column can be the other column of the combined index, so that when the record is inserted, the autogrow column is incremented by the combined index to the previous column sort.

2) FOREIGN KEY constraint:

　　MySQL supports foreign key storage engine only InnoDB, when creating foreign keys, the parent table must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

When you create an index, you can specify the corresponding actions on the child table when the parent table is deleted, updated, including restrict, Cascade, set NULL, and no action. where restrict and no action are the same, it means that the parent table cannot be updated when the child table is associated;

Casecade indicates that the parent table updates or deletes the records corresponding to the child table when it is updated or deleted, and set NULL indicates that the corresponding field of the child table is set NULL when the parent table is updated or deleted. When a table is created with a foreign key reference by another table, the table's corresponding index or primary key is forbidden from deletion.

You can use set foreign_key_checks=0, temporarily turn off foreign key constraints, set Foreign_key_checks=1, and open constraints.
Three MEMORY
　　Memory creates a table using the content that exists in it. Each memory table actually corresponds to a disk file in the format of. frm. The memory type of table access is very fast because it is placed into the data, and the hash index is used by default, but once the server shuts down,

The data in the table will be lost, but the table will continue to exist. By default, the memory data table uses a hash index, which makes the "equality comparison" very fast, but the "range comparison" is much slower. Therefore, the hash index values are suitable for use in the "=" and "<=>" operators,

Not suitable for use in the "<" or ">" operators, but also not in the order by sentence. If you do want to use the < or > or the betwen operator, you can use the Btree index to speed up. The data rows stored in the Memory data table use the same length format,

So speed up processing, which means you can't use a variable length data type such as BLOB and text. VARCHAR is a variable-length type, but because it is a fixed-length char type within MySQL, you can use

Select  from Mans order by ID;

Use the using Hash/btree to specify a specific to index.

using hash on tab_memory (city_id);

When you start the MySQL service, use the--init-file option to put a statement such as insert into...select or load data infile into this file to load the table from a persistent data source when the service starts.
The server needs enough memory to maintain the memory table at the same time, and when it is no longer used, it is necessary to execute DELETE from or TRUNCATE table or to delete the entire table when it is not in use.
The size of the amount of data placed in each memory table is constrained by the max_heap_table_size system variable, the initial value of this system variable is 16M, and the max_rows clause can be used to specify the maximum number of rows in the table when creating the memory table.

SQL Getting Started Classic (fifth edition) Ryan Stephens study notes follow-up-storage engine