The difference between Oracle and MySQL

First, the main types of databases

Database types can be divided into: mesh database, relational database, tree database, object-oriented database. The most important things in business are relational databases, such as Oracle, DB2, Sybase, My SQL Server, Informax, Redis, MySQL, and so on.

The difference between Oracle and MySQL

Oralce is large data and MySQL is a small-to-medium relational database. Oralce accounted for 40% of the market and MySQL accounted for 20% of the market.

One, concurrency

Oracle supports high concurrency and traffic, and is the best tool for OLTP

Concurrency is the most important feature of an OLTP database, but concurrency involves the acquisition, sharing, and locking of resources.

MySQL installed after the 152M and Orcale accounted for more than 3G, in the use and operation of the time to seize the machine resource performance is particularly high.

MySQL: Table-level lock-based, the granularity of the resource lock is very large, if a session on a table lock time too long, the other session will not be able to update the data in the table. Although the InnoDB engine table can use row-level locks, the mechanism of this row-level lock relies on the index of the table, and table-level locks are still used if the table has no indexes, or if the SQL statement does not use an index.

Oracle: With row-level locks, the granularity of resource locking is much smaller, knowledge locks the resources required by SQL, and locks up the data rows in the database without relying on indexes, so Oracle supports concurrency much better.

Second, consistency

The oracle:oracle supports the Serializable isolation level, enabling the highest level of read and write consistency, before each session commits to see the committed changes. Oracle implements read-write consistency by constructing multiple databases in the Undo table space, and when each session query, if the corresponding data block changes, Oracle constructs the old block of data in the Undo table space for the session when it queries.

Mysql:mysql does not have an oracle-like mechanism for constructing multiple versions of data blocks, which supports the isolation level of read commietd. When a session reads data, other sessions cannot change the data, but you can insert data at the end of the table.

When the session updates the data, you add an exclusive lock, and the other session cannot access the data.

Iii. Business

Oracle: A transaction is a state in which a database is swapped from one state to another, unlike a file system, which is a data-specific use.

Its characteristics are mainly divided into four

Atomicity: statement-level atomicity, process-level atomicity, transactional-level atomicity.

Consistency: Consistent state, no two states in the same transaction

Isolation: Transaction keys are separated from each other (there may also be autonomous things)

Persistence: The transaction commits, then the state is used.

Distributed transactions: In Oralce, multiple databases are controlled in one transaction to ensure the integrity of the data in the database, mainly through Dblink.

Autonomous transaction: is a child transaction that is independent of the service, and its commit and rollback do not affect the operation of the primary transaction.

Autonomous transactions provide a new way of controlling transactions with PL/SQL, which can be used to:

1, top-level anonymous block

2. Local, independent or packaged functions and procedures

3. Method of Object type

4. Database triggers

MySQL: The implementation of a transaction is a data-based engine, and different storage engines do not support transactions in the same way. The storage engines that support transactions in MySQL are InnoDB and NDB. InnoDB is the default storage engine for MySQL, the default isolation interface is RR, and is closer to the isolation level of RR, with multiple versions of concurrency control to solve the problem of non-repetition, plus gap lock (concurrency control) to solve the Phantom reading problem, Therefore, the InnoDB RR isolation boundary actually achieves the serialization level effect, and retains the better concurrency performance.

The isolation of transactions is achieved through locks, while the atomicity, consistency, and persistence of transactions are implemented through the log of transactions. Transactions are redo and undo

Distributed transactions: There are many ways to implement distributed transactions, which can either adopt the native transaction support provided by INNODB or use Message Queuing to achieve the eventual consistency of distributed transactions.

Model divided into three blocks: application, Explorer, things Manager

The application defines the boundary of the transaction and specifies which transactions need to be done;

The resource Manager provides a way to access the transaction, and a database is an explorer;

The transaction manager coordinates each transaction in the global transaction.

The distributed transaction takes the form of a two-segment commit, starting with all the transaction nodes in the stage and telling the transaction manager ready. The second stage of the transaction manager tells each node whether it is a commit or a rollback. If there is a node failure, the global node is all rollback, thus guaranteeing the atomicity of the transaction.

Atomicity: The execution of a transaction is treated as an indivisible minimum unit, the operations inside the transaction are either executed successfully or all fail back, and no part of it can be executed.

Consistency: The execution of a transaction should not compromise the integrity of the data .

Isolation: Typically, the behavior between transactions should not affect each other, but in practice, the degree to which the transactions interact affects the isolation level.

Persistence: After a transaction commits, the committed transaction needs to be persisted to disk, and the submitted data should not be lost even if the system crashes.

Four isolation levels for transactions:

1.READ UNCOMMITTED (read not submitted). Under the isolation level of RU, transaction a changes the data, even if it is not committed, and is also visible to transaction B, which is called dirty reading. This is an isolation level with a low degree of isolation, which can cause many problems in practical use, and is generally not commonly used.

2.READ COMMITTED (Submit read). Under RC isolation level, there is no problem with dirty reads. Transaction a changes the data, the commit will be visible to transaction B, for example, when transaction B is opened to read Data 1, then transaction a opens, the data is changed to 2, commit, B read the data again, will read the latest data 2. Under the isolation level of RC, an issue with non-repeatable reads occurs. This isolation level is the default isolation level for many databases.

3.REPEATABLE Read (Repeatable read). There is no problem of non-repeatable reads under the isolation level of RR. Transaction a changes the data, and after it is committed, it is not visible to transactions that are opened before transaction a. For example, transaction B reads data 1 when it is opened, then transaction a opens, changes the data to 2, commits, and B reads the data again, still reading only 1. Under the isolation level of RR, a phantom read problem occurs. Phantom reading means that when a transaction reads a value in a range, another transaction inserts a new record within that range, and the previous transaction reads the value of the range again, reading the newly inserted data. MySQL default isolation level is RR, however MySQL's InnoDB engine gap lock successfully solved the problem of phantom reading.

4.SERIALIZABLE (Serializable). Serializable is the highest isolation level. This isolation level forces all things to be executed serially, and at this isolation level, every row of data that is read is locked, resulting in a large number of lock expropriation problems with the worst performance.

MySQL supports transactions in the case of row-level locks on InnoDB stored engines.

Iv. Persistence of data

Oracle

Ensure that the submitted data is recoverable because Oracle writes the submitted SQL action line to the log file online and remains on disk. In the event of a database or host exception restart, Oracle can recover the data submitted by the customer online logs after a restart.

Mysql

By default, SQL statements are committed, but data may be lost if a DB or host restart occurs during the update process.

V. Ways of submission

Oracle is not automatically committed by default and requires manual submission by the user.

MySQL is automatically committed by default.

VI. Logical Backup

Oracle logical backup does not lock data, and the backed up data is consistent.

MySQL logical backup to lock the data in order to ensure that the backup data is consistent, affecting the normal use of DML.

Seven, hot backup

Oracle has a proven hot backup tool, Rman, that does not affect the user's use of the database, even if the backup database is inconsistent, it can be restored by archiving the log, and the online redo system into a consistent recovery.

Mysql:myisam engine, with MYSLQ myslqhostcopy hot standby I, need to read the table lock, affecting the DML operation.

InnoDB's engine, which backs up innodb tables and indexes, but does not back up, when a frm file is backed up with Ibbackup, a log file records data changes during the backup, so you can use the database without locking the table, but this tool is chargeable.

Innobackup is a script used in conjunction with Ibbackup, and he will assist in the backup of the frm files

Viii. scalability and flexibility of SQL statements

MySQL has a lot of very useful and convenient extensions to SQL statements, such as the limit function, inserts can insert multiple rows of data at a time, select some data management can not add the From

Oracle feels more stable and traditional in this respect.

Nine, copy

Oracle: Both push or pull traditional data replication, there are Datagurad dual-machine or multi-machine disaster tolerant mechanism, the main library problem is that can automatically switch to the master library, but configuration management is called complex.

MySQL: Replication server configuration is simple, but the main library problem, from the library may lose some data, and need to manually switch to from the database

Ten, performance diagnosis

Oracle has a variety of sophisticated performance diagnostic tuning tools to enable many automated analysis, diagnostic functions such as AWR,ADDM, SQLTrace, tkproof, etc.

MYSLQ has fewer diagnostic tuning methods, mainly slow query logs.

Xi. Rights and security

MySQL users with the host, there is no meaning, and more easily by the copycat host and IP to have the opportunity.

Oracle's rights and security concepts are more traditional and more classic.

12. Partition Index of partition table

Oracle's partitioned tables and partitioned indexes are mature enough to improve the user's access to the DB experience.

MySQL's partitioned table is not yet mature and stable.

13. Management Tools

Oracle has many sophisticated command lines, graphical interfaces, web management tools, and many third-party management tools that are extremely easy and efficient to manage.

MySQL management tools are less, the installation of management tools under Linux sometimes to install additional packages, there is some complexity.

This article from "Lingyu Technology blog" blog, declined reprint!

The difference between Oracle and MySQL