Introduction to some differences between Mysql and Oracle

Preface: In the daily work of programmers, it is inevitable to deal with databases. There are many databases we know, such as: mysql, oracle, sql server, etc.

Today I will introduce to you Some simple differences between mysql and oracle. (Recommended: mysql video tutorial)

1. Transaction submission

MySQL commits transactions by default, and at the same time it must be <strong>START TRANSACTION</strong> Marks the beginning of a transaction, otherwise the data cannot be rolled back using rollback.

Oracle requires users to submit manually by writing the <strong>commit</strong> command or clicking the <strong>commit</strong> button.

2. Paging query

MySQL can directly use the following sql language to query, and paging can be achieved with limit.

select ... from ... where ... limit x,y

Among the two parameters after limit, x represents the starting subscript, starting from 0, and y represents the number of records returned.

But Oracle needs to use pseudo columns <strong>ROWNUM</strong> and nested queries. <strong>ROWNUM</strong> represents the row number of a record. It should be noted that it is assigned after acquiring a row. Therefore, it is not possible to obtain paging data by specifying the <strong>ROWNUM</strong> interval in a first-level query language, and another query is required.

3、Transaction isolation level

The default isolation level of MySQL is<strong>read </strong>commited, Oracle’s default isolation level is <strong>repeatable</strong>. At the same time, both support serializable serialized transaction isolation level to achieve the highest level of read consistency.

4. Support for transactions

MySQL can only support transactions with the row-level lock 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 online. The online log file is saved to disk and can be restored at any time.

6. Concurrency

MySQL mainly uses table-level locks, and the granularity of resource locking is very large. If a session locks a table for a long time If it is too long, other session will not be able to change the data in this table.

Although InnoDB engine tables can use row-level locks, the row-level lock mechanism depends on the index of the table. If the table does not have an index, or the SQL statement does not have an index, Then table-level locks are still used.

Oracle uses row-level locking, which locks resources at a much smaller granularity. It only locks the required resources, and the locking is on the data rows in the database and does not rely on indexes. So Oracle's support for concurrency is much better.

7. Logical backup

MySQL needs to lock the data during logical backup to ensure that the backed up data is consistent, which affects the normal businessdmlUse, Oracle logical backup does not lock data, and the backed up data is consistent.

8. Replication

MySQL replication server configuration is very simple, but when there is a problem with the main database, the cluster database may lose certain data and needs to be switched manually. From Cong library.

Oracle has both push or pull traditional data replication and <strong>dataguard</strong>'s dual-machine or multi-machine disaster recovery mechanism. When there is a problem with the main database , can automatically switch the standby database to the main database, but data management is more complicated.

9. Performance judgment

MySQL has few diagnostic and tuning methods, mainly slow query logs.

Oracle has a variety of mature performance diagnosis and tuning tools that can achieve many automatic analyses.

10. Permissions and Security

MySQL users are related to the host, and the host and IP can easily be counterfeited.

Oracle's permissions and security concepts are relatively traditional.

11. Partition table and partition index

MySQL’s partition table is not mature and stable.

Oracle's partition table and partition index functions are very mature and can improve the user's experience of accessing the db.

12. Management tools

MySQL has few management tools. At the same time, the installation of management tools under Linux sometimes requires the installation of additional packages, which is more complicated.

Oracle has many mature command lines, graphical interfaces, web management tools, and many third-party management tools, which are relatively efficient and convenient.

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

Oracle is a heavyweight database. There is a fee. Oracle Corporation has any services for the Oracle database.

The above is the detailed content of Introduction to some differences between Mysql and Oracle. For more information, please follow other related articles on the PHP Chinese website!