Shulou(Shulou.com)05/31 Report--

Oracle migration to MySQL performance degradation of which points, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Recently, more customer systems have experienced performance problems (CPU 100%) after the transformation from Oracle to MySQL, or the complex SQL of the background CRM system accumulates during the business peak, resulting in business failures.

In my memory, Taobao initially migrated from Oracle to MySQL also encountered a lot of SQL performance problems, the most memorable sub-query, the original version is MySQL5.1, this version of the sub-query optimization is poor, resulting in a lot of migration from Oracle to MySQL system has performance problems, so the latter development specifications stipulate that the foreground trading system does not have complex table join.

Next, I'll list some common problems that may occur when migrating from Oracle to MySQL:

When customers migrate to O data, there are three types of SQL that must be changed, need not be changed, and can be changed or not. For those that can be changed or not, we should provide some guiding suggestions to help users avoid possible problems in the future.

Guide database developers and database administrators to make rational use of MySQL and give full play to the best performance of MySQL.

Parallel processing

Background introduction

Oracle can physically divide a large serial task (any DML, general DDL) into several small parts, which can be processed at the same time, and finally combine the results of each smaller part to get the final result, so Oracle can use parallel processing technology to run very complex SQL queries in OLAP application scenarios.

There are several ways to start parallel queries:

Use a hint prompt in the query: select / * + parallel (4) / count () from test_a;-specify a parallel query with a degree of parallelism of 4.

Modify the table with alter table: alter table test_a parallel 4bot-tells oracle to use parallelism 4 when creating the execution plan for this table.

Reform suggestion

Because MySQL does not support parallel processing, when an application is migrated from Oracle to MySQL, special attention should be paid to the use of SQL statements with parallel processing. Suggestions for handling:

Analytical database products such as ADS can be used on the Ali cloud platform to handle parallel analysis queries in Oracle.

The complex SQL statement is decomposed into a single SQL statement, and the calculation results are put into the application for processing.

SQL performs logical read, physical read, time consuming

Background introduction

Compared with the optimizer of MySQL, the optimizer of Oracle has rich and perfect optimization algorithms. Only Oracle on table joins supports nested loop, hash join and sort-merge join, while MySQL only supports nested loop, so the processing power of MySQL will be significantly reduced in some complex queries with large table association and multi-table association. So how do you identify queries that are not suitable for migration to MySQL? It can be judged by some key data in SQL execution: logical read, physical read, and elapsed time.

Physical reading: reading data from blocks into buffer cache.

Logical read: refers to reading data blocks from Buffer Cache.

Execution time: the time it takes for Oracle to execute a SQL.

Query a table t for the first time

Select * from t

Second query:

Select * from t

The first query has 6 physical reads, and the second query has 0 physical reads and 6 logical reads. When a block is read for the first time, it is cached in buffer cache, and the second time it is read and modified, it is buffer cache in memory.

Logical read is a very important metric in Oracle performance tuning. It is not only easy to collect, but also can tell us a lot about the workload of the database engine. Logical reads are the number of blocks read from the cache when the SQL statement is executed.

Reform suggestion

MySQL is very fast for simple SQL statements. For logical reads in Oracle applications, it is no longer suitable for SQL with very high physical read or execution time to MySQL, and needs to be modified:

In the case of logical read, physical read and execution time of a single table query, SQL may have a full table scan (dump requirements) or poor index. You can use read-only nodes to withstand dump or optimize the index.

ADS analytical database products can be used to deal with the cases of logical reading, physical reading and long execution time of multi-table query.

In the case of multi-table query logic reading, physical reading and execution time is relatively long, the business can be decomposed into a single SQL statement, and the calculation results can be put into the application for processing.

Note: if logical and physical reads exceed 100W and the execution time exceeds 5S, they belong to larger SQL queries.

In (… .)

Background introduction

In (…) in Oracle. (.) Although there is no limit on the number of parameters in MySQL, there is a limit on the length of SQL. At the same time, the optimizer has a limit on in (…) Binary search is used when the query is optimized, so in (…) The more the number, the worse the performance, so it is recommended to control the number of in, not more than 100.

Reform suggestion

Oracle:select * from t where id in (id1,id2 … .. id1000)

MySQL:select * from t where id in (id1,id2 … .. id100)

Subquery

Background introduction

When MySQL processes subqueries prior to version 5.6, because the optimizer only supports the nested loop algorithm, it will cause performance bottlenecks when the associated table is large. The author once participated in the migration of MySQL migrated from Oracle in a large-scale project. At that time, the version of the database was 5.5. there were a large number of subqueries in the original Oracle application. After migrating to MySQL, the SQL execution piled up, the number of connections was full, and the cpu of the database was quickly consumed. Finally, the subquery was modified before the system was restored.

Typical subquery

SELECT first_name

FROM employees

WHERE emp_no IN

(SELECT emp_no FROM salaries_2000 WHERE salary = 5000)

The processing logic of MySQL is to traverse every record in the employees table and substitute it into the subquery.

Reform suggestion

Rewrite subquery

SELECT first_name

FROM employees emp

(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal

WHERE emp.emp_no = sal.emp_no

Note: subqueries are risky in version 5. 1 and 5. 5, so change subqueries to association.

With the version of Mysql 5.6, you can avoid troublesome subquery rewriting problems.

View optimization

Background introduction

The normal view does not store the actual information, and the data it operates on comes from the basic table, so indexes cannot be created on the normal view. What do you do when you need to make a large number of queries to the view, but the query efficiency is low? There are materialized views in Oracle, materialized views are physically real, and indexes can be created. MySQL does not support materialized views, so when views in Oracle are migrated to MySQL, performance may be degraded because there are no materialized views.

Reform suggestion

Split the business of the view and implement it by the application.

Functional index

Background introduction

A function-based index, similar to an ordinary index, except that an ordinary index is based on a column, and it is based on a function. Of course, this time it has some effect on inserting data, because it needs to be calculated by the function, and then the index is generated. However, the inserted data is generally inserted in a small amount, while the query data is generally large. In order to optimize the query speed, it is acceptable to reduce the insertion speed slightly.

MySQL does not support functional indexes, so when SQL statements in Oracle that use functional indexes are migrated to MySQL, the performance of full table scans will be degraded due to the inability to use indexes.

For example, execute the following SQL statement:

Select * from emp where date (gmt_create) = '2017-02-20'

Even if the index is established on gmt_create, the emp table is scanned throughout the table, and the gmt_create field is removed and compared. If we set up a function-based index, such as create index emp_upper_idx on emp (date (gmt_create)); at this time, we only need to scan a small part of the data by interval, and then get rowid to access the data in the table, which is relatively fast.

Reform suggestion

Remove the function on the field through SQL rewriting, so that you can use the index on the field:

Select * from emp where gmt_create > = '2017-01-20 00 and gmt_created

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.