Looking back at DBMS

# Note: the easy-to-understand concept is not commented out :) # logical architecture: user requests [through the connector] connection pool [allocate thread connections, connection queues] query analysis engine [operation solver, syntax semantic lexical analyzer, optimizer, and scheduler executor

# Note: the easy-to-understand concept is not commented out :) # logical architecture: user requests [through the connector] connection pool [allocate thread connections, connection queues] query analysis engine [operation solver, syntax semantic lexical analyzer, optimizer, and scheduler executor

# Note: the easy-to-understand concept is not annotated:) # logic architecture: user requests [through a connector] connection pool [allocating thread connections, connection queue] query and analysis engine [operator solver, syntax semantic lexical analyzer, optimizer, scheduler executor] manager [Transaction Manager, lock manager, File Cache disk space manager, recovery manager] storage engine interface [file system]. transaction: ACID for transactions A atomicity the execution of each transaction is A separate C consistency the consistency of the execution of each transaction completed I isolation each atomic transaction will not affect other transactions d. Persistent the results of each submitted atomic transaction are permanently stored in the database. isolation level: ansi 99 standard (also related to transactions) read uncommitted can READ (Others) UNCOMMITTED data may appear (dirty READ) read committed read-only to (Others) the committed data may appear (phantom read) The REPEATABLE-READ must be One execution has no concept of parallelism # phantom read: The two operate on the same piece of data, but the submission has a sequence. In this time difference, there are third parties who have done the operation during more advanced isolation level (MS-SQL) snapshot committer snapshot level commit snapshow snapshot read. change isolation level profile (MYSQL) [mysqld] transaction-isolation = REPEATABLE-READ environment variable SELECT @ GLOBAL. tx_isolation, @ tx_isolation; set global tx_isolation = 'repeatable-read'; set session tx_isolation = 'serializable ';. for multiple transaction log groups, partition files are used to convert random disk I/O into sequential I/O as much as possible. three major paradigms: (simple, for example) 1. cannot be divided (each key) 2. each table must have a primary key reference (sid, cid) 3. master Key to initiate the transfer reference cid (FK) -- cid (PK) Example: StudentClasssid (PK) cid (PK) snamecnamecid (FK ). basic Service Query: (MYSQL) show global variables like '% cache %' \ G; show session variables like '% cache % \ G'; show global/session status; example: SQL _mode SQL mode, tranitional traditional mode strict_trans_tables strict transaction mode strict_all_tables strict mode query for tables: select @ global. SQL _mode; equivalent to show global variables like '% cache %' \ G; setting: set global SQL _mode = 'strict _ trans_tables ';. Mysql usage: local communication/tmp/mysql. sock network communication socket Client tool mysql, mysqladmin, mysqldump import SQL Script 1. bash> mysql <xxx. SQL 2. mysql> /. xxx. SQL help document help context | help keyword. database lock: Read shared lock writes exclusive lock, exclusive lock granularity {table, row} # Oracle can adjust granularity to column, database Level LOCK TABLEStbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type]... lock_type: READ [LOCAL] | [LOW_PRIORITY] writeunlock tables example: Add a read lock table student READ; # other users cannot write Student table unlock unclock student. TRANSACTION: # The official article is very clear about o START transaction or BEGIN start a new TRANSACTION. o COMMIT commits the current transaction, making its changes permanent. o ROLLBACK rolls back the current transaction, canceling its changes. o SET autocommit disables or enables the default autocommit mode forthe current session. example: start transaction; SELECT @ A: = SUM (salary) FROM table1 WHERE type = 1; savepoi Nt a // Save the vertex UPDATE table2 SET summary = @ a where type = 1; COMMIT or rollback a // rollback only rolls back to a distributed transaction (isolation level requires SERAILIZABLE) classification: local transaction + external transaction (mutex) one or more Resource Manager (RM) and one Transaction Manager (TM) are required to work together in two phases: Prepare for processing (rollback/commit) execute the statement ---> xa start 'xa _ test'; ---> ACTIVEinsert into test (num) values (2 ); xa end 'xa '---> IDLExa prepare 'xa'; ---> PREPADERxa commit 'xa '| xa rollback 'xa recover (requires that all access to the PREPADER can be monitored ). user Management GRANTpri V_type [(column_list)] [, priv_type [(column_list)]... ON [object_type] priv_levelTO user_specification [, user_specification]... [REQUIRE {NONE | ssl_option [[AND] ssl_option]...}] [WITH with_option...] object_type: TABLE | FUNCTION | PROCEDUREpriv_level: * | *. * | db_name. * | db_name.tbl_namessl_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subobject' with _ option: grant option example: grant select, Update, delete, insert on *. * to user @ 'localhost' revoke select, update, delete, insert on *. * from user @ 'localhost '. cache: show global variables like '% query_cache %' query _ cache_type {on, off, demand} the SQL statement in demand must contain the minimum query_cache_limit value in the cached block. The maximum value of a single cache is query_cache_wcolck_invalidate. The read (in cache) hit rate cannot be returned: mariaDB [hellodb]> show global status where Variable_name = 'qcach E_hits 'OR Variable_name = 'com _ select '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Com_select | 24 | hour | 4 | + --------------- + ------- + hour/(Com_select + Qcache_hits) you should also refer to another indicator: the ratio of hit to write, that is, the value of Qcache_hits/Qcache_inserts. If the ratio is greater than, the cache is also effective. It can reach, which is an ideal situation .. Log category: Query timeout query Error log binary (ORACLE: redo log) relay log transaction log = {on | off} query log is enabled (must be enabled together) log_output = {TABLE | FILE | NONE} query log output location gereral_log = {on | off} query log enabled (must be enabled together) general_log_file =/path/loglong_query_time = time (second) timeout query slow_query_log = {ON | OFF} whether to enable the timeout query slow_query_log_file =/path/log. copy: from the initiate request I/O thread request the master receives the request using the binlog dump thread to respond to the request received from the I/O thread and saved as a relay log from the SQL thread the relay thread is saved as the execution log status: 1. the status of the master replication thread is listed below The most common State of the State column of the Binlog Dump thread. If you do not see any Binlog Dump thread on the master server, this indicates that the replication is not running-that is, there is no connection to any slave server. · The Sending binlog event to slave binary log is composed of various events. An event usually adds other information to an update. The thread has read an event from the binary log and sent it to the slave server. · Finished reading one binlog; the switching to next binlog thread has read the binary log file and is opening the next log file to be sent to the slave server. · Has sent all binlog to slave; waiting for binlog to be updated threads have read all major updates from binary logs and sent them to the slave server. The thread is currently idle, waiting for a new event in the binary log caused by a new update on the master server. · A very simple state that occurs when the Waiting to finalize termination thread is stopped. 2. The replication slave I/O thread State lists the most common states of the slave server's I/O thread State column. This STATUS also appears in the Slave_IO_State column, which is displayed by show slave status. This indicates that you can only use this statement to carefully view what happened. · The Connecting to master thread is trying to connect to the master server. · Checking master version is a temporary status that occurs immediately after a connection with the master server is established. · Registering slave on master is in a temporary status after a connection with the master server is established. · Requesting binlog dump is in a temporary status after a connection with the master server is established. The thread sends a request to the master server to obtain the binary log Content starting from the request's binary log file name and location. · Waiting to reconnect after a failed binlog dump request if the binary log dumping request fails (because there is no connection), the thread enters the sleep state, and then periodically tries to reconnect. You can use the -- master-connect-retry option to specify the interval between retries. · Reconnecting after a failed binlog dump request thread is trying to reconnect to the master server. · The Waiting for master to send event thread has been connected to the master server and is Waiting for the arrival of the binary log event. If the master server is idle, it will take a long time. Timeout occurs if the waiting duration is slave_read_timeout seconds. In this case, the thread considers the connection to be interrupted and attempts to reconnect. · The Queueing master event to the relay log thread has read an event and copied it to the relay log for processing by the SQL thread. · An error occurs when Waiting to reconnect after a failed master event read (because there is no connection. The thread will sleep for master-connect-retry seconds before attempting to reconnect. · Reconnecting after a failed master event read thread is trying to reconnect to the master server. After the connection is re-established, the status changes to Waiting for master to send event. · Waiting for the slave SQL thread to free enough relay log space is using a non-zero relay_log_space_limit value. the relay log has grown to exceed the combined size. The I/O thread is waiting until the SQL thread processes the relay log Content and deletes some relay log files to free up enough space. · Waiting for slave mutex on exit occurs when the thread stops. 3. Replication lists the most common statuses of the State columns of SQL threads on the slave server. · Reading event from the relay log thread has read an event from the relay log and can process the event. · Has read all relay log; waiting for the slave I/O thread to update it thread Has processed all events in the relay log file, it is waiting for the I/O thread to write new events to the relay log. · Waiting for slave mutex on exit occurs when the thread stops. The State column of the I/O thread can also display the statement text. This indicates that the thread has read an event from the relay log, extracted the statement from it, and is executing the statement. Replication Process: master server operation: 1. authorize the account that can only be copied to the slave grant replication slave on *. * TO 'repl' @ '% .mydomain.com' identified by 'slavepass'; 2. enable binary log (profile) [mysqld] log-bin = mysql-bin3. specify master server-idserver-id = 1 slave server operation: 1. specify the slave server-id (which cannot be the same as the master) server-id = 22. # enable relay logs (enabled by default) 3. connection master server change master tomaster_host = 'master-user of remote master ip address 'master-user = 'replication account of remote master' master-password = 'Password' master-log-file = 'remote master 'Master-log-position = where the bin-log file is located Dynamic log 4. start the slave server start slave ;. view: create view v_view WITH [CASCADED | LOCAL] check option. TRIGGER: no additional features create trigger trigger_name trigger_time trigger_eventON tbl_name for each row trigger_1_told and NEW keywords to update previous data trigger_eventinsert update deleteNEW xx-OLD-xx. stored Procedures and functions: create procedure sp_name ([proc_parameter [,...]) [characteristic...] routine_bodyCREATE FUNCTION sp_name ([func_parameter [,...] ]) RETURNS type [characteristic...] routine_body. index: there are some interesting things: oracle: Internal indexes (multiple btree channels, multi-level, common function indexes used for function query creation inversion as the name suggests) external index (Bitmap has a repeat data bitmap connection to extract duplicate data at one time (one step closer) i/o time slice for compressing btree for cpu time slice policy partition if there is a Data Partition index, the index organization table will appear ??? The same columns in multiple tables of the Cluster Index form the custom index type of the cluster domain index !!!! The virtual index is only available for testing, and there is no index data to be created. The full text is nothing to say that the Binary Index creates indexes for blob and other data.) mysql index: the storage engine function btreeInnoDB, and the left node of MyISAM is smaller than the right node, improve query efficiency rtreeMyISAMbtree is a two-dimensional structure, so rtree is more than three-dimensional hashMemory/Heap suitable for key-value storage structure fulltextMyISAM large text use case: create index part_of_name ON customer (name (10 ));

Plan: Next chapter updates mysql optimization, benchmarking, master-slave, master-master mode replication, backup and recovery

This article is from the "Apprentice" blog. Please keep this source