Shulou(Shulou.com)06/01 Report--

This article mainly tells you the main differences between Mysql and Oracle. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope what are the main differences between Mysql and Oracle? this article can bring you some practical help.

MySQL commits the transaction by default, and there must be a START TRANSACTION that marks the beginning of a transaction, otherwise data cannot be rolled back with rollback.

Oracle needs to be submitted manually by the user, by writing the commit command or by clicking the commit button.

2. Paging query

MySQL can be queried directly using the following sql language, and paging can be achieved with limit.

Select... From... Where... Limit x,y

Of the two parameters followed by limit, x represents the starting subscript, starting at 0, and y represents the number of records returned.

But oracle requires pseudo-column ROWNUM and nested queries. ROWNUM represents the line number of a record, and it is important to note that it is not assigned until the row is obtained. Therefore, it is impossible to get paged data by specifying a ROWNUM interval in one-tier query language, and another query is needed.

3. Transaction isolation level

The default isolation level for MySQL is read commited,oracle, and the default isolation level is repeatable. At the same time, both support serializable serialization transaction isolation level to achieve the highest level of read consistency.

4. Support for transactions

MySQL supports transactions only in the case of row-level locks of the innodb storage engine, but oracle fully supports transactions.

5. Save data persistence

MySQL will lose data when the database is updated or restarted. Oracle writes the submitted sql operation line into the online log file and saves it to disk, which 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 make it impossible for other session to change 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 have an index.

Oracle uses row-level locks, locking resources with much smaller granularity, locking only the resources needed, and locking on rows of data in the database without relying on the index. So Oracle supports concurrency much better.

7. Logical backup

Data must be locked during MySQL logical backup to ensure that the backed up data is consistent, which affects the normal use of dml. Oracle logical backup does not lock data, and the backed up data is consistent.

8. Copy

It is easy for MySQL to replicate the configuration of the CVM, but when there is a problem with the master database, the cluster database may lose some data, and you need to manually switch from the cluster database.

Oracle not only has push or pull traditional data replication, but also has dataguard dual-computer or multi-machine disaster recovery mechanism. When there is a problem in the main database, it can automatically switch the standby database to the main database, but the data management is more complex.

9. Performance judgment

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 a lot of automatic analysis.

10. Permissions and security

MySQL users are related to the host and are easy to impersonate the host and ip.

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

11. Partition table and partition index

The partition table of MySQL is not 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 has few administrative tools, and the installation of administrative tools under linux sometimes requires the installation of additional packages, which is more complex.

Oracle has many mature command line, graphical interface, web management tools, as well as many third-party management tools, which are more efficient and convenient.

Finally: MySQL is a lightweight database and is free, with no service to recover data.

Oracle is a weight database, for a fee, Oracle has any service for Oracle database.

What are the main differences between Mysql and Oracle to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.