MySQL common sense as well as storage engines, locks and transactions

Common Database systems:

1 Oracleof Oracle

2,IBM 's DB2

3. Microsoft Access and SQL Server

4. Open Source PostgreSQL

5. Open Source MySQL

MySQL Database three phases :

Initial Open Source database phase

Sun MySQL Stage

Oracle MySQL Phase

OLTP: Online transaction processing for basic, routine transactions.

OLAP: Online analytical processing, the main application of Data Warehouse.

MySQL Branch version (some):

The upgraded version of the Mariadb:maria engine room MyISAM storage engine adds support for the hash join and optimization of the semi join, improving query performance for OLAP.

Drizzle: Database for cloud computing components and Web applications, high concurrency, efficient memory usage, open source.

Innosql:1, InnoDB Flash cache: The SSD as the Flash cache 2, InnoDB Share Memory: Share memory as the InnoDB of shared memories, in order to improve the database preheating. 3. IO Statistics: Extends the contents of MySQL's original slow log and can now record the logical read and physical read IO of an SQL statement

Storage engine (table type):

InnoDB Storage Engine: Supports transactions, rollbacks, primarily for OLTP, supports row lock design, supports foreign keys, and supports non-locking reads similar to Oracle. (MySQL 5.5.8 starts as the default storage engine) data is placed in a separate tablespace, using MVCC (multi-version concurrency control), 4 isolation levels, default repeatable level, using Netx-key locking to avoid phantom reads, insert buffering, two writes, Adaptive hash Index, pre-read, and so on. In a clustered manner, each table is stored in the order of the primary key. indexes and data are tightly bound, and no compression is used, resulting in innodb larger than MyISAM size.

Usage: You should choose InnoDB if you want to perform insert and update on most of the projects that are hosted.

Advantage: It provides good transaction management, crash, repair, and concurrency control.

disadvantage: Its reading and writing efficiency is slightly poor, occupy the data space is relatively large .

MyISAM Storage Engine: Does not support transaction, table lock design, support full-text indexing, mainly for OLAP, before InnoDB is the default storage engine, its buffer pool only cache index files, not cache data files. indexes and data are separate, can load more indexes, and the index is compressed, relative to memory efficiency is improved, he uses a table locking mechanism to optimize multiple concurrent read and write operations. The MyISAM emphasizes fast read operations.

Use occasions: most of the projects in the load are read and write less project platform, and MyISAM read performance is much stronger than InnoDB

Advantages: Small footprint, fast processing speed,

Disadvantages: the integrity and concurrency of transaction logs are not supported

NDB Storage Engine: The cluster storage engine, similar to Oracle's RAC cluster (share everything), is structured as a share nothing cluster. The data is all in memory, so the primary key looks very fast and can add NDB storage data nodes online, for OLTP. It seems that MySQL cluster enterprise application is not many, because the technology and stability are not too mature.

Memory storage Engine: Data all the house, the database restarts or crashes, the data in the table disappears, is suitable for storing OLTP application staging tables, or it can be used as a dimension table for the Data Warehouse applied by the OLAP database, by default using a hash index instead of a B + index.

Advantages: Fast Storage speed

Cons: Lack of stability and integrity

Infobright Storage Engine: third-party storage engine, stored in columns instead of rows, therefore suitable for OLAP database applications

Merge (Mrg_myisam) storage Engine: A set of MyISAM tables that must be structurally identical, using the merge table to transparently query and update multiple tables

Ntse Storage Engine: NetEase development for internal use, currently does not support compression, row-level caching and other features.

BDB: Another option for transactional tables originating from Berkeley DB

Archive: Ideal for storing large numbers of independent, historical data that are not often read, archive have an efficient insertion speed, but support for queries is relatively poor

CSV: A storage engine that logically divides data by commas

Blackhole: Black hole engine, any data written will disappear, generally used to record binlog, do replication relay.

Wait for the storage engine!

View the storage engine under MySQL:

(1) , Query Mysql -supported engines

Mysql->show engines;

Mysql->show Engines\g; +g and no G two different display modes.

(2) , Query Mysql support engine information

Mysql->show variables like ' have% '

(3) , Query Mysql default storage engine

Mysql-> Show variables like ' storage_engine ';

If you want to modify the storage engine, you can modify it in My.ini

default-storage-engine= Engine Type

To modify the storage engine for a table:

ALTER TABLE T1 engine = InnoDB;

Transaction:

Transaction (TRANSACTION):

A transaction is a user-defined sequence of operations that either does or does not, and is an inseparable unit of work

In MySQL, a transaction can be an SQL statement, a set of SQL statements, or an entire program

Only InnoDB, BDB storage engines support transactions

Show Engines\g

4 Characteristics of a transaction (ACID)

Note: Watch engine

Atomicity (atomicity):

atoms mean the smallest particles, or things that can't be divided, all the statements that make up a transaction are either all executed or all canceled

Consistency (consistency):

Refers to the data in the same transaction, should be consistent before and after

S1 read the data, S2 also access the same data, and modify it, S1 reread, the data obtained is not the same as just now, which violates the consistency

(S1 in the transaction)

Isolation (Isolation):

The operation of a transaction is not visible to the other session before commit

Persistence (Durability):

When the transaction is complete, its effect should be preserved and cannot be undone

How Transactions work

If DML is not in the transaction, S1 is changed, and S2 can see it immediately.

If DML is in a transaction, the DML before commit does not function to the table, but is recorded in the transaction log file

So no other session can see the results.

Commit, the DML action in the transaction log f file is logged to the table

When a commit or rollback is executed, the transaction ends.

Introduction to Locks:

A mechanism by which a computer coordinates concurrent access to a resource by multiple processes or threads

In addition to CPU/MEM/HD, data (or tables, a row of records) is also a resource for multiple users to share and compete with.

MySQL provides multi-user access to data concurrently, different DBMS provides concurrency control, and different development tools often provide commands to implement database concurrency control.

MySQL common three kinds of lock levels-table level lock, page lock, row level lock, there are two modes of table-level lock-table shared read lock and table exclusive write lock.

MyISAM:
Table-level locks. When the MyISAM table is read, it does not block other users from reading requests to the same table, but blocks the write operation to the same table >;
When writing to the MyISAM table, it blocks other users from reading and writing requests to the same table.

InnoDB :
A row lock is provided (locking on the row level), and the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table.

The advantages of row-level locks are as follows:
1) reduce the lock state when many connections make separate queries.
2) If an exception occurs, you can reduce the loss of data. Because you can roll back only one row or a few rows of data at a time.

The disadvantages of row-level locks are as follows:

1 ) consumes more memory than page-level and table-level locks.

2) There are more I/O required to query than page-level and table-level locks, so we often use row-level locks for write operations rather than read operations.

3) easy to appear deadlock.

Note: Nnodb cannot determine the line of operation, this time using the intent lock, that is, the table lock

Manual lock

Lock tables EMP Read|write;

unlock tables; or quit will release the lock

Flush tables with read lock; --All table read lock

Graphical SQL Query Analyzer:

MYSQL Workbench

Toad for Mysql

Imysql-front

Other appropriate tools: Phpmyadmin,navicat for MySQL,mysqlquerybrowser.

This article is from the "8430296" blog, please be sure to keep this source http://8440296.blog.51cto.com/8430296/1582129

MySQL common sense as well as storage engines, locks and transactions