50 things to know before migrating Oracle to MySQL
A while back I was at a seminar on migrating database applications to
MySQL. A lot of the attendees were Oracle users. Based on their
questions, comments and conversations during lunch, I made the following
list of things Oracle users need to know about migrating to MySQL.
Most of these are “gotchas” that would be contraindications or require
some thought about a workaround.
Note: this is not meant to be MySQL-bashing. Some of these
limitations are going to be fixed in future versions of MySQL, but they
generally apply to current GA version 5.1. Some things are possible to
achieve by choosing one particular way to use the server, at the
exclusion of other things (e.g. Cluster rules out foreign keys, spatial
data types rules out transactions). I am posting this list at the
request of some Oracle DBAs who asked me to produce it for them.
- Subqueries are poorly optimized.
- Complex queries are a weak point.
- The query executioner (aka query optimizer / planner) is less
- Performance tuning and metrics capabilities are limited.
- There is limited ability to audit.
- Security is unsophisticated, even crude. There are no groups or
roles, no ability to deny a privilege (you can only grant privileges). A
user who logs in with the same username and password from different
network addresses may be treated as a completely separate user. There
is no built-in encryption comparable to Oracle.
- Authentication is built-in. There is no LDAP, Active Directory, or
other external authentication capability.
- Clustering is not what you think it is.
- Stored procedures and triggers are limited.
- Vertical scalability is poor.
- There is zero MPP support.
- SMP is supported, but MySQL doesn’t scale well to more than 4 or 8
- There is no fractional-second storage type for times, dates, or
- The language used to write stored procedures, triggers, scheduled
events, and stored functions is very limited.
- There is no roll-back recovery. There is only roll-forward
- There is no support for snapshots.
- There is no support for database links. There is something called
the Federated storage engine that acts as a relay by passing queries
along to a table on a remote server, but it is crude and buggy.
- Data integrity checking is very weak, and even basic integrity
constraints cannot always be enforced.
- There are very few optimizer hints to tune query execution plans.
- There is only one type of join plan: nested-loop. There are no
sort-merge joins or hash joins.
- Most queries can use only a single index per table; some
multi-index query plans exist in certain cases, but the cost is usually
underestimated by the query optimizer, and they are often slower than a
- There are no bitmap indexes. Each storage engine supports
different types of indexes. Most engines support B-Tree indexes.
- There are fewer and less sophisticated tools for administration.
- There is no IDE and debugger that approaches the level of
sophistication you may be accustomed to. You’ll probably be writing
your stored procedures in a text editor and debugging them by adding
statements that insert rows into a table called debug_log.
- Each table can have a different storage backend (“storage engine”).
- Each storage engine can have widely varying behavior, features, and
- Foreign keys are not supported in most storage engines.
- The default storage engine is non-transactional and corrupts
- Oracle owns InnoDB, the most advanced and popular storage engine.
- Certain types of execution plans are only supported in some storage
engines. Certain types of COUNT() queries execute instantly in some
storage engines and slowly in others.
- Execution plans are not cached globally, only per-connection.
- Full-text search is limited and only available for
non-transactional storage backends. Ditto for GIS/spatial types and
- There are no resource controls. A completely unprivileged user can
effortlessly run the server out of memory and crash it, or use up all
- There are no integrated or add-on business intelligence, OLAP cube,
- There is nothing analogous to Grid Control.
- There is nothing even remotely like RAC. If you are asking “How do
I build RAC with MySQL,” you are asking the wrong question.
- There are no user-defined types or domains.
- The number of joins per query is limited to 61.
- MySQL supports a smaller subset of SQL syntax. There are no
recursive queries, common table expressions, or windowing functions.
There are a few extensions to SQL that are somewhat analogous to MERGE
and similar features, but are very simplistic in comparison.
- There are no functional columns (e.g. a column whose value is
calculated as an expression).
- You cannot create an index on an expression, you can only index
- There are no materialized views.
- The statistics vary between storage engines and regardless of the
storage engine, are limited to simple cardinality and rows-in-a-range.
In other words, statistics on data distribution are limited. There is
not much control over updating of statistics.
- There is no built-in promotion or failover mechanism.
- Replication is asynchronous and has many limitations and edge
cases. For example, it is single-threaded, so a powerful slave can find
it hard to replicate fast enough to keep up with a less powerful
- Cluster is not what you think it is. Maybe I already said that,
but it bears repeating.
- The data dictionary (INFORMATION_SCHEMA) is limited and very slow
(it can easily crash a busy server).
- There is no online ALTER TABLE.
- There are no sequences.
- DDL such as ALTER TABLE or CREATE TABLE is non-transactional. It
commits open transactions and cannot be rolled back or crash-recovered.
Schema is stored in the filesystem independently of the storage engine.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/58054/viewspace-630792/，如需转载，请注明出处，否则将追究法律责任。