What is the difference between oracle and mysql

Difference: 1. Oracle is a large database, while Mysql is a small and medium-sized database; 2. Mysql is open source, while Oracle is paid; 3. Mysql does not support transactions by default, while Oracle does Fully supported; 4. Oracle requires manual submission by default, and Mysql automatically submits by default.

##The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The difference between MySQL and Oracle

1. Macroscopically:

1. Oracle is Large databases, while Mysql is a small and medium-sized database; Mysql is open source, 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 for transactions It is not supported by default, but some storage engines such as innodb can support it; Oracle fully supports things.

2. Data persistence

Oracle guarantees that submitted transactions can be recovered, because Oracle writes the submitted sql operation line into the online online log file. Save it to disk. If the database or host restarts abnormally, restarting Oracle can restore the data submitted by the customer based 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.

3. Transaction isolation level

MySQL is the isolation level of repeatable read, while Oracle is the isolation level of read committed, and both support serializable serialized transactions. Isolation level, the highest level that can be achieved.

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, and other sessions cannot access the data

4. Submission method

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

5. Logical backup

Mysql logical backup requires locking data to ensure that the backed up data is consistent and affects normal business DML (Data Manipulation Language) Language) is used; Oracle does not lock data during logical backup, and the backed up data is consistent.

6. Flexibility of sql statements

Mysql has many very practical and convenient extensions to sql statements, such as the limit function (paging), insert can insert multiple items at one time Row data; Oracle feels more stable and traditional in this regard. Oracle's paging is completed through pseudo columns and subqueries, and data can only be inserted row by row.

7. 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.

8. Partitioned tables and partitioned indexes

MySQL’s partitioned table is not yet mature and stable; Oracle’s partitioned table and partitioned index functions are very mature and can improve user access. db experience.

9. 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 yourself.

10. Permissions and security

Oracle’s permissions and security concepts are relatively traditional and quite satisfactory; MySQL’s users are related to the host, which feels meaningless, and it is easier to be There are opportunities for counterfeiting hosts and IPs.

11. 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 video tutorial]

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