PHP Database Basics

Transaction

Four characteristics of a transaction (ACID):

Atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability)
-(1) atomicity
All the operations in the whole transaction are either committed successfully or all failed to rollback.
-(2) consistency
Ensure consistency between data operations in the database before and after operation. (such as a user's transfer between multiple accounts, but the total amount of the user is constant)
-(3) Isolation
Isolation requires a transaction to modify the data in the database, which is not visible to other transactions until the commit is completed. (i.e. serial execution between transactions)

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

1) Uncommitted reads the lowest isolation level, allowing other transactions to see data that is not committed, which can result in dirty reads.

2) submitted read
Because the database is read-write separation, the transaction reads the time to obtain a read lock, but after reading the release immediately after the release of the read lock, it is possible to modify the data by other transactions, and then read is to find the results of reading data before and after the difference, resulting in non-repeatable reading. (read locks do not need to be released after transaction commits, and write locks are released after transaction commits)

3) Repeatable Reading
None of the data obtained by select can be modified, thus avoiding the inconsistency of the read before and after a transaction. However, there is no way to control phantom reading, because other transactions cannot change the selected data at this time, but can increase the data;

4) Serializable
All transactions are executed one after the other, which avoids Phantom reads, and for databases that implement concurrency control based on locks, serialization requires that a range lock be acquired when executing a scope query, and if a database that does not implement concurrency control based on locks is checked for transactions that violate serial operations, the transaction needs to be rolled back.

Summary: Four levels are gradually enhanced, each level solves the problem, the higher the transaction level, the worse the performance, most of the environment (read Committed can be used)

Isolation level dirty read non-repeatable read Phantom read
Uncommitted reads (READ UNCOMMITTED) may be possible
Read Committed may not be possible
REPEATABLE READ (Repeatable Read) could not possibly
Serializable (serializable) cannot be impossible

Summary: Uncommitted reads can cause dirty reads, read-committed to resolve dirty reads, but will cause non-repeatable read-and repeatable reads to resolve inconsistent read results, but resulting in Phantom read (not previously, now has), Serializable solved the Phantom read, but the addition of a lot of range lock, may cause lock timeout;

(4) Persistence once a transaction commits, its modifications are persisted to the database, and the committed modification data is not lost even if the system crashes.

Dirty reads, non-repeatable reads, and Phantom reads

(1) Dirty Read (Action for rollback): Transaction T1 updates the contents of a row of records, but does not commit the changes, the transaction T2 reads the updated row, and T1 performs a rollback operation, canceling the modification just made. Now the number of rows read by T2 is invalid (one transaction reads another);

(2) Non-repeatable read (for modified operation): Transaction T1 reads a row of records, and then T2 modifies the row record that T1 just read, and then T1 reads the row again, and finds that the result is different from the one just read.

(3) Phantom Read (Action for update): Transaction T1 reads a result set returned by a specified where clause, and then inserts a new row of records T2 the transaction, which exactly satisfies the query criteria used by T1. T1 then retrieves the table again, but sees the data that T2 inserted. (I didn't see it for the first time, I saw it for the second time)

Index

2.1 Advantages and disadvantages of database indexes and when are database indexes invalidated?

Features of the index

(1) Can speed up the retrieval speed of the database;
(2) can only be created on the table, cannot be created on the view;
(3) can be directly created and can be indirectly created;
(4) The index can be used in the optimization hiding;
(5) Execute SQL statements using the query processor, which can use only one index at a time on a table.

Benefits of indexing

(1) Create a unique index to ensure the uniqueness of each row of data in the database table;
(2) Greatly accelerate the retrieval speed of data, which is the main reason for creating indexes;
(3) Accelerating links between database tables, particularly in the implementation of database reference integrity;
(4) When using grouping and sorting clauses to retrieve, it can also significantly reduce the time of grouping and sorting in the query;
(5) by using the index, the optimization of the hidden device can be used in the query to improve the performance of the system;

Disadvantages of the Index

(1) It takes time to create indexes and maintain indexes, which increases with the number of times;
(2) The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain amount of physical space, if the aggregation index, then the space will be larger;
(3) When the data in the table is added, deleted and modified, the index also need maintenance, reduce the speed of data maintenance;

Index classification

(1) Normal index (it does not have any restrictions.) )
(2) Uniqueness Index (the value of the indexed column must be unique, but a null value is allowed.) )
(3) Primary key index (a special unique index that does not allow null values.) The primary key index is typically created at the same time as the table. )
(4) Combined index
(5) The clustered index constructs a B + tree according to the primary key of each table, and the leaf node holds the row record data for the entire table, so that the leaf node of the clustered index becomes the data page.
(6) Nonclustered index (secondary index) (the page node does not hold a whole row of records).

Index invalidation

(1) If there is or in the condition, even if there is a conditional index, it will not be used (as little as or);
(2) A like query is preceded by a%, for example select * FROM MyTable wheret Name "%admin";
(3) If the column type is a string, be sure to use quotation marks in the condition, otherwise the index will not be used;

Index of each engine support: (Core understand B-tree index)

Myisam,innodb,memonry comparison of three common MySQL engine types
Index MyISAM Index INNODB Index Memonry Index
B-tree support support for index support
Hash index does not support unsupported support
R-tree Index Support does not support unsupported
Full-text Index does not support temporarily unsupported unsupported

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

Index structure in the database?
Because in the use of two-fork tree, because the depth of the binary tree is too large to cause I/O read too frequently, resulting in inefficient query. Therefore, the use of multi-tree structure, B-tree operations can keep the B-tree low height.
B-Tree is also called balanced multi-Path search tree, a M-order B-Tree features as follows:
-1. Each node in the tree contains a maximum of M children (m>=2);
-2. Root nodes and leaf nodes, each of the other nodes has at least (Ceil (M/2)) Children (where ceil (x) is an upper-bound function);
-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 on the same layer, and the leaf nodes do not contain any keyword information (which can be seen as external nodes or failed query nodes, pointers to these nodes are null); (Note: Leaf nodes are just pointers to children without children, and these nodes also exist, there are elements, like red and black trees, Each null pointer is a leaf node, just not drawn.

B + Tree

Under what circumstances is it appropriate to build an index?
(1) for frequently appearing in the keyword ORDER BY, the group by, the field after the distinct, to establish the index;
(2) An index is established on the result set field of a set operation such as union, which is indexed as above;
(3) To create an index for a field that is often used as a query selection;
(4) The index is established on the properties that are used as link tables;
(5) Consider the use of index overlay, the data is rarely updated table, if the user often only query several of these fields, you can consider to establish the index on these fields, so that the table scan changes to the index of the scan.

MySQL syntax order

That is, when the following keywords exist in SQL, they are kept in this order:
SELECT[DISTINCT], from, join (such as Left join), on, where, group by, having, union, order by, limit;

MySQL execution order

That is, SQL executes at execution time in the following order:
From, on, join, where, group by, having, select, distinct, union, order by
Group BY IS to be used 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
Implementing Multi-table queries (inner joins)
Select u.uname,a.addr from Lm_user u inner joins lm_addr A on u.uid = A.uid;
Using the select from where can also be implemented
Select U.uname,a.addr from Lm_user u, lm_addr a where u.uid = A.uid;

Stored Procedures

Delimiter

Createprocedureprocedurebill () comment′ Check all sales conditions ′beginselectbillid,txtime,amtfromlmbill;end

delimiter;
Call a stored procedure
Call Procedure_bill ();
viewing stored procedures
Show procedure status like ' Procedure_bill ';

Establishing a many-to-many data table relationship in MySQL database

In the database, if the relationship between the two tables is a many-to-many relationship, such as: "Student table and Curriculum", a student can choose more than one course, a course can be selected by multiple students, according to the design principles of the database, a third association table should be formed.
Step 1: Create three datasheets student, Course,stu_cour

/** Student Table */create table Student (stu_id INT auto_increment,name VARCHAR (), age INT, Class VARCHAR (100), Address VARCHAR , PRIMARY KEY (stu_id))/* Student Curriculum */create TABLE Course (cour_id INT auto_increment,name VARCHAR (30), CODE VARCHAR, PRIMARY KEY (cour_id))/** Student Course Association Table */create Table Stu_cour (sc_id int auto_increment,stu_id int, cour_id int,primary KEY (sc_ ID))

Step Two: Add a foreign key for the Stu_cour association table

/* Add foreign KEY constraint */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);

Finish creating!

Note: Add a foreign key for a data table that has been added:
-Syntax: ALTER TABLE name add constraint fk_id foreign key (your foreign key field name) REFERENCES outer table name (the primary key field name for the corresponding table); 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 common databases? Did Redis ever use it?

Common relational databases:
Mysql, SQL Server, Oracle
Common modeless databases:
MongoDB, Merncached,redis ...
Redis
(1) Redis is a very fast, non-relational database that can store the mappings between key (key) and 5 different types of values (value), which can persist data stored in memory to the hard disk.
(2) compared to merncached
1) Both can be used to store key-value mappings, and the performance of each other is similar;
2) Redis can automatically write data to the hard disk in two different ways;
3) In addition to storing normal string keys, Redis can store other 4 kinds of data structures, merncached can only store string keys;
4) Redis can be used as a primary database or as a secondary database for other storage systems;

Database engine (Storage engine)

What is the engine?
When you access a database, whether it is manual access or program Access, is not directly read and write database files, but through the database engine to access the database files.
Take the relational database as an example, you send SQL statements to the database engine, the database engine interprets the SQL statements, extracts the data you need to return to you. Therefore, for the visitor, the database engine is the interpreter for the SQL statement.

The difference between MyISAM and InnoDB engines

Main differences:
-(1) MYISAM is a non-transactional security type, while InnoDB is a transaction-safe type;
-(2) The granularity of Nyisam lock is table-level lock, while InnoDB supports row-level lock;
-(3) MyISAM supports full-text indexing, while InnoDB does not support full index
-(4) MyISAM is relatively simple, so in terms of efficiency is better than INNODB, small applications can consider the use of MyISAM;
-(5) MyISAM table is saved as a file form, in the cross-platform data transfer in the use of MyISAM storage will save a lot of trouble;
-(6) The InnoDB table is more secure than the MyISAM table and can switch non-transactional tables to the transaction table in the event that the data is not lost;
Application Scenarios:
-(1) MyISAM manages non-transactional tables that provide high-speed storage and retrieval, as well as full-text search capabilities, and MyISAM is a better choice if you need to perform a large number of select queries in your application.
-(2) InnoDB is used for transaction processing applications with many features, including acid transaction support. If you need to perform a large number of insert or update operations in your app, you should use InnoDB, which can improve the performance of multiple user concurrency operations.

Other

What are the paradigms in the database

There are currently 6 paradigms for relational databases: the first paradigm {1NF}, the second paradigm {2NF}, the third paradigm {3NF}, the bass-cod paradigm {BCNF}, the fourth paradigm {4NF}, the V-paradigm {5NF, also known as the perfect paradigm}. The paradigm that satisfies the minimum requirements is the first paradigm. On the basis of the first paradigm to further meet the requirements of more specifications called the second paradigm {2NF}, the rest of the paradigm in turn, in general, the database only needs to meet the third normal form (3NF) ok.
Paradigm:

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

(2) 2NF: Ensure that each column in the table is associated with the primary key (the Federated primary key);

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

(4) BCNF: On the basis of 1NF, any non-primary attribute cannot be dependent on the primary key subset (eliminating the dependence on the main code subset on the 3NF basis);

(5) 4NF: the need to remove the many-to-many relationships within the same table;

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

