Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the differences between oracle and mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Differences: 1, Oracle is a large database, while Mysql is a small and medium-sized database; 2, Mysql is open source, Oracle is charged; 3, Mysql does not support transactions by default, while Oracle fully supports things; 4, Oracle defaults to manual commit, and Mysql defaults to automatic commit.

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

The difference between MySQL and Oracle

1. Macroscopically:

1. Oracle is a large database, while Mysql is a small and medium-sized database; Mysql is open source, Oracle is charged, and the price is expensive.

2. Oracle supports large concurrency and large traffic, which is the best tool for OLTP.

3, the memory occupied by installation is also different, the memory occupied by Mysql after installation is far less than that occupied by Oracle, and the more Oracle is used, the more memory will be used.

Second, at the micro level:

1. Support for transactions

Mysql does not support transactions by default, but there are some storage engines such as innodb that support it, while Oracle fully supports things.

2. Persistence of data

Oracle ensures that all committed transactions can be recovered, because Oracle writes the submitted sql operation line into the online log file and saves it to disk. If there is an abnormal restart of the database or host, restart Oracle can recover the data submitted by the customer through online log.

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

3. Transaction isolation level

MySQL is the isolation level of repeatable read, while Oracle is the isolation level of read commited, and both support serializable serialization transaction isolation level, which can achieve the highest level.

Read consistency. The committed changes are not seen by other session until each session commits. Oracle achieves read consistency by constructing multiple versions of data blocks in the undo table space. If the corresponding data block changes in each session query, Oracle will construct the old data block it queried for the session in the undo table space.

MySQL does not have a mechanism for constructing multi-version blocks similar to Oracle, and only supports the isolation level of read commited. When an session reads the data, other session cannot change the data, but can insert the data at the end of the table. When session updates data, an exclusive lock is added. Other session cannot access the data.

4. Submission method

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

5. Logical backup

Mysql logical backup is to lock the data to ensure that the backed up data is consistent, affecting the normal use of DML (data manipulation language Data Manipulation Language); Oracle logical backup does not lock the data, 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 limit function (paging), insert can insert multiple rows of data at a time; Oracle feels more stable and traditional in this respect, Oracle paging is done through pseudo-columns and subqueries, inserting data can only insert data row by row.

7. Data replication

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

Oracle: there are not only push or pull traditional data replication, but also dataguard dual-machine or multi-machine disaster recovery mechanism. The problem with the master database is that it can automatically switch from slave database to master database, but the configuration management is complicated.

8. Partition tables and partition indexes

MySQL's partition table is not very mature and stable; Oracle's partition table and partition index functions are very mature, which can improve the user's experience of accessing db.

9. After-sales and fees

Oracle is charged, if there is a problem, find customer service; Mysql is free, open source, and solve the problem on your own.

10. Permissions and security

The permissions and security concepts of Oracle are more traditional and well-regulated; MySQL users are related to hosts and feel meaningless, and they are more likely to be imitated hosts and ip to take advantage of.

11. Performance diagnosis

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

These are all the contents of this article entitled "what are the differences between oracle and mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.