7.Mysql Database table engine and character set

1. 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... To the server process

A passage of text （MySQL sentence ）, The server process sends a piece of text to the client process after processing （ Processing results ）. The server process is to the client

What is done with the request sent by the end process , To produce the final processing result ？ The client can send various requests for addition, deletion, modification and query to the server , We

Here, take a complex query request as an example to draw a diagram to show the general process ：

Although query caching can sometimes improve system performance , But it also has to incur some overhead due to maintaining this cache , For example, you have to query every time

Search in memory , Query cache needs to be updated after query request processing , Maintain the memory area corresponding to the query cache . from MySQL 5.7.20 Start , No

Query caching is recommended , And in MySQL 8.0 Delete in .

2. Storage engine

MySQL  The server encapsulates the data storage and extraction operations into a file called   Storage engine   In the module of . We know   surface   It consists of line by line records , But it's just a logical concept , How to represent records physically , How to read data from a table , How to write data to specific physical memory , This is all   Storage engine   Responsible things . In order to achieve different functions , MySQL  Offers a wide variety of   Storage engine  , Different   Storage engine   The specific storage structure of managed tables may be different , Access algorithms may also be different .

The storage engine used to be called   Watch processor  , Its function is to receive instructions from the upper layer , Then extract or write the data in the table .

For management convenience , People put   Connection management  、  The query cache  、  Syntax parsing  、  Query optimization   These functions that do not involve real data storage are divided into MySQL server  The function of , The functions of real data access are divided into   Storage engine   The function of . Different storage engines up  MySQLserver  Layer provides a unified calling interface （ Storage engine API）, Contains dozens of underlying functions , image " Read the first item of index "、" Read index next "、" insert record " wait .

So in  MySQL server  After query optimization , You only need to call the API, Just get the data and return it to the client .

MySQL  Support for a wide variety of storage engines ：

ARCHIVE  For data archiving （ Rows cannot be modified after being inserted ）

BLACKHOLE  Discard write operation , The read operation will return empty content

CSV  When storing data , Separate data items with commas

FEDERATED  Used to access remote tables

InnoDB  Transaction storage engine with foreign key support

MEMORY  Table in memory

MERGE  Used to manage multiple MyISAM A collection of tables made up of tables

MyISAM  The main non transactional storage engine

NDB MySQL Cluster specific storage engine

3.MyISAM and InnoDB The difference between table engines

1)  Transaction support

MyISAM Unsupported transaction , and InnoDB Support .

Business ： An execution unit that accesses and updates data in a database . Things are operating , Either all or none

2)  Storage structure

MyISAM： Every MyISAM Store three files on disk .

• .frm File storage table structure .
• .MYD File storage data .
• .MYI File store index .

InnoDB： It is mainly divided into two types of files for storage

• .frm  Storage table structure
• .ibd  Store data and index  （ It could be more than one .ibd file , Or a separate tablespace file ）

3)  Watch lock difference

MyISAM： Only table level locks are supported , The user is operating myisam Table time ,select,update,delete,insert Statement will automatically lock the table , If the lock table is satisfied insert In the case of concurrency , You can insert new data at the end of the table .

 InnoDB： Support transaction and row level locks , yes innodb The biggest feature of .

Row lock greatly improves the new ability of multi-user concurrent operation . however InnoDB The row lock , It's just WHERE The primary key of is valid , Non primary key WHERE They'll lock the whole watch .

4)  Table primary key

MyISAM： Allows tables without any indexes and primary keys to exist , The index is the address of the saved line . InnoDB： If no primary key or non empty unique index is set , It will automatically generate one 6 The primary key of the byte ( The user is not visible ), Data is part of the main index , The additional index holds the value of the primary index .

InnoDB Has a larger primary key range , The biggest is MyISAM Of 2 times .

5)  The specific number of rows in the table

MyISAM： Holds the total number of rows in a table , If select count() from table; I'm just going to pull it out . InnoDB： The total number of rows in a table is not saved

( Traversal only ), If you use select count() from table; I'm going to go through the entire table , Quite a lot of consumption , But it's adding wehre After the condition ,

myisam and innodb They're all handled the same way .

6) CURD operation

MyISAM： If a large number of SELECT,MyISAM It's a better choice . InnoDB： If your data performs a lot of INSERT or UPDATE, For performance reasons , You should use InnoDB surface .DELETE  In terms of performance InnoDB better , but DELETE FROM table when ,InnoDB The table will not be recreated , It's line by line deletion , stay innodb If you want to empty a table that holds a lot of data , Best use truncate table This command .

7)  Foreign keys

MyISAM： I won't support it  InnoDB： Support

8)  The query efficiency

MyISAM Relatively simple , So the efficiency is better than InnoDB, Small applications can consider using MyISAM.

It is recommended to consider using InnoDB To replace MyISAM engine , as a result of InnoDB A lot of good characteristics of oneself , For example, transaction support 、 Storage   The process 、 View 、 Row level locking and so on , In the case of a lot of concurrency , Believe in InnoDB It must be better than MyISAM Much better .

in addition , Any kind of watch is not omnipotent , Choose the right table type only for the right business type , To make the most of MySQL Performance advantages . If it's not very complicated Web application , Non critical applications , You can still think about MyISAM Of , This specific situation can be considered by oneself .

9）MyISAM and InnoDB The application scenarios of the two

MyISAM Manage non transaction tables . It provides high-speed storage and Retrieval , And full text search capabilities . If the application needs to execute a large number of SELECT Inquire about , that MyISAM It's a better choice . InnoDB For transactional applications , It has many characteristics , Include ACID Transaction support . If the application needs to execute a large number of INSERT or UPDATE operation , Should be used InnoDB, This can improve the performance of multi-user concurrent operations . Now the default is InnoDB.

4. Learn about character sets and garbled code

Character set Introduction

We know that only binary data can be stored in a computer , How to store strings ？ Of course, it is to establish the mapping relationship between characters and binary data ,

To establish this relationship, we should at least find out two things ：

1.  Which characters do you want to map into binary data ？

That is, clearly define the character range .

2.  How to map ？

The process of mapping a character to binary data is also called   code  , The process of mapping a binary data to a character is called   decode  . People Abstract A   Character set   To describe the coding rules of a certain character range

Let's take a look at some common character sets ：

ASCII  Character set

Collects 128 Characters , Including Spaces 、 Punctuation 、 Numbers 、 Upper and lower case letters and some invisible characters . Because it's only 128 Characters , So you can use 1 Two bytes to encode , Let's look at the encoding of some characters ：

• 'L' -> 01001100（ Hexadecimal ：0x4C, Decimal system ：76）
• 'M' -> 01001101（ Hexadecimal ：0x4D, Decimal system ：77）

ISO 8859-1  Character set

Collects 256 Characters , Is in  ASCII  On the basis of the character set, it has expanded 128 Four common characters in Western Europe ( Including the letters of Germany and France ), You can also use 1 Two bytes to encode . This character set also has an alias  latin1 .

GB2312  Character set

It includes Chinese characters and Latin letters 、 The Greek letter 、 Japanese hiragana and katakana letters 、 Russian Cyrillic alphabet . It contains Chinese characters 6763 individual , Other characters 682 individual . At the same time, this character set is compatible with  ASCII  Character set , So it seems strange in the way of coding ：

• If the character is in  ASCII  Character set , Then 1 Byte encoding .
• Otherwise the 2 Byte encoding .

The number of bytes required to represent a character may be encoded in different ways, which is called   Variable length encoding  . For example, string  ' Love u' , Its

in  ' Love '  Need to use 2 Bytes for encoding , The encoded hexadecimal representation is  0xCED2 , 'u'  Need to use 1 Bytes for encoding , The encoded hexadecimal representation is  0x75 , So put together is  0xCED275 .

Tips: ：  How can we tell whether a byte represents a single character or a part of a character ？ Don't forget  ASCII  The character set contains only 128 Characters , Use 0～127 Can represent all characters , So if a byte is in 0～127 Within , It means that a byte represents a single character , Otherwise, two bytes represent a single character .

GBK  Character set

GBK  The character set is only in the range of included characters  GB2312  The character set is extended , The coding method is compatible  GB2312 .

utf8  Character set

All the characters you can think of on earth , And it's expanding . This character set is compatible with  ASCII  Character set , Using variable length coding , Encoding a character requires the use of 1～4 Bytes , Like this ：

• 'L' -> 01001100（ Hexadecimal ：0x4C）
• ' ah ' -> 111001011001010110001010（ Hexadecimal ：0xE5958A）

Tips: ：  In fact, to be exact ,utf8 It's just Unicode A coding scheme for character sets ,Unicode The character set can be utf8、

utf16、utf32 These coding schemes ,utf8 Use 1～4 One byte encodes one character ,utf16 Use 2 Or 4 One byte encodes one character ,utf32 Use 4 One byte encodes one character . More detailed Unicode Knowledge of and its coding scheme is not the focus of this book , Let's check the Internet ～ MySQL The concept of character set and encoding scheme is not distinguished in , So when you nag later, put utf8、utf16、utf32 Are treated as a character set .

For the same character , Different character sets may also have different encoding methods . For example, for Chinese characters  ' I '  Come on , ASCII  This character is not included in the character set at all , utf8  and  gb2312  Character set for Chinese characters   I   The encoding method of is as follows ：

• utf8 code ：111001101000100010010001 (3 Bytes , Hexadecimal means ：0xE68891)
• gb2312 code ：1100111011010010 (2 Bytes , Hexadecimal means ：0xCED2)

5.MySQL Medium utf8 and utf8mb4

We said above  utf8  The character set indicates that a character needs to use 1～4 Bytes , But some of the characters we often use 1～3 It's just a byte . And in the  MySQL  The character set in represents the maximum byte length of a character, which will affect the storage and performance of the system in some ways , So design  MySQL My uncle secretly defined two concepts ：

• utf8mb3 ： Castrated  utf8  Character set , Use only 1～3 Bytes represent characters .
• utf8mb4 ： authentic  utf8  Character set , Use 1～4 Bytes represent characters .

There is one point that requires your great attention , stay  MySQL  in  utf8  yes  utf8mb3  Another name for , So after that  MySQL  I mentioned  utf8  It means using 1~3 Bytes to represent a character , If you use 4 The case of byte encoding a character , For example, store some emoji Expression or something , Please use  utf8mb4 .

View of character set

MySQL  Support many character sets , View the current  MySQL  The following statement can be used for the character sets supported in ：

show charset;