Shulou(Shulou.com)06/01 Report--

This article will explain in detail what advanced skills are needed for MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

MySQL Storage engine Overview

The core point of the database is to store data, so data storage can not avoid dealing with disks. So how to store the data and how to store it is the key to storage. So the storage engine acts as the engine for data storage to drive data storage at the disk level.

The architecture of MySQL can be understood according to the three-tier model.

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

Concurrent support for transaction integrity constraints physical storage support index performance help

MySQL supports multiple storage engines by default to suit different database applications. Users can choose the appropriate storage engine according to their needs. Here are the storage engines supported by MySQL

MyISAMInnoDBBDBMEMORYMERGEEXAMPLENDB ClusterARCHIVECSVBLACKHOLEFEDERATED

By default, if you create a table without specifying a storage engine, you will use the default storage engine. If you want to modify 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 code

Strange, why is it gone? Check online, cancel this parameter in 5.5.3

You can query the storage engines supported by the current database in the following two ways

Show engines\ g copy code

When you create 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 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 it through show create table.

If you do not specify a storage engine, the default built-in storage engine for MySQL has been InnoDB since MySQL version 5.1. Build a table and take a look.

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

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

If you want to replace your storage engine, you can use the

Alter table cxuan003 engine = myisam; copy code

To replace, 0 rows affected will be displayed after the replacement is completed, but the operation has actually been successful

Let's use show create table to check the sql of the table.

Storage engine featur

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

MyISAM

Before version 5.1, MyISAM is the default storage engine for MySQL. MyISAM has poor concurrency and uses fewer scenarios. The main features are

If transactional operations are not supported, the features of ACID do not exist, and this is designed for performance and efficiency considerations.

Foreign key operation is not supported. If you forcibly add a foreign key, MySQL will not report an error, but the foreign key will not work.

The default lock granularity of MyISAM is table-level locks, so the concurrency performance is poor, locking is faster, lock conflicts are less, and deadlocks are less likely to occur.

MyISAM stores three files on disk with the same file name and table name, with .frm (storage table definition), .MYD (MYData, storage data), and MYI (MyIndex, storage index). It is important to note here that MyISAM caches only index files, not data files.

The index types supported by MyISAM are global index (Full-Text), B-Tree index, R-Tree index

Full-Text index: it appears to solve the problem of low efficiency of fuzzy query for text.

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

R-Tree index: there are some differences between its storage mode and B-Tree index. It is mainly designed to store spatial and multidimensional data fields as indexes. The current MySQL version only supports fields of geometry type as indexes. The advantage over BTREE,RTREE lies in scope search.

If the host where the database is located is down, the data file of MyISAM is easy to be damaged and difficult to recover.

Add, delete, modify and query performance: SELECT has high performance and is suitable for situations with more queries.

InnoDB

Since MySQL 5.1, the default storage engine has become the InnoDB storage engine, which has changed greatly from the MyISAM,InnoDB storage engine. Its main features are

Support for transaction operations, with transaction ACID isolation features, the default isolation level is repeatable read (repetable-read), through MVCC (concurrent version control) to achieve. Can solve the problem of dirty reading and unrepeatable reading. InnoDB supports foreign key operations. InnoDB default lock granularity row-level lock, concurrent performance is better, deadlock will occur. Like MyISAM, the InnoDB storage engine also has .frm file storage table structure definitions, but the difference is that InnoDB table data and index data are stored together, both on the leaf node of B+ numbers, while MyISAM table data and index data are separate. InnoDB has a secure log file that is used to recover data loss problems caused by database crashes or other situations and to ensure data consistency. InnoDB and MyISAM support the same index types, but the implementation varies greatly due to different file structures. In terms of add, delete, modify and query performance, if you perform a large number of add, delete and change operations, it is recommended to use the InnoDB storage engine, which deletes rows during deletion operations and does not rebuild the table. MEMORY

The MEMORY storage engine creates tables using content that exists in memory. Each MEMORY table actually corresponds to only one disk file in .frm format. Tables of type MEMORY are accessed quickly because their data is stored in memory. The HASH index is used by default.

MERGE

The MERGE storage engine is a combination of a group of MyISAM tables. The MERGE table itself has no data. The operation of querying, updating and deleting the MERGE type table is actually carried out on the internal MyISAM table. The MERGE table retains two files on disk, one is the .frm file storage table definition, the other is the composition of the .MRG file storage MERGE table, and so on.

Select the appropriate storage engine

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

MyISAM: if the application is usually retrieval-based, only a small number of insert, update and delete operations, and the integrity of things, the degree of concurrency is not very high, it is usually recommended to choose MyISAM storage engine. InnoDB: if you use foreign keys and require a high degree of concurrency and data consistency, you usually choose InnoDB engine. Generally, Internet companies have higher requirements for concurrency and data integrity, so InnoDB storage engine is generally used. The MEMORY:MEMORY storage engine keeps all the data in memory and can provide rapid access when it needs to be located quickly. MEMORY is usually used for small tables that are updated less frequently and for quick access to get results. The interior of MERGE:MERGE is to use MyISAM table. The advantage of MERGE table is that it can break through the restriction on the size of a single MyISAM table, and it can effectively improve the access efficiency of MERGE table by distributing different tables on multiple disks. Select the appropriate data type

One of the problems we often encounter is how to choose the right data type when building a table. Usually choosing the right data type can improve performance and reduce unnecessary trouble. Let's discuss it together. How to choose the right data type.

The choice of CHAR and VARCHAR

Char and varchar are the two data types that we often use to store strings. Char generally stores fixed-length strings, which belong to fixed-length character types, such as the following

The value char (5) stores bytes''5 bytes' cx''cx'5 bytes' cxuan''cxuan'5 bytes' cxuan007''cxuan'5 bytes

As you can see, no matter what your value is, once the length of the char character is specified, if the length of your string is not enough to specify the length of the character, then fill it with spaces, and if it exceeds the length of the string, only the characters of the specified length will be stored.

One thing to note here: if MySQL uses non-strict mode, the last row of the table above can be stored. If MySQL uses strict mode, the last row of storage on the table will report an error.

If the varchar character type is used, let's look at an example

The value varchar (5) stores bytes''1 byte 'cx''cx' 3 bytes' cxuan''cxuan'6 bytes' cxuan007''cxuan'6 bytes

As you can see, if you use varchar, the bytes stored will be stored based on the actual value. You may wonder why the length of varchar is 5, but you need to store 3 or 6 bytes, because when using the varchar data type for storage, the default is to increase the length of a string at the end, occupying 1 byte (if the length of the column declaration exceeds 255, two bytes are used). Varchar does not populate empty strings.

Char is generally used to store fixed-length strings, such as ID number, mobile phone number, mailbox, etc., and varchar is used to store variable-length strings. Because the length of char is fixed, its processing speed is much faster than VARCHAR, but the disadvantage is a waste of storage space, but with the continuous evolution of MySQL version, the performance of varchar data type is also improving, so in many applications, VARCHAR type is more used.

In MySQL, different storage engines have different principles for using CHAR and VARCHAR.

MyISAM: it is recommended to use fixed-length data columns instead of variable-length data columns, that is, CHARMEMORY: use fixed-length data columns for processing, CHAR and VARCHAR will be treated as CHAR InnoDB: VARCHAR types TEXT and BLOB are recommended

Generally speaking, when saving less text, we will choose CHAR and VARCHAR. When saving text with a large amount of data, we often choose TEXT and BLOB;TEXT and BLOB. The main difference between BLOB and BLOB is that BLOB can save binary data, while TEXT can only save character data, and TEXT can only save character data.

TEXTMEDIUMTEXTLONGTEXT

BLOB is subdivided into

BLOBMEDIUMBLOBLONGBLOB

Three, their main differences are different length of storage text and different storage bytes. Users should choose the minimum storage type to meet their needs according to the actual situation. Here are some problems in BLOB and TEXT.

TEXT and BLOB have some performance problems after deleting data. In order to improve performance, it is recommended to use the OPTIMIZE TABLE function to defragment the table.

You can also use composite indexes to improve query performance for text fields (BLOB and TEXT). A composite index is to create a hash value based on the contents of large text (BLOB and TEXT) fields and store this value in the corresponding column, so that the corresponding data rows can be found according to the hash value. Generally use hashing algorithms such as md5 () and SHA1 (). If the strings generated by the hash algorithm have trailing spaces, do not store them in CHAR and VARCHAR. Let's take a look at this use.

First create a table that records the blob field and hash values

Inserts data into the cxuan005, where the hash value is the hash value of the info.

And then insert two more pieces of data.

Insert a piece of data whose info is cxuan005

If you want to query the data whose info is cxuan005, you can query the hash column.

This is an example of a composite index, using a prefix index if you want to make a fuzzy query on BLOB.

Other ways to optimize BLOB and TEXT:

Do not retrieve BLOB and TEXT indexes unless necessary to separate BLOB or TEXT columns into separate tables. Selection of floating-point number and fixed-point number

Floating-point number refers to the value containing decimals. After the floating-point number is inserted into the specified column with more than the specified precision, the floating-point number will be rounded. The floating-point number in MySQL refers to float and double, and the fixed-point number refers to decimal. The fixed-point number can save and display data more accurately. Let's use an example to explain the accuracy of floating-point numbers.

First create a table cxuan006, just to test the floating point problem, so the data type we choose here is float

Then insert two pieces of data respectively.

Then execute the query, and you can see that the rounding of the two pieces of data from the query is different.

In order to clearly see the accuracy of floating-point and fixed-point numbers, let's take a look at another example.

First modify the two fields of cxuan006 to the same length and number of decimal places

Then insert two pieces of data

By performing the query operation, it can be found that floating-point numbers produce errors compared to fixed-point numbers.

Date type selection

In MySQL, DATE, TIME, DATETIME, and TIMESTAMP are used to represent date types.

138pictures will give you an introduction to MySQL

The differences in date types have been introduced in this article, so we will not elaborate on them here. The following is a brief introduction to the choice

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 you can use DATE if the actual application value needs to be saved. TIME is used to represent minutes and seconds, and TIME can be used if the actual application value needs to be saved. YEAR is used to represent years, and YEAR has 2-bit (preferably 4-bit) and 4-bit format years. The default is 4 digits. If the actual application only saves the year, then saving the YEAR type with 1 bytes is fine. It can not only save storage space, but also improve the operation efficiency of the table. MySQL character set

Let's take a look at the MySQL character set, which is simply a set of text symbols and coding and comparison rules. In 1960, the American Standardization Organization ANSI released the first computer character set, which is the famous ASCII (American Standard Code for Information Interchange). Since ASCII coding, each country and international organization has studied its own character set, such as ISO-8859-1, GBK and so on.

However, each country uses its own character set, which brings great difficulties to portability. Therefore, in order to unify character coding, the International Organization for Standardization (ISO) has designated a unified character standard-Unicode coding, which contains almost all character codes. Here are some common character encodings

Whether the character set is fixed-length 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 coding UTF-16 no 2-byte or 4-byte encoding UTF-32 is 4-byte coding

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

MySQL supports multiple character sets, and you can use show character set; to view all available character sets

Or use the

Select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets; copy the code

To check it out.

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

Design and use of index

We have introduced several types of indexes above and elaborated on different types of indexes, clarifying their advantages and disadvantages, and so on. Let's talk about indexes from a design point of view. One thing you must know about indexes is that indexes are the most commonly used tools used by databases to improve performance.

Overview of Index

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

As the name implies, the prefix index is to index the prefix of the column field, and the length of the prefix index is related to the storage engine. The length of MyISAM prefix index is up to 1000 bytes, and the length of InnoDB prefix index is 767 bytes. The lower the repeatability of index values, the higher the query efficiency.

In MySQL, there are mainly the following indexes

Global index (FULLTEXT): global index, currently only the MyISAM engine supports global index, its emergence is to solve the problem of low efficiency of fuzzy query against text, and is limited to CHAR, VARCHAR and TEXT columns. Hash index (HASH): the hash index is the data structure of the only key-value key-value pair used in MySQL, which is suitable for use as an index. HASH indexes have the advantage of one location, and do not need to look up node by node like a tree, but this kind of lookup is suitable for finding a single key, and the performance of HASH indexes is very low for range lookups. By default, the MEMORY storage engine uses HASH indexes, but also supports BTREE indexes. B-Tree index: B means Balance, BTree is a balanced tree, it has many variants, the most common is B + Tree, which is widely used by MySQL. R-Tree index: R-Tree is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb and Archive. Compared with B-Tree, R-Tree has the advantage of range search.

Indexes can be created when the table is created, or individually. Let's create a prefix index on cxuan004.

When we use explain for analysis, we can see that cxuan004 uses indexes.

If you do not want to use the index, you can delete the index. The delete syntax for the index is

Principles of index design

When creating an index, we should try our best to consider the following principles in order to improve the efficiency of the index.

Select the index location, and the most appropriate location for the index is the column that appears in the where statement, not the column in the selection list after the select keyword. Choose to use a unique index, as the name implies, the value of the unique index is unique, you can more quickly determine a record, for example, the student's student number is suitable to use the unique index, while the student's gender is not suitable, because no matter which value is searched, there are almost half of the rows. Index frequently used fields, and if a field is often used as a query condition, the query speed of this field greatly affects the query speed of the entire table, so indexing such fields can improve the query speed of the entire table. Do not over-index, limit the number of indexes, the more indexes, the better, each index will take up disk space, the more indexes, the more disk space is needed. Try to use the prefix index, if the value of the index is very long, then the query speed will be affected. At this time, you should use the prefix index to index some characters of the column, which can improve the retrieval efficiency. With the leftmost prefix, when you create an index with n columns, you actually create n indexes available to MySQL. A multi-column index can act as several indexes, using the leftmost column of the index to match rows, which is called the leftmost prefix. For tables that use the InnoDB storage engine, records are saved in a certain order. If there is a clear definition of the primary key, it is saved in the order of the primary key; if there is no primary key, but there is 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 saved in the order of that column. In general, the order in which primary keys are used is the fastest to delete indexed views that are no longer in use or are rarely used

MySQL has provided the view function since 5.0. let's introduce the view function below.

What is a view?

The English name of the view is view, which is a virtual table. The view is transparent to the user, it does not actually exist in the database, and the view is a table made up of database rows and columns dynamically, so what is the advantage of the view over the database table?

The advantages of a view over an ordinary table include the following

Using views can simplify the operation: instead of paying attention to the definition of the table structure, we can define the frequently used data sets into views, which simplifies the operation. Security: users can not change and delete the view at will to ensure the security of the data. Data independence: once the structure of the view is determined, it can shield the influence of the change of the table structure on the user, and the addition of columns in the database table has no effect on the view; has a certain degree of independence on the operation of the view

The actions of a view include creating or modifying a view, deleting a view, and viewing a view definition.

Create or modify a view

Use create view to create views

To demonstrate the function, let's first create a product table with three fields, id,name,price, and here are the table-building statements

Create table product (id int (11), name varchar (20), price float (1010)); copy the code

And then we insert some pieces of data into it.

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

The structure of the table after insertion is as follows

Then we create the view.

Create view v1 as select * from product; copy code

Then let's take a look at the structure of the v1 view

You can see that we put the data in product in the view, which is equivalent to creating a copy of product, but this copy has nothing to do with the table.

View u

Show tables; copy code

You can also see all the views.

The syntax for deleting a view is

Drop view v1; copy code

Can be deleted directly.

Views have other operations, such as query operations

You can also use

Describe v1; copy code

View table structure

Update View

Update v1 set name = "grape" where id = 1; copy code stored procedures

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 perform a set of specific functions in a database system. It is stored in the database system and is permanently valid after compilation. So what are the advantages of using stored procedures?

Using stored procedures can be encapsulated and can hide complex SQL logic. Stored procedures can receive parameters and return results. Stored procedures have very high performance and are generally used to execute statements in batches.

What are the disadvantages of using stored procedures?

The dependence of complex stored procedures on database is strong, and the portability is poor. Stored procedures are created using stored procedures.

After realizing what a stored procedure is, let's use a stored procedure. Here is a little trick to understand the use of delimiter. Delimiter is used to customize the Terminator. What does it mean if you use the

Delimiter? Copy the code

If so, then you can use it at the end of the sql statement; you can't make the SQL statement execute, do you believe it? We can take a look.

As you can see, we used it at the end of the line of the SQL statement; but we didn't see the execution result. Let's use the

Delimiter; copy the code

Restore the default execution conditions and take a look.

The first thing we need to create a stored procedure is to replace; with?, the following is a statement to create 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 after it is created, we can use the call method to call the stored procedure

Because we defined the use of delimiter above? To end, so it should also be used here.

Stored procedures can also accept parameters, such as when we define a situation in which parameters are received

Then we use call to call the stored procedure

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

Select * from product where id = 2; copy the code

So only the result of id = 2 is queried.

Stored procedure deletion

Only one stored procedure can be deleted 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 view

After the stored procedure is created, users may need to view information such as the status of the stored procedure in order to understand the basic situation of the stored procedure.

We can use it.

The use of show create procedure proc_name; copy code variables

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

User variable

User variables are implemented based on session variables and can be temporarily stored, and user variables are related to the connection, that is, a client-defined variable cannot be seen by other clients. When the client exits, the link is automatically released. We can use the set statement to set a variable

Set @ myId = "cxuan"; copy the code

Then use the select query condition to query out the user variable we just set

The user variable is related to the client. When we exit, this variable will disappear automatically. Now we exit the client.

Exit copy code

Now let's log back in to the client and use the select conditional query again

It is found that this @ myId is no longer available.

Local variable

Local variables in MySQL are similar to Java in that local variables in Java are methods or blocks of code where Java is located, while local variables in MySQL are scoped to the stored procedures in which they are located. MySQL local variables are declared using declare.

Session variable

The server maintains a session variable for each connected client. Can be used

Show session variables; copy code

Show all session variables.

We can set the session variable manually

Set session auto_increment_increment=1; or use set @ @ session.auto_increment_increment=2; to copy the code

Then make a query to query the session variable using the

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 server.

Can be used

Show global variables; copy code

View global variables

You can set global variables in the following two ways

Set global sql_warnings=ON;-- global cannot omit / * * or * * / set @ @ global.sql_warnings=OFF; copy code

When querying global variables, you can use the

Or

Introduction of MySQL process statement

MySQL supports the following control statements

IF

IF is used to realize logical judgment and execute different SQL statements under different conditions.

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

The CASE implementation is slightly more complex than IF, and the syntax is as follows

CASE... WHEN... THEN... ... END CASE copy code

CASE statements can also be completed using IF

LOOP

LOOP is used to implement simple loops

Label:LOOP... END LOOP label; copy the code

If. If the SQL statement is not written in, it is a simple dead-loop statement

LEAVE

Used to indicate exit from annotated process constructs, usually used with BEGIN...END or loops

ITERATE

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

REPEAT

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

REPEAT... UNTILEND REPEAT; copy code WHILE

The meaning of WHILE statement is almost the same as that of REPEAT. The difference between WHILE loop and REPEAT loop is that WHILE executes the loop when the condition is met, and REPEAT exits the loop when the condition is met.

Trigger

MySQL supports triggers since 5.0. triggers generally act on tables, trigger when the definition conditions are met, and execute the set of statements defined in triggers. Let's take a look at triggers.

For example, take a trigger: for example, if you have a log table and an amount table, and you record each amount you enter, what will happen to you? 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 record, of course, triggers not only add operations, but also update and delete operations.

Create trigger

We can create triggers in the following ways

Create trigger triggername triggertime triggerevent on tbname for each row triggerstmt copy code

The above involves a few parameters, I know you are a little confused, explain.

Triggername: this refers to the name of the trigger triggertime: this refers to the trigger trigger timing, BEFORE or AFTERtriggerevent: this refers to the trigger trigger event, there are three kinds of events: INSERT, UPDATE, or DELETE. Tbname: this parameter refers to the name of the table created by the trigger, on which table to create triggerstmt: the program body of the trigger, that is, the SQL statement

So, you can create six kinds of triggers

BEFORE INSERT 、 AFTER INSERT 、 BEFORE UPDATE 、 AFTER UPDATE 、 BEFORE DELETE 、 AFTER DELETE

The for each now above indicates that an action on any record triggers a trigger.

Let's demonstrate the operation of a trigger through an example.

Using the procuct table above as an example, we create a table of product_info product information.

Create table product_info (p_info varchar (20)); copy code

Then we create a trigger

We insert a piece of data into the product table

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

When we do the select query, we can 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 take a look at the product_info table. We expected that there was data. Let's take a look at the details.

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

Delete trigger

Triggers can be deleted using drop. The deletion syntax is as follows

Drop trigger tg_pinfo; copy code

The syntax is the same as deleting a table

View trigger

We often look at triggers, and we can view the status, syntax, and other information of triggers by executing show triggers commands.

Another query method is to query the information_schema.triggers table in the table, which can query the specified information of the specified trigger, which is much more convenient to operate.

Before adding a piece of data, the function of the trigger is to check whether the data is reasonable. For example, after checking whether the email format deletes the data correctly, it is equivalent to the function of data backup to record the operation log of the database or as the execution track of the table.

Note: there are two restrictions on the use of triggers

The trigger cannot call the stored program that returns the data to the client. You cannot also use dynamic SQL statements of CALL statements. Can not start and end statements in the trigger, such as START TRANSACTION about what advanced skills MySQL needs to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.