The difference between MySQL and Oracle

One, concurrency

Concurrency is the most important feature of an OLTP database, but concurrency involves the acquisition, sharing, and locking of resources.

Mysql:
MySQL is a table-level lock-based, the granularity of the resource lock is very large, if a session on a table lock time too long, the other session will be unable to update the data in this table.
Although the InnoDB engine table can use row-level locks, the mechanism of this row-level lock relies on the index of the table, and table-level locks are still used if the table has no indexes, or if the SQL statement does not use an index.
If you want to learn about big data, you want to learn big data and need free learning materials to add groups: 784789432. You are welcome to join. Daily three o'clock in the afternoon to share the basic knowledge of the live, 20:00 in the evening will be broadcast live to share Big Data project combat.

Oracle
Oracle uses row-level locks, which are much smaller in the granularity of resource locking, only the resources required for locking SQL, and the locks are on the data rows in the database, and are not dependent on the index. So Oracle's support for concurrency is much better.

Second, consistency
Oracle
Oracle supports serializable isolation levels for the highest level of read consistency. Each session is submitted after the other session to see the submitted changes. Oracle implements read consistency by constructing multiple versions of data blocks in the undo table space.
For each session query, if the corresponding data block changes, Oracle constructs the old block of data in the Undo table space for the session when it queries.

Mysql:
MySQL does not have a mechanism similar to Oracle's construction of multi-version data blocks, only the isolation level of read commited is supported. 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 the data, you add an exclusive lock, and the other session cannot access the data.

Iii. Business
Oracle fully supports transactions very early.

MySQL supports transactions in the case of a row-level lock on the InnoDB storage engine.

Iv. Data Persistence
Oracle
Ensure that the submitted data is recoverable because Oracle writes the submitted SQL action line to the online log file and remains on disk.
In the event of a database or host exception restart, Oracle can recover the data submitted by the customer online logs after a restart.
Mysql:
The SQL statement is submitted by default, but may lose data if there is a problem with DB or host restart during the update process.

V. Ways of submission
Oracle is not automatically committed by default and requires manual submission by the user.
MySQL is automatically committed by default.

VI. Logical Backup

Oracle logical backup does not lock data, and the backed up data is consistent.

MySQL logical backup to lock the data in order to ensure that the backup data is consistent, affecting the business normal DML use.

Seven, hot backup
Oracle has a proven hot standby tool, Rman, that does not affect the user's use of the database while sparing. Even if the databases that are backed up are inconsistent, you can respond to them in a consistent response from the archive log and online redo logs on recovery.
Mysql:
MyISAM's engine, with MySQL's own mysqlhostcopy, needs to read the lock on the table, affecting DML operations.
InnoDB engine that backs up InnoDB tables and indexes, but does not back up the. frm file. When you back up with Ibbackup, a log file records data changes during the backup, so you can use the database without locking the table and not affecting other users. However, this tool is chargeable.
Innobackup is a script that is used in conjunction with Ibbackup, and he assists in backing up the. frm file.

Viii. expansion and flexibility of SQL statements
MySQL has a lot of very useful and convenient extensions to SQL statements, such as the limit function, which inserts multiple rows of data at a time, and select some management data can be non-additive.
Oracle feels more stable and traditional in this respect.

Nine, copy
Oracle: Traditional data replication with either push or pull, or dataguard dual or multi-machine disaster recovery mechanism, the problem with the main library is that you can automatically switch the repository to the main library, but configuration management is more complex.
MySQL: Replication server configuration is simple, but when the main library problems, Concou may lose some data. And you need to manually switch Concou to the main library.

Ten, performance diagnosis
Oracle has a variety of sophisticated performance diagnostic tuning tools that enable many automated analysis and diagnostic functions. such as AWR, ADDM, SQLTrace, tkproof, etc.
MySQL has fewer diagnostic tuning methods, mainly slow query logs.

Xi. Rights and security

MySQL users and host-related, feel no meaning, in addition to more easily be phishing host and IP has an opportunity.
Oracle's rights and security concepts are more traditional and more classic.

12. partition table and partition index
Oracle's partitioned tables and partitioned indexes are mature enough to improve user access to the DB experience.
MySQL's partitioned table is not yet mature and stable.

13. Management Tools
Oracle has a variety of sophisticated command lines, graphical interfaces, web management tools, and a number of third-party management tools that are extremely easy and efficient to manage.
MySQL management tools are less, installation of management tools under Linux sometimes to install additional packages (phpMyAdmin, etc), there is a certain complexity.

The server provides an example of Oracle service, which is the core of the database, it is used for database management, object management and storage, data storage, query, database resource monitoring, monitor and other services.
And the client is just a tool to interact with the server, such as Sqlplus, in the Sqlplus Execute SQL statements to the service side, the server to parse after the operation of the SQL, and output the operation results to the client.
This completes a client-to-server interaction process.

Other:

2. Oracle is a large database and MySQL is a small and medium-sized database, Oracle has a market share of 40%,mysql only about 20%, while MySQL is open source and Oracle is very expensive.
4. Oracle supports large concurrency, large traffic, and is the best tool for OLTP (on-line Transaction processing online transaction processing system).
6. The space used for the installation is also very different, after MySQL installation is 152M and Oracle has about 3G, and when used, Oracle occupies a particularly large memory space and other machine performance.
   4.Oracle also some differences in MySQL operation
   ① primary key MySQL generally uses the automatic growth type, when the table is created as long as the primary key of the specified table is auto increment, when inserting a record, no need to specify the primary key value of the record, MySQL will automatically grow; Oracle does not have an auto-grow type, the primary key is generally used by the sequence, The next value of the sequence number is paid to the field when the record is inserted, but only if the ORM framework is a native primary key generation strategy.
   ② Single-quote processing in MySQL you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes. Single quotation marks must be replaced before inserting and modifying strings: Replace all occurrences of a single quotation mark with two single quotes.
   ③ the processing of the SQL statement of the page turn the SQL statement of the page is simpler, with the limit starting position, the number of records; Oracle's SQL statement for paging is cumbersome. Each result set has only one rownum field indicating its location and can only be used with rownum<100, not rownum>80
   ④ long string processing of long strings Oracle also has its special place. Insert and update when the maximum operand length is less than or equal to 4,000 single bytes, if you want to insert a longer string, consider the field with the Clob type, the method borrows the Dbms_lob package from the Oracle. Be sure to do non-null and length judgments before inserting a modified record, and the field values that cannot be empty and the value beyond the length field should be warned to return the last action. ⑤ NULL character processing the non-empty fields of MySQL also have empty content, and Oracle defines non-empty fields that do not allow empty content. The Oracle table structure is defined by not NULL for MySQL, and errors are generated when the data is being directed. As a result, null characters are judged on the data, and if NULL or NULL, it is necessary to change it to a space string.
   Fuzzy comparison of ⑥ strings MySQL uses the field name like '% string% ', Oracle can also use the field name as '% string% ' but this method cannot use the index, the speed is not fast.

⑦oracle implements most of the functions in Ansii SQL, such as the isolation level of transactions, propagation characteristics, and so on, and MySQL is still relatively

The difference between MySQL and Oracle