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 ：
本文为[Don't eat tomatoes]所创，转载请带上原文链接，感谢