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

This paper mainly introduces the view of MySQL database and the stored procedure of MySQL database, as well as the two storage engines of MySQL, MyISAM and InnoDB. I hope you can get something through this article.

I. Preface

A MySQL view (View) is a virtual table. Like a real table, a view is made up of columns and rows, but the view does not actually exist in the database. The data for rows and columns comes from the table used in the query that defines the view and is dynamically generated when the view is used.

II. MySQL View 2.1View function

When we use SQL statements for multi-table query, the command is very lengthy and troublesome, if such an operation is also very frequently used, it will increase the workload of the staff, after all, there is no guarantee that such a long code will not be written wrong, and many times such a complex query will also cause the server resource occupation ratio to become larger, so what good way do we have to solve this problem?

In fact, we can summarize the data contents that need to be queried frequently (which may exist in multiple tables) into a virtual table, which not only facilitates the staff query, but also reduces the burden on the server. and while saving disk space, it can also support dynamic changes of data, and such tables are "views".

In fact, a view is a logical table that does not store data itself. Instead, it is saved as a select statement in the data dictionary (which can be understood as a container). Through the view, you can display the backup data of the base table; the view data comes from the table used in the query of the view, and is generated dynamically using the view.

Base table: the table base table used to create views

2.2 Features, advantages and disadvantages of the view

The view takes up less resources, just like the moon in the water, which does not actually exist, but will change according to the change of the base table.

Advantages:

1) simple: users who use views do not need to care about the structure, association conditions and filter conditions of the corresponding tables at all, which is already the result set of filtered composite conditions for users.

2) Security: users who use the view can only access the result set they are allowed to query, and the permission management of the table cannot be limited to a row or column, but it can be easily implemented through the view.

3) data independence: once the structure of the view is determined, you can shield the impact of changes in the table structure on the user, and adding columns to the source table has no effect on the view; if the source table modifies the column name, it can be solved by modifying the view without affecting the visitors.

All in all, most of the use of views is to ensure data security and improve query efficiency.

Disadvantages:

1) poor performance: the database must convert the view query into a query on the basic table, and if the view is defined by a complex multi-table query, then even a simple query of the view, it will take a certain amount of time for the database to turn it into a complex combination.

2) modification restrictions: when users try to modify some information of the view, the database must convert it into changes to some information of the basic table, which is very convenient for simple views, but for more complex attempts, it may be unmodifiable.

2.3 A brief introduction to the View algorithm

For the algorithm of the view, briefly, we need to specify the basic syntax when creating the view:

Create + [algorithm = temptable/merge/undefined] + view + View name + as + select statement with check option

View algorithm is a way for the system to parse the select statements of views and external query views. There are three view algorithms, which are:

Undefined: undefined (default), this is not an algorithm actually used, but a "pass the buck" algorithm. In the undefined case, tell the system that the view does not define an algorithm, please choose for yourself. Temptable: temporary table algorithm, the system first executes the select statement of the view, and then executes the external query statement. Merge: merging algorithm in which the system merges the select statement corresponding to the view with the select statement of the external query view before executing. This algorithm is more efficient, and it is often chosen by default when no algorithm is defined. 2.4 create View command

Create a general view command format:

Create view view name as select + content 3, MySQL stored procedure (inclined to software development direction) 3.1 what is a stored procedure?

To put it simply, it is a set of SQL statements, which is powerful and can implement some complex logic functions, similar to the methods in the JAVA language.

Note: stored procedures are somewhat similar to triggers, both are a set of SQL sets, but stored procedures are called actively and are more powerful than triggers, which are called automatically after something is triggered.

3.2 characteristics of stored procedures

There are input and output parameters, variables can be declared, if/else, case,while and other control statements, by writing stored procedures, you can achieve complex logic functions

General features of functions: modularization, encapsulation, code reuse

Fast, only the first execution needs to go through the compilation and optimization steps, and the subsequent calls can be executed directly, omitting the above steps

Fourth, MySQL storage engine 4.1 storage engine introduction

Data in MySQL is stored in files or memory using a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately provides a wide range of functions and capabilities.

These different technologies and their corresponding functions are called "storage engines" in MySQL. MySQL provides several different storage engines that can be pre-set or enabled in the MySQL server.

Architecture of 4.2MySQL

The above is mainly composed of six parts, including various components, storage engine and file system. Let's make a brief introduction.

Connectors: connecting components, requiring driver support; mainly used for interaction between code programs in different languages and MySQL

Connection Pool: connection pool component; mainly for caching requirements such as managing and buffering users' connections, threading, etc.

Management Service & Utilities: components of management services and tools, such as backup and restore, MySQL replication, clustering, etc.

SQL Interface:SQL interface; mainly accepts the user's SQL command statements and returns the results that the user needs to query

Parser: query parser; when SQL command statements are passed to the parser, they are validated and parsed by the parser (permissions, syntax structures)

Optimizer: query optimizer; the SQL statement optimizes the query using the query optimizer before execution; for example:

Select id,name from where hobby = 'read'

1) this select statement query will first select according to the where statement

2) secondly, attribute projection is carried out according to id and name

3) output the final query result by combining two query conditions

Caches & Buffers: cache; when there is a hit query result in the query cache, the query statement can directly fetch data from the query cache

Plugggable Storage Engines: plug-in storage engine for managing operational data (ways to store, update, and query data)

Introduction of 4.3MyISAM storage engine 4.3.1MyISAM

The MyISAM storage engine is the default storage engine prior to version 5.5 of the MySQL database system. The problem taken into account is that the number of queries is much greater than the number of updates, so the data need to be read faster and do not need to take up a lot of memory and storage resources.

MyISAM manages non-transactional tables, provides index and field management, and has a table locking mechanism to optimize multiple concurrent read and write operations.

Characteristics of 4.3.2MyISAM

Transactions are not supported

Table locking mechanism that locks the entire table when data is updated

Databases block each other in the process of reading and writing

Cached indexes can be set through key_buffer_size to improve access performance and reduce disk IO read and write pressure.

High speed and low resource consumption

Foreign key constraints are not supported, only full-text indexing is supported

The storage file is

1) .frm file storage table definition

2). MYD data file extension

3) .MYI index file extension

The applicable scenario business of 4.3.2MyISAM does not need to support transactions; services that read more services unilaterally or write data unilaterally; services with less concurrent access; services with less data modification; introduction of 4.4InnoDB storage engine 4.4.1InnoDB with lower hardware resources of the server

InnoDB storage engine is the default storage engine after version 535 of MySQL database system, which is generated to solve or optimize the shortcomings of MyISAM storage engine.

It can be said that the InnoDB storage engine is designed for maximum performance when dealing with large amounts of data.

4.4.2InnoDB features support transactions and four transaction isolation levels; row-level locking, except for full table scanning, is table-level locking; read and write blocking is related to transaction isolation level; cache efficiency, which can cache both indexes and data; tables and primary keys are stored in clusters; support partitions and tablespaces; support foreign key constraints; 4.4.3InnoDB application scenario business requires transaction support; high concurrency business Frequent business data updates; high consistency requirements for business data such as Weibo: high hardware resources for banking business. Fifth, how to configure an appropriate storage engine? 5.1 steps to configure and modify the storage engine

Select the appropriate storage engine according to your requirements, and then consider how to modify it

(1) View the types of storage engines that can be configured in the database

(2) View the type of storage engine being used by the table

(3) configure the storage engine to the selected type

5.2 specific steps 5.2.1 View the types of storage engines that the database can configure Mysql > show engines +- -+ | Engine | Support | Comment | Transactions | XA | Savepoints | +- -- + | InnoDB | DEFAULT | Supports transactions Row-level locking, and foreign keys | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | | MEMORY | YES | Hash based, stored in memory Useful for temporary tables | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | | MyISAM | YES | MyISAM storage engine | NO | | CSV | | YES | CSV storage engine | NO | | ARCHIVE | YES | Archive storage engine | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | | | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | +-| -+ 9 rows in set (0.00 sec)

The Engine column represents the types of engines supported by the current version of MySQL

The Support column indicates whether the corresponding engine can be used, and DEFAULT indicates the type used by default. This version (MySQL5.7.17) is InnoDB.

The Transactions column indicates whether the corresponding engine supports transactions.

5.2.2 View the type of storage engine the table is using Mysql > show table status from student where name = 'info' +- -- + -+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-+ -+- -+-+ | info | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | 5 | 2020-01-08 19:47:38 | NULL | NULL | utf8_general_ci | NULL | +-+- +- -+ 1 row in set (0.01 sec)

Or you can use the create command to view the storage engine of the table

Mysql > show create table info +- -+ | Table | Create Table | | +- - -+ | info | CREATE TABLE "info" ("id" int (3) NOT NULL AUTO_INCREMENT) "name" varchar (6) DEFAULT NULL, "score" decimal (5) DEFAULT NULL PRIMARY KEY ("id") ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set Sec) 5.2.3 configure the storage engine for the selected type

Use the alter command: alter table table name engine= engine name

Mysql > alter table info engine=Myisam;Query OK, 4 rows affected (0.01sec) Records: 4 Duplicates: 0 Warnings: 0mysql > show create table info +- -+ | Table | Create Table | | +- - -+ | info | CREATE TABLE "info" ("id" int (3) NOT NULL AUTO_INCREMENT) "name" varchar (6) DEFAULT NULL, "score" decimal (5) DEFAULT NULL PRIMARY KEY ("id") ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 | +-+- -+ 1 row in set (0.00 sec)

Or add: default-storage-engine= engine name under the mysqld of MySQL main configuration file / etc/my.cnf during manual compilation and installation, and then restart the service.

In addition, you can specify the storage type when creating the table: create table test (id int) engine=MyISAM

After reading the above, do you have any further understanding of the view, stored procedure and storage engine of the MySQL database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.

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.