One: plug-in storage engine
cThe terminal is connected to the s server through connectors.
Framework
SHOW ENGINES; View supported engine
SHOW table status where Engine=’InnoDB ’; Display table status information
SHOW table status\G View table status information
InnoDB Storage engine
Data are stored in table space with 2 storage formats.
1 Data and indexes of all tables of InnoDB type are stored in the same table space named ibdata1, but this approach does not support the advanced capabilities of InnoDB
2 In the configuration of asking price that kind of join
innodb_file_per_table=ON，It means that each table uses a separate tablespace file; (supports advanced functions such as single table import and export).
Restart: systemctl restart MySQL
Each table’s data file (data and index, stored in a database directory) is stored in its own dedicated table space file and in a database directory: tbl_name. IBD
The table created in this way is a separate table space in the corresponding library.
Table structure definition: in database directory, tbl_name.frm
For concurrent operations, it is based on snapshot format.
Using Aggregated Index: Store the index and data in one place, find the data directly through the index, and assist the index to point to the Aggregated Index
Support row lock:
Lock size: row level lock, gap lock
InnoDB Characteristics: Summary:
Data storage: table space;
Concurrency: MVCC, gap lock, row level lock;
Index: clustered index and auxiliary index;
Performance: Pre-read operation, memory data buffer, memory index cache, adaptive Hash index, insert operation cache;
Backup: support hot standby;
MyISAM：
Support full-text index (FULLTEXT index) but poor performance, compression, space function (GIS);
No support for transaction ()
Lock size: table lock
Scenarios: Scenarios that read only or read more or write less, smaller tables (to ensure a shorter recovery time after crash);
File: each table has three files stored in the database directory.
tbl_name.frm：Table format definition;
tbl_name.MYD：Data file;
tbl_name.MYI：Index file;
Characteristic:
Lock and Concurrency: table lock;
Repair: manual or automatic repair, but may lose data.
Index: non clustered index;
Delayed index update;
Table compression;
Other storage engines:
CSV：The CSV file (a text file separated by a comma separated field) is used as the MySQL table file.
MRG_MYISAM：Merging multiple MyISAM tables into virtual tables;
BLACKHOLE：Similar to /dev/null, does not really store data;
MEMORY：Memory storage engine, support hash index, table lock, often used in temporary table;
FEDERATED: A storage engine interface for accessing tables on other remote MySQL servers;
Search Engines:
lucene, sphinx
lucene：Solr, ElasticSearch
SHOW ENGINE Innondb status View engine status
Two things:
Concurrency control: a locking mechanism for concurrency control
Lock type:
Read lock: shared lock, which can be shared by multiple read operations.
Write lock: exclusive lock, exclusive lock.
Lock granularity:
Table lock: locks are applied at the table level, and the concurrency is low; every time you read and write, you need to lock the whole table.
Row lock: the lock is applied at the row level, and the concurrency is high; the cost of maintaining the lock state is large.
Locking strategy: seeking a balance mechanism between lock granularity and data security.
Storage engine: level and when to apply or release locks are determined by the storage engine itself.
MySQL Server：Table level can be decided by itself, explicit request is allowed, explicit lock.
Lock category:
Explicit lock: a lock that the user requests manually.
Implicit lock: the lock stored by the engine itself according to needs.
The use of explicit locks:
(1) LOCK TABLES Lock table
LOCK TABLES tbl_name read|write, tbl_name read|write, …
UNLOCK TABLES Unlock
as
use mydb
LOAK TABLE tbl2 read ; Read lock is another thread that can read and write.
(2) FLUSH TABLES Brush all the tables (write the data in the table to disk and close the table) to lock the entire library.
FLUSH TABLES tbl_name,… [WITH READ LOCK];
UNLOCK TABLES; Unlock
FLUSH TABLES with read lock； Data is written to all disks in the cache, and all tables in the library are read locked.
Things: A set of atomic SQL queries, or a separate unit of work consisting of one or more SQL statements;
See if supporting a thing needs to be full of ACID testing.
ACIDTest:
A：AUTOMICITY，Atomicity; all operations in the whole transaction are either successfully executed or rolled back after all failures; this is based on the event log
C：CONSISTENCY，Consistency; the database should always be changed from one consistent state to another.
I：ISOLATION，Isolation; whether operations made by a transaction are visible to other transactions before they are submitted; there are multiple levels of isolation for the purpose of guaranteeing concurrent operations; isolation between things
D：DURABILITY，Persistence; once a transaction is submitted, the changes made will be permanently preserved.
Thing Logging: A continuous space on disk: By default, the operation itself is logged, synchronized from the log to the data, so that it can be rolled back according to the logging steps
The operation steps are recorded in the log, which can support rollback, and things log is redundant.
SHOW GLOBAL VARIABLES LIKE ‘innodb%log%’； View things log
Turn off automatic submission:
1 SHOW SESSION VARIABLES LIKE ‘%commit%’;
2SELECT @@ session .autocommit; See if automatic submission is enabled.
3SET @@session.autocommit=0; Shut down automatically
Manual control transactions:
Startup: START TRANSACTION
SAVEPOINT first ; Do save points for later rollback to bring this point.
Submission: COMMIT
Rollback: ROLLBACK before it is submitted
ROKKBACK to first ; Roll to the first save point.
Transaction isolation level:
1 READ-UNCOMMITTED：Read uncommitted –> dirty reading; isolation level is too low; others do not submit data to be seen.
2 READ-COMMITTED：Read submit –> do not read repeatedly; do not read before submission.
3 REPEATABLE-READ：Repeatable read – & gt; phantom read; in the other party before the submission of the data can not be seen after the submission of the other party, as long as they did not make any changes, but their own rollback to see
4 SERIALIZABLE：Serialization; when an operation is blocked before committing or rolling back, it can be operated only when the parties are over
For example, SELECT @@session.tx_isolation; view.
SET @@sessio.tx_isolation=’READ-UNCOMMITTED’ Modification level
