We introduced you to the basic SQL commands, data types, and functions in the MySQL Introduction. With that knowledge in mind, you are ready to develop MySQL. However, to become a qualified developer, you need to have some more advanced skills. Let’s take a look at what advanced skills MySQL requires

MySQL Storage Engine

Storage Engine Overview

The core of a database is to store data, data storage can not avoid dealing with disk. So how data is stored and how it is stored is the key to storage. So a storage engine is a data storage engine that drives data to be stored at the disk level.

The architecture of MySQL can be understood in a three-tier pattern

Storage engine is also the component of MySQL, it is a kind of software, it can do and support the main functions are

  • concurrent
  • Support transactions
  • Integrity constraint
  • Physical storage
  • Support the index
  • Performance to help

By default, MySQL supports multiple storage engines for different database applications. You can select a proper storage engine as required. The following are storage engines supported by MySQL

  • MyISAM
  • InnoDB
  • BDB
  • MEMORY
  • MERGE
  • EXAMPLE
  • NDB Cluster
  • ARCHIVE
  • CSV
  • BLACKHOLE
  • FEDERATED

By default, if you create a table without specifying a storage engine, the default storage engine is used. To change the default storage engine, you can set default-table-type in the parameter file to view the current storage engine

show variables like 'table_type';
Copy the code

I wonder why it’s gone? Check online. This parameter was removed in 5.5.3

You can use the following two methods to query the storage engine supported by the current database

show engines \g
Copy the code

When creating a new table, you can set the storage ENGINE for the new table by adding the ENGINE keyword.

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;
Copy the code

In the figure above we specify the storage engine for MyISAM.

What if you don’t know the storage engine of the table? You can check this by running the show create table command

If you do not specify a storage engine, MySQL’s default built-in storage engine has been InnoDB since MySQL 5.1. Make a chart and look at it

As shown in the figure above, we do not specify a default storage engine, so let’s take a look at the table

As you can see, the default storage engine is InnoDB.

If your storage engine wants to be replaced, use it

alter table cxuan003 engine = myisam;
Copy the code

After the replacement, the system displays 0 Rows affected, but the operation has succeeded

Let’s use show create table to look at the SQL of the table

Storage Engine Features

Here are some common storage engines and their basic features: **MyISAM, InnoDB, MEMORY, and MERGE **

MyISAM

Before version 5.1, MyISAM was the default storage engine of MySQL. MyISAM has poor concurrency and fewer scenarios

  • Without transaction support, ACID’s properties are gone, and this is designed for performance and efficiency.

  • Foreign key operations are not supported. If you force a foreign key, MySQL will not report an error, but the foreign key will not work.

  • MyISAM default lock granularity is the table level lock, so the concurrency performance is poor, fast lock, lock conflict is less, less likely to occur deadlock situation.

  • MyISAM stores three files on disk with the same file name and table name and extension.frm(to store table definitions),.myd (to store data), and MYI(to store index). It is important to note here that MyISAM caches only index files, not data files.

  • MyISAM supports full-text, B-tree, and R-tree indexes

    Full-text indexing: It was developed to solve the problem of inefficient fuzzy queries for Text.

    B-tree index: All index nodes are stored according to the data structure of the balanced Tree. All index data nodes are in the leaf node

    R-tree index: It is different from B-tree index in storage mode. It is mainly designed for indexing fields of storage space and multidimensional data. The current version of MySQL only supports indexes of the geometry type.

  • MyISAM data files are easily damaged and difficult to recover if the host where the database resides goes down.

  • Add, delete, modify, and search performance: SELECT has high performance and is suitable for many queries

InnoDB

Since MySQL 5.1, the default storage engine has become the InnoDB storage engine. Compared to MyISAM, InnoDB storage engine has changed a lot. Its main features are

  • Supports transaction operations with transaction ACID isolation, the default isolation level isRepetable-readAnd throughMVCC (Concurrent Version Control)To achieve. To be able to solveDirty readandUnrepeatable readThe problem.
  • InnoDB supports foreign key operations.
  • InnoDB default lock granularityRow-level locks, the concurrency performance is good, deadlock will occur.
  • Like MyISAM, InnoDB storage engine is available.frm File storage table structureInnoDB’s table data and index data are stored together on B+ leaf nodes, while MyISAM’s table data and index data are separate.
  • InnoDB has a secure log file. This log file is used to recover data loss caused by database crashes or other situations and ensure data consistency.
  • InnoDB and MyISAM support the same index types, but the implementation varies greatly due to different file structures.
  • In terms of performance, InnoDB storage engine is recommended if a large number of add, delete, change and query operations are performed. InnoDB storage engine deletes rows during deletion operations and does not rebuild tables.

MEMORY

The MEMORY storage engine uses what is in MEMORY to create tables. Each MEMORY table actually corresponds to only one disk file in the.frm format. Memory-type tables are fast to access because their data is stored in MEMORY. The default HASH index is used.

MERGE

The MERGE storage engine is a group of MyISAM tables. The MERGE table does not have data. The MERGE table queries, updates, and deletes internal MyISAM tables. MERGE tables keep two files on disk, one is the.frm file storage table definition, one is the.mrg file storage table composition, and so on.

Select the appropriate storage engine

In the actual development process, we often choose the appropriate storage engine according to the characteristics of the application.

  • MyISAM: The MyISAM storage engine is usually recommended if the application is typically retrieval oriented, with only a few inserts, updates, and deletes, and the integrity and concurrency of things is not very high.
  • InnoDB: If foreign keys are used, high concurrency is required, and data consistency is required, then InnoDB engine is usually selected. Generally, Large Internet companies have high requirements on concurrency and data integrity, so InnoDB storage engine is generally used.
  • MEMORY: The MEMORY storage engine keeps all data in MEMORY, providing extremely fast access when quick location is required. MEMORY is typically used to update small tables that are infrequently used for fast access to get results.
  • MERGE: Internal MERGE tables use MyISAM tables. MERGE tables can overcome the size limitation of a single MyISAM table and improve access efficiency by distributing different tables on multiple disks.

Choose the appropriate data type

One of the most common problems we encounter is how to select the right data type when building a table. Choosing the right data type can often improve performance and reduce unnecessary trouble.

CHAR and VARCHAR options

Char and vARCHar are two data types that we often use to store strings. Char typically stores a fixed-length string, which is a fixed-length character type, as shown below

value char(5) Store the byte
‘ ‘ ‘ ‘ 5 bytes
‘cx’ ‘cx ‘ 5 bytes
‘cxuan’ ‘cxuan’ 5 bytes
‘cxuan007’ ‘cxuan’ 5 bytes

As you can see, no matter what your value is written to, once you specify the length of a char character, if your string is not long enough to specify the length of a char character, then fill it with a space. If it is longer than the string length, only the specified length of a char character is stored.

Note here that the last row of the table above can be stored if MySQL is using non-strict mode. If MySQL is using strict mode, the last row above the table is stored in an error.

If the VARCHAR character type is used, let’s look at an example

value varchar(5) Store the byte
‘ ‘ ‘ ‘ 1 byte
‘cx’ ‘cx ‘ 3 bytes
‘cxuan’ ‘cxuan’ 6 bytes
‘cxuan007’ ‘cxuan’ 6 bytes

As you can see, if you use VARCHAR, the stored bytes will be stored according to the actual value. You may wonder why vARCHAR has a length of 5 but needs to store 3 or 6 bytes. This is because storage with the VARCHAR data type by default adds a string length of 1 byte at the end (two bytes if the column declaration is longer than 255). Varchar does not fill empty strings.

Char is used to store strings of fixed length, such as id number, mobile phone number, email address, etc. Use vARCHAR to store strings of variable length. Since the char length is fixed, it is much faster than VARCHAR, but its disadvantage is that it wastes storage space. However, with the evolution of MySQL version, the performance of VARCHAR data type is also improving, so in many applications, VARCHAR type is used more.

In MySQL, different storage engines use CHAR and VARCHAR differently

  • MyISAM: It is recommended to use fixed-length data columns instead of variable length data columns, namely CHAR
  • MEMORY: Processing with fixed length, CHAR and VARCHAR are treated as CHAR
  • InnoDB: The VARCHAR type is recommended

The TEXT with a BLOB

CHAR and VARCHAR are preferred for small volumes of TEXT, while TEXT and BLOB are preferred for large volumes of TEXT. The main difference between TEXT and BLOB is that blobs can hold binary data; TEXT, on the other hand, can only hold character data

  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

The BLOB is subdivided down

  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

There are three types of BLOB and TEXT. The main difference between them is that the length of TEXT is different from that of bytes. Users should select the minimum storage type that meets their requirements based on actual conditions

TEXT and BLOB can have some performance issues after deleting data, so it is recommended to use the OPTIMIZE TABLE function to improve performance.

Composite indexes can also be used to improve query performance for TEXT fields (BLOB and TEXT). Composite indexes create hash values based on the contents of large TEXT fields (BLOB and TEXT) and store the hash values in corresponding columns so that the corresponding rows can be found based on the hash values. Hash algorithms such as MD5 () and SHA1() are commonly used. If hashes generate strings with trailing Spaces, do not store them in CHAR and VARCHAR. Let’s look at how this works

Start by creating a table that records bloB fields and hash values

Insert data to cXuan005 with the hash value as the hash value of info.

Then insert two more pieces of data

Insert a data line with INFO cXuan005

If you want to query data with INFO cXuan005, you can do so by querying the hash column

This is an example of a composite index, which uses a prefix index if you want to fuzzily query bloBs.

Other ways to optimize BLOB and TEXT:

  • Do not retrieve BLOB and TEXT indexes unless necessary
  • Separate BLOB or TEXT columns into separate tables.

Floating-point and fixed-point options

Floating point numbers are values that contain decimals. Floating point numbers are rounded up after being inserted into a specified column with a specified precision. In MySQL, floating point numbers are floats and doubles. Here is an example to explain the accuracy of floating points

First, we create a table cXuan006 just to test floating-point problems, so our data type is float

Then insert two pieces of data separately

The query is then executed, and you can see that the two results are rounded differently

To see the accuracy of floating-point versus fixed-point numbers clearly, let’s look at another example

First change the two fields of CXuan006 to the same length and decimal number

Then insert two pieces of data

When you perform a query operation, you can see that floating point numbers produce errors compared to fixed-point numbers

Date type selection

In MySQL, DATE, TIME, DATETIME, TIMESTAMP are used to represent the DATE type

138 images to get you started with MySQL

This article has covered the difference between date types, but we won’t elaborate on it here. Here are the options

  • TIMESTAMP is related to the time zone and better reflects the current time. If the recorded date needs to be used by people in different time zones, it is best to use TIMESTAMP.
  • DATE is used to represent the year, month and day, and can be used if the actual application value needs to be saved.
  • TIME is used to represent minutes and seconds, if the actual application value needs to save minutes and seconds.
  • YEAR is used to represent a YEAR, and YEAR has two bits (preferably four) and four bit formats. The default is 4 bits. In practice, if you only store years, storing the YEAR type with 1 bytes is perfectly fine. It not only saves storage space, but also improves table operation efficiency.

MySQL character set

Let’s take a look at the MySQL character set. Simply put, a character set is a set of character symbols and encoding and comparison rules. In 1960, ANSI published the first computer character set, known as the American Standard Code for Information Interchange (ASCII). Since ASCII coding, every country and international organization has developed its own character set, such as ISO-8859-1, GBK, etc.

But each country’s use of its own character set poses great difficulties for portability. Therefore, in order to unify character encoding, the International Organization for Standardization (ISO) has specified a unified character standard – Unicode encoding, which contains almost all character encoding. Here are some common character encodings

Character set Is no longer encoding
ASCII is Single-byte 7-bit encoding
ISO-8859-1 is Single-byte 8-bit encoding
GBK is Double byte encoding
UTF-8 no 1-4 byte encoding
UTF-16 no 2-byte or 4-byte encoding
UTF-32 is 4-byte encoding

For database, character set is very important, because most of the data stored in database are all kinds of characters, character set is very important for database storage, performance, system transplantation.

MySQL supports multiple character sets. You can use show character set. To view all available character sets

Or use

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;
Copy the code

To look at it.

Use information_schema.character_set to view character sets and collation rules.