7. MySQL database table engine and character set

Don't eat tomatoes 2021-09-15 06:06:57
mysql database table engine character

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 .

9MyISAM 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;

本文为[Don't eat tomatoes]所创,转载请带上原文链接,感谢

  1. Open source: Suzhou tourism strategy based on pyecharts visual analysis
  2. 從底層開始帶你了解並發編程,五步搞定Java開發環境部署,
  3. 以商品超卖为例讲解Redis分布式锁,一招彻底帮你搞定HashMap源码,
  4. 从青铜到王者的路线,2021Java者未来的出路在哪里?
  5. JavaScript Advanced Programming (3rd Edition) Reading note 6
  6. 從青銅到王者的路線,2021Java者未來的出路在哪裏?
  7. Quelle est la voie à suivre pour les 2021 Java du bronze au roi?
  8. Prenez l'exemple de la surproduction de marchandises pour expliquer redis Distributed Lock, un tour complet pour vous aider à résoudre le code source de hashtap,
  9. 以商品超賣為例講解Redis分布式鎖,一招徹底幫你搞定HashMap源碼,
  10. Win10系统 java环境配置
  11. Non-ASCII character ‘\xe5‘ in file kf1.py on line 4, but no encoding declared; see http://python.or
  12. 手把手教你搭建微信小程序服务器(HTTPS)
  13. JavaScript Review sketch - 1
  14. sqli-labs-less-18 http头user agent+报错注入
  15. Git下载、安装、配置、配合Intellij Idea实现代码版本控制
  16. NHibernate inheritance
  17. Summary of basic knowledge points of JavaScript language (mind map)
  18. GIT télécharge, installe, configure et implémente le contrôle de version de code avec intellij idea
  19. Sqli Labs - less - 18 http header user agent + Error Reporting Injection
  20. Vous apprendrez à construire un serveur d'applet Wechat (https) à la main
  21. Non - ASCII character 'xe5' in file kf1.py on Line 4, but no Encoding declared;Voirhttp://python.or
  22. 作为一名程序员我不忘初心,Java最新实习面试经验总结,
  23. 作为一名Java面试者你应该知道的,2021最新Java常用开源库总结,
  24. 作为一个程序员,你觉得最大的悲哀是什么,2021年大厂Java岗面试必问,
  25. Configuration de l'environnement Java du système win10
  26. 作為一個程序員,你覺得最大的悲哀是什麼,2021年大廠Java崗面試必問,
  27. En tant que programmeur, quelle est la plus grande tristesse que vous ressentez? L'entrevue d'emploi Java de 2021 dans une grande usine vous demandera:
  28. Comme vous devriez le savoir en tant qu'intervieweur Java, 2021 dernier résumé des bibliothèques open source couramment utilisées pour Java,
  29. En tant que programmeur, je n'oublie pas le dernier résumé de mon expérience d'entrevue de stage en Java.
  30. 作為一名Java面試者你應該知道的,2021最新Java常用開源庫總結,
  31. New feature of Java 8. Stream (). Map (general programming method: collect. Groupingby)
  32. Computer graduation project java + SSM hospital registration system
  33. 作為一名程序員我不忘初心,Java最新實習面試經驗總結,
  34. 使用Docker部署Spring-Boot项目,论程序员成长的正确姿势,
  35. Conseils pour améliorer l'efficacité du Code Java mille fois
  36. 全网首发,我在华为做Java外包的真实经历!
  37. 全套Java视频百度云,终于找到一个看得懂的JVM内存模型了,
  38. 入职3个月的Java程序员面临转正,字节跳动 京东 360 网易面试题整理,
  39. Docker tutorial series (I) introduction to docker tutorial spring cloud mybatis distributed microservice Cloud Architecture
  40. 全網首發,我在華為做Java外包的真實經曆!
  41. Lancement de l'ensemble du réseau, je fais l'expérience réelle de l'externalisation Java à Huawei!
  42. Run around with money? Li Weijia fell into the storm of endorsement! In the face of collective hot discussion, personal attitude has become the focus of attention
  43. 全套Java視頻百度雲,終於找到一個看得懂的JVM內存模型了,
  44. Un ensemble complet de vidéos Java Baidu Cloud a finalement trouvé un modèle de mémoire JVM compréhensible.
  45. Déployez le projet Spring Boot avec docker, et parlez de la bonne posture pour que les programmeurs grandissent.
  46. 关于网络优化你必须要知道的重点,GC 堆排 Tomcat 算法题,
  47. 关于电商秒杀系统中防超卖处理方案简述,Java开发热门前沿知识,
  48. Les programmeurs Java qui sont entrés dans l'entreprise pendant trois mois ont dû faire face à une correction d'échelle, et les octets ont sauté dans le traitement des questions d'entrevue de JD 360 Netease.
  49. What is the new syntax of XX ≠ null in Java?
  50. Spring scheduled task cron expression (@ scheduled)
  51. Une brève description du plan de traitement anti - surproduction dans le système d'arrêt du commerce électronique et les connaissances de pointe du développement Java.
  52. Ce que vous devez savoir sur l'optimisation du réseau, c'est que le problème de l'algorithme Tomcat de gerbage GC,
  53. 凭借这份Java面试题集,成体系化的神级Java进阶笔记,
  54. 凭借这份Java面试题集,BAT大厂面试基础题集合,
  55. Docker Knowledge point collation
  56. Redis sur la réalisation élégante des tâches retardées
  57. 憑借這份Java面試題集,BAT大廠面試基礎題集合,
  58. Avec cet ensemble de questions d'entrevue Java, l'ensemble de questions de base d'entrevue de bat,
  59. Avec cet ensemble de questions d'entrevue Java, les notes avancées Java de niveau divin sont systématisées,
  60. Android Architect path 21 Responsive Programming RX Java thread transformation Principles