Basic MySql knowledge, storage engine and common data types, basic mysql knowledge

Basic MySql knowledge, storage engine and common data types, basic mysql knowledge

Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka

This article will mainly explain some basic knowledge about the database, introduce the storage engine of MySql, and finally introduce the commonly used data types of MySql.

1. Basic Knowledge

1.1 database Overview

• Simply put, databases (databases or databases) are containers 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 the core of the database.

1.2,Relational Database Management System

• Through the "Database Management System", database users can easily access various database objects in database containers (add, delete, modify, query, and other operations ), it also allows you to easily complete database maintenance (backup, recovery, repair, and other operations)

• The database user cannot directly obtain the specific content in the database file through the operating system;

• The database management system calls the process management, memory management, device management, and file management services of the operating system, provides database users with interfaces for managing and controlling various database objects and database files in database containers.

1.3,Relational Database

A relational database is a database built on a relational database model. It processes data in a database using concepts and methods such as set algebra. It is also organized into a group of tables with formal descriptive data, this form of table is actually used to load the special collection bodies of data items. The data in these tables can be accessed or reassembled in many different ways without the need to reorganize database tables. The definition of a relational database results in a table of metadata or formal descriptions of tables, columns, ranges, and constraints. Each table (sometimes called a link) contains one or more data types represented by columns. Each row contains a unique data entity, which is a type defined by the column.

Basic Features

• Relational Database

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

• Relational Database Type and Value

The relational database model is called the relational database model. It refers to the description of the relational database, the definition of several domains, and several relational patterns defined on these domains.
The value of a relational database is a set of links corresponding to these relational modes at a certain time point, usually referred to as relational databases.

1.4,Structured Query Language SQL

Structured Query Language (Structured Query Language) is a Language between relational algebra and relational calculus. Its functions include Query, manipulation, definition, and control, is a commonly used standard language for relational databases. Currently, the SQL language has been identified as an international standard for relational database systems and is adopted by most commercial relational database systems. In the SQL language, you can specify what to do instead of what to do. Instead of telling SQL how to access the database, you only need to tell SQL what to do. You can use SQL statements for data controls during design or operation.

1.5,SQL database architecture
The architecture of SQL database is also a three-level structure.

The SQL term is different from the traditional relational model term. In SQL, the external mode corresponds to the view, and the mode corresponds to the basic table. The tuples are called rows and the attributes are called columns. The internal mode corresponds to the storage file. The architecture of SQL database has the following features:

• The SQL Schema is a set of tables and constraints.

• A Table is a set of rows. Each Row is a sequence of columns, and each Column corresponds to a data item.

• A table can be either a basic table or a view. The basic table is the table actually stored in the database. A view is a table exported from a basic table or other views. It is not stored in the database independently. That is to say, the database only stores the view definition, but does not store the View data, the data is still stored in the basic table of the exported view. Therefore, the view is a virtual table.

• A basic table can be stored across one or more storage files. A storage file can also store one or more basic tables. A table can contain several indexes and indexes are stored in the storage file. Each storage file corresponds to a physical file on the external storage. The logical structure of the storage file constitutes the internal mode of the relational database.

• You can use SQL statements to query views and basic tables. In the user's opinion, views are the same as basic tables, and they are all Relational Tables ).

• SQL users can be applications or end users. SQL statements can be embedded in programs in the host language. The host languages include Fortran, Cobol, Pascal, PL/I, C, and Ada. The SQL language can also be used as an independent user interface, it is used by end users in the interactive environment.

1.6. SQL Composition

• This part of data definition is also called "SQL ddl". It is used to define the SQL mode, basic table, view, and index.

• Data manipulation is also called "SQL DML ". Data manipulation is divided into two types: Data Query and data update. Data Update is divided into three operations: insert, delete, and modify.

• This part of data control is also called "SQL DCL ". Data Control includes authorization to basic tables and views, description of integrity rules, and transaction control statements.

The use of Embedded SQL involves the use rules of SQL statements embedded in the host language program.

2. Storage Engine

A relational database table is a data structure used to store and organize information. It can be understood as a table composed of rows and columns, similar to a workbook in Excel. Some tables are simple, some tables are complex, and some tables do not need to store any long-term data at all. Some tables read very quickly, but are poorly inserted; in the actual development process, we may need a variety of tables. Different tables mean that different types of data are stored and data processing is also different. For MySQL, it provides many types of storage engines. We can select different storage engines based on the data processing requirements to maximize the use of MySQL's powerful functions.
On the mysql client, run the following command to view the engines supported by MySQL.
Show engines;

We can see that MySQL has multiple storage engines:
MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, and BLACKHOLE.

MyISAM and InnoDB are the most commonly used, which are described below.

2.1. MyISAM

MyISAM tables are independent of the operating system, which means that they can be easily transplanted from a Windows server to a Linux server. Whenever we create a MyISAM engine table, three files will be created on the local disk. The file name indicates. For example, if I have created a tb_Demo TABLE OF THE MyISAM engine, the following three files will be generated:

1. tb_demo.frm, storage table definition;

2. tb_demo.MYD: store data;

3. tb_demo.MYI, which stores indexes.

MyISAM tables cannot process transactions, which means tables with transaction processing requirements cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for the following scenarios:

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

2. Insert intensive tables. The Concurrent Insertion feature of MyISAM allows you to select and insert data at the same time. For example, the MyISAM storage engine is suitable for managing mail or Web server log data.

2.2. InnoDB

InnoDB is a robust transactional storage engine, which has been used by many Internet companies and provides a powerful solution for users to operate very large data storage. InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints. InnoDB is the best choice in the following scenarios:

1. Update intensive tables. The InnoDB Storage engine is particularly suitable for processing 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, InnoDB tables can be automatically recovered from disasters.

4. Foreign key constraints. MySQL only supports the InnoDB Storage engine.

5. The AUTO_INCREMENT attribute can be automatically added.

In general, InnoDB is a good choice if you need transaction support and a high concurrent reading frequency.

2.3. MEMORY

The starting point of using the MySQL Memory storage engine is speed. To get the fastest response time, the logical storage medium used is the system memory. Although storing table data in the Memory does provide high performance, when the mysqld daemon crashes, all Memory data will be lost. The acquisition speed also brings some defects. It requires that the data stored in the Memory data table be in the same length format, which means that the variable-length data types such as BLOB and TEXT cannot be used, VARCHAR is a variable-length type, but it can be used because it is used as a CHAR type with Fixed Length in MySQL.

Memory storage engine is generally used in the following situations:

1. The target data is small and frequently accessed. Data is stored in the Memory, which may cause Memory usage. You can use the max_heap_table_size parameter to control the size of the Memory table and set this parameter to limit the maximum size of the Memory table.

2. if the data is temporary and must be available immediately, it can be stored in the memory table.

3. If the data stored in the Memory table is suddenly lost, it will not have a substantial negative impact on the Application Service.

Memory supports both hash indexes and B-tree indexes. B-tree indexes are better than hash indexes. Partial queries and configuration queries can be used, and operators such as <,> and> = can be used to facilitate data mining. It is very fast to perform "equal comparison" for hash indexes, but the speed for "range comparison" is much slower. Therefore, hash index values are suitable for operators of = and <>, it is not suitable in the <or> operator or in the order by clause.

You can use the USING clause to specify the version to use when creating a table. For example:
Copy the Code as follows:

Create table users (

Id smallint unsigned not null auto_increment,

Username varchar (15) not null,

Pwd varchar (15) not null,

Index using hash (username ),

Primary key (id) engine = memory;

The code above creates a table and uses the HASH index on the username field. The following code creates a table and uses the BTREE index.
Copy the Code as follows:

Create table users (

Id smallint unsigned not null auto_increment,

Username varchar (15) not null,

Pwd varchar (15) not null,

Index using btree (username ),

Primary key (id) engine = memory;

2.4. MERGE

The MERGE storage engine is a combination of MyISAM tables. These MyISAM tables must have the same structure. Although they are not as prominent as other engines, they are useful in some cases. To put it bluntly, the Merge table is the aggregator of several identical MyISAM tables. The Merge table does not have data, and you can query, update, or delete tables of the Merge type, these operations are actually performed on the internal MyISAM table. Use Cases of the Merge storage engine.

For server logs, a common storage policy is to divide data into many tables, and each name is related to a specific time end. For example, you can use 12 identical tables to store server log data. Each table is named after each month. When it is necessary to generate reports based on the data of all 12 log tables, this means that you need to write and update multi-table queries to reflect the information in these tables. Instead of writing these queries that may cause errors, you can combine these tables to use a query and then delete the Merge table without affecting the original data, deleting a Merge table only deletes the definition of a Merge table, which has no impact on the internal table.

2.5,Summary

InnoDB Storage engine: used for transaction processing applications, with many features

MyISAM storage engine: Mainly used for management fee transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities.

MEMORY storage engine: it provides "in-MEMORY" tables. All data of the MEMORY storage engine is in the MEMORY. The data processing speed is fast, but the security is not high.

3. Glossary

• Field and Data Type

Although all these engines support common data types, such as integer, real, and complex types, not all engines Support other field types, especially blogs (Binary large objects) or TEXT type. Other engines may only support limited character widths and data sizes.
These limitations may directly affect the data you can store, or indirectly affect the type of the search you implement or the index you create for the information. These differences can affect the performance and functions of your application, because you must choose to make decisions on the features of the storage engine based on the data type you want to store.

• Lock

The locking function in the database engine determines how to manage access and update information. When an object in the database is locked for Information Update, other processes cannot modify the data before the update is complete (in some cases, this data cannot be read ).
Locking affects not only how many different applications update information in the database, but also the query of that data. This is because the query may need to access the data being modified or updated. In general, this latency is very small. Most locking mechanisms are designed to prevent multiple processes from updating the same data. Because inserting and updating information to data both needs to be locked, you can imagine that multiple applications may have a great impact on using 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. Three supported levels are available: Table locking, block locking, and row locking. Table locks are the most supported. Such locks 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 great impact on many multi-user databases, because it delays the update process.
Page-level locking uses the Berkeley DB Engine and locks data based on the uploaded information page (8 KB. When the database is updated in many places, this locking will not cause any problems. However, the last 8 KB of the data structure will be locked when several lines of information are added. When a large number of rows are required, especially a large amount of small data, this will cause problems.
Row-level locking provides the best parallel access function. Only one row of data in a table is locked. This means that many applications can update the data of different rows in the same table without locking. Only the InnoDB Storage engine supports row-level locking.

• Create an index

Index creation can significantly improve performance when searching and recovering data in the database. Different storage engines provide different indexing technologies. Some technologies may be more suitable for the data type you store.
Some storage engines do not support indexing at all, probably because they use basic table indexes (such as the MERGE engine) or because indexes (such as FEDERATED or BLACKHOLE engines) are not allowed in data storage ).

• Transaction Processing

The transaction processing function provides reliability during the update and insertion of information into the table. This reliability is achieved through the following method, which allows you to update the data in the table, but only accepts the changes to the table after all the related operations of the application are complete. For example, each accounting entry in the accounting process will include changes to the debit and credit account data, you need to use the transaction processing function to ensure that the changes to the data of the debit account and the credit section are successfully completed before accepting the changes. If any operation fails, you can cancel the transaction and the modifications do not exist. If the transaction processing is completed, we can confirm this operation by allowing this modification.

4. Data Types

• String Data Type

1. in char (n) and varchar (n) brackets, n represents the number of characters and does not represent the number of bytes. Therefore, when Chinese characters are used (UTF8), m Chinese characters can be inserted, however, it will actually occupy m * 3 bytes.
2. at the same time, the biggest difference between char and varchar is that char occupies n characters regardless of the actual value, while varchar only occupies the space occupied by actual characters + 1, and the actual space is + 1 <= n.
3. the string is truncated after the n settings of char and varchar are exceeded.
4. The upper limit of char is 255 bytes, the upper limit of varchar is 65535 bytes, and the upper limit of text is 65535.
5. the space at the end of the char will be truncated during storage. varchar and text will not.
6. varchar uses 1-3 bytes to store the length. text does not.

• Numeric data type

Integer

 

The above definition is signed. Of course, you can also add the unsigned keyword to define it as a non-Signed type, so the corresponding value range will be flipped over, for example:

The value range of tinyint unsigned is 0 ~ 255.

Floating Point Type

 

• Binary data type

Binary data can store any data, such as text, images, and multimedia. The specific types are described as follows:

• Attribute of Data Type

Original works of Lin bingwen Evankaka. Reprinted please indicate the source http://blog.csdn.net/evankaka