What is MySQL Storage Engine?

If you are not aware with MySQL, or are familiar with other relational database systems, the concept of a storage space motor can take some time to understand. In summary, although MySQL communicates and manages data via Structured Query Language (SQL), internally MySQL has different mechanisms to support the storage space management and retrieval of the underlying data. The flexibility of MySQL storage space google is both a blessing and a curse. The saying “With great flexibility comes great responsibility” is applicable in this sense

We will not be detailing storage space google in this book, but it is critical that you understand some basic information about storage space motor features and capabilities, including the following:

• Transactional and non-transactional

• Chronic and non-persistent

• Table and row level locking

• Different index methods such as B-tree, B+tree, Hash, and R-tree

• Clustered indices versus non-clustered indexes

• Primary versus secondary indexes

• Data compression

• Full text index capabilities

MySQL supports the capability of pluggable storage space google from other service providers, which includes both free and commercial offerings. Being a free product, MySQL offers variants that support additional different storage space google.

There are three primary storage space google that are included automatically with MySQL:

• MyISAM A non-transactional storage space motor that was the standard for all MySQL versions prior to 5.5

• InnoDB The most popular transactional storage space motor and the standard motor starting with version 5.5

• Memory As the name suggests, a memory based, non-transactional, and non-persistent storage space engine

NOTE

Starting with version 5.5, the standard storage space motor for tables has changed from the MyISAM storage space motor to the InnoDB storage space motor. This can have a significant effect when you are installing packaged software that relies on the standard settings and was originally written for the MyISAM storage space motor.

Current versions of MySQL also include the built-in storage space engines of ARCHIVE, MERGE, BLACKHOLE, and CSV. Some of the other popular storage space engines provided by MySQL or third parties include Federated, ExtraDB, TokuDB, NDB, Maria, InfinDB, Infobright, as well as many more.

TIP

You can use the SHOW CREATE TABLE, SHOW TABLE STATUS, or INFORMATION_SCHEMA.TABLES to determine the storage space motor of any given table. Chapter 2 provides detailed examples of these options.

MySQL 5.5 Reinforced Storage space Engines

InnoDB: The standard storage motor as of MySQL 5.5.5. InnoDB is a transaction-safe (ACID compliant) storage motor for MySQL that has make, rollback, and crash-recovery abilities to secure customer details. InnoDB row-level securing (without escalation to rougher granularity locks) and Oracle-style reliable nonlocking flows increase multi-user concurrency and performance. InnoDB shops customer details in grouped indices to reduce I/O for common concerns based on primary important factors. To maintain details reliability, InnoDB will also support FOREIGN KEY referential-integrity restrictions. For more details about InnoDB, see Section 14, The InnoDB Storage space Engine.

MyISAM: The MySQL storage motor that is used the most in Web, data warehousing, and other application surroundings. MyISAM is supported in all MySQL options, and is the standard storage motor prior to MySQL 5.5.5.

Memory: Stores details in RAM for extremely instant access in surroundings that need quick concerns of referrals and other like details. This motor was formerly known as the HEAP motor.

Merge: Enables a MySQL DBA or designer to rationally group a sequence of identical MyISAM platforms and referrals them as one item. Suitable for VLDB surroundings such as details warehousing.

Archive: Provides the perfect solution for saving and accessing considerable amounts of seldom-referenced traditional, stored, or security review details.

Federated: Offers the ability to link individual MySQL web servers to create one sensible data source from many physical web servers. Very excellent for allocated or details mart surroundings.

NDB (also known as NDBCLUSTER)—This grouped data source motor is particularly suited to programs that need the maximum possible degree of up-time and accessibility. Oracle dba course is always availaable for you if you join the sql training in Pune.

Don't be shellfish...Digg thisBuffer this pageEmail this to someoneShare on FacebookShare on Google+Pin on PinterestShare on StumbleUponShare on LinkedInTweet about this on TwitterPrint this pageShare on RedditShare on Tumblr

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>