Fragmetacion de datos mysql

2011 creado por Javier Hasaff Gamboa del Río

Laboratorio 2 (UNIDAD II) FRAGMENTACIÓN DE DATOS

* Realizar el siguiente ejercicio (Perteneciente al sitio oficial de M ySQL)

Improving Database Performance with Partitioning
A few years ago, I wrote an article entitled "The Foundation of Excellent Performance" (still available at http://www.tdan.com/i016fe03.htm) where I argued against thenotion that SQL code was the number one contributor to performance in a database-driven system. Instead, I stated in the article that I firmly believed how good physical database design was far and away the leading component of superior database performance. In addition, I showed that Oracle's own research illustrated how poor design was the main culprit behind database downtime (planned orunplanned). In the years since then, I've not changed my stance and still think that any DBA who wants a high-performance database has got to invest in intelligent and savvy physical design to produce the kind of response times that make end users smile instead of scream. One of the reasons I'm very excited about the release of M ySQL 5.1 is that it contains a potent new weapon for designing superchargeddatabases that any M ySQL DBA should quickly learn how to use and exploit. By smartly using the new 5.1 partitioning feature, a DBA can oftentimes dramatically improve the performance of most any VLDB or data warehouse they happen to be in charge of.

What is Partitioning?
Partitioning is a physical database design technique that many data modelers and DBAs are quite familiar with. Althoughpartitioning can be used to accomplish a number of various objectives, the main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced. There are two major forms of partitioning: 1. Horizontal Partitioning - this form of partitioning segments table rows so that distinct groups of physical row-based datasets are formed that can be addressedindividually (one partition) or collectively (one-to-all partitions). All columns defined to a table are found in each set of partitions so no actual table attributes are missing. An example of horizontal partitioning might be a table that contains ten years worth of historical invoice data being partitioned into ten distinct partitions, where each partition contains a single year's worth of data.2. Vertical Partitioning - this partitioning scheme is traditionally used to reduce the width of a target table by splitting a table vertically so that only certain columns are included in a particular dataset, with each partition including all rows. An example of vertical partitioning might be a table that contains a number of very wide text or BLOB columns that aren't addressed often being brokeninto two tables that has the most referenced columns in one table and the seldom-referenced text or BLOB data in

another. Before database vendors began building partitioning (mainly horizontal) into their engines, DBAs and data modelers had to physically design separate table structures to hold the desired partitions, which either held redundant data (separate tables with data that were basedoff a live parent table) or were linked together to form one logical parent object (usually via a view). This practice has since been made obsolete for the most part for horizontal partitioning, although it is sometimes still done for vertical partitioning.

Partitioning in MySQL 5.1
One of the great new features in M ySQL 5.1 is support for horizontal partitioning. The really good news aboutM ySQL and the new 5.1 partitioning feature is all the major forms of partitioning are supported: 1. Range - this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980's, 1990's, and everything beyond and including the year 2000. 2. Hash - this...