The difference between oracle and mysql
Alibaba Cloud Simple Application Server: Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.
1. Concurrency
Concurrency is the most important feature of a database, but concurrency involves resource acquisition, sharing, and locking.
MySQL: MySQL mainly uses table-level locks, and the granularity of resource locking is very large. If a session locks a table for too long, it will prevent other sessions from updating the data in this table. Although the InnoDB engine table can use row-level locks, the mechanism of this row-level lock depends on the index of the table. If the table does not have an index, or the SQL statement does not use the index, then the table-level lock is still used.
Oracle: Oracle uses row-level locking, and the granularity of resource locking is much smaller, but it only locks the resources required by sql, and the locking is on the data rows in
the database, which does not depend on and index. So Oracle's support for concurrency is much better.
2. consistency
Oracle: oracle supports serializable isolation level, which can achieve the highest level of read consistency. After each session is submitted, other sessions can see the submitted changes. Oracle achieves read consistency by constructing multi-version data blocks in the undo tablespace. During each session query, if the corresponding data block changes, Oracle will construct the old data for the session in the undo tablespace for this session. Piece.
MySQL: MySQL does not have a mechanism similar to Oracle to construct multi-version data blocks, and only supports the isolation level of read commited. When a session reads data, other sessions cannot change the data, but you can insert data at the end of the table. When the session updates data, an exclusive lock is added, and other sessions cannot access the data.
3. Affairs
Oracle has fully supported transactions very early.
MySQL only supports transactions in the case of row-level locks of the innodb storage engine.
4. data persistence
oracle: Ensure that the submitted data can be restored, because oracle writes the submitted sql operation line to the online log file and keeps it on the disk. If an abnormal restart of the database or host occurs, oracle can test the online online log to recover customers after restart Submitted data.
MySQL: By default, SQL statements are submitted, but if there is a problem with the db or host restart during the update process, data may be lost.
5. Submission method
Oracle does not automatically submit by default, users need to manually submit.
Mysql default is automatically submitted.
6, logical backup
Data is not locked during Oracle logical backup, and the data backed up is consistent.
Data must be locked during mysql logical backup to ensure that the backed up data is consistent, which affects the normal use of business dml.
7, hot backup
Oracle has a mature hot standby tool rman. When hot standby, it does not affect the user's use of the database. Even if the backup database is inconsistent, you can use the archive log and online redo log to restore a consistent response during recovery.
Mysql: Myisam's engine, when using the mysqlhostcopy hot standby that comes with mysql, you need to add a read lock to the table, which affects the dml operation. Innodb's engine, it will back up InnoDB's tables and indexes, but will not back up .frm files. When backing up with ibbackup, there will be a log file to record the data changes during the backup, so it is not necessary to lock the table, and it will not affect other users to use the database. But this tool is charged. innobackup is a script used in conjunction with ibbackup, he will assist in the backup of .frm files.
8, the expansion and flexibility of SQL statements
MySQL has many very practical and convenient extensions to the SQL statement, such as the limit function, insert can insert multiple rows of data at a time, select some management data without adding from.
Oracle feels more traditional in this respect.
9. copy
Oracle: There are both push or pull traditional data replication and dataguard's dual-machine or multi-machine disaster recovery mechanism. The problem with the main database is that the standby database can be automatically switched to the main database, but the configuration management is more complicated.
MySQL: The configuration of the replication
server is simple, but when there is a problem with the main database, the cluster database may lose certain data. And need to manually switch the cluster library to the main library.
10. performance diagnosis
Oracle has various mature performance diagnosis tuning tools, which can realize many automatic analysis and diagnosis functions. Such as awr, addm, sqltrace, tkproof, etc.
There are few diagnostic and tuning methods for mysql, mainly slow query logs.
11. Authority and Security
The users of mysql are related to the host, and it feels meaningless. In addition, they are more likely to be counterfeit by the host and ip.
Oracle's authority and security concepts are more traditional and quite satisfactory.
12. partition table and partition index
Oracle's partition table and partition index functions are very mature, which can improve the user's experience of accessing db.
The mysql partition table is not yet mature and stable.
13. Management Tools
Oracle has a variety of mature command line, graphical interface, web management tools, and many third-party management tools, management is extremely convenient and efficient.
There are few mysql management tools, and the installation of management tools under linux sometimes requires installation of additional packages (phpmyadmin, etc), which has certain complexity.
