MySQL basics, storage engine, and common data types

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

This article will mainly explain the basic knowledge of the database, introduced the MySQL storage engine, and finally said the MySQL common data type

1. Basic Knowledge

1.1. Database Overview

• To put it simply: a database or DB is a container for storing and managing data;

• Strictly speaking: A database is "a container that organizes, stores, and manages data according to a certain data structure."

• Summary: Data is always at the heart of the database.

1.2. Relational database management system

• Through the "Database management system", database users can easily implement various database objects in the Database container access (add, delete, change, check and so on), and can easily complete the maintenance of the database (backup, recovery, repair and other operations)

• The database user cannot obtain the specific contents of the database file directly through the operating system;

• The database management system provides the interface for database users to manage and control various database objects and database files in database container by invoking the service of process management, memory management, device management and file management of the operating system.

1.3. relational database

The relational database is a database based on the relational database model, which uses concepts and methods such as set algebra to process data in a database, and is organized into a set of formally descriptive tables in which the essence of the form is the special collection of data items. The data in these tables can be accessed or re-convened in many different ways without the need to reorganize the database tables. The definition of a relational database results in a table of metadata or a formal description of tables, columns, scopes, and constraints. Each table (sometimes referred to as a relationship) contains one or more data types that are represented by columns. Each row contains a unique data entity, which is the kind that is defined by the column.

Basic Features

• relational database

In a given field of application, the set of links between all entities and entities forms a relational database.

• Types and values of relational databases

The type of relational database is called relational database schema, which is the description of relational database, the definition of several domains, and several relational patterns defined on these domains.
The value of a relational database is the set of relationships that these relational patterns correspond to at some point, usually referred to as relational databases.

1.4. Structured Query Language SQL

Structured Query Language SQL (Structured Query Language) is a language between relational algebra and relational calculus, and its functions include querying, manipulating, defining and controlling four aspects, and it is a common and powerful relational database standard language. At present, SQL language has been identified as the international standard of relational database system, and is adopted by most commodity relational database system. In the SQL language, you specify what you want to do instead of how to do it, and you don't need to tell SQL how to access the database, just tell SQL what the database needs to do. You can use SQL statements for data controls at design or run time.

1.5. Architecture of SQL database
SQL database architecture is basically a three-level schema structure

SQL terminology differs from traditional relational model terminology. In SQL, the outer pattern corresponds to the view, the pattern corresponds to the base table, the tuple is called the row, and the property is called the column. The internal mode corresponds to the storage file. The architecture of the SQL database has the following characteristics:

• The SQL Schema (schema) is a collection of tables and constraints.

• A table is a collection of rows (row), each row is a sequence of columns (column), and each column corresponds to one data item.

• A table can be a basic table, or it can be a view. A base table is a table that is actually stored in the database. A view is a table that is exported from a base table or other view, which itself is not stored independently in the database, meaning that only the definition of the view is stored in the database and does not hold the view's data, which is still stored in the base table of the exported view, so the view is a virtual table.

• A base table can span one or more stored files, a storage file can also hold one or more base tables, a table can have several indexes, and the index is stored in the file. Each storage file corresponds to a physical file on the external storage, and the logical structure of the stored file makes up the internal schema of the relational database.

• The user can use SQL statements to query the view and base tables, and other operations. In the user's opinion, the view is the same as the base table, which is the relationship (that is, the table).

• The SQL user can be an application or an end user. SQL statements can be embedded in programs in the host language, with languages such as Fortran, Cobol, Pascal, pl/i, C, and Ada, and the SQL language can be used as a standalone user interface for end users in an interactive environment.

1.6,the composition of SQL

• This part of the data definition, also known as SQL DDL, is used to define SQL schemas, base tables, views, and indexes.

• This part of data manipulation is also known as "SQL DML". Data manipulation is divided into two categories: Data query and Data update. The data update is divided into three operations such as inserting, deleting, and modifying.

• This part of data control is also known as the "SQL DCL". Data control includes authorization to basic tables and views, description of integrity rules, transaction control statements, and so on.

Embedded SQL uses this section to refer to the rules used by SQL statements embedded in the host language program.

2. Storage Engine

Relational database tables are data structures for storing and organizing information, and you can interpret tables as tables of rows and columns, similar to the form of spreadsheets in Excel. Some tables are simple, some tables are complex, some tables do not have to store any long-term data, some tables are read very fast, but when inserting data is very poor, and we in the actual development process, we may need a variety of tables, different tables, it means the storage of different types of data, data processing will also exist differences, then. For MySQL, it offers many types of storage engines, and we can take advantage of MySQL's powerful capabilities by choosing different storage engines based on the need for data processing.
In the MySQL client, use the following command to view the MySQL-supported engine.
Show engines;

You can see that MySQL has a variety of storage engines:
MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.

Usually use the most is MyISAM and InnoDB, the following mainly to introduce these two kinds.

2.1, MyISAM

The MyISAM table is independent of the operating system, which means that it can be easily ported from a Windows Server to a Linux server, and every time we build a table for a MyISAM engine, three files are created on the local disk, which is indicated by the file name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated:

1.TB_DEMO.FRM, storage table definition;

2.tb_demo. MYD, storing data;

3.tb_demo. MYI, stores the index.

The MyISAM table cannot handle transactions, which means that there is a table for transaction processing requirements and cannot use the MyISAM storage engine. The MyISAM storage engine is ideal for use in the following situations:

1. Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.

2. Insert intensive tables. The concurrent Insert feature of MyISAM allows data to be selected and inserted at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

2.2, InnoDB

InnoDB is a robust transactional storage engine that has been used by many Internet companies to provide a powerful solution for users to operate very large data stores. InnoDB is used as the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, which are ideal for use with InnoDB in the following situations:

1. Update the dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.

2. Transactions. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.

3. Automatic disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.

4. Foreign KEY constraints. MySQL supports the foreign key storage engine only InnoDB.

5. Supports automatic increment of column auto_increment properties.

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

2.3, Memory

The starting point for using the MySQL memory storage engine is speed. To get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, all memory data will be lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format, which means that a variable length data type such as BLOB and text cannot be used, and varchar is a variable-length type, but because it is a fixed-length char type within MySQL, So it can be used.

typically uses the memory storage engine in the following situations:

1. The target data is small and is accessed very frequently. Storing the data in memory, so it will cause the use of memory, can be controlled by the parameter max_heap_table_size the memory table size, set this parameter, you can limit the memory table maximum size.

2. If the data is temporary and required to be immediately available, it can be stored in the memory table.

3. Data stored in the memory table, if suddenly lost, will not have a substantial negative impact on the app service. The

memory supports both the hash index and the B-tree index. The B-Tree index is better than the hash index, you can use partial queries and wildcard queries, or you can use operators such as <, >, and >= to facilitate data mining. Hash indexes are very fast for equality comparisons, but are much slower for range comparison, so the hash index values are suitable for use in operators of = and <>, not in the < or > operators, nor in the ORDER BY clause. The

can use a using clause to specify the version to use when the table is created. For example, the
Copy code code is as follows:

CREATE TABLE users (

ID smallint unsigned NOT NULL auto_increment,

Username varchar () NOT NULL,

PWD varchar (NOT NULL),

Index using hash (username),

Primary key (ID)) engine=memory;

The code above creates a table that uses a hash hash index on the username field. The following code creates a table that uses the Btree index.
Copy the code code as follows:

CREATE TABLE Users (

ID smallint unsigned NOT NULL auto_increment,

Username varchar () NOT NULL,

PWD varchar (NOT NULL),

Index using Btree (username),

Primary key (ID)) engine=memory;

2.4. MERGE

The merge storage engine is a combination of a set of MyISAM tables that must be identical in structure, although their use is not as prominent as other engines, but is useful in some cases. To be blunt, the merge table is just a few aggregators of the same MyISAM table, and there is no data in the merge table, and the merge type table can be queried, updated, deleted, and actually operated on the internal MyISAM table. The usage scenario for the merge storage engine.

For server logs, the most common storage strategy is to divide the data into tables, each associated with a specific time-end. For example, you can use 12 identical tables to store server log data, each named by the name of each month. When it is necessary to generate a report based on data from all 12 log tables, this means that multiple table queries need to be written and updated to reflect the information in those tables. Instead of writing these queries that may have errors, instead of merging the tables with a single query and then deleting the merge table without affecting the original data, deleting the merge table simply removes the definition of the merge table and has no effect on the internal table.

2.5. Summary

InnoDB Storage Engine: For transactional applications with many features

MyISAM Storage Engine: primarily for management fee transactions tables, it provides high-speed storage and retrieval, as well as full-text search capabilities

Memory Storage Engine: Provides an "in" Table, memory storage engine of all the data is in RAM, data processing speed, but not high security

3. Noun explanation

• fields and data types

While all of these engines support common data types such as Integer, Real, and character, not all engines support other field types, especially blogs (binary large objects) or text text types. Other engines may only support a limited number of character widths and data sizes.
These limitations can directly affect the data you can store, and may have an indirect effect on the type of search you implement or the indexes you create for that information. These differences can affect the performance and functionality of your application because you have to choose the capabilities of the storage engine that you want to store based on the type of data you are storing.

• Lock

The locking feature in the database engine determines how information access and updates are managed. When an object in the database is locked for information updates, other processing cannot modify the data (which in some cases is not allowed to read) until the update is complete.
Locking affects not only how many different applications update the information in the database, but also the query for that data. This is because the query may be accessing data that is being modified or updated. In general, this delay is very small. Most locking mechanisms are primarily designed to prevent multiple processes from updating the same data. Because of the need to lock in both information and update information, you can imagine that multiple applications can have a significant impact on the same database.
Different storage engines support locking at different object levels, and these levels affect information that can be accessed at the same time. There are three levels of support: Table lock, Block lock, and row lock. The most supported are table locks, which are provided in MyISAM. When the data is updated, it locks the entire table. This prevents many applications from updating a specific table at the same time. This has a big impact on applying many multiuser databases because it delays the process of updating.
Page-level locking uses the Berkeley DB Engine and locks data based on the uploaded information page (8KB). There is no problem with this lock when updates are made in many parts of the database. However, because adding a few lines of information to lock the last 8KB of the data structure, when the need to add a large number of rows, and not a large number of small data, will cause problems.
Row-level locking provides the best parallel access capability, with only one row of data locked in a table. This means that many applications can update data from different rows in the same table without causing locking problems. Only the InnoDB storage engine supports row-level locking.

• Build an index

Indexing can significantly improve performance when searching and recovering data in a database. Different storage engines provide different techniques for indexing. Some techniques may be better suited to the type of data you store.
Some storage engines do not support indexing at all, either because they use a base table index (such as the merge engine) or because the data is stored in a way that does not allow indexing (such as federated or Blackhole engines).

• Transaction processing

Transactional functionality provides reliability by providing the ability to update and insert information into a table. This reliability is achieved by allowing you to update the data in the table, but only accept your changes to the table when all relevant operations for the application are complete. For example, in accounting processing, each accounting entry processing will include changes to the debit and credit account data, and you will need to use the transactional functionality to ensure that the data changes to the debit and credit accounts are completed successfully before you accept the modifications. If any of the operations fail, you can cancel the transaction and the modifications will not exist. If the transaction process is complete, we can confirm the operation by allowing this modification.

4. Data type

• String data type

The 1.char (n) and varchar (n) brackets mean the number of characters, not the number of bytes, so when Chinese is used (UTF8) means you can insert M Chinese, but it actually consumes m*3 bytes.
2. At the same time char and varchar The biggest difference is that char regardless of the actual value will occupy n characters space, and varchar will only occupy the actual character should occupy +1 of the space, and the actual space +1<=n.
3. After the n setting of char and varchar is exceeded, the string is truncated.
The upper limit of 4.char is 255 bytes, the upper bound of varchar is 65535 bytes, and the text is capped at 65535.
5.char will truncate the trailing spaces when stored, varchar and text will not.
6.varchar uses 1-3 bytes to store the length, text does not.

• Numeric data type

Integral type

 

The above definitions are signed, of course, you can also add the unsigned keyword, defined as unsigned type, then the corresponding value range will be overturned, such as:

The value range of tinyint unsigned is 0~255.

Floating point Type

 

• Binary data types

A binary type can store any data, such as text, images, multimedia, and so on. The specific types are described below:

                              

• Properties of the data type

Lin Bingwen Evankaka Original works. Reprint please specify the source Http://blog.csdn.net/evankaka

MySQL basics, storage engine, and common data types