MySQL performance tuning and architecture design Note: two storage engine

3.1 Overview:

1,myisam is the default engine of the previous version of MySQL 5.5, and after 5.5 The default storage engine is InnoDB

2. Check the MySQL storage Engine command, and enter show engines at the mysql> prompt; Field support is: Default means storage engine

3, set INNODB as the default engine: In the configuration file my.cnf [mysqld] under the Add DEFAULT-STORAGE-ENGINE=INNODB sentence

3.2 MyISAM Storage Engine Overview:

1,myisam Storage Engine table in the database, each table is stored as a three physical file named after the table name. frm,. Myi,myd the role of three documents in chapter II; Note that, regardless of how many indexes a table has, it is stored in the MYI file.

2, MyISAM supports the following three types of indexes:

1. B-tree Index

B-tree index, as the name implies, is that all the index nodes are stored according to the balance Tree data structure , all the index data nodes are in the leaf node.

2. R-tree Index

The R-tree index is stored in a different way from the B-tree index, and is primarily designed to index fields of storage space and multi- dimensional data, so the current MySQL version only supports the geometry type of fields for indexing.

3. Full-text Index

The Full-text index is the full-text index that we say, and his storage structure is b-tree. The main thing is to solve inefficient problems that we need to use like queries.

after an error occurs with a table file for the 3,myisam storage engine, it affects only the table, not the other tables, and not can affect other databases.

4, if we are running in the process of finding a MyISAM table problem, You can try to verify with the check Table command online, and you can try to fix it by repair Table command

To the complex. In the case of a database shutdown, we can also detect or repair some (or some) of the tables in the database by using the Myisamchk tool . However, it is strongly recommended that you do not need to repair the table easily, before repair, as far as possible to do the backup work, so as not to cause unnecessary consequences

3.3 Innodb Storage Engine Introduction

1. Support Transaction Installation

2, data multi-version read

3, improvement of locking mechanism--row-level lock

4. Implement FOREIGN key

The physical structure of 5,INNODB is divided into two parts:

<1> Data files (table data and index data):

tablespace: A shared tablespace, where all tables and index data are stored in the same tablespace (one or more data files), specified by Innodb_data_file_path, the number of increments

The file requires a shutdown restart.

The other is the exclusive table space, where the data and indexes of each table are stored in a in a separate. ibd file.

6, although we can set our own tables using shared table space or exclusive table space, but the shared table is emptymust exist because Innodb's undo information and some other metadata information are stored in a shared table spaceinside the. The data file for a shared tablespace can be set to a fixed size and can be automatically scaled up to two forms, automaticallythe extended form of the file can set the maximum size of the file and the amount of each display. When you create an auto-extended data file, it is advisable to add the maximum size attribute, one reason is that the file system itself is a certain size limit (butis Innodb do not know), there is another reason is the convenience of self-maintenance. In addition, INNODB can not only use the textthe original block device, which is what we often call a bare device. when our file table space is about to run out, we have to add data files for it, of course, only sharetable space has this action. Adding a data file to a shared table space is a simple operation, justthe Innodb_data_file_path parameter is followed by a standard format to set the file path and related properties, but hereOne thing to note is that INNODB does not create a directory when creating a new data file, if the specified target

If the record does not exist, an error will be made and cannot be started. Another more troubling thing is Innodb after adding data files to the shared table space , it is necessary to restart the database system to take effect, and if you use a bare device, you need to restart two times. That's one of the reasons I've never been too fond of using shared table spaces for exclusive tablespaces.

MySQL performance tuning and architecture design Note: two storage engine