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.

Index design and use

We have introduced several types of indexes and explained the advantages and disadvantages of different types of indexes. Now we are going to talk about indexes from a design perspective.

The index overview

All MySQL types can be indexed, and using indexes on related columns is the best way to improve SELECT query performance. MyISAM and InnoDB both use BTREE as an index. MySQL 5 does not support functional indexes, but does support prefix indexes.

A prefix index, as its name implies, indexes the prefix of a column field. The length of the prefix index depends on the storage engine. MyISAM prefix index length up to 1000 bytes, InnoDB prefix index length up to 767 bytes, the less repeatable the index value, the higher the query efficiency.

In MySQL, there are mainly the following indexes

  • Global index (FULLTEXT): Global index. Currently, only MyISAM supports global index. It was introduced to solve the problem of inefficient fuzzy query for TEXT and is limited to CHAR, VARCHAR, and TEXT columns.
  • HASH index: hash index is the only key-value data structure used in MySQL. It is suitable for indexing. HASH indexes have the benefit of locating once rather than looking node by node like a tree, but this lookup is appropriate for individual key lookups, where performance is low for range lookups. By default, the MEMORY storage engine uses HASH indexes, but also supports BTREE indexes.
  • B-tree indexes: B stands for Balance, BTree is a kind of balanced Tree, it has many varieties, the most common is B+ Tree, it is widely used by MySQL.
  • R - Tree indexes: R-tree is rarely used in MySQL and supports only the geometry data type. Only MyISAM, BDb, InnoDb, NDb, and Archive support this type of storage engine. Compared with B-tree, R-tree has the advantage of range search.

Indexes can be created at table creation time or separately. To do this separately, we create prefixed indexes at CXuan004

We used Explain for analysis and saw index usage at CXuan004

If you do not want to use an index, you can drop it

Index design principles

When creating an index, consider the following principles to improve index efficiency.

  • chooseThe index position, select the most appropriate location for the index to appear inwhereThe column in the statement, instead ofselectColumn in the selection list after keyword.
  • Choose to useThe only indexAs the name implies, the value of a unique index is unique, so that a record can be determined more quickly. For example, a student’s student number is suitable for a unique index, while a student’s gender is not suitable for a unique index, because no matter which value is searched, almost half of the rows are searched.
  • Create indexes for frequently used fields. If a field is frequently used as a query criterion, the query speed of this field greatly affects the query speed of the entire table. Therefore, create indexes for such fields to improve the query speed of the entire table.
  • Do not over-index, limit the number of indexes, not the more the better, each index will take up disk space, the more indexes, the more disk space required.
  • Try to useThe prefix indexIf the index value is very long, the query speed will be affected. In this case, prefix indexes should be used to index certain characters of the column to improve the search efficiency.
  • Using the left-most prefix, when you create an index with n columns, you actually create n indexes available to MySQL. A multi-column index can function as several indexes, matching rows with the leftmost column of the index, called the leftmost prefix.
  • For tables using the InnoDB storage engine, records are saved in a certain order. If there is an explicit primary key definition, it is saved in the order of the primary key; If there is no primary key but a unique index, it is saved in the order of the unique index. If there is neither a primary key nor a unique index, an internal column is automatically generated in the table and stored in the order of that column. In general, the order in which primary keys are used is the fastest
  • Delete indexes that are no longer used or rarely used

view

MySQL has provided view functionality since 5.0. Here we introduce the view functionality.

What is a view

A view, which is called a view, is a virtual table. A view is transparent to the user and does not actually exist in the database. A view is a dynamic table composed of database rows and columns. What is the advantage of a view over a database table?

The advantages of views over regular tables include the following

  • Using views simplifies operations: Instead of focusing on the definition of a table structure, we can simplify operations by defining frequently used data sets as views.
  • Security: Users cannot modify or delete views at will, which ensures data security.
  • Data independence: Once the structure of the view is determined, the impact of table structure changes on users can be shielded, and the addition of columns to the database table has no impact on the view; Has certain independence

Operations on views

Operations on views include creating or modifying views, deleting views, and viewing view definitions.

Create or modify a view

Use create View to create the view

To demonstrate the functionality, create a table called product with three fields: id, name, price

Create table product(id int(11),name varchar(20),price float(10,2));Copy the code

And then we insert a few pieces of data into it

Insert into the product values (1, "apple", "3.5"), (2, "banana", "4.2"), (3, "melon", "1.2");Copy the code

The table structure after insertion is as follows

Then we create the view

create view v1 as select * from product;
Copy the code

Then let’s look at the structure of the V1 view

You can see that we put the data from the product in the view, which also creates a copy of the product, except that the copy is independent of the table.

The view to use

show tables;
Copy the code

You can also see all the views.

The syntax for deleting a view is

drop view v1;
Copy the code

Can be directly deleted.

Views also have other operations, such as query operations

You can still use it

describe v1;
Copy the code

View table structure

Update the view

update v1 set name = "grape" where id = 1;
Copy the code

The stored procedure

MySQL has supported stored procedures and functions since 5.0.

So what is a stored procedure?

A stored procedure is a set of SQL statements that accomplish a set of specific functions in the database system. It is stored in the database system and lasts forever after compilation. So what are the advantages of using stored procedures?

  • The use of stored procedures is wrapper able to hide complex SQL logic.
  • Stored procedures can take parameters and return results
  • Stored procedures perform very well and are typically used to execute statements in batches

What are the disadvantages of using stored procedures?

  • Stored procedures are complex to write
  • Stored procedure has strong dependence on database and poor portability

Stored procedure usage

Stored procedure creation

Now that you know what stored procedures are, let’s use stored procedures. There’s a little trick here, which is delimiter. Delimiter is used for custom terminators

delimiter ?
Copy the code

Then you use it at the end of the SQL statement; Can’t make SQL statement execute, don’t believe? We can look at that

As you can see, we have used it at the end of the SQL statement; But we didn’t see the results. Here we use

delimiter ;
Copy the code

Restore the default execution conditions and look again

When we create a stored procedure, we start with; Replace with? Below is the creation statement of a stored procedure

mysql> delimiter ?
mysql> create procedure sp_product()
    -> begin
    -> select * from product;
    -> end ?
Copy the code

A stored procedure is actually a function, so once created, we can call the stored procedure using the Call method

Because we defined using delimiter above? So it should be used here as well.

Stored procedures can also accept parameters, as we define a case that accepts parameters

We then call the stored procedure using call

As you can see, when we call id = 2, the SQL statement for the stored procedure is equivalent to

select * from product where id = 2;
Copy the code

So only the result with id = 2 will be found.

Stored procedure Deletion

You can delete only one stored procedure at a time. The syntax for deleting a stored procedure is as follows

drop procedure sp_product ;
Copy the code

Just use sp_product instead of adding ().

Stored Procedure Viewing

After a stored procedure is created, you may need to view its status to learn about the basic information about the procedure

We can use

show create procedure proc_name;
Copy the code

Use of variables

In MySQL, variables can be divided into two categories, namely system variables and user variables, which is a rough classification. But according to the actual application, it is refined into four types, namely, local variable, user variable, session variable and global variable.

The user variables

User variables are implemented based on session variables and can be held temporarily. User variables are connected, meaning that variables defined by one client cannot be seen by other clients. When the client exits, the link is automatically released. We can set a variable using the set statement

set @myId = "cxuan";
Copy the code

Then use the SELECT query condition to find the user variable we just set

The user variable is associated with the client, and when we exit, it automatically disappears, so now we exit the client

exit
Copy the code

Now let’s log back into the client and use the SELECT condition query again

It turns out there’s no @myID anymore.

A local variable

Local variables in MySQL are similar to Java in that local variables in Java are methods or code blocks in Java, whereas local variables in MySQL are scoped to stored procedures. MySQL local variables are declared using DECLARE.

Session variable

The server maintains a session variable for each connected client. You can use

show session variables;
Copy the code

Display all session variables.

Session variables can be set manually

set session auto_increment_increment=1; Or set @@session.auto_increment_increment=2;Copy the code

Then query the session variable usage

Or use

The global variable

When the service starts, it initializes all global variables to default values. Its scope is the entire life cycle of the server.

You can use

show global variables;
Copy the code

View global variables

You can set global variables in one of two ways

set global sql_warnings=ON; /** / set @@global.sql_warnings=OFF;Copy the code

This parameter is used when querying global variables

Or is it

MySQL flow statements

MySQL supports the following control statements

  • IF

IF is used to make logical judgments and execute different SQL statements according to different conditions

IF ... THEN ...
Copy the code
  • CASE

The CASE implementation is slightly more complex than IF, with the following syntax

CASE ... WHEN ... THEN... . END CASECopy the code

CASE statements can also be done using IF

  • LOOP

LOOP is used to implement simple loops

label:LOOP
     ...
END LOOP label;
Copy the code

If… Do not write SQL statements, then it is a simple infinite loop statement

  • LEAVE

Used to indicate exit from annotated process construction, usually with BEGIN… END or loop together

  • ITERATE

The ITERATE statement must be used in the loop to skip the rest of the current loop and go straight to the next loop.

  • REPEAT

A loop control statement with a condition that exits the loop when the condition is met.

REPEAT
   ...
   UNTIL
END REPEAT;
Copy the code
  • WHILE

The meaning of the WHILE statement is similar to that of REPEAT. The difference between the WHILE loop and REPEAT loop lies in that WHILE executes the loop only when the condition is met, WHILE REPEAT exits the loop when the condition is met.

The trigger

MySQL supports triggers since 5.0. Triggers typically operate on tables, fire when a condition is met, and execute a set of statements defined in the trigger.

Consider triggers, for example: If you have a log table and a dollar table, what happens if you log every dollar you enter? Insert data into both the amount table and the log table? If you have a trigger, you can enter data directly into the amount table, and the log table will automatically insert a log entry. Of course, triggers are not only new operations, but also update and delete operations.

Create trigger

We can create triggers in the following way

create trigger triggername triggertime triggerevent on tbname for each row triggerstmt
Copy the code

There are a few parameters involved, and I know you’re confused, but explain.

  • triggernameThis is the name of the trigger
  • triggertime: This refers to the trigger time, yesBEFOREorAFTER
  • triggereventThis refers to the trigger event, there are three types of events:INSERT, UPDATE, or DELETE.
  • tbnameThis parameter refers to the name of the table on which the trigger is created
  • triggerstmt: The program body of the trigger, that is, the SQL statement

So, you can create six triggers

BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE

For each now above means that any action on a record triggers the trigger.

Let’s use an example to illustrate the operation of a trigger

Again using the procuct table above, we create a product_info product information table.

create table product_info(p_info varchar(20)); 
Copy the code

Then we create a trigger

We insert a piece of data into the Product table

Insert into the product values (4, "pineapple", 15.3);Copy the code

We perform the select query and see that there are now four pieces of data in the Product table

We didn’t insert data into the product_INFO table. Now let’s look at the product_INFO table. We expect there to be data

When was this data inserted? We inserted this data when we created trigger TG_pinfo.

Delete trigger

A trigger can be dropped using drop. The syntax for deleting a trigger is as follows

drop trigger tg_pinfo;
Copy the code

The syntax is the same as deleting a table

View trigger

Triggers are often looked at, and information about the status, syntax, and so on can be seen by executing the command Show Triggers.

Another way to query is to query the Information_schema. triggers table in the table, which makes it much easier to query for specified information for specified triggers

Function of flip-flop

  • Before adding a piece of data, check that the data is reasonable, such as the mail format is correct
  • After data is deleted, data is backed up
  • You can record database operation logs or use them as execution traces of tables

Note: There are two limitations to the use of triggers

  1. The triggering program cannot call the stored program that returns data to the client. Dynamic SQL statements for CALL statements cannot also be used.
  2. Statements, such as START TRANSACTION, cannot be started and terminated in triggers