MySQL5.5 In the future, it will be used by default InnoDB Storage engine , among InnoDB and BDB Provide transaction safety watch , Other storage engines are non transactional security tables .
To modify the default engine , You can modify... In the configuration file default-storage-engine. Can pass :show variables like 'default_storage_engine'; View the current database to the default engine . command :show engines and show variables like 'have%' You can list the engines supported by the current database . among Value Is shown as disabled Indicates that the database supports this engine , It is disabled when the database is started . stay MySQL5.1 in the future ,INFORMATION_SCHEMA There is a... In the database ENGINES Table of , The information it provides is similar to show engines; The sentence is exactly the same , You can use the following statement to query which storage engines support transaction processing :select engine from information_chema.engines where transactions = 'yes';
Can pass engine Keyword specifies the engine used when creating or modifying a database .
The main storage engine :MyISAM、InnoDB、MEMORY and MERGE Introduce :
When creating a table, use engine=... or type=... To specify the engine to use .show table status from DBname To view the specified table to the engine .

( One )MyISAM
It does not support transactions , Foreign keys are also not supported , Especially fast access , There is no requirement for transaction integrity or SELECT、INSERT The main application can basically use this engine to create tables .
Every MyISAM Stored on disk as 3 File , Where file name and table name are the same , But the extensions are :

  • .frm( Storage table definition )
  • MYD(MYData, Store the data )
  • MYI(MYIndex, Storage index )

   Data files and index files can be placed in different directories , Average distribution IO, Get faster . To specify the path of the data file and index file , You need to create a table through DATA DIRECTORY and INDEX DIRECTORY The statement specifies , File paths need to use absolute paths .
   Every MyISAM Every watch has a sign , Server or myisamchk The program is checking MyISAM This flag is set when the data table is created .MyISAM The table also has a flag to indicate whether the data table has been closed normally since it was last used . If the server thinks it's down or crashed , This flag can be used to determine whether the data table needs to be checked and repaired . If you want to make this check automatic , You can use it when you start the server --myisam-recover The phenomenon . This allows the server to open one at a time MyISAM Data table is the mark of automatically checking data table and repairing it if necessary .MyISAM Tables of type can be corrupted , have access to CHECK TABLE Statement to check MyISAM Table health , And use REPAIR TABLE Statement to fix a corruption to MyISAM surface .
  MyISAM The table also supports 3 Different storage formats :

  • static state ( Fixed length ) surface
  • Dynamic table
  • Compression meter

   The static table is the default storage format . Fields in static tables are all non variable length fields , So every record is a fixed length , The advantage of this storage method is that the storage is very fast , Easy to cache , It's easy to recover in case of failure ; The disadvantage is that it usually takes up more space than a dynamic table . The static table will complement the space according to the width defined by the column when storing data , But you don't get these spaces when you visit , These spaces have been removed before being returned to the application . And also notice that : In some cases, you may need to return the space after the field , When this format is used, the space after it will be automatically processed .
   Dynamic tables contain variable length fields , Records are not fixed length , The advantage of this storage is that it takes up less space , However, frequent updating and deletion of records will cause fragmentation , It needs to be carried out on a regular basis OPTIMIZE TABLE Sentence or myisamchk -r Command to improve performance , And it's relatively difficult to recover in case of failure .
   The compressed table consists of myisamchk Tool creation , Take up a very small space , Because each record is compressed individually , So there's only a very small cost of access . 
( Two )InnoDB
InnoDB The storage engine provides with commit 、 Transaction security of rollback and crash recovery capability . But contrast MyISAM Storage engine for ,InnoDB Write processing is less efficient and takes up more disk space to retain data and indexes .
1) Auto grow Columns :
InnoDB The auto growing columns of the table can be inserted manually , But if it's empty or 0, The actual insertion is automatically increased to the value . Can pass "ALTER TABLE...AUTO_INCREMENT=n;" Statement to force the start value of the autogrow value , The default is 1, But the force to the default value is stored in memory , This value will be lost when the database is restarted . have access to LAST_INSERT_ID() Query the value used by the current thread to insert the record last . If you insert more than one record at a time , Then the auto growth value used by the first record is returned .
about InnoDB surface , Autogrow columns must be indexes . If it's a composite index , It must also be the first column of the composite index , But for MyISAM surface , Autogrow columns can be other columns of a composite index , After inserting the record like this , The auto growing columns are sorted and incremented according to the combined index .
2) Foreign key constraints :
MySQL The only storage engine that supports foreign keys is InnoDB, When creating a foreign key , The parent table must have a corresponding index , When creating a foreign key, the sub table will also automatically create the corresponding index .
      When creating an index , Can be specified in delete 、 When updating the parent table , The corresponding operation on the sub table , Include restrict、cascade、set null and no action. among restrict and no action identical , It refers to the restriction when the sub tables are related , The parent table cannot be updated ;casecade Indicates that when the parent table is updated or deleted , Update or delete the record corresponding to the sub table ;set null  When the parent table is updated or deleted , The fields corresponding to the sub table are set null.
   When a table is referenced by a foreign key created by another table , Then the index or primary key corresponding to the table is forbidden to be deleted .
   have access to set foreign_key_checks=0; Temporarily turn off foreign key constraints ,set foreign_key_checks=1; Open constraints .
( 3、 ... and )MEMORY
memory Use what's in memory to create a table . Every MEMORY The table actually corresponds to a disk file , The format is .frm.MEMORY Type of table access is very fast , Because the data is in memory , And by default HASH Indexes , But once the server is shut down , The data in the table will be lost , But the table will continue to exist .
      By default ,memory Tables use hash indexes , Use this index to “ Equal comparison ” Very fast , But yes. “ Range comparison ” It's much slower . therefore , Hash index values are suitable for "=" and "<=>" In the operator of , Not suitable for use in "<" or ">" In the operator , It's also not suitable for order by In words and sentences . If you really want to use "<" or ">" or betwen The operator , have access to btree Index to speed up .
   Stored in MEMORY The data rows in the data table are in the same length format , So speed up processing , This means that you can't use BLOB and TEXT Such a variable length data type .VARCHAR It's a variable length type , But because it's in MySQL The interior is fixed in length CHAR type , So you can use .

create table tab_memory engine=memory select  id,name,age,addr from  man order by  id;

   Use USING HASH/BTREE To specify a specific index .

create index mem_hash using  hash on  tab_memory(city_id);

   Start up MySQL Use when serving --init-file Options , hold insert or load data infile Such statements are put into this file , You can load tables from persistent data sources when the service starts .
   The server needs enough memory to keep the server in use at the same time MEMORY surface , When not in use MEMORY Table time , To release MEMORY Memory occupied by the table , Should carry out DELETE FROM or truncate table Or delete the entire table .
   Every MEMORY The amount of data placed in the table , suffer max_heap_table_size Constraints on system variables , The initial value of this system variable is 16M, At the same time, creating MEMORY You can use MAX_ROWS Clause to specify the maximum number of rows in the table .
( Four )MERGE
merge The storage engine is a set of MyISAM Combination of tables , these MyISAM The table structure must be exactly the same ,MERGE There is no data in the table , Yes MERGE Types of tables can be queried 、 to update 、 Deleted actions , These operations are actually internal MyISAM Table operation . For MERGE Table insert operations , It's based on INSERT_METHOD Clause defines the inserted table , There can be 3 Different values ,first and last Value causes the insert operation to be applied to the first or last table , Do not define this clause or NO, That means you can't do this MERGE Table to insert . It can be done to MERGE table drop operation , This operation is just deleting MERGE The definition of the table , No impact on internal tables .MERGE Keep... On disk 2 In a MERGE Table name start file :.frm Definition of file storage table ;.MRG The file contains information about the composite table , Include MERGE Which tables are made up of , The basis for inserting data . It can be modified by .MRG File to modify MERGE surface , But after modification, we have to pass flush table Refresh .

create  table  man_all(id int , name  varchar (20))engine=merge union =(man1,man2) insert_methos= last ;

MySql More about storage engine

  1. database --&gt; MySQL Storage engine introduction

    MySQL Storage engine introduction MyISAM yes MySQL The default database engine for (5.5 Prior to version ), From the early ISAM(Indexed Sequential Access Method: Indexed sequential access methods ) Improved by . although ...

  2. {MySQL Storage engine introduction } One The storage engine explains Two MySQL Storage engine classification 3、 ... and The use of different storage engines

    MySQL Storage engine introduction MySQL The storage engine Contents of this section One The storage engine explains Two MySQL Storage engine classification 3、 ... and The use of different storage engines One The storage engine explains First of all, make sure that , The concept of a storage engine is MySQL There's only , No ...

  3. MYSQL Storage engine introduction -- Application scenarios

    MySQL Storage engines usually have 3 Kind of ? What are their respective characteristics ? What are the application scenarios ? MySQL5.5 In the future, it will be used by default InnoDB Storage engine , among InnoDB and BDB Provide transaction safety watch , Other storage engines are non transactional security tables . To modify ...

  4. mysql Storage engine introduction

    One   The storage engine explains First of all, make sure that , The concept of a storage engine is MySQL There's only , Not all relational databases have the concept of a storage engine , Later we will say , But now make sure that . Before making it clear what a storage engine is , Let's start with a metaphor , ...

  5. mysql Storage engine introduction 1

    1.1  The use of storage engines Each table in the database is ( When the table is created ) Specified storage engine to handle . The engine available to the server depends on the following factors : MySQL Version of How the server is configured during development Startup options To find out what storage is in the current server ...

  6. mysql Storage engine introduction 2

    understand MYSQL Of all know , stay MYSQL Create any data table in , There are corresponding tables in the database directory corresponding to its data directory .frm file ,.frm The file is used to hold the metadata of each data table (meta) Information , Including the definition of table structure, etc ,.frm ...

  7. mysql Storage engine introduction , Indexes

    difference : MyISAM Types do not support advanced processing such as transaction processing , and InnoDB Type of support .MyISAM Type tables emphasize performance , The ratio of execution InnoDB The type is faster , Transaction support is not provided , and InnoDB Provide transaction support, external key, etc ...

  8. mysql Storage engine 、 Business

    MySQL Storage engine introduction file system A mechanism by which operating systems organize and access data . A file system is software . File system type ext2  ext3  ext4  xfs data Whatever file system you use , The data content doesn't change Different ...

  9. SQL Learning notes 3 ( Add -1) And MySQL Storage engine

    Read the directory One What is a storage engine Two mysql Supported storage engines 3、 ... and Using the storage engine One What is a storage engine mysql The library built in ===> Folder Tables created in the library ===> file What we use to store data in real life ...

Random recommendation

  1. HDU 5047 Sawtooth( Large number simulation ) Shanghai District tennis 1006

    Topic link : Problem solving report : Ask a question “M” How many blocks can a rectangular plane be divided into . The input has n individual “M", present ...

  2. Magicodes.WeiChat—— Design and implementation of multi tenant

    Summary multi-tenancy (Multi Tenancy/Tenant) It's a software architecture , Its definition is : Running a single application instance on a single server , It serves multiple tenants . This framework uses a shared database . share Schema. Data from shared data tables ...

  3. Objective-C The elegant name of ( turn )

    There are only two hard things in Computer Science: cache invalidation and naming things. In computer science, there are only two ...

  4. Java [Leetcode 40]Combination Sum II

    Title Description : Given a collection of candidate numbers (C) and a target number (T), find all unique combinati ...

  5. HW4.21

    import java.util.Scanner; public class Solution { public static void main(String[] args) { Scanner i ...

  6. css Layout : Two columns Adaptive height

    Use only css Realization There are two ways , One is through relative positioning , Get the height of the father element by absolute positioning , One is through margin-bottom:-999em;padding-bottom: 999em; The father element goes beyond the hidden ...

  7. nodejs Module learning : express-session analysis

    nodejs Module learning : express-session analysis nodejs It's growing fast , from npm You can see the number of packages hosted above . But on the other hand , It also reflects nodejs Our foundation is not solid , It needs developers to create ...

  8. mysql Develop basic series 17 Stored procedures and functions ( On )

    One . summary Stored procedures and functions are compiled in advance and stored in a database sql Statement set , It can simplify a lot of work for application developers , Reduce data transmission between database and application server , It's good to improve the efficiency of data processing . The difference between stored procedures and functions is ...

  9. sysctl -p Reload file /etc/sysctl.conf -a All the parameters -w A temporary designation

    sysctl The command is used to configure kernel parameters at run time , These parameters are located in /proc/sys Under the table of contents .sysctl Configuration and display in /proc/sys Kernel parameters in the directory . It can be used sysctl To set or reset networking features , Such as IP forward .IP broken ...

  10. inline Modify the problem of virtual function

    Whether virtual functions can be inlined ? Generally speaking ,inline It's compile time behavior , A virtual function is the behavior of a program at execution time , Therefore, compilers generally refuse to inline virtual functions !