MySQL Database summary

1. Basic Concept 1.1 data

The symbolic records describing things are called data, and the symbols describing things can be either numbers or text, pictures, images, sounds, languages, etc., and the data is represented by many forms, which can be digitized and stored in a computer.

To describe a thing in a computer, you need to extract the typical characteristics of this thing, to form a record, which is equivalent to a line of content in a file.

1.2 What is a database

The database is the warehouse where the data is stored, except that the warehouse is on a computer storage device, and the data is stored in a certain format.

In the past, people stored data in cabinets, and now the volume of data is large, no longer applicable

Database is a long-term storage in the computer, organized, shareable data can be.

The data in the database is organized, described and stored according to a certain data model, with small redundancy, high data independence and extensibility, and can be shared for various users.

1.3 What is a database management system (DBMS Management)

After understanding the concept of data and DB, how to organize and store it scientifically, how to efficiently acquire and maintain data becomes the key

This is the use of a system software---database management system

such as MySQL, Oracle, SQLite, Access, MS SQL Server

MySQL is mainly used for large-scale portals, such as Sogou, Sina, etc., its main advantage is open source code, because the open source of this database is free, he is now the Oracle company's products.
Oracle is mainly used in banks, railways, airports, etc. The database is powerful and the software is expensive. It is also the product of Oracle Corporation.
SQL Server is Microsoft's products, mainly used in large and medium-sized enterprises, such as Lenovo, founder and so on.

1.4 Relationship between database servers, data management systems, databases, tables and records (key understanding)

Entries: 1 Mark 324245234 22 (Multiple fields of information form a record, that is, a row in the file)

Table: student,scholl,class_list (i.e. file)

Database: Oldboy_stu (that is, folder)

Database management system: such as MySQL (is a software)

Database server: One computer (high memory requirements)

1.5 Summary

Database server-: Running database management software

Database management software: Management-Database

Database: A folder for organizing files/tables

Table: A file that is used to hold multiple lines of content/Multiple records
______________________________

2. Introduction to the underlying SQL statement 2.1

The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL languages:
1. DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER

2. DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select

3. DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

2.2 SQL statements

1. Action Folder
   Add: Create Database db1 charset UTF8;
   Check: show databases;
   Change: Alter DATABASE DB1 CharSet latin1;
   Delete: Drop database db1;

2. Manipulating files
   Switch to the folder first: Use DB1
   Add: CREATE TABLE T1 (id int,name char);
   Check: Show tables
   Change: ALTER TABLE t1 modify name char (3);
   ALTER TABLE T1 change name name1 char (2);
   Delete: drop table T1;

3. Contents/Records in the action file
   Add: INSERT INTO T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');
   Check: SELECT * from T1;
   Change: Update t1 set name= ' SB ' where id=2;
   Delete: delete from T1 where id=1;
   _________________________

3. Common Storage Engine 3.1 database storage engine

For starters, we don't usually focus on the storage engine, but MySQL provides multiple storage engines, including the engine that handles transaction security tables and the engine that handles non-transactional security tables. In MySQL, there is no need to use the same storage engine throughout the server, and for specific requirements, you can use a different storage engine for each table.

3.2 Introduction to Storage engines

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application. The storage engine is how to store the data, how to index the stored data, and how to update and query the data.

For example, if you are working on a large amount of temporary data, you may need to use a memory storage engine. The memory storage engine can store all the tabular data in memory. Alternatively, you might need a database that supports transactional processing (to ensure that the data is backed up when transaction processing is unsuccessful).

3.2.1 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. MySQL version 5.6.13 is installed on my computer, and InnoDB is 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:

Updates a dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
Transaction. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
Automated Disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
FOREIGN KEY constraints. MySQL supports the foreign key storage engine only InnoDB.
Supports automatic increment of column Auto_increment property.
Starting from 5.7, the InnoDB storage engine becomes the default storage engine.
In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

3.2.2 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; whenever we build a table for a MyISAM engine, we build three files on the local disk, and the file name is the table name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated:

TB_DEMO.FRM, which stores the table definition.
Tb_demo. MYD, storing data.
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:

Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
Inserts a dense table. 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.

3.2.3 Mrg_myisam

The Mrg_myisam storage engine is a combination of a set of MyISAM tables, and the old version of MERGE is actually a matter of fact, these MYISAM table structures must be identical, although their use is not as prominent as other engines, but in some cases it is useful. 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.

Engine=merge, indicating the use of the merge engine, in fact, with Mrg_myisam, is also right, in MySQL 5.7 has not seen the merge.
union= (t1, T2), which indicates which tables are hooked up in the merge table, can modify the union value by ALTER TABLE to implement the ability to delete and remove the Merge table sub-table. Like what:

altertableunion(tb_log1) insert_method=last;

Insert_method=last,insert_method indicates the insertion method, the value can be: 0 is not allowed to insert, first inserted into the Union in a table, last inserted into the final table in the Union.
The merge table and the member data tables that comprise the merge data table structure must have exactly the same structure. The data columns of each member data table must define the same name and type in the same order, and the index must be defined in the same order and in the same way.

3.2.4 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.

Memory storage engines are typically used in the following situations:

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.
If the data is temporary and needs to be immediately available, it can be stored in the memory table.
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 index and 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.

3.2.5 CSV

The CSV storage engine stores data based on CSV format files.

CSV storage Engine because of its own file format, all columns must be forced to specify not NULL.
The CSV engine also does not support indexing, and partitioning is not supported.
The CSV storage engine also contains a. frm file that stores the table structure, and also creates a. csv file that stores data, and a meta-information file with the same name, with the file name extension. CSM, which holds the state of the table and the amount of data saved in the table.
Each data row occupies one line of text.
Because the CSV file itself can be edited directly by the Office and other software, discouragement is not in accordance with the rules of the situation, if the contents of the CSV file is corrupted, you can also use the Check table or REPAIR table command to check and repair

3.2.6 ARCHIVE

Archive is archived, many of the advanced features are no longer supported after archiving, supporting only the most basic insert and query functions. Prior to MySQL version 5.5, archive did not support indexing, but it began to support indexing in MySQL 5.5. Archive has a good compression mechanism, which uses the Zlib compression library, which is compressed in real time when the record is requested, so it is often used as a repository.

3.2.7 Blackhole

Black hole storage Engine, all inserted data will not be saved, the Blackhole engine table will always remain empty, any data written will disappear.

3.2.8 Performance_schema

Primarily used to collect database server performance parameters. MySQL user is not able to create a storage engine for Performance_schema tables, generally used to record binlog do replication relay. Here are some official introduction to MySQL performance Schema

3.2.9 Federated

Mainly used to access other remote MySQL server A proxy, it through the creation of a remote MySQL server client connection, and transfer the query to the remote server execution, and then complete the data access; The implementation on MARIADB is Federatedx

3.2.10 Other

Here are some other database-supplied storage engines, oqgraph, Sphinxse, Tokudb, Cassandra, CONNECT, and Squence. The name provided is for informational purposes only.

3.3 How to choose the right storage engine

Choose from a few selection criteria and then follow the standard, select the corresponding storage engine, or choose the storage engine you use based on the common engine comparison. Which engine to use needs to be flexibly selected, and multiple tables in a database can use different engines to meet a variety of performance and real-world requirements. Using the appropriate storage engine will improve the performance of the entire database.

-whether support services are required;
-Need to use hot spare;
-Crash recovery, can accept crashes;
-whether foreign key support is required;
-Restrictions on storage;
-Support for indexing and caching;

Cond

MySQL Database summary