MySQL database basic knowledge point reserve (organized summary)

This article brings you relevant knowledge about the mysql database. It mainly organizes some basic knowledge points of the database, including indexes, syntax order, execution order, stored procedures and other related issues. I hope everyone has to help.

Recommended learning: mysql tutorial

1. Database

1.1 Transaction

1.1.1 Four transaction characteristics (ACID)

• Atomicity (Atomicity)
  The required operations in the entire transaction Either all commits are successful, or all fail and are rolled back.
• Consistency (Consistency)
  Ensure the consistency of data in the database before and after the operation. (For example, transfers between multiple accounts of a user, but the total amount of the user remains unchanged)
• Isolation (Isolation)
  Isolation requires a transaction to perform a transaction on the data in the database Modifications are not visible to other transactions before they are committed. (That is, transactions must be executed serially)
• Persistence (Durability)
  Durability means that once a transaction is submitted, the changes to the data in the database are permanent Continuously, even if the database system encounters a failure, the operation of committing the transaction will not be lost.

SQL The standard defines four types of isolation: (The following isolation is from low to high, and concurrency is from high to low)

• Uncommitted to read.
  The lowest isolation level, allowing other transactions to see uncommitted data, which will lead to dirty reads.
• Read submitted.
  Since the database is read and write separated, the read lock is acquired when the transaction reads, but it is released immediately after reading. After the read lock is released, the data may be modified by other transactions. If you read again, you will find that the data was read before and after. The results are different, resulting in non-repeatable reads. (Read locks do not need to be released after the transaction is committed, while write locks need to be released after the transaction is committed.)
• Repeatable reading.
  All data obtained by select cannot be modified, so as to avoid inconsistent reading before and after a transaction. But there is no way to control phantom reading, because other transactions cannot change the selected data at this time, but they can add data;
• can be serialized.
  All transactions are executed one after another, so as to avoid phantom reads. For databases that implement concurrency control based on locks, serialization requires that when executing range queries, a range lock needs to be obtained. If concurrency is not implemented based on locks, Controlled database, when a transaction that violates serial operations is detected, the transaction needs to be rolled back.

Summary: The four levels are gradually enhanced, each level solves the problem. The higher the transaction level, the worse the performance.

Isolation level               Dirty read  Non‐repeatable   Phantom read 
Read uncommitted possible  possible     possible
Read committed  Not possible  possible     Perhaps 
Repeatable read read) impossible impossible   possible
Serializable   impossible  impossible    impossible

  

##Summary: Uncommitted reads will cause dirty reads—> Committed reads solve dirty reads , but it will cause non-repeatable reading -> Repeatable reading solves the problem of inconsistent reading results, but causes phantom reading (not before, but now) -> Serializable solves phantom reading, but adds a lot of range locks , may cause lock timeout;

1.1.2 Dirty read, non-repeatable read and phantom read

• Dirty read (for rollback operation): Transaction T1 updated a row of records content, but the modifications were not committed. Transaction T2 read the updated rows, and then T1 performed a rollback operation and canceled the modifications just made. Now the number of rows read by T2 is invalid (one transaction read another transaction);
• Non-repeatable read (for modified operations): Transaction T1 read a row of records, and then T2 modified it T1 just read the row of records, and then T1 read this row of records again, and found that the results were different from the results just read.
• Phantom reading (for update operations): Transaction T1 reads the result set returned by a specified where clause, and then transaction T2 inserts a new row of records, which can exactly satisfy the query used by T1 condition. Then T1 retrieves the table again, but sees the data inserted by T2 again. (I didn’t see it the first time, but I saw it the second time)

2. Index

2.1 Index features

1. Can speed up database retrieval;
2. can only be created on tables, not views;
3. can be created directly or indirectly;
4. can be hidden during optimization Use indexes in;
5. Use the query processor to execute sql statements. On a table, only one index can be used at a time.

2.1.1 Index advantages

1. Create a unique index to ensure the uniqueness of each row of data in the database table;
2. Greatly speed up data retrieval, which is the main reason for creating indexes;
3. Accelerate the links between database tables, especially in achieving database reference integrity;
4. In When using grouping and sorting clauses for retrieval, the time for grouping and sorting in the query can also be significantly reduced;
5. By using indexes, optimization hiders can be used in queries to improve system performance;

2.1.2 Index Disadvantages

1. Creating and maintaining indexes takes time, and this time increases with the increase in number;
2. Indexing requires Occupying physical space, in addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If a clustered index is established, the space required will be larger;
3. When comparing the data in the table When adding, deleting and modifying, the index also needs to be maintained, which reduces the data maintenance speed;

2.2 Index classification

(1) Ordinary index (it There are no restrictions.)
(2) Unique index (the value of the index column must be unique, but null values ​​are allowed.)
(3) Primary key index (a special unique index that does not allow null values) . Generally, the primary key index is created at the same time when creating the table.)
(4) Combined index
(5) Clustered index constructs a B-tree according to the primary key of each table, and the leaf node stores the entire The rows of the table record data, so the leaf nodes of the clustered index become data pages.
(6) Non-clustered index (auxiliary index) (the page node does not store an entire row of records).

2.3 Index invalidation

(1) If there is an or in the condition, even if there is a condition with an index, it will not be used (use or as little as possible);
(2) Like query starts with %, for example, SELECT * FROM mytable WHEREt Name like'�min';
(3) If the column type is a string, it must be enclosed in quotation marks in the condition, otherwise it will not Will use index;

2.4 Each engine supports index

MyISAM,InnoDB,Memory Three commonly used MySQL Engine Type Comparison:
三 myisam index InnoDB index Memonry index
B-Tree index supports support for support
Hash index does not support it without support
R-Tree Index  Supported       Not supported      Not supported
Full‐text index       Not supported     Not supported yet     Not supported

2.5 Index structure in the database

Because when using a binary tree , because the depth of the binary tree is too large, I/O reading and writing are too frequent, which in turn leads to low query efficiency. Therefore, using a multi-way tree structure, various operations on the B-tree can keep the height of the B-tree low.

B-tree is also called balanced multi-path search tree. The characteristics of an m-order B-tree are as follows:

• 1. Each node in the tree contains at most m children (m>=2);
• 2. Except for the root node and leaf nodes, each other node has at least (ceil (m/2)) children (where ceil (x) is a function that takes the upper limit);
• 3. The root node has at least 2 children (unless the B-tree only contains one node: Root node);
• 4. All leaf nodes appear in the same layer, and leaf nodes do not contain any keyword information (can be regarded as external nodes or nodes where the query failed, pointing to these nodes The pointers of the points are all null); (Note: Leaf nodes just have no children and pointers to children. These nodes also exist and have elements. Similar to a red-black tree, each null pointer is regarded as a leaf node, but there is no Just draw it)
  
  B tree
  
  Under what circumstances is it suitable to build an index?
  (1) For keywords that often appear in Create an index on the fields following order by, group by, distinct;
  (2) Create an index on the result set fields of union and other set operations. The purpose of establishing the index is the same as above;
  (3) It is often used as Query the selected fields and create indexes;
  (4) Create indexes on attributes that are often used as table links;
  (5) Consider using index coverage for tables whose data is rarely updated. If users often To query only a few of the fields, you can consider creating indexes on these fields to change the table scan to an index scan.

3. Mysql syntax order

That is, when the following keywords exist in sql, they must maintain this order:

select[distinct ], from, join (such as left join), on, where, group
by, having, union, order by, limit;

4. Mysql execution order

That is, the sql is executed in the following order during execution:

from, on, join, where, group by, having, select, distinct, union, order by
group by and aggregation Functions are used together,

For example:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

Realize multi-table query (inner join)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

The same can be achieved by using select from where

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

5. Stored procedure

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

Call stored procedure

call procedure_bill();

View stored procedure

show procedure status like 'procedure_bill';

6. Establish a many-to-many data table relationship

In the database, if the relationship between the two tables is a many-to-many relationship, such as: "student table and course schedule", a student can choose multiple courses, and a course can also be selected by multiple students. Select; According to the design principles of the database, a third association table should be formed.
Step 1: Create three data tables Student, Course, Stu_Cour

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

Step 2: Add foreign keys to the Stu_Cour related table

/*添加外键约束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

Complete the creation!

• Note: Add a foreign key to the already added data table:
  - Syntax: alter table table name add constraint FK_ID foreign key (your foreign key field name) REFERENCES appearance Table name (primary key field name of the corresponding table);

Example: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

7. Database engine (storage engine)

When you access the database, whether it is manual access or program access, you do not read and write the database file directly, but access the database through the database engine. document.

Take a relational database as an example. Send a SQL statement to the database engine. The database engine interprets the SQL statement, extracts the data you need and returns it to you. Therefore, to visitors, the database engine is the interpreter of SQL statements.

7.1 The difference between MYISAM and InnoDB engines

Main difference:

• ##MYISAM is non-transactionally safe. And InnoDB is transaction-safe;
• NYISAM lock granularity is table-level locks, while InnoDB supports row-level locks;
• MYISAM supports full-text indexing, while InnoDB does not support full-text indexing.
• MYISAM is relatively simple, so it is better in efficiency. InnoDB, small applications can consider using MYISAM;
• MYISAM The table is saved in the form of a file and used in cross-platform data transfer MYISAM Storage will save a lot of trouble;
(6)
• InnoDB table is more secure than MYISAM table, which can ensure that the data is not In case of loss, switch the non-transaction table to the transaction table;

Application scenario:

• MYISAM manages non-transactional tables, which provides high-speed storage and retrieval, as well as full-text search capabilities. If the application needs to perform a large number of select queries, then MYISAM is a better choice.
• InnoDB is used for transaction processing applications and has numerous features, including ACID transaction support. If your application needs to perform a large number of insert or update operations, you should use innodb, which can improve the performance of multi-user concurrent operations.

8. Database paradigm

Currently there are 6 paradigms in relational databases: first normal form {1NF}, second normal form {2NF}, third normal form {3NF}, Bath —Code normal form {BCNF}, fourth normal form {4NF}, fifth normal form {5NF, also known as perfect normal form}. The paradigm that meets the minimum requirements is the first normal form. On the basis of the first normal form, the one that further meets more standard requirements is called the second normal form {2NF}, and the other paradigms follow suit. Generally speaking, the database only needs to satisfy the third normal form (3NF).
Paradigm:

• 1NF: Ensure that each column remains atomic;
• 2NF: Ensure that each column in the table is related to the primary key (joint primary key);
• 3NF: Ensure that each column in the table is directly related to the primary key (foreign key);
• BCNF: On the basis of 1NF, any non-primary attribute cannot depend on a subset of the primary key (eliminate the dependence on the primary key subset on the basis of 3NF) Dependence on the main code subset);
• 4NF: Request to delete the many-to-many relationship in the same table;
• 5NF: Re-establish the original structure from the final structure;

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL database basic knowledge point reserve (organized summary). For more information, please follow other related articles on the PHP Chinese website!