high performance mysql sakila_HPM Note4, Schema Optimization and Indexing

Choosing Optimal Data Types

Smaller is usually better

Simple is good

Avoid NULL if possible

Indexing Basics

Indexes are implemented in the storage engine layer, not the server layer

B-Tree indexes is the default index type of MySQL

B-Trees store the indexed columns in order, they're usefull for searching for ranges of data

B-Tree indexes work well for lookups by the full key value, a key range, or a key prefix

B-Tree limitations:

1. They are not usefull if the lookup does not start from the leftmost side of the indexed columns

2. You can't skip columns in the index

3. The storage engine can't optimize accesses with any columns to the right of the first range condition

Hash indexes

Hash index is built on a hash table and is useful only for exact lookups that use every column in the index

For each row, the storage engine computes a hash code of the indexed columns, which is a small value that will probably differ from the hash codes computed for other rows with different key values

It stores the hash codes in the index and stores a pointer to each row in a hash table

Hash indexes are the default index type for Memory tables in MySQL

Hash index limitations:

1. Index contains only hash code and row pointers, MySQL can't use the values in the index to avoid reading the rows

2. MySQL can't use has indexes for sorting bc they don't store rows in sorted order

3. Hash indexes don't support partial key matching bc they compute the hash from the entire indexed value

4. Hash indexes support only equality comparisons that use the =, IN(), and <=> operators, they don't support range queries

5. Accessing data in a hash index is very quick, unless there are many collisions(When there are multiple values with the same hash, the storage engine must follow each row pointer in the linked list and compare their values to the lookup value to find the right rows)

6. Some index maintenance operations can be slow if there are many hash collisions

Hash index are useful for lookup tables in a classic "star" schema data-warehousing

The InnoDB storage engine has a special feature called adaptive hash indexes

When InndoDB notices that some index values are being accessed very frequently, it builds a hash index for them in memory on top of B-Tree index

This process is completely automatic, and you can't control or configure it

You can build your own hash index if storage engine does not support it

Spatial(R-Tree) indexes

MyISAM supports spatial indexes, which you can use with geospatial types such as GEOMETRY

Full-text indexes

FULLTEXT is a special type of index for MyISAM tables

Full-text indexes are for MATCH AGAINST operations, not ordinary WHERE clause operations

Indexing Strategies for High performance

Isolate the Column

If you don't isolate the indexed columns in a query, MySQL generally can't use indexes on columns unless the columns are isolated in the query

// these queries can't use index

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

Prefix Inndexes and Index Selectivity

For long character columns such as BLOB or TEXT, we can index the prefix and make index selectivity close to full column's selectivity

Clustered Indexes

From wikipedia:

Index architectures can be classified as clustered or non-clustered.

Unclustered

An Unclustered index is structured in a way that doesn't correspond to the order of the actual data records. It resembles the words index at the back of a book. For this reason, it will perform worse than clustered indexes on ranged searches where the result set is large, since each result could cost an additional I/O-operation to get the actual data record. One can have any number of these kinds of indexes, since all that is required is the space to store the index itself -- one does not copy the actual data to create a new index.

Clustered

Clustering alters the data block into a certain distinct order to match the index, hence it is also an operation on the data storage blocks as well as on the index. An address book ordered by first name resembles a clustered index in its structure and purpose. The exact operation of database systems vary, but because storing data is very redundant the row data can only be stored in one order. Therefore, only one clustered index can be created on a given database table. Clustered indexes can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.

Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).Clusturing is a great example of Indexing and is far more sophisticated than an unclustered index.

Now only solidDB and InnoDB support clustered indexes

InnoDB clusters the data by the primary key

If you don't define a primary key, InnoDB will try to use a unique nonnullable index instead

If there's no such index, InnoDB will define a hidden primary key for you and the cluster on that

Clustered indexes advantages:

1. You can keep related data close together

2. Data access is fast

3. Queries that use covering indexes can use the primary key values contained at the leaf node

Clustered indexes disadvantages:

1. Clustering gives the largest improvement for I/O-bound workfloads. If the data fits in memory the roder in which it's accessed doesn't really matter, so clustering doesn't give much benefit

2. Insert speeds depend heavily on insertion order

3. Updating the clustered index columns is expensive, because it forces InnoDB to move each updated row to a new location

4. Tables built upon clustered indexes are subject to page splits when new rows are inserted, or when a row's primary key is updated such that the row must be moved

5. Clustered tables can be slower for full table scans

6. Secondary(nonclustered) indexes can be larger than you might expect, because their leaf nodes contain the primary key columns of the referenced rows

7. Secondary index accesses require two index lookups instead of one. To find a row from a secondary index, the storage engine first finds the leaf node in the secondary index and then uses the primary key values stores there to navigate the primary key and find the row

An index that contains or covers all the data needed to satisfy a query is called a covering index

An Indexing Case Study

You should put range column to the end of index, because MySQL uses only the leftmost prefix, up to and including the first condition that specifies a range of values

Avoid multiple range conditions

The query can be slow if the user interface is paginated and someone requests a page that's not near the beginning

Denormalizing, precomputing, and caching are likely to be the only strategies that work for queries like this

An even better strategy is to limit the number of pages you let the user view

This is unlikely to impact the user's experience, because no one really cares about the 10,000th page of search results

Index and Table Maintenance

Finding and Repairing Table Corruption

CHECK TABLE

REPAIR TABLE

ALTER TABLE innodb_tbl ENGINE=INNODB;

myisamchk

backup and recovery

Updating Index Statistics

ANALYZE TABLE

SHOW INDEX FROM

INFORMATION_SCHEMA.STATICTICS

Reducing Index and Data Fragmentation

OPTIMIZE TABLE

dump and reload the data

Normalization and Denormalization

Database Normalization

Advantages of Normalized Schema:

1, Normalized upodates are usually faster than denormalized updates

2, When the data is well normalized, there's little or no duplicated data, so there's less data to change

3, Normalized tables are usually smaller, so they fit better in memory and perform better

4, The lack of redundant data means there's less need for DISTINCT or GROUP BY queries when retrieving lists of values

Drawbacks of Normalized Schema:

Any non-trivial query on a well-normalized schema will probably require at least one join, and perhaps several

This is not only expensive, but it can make some indexing strategies impossible

Advantages of Normalized Schema:

1, Everyhing is in the same table, which avoids joins. This can be much faster than a join, because it avoids random I/O

2, A single table can allow more efficient indexing strategies

In the real world, you often need to mix the approaches, possibly using a partially normalized schema, cache tables, and other techniques

The most common way to denormalized data is to duplicate or cache selected columns from one table in anotehr table, you can use triggers to udpate the cached values which makes the implementation easier

You'll often need extra idnexes, redundant fields, or even cache and summary tables to speed up read queries

Speeding Up ALTER TABLE

MySQL's ALTER TABLE performance can become a problem with very large tables, MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table

ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; // slow

ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5; // fast. This statement modifies the .frm file and leaves the table alone

Modifying Only the .frm file to convince MySQL not to rebuild the table

CREATE TABLE sakila.film_new LIKE sakila.film

ALTER TABLE sakila.film_new MODIFY COLUMN rating ENUM('G', 'PG', 'PG-13', 'R', 'NC-17', 'PG-14') DEFAULT 'G'

FLUSH TABLES WITH READ LOCK;

// swap the .frm files of table film and film_new

mv film.frm film_tmp.frm

mv film_new.frm film.frm

mv film_tmp.frm film_new.frm

UNLOCK TABLES

DROP TABLE sakila.film_new

Builing MyISAM Indexes Quickly

The usual trick for loading MyISAM tables efficiently is to disable keys, load the data, and reenable the keys:

ALTER TABLE test.load_data DISABLE KEYS

-- load the data

ALTER TABLE test.load_data ENABLE KEYS

This only works for nonunique indexes.

For unique indexes, the faster way:

1, create a table of the desied structure, without any indexes

2, load the data into the table to build the .MYD file

3, create another empty table with the desired structure, including indexes, this will create .frm and .MYI files

4, FLush the tables with a read lock

5, Rename the second tables's .frm and .MYI files, so MySQL uses them for the first table

6, Release the read lock

7, Use REPAIR TABLE to build the table's indexes

Notes on Storage Engines

MyISAM

Table locks

MyISAM tables have table-level locks, be careful this doesn't become a bottleneck

No automated data recovery

If the MySQL server crashes or power goes down, you should check and possibly repair your MyISAM tables before using them. If you have large tables, this could take hours

No transactions

MyISAM table don't support transactions

Only indexes are cached in memory

MyISAM caches only the index inside the MySQL process, in the key buffer. The operating system caches the table's data

Compact storage

Rows are stored jam-packed one after anotehr, so you get a small disk footprint and fast full table scans for on-disk data

Memory

Table locks

Memory tables have table locks but isn't usually a problem because queries on Memory tables are normally fast

No dynamic rows

Memory tables don't support dynamic(variable-length) rows, so they don't support BLOB and TEXT fields at all. Even a VARCHAR(5000) turns into a CHAR(5000) -- a huge memory waste if most values are small

Hash indexes are the default index type

No index statistics

You may get bad execution palns for some complex queries

Content is lost on restart

Memory tables don't persist any data to disk, so the data is lost when the server restarts, even though the table's definitions remain

InnoDB

Transactional

InnoDB supports transactions adn four transaction isolation levels

Foreign keys

As of MySQL 5.0, InnoDB is the only storage engine that supports foreign keys

Row-level locks

Locks are set at the row level, with no escalation and nonblocking selects--standard selects don't set any locks at all, which gives very good concurrency

Multiversioning

InnoDb uses multiversion concurrecncy control, so by default your selects may read stale data

Clustering by primary key

All InnoDB tables are clustered by the primary key, which you can use to your advantage in schema design

All indexes contain the primary key columns

Indexes refer to the rows by the primary key, so if you don't keep your primary key short, the indexes will grow very large

Optimized caching

InnoDB caches both data and memory in the buffer pool, it also automatically builds hash indexes to speed up row retrieval

Unpacked indexes

Indexes are not packed with prefix compression, so they can be much larger than for MyISAM tables

Slow data load

As of MySQL 5.0, InnoDb does not specially optimize data load operations. It builds indexes a row at a time, instead of building them by sorting. This may result in significantly slower data loads

Blocking AUTO_INCREMENT

In versions earlier than MySQL 5.1, InnoDB uses a table-level lock to generate each new AUTO_INCREMENT value

No cached COUNT(*) value

Unlick MyISAM or Memory tables, InnoDB tables don't store the number of rows in the table, which means COUNT(*) queries without a WHERE clause can't be optimized away and require full table or index scans

分享到：

2009-07-16 18:04

浏览 1262

评论