Getting to know MySQL

This is the 28th day of my participation in the August Text Challenge.More challenges in August

The database

Database is a warehouse that organizes, stores, and manages data according to data structures

function

• Persistently store and manage (add, delete, update) large amounts of data
• High efficiency query and extract the data that meet the conditions, fast data access
• Constraints on the type of data stored
• Remote data sharing and permission management are supported

Database Management System (DBMS)

• Is to manipulate and manage, establish and use the database of large-scale software, to ensure the security and integrity of database data

• DBMS can access the data in the database, database maintenance work

• Data definition language DDL and data operation language DML are provided to read and write data through the interface provided by DNMS, and to achieve the operation of adding and deleting data in the database

• Allows multiple users to create, modify, and access databases at different times

  The data in the file can be opened by Excel and WPS to view, add and delete dataCopy the code

The DBMS classification

• relational

  MySQL(the most popular open source database management system), Microsoft SQL Server, Oracle(for a fee), pgSQL

• non-relational

  MongoDb, Redis, Cloudant, HBase

Relational database

The characteristics of

· Data is presented in the form of tables, each table has its own name (unique within the same database), and the tables are organized into a one-to-one correspondence between rows and columns 2. Behavior of various data record names 3. Data domain corresponding to each column record name 4. A number of rows and columns form a table 5. A number of tables form a relational database. Each database has its own name (unique)Copy the code

​

The term

• Data: data

• Row: Data in a table is stored in rows, with each record stored in its own row (data record)

• Column: A column (data) that has data elements of the same type

• Data tables: A table is a matrix of data that looks like a simple electronic table in a database

• Database: A collection of tables, DB

• Primary key: A column (or group of columns) that uniquely identifies each row in a table is called a primary key and is used to represent a particular row. NULL values are not allowed for primary key columns

  Without a primary key, updating or deleting a particular row in a table is difficult because there is no secure way to ensure that only the relevant rows are involved. Although a primary key is not always required, most database designers should ensure that each table they create has a primary key for future data manipulation and management.

• Indexes: Use indexes to quickly access data in a data table

The MySQL database

• MySQL is open source and free at no additional cost
• MySQL supports large databases and data warehouses with 50 million records. The maximum size of 32-bit system table files can be 4GB, and the maximum size of 64-bit system table files can be 8TB.
• MySQL uses the standard SQL data language format
• MySQL runs on multiple systems and supports multiple languages. These languages include C, C++, Python, Java, perl.php, Eiel.Ruby, and Tcl

advantages

1. Simple and easy to use

   MySQL is a high-performance and relatively simple database system that is less complex to set up and manage than some larger systems

2. The price

   It is free for most individual users and can be downloaded and installed by anyone on the official website

3. small

   The database distribution is a mere 21M, with a mere 51M installed

4. Query language support

   You can take advantage of SQL (Structured Query Language), the language of choice for all modern database systems

5. performance

   There is no limit to the number of users, and multiple clients can use the same database simultaneously. MySQL runs fast. The developers claim MySQL is probably the fastest database available today

6. Connectivity and security

   MySQL is fully networked, accessible from anywhere, you can share your database with anyone anywhere, and you can control who can’t see your data

disadvantages

• The high cost of consistency is poor read and write performance
• Fixed table structure
• High concurrent read and write requirements
• Efficient reading and writing of massive data

The server handles client requests

In fact, no matter which way the client process and server process communicate, the final effect is: ** the client process sends a piece of text to the server process (MySQL statement), and the server process sends a piece of text to the client process (processing result). ** What does the server process do to the request sent by the client process to produce the final result? The client can send all kinds of requests to the server to add, delete, modify and check. Here we take the complicated query request as an example to draw a picture to show the general process:

Although the query cache can sometimes improve the system performance, it also has to cause some overhead due to the maintenance of the cache. For example, the query cache has to be retrieved every time. After the query request is processed, the query cache needs to be updated and the memory region corresponding to the query cache is maintained. As of MySQL 5.7.20, query caching is not recommended and was removed in MySQL 8.0.

The storage engine

The MySQL server encapsulates the data storage and extraction operations into a module called the storage engine. We know that a table is made up of rows of records, but this is only a logical concept. The storage engine is responsible for how records are represented physically, how data is read from the table, and how data is written to specific physical storage. To achieve different functions, MySQL provides a variety of storage engines. The specific storage structure of tables managed by different storage engines may be different, and the access algorithm adopted may also be different.

A storage engine, formerly known as a table processor, receives instructions from the top and then extracts or writes data from the table.

For the convenience of management, connection management, query caching, syntax parsing, query optimization and other functions that do not involve real data storage are divided into MySQL Server functions, and real data access functions are divided into storage engine functions. The various storage engines provide a unified calling interface (also known as the storage engine API) to the upper MySQL Server layer, containing dozens of low-level functions such as “read index first”, “read index next”, “insert record” and so on.

Therefore, after MySQL Server completes query optimization, it only needs to call the API provided by the underlying storage engine according to the generated execution plan and return the data to the client.

MySQL supports a wide variety of storage engines:

Some operations about the storage engine

View the storage engines supported by the current server program

We can use the following command to view the storage engines supported by the current server application:

SHOW ENGINES;
Copy the code

Set the storage engine for the table

As we said earlier, storage engines are responsible for extracting and writing data from tables. We can set different storage engines for different tables, that is, different tables can have different physical storage structures, and different extraction and writing methods.

Specify the storage engine when creating the table

The default storage engine, InnoDB, will be used instead of specifying the storage engine. (This can be modified in a later section.) If we wanted to explicitly specify the storage engine for the table, we could write:

CREATE TABLE name; ENGINE = Storage ENGINE name;Copy the code

For example, if we want to create a table with MyISAM as the storage engine, we can write:

mysql> CREATE TABLE engine_demo_table( -> i int -> ) ENGINE = MyISAM; Query OK, 0 rows affected (0.02sec) mysql>Copy the code

Modify the storage engine of the table

If the table is already created, we can also use the following statement to change the storage engine of the table:

ALTER TABLE table_name ENGINE = storage ENGINE;Copy the code

For example, if we modify the storage engine of engine_demo_table:

mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
Copy the code

Engine_demo_table = engine_demo_table

mysql> SHOW CREATE TABLE engine_demo_table\G *************************** 1. row *************************** Table: engine_demo_table Create Table: CREATE TABLE 'engine_demo_table' (' I 'int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01sec)  mysql>Copy the code

You can see that the storage engine for this table has been changed to InnoDB.

Learn about character sets and garbled characters

Character Set Introduction

We know that only binary data can be stored on a computer, so how do you store strings? To create a mapping between characters and binary data, you need to know at least two things:

1. Which characters do you map to binary data?

   That is, define the character range clearly.

2. How do I map it?

   The process of mapping a character to a binary is also called encoding, and the process of mapping a binary to a character is called decoding.

People abstract the concept of a character set to describe the encoding rules for a range of characters

Let’s take a look at some common character sets:

• The ASCII character set

  Contains 128 characters, including Spaces, punctuation marks, digits, upper – and lower-case letters, and some invisible characters. Since there are only 128 characters in total, we can use 1 byte to encode the characters. Let’s look at some character encoding methods:

  'L' -> 01001100 (hex: 0x4C, decimal: 76) 'M' -> 01001101 (hex: 0x4D, decimal: 77)Copy the code

• ISO 8859-1 character set

  A total of 256 characters, is in the ASCII character set on the basis of the expansion of 128 common western European characters (including German and French letters), can also use 1 byte to encode. This character set also has an alias latin1.

• The GB2312 character set

  It contains Chinese characters, Latin alphabet, Greek alphabet, Japanese hiragana and Katakana alphabet, Russian Cyrillic alphabet. Among them, 6,763 Chinese characters and 682 other characters are included. This character set is also compatible with the ASCII character set, so it is somewhat strange in terms of encoding:

  • If the character is inASCIIIn the character set, 1-byte encoding is used.
  • Otherwise, 2-byte encoding is used.

  This encoding to indicate that the number of bytes required for a character may differ is called variable-length encoding. For example, the string ‘love U’, where ‘love’ needs to be encoded with 2 bytes in hexadecimal representation 0xCED2, ‘u’ needs to be encoded with 1 byte in hexadecimal representation 0x75, so it adds up to 0xCED275.

  Tip: How can we tell whether a byte represents a single character or part of a character? Remember that the ASCII character set contains only 128 characters, and 0 to 127 can represent all characters, so if a byte is within 0 to 127, it means that one byte represents a single character, otherwise two bytes represent a single character.

• GBK character set

  The GBK character set is only an extension of the GB2312 character set in the range of included characters, and compatible with GB2312 encoding mode.

• Utf8 character set

  Every character imaginable on Earth, and it keeps growing. This character set is compatible with the ASCII character set and uses variable-length encoding, which takes 1 to 4 bytes to encode a character, for example:

  'L' -> 01001100 (hex: 0x4C) 'ah' -> 11100101100101010110001010 (hex: 0xE5958A)Copy the code

  Tip: Utf8 uses 1 to 4 bytes to encode a character, utF16 uses 2 to 4 bytes to encode a character, and UTF16 uses 2 to 4 bytes to encode a character. Utf32 uses four bytes to encode a character. MySQL does not distinguish between character sets and encoding schemes. Utf8, UTF16, and UTF32 are all treated as character sets.

Different character sets may have different encoding methods for the same character. For example, the character ‘I’ is not included in the ASCII character set at all. Utf8 and GB2312 encode the character as follows:

Utf8 Encoding: 111001101000100010010001 (3 bytes, hexadecimal: 0xE68891) GB2312 encoding: 1100111011010010 (2 bytes, hexadecimal: 0xCED2)Copy the code

MySQL utf8 and UTF8MB4

We said that the UTF8 character set means that a character needs to use 1 to 4 bytes, but some of the characters we commonly use can use 1 to 3 bytes. In MySQL, the character set represents the maximum length of a character, which in some ways affects the storage and performance of the system. Therefore, the uncle who designed MySQL secretly defined two concepts:

• utf8mb3: Castratedutf8Character set: contains 1 to 3 bytes.
• utf8mb4: authenticutf8Character set: contains 1 to 4 bytes.

Note that utF8 is an alias for UTF8MB3 in MySQL, so using UTF8 in MySQL means using 1 to 3 bytes to represent a character. If you use 4 bytes to encode a character, such as storing some emojis, Please use UTF8MB4.

Character set viewing

MySQL supports many different character sets. To check the character set supported by MySQL, use the following statement:

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news