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

This article mainly talks about "what are the differences between mysql and oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what's the difference between mysql and oracle?"

There are differences, respectively: 1, MySQL automatically commit transactions, Oracle manual commit; 2, isolation level, MySQL is "read commited", Oracle is "repeatable read"; 3, MySQL is lightweight and free, while Oracle is heavy and charged.

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

The difference between mysql and oracle

(1) commit of transaction

MySQL defaults to automatic submission, while Oracle does not automatically submit by default. You need to submit it manually. You need to write the commit; instruction or click the commit button.

(2) pagination query

MySQL writes "select... from... where...limit x, y" directly in the SQL statement. Paging can be achieved with limit.

Oracle requires pseudo-column ROWNUM and nested queries.

(3) transaction isolation level

MySQL is the isolation level of read commited, while Oracle is the isolation level of repeatable read, 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 tablespace, each session

When querying, if the corresponding data block changes, 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) support for transactions

MySQL supports transactions only in the case of row-level locks of innodb storage engine, while Oracle fully supports transactions

(5) persistence of data preservation

When MySQL is updated or restarted in the database, the data will be lost. Oracle writes the submitted sql operation line to the online log file, which is kept on disk and can be recovered at any time.

(6) concurrency

MySQL is mainly table-level locking, and the granularity of resource locking is very large. If a session locks a table for too long, it will prevent other session from updating the data in this table.

Although InnoDB engine tables can use row-level locks, this row-level locking mechanism depends on the index of the table, and table-level locks are still used if the table does not have an index, or if the sql statement does not use an index.

Oracle uses row-level locks, locking resources with much smaller granularity, only locking resources needed by sql, and locking on data rows in the database, independent of and index. So Oracle's support for concurrency is much better.

(7) logical backup

MySQL logical backup should lock the data to ensure that the backed up data is consistent, affecting the normal use of dml, Oracle logical backup does not lock the data, and the backup data is consistent

(8) copy

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.

(9) performance diagnosis

There are few diagnostic and tuning methods for MySQL, mainly slow query logs.

Oracle has a variety of mature performance diagnosis and tuning tools, which can achieve many automatic analysis and diagnosis functions. Such as awr, addm, sqltrace, tkproof, etc.

(10) permissions and security

MySQL users are related to the host and feel meaningless, and they are more likely to be imitated by the host and ip to take advantage of it.

The concept of permissions and security in Oracle is more traditional and well-regulated.

(11) Partition tables and partition indexes

MySQL's zoning 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.

(12) Management tools

MySQL management tools are few, and the installation of management tools under linux sometimes requires the installation of additional packages (phpmyadmin, etc), which is somewhat complicated.

Oracle has a variety of mature command line, graphical interface, web management tools, as well as many third-party management tools, management is extremely convenient and efficient.

(13) the most important difference

MySQL is a lightweight database and is free, and there is no service to recover data.

Oracle is a heavy database and charges a fee. Oracle has any service for Oracle database.

At this point, I believe you have a deeper understanding of "what's the difference between mysql and oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.