Php database basics

Introduction to the eight open-source frameworks of Deep Learning: deep learning (DeepLearning) is a characterization learning method based on data in machine learning, the advantage of deep learning is that non-supervised or semi-supervised feature learning and efficient hierarchical feature extraction algorithms can replace manual feature acquisition (feature)

Transactions

Four Features of transactions (ACID ):

Atomicity, Consistency, Isolation, and Durability)
-(1) Atomicity
The operations to be performed in the entire transaction are either committed successfully or rolled back.
-(2) consistency
Ensure the consistency between data operations in the database and those after the operation. (For example, transfers between multiple accounts of a user, but the total amount of the user is unchanged)
-(3) isolation
Isolation requires a transaction to modify the data in the database, which is invisible to other transactions before being committed. (Serial execution is required between transactions)

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

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

2) committed read
Because the database is read/write splitting, the read lock is obtained when the transaction reads data, but is released immediately after reading the data. after the read lock is released, the data may be modified by other transactions, when reading the data again, we can see that the results of reading data before and after are different, resulting in non-repeated reading. (Read locks do not need to be released after the transaction is committed, but write locks need to be released after the transaction is committed)

3) Repeatable Read
All the data obtained by the select statement cannot be modified. This prevents inconsistent reads before and after a transaction. However, there is no way to control phantom reads, because at this time other transactions cannot change the selected data, but data can be added;

4) Serializable
All transactions are executed one by one to avoid phantom reads. for databases that implement concurrent control based on locks, serialization requires that range locks be obtained when performing range queries, if it is not a database that implements concurrency control based on locks, the transaction needs to be rolled back when a transaction that violates the serial operation is detected.

Conclusion: 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 when reading dirty reads when reading
Uncommitted read (read uncommitted) committed data may have been committed.
Committed read (read committed) committed cannot be committed. it is possible that committed has been committed.
The repeatable read operation cannot be performed.
Serializable Zookeeper cannot be serializable.

Summary: uncommitted reads may cause dirty reads-> committed reads to solve dirty reads, but will cause non-repeated reads-> repeatable reads to solve inconsistent reading results, but it causes phantom read (not previously, but now)-> serializable solves Phantom read, but adding many Range locks may cause lock timeout;

(4) once a transaction is committed, the changes it makes will be permanently saved to the database. at this time, even if the system crashes, the committed changes will not be lost.

Dirty read, non-repeated read, and Phantom read

(1) dirty read (for rollback operations): Transaction T1 updates the content of a row of records, but does not commit the modifications. transaction T2 reads the updated row, then T1 executes the rollback operation and cancels the modification. The number of rows read by T2 is invalid now (one transaction reads another transaction );

(2) non-repeated read (for modified operations): Transaction T1 reads a row of records, followed by T2 modifies the row of records that T1 just read, and then T1 reads this row again, the result is different from the Read result.

(3) Phantom read (update operations): Transaction T1 reads the result set returned by a specified where clause, and then inserts a new row of records in transaction T2, this row of records exactly meets the query conditions used by T1. Then T1 searches the table again, but the data inserted by T2 is displayed. (I did not see it for the first time, but I saw it for the second time)

Index

2.1 Advantages and disadvantages of database indexes and when database indexes fail?

Index features

(1) it can speed up database retrieval;
(2) you can only create a table, not a view;
(3) both direct and indirect creation;
(4) indexes can be used in optimization hiding;
(5) Use the query processor to execute SQL statements. only one index can be used at a time on a table.

Advantages of indexes

(1) create a unique index to ensure the uniqueness of each row of data in the database table;
(2) greatly speeding up data retrieval, which is the main reason for creating an index;
(3) acceleration of links between database tables, especially in achieving database reference integrity;
(4) when you use grouping and sorting clauses for search, you can also significantly reduce the time for grouping and sorting in queries;
(5) by using indexes, you can use the optimized hiding tool in the query to improve the system performance;

Index disadvantages

(1) It takes time to create and maintain indexes. this time increases with the increase of the number of indexes;
(2) indexes occupy physical space. in addition to data tables, each index occupies a certain amount of physical space. If clustered indexes are created, the required space is larger;
(3) when adding, deleting, and modifying table data, indexes also need to be maintained to reduce the data maintenance speed;

Index Category

(1) General Index (it has no restrictions .)
(2) unique index (the index column value must be unique, but null values are allowed .)
(3) primary key index (a special unique index, which does not allow null values. Generally, the primary key index is created when the table is created .)
(4) Composite Index
(5) the clustered index constructs a B + tree based on the primary key of each table, and the leaf node stores the row record data of the entire table, therefore, the leaf node of the clustered index becomes a data page.
(6) non-clustered index (secondary index) (page nodes do not store a whole row of records ).

Index failure

(1) If there is or in the condition, it will not be used even if there is a condition with an index (use less or as much as possible );
(2) Like queries start with %, for example, SELECT * FROM mytable WHEREt Name like '% admin ';
(3) If the column type is a string, it must be enclosed by quotation marks in the condition. Otherwise, the index will not be used;

Indexes supported by various engines: (core knowledge about B-Tree indexes)

Comparison of MyISAM, InnoDB, and Memonry common MySQL engine types
Index comment MyISAM index comment InnoDB index comment Memonry index
B-tree indexes support zookeeper, zookeeper, and zookeeper.
The Hash index zookeeper does not support zookeeper and does not support zookeeper.
The R-Tree indexes support zookeeper, which does not support zookeeper, zookeeper, and zookeeper.
Full-text indexing does not support zookeeper indexing. Currently, zookeeper indexing is not supported.

What is the index structure in the database? Under what circumstances is the index suitable?

What is the index structure in the database?
When using a binary tree, the depth of the binary tree is too large, resulting in too frequent I/O reads and writes, resulting in low query efficiency. Therefore, the multi-path tree structure is adopted, and various operations of tree B can keep tree B at a low height.
  B is also called a balanced multi-path search tree. the features of a m-level B-tree are as follows:
-1. each node in the tree can contain up to m children (m> = 2 );
-2. apart from the root and leaf nodes, each other node has at least (ceil (m/2) children (ceil (x) is a function to take the upper limit );
-3. there are at least two children (unless the B-tree contains only one node: the root node );
-4. all the leaf nodes appear on the same layer, and the leaf node does not contain any keyword information (it can be seen as an external node or a node that fails to query, and the pointer to these nodes is null). (note: leaf nodes only have no children and pointers to children. these nodes also exist and have elements. similar to the red and black trees, every null pointer is regarded as a leaf node, but it is not drawn)

Step 1: Create three data tables: Student, Course, Stu_Cour

/** Student TABLE */create table Student (stu_id INT AUTO_INCREMENT, name varchar (30), age INT, class VARCHAR (50), address VARCHAR (100 ), primary key (stu_id)/* student curriculum */create table Course (cour_id INT AUTO_INCREMENT, name varchar (50), code varchar (30), 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 2: Add a foreign key to the Stu_Cour join table

/* ADD a 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 );

Complete creation!

Note: add a foreign key for the added data table:
-Syntax: alter table name add constraint FK_ID foreign key (your foreign key field name) REFERENCES external table name (corresponding table 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 common databases? Have Redis been used?

Commonly used relational databases:
Mysql, SQLServer, Oracle
Common stateless databases:
MongoDB, Merncached, Redis ......
Redis
(1) Redis is a fast non-relational database that can store mappings between Keys and five different types of values, you can persistently store key-value pairs stored in the memory to the hard disk.
(2) compared with Merncached
1) both of them can be used to store key-value ing, and their performance is almost the same;
2) redis can automatically write data to the hard disk in two different ways;
3) in addition to common string keys, redis can also store four other data structures. merncached can only store string keys;
4) redis can be used as the primary database or as an auxiliary database for other storage systems;

Database Engine (storage engine)

What is an engine?
When you access a database, neither manual access nor program access directly reads or writes database files, but accesses database files through the database engine.
Zookeeper uses a relational database as an example. you send SQL statements to the database engine. the database engine interprets SQL statements and extracts the required data and returns it to you. Therefore, for visitors, the database engine is the interpreter of SQL statements.

Differences between MYISAM and InnoDB engines

Main differences:
-(1) MYISAM is non-transactional security, while InnoDB is transaction security;
-(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 more efficient than InnoDB. for small applications, consider using MYISAM;
-(5) MYISAM tables are saved as files. using MYISAM storage in cross-platform data transfer saves a lot of trouble;
-(6) The InnoDB table is safer than the MYISAM table and can switch non-transaction tables to the transaction table without data loss;
Application scenarios:
-(1) MYISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. if the application needs to execute a large number of select queries, MYISAM is a better choice.
-(2) InnoDB is used for transaction processing applications and has many features, including ACID transaction support. If the application requires a large number of insert or update operations, innodb should be used to improve the performance of multi-user concurrent operations.

Others

What are the paradigms in the database?

Currently, relational databases have six Paradigms: The first paradigm {1NF}, the second paradigm {2NF}, the third paradigm {3NF}, and the Bath-cod paradigm {BCNF }, the fourth paradigm {4NF} and the fifth paradigm {5NF, also known as the perfect paradigm }. The paradigm that meets the minimum requirements is the first paradigm. The second paradigm {2NF} is called to meet more normative requirements on the basis of the first paradigm, and the other paradigms are also called. Generally, the database only needs to satisfy the third paradigm (3NF.
Paradigm:

(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: make sure that each column in the table is directly related to the primary key (foreign key );

(4) BCNF: Based on 1NF, no non-master attribute can depend on the primary key subset (the dependency on the primary code subset is eliminated based on 3NF );

(5) 4NF: multiple-to-multiple relationships in the same table must be deleted;

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