Home > MySQL Introduction > MySQL Primer

MySQL Primer

logo-mysql

  

  

MySQL Origin:

MySQL was originally developed by Michael Widenius and David Axmark beginning in 1994, founders of a Swedish company MySQL AB. It was first released internally on 23 May 1995. Windows version was released on 8 January 1998 for Windows 95 and NT. Sun Microsystems acquired MySQL AB on 26 February 2008. MySQL AB yet holds the copyright to most of the codebase. In April 2009, Oracle Corporation entered into an agreement to purchase Sun Microsystems, current owners of the MySQL intellectual property. The deal was unanimously approved by Sun’s board of directors, and it is anticipated to close soon, subject to Sun stockholder approval, certain regulatory approvals and customary closing conditions.

MySQL is written in C and C++. MySQL runs on more than 20 different system platforms, including AIX, BSDi, FreeBSD, HP-UX, i5/OS, Linux, Mac OS X, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, eComStation, OS/2 Warp, QNX, IRIX, Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos, Tru64 and Microsoft Windows; giving you the kind of flexibility that puts you in control. In addition, an ODBC interface called MyODBC allows additional programming languages that support the ODBC interface to communicate with a MySQL database, such as ASP or ColdFusion. The MySQL server and official libraries are mostly implemented in ANSI C/ANSI C++.

To administer MySQL databases one can use the included command-line tool (commands: mysql and mysqladmin). Also downloadable from the MySQL site are GUI administration tools: MySQL Administrator, MySQL Migration Toolkit and MySQL Query Browser. The GUI tools are now included in one package called MySQL GUI Tools. In addition to the these tools developed by MySQL AB, there are several other commercial and non-commercial tools available. Examples include Navicat Free Lite Edition, AnySQL Maestro Freeware Edition or SQLyog Community Edition, they are free desktop based GUI tools, and phpMyAdmin, a free Web-based administration interface implemented in PHP. 

  

MySQL Introduction:

MySQL is a relational database management system (RDBMS) which can be used to store, sort, arrange, and display information. MySQL stands for “My Structured Query Language”. The program runs as a server providing multi-user access to a number of database instances. The project’s source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL is commonly used by free software projects which require a full-featured database management system, such as WordPress, phpBB etc. It is also used in very high-scale World Wide Web products including Google and Facebook. Several high-traffic web sites (Flickr,Facebook,Wikipedia, Nokia, Auctionmarts and YouTube) use MySQL for data storage and logging of user data.

MySQL is an open-source database system with which we can do the following things:

  • Design the structure of the tables (called schema) and how they relate to one another
  • Add, edit and delete data
  • Sort and manipulate data
  • Query the database (that is, ask questions about the data)
  • Produce listings based on queries

To interact with MySQL, we enter commands on a command line. These commands, such as CREATE, INSERT, UPDATE, etc. are based on a more general language called SQL (Structured Query Language). SQL has a long and colorful history, starting at IBM in the 70’s (based on the research of E.F. Codd who developed the relational data model in 1970), and later through the work of a small company called Relational Software, Inc. In 1979, this group produced the first commercially available relational database system and implemented SQL as its query language. They called the product Oracle.

The MySQL database has become the world’s most popular open source database because of its consistent fast performance, high reliability and ease of use. Not only is MySQL the world’s most popular open source database, it’s also become the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) MySQL represents the most impressive market success, exceeded only perhaps by Apache, in free and open source software. In terms of installed base, MySQL has left the technically impressive rival PostgreSQL behind. It has marginalized mSQL, SQLite, and SAP DB. It has started to challenge the proprietary database companies on their own turf. Nobody can say why licensing costs for proprietary databases have dropped sharply in recent years, but one suspects that it’s due to MySQL’s competition.

  • MySQL AB claims an installed base of five million systems, the largest of any database engine.
  • The mysql.com domain sees almost as much traffic as ibm.com.
  • Six hundred attendees flocked to the recent MySQl conference.
  • MySQL AB has recently been heavily marketing, its own publishing outlet,MySQL Press.

Many of the applications that a Web developer wants to use can be made easier by the use of a standardized database to store, organize, and access information. MySQL can easily be integrated into Perl programs by using the Perl DBI (DataBase Independent interface) module. DBI is an Application Program Interface (API) that allows Perl to connect to and query a number of SQL databases (among them MySQL, mSQL, PostgreSQL, Oracle, Sybase, and Informix).

  

  

Architecture:

The storage engine architecture of MySQL makes it unique in the database world. Of special interest is the pluggable storage engines layer, in the MySQL architecture shown below:

mySqlPSEArch

  

Connectors:

Above the storage engine layer, MySQL presents itself as any other database management system would. A group of connectors is available through which to query the database.

Connection pool:

A connection pool in the server provides authentication and manages threads, connections, memory, and caches. Modules in the server parse queries, optimize access paths, and so on.

Storage engines:

Multiple storage engines can be plugged into the architecture. Storage engines are the components of the database server that perform actions on the underlying data being maintained at the physical server level. The pluggable storage engine architecture enables a standard set of management and support services to apply to all underlying storage engines. The result is like having many databases under the umbrella of a single manager.

Reasons for using pluggable architecture of storage engines:

Some storage engines, such as those used for archiving, are nontransactional in nature. These storage engines can insert and read data very efficiently. Other storage engines are tuned for efficiency when transactional operations are required, and still others provide high availability through clustering. These storage engines avoid the natural overhead of proprietary systems, which typically must live with the transactional overhead of their table architectures even when it isn’t needed.

For example, a site that primarily needs to look up data can avoid all of the transactional overhead by using the MyISAM storage engine, which is the default engine for MySQL. The MyISAM engine offers high-speed query and insert capability, is nontransactional, provides table-level locking, and supports indexes. It is a good engine for traditional data warehouses.

The MySQL database server comes supplied with a group of internal storage engines. External storage engines are tuned to optimize performance for specific products and situations, and are supplied both by independent software vendors and by the MySQL community.

Having multiple storage engines provides you with the capability of multiple databases. If you migrate to MySQL from another database, you don’t need to change the way your applications interact with the database server — the SQL code is the same. But internal to the MySQL server, you can pick and choose storage engines that meet your application’s needs.

For example, if you discover that you need a transactional database, you can use a single statement to transform your database from a nontransactional system to a transactional one. If you then decide that you need a data warehouse, a single command can put you in possession of a powerful column-oriented database tuned for data retrieval. If your main activity is to capture and store data, the Archive storage engine is designed to efficiently handle large volumes of inserts and compress data to a small footprint.

MySQL is based on a tiered architecture, consisting of both primary subsystems and support components that interact with each other to read, parse, and execute queries, and to cache and return query results. The organization of these features are shown in the following figure. Let’s look into each one briefly to help gain a better understanding of how the parts fit together.

 

mysqlarch

  

  

  

Primary Subsystems:

The MySQL architecture consists of five primary subsystems that work together to respond to a request made to the MySQL database server:

  • The Query Engine
  • The Storage Manager
  • The Buffer Manager
  • The Transaction Manager
  • The Recovery Manager

 

The Query Engine:

This subsystem contains three interrelated components:

  • The Syntax Parser
  • The Query Optimizer
  • The Execution Component

The Syntax Parser decomposes the SQL commands it receives from calling programs into a form that can be understood by the MySQL engine. The objects that will be used are identified, along with the correctness of the syntax. The Syntax Parser also checks the objects being referenced to ensure that the privilege level of the calling program allows it to use them.

The Query Optimizer then streamlines the syntax for use by the Execution Component, which then prepares the most efficient plan of query execution. The Query Optimizer checks to see which index should be used to retrieve the data as quickly and efficiently as possible. It chooses one from among the several ways it has found to execute the query and then creates a plan of execution that can be understood by the Execution Component. The Query Optimizer uses probability-based induction, so you may want to override it if you think that you already know the ideal way to access your query results; this will prevent the engine from using another, less optimal plan. MySQL provides you the option of giving the engine certain “hints” if you want it to use specific indexes without checking the Optimizer.

The Execution Component then interprets the execution plan and, based on the information it has received, makes requests of the other components to retrieve the records.

 

The Storage Manager:

The Storage Manager interfaces with the operating system (OS) to write data to the disk efficiently. Because the storage functions reside in a separate subsystem, the MySQL engine operates at a level of abstraction away from the operating system. This means that if you port to a different operating system that uses a different storage mechanism, for example, you can rewrite only the storage portion of the code while leaving the rest of the engine as is. With the help of MySQL’s Function Libraries (discussed shortly in the section “Support Components”), the Storage Manager writes to disk all of the data in the user tables, indexes, and logs as well as the internal system data.

The Query Cache – If a query returns a given set of records, repeating the same query should return the same set of records unless the underlying data has somehow changed. As obvious as this sounds, few of the other major relational database management system (RDBMS) vendors provide features that take advantage of this principle. Other database products are efficient in storing optimized access plans that detail the process by which data is retrieved; such plans allow queries similar to those that have been issued previously to bypass the process of analyzing indexes yet again to get to the data.

Result set caching takes this principle a step further by storing the result sets themselves in memory, thus circumventing the need to search the database at all. The data from a query is simply placed in a cache, and when a similar query is issued, this data is returned as if in response to the query that created it in the first place. The MySQL engine uses an extremely efficient result set caching mechanism, known as the Query Cache, that dramatically enhances response times for queries that are called upon to retrieve the exact same data as a previous query.

This mechanism is so efficient that a major computing publication declared MySQL queries to be faster than those of Oracle and SQL Server (which are both known for their speed). If implemented properly, decision support systems using MySQL with canned reports or data-driven web pages can provide response speeds far beyond those that would be expected without the Query Cache.

 

The Buffer Manager:

This subsystem handles all memory management issues between requests for data by the Query Engine and the Storage Manager. MySQL makes aggressive use of memory to cache result sets that can be returned as-is rather than making duplicate requests to the Storage Manager; this cache is maintained in the Buffer Manager.

This is also the area where new records can be cached while waiting for availability of targeted tables and indexes. If any new data is needed, it’s requested from the Storage Manager and placed in the buffer before then being sent to the Query Engine.

 

The Transaction Manager:

The function of the Transaction Manager is to facilitate concurrency in data access. This subsystem provides a locking facility to ensure that multiple simultaneous users access the data in a consistent way, without corrupting or damaging the data in any way. Transaction control takes place via the Lock Manager subcomponent, which places and releases locks on various objects being used in transactions. Each transactional table handler implements its own Transaction Manager to handle all locking and concurrency needs.

 

The Recovery Manager:

The Recovery Manager’s job is to keep copies of data for retrieval later, in case of a loss of data. It also logs commands that modify the data and other significant events inside the database.

So far, only the InnoDB and BDB table handlers provide recovery management. The MyISAM handler doesn’t have transactional recovery procedures, but it does provide mechanisms that apply certain recovery features in case of a server outage; these features “fix” any internal inconsistencies that might occur as the result of such a crash. Such inconsistencies are usually related to indexes not being properly updated to reflect the contents of a table or records being incompletely written to a database.

 

arch

 

 

 

MySQL Quotes:

MySQL is the world’s most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout its history. With its superior speed, reliability, and ease of use, MySQL has become the preferred choice for Web, Web 2.0, SaaS, ISV, Telecom companies and forward-thinking corporate IT Managers because it eliminates the major problems associated with downtime, maintenance and administration for modern, online applications.

Many of the world’s largest and fastest-growing organizations use MySQL to save time and money powering their high-volume Web sites, critical business systems, and packaged software — including industry leaders such as Yahoo!, Alcatel-Lucent, Google, Nokia, YouTube, Wikipedia, and Booking.com.

The flagship MySQL offering is MySQL Enterprise, a comprehensive set of production-tested software, proactive monitoring tools, and premium support services available in an affordable annual subscription.

MySQL is a key part of LAMP (Linux, Apache, MySQL, PHP / Perl / Python), the fast-growing open source enterprise software stack. More and more companies are using LAMP as an alternative to expensive proprietary software stacks because of its lower cost and freedom from platform lock-in.

The MySQL database is owned, developed and supported by Sun Microsystems, one of the world’s largest contributors to open source software. MySQL was originally founded and developed in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael “Monty” Widenius, who had worked together since the 1980’s.

Our Continued MySQL Values

We want the MySQL database to be:

  • The best and the most-used database in the world for online applications
  • Available and affordable for all
  • Easy to use
  • Continuously improved while remaining fast, secure and reliable
  • Fun to use and improve
  • Free from bugs

We want the people working on MySQL to:

  • Subscribe to the Open Source philosophy
  • Aim to be good citizens
  • Prefer partners that share our values and mindset
  • Answer email and give assistance to users, customers, partners and co-workers
  • Be a virtual organization, networking with others

Whether you’re new to database technology or an experienced developer or DBA, MySQL offers a comprehensive range of certified software, support, training and consulting to make everyone successful.

 

 

Links:

http://www.mysql.com/

http://www.sun.com/software/products/mysql/index.jsp

 

Note: Refer to other posts in MySQL category for further knowledge.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: