5. Storage Engine

1. Storage engine (table type)

To view the current default storage engine:

Show variables like '%engine% ';

To view the supported storage engines:

Show engines;

To modify the storage engine for a table:

ALTER TABLE T1 engine = InnoDB;

[Mysqld]

Default_storage_engine=myisam ---- Modifying the database default engine

2. Common engine introduction

MySQL Storage engine comparison-Dream dream-Iteye technology website

MyISAM

Most commonly used, with high insertion, query speed

Support Fulltext

Transactions are not supported, foreign keys cannot be built

The security requirements of the article table, the News table and so on are not high

The integrity and concurrency requirements for transactions are not very high

Applications are primarily query and insert, with few update and delete operations

the data in the MySQL database is in the form of a file stored on the disk, you can CP the way the files are backed up

stored in my.cnf datadir directory

1 sheets of MyISAM 3 files

table1.frm Storage Table Structure table1.myd storing data table1.myi Storing index

Perform regular optimize table T1 to improve performance and defragment disk space

The MyISAM table also supports different storage formats in 3:

           1         static table             2         dynamic table           3         compression table

static tables are the default storage format, and fields in static tables are non-variable-length fields, with the advantage that storage is very fast, easy to cache, failures are easy to recover, and the disadvantage is that it takes up more space than a dynamic table. (Note: In the case of storage, when the width of the column is not enough, a space is used to make up, when the access time does not get these spaces)

The field of a dynamic table is longer, and the advantage is that it takes up relatively little space, but frequent updates to delete records are fragmented, require regular performance improvements, and are relatively difficult to recover in the event of a failure.

Compact tables take up less disk space and each record is individually compressed, so there is very little access expense.

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------

InnoDB

Support transactions, support for foreign KEY constraints

Less efficient than myisam,innodb writing data, and consumes more disk space

order form, Money form, Bill form, train ticket form , the security requirement is high, needs the transaction function, should choose InnoDB

Modify storage mode

ibdata1 Table space files

ib_logfile0 log Files ib_logfile1 log Files

ls/var/lib/mysql/ds/

T1.frm file only (save table structure)

Data and indexes are saved in a tablespace file

Innodb_data_home_dir is not specified by default

The default is to create a autoextend shared tablespace file under DataDir ibdata1

Stores the data and index of the InnoDB table for all libraries in a shared tablespace file ibdata1, ibdata1 file does not shrink automatically after data is deleted

In the case of disk monitoring, the alarm may be constant, but in fact MySQL can run well

A physical backup of a single database is also a problem, and typically only mysqldump data is exported

[email protected] database]#/usr/local/mysql56/bin/mysqldump ds stu >/tmp/db

Specify tablespace files

Innodb_data_home_dir=

Innodb_data_file_path=/database2/ibdata1:100m;/tmp/ibdata2:50m:autoextend

You can also refer to ibdata2 under the/database2.

The last data file must be automatically expanded, and only the last file will allow automatic expansion

Data is first placed in the Ibdata1, after 100M, the data is placed in the IBDATA2, after the 50MB, IBDATA2 will automatically grow

1.rm-f/database/ib_log*--may be

2. Add 2 configuration Items

3. Build folder/database2 and Chown

4. Start the service

Later only with the new designated IBDATA1 and Ibdata2, the original/database/ibdata1 is useless, but it also has data, to first cut back the data inside it to export

InnoDB has two ways to manage tablespaces:

1. Shared table space, 5.5 default

2. Stand-alone table space, 5.6 default

Stand-alone table space:

1. Each table has a self-contained table space file

2. The data and index of each table will exist in the table space file

drop table automatically reclaims tablespace , delete large amount of data can be =innodb by ALTER TABLE T1 engine; Reclaim space

Shared table spaces have a slight advantage in insert operations, and none of the others have a separate table space.

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------

Memory (Heap)

The data in the table is placed in memory, no actual disk is saved, and the data in the table is lost once the service is closed

The server needs enough memory to store the storage meter

Very high efficiency of adding and deleting and checking

When data in the memory table is no longer needed, you should delete from or TRUNCATE TABLE or drop table

The memory table only corresponds to 1. frm files and accesses very quickly

Support Btree and hash index, using hash index by default

For temporary transit tables, you can use the memory engine, the fastest

Intermediate result tables primarily for statistical operations

Selectcase age, then salary*1. 2 when salary*1.3 else salary*1.4 endfrom ds.stu;

This is a temporary result that can be stored in the memory table T1

CreateTable T1 () engine = memory;

Insertinto T1 Select ...

insert   into   t1    select  stuid stuname age age when and salary*1.2 when and salary*1.3 elsesalary*1.4  end  as salary from Ds.stu

--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------

Merge (Mrg_myisam)

The merge storage engine is a combination of a set of MyISAM tables

These MyISAM tables must be structurally identical and the merge table itself has no data

The query, update, and delete of the merge table is actually performed on the internal MyISAM table.

The insert operation on the merge table is defined by the INSERT_METHOD clause that defines the inserted table, which can have 3 different values

Insert_method=first|last|no

First inserted on the 1th table, last inserted on the final table

No or do not define this clause to indicate that the insert operation cannot be performed on this merge table

Dropmerge table, just delete the definition of the merge table and have no effect on the internal MyISAM table

The merge table's corresponding file on disk

CreateTable Payment_11

(

Idint,

Moneydec (6, 1)

) Engine=myisam;

CreateTable Payment_12

(

Idint,

Moneydec (6, 1)

) Engine=myisam;

CreateTable Payment_all

(

Idint,

Moneydec (6, 1)

) Engine=merge union= (Payment_11, Payment_12) insert_method=last; 11, 12 does not exist when it is created

Typically, we use the merge table to transparently query and update multiple tables

From for notes (Wiz)

5. Storage Engine