Some basic knowledge about databases (1) ------ database engine, ------ Database Engine

Some basic knowledge about databases (1) ------ database engine, ------ Database Engine

MySQL database provides 13 different storage engines to handle different data processing. Most people who have used MySQL should know the two storage engines commonly used by MySQL: MyISAM and InnoDB. In most cases, MyISAM exists as the default storage engine of MySQL. In fact, most of us use MySQL's default MyISAM instead of configuring the storage engine. If you need to use a specific storage engine on a specific table, you can use the following command line:

CREATE TABLE tablename (column1, column2, [etc...]) ENGINE = [storage_engine].

So what is the database engine?

Simply put, a database storage engine is used to store, process, and query data tables. There is no perfect storage engine in this world, but for most applications, the default MyISAM is enough. Of the 10 built-in storage engines in MySQL, not all engines are available. You can use the following command to query the storage engine provided by your MySQL Server:

Code:

mysql -uroot -pPassword:mysql> show engines;

This command will list the storage engines supported by your server:

• MyISAM
• InnoDB
• MERGE
• MEMORY (HEAP)
• BDB (BerkeleyDB)
• EXAMPLE
• ARCHIVE
• CSV
• BLACKHOLE
• ISAM

You can select a storage engine based on your application requirements. For example, if you need to store a large amount of log data, you can use the ARCHIVE storage engine that only supports INSERT and SELECT operations. You can set a specific storage engine for your database server, database, or table. This flexible configurability is also why MySQL is more popular than databases that only support a single storage engine.

MyISAM

MyISAM is actually a branch of the ISAM storage engine. If your application does not require TRANSACTION or row-level locks (it only provides table-level locks), MyISAM is the best solution. MyISAM is particularly suitable for INSERT/UPDATE operations with fewer SELECT operations. When your application requires a large number of INSERT/UPDATE operations, you need to consider whether you should change to a storage engine, because table-level locks will cause performance problems for this operation.

The maximum number of lines supported by MyISAM is ~ 4.29E + 09. the maximum number of indexes for each table is 64. MyISAM also provides full index support for TEXT/BLOB columns, which facilitates search and other operations.

InnoDB

Compared with MyISAM, InnoDB provides more features to improve system performance. This also results in InnoDB spending more time on Initialization than MyISAM, but this brings far more benefits than the additional time spent on initialization. One major difference is that InnoDB provides row-level locks. This provides the possibility of concurrent INSERT, UPDATE, and DELETE operations. Unlike MyISAM, the next operation can be performed only after an operation is completed.

At the same time, InnoDB also provides the foreign key function. This ensures that the data in table 1 exists before you insert data into table 2. In addition, this will prevent you from deleting the data that table 2 depends on in table 1.

InnoDB also provides that the data cache has been indexed in the memory and on the disk, which can greatly improve the system performance. This may not be an ideal solution for low-memory systems, but it is not a problem for systems with sufficient memory.

MERGE
MERGE, the storage engine added from MySQL 3.23.25. It allows you to perform unified operations on a MyISAM set, just like operating a table. However, using this engine has some constraints. For example, all tables must be uniformly defined.

MEMORY (HEAP)
The HEAP Storage engine, also known as MEMORY, allows table creation in the MEMORY. The MySQL Server retains the table format, so that you can quickly create a "trash" table and then quickly read data for better processing. However, this makes the storage engine not suitable for long-term data processing.

BDB (BerkeleyDB)

The BerkeleyDB storage engine processes transaction-safe tables and uses hash-based storage systems. This storage engine is suitable for fast reading and writing certain data, especially data of different keys. However, this storage engine has many disadvantages, such as the slow speed of columns without indexes. It is also ignored for this reason. I still believe it can be used.

EXAMPLE

EXAMPLE: The storage engine added from MySQL 4.1.3. This storage engine mainly serves programmers. EXAMPLE provides the ability to create tables, but cannot insert or query information.

ARCHIVE
ARCHIVE, a storage engine added since MySQL 4.1.3, is often used to store large-scale data (no index required ). This storage engine only supports INSERT and SELECT operations, and all information is compressed. These features make ARCHIVE suitable for storing logs, transaction records, accounts, and so on. However, when reading data, the entire table needs to be decompressed and read before the data is returned. Therefore, this storage engine is most suitable for scenarios with low usage frequency.

CSV
CSV: The storage engine added since MySQL 4.1.4. data is stored in strings separated by commas. Therefore, this storage engine is not suitable for large-scale data storage or data tables that need to be indexed. This storage engine is suitable for converting data into spreadsheet files.

BLACKHOLE
This storage engine seems useless because it does not allow the storage or query of any data. Therefore, BLACKHOLE is usually used to test the database structure, index, and erase queries. You can still use the INSERTS command to insert data, but all the data is void.

ISAM
The most primitive storage engine is ISAM, which manages non-transactional tables. Later, it was replaced by MyISAM, and MyISAM is backward compatible, so you can forget this ISAM storage engine.

Summary
In general, there is no perfect database storage engine. For most applications and DBAs, InnoDB and MyISAM are enough. But remember that although InnoDB and MyISAM are quite common, they are not perfectly supported for all scenarios. Maybe other storage engines can well support your applications.

Next episode notice:

Database transaction isolation mechanism and features;

References:

1. http://www.linux.org/threads/an-introduction-to-mysql-storage-engines.4220/

Common Database Engines

1. Database Engine
Microsoft JET (Joint Engineering feature E) is the core element of embedded database functions provided by Access and Visual Basic. JET is an all-around relational database engine that can be used to process most small and medium databases. The so-called database engine is an interface between applications and database storage, it Abstracts database-related memory management, cursor management, error management, and other complex details into a highly consistent and simplified programming interface.
The Jet Database Engine is contained in a group of dynamic link library (DLL) files that are linked to Visual Basic programs at runtime. It translates application requests into physical operations on the. mdb file or other databases. It truly reads, writes, and modifies databases, and handles all internal transactions, such as indexing, locking, security, and integrity of references. It also contains a query processor used to receive and execute Structured Query Language (SQL) queries to implement the required database operations. In addition, the Jet Database Engine also contains a result processor used to manage the results returned by queries.
2. ODBC
Open DataBase Connectivity (ODBC) is a DataBase access standard defined by Microsoft. It provides a standard DataBase access method to access databases on different platforms. An ODBC application can access databases on a local PC or on a variety of heterogeneous platforms, such as SQL Server, Oracle, or DB2.
ODBC is essentially a set of database access APIs (application programming interfaces). However, programmers can access ODBC functions without having to understand ODBC functions, this is because development tools such as Visual Basic provide some objects that encapsulate ODBC functions.
3. OLE DB
Ole db is the latest database access interface developed by Microsoft. Microsoft defines it as the successor of ODBC. Similar to ODBC, ole db provides access to relational databases. On this basis, it extends some of the functions provided by ODBC. As a standard interface, ole db can access all types of data, including relational databases, dBase, and other ISAM (index sequence access method) type files, even E-MAIL, or Windows 2000 active directories.
Ole db applications can be divided into two types: ole db Provider and ole db Consumer. ole db users are applications that use the ole db interface, the ole db Provider is responsible for accessing the data source and providing data to ole db users through the ole db interface.

Who can introduce the database engine in detail?

SQL Server FullText Search (MSSQLSERVER): full-text Search service
If full-text index is available, SQL Server FullText Search is not enabled.

The SQL Server Agent is a task scheduler and alarm manager. In actual applications and environments, you can define periodic activities as a task, with the help of the SQL Server Agent, you can use the SQL Server Agent to automatically run SQL Server. If you are a system administrator, you can use the SQL Server Agent to notify you of some warning information, to locate problems and improve management efficiency. The SQL Server Agent mainly includes the following components: Job, alarm, and operation.
Perform the following steps to configure the SQL Server Agent in SQL Server Enterprise Manager:

(1) start SQL Server Enterprise Manager, log on to the specified Server, open the Management folder, and start SQL Server Agent.
(2) Right-click the SQL Server Agent icon and select Properties from the pop-up menu. Open the SQL Server Agent Properties dialog box and select the General tab. 17-1. The meanings of each option are as follows:
System account: The System account under which the SQL Server Agent runs. The System account is a member of the sysadmin role;
This account: defines which NT account the SQL Server Agent runs under. This account must be the sysadmin role on the SQL Server Agent Server. If This option is selected, the Password must be provided. Select this option in the following cases:
Transfers events to application logs of other NT computers;
How to Create a job that uses resources on multiple computers;
Notify the operator by email or paging.
The Mail Profile has a valid Mail configuration file name. This file stores the configuration information for the normal operation of the Mail system configured for the SQL ServerAgent running account through SQL Mail;
Save copies of the sent messages in the Sent Items folder: Specifies the directory where the sent messages are stored;
File name: Specifies the SQL Server Agent Log File name. The default name is C: \ Mssql7 \ Log \ Sqlagent. out;
Error message pop-up recipient specifies the name of the receiver that receives the pop-up Error message from the network. These Error messages are written to the Error log by the SQL Server Agent.
(3) Select the Advanced Tab
Auto restart SQL Server if it stops unexpectedly: indicates that if SQL Server stops running unexpectedly, it will be restarted automatically;
Auto restart SQL Server Agent if it stops unexpectedly: indicates that if the SQL Server Agent stops running unexpectedly, it will be restarted automatically;
Forward events to a different server: Forward the event to another server. Select the receiving Server from the drop-down list next to the Server;
Unhandled events: Forward events that are not processed on the local server; All indicates All events;
If error has sever... the remaining full text>