What is the difference between MySQL and Oracle?

This article will introduce to you the difference between MySQL and Oracle. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

##1. Macroscopically:

1. Oracle is a large database and Mysql is a small and medium-sized database; Mysql is open source and Oracle is paid. And expensive.

2. Oracle supports large concurrency and large access volume, and is the best tool for OLTP.

3. The memory occupied by the installation is also different. After the Mysql installation is completed, the memory occupied is much smaller than the memory occupied by Oracle, and the more Oracle is used, the more memory it will occupy.

2. Microscopic view:

1. Support for transactions

Mysql does not support transactions by default, but some storage engines such as innodb can support it. ; And Oracle fully supports things.

2. Concurrency

What is concurrency? Concurrency is the most important feature of the OLTP (

On-Line Transaction Processing Online Transaction Processing) database. Concurrency involves the acquisition, sharing and locking of resources.

Mysql supports both table locks and row-level locks. Table locks are very powerful in locking resources. If a session locks a table for too long, other sessions will be unable to update the data of this table.

Oracle uses row-level locking, which locks resources much less strongly. It only locks the resources required by SQL, and the locking is on the data rows in the database and does not depend on the index. Therefore, Oracle's support for concurrency is much better.

3. Data persistence

Oracle guarantees that submitted transactions can be recovered, because Oracle writes the submitted sql operation line into the online online log file and saves it to the disk. If If the database or host restarts abnormally, restarting Oracle can restore the data submitted by the customer by relying on the online log.

Mysql submits sql statements by default, but if there is a db or host restart problem during the update process, data may also be lost.

4. Transaction isolation level

MySQL is the isolation level of repeatable read, while Oracle is the isolation level of read committed. At the same time, both support serializable serialized transaction isolation level, which can achieve the highest level.

Read consistency. Only after each session is submitted can other sessions see the submitted changes. Oracle achieves read consistency by constructing multi-version data blocks in the undo table space. When each session is queried, if the corresponding data block changes, Oracle will construct the old data for this session in the undo table space when it is queried. piece.

MySQL does not have a mechanism to construct multi-version data blocks similar to Oracle, and only supports the isolation level of read committed. When one session reads data, other sessions cannot change the data, but they can insert data at the end of the table. When a session updates data, an exclusive lock must be added so that other sessions cannot access the data

5. Submission method

Oracle does not automatically submit by default and needs to be submitted manually. Mysql automatically commits by default.

6. Logical backup

Mysql logical backup requires locking data to ensure that the backed up data is consistent, which affects the normal use of DML (Data Manipulation Language) in the business; Oracle logic Data is not locked during backup, and the backed up data is consistent.

7. Flexibility of sql statements

Mysql has many very practical and convenient extensions to sql statements, such as limit function (paging), insert can insert multiple rows of data at a time; Oracle is here It feels more stable and traditional. Oracle's paging is done through pseudo columns and subqueries, and data can only be inserted row by row.

8. Data replication

MySQL: The replication server configuration is simple, but when there is a problem with the main database, the cluster database may lose a certain amount of data. And you need to manually switch the plex library to the main library.

Oracle: There are both push or pull traditional data replication and dataguard’s dual-machine or multi-machine disaster recovery mechanism. If there is a problem with the main database, the standby database can be automatically switched to the main database, but the configuration management is complicated. complex.

9. Partitioned table and partitioned index

MySQL's partitioned table is not yet mature and stable; Oracle's partitioned table and partitioned index functions are very mature and can improve the user's experience of accessing the DB.

10. After-sales and fees

Oracle charges a fee. If you have any problems, please contact customer service; Mysql is free and open source. If you have problems, you can solve them by yourself.

11. Permissions and security

Oracle's permissions and security concepts are relatively traditional and quite satisfactory; MySQL users are related to the host, which feels meaningless. In addition, the host and IP are more likely to be counterfeited. Take advantage of the opportunity.

12. Performance diagnosis

Oracle has various mature performance diagnosis and tuning tools, which can realize many automatic analysis and diagnosis functions. For example, awr, addm, sqltrace, tkproof, etc.; MySQL has few diagnostic and tuning methods, mainly slow query logs.

Related recommendations: "

mysql tutorial"

The above is the detailed content of What is the difference between MySQL and Oracle?. For more information, please follow other related articles on the PHP Chinese website!