MySQL comprehensive knowledge learning

What are the main types of SQL statements

Data definition language DDL (Data Ddefinition Language): CREATE, DROP, ALTER are mainly for the above operations that have operations on logical structures, including table structures, views, and indexes.

The data query language DQL (Data Query Language) SELECT
is better understood, that is, the query operation, with the select keyword. Various simple queries, connection queries, etc. belong to DQL.

The data manipulation language DML (Data Manipulation Language) INSERT, UPDATE, and DELETE are mainly used for the above operations, that is, to operate on the data. Corresponding to the query operation mentioned above, DQL and DML jointly construct the addition, deletion, modification, and query operations commonly used by most junior programmers. The query is a more special kind that is divided into DQL.

The data control functions DCL (Data Control Language) GRANT, REVOKE, COMMIT, ROLLBACK are mainly for the above operations, namely the operation of the database security integrity, etc., which can be simply understood as permission control.

Six related queries

Cross join (CROSS JOIN)

Inner join (INNER JOIN)

Equivalent connection: ON A.id=B.id

Unequal value connection: ON A.id> B.id

Self-join: SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

Outer join (LEFT JOIN/RIGHT JOIN)

Left outer join: LEFT OUTER JOIN, the left table is the main one, the left table is queried first, the right table is matched according to the association condition after ON, and the ones that are not matched are filled with NULL, which can be abbreviated as LEFT JOIN

Right outer join: RIGHT OUTER JOIN, the right table is the main one, the right table is queried first, and the left table is matched according to the association conditions after ON. The ones that are not matched are filled with NULL, which can be abbreviated as RIGHT JOIN

Joint query (UNION and UNION ALL)

Is to gather multiple result sets together, the results before UNION as the benchmark, it should be noted that the number of columns in the joint query must be equal, and the same record rows will be merged

If you use UNION ALL, duplicate rows of records will not be merged

Efficiency UNION is higher than UNION ALL

FULL JOIN

MySQL does not support full connections

You can use LEFT JOIN and UNION and RIGHT JOIN in combination with SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id

• Cross join (CROSS JOIN)

The difference between int(10) and char(10) and varchar(10) in mysql

The 10 in int(10) indicates the length of the displayed data, not the size of the stored data; the 10 in chart(10) and varchar(10) indicates the size of the stored data, that is, how many characters are stored.

int(10) 10-bit data length 9999999999, occupying 32 bytes, int type 4 bits

char(10) 10-digit fixed character string, with insufficient space, up to 10 characters; varchar(10) 10-digit variable character string, insufficient space, with up to 10 characters

char(10) means to store 10 characters of fixed length. If there are less than 10 characters, fill up with spaces, occupying more storage space; varchar(10) means to store 10 characters of variable length, and store as many as there are spaces. It is also stored as a character, which is different from the space of char(10). The space of char(10) means that the placeholder is not a character

MySQL storage engine architecture

Official documents

Insert picture description here


According to the diagram, the logical architecture is divided from top to bottom:

  • Application layer, which mainly includes connection processing, authentication, and security management
  • Service layer
  • NoSQL Interface, CRUD operation
  • SQL Interface, DML, DDL, stored procedures, views, triggers
  • Parser parser, query things, object priority
  • Optimizer optimizer, query path
  • Caches & Buffers, cache, buffer
  • Storage engine, supports multiple storage engines
  • File system, data, index, log

MySQL master-slave replication principle

https://zhuanlan.zhihu.com/p/50597960

Insert picture description here

MySQL master-slave replication involves three threads

  • Primary node binary log dump thread

When the slave node connects to the master node, the master node will create a log dump thread to send the contents of the bin-log. When reading the operation in the bin-log, this thread will lock the bin-log on the master node. When the read is completed, the lock will be released even before it is sent to the slave node.

  • Slave node I/O thread

When the start slavecommand is executed on the slave node , the slave node will create an I/O thread to connect to the master node and request the updated bin-log in the master library. After the I/O thread receives the update sent by the binlog dump process of the master node, it saves it in the local relay-log.

  • Slave node SQL thread

The SQL thread is responsible for reading the content in the relay log, analyzing it into specific operations and executing them, and ultimately ensuring the consistency of the master-slave data.

How to delete millions of data or more

Regarding the index: Because the index requires additional maintenance costs, because the index file is a separate file, when we add, modify, or delete data, additional operations on the index file will occur, which require additional IO, Will reduce the execution efficiency of addition/modification/deletion. Therefore, when we delete millions of data in the database, query the official MySQL manual to find that the speed of deleting data is directly proportional to the number of indexes created.

So when we want to delete millions of data, we can delete the index first (it takes more than three minutes at this time)

Then delete the useless data (this process takes less than two minutes)

After the deletion is completed, the index is recreated (the data is less at this time). The index creation is also very fast, about ten minutes.

Compared with the previous direct deletion, it is definitely much faster, not to mention that in case the deletion is interrupted, all the deletions will be rolled back. That's even more a pit.