T-SQL statement and several database engines, t-SQL Database Engine

T-SQL statement and several database engines, t-SQL Database Engine

Create a table

Note:

A. Self-Growth

B. database engine,

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications.
MYISAM
MYISAM is the mysql isam extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MYISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations. The cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MYISAM also has some useful extensions, such as the MYISAMCHK tool used to fix database files and the MYISAMPACK tool used to restore wasted space.
MYISAM emphasizes fast read operations, which may be the main reason why MYSQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format.
HEAP
HEAP allows only temporary tables in memory. The HEAP is faster than ISAM and MYISAM because it stores HEAP in memory, but the data it manages is unstable. If it is not saved before it is shut down, all data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete a table after the table is used up.
INNODB and BERKLEYDB
INNODB and BERKLEYDB (BDB) database engines are both direct products that make MYSQL flexible technology. This technology is MySql ++ API. When using MySql, almost every challenge you face comes from the fact that ISAM and MYIASM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MYISAM engines, INNODB and BDB include support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

 

Database Engine Introduction

The MySQL Database Engine depends on how MySQL is compiled during installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines: ISAM, MYISAM, and HEAP. The other two types of INNODB and BERKLEY (BDB) are also frequently used. If the technology is superb, you can use MySQL + API to build an engine. The following describes several database engines:

ISAM: ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of ISAM are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications.
MyISAM: MyISAM is the MySQL ISAM extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations, the cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the MyISAMCHK tool used to fix database files and the MyISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. An important defect in MyISAM format is that data cannot be restored after the table is damaged.
HEAP: the HEAP allows temporary tables that reside only in the memory. HEAP is faster than ISAM and MYISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up.
InnoDB: the InnoDB database engine is a direct product that creates MySQL flexibility. This technology is MYSQL + API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MyISAM engines, InnoDB includes support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.
If you feel that you are indeed highly skilled, you can also use MySQL + API to create your own database engine. This API provides you with the functions of operation fields, records, tables, databases, connections, and security accounts, as well as all the other numerous functions required to create DBMS such as MySQL. An in-depth explanation of the API is beyond the scope of this article, but it is important to understand the existence of MySQL + API and the technology behind its swap engine. It is estimated that the plug-in database engine model can even be used to create a local XML provider for MySQL ). (Any MySQL + API developer who reads this article may regard this as a requirement .)
Differences between MyISAM and InnoDB
InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

The following are some differences between details and specific implementations:
1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) fromtable, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) clause contains the where condition, the operations on the two tables are the same.
3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table frommaster operation does not work for InnoDB. The solution is to change the InnoDB TABLE to the MyISAM TABLE first, and then the InnoDB TABLE after the data is imported, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, updatetable set num = 1 where name like "a %"
The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.
From the perspective of MySQL users, Innodb and MyISAM both prefer to use MySQL, but from the perspective of my current O & M database platform, we need to meet the following requirements: 99.9% stability, for convenient scalability and high availability, MyISAM is definitely my first choice.

The reason is as follows:
1. First, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.
2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.
3. From the platform perspective, it is often the next two months that application developers may accidentally update the where write range of a table, which makes the table unusable, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. Don't tell me to let Innodb export xxx regularly. SQL mechanism backup, because the data volume of the smallest database instance on my platform is basically dozens of GB.
4. From the application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.
5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because the dictionary data files cannot be used by the other party only.
6. If Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to Innodb, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.
7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some selectcount (*) operations on the merge table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.
Of course, Innodb is not absolutely unnecessary. I use Innodb to simulate stock trading projects with transaction projects, for example. When more than 0.2 million of active users are active, it is also very easy to cope, therefore, I personally like Innodb, but if I start from the database platform application, I will still start with MyISAM.
In addition, some people may say that MyISAM cannot defend against too many write operations, but I can make up for it through the architecture, saying that my existing database platform capacity is: the total volume of Master/Slave Data is more than several hundred TB, and more than million pv dynamic pages are generated every day. There are also several major projects that do not count as the total number of PVS for calling through data interfaces, (this includes a large project where a single database processes tens of millions of queries every day because memcached was not deployed in the early stage ). The average load of my entire database server is between 0.5 and 1.

In general, MyISAM is suitable:
(1) perform a lot of count calculations;
(2) insertion is not frequent, and queries are very frequent;
(3) No transactions.

InnoDB is suitable:
(1) high reliability requirements or transactions;
(2) When tables are updated and queried frequently, and tables are locked for a great opportunity, specify the creation of the Data Engine.
The switch that makes all flexibility possible is to provide the MySQL extension-TYPE parameter for ansi SQL. MySQL allows you to specify the database engine at the table layer, so they sometimes refer to table formats. The following sample code shows how to create tables using the MyISAM, ISAM, and HEAP engines. Note that the code for creating each table is the same except for the final TYPE parameter, which is used to specify the Data Engine.

C. Case-insensitive

D. The last line does not contain commas (,).