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 .
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
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
MyISAM： Every MyISAM Store three files on disk .
InnoDB： It is mainly divided into two types of files for storage
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 .
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 .
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 .
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 .
MyISAM： I won't support it InnoDB： Support
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 .
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.
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 ：
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 ：
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 ：
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 ：
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 ：
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 .
MySQL Support many character sets , View the current MySQL The following statement can be used for the character sets supported in ：