php database basics

Transactions

Four characteristics of transactions (ACID):

 Atomicity, Consistency, Isolation, Durability
- (1) Atomicity
In the entire transaction All required operations must be submitted successfully or all failed and rolled back.
- (2) Consistency
Ensure the consistency of the 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)
- (3) Isolation
Isolation requires a transaction to modify the data in the database. Before the submission is completed, other transactions are Invisible. (That is, transactions must be executed serially)

Four types of isolation are defined in the SQL standard: (The following isolation is from low to high, and concurrency is from high to low)

1) The lowest isolation for uncommitted reads level, allowing other transactions to see uncommitted data, leading to dirty reads.

2) Read Committed
Since the database is read-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, and it will be discovered when reading again. The results of reading data before and after are different, resulting in non-repeatable reading. (Read locks do not need to be released after the transaction is submitted, while write locks need to be released after the transaction is submitted)

3) Repeatable read
All data obtained by select cannot be modified, so that inconsistencies in reading before and after a transaction can be avoided . But there is no way to control phantom reading, because other transactions cannot change the selected data at this time, but can add data;

4) Serializable
All transactions are executed one after another, so that phantom reading can be avoided. For lock-based For a database that implements concurrency control, serialization requires that a range lock be obtained when executing a range query. If the database does not implement concurrency control based on locks, when a transaction that violates serial operations is detected, the transaction needs to be rolled back. affairs.

Summary: The four levels are gradually enhanced, and each level solves the problem. The higher the transaction level, the worse the performance. In most environments (read committed can be used)

Isolation level                Dirty read   Non‐repeatable read   Phantom read 
 Not yet Read uncommitted (read uncommitted) Possible
Read committed (read committed) Impossible Possible
Repeatable read Impossible Impossible Serializable (serializable) Impossible

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

(4) Persistence Once a transaction is committed, its modifications will be permanently saved in the database, Even if the system crashes at this time, the submitted modified data will not be lost.

Dirty read, non-repeatable read and phantom read

(1) Dirty read (for rollback operation): Transaction T1 updated the content of a row of records, but did not commit the modification. After transaction T2 reads the update line, and then T1 performs a rollback operation and cancels the modifications just made. Now the number of rows read by T2 is invalid (one transaction reads another transaction);

(2) Non-repeatable read (for modified operations): Transaction T1 reads a row of records, and then T2 modifies T1 The row of records just read, and then T1 reads this row of records again, and finds that the results are different from the results just read.

(3) Phantom read (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 conditions used by T1 . 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)

Index

2.1 What are the advantages and disadvantages of database indexes and when do database indexes fail?

Characteristics of index

(1) can speed up database retrieval;

(2) can only be created on tables, not views;
(3) can be created directly or indirectly;
(4) ) You can use indexes in optimization hiding;
(5) Use the query processor to execute sql statements. On a table, only one index can be used at a time.

Advantages of index

(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 an index;
(3) Speed ​​up the retrieval between database tables Links are particularly meaningful in achieving referential integrity of the database;
(4) When using grouping and sorting clauses for retrieval, the time of grouping and sorting in queries can also be significantly reduced;
(5) By using Indexes can use optimization hiders in queries to improve system performance;

Disadvantages of indexes

(1) It takes time to create and maintain indexes, which increases with the increase in number;
(2) Indexes need to occupy 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 speed of data maintenance;

Index classification

(1) Ordinary index (it has 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) Composite index
(5) The clustered index constructs a B+ tree according to the primary key of each table, and the row record data of the entire table is stored in the leaf nodes, so the leaf nodes of the clustered index also become data pages.
(6) Non-clustered index (auxiliary index) (the page node does not store an entire row of records).

Index invalid

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

Each engine supports indexes: (Core understands B-Tree indexes )

Comparison of three commonly used MySQL engine types including MyISAM, InnoDB, and Memory
Index   MyISAM index  InnoDB index  Memory index 
B-tree index Support      Support      Hash index   Not supported      Not supported      Support 
R-Tree index    Support     Not supported      Not supported
Full-text index Not supported     Not supported yet       Not supported

The index structure in the database? Under what circumstances is it appropriate to build an index?

Index structure in database?

Because when using a binary tree, the depth of the binary tree is too large, resulting in too frequent I/O reading and writing, 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. Remove the root node Except for leaf nodes, every 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 contains only one node: the root node);
- 4. All leaf nodes appear at the same level, and the leaf nodes do not contain any keyword information (can be regarded as external nodes or nodes where the query failed) points, the pointers pointing to these nodes 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. Nodes, just not drawn)

B+tree

Under what circumstances is it suitable to build an index?

(1) is a field that often appears after the keywords order by, group by, distinct, Create an index;
(2) Create an index on the result set fields of union and other set operations. The purpose of indexing is the same as above;
(3) Create an index for fields that are often used for query selection;
(4) In frequently used Create an index on the attributes of the table link;
(5) Consider using index coverage. For tables whose data is rarely updated, if users often only query a few fields, consider building indexes on these fields. Thus changing the scan of the table to the scan of the index.

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;

Mysql execution order

That is, when executing, sql is executed in the following order:
from, on, join, where, group by, having, select, distinct, union, order by
group by to Use with aggregate functions, for example:
select a.Customer,sum(a.OrderPrice) from orders a where a.Customer='Bush' or a.Customer = 'Adams' group by a.Customer
implement 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 using select from where
select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

Stored procedure

delimiter

createprocedureprocedurebill()comment'Query all sales'beginselectbillid,txtime,amtfromlmbill;end

delimiter;
Call stored procedure
call procedure_bill() ;
View stored procedures
show procedure status like 'procedure_bill';

Establish a many-to-many data table relationship in the MySQL database

In the database, if the relationship between two tables is many-to-many Relationships, such as: "student table and course schedule", one student can choose multiple courses, and one course can also be chosen by multiple students; 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 creation!

Note: Add foreign keys to the already added data table:
-Syntax: alter table table name add constraint FK_ID foreign key (your foreign key field name) REFERENCES foreign table name (the corresponding table's primary key field name); Example: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

SQL optimization

NO SQL database

What are the commonly used databases? Have you ever used Redis?

Commonly used relational databases:
Mysql, SQLServer, Oracle
Commonly used schema-less databases:
MongoDB, Merncached, Redis...
Redis
(1) Redis is a very fast non-relational database that can store keys (Key ) and five different types of values ​​(value), the key-value pair data stored in the memory can be persisted to the hard disk.
(2) Compared with Merncached
1) Both can be used to store key-value mapping, and their performance is almost the same;
2) redis can automatically write data to the hard disk in two different ways;
3) redis In addition to storing ordinary string keys, it can also store 4 other data structures. Merncached can only store string keys;
4) Redis can be used as both the main database and the auxiliary database of other storage systems;

Database engine (storage engine)

What is the 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 file through the database engine.
Taking a relational database as an example, you send a SQL statement to the database engine, and 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.

The difference between MYISAM and InnoDB engines

Main differences:
- (1) MYISAM is non-transaction-safe, while InnoDB is transaction-safe;
- (2) The granularity of NYISAM locks is table-level locks, while InnoDB supports row-level locks;
- (3) MYISAM Supports full-text indexing, while InnoDB does not support full-text indexing
- (4) MYISAM is relatively simple, so it is better than InnoDB in efficiency. Small applications can consider using MYISAM;
- (5) MYISAM tables are saved as files. Using MYISAM storage in cross-platform data transfer will save a lot of trouble;
- (6) InnoDB tables are more secure than MYISAM tables, and non-transactional tables can be switched to transaction tables without losing data;
Application scenarios:
- (1) MYISAM manages non-transactional tables. It 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.
- (2) InnoDB is used for transaction processing applications and has many 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.

Others

What are the paradigms in databases

  Currently there are 6 paradigms in relational databases: first normal form {1NF}, second normal form {2NF}, third normal form {3NF}, Buss-Codd 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).
Norm:

(1) 1NF: Ensure that each column remains atomic;

(2) 2NF: Ensure that each column in the table is related to the primary key (joint primary key);

(3) 3NF: Ensure that the Each column is directly related to the primary key (foreign key);

(4) BCNF: On the basis of 1NF, any non-primary attribute cannot depend on the primary key subset (on the basis of 3NF, the dependence on the primary key subset is eliminated);

(5)4NF: Request to delete the many-to-many relationship in the same table;

(6)5NF: Re-establish the original structure from the final structure;