Iii. Table type
Many mysql users may be surprised that MySQL does provide users with 5 different table types, such as DBD, heap, isam, merge, and myiasm. DBD is classified as transaction security, while others are non-transaction security.
3.1. Transaction Security
DBD
The Berkeley dB (DBD) table is a table that supports transaction processing, developed by sleepycat (http://www.sleepycat.com. It provides the long-awaited MySQL feature-transaction control. Transaction control is a very valuable feature in any database system, because they ensure that a group of commands can be successfully executed.
3.2 non-Transaction Security
Heap
The heap table is the fastest table to access data in MySQL. This is because they use a hash index stored in dynamic memory. Another key point is that if MySQL or the server crashes, data will be lost.
Isam
Isam tables are the default table types of earlier MySQL versions until myiasm is developed. We recommend that you do not use it any more.
Merge
Merge is an interesting new type that appears after 3.23.25. A merge table is actually a set of identical MyISAM tables and is merged into a table mainly for efficiency reasons. This improves speed, search efficiency, repair efficiency, and saves disk space.
Myiasm
This is the default table Type of MySQL. It is based on iasm code, but there are many useful extensions. Why is myiasm better:
Myiasm tables are smaller than iasm tables, so fewer resources are used.
Myiasm tables can be transplanted at the binary layer on different platforms.
Larger key size, larger key size limit.
3.3 specify the table Type
You can specify the table type when creating a table. In the following example, create a heap table:
--------------------------------------------------------------------------------
Mysql> Create Table email_addresses type = heap (
-> Email char (55) not null,
-> Name char (30) not null,
-> Primary Key (email ));
--------------------------------------------------------------------------------
Bdb tables require some configuration work. For more information, see http://www.mysql.com/doc/ B /d/bdb_overview.html.
3.4. More table types.
To make MySQL management more interesting, MySQL 4.0 will provide two new table types, Innobase and gemeni.
4. optimization tools
The MySQL server itself provides several built-in commands to help with optimization.
4.1. Show
You may be interested in knowing what the MySQL server actually has. The following command provides a summary:
--------------------------------------------------------------------------------
Mysql> show status;
--------------------------------------------------------------------------------
It provides a fairly long list of state variables and their values. Some variables include the number of customers with exceptional termination, the number of connections with exceptional termination, the number of connection attempts, the maximum number of concurrent connections, and a large amount of other useful information. This information is of great value for identifying system problems and inefficiency.
Show can do more. It can display valuable information about log files, specific databases, tables, indexes, processes, and permission tables. For more information, see the MySQL manual.
4.2. Explain
When you are facing a SELECT statement, explain how the SELECT command is processed. This is not only helpful for deciding whether to add an index, but also for determining how a complicated join is processed by MySQL.
4.3 optimize
The optimize statement allows you to restore space and merge data file fragments. This is especially important after a large number of updates and deletions are performed on tables that contain long rows. Optimize currently only works in myiasm and bdb tables.