Execution process of database SQL statement

Time:2022-5-17

1.

 

Connector:

TCP protocol

Connecting and disconnecting

Verify authority, user name and password

Number of connections: show processlist; kill  show global variables like ‘wait _ timeout’;

Default sleep time: 8 hours

After the connection, the user information is cached, and the permissions of the database are changed. The connection takes effect only after re login

Create user: create user ‘XTC’ @ ‘localhost’ identified by ‘123456’;

Change user permissions: grant all privileges on ** to ‘xtc’@’localhost’;

Refresh permissions: flush privileges;

Change Password: alter user ‘XTC’ @ ‘%’ identified by ‘654321’;

Change access permissions: update user set host = ‘%’ where user =’xtc ‘;

Query table structure: describe user;

   

Query Cache:

View cache status: show global variables like “query_cache_type”;

Enable cache: / etc / MySQL / my CNF # add query_ cache_ Type = demand (can be hit only if sql_cache is added to the query) off off on by default # on # restart after change

View the number of hits and the amount of storage. Show status like “% qcache%”

        

 

  • Qcache_ free_ Blocks: indicates how many blocks remain in the query cache. If the value is large, it indicates that there are too many memory fragments in the query cache, which may be sorted out in a certain time.
  • Qcache_ free_ Memory: the memory size of the query cache. Through this parameter, you can clearly know whether the query memory of the current system is enough, whether it is too much or not. The DBA can adjust it according to the actual situation.
  • Qcache_ Hits: indicates the number of hits to the cache. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the cache effect.
  • Qcache_ Inserts: indicates the number of misses and then inserts, which means that the new SQL request is not found in the cache and has to execute query processing. After executing query processing, insert the result into the query cache. The more times this happens, the less the query cache is applied, and the effect is not ideal. Of course, after the system is started, the query cache is empty, which is normal.
  • Qcache_ lowmem_ Prunes: this parameter records how many queries are removed from the query cache due to insufficient memory. With this value, the user can adjust the cache size appropriately.
  • Qcache_ not_ Cached: indicates because of query_ cache_ The number of queries that are not cached due to the setting of type.
  • Qcache_ queries_ in_ Cache: the number of queries cached in the current cache.
  • Qcache_ total_ Blocks: number of blocks currently cached.

 

Lexical analyzer: parse SQL statements into a syntax tree,

Optimizer: selecting indexes

Executor: call engine interface

 

bin-log:

Server layer engine common

Binary file

Unlimited size, additional write, and the previous file will not be overwritten

Check whether it is enabled: show global variables like “% log_bin%”;

Open: / etc / MySQL / my CNF # add:

Log bin = / user / local / MySQL / data / binlog / MySQL bin. You need to give permission to this directory

Server id = 1. It needs to be added after 5.7. It is a unique ID, which is defined by itself

Binlog format = row #binlog format. There are three statements that only record the execution, but the efficiency is low. There may be inconsistency between the master and the slave. Row records the results of SQL execution, which is inefficient and safe. Mixed is a combination of the two

Sync binlog = 1 # means to synchronize with the hard disk every time you write, which will affect the performance. When it is 0, it means that MySQL will not brush the disk when the transaction is committed, which is determined by the system

The MySQL bin 00000 1 file will be generated in the specified directory

View binlog file:

  /usr/bin/mysqlbinlog –no-defaults  /usr/local/mysql/data/binlog/mysql-bin.000001

  

mysql> show variables like ‘%log_ bin%’; Check whether the bin log is enabled
mysql> flush logs; The latest bin log log will be added
mysql> show master status; View the information about the last bin log
mysql> reset master; Clear all bin log logs

 

Recover all data from bin log
/usr/bin/mysqlbinlog  –no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p superzig-operatingtable-1
Recover data at the specified location
/usr/bin/mysqlbinlog –no-defaults –start-position=”74311″ –stop-position=”74582″  /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p superzig-operatingtable-1
Recover data in specified time period
/usr/bin/mysqlbinlog –no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 –stop-date= “2018-03-02 12:00:00”  –start-date= “2019-03-02 11:55:00″|mysql -uroot -p superzig-operatingtable-1

 

Recommended Today

leetcode-JZ24

topic Define a function that takes as input the head node of a linked list, reverses the linked list and outputs the head node of the reversed linked list. Example: Input: 1-&gt;2-&gt;3-&gt;4-&gt;5-&gt;NULL Output: 5-&gt;4-&gt;3-&gt;2-&gt;1-&gt;NULL limit: 0 <= 节点个数 <= 5000 Original title link analyze first type of practice Traverse the linked list, store the variable, […]