MySQL view, triggers, stored procedures, things, functions

View

View: is a virtual table (non-real), which is essentially "get dynamic datasets based on SQL statements and name them", and use the result set as a table by simply using the name to get a result set when you use it.

Syntax: CREATE view name as SQL statement

Emphasis 1, on hard disk, view only table structure file, no table data file

2, the view is usually used for socialize, try not to modify the data in the view

Trigger

Trigger: A trigger can be used to customize the user to the table for "Add, Delete, change" action before and after the behavior, note: No query

Transaction

Transactions: are multiple SQL operations used to move certain operations, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity. Many of these SQL statements are called Atomicity of transactions

Stored Procedures

Stored procedure: Contains a series of executable SQL statements, stored procedures in MySQL, by calling its name can execute its internal heap of SQL

Advantages:

1. SQL statement to replace program write, implement program and SQL decoupling

2. Based on the network transmission, the data volume of the alias is small, and the amount of direct SQL data is large

Cons: Programmer extension is not a convenient feature

Three ways to use the program in conjunction with the database:

Way one: MySQL: Stored Procedure program: Call stored Procedure

Way two: MySQL: No program: Pure SQL statement

Way three: MySQL: No Program: Classes and objects, i.e. ORM (essentially or purely SQL statements)

Indexing principle and slow query optimization

01 Why should I use an index
for an application, the reading and writing ratio of the database is basically 10:1, that is, the number of read and write less
and for writing, there are very few performance issues, most performance issues are slow queries
when it comes to acceleration, you have to use the index.
02 What is an index
The index is equivalent to the book directory, is a special data structure in MySQL, called Key,
The essence of indexing is to reduce the number of IO times to improve query performance by shrinking the query scope continuously
emphasis: Once an index has been created for a table, subsequent queries will look up the index first, and then find the data based on the results of the index positioning
03 Impact of the index
1, under the premise of a large number of data in the table, the creation of indexing speed is very slow,
2, after the index is created, the query performance of the table will be greatly improved, but the write performance will be reduced
04 Clustered index (primary key)
feature: A whole piece of data stored by the leaf node
05 Secondary index (UNIQUE,INDEX)
Features:
If the index is created by this field,
then the leaf node is stored: {name: The value of the primary key of the record where the name is located}
Overwrite index: All the data we want is found in the leaf node of the secondary index only
select name from user where name= ' Egon '; 
Select age from user where name= ' Egon ';

MySQL view, triggers, stored procedures, things, functions