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

This article mainly introduces the specific differences and functions of mysql and Oracle, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the specific differences and functions of mysql and Oracle.

I. concurrency

Concurrency is the most important feature of oltp database, but concurrency involves resource acquisition, sharing and locking.

Mysql:

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.

If you want to know big data's learning route, if you want to learn big data knowledge and need free learning materials, you can add group: 784789432. Welcome to join us. Every day, a live broadcast will be held at 3 pm to share basic knowledge, and at 20:00 in the evening, a live broadcast will be held to share the actual combat of big data project.

Oracle:

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.

Second, consistency

Oracle:

Oracle supports the isolation level of serializable to achieve the highest level of 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 undo tablespaces

When each session query, if the corresponding data block changes, oracle will construct the old data block of its query for the session in the undo table space.

Mysql:

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, and other session cannot access the data.

III. Affairs

Oracle fully supports transactions a long time ago.

Mysql supports transactions only in the case of row-level locks of the innodb storage engine.

IV. Data persistence

Oracle

Ensure that the submitted data is recoverable, because oracle writes the submitted sql operating line to the online log file and keeps it on disk

If there is an abnormal restart of the database or host, oracle can take the online log to recover the data submitted by the customer after the restart.

Mysql:

The sql statement is submitted by default, but data may be lost if there is a problem with db or host restart during the update.

V. submission method

Oracle does not submit automatically by default, and requires users to submit it manually.

Mysql defaults to autocommit.

VI. Logical backup

Oracle logical backups do not lock the data, and the backed up data 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 dml.

7. Hot backup

Oracle has a mature hot backup tool rman, which does not affect users' use of the database when hot backup. "even if the backed up database is inconsistent, you can respond consistently through archived logs and online redo logs during recovery."

Mysql:

When using the mysqlhostcopy hot backup provided by mysql, the myisam engine needs to add a read lock to the table, which affects the dml operation.

Innodb's engine, which backs up innodb's tables and indexes, but not .frm files. When backing up with ibbackup, there is a log file that records the data changes during the backup, so you do not have to lock the table and do not affect other users to use the database. But there is a charge for this tool.

Innobackup is a script used in conjunction with ibbackup that assists in backing up .frm files.

VIII. Expansion and flexibility of sql statements

Mysql has many very practical and convenient extensions to sql statements, such as limit function, insert can insert multiple rows of data at a time, select some management data can not add from.

Oracle feels more stable and traditional in this respect.

IX. Copy

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.

Mysql: the configuration of replicating the CVM is simple, but if there is a problem with the main database, the cluster may lose some data. And you need to manually switch the cluster library to the main library.

Performance diagnosis

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.

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

11. Authority 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.

XII. Partition tables and indexes

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

Mysql's zoning table is not very mature and stable.

XIII. Management tools

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.

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.

The server provides the instance of oracle service, which is the core of the database, which is used for database management, object management and storage, data storage, query, database resource monitoring, monitoring and other services.

The client is just a tool that interacts with the server, such as sqlplus, which executes the SQL statement in sqlplus and transmits it to the server. After parsing, the server executes the operation in SQL, and outputs the operation result to the client.

This completes a process of interaction between the client and the server.

Other:

Oracle is a large database and Mysql is a small and medium-sized database. Oracle has a market share of 40% and MySQL is only about 20%. At the same time, Mysql is open source and Oracle is very expensive. Oracle supports large concurrency and large traffic, and is the best tool for OLTP (On-Line Transaction Processing online transaction processing system). The space used for installation is also very different, 152m after Mysql installation, while Oracle has about 3G, and Oracle takes up a lot of memory space and other machine performance when used.

4.Oracle also has some differences in Mysql operation.

① primary key Mysql generally uses the automatic growth type, when creating a table, as long as the primary key of the table is specified as auto increment, and when inserting a record, there is no need to specify the primary key value of the record, and Mysql will grow automatically; Oracle has no automatic growth type, and the primary key is generally used in the sequence, and the next value of the serial number is paid to this field when inserting the record; it's just that the ORM framework is as long as it is a native primary key generation strategy.

The handling of ② single quotation marks you can wrap strings in double quotes in MYSQL and only in single quotation marks in ORACLE. Single quotation marks must be replaced before inserting and modifying strings: replace all occurrences of one single quote with two single quotation marks.

The processing of the SQL statement of ③ page flipping MYSQL processing page SQL statement is relatively simple, with LIMIT starting position, record the number; ORACLE processing page SQL statement is more cumbersome. Each result set has only one ROWNUM field to indicate its location, and can only use ROWNUM80

④ long string handling long string processing ORACLE also has its special features. The maximum operable string length for INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to insert a longer string, consider using the CLOB type for the field, which is borrowed from the DBMS_LOB package included in ORACLE. Be sure to make a non-empty and length judgment before inserting the modification record. Field values that cannot be empty and values that exceed the length field should be warned to return to the last operation. The processing of ⑤ null characters the non-empty field of MYSQL also has empty content. If non-empty field is defined in ORACLE, empty content is not allowed. Define the structure of the ORACLE table according to the NOT NULL of MYSQL, and errors will occur when importing data. Therefore, it is necessary to judge the empty character when importing the data, and if it is a NULL or empty character, you need to change it to a space string.

Fuzzy comparison of ⑥ strings the field name like'% string%'is used in MYSQL, and the field name like'% string% 'can also be used in ORACLE, but this method cannot use indexing and is not fast.

⑦ Oracle implements most of the functions in ANSII SQL, such as the isolation level of transactions, propagation characteristics, etc., while Mysql is still relatively good in this respect.

After reading the specific differences and functions between mysql and Oracle, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.