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

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.

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 the replication server is simple, but when there is a problem with the main database, the cluster database 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.

XIV. Technical support

If there is a problem with oracle, you can go to customer service.

If there is a problem in mysq, you can solve it yourself.

15. Authorization

Oracle charge

Mysq Open Source-Free

Sixteen. Select

If you have money, you should use oracle.

No money and can meet the needs of the proposal to use mysq. (Alibaba, wiki encyclopedia and other large-scale projects also use mysql, and people mainly use distributed storage, cache, sub-table, sub-library and other technologies.)

Here is another article, mainly in terms of sql:

The difference between Oracle database and MySQL database is the main content of this article, and we hope it can be helpful to you.

1. Usage rules of group function

The group function in mysql can be used freely in the select statement, but if there is a group function in the query statement in oracle, the other column names must be handled by the group function, or the column in the group by clause will report an error.

Eg:

Select name,count (money) from user; this is no problem in mysql, there is a problem in oracle.

While select name,count (money) from user group by name or select max (name), count (money) from user

No errors will be reported in oracle, nor will they be reported in mysql.

two。 Automatic growth of data type processing

MYSQL has auto-growing data types. You don't have to manipulate this field when you insert a record, and you will automatically get the data value. ORACLE does not have an auto-growing data type, so you need to create an auto-growing serial number, and when you insert a record, you assign the next value of the serial number to this field.

Name of the CREATE SEQUENCE serial number (preferably table name + serial number tag) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE

The maximum value is determined by the length of the field. If the auto-growing serial number NUMBER (6) is defined, the maximum value is 999999.

The value of this field inserted by the INSERT statement is: the name of the serial number. NEXTVAL

3. The treatment of single quotation marks

Strings can be wrapped in double quotation marks 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.

4. The processing of the SQL statement of turning pages

MYSQL is relatively simple to deal with page turning SQL statements, using the LIMIT start position, record the number; in PHP, you can also use SEEK to locate the result set. It is more tedious for ORACLE to deal with SQL statements that turn pages. Each result set has only one ROWNUM field to indicate its location, and can only use ROWNUM80.

Here are two better ORACLE paging SQL statements after analysis (ID is the field name of the only keyword):

Statement 1:

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN (SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE condition 1 ORDER BY condition 2) WHERE NUMROW > 80 AND NUMROW

< 100 ) ORDER BY 条件3; 语句二： SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW >

80 AND NUMROW

< 100 ) ORDER BY 条件3; 5.长字符串的处理 长 字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节，如果要插入更长的字 符串，请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和 超出长度字段值都应该提出警告，返回上次操作。 6.日期字段的处理 MYSQL日期字段 分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为 SYSDATE，精确到秒，或者用字符串转换成日期型函数TO_DATE('2001-08-01','YYYY-MM-DD')年-月-日24小时:分 钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式，可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS') 日期字 段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用DATE_FIELD_NAME >

SUBDATE (NOW (), INTERVAL 7 DAY) ORACLE found 7 days from the current time with DATE_FIELD_NAME > SYSDATE-7

Several functions that insert the current time into the MYSQL are: the NOW () function returns the current date and time as `'YYYY-MM-DD HH:MM:SS', which can be directly stored in the DATETIME field. CURDATE () returns today's date in the format 'YYYY-MM-DD', which can be saved directly into the DATE field. CURTIME () returns the current time in the format 'HH:MM:SS', which can be saved directly in the TIME field. Example: insert into tablename (fieldname) values (now ())

The current time in oracle is sysdate

7. Processing of null characters

MYSQL's non-empty fields also have empty content, and ORACLE defines non-empty fields that do not allow empty content. 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.

8. 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 index and is not fast. Using string comparison function instr (field name,' string') > 0 will get more accurate search results.

9. In programs and functions, please pay attention to the release of result sets and pointers after the work of manipulating the database is completed.

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.