MySQL优化：摘录自手册 MySQL 5.6 Reference Manual :: 8 Optimization

 http://user.qzone.qq.com/892054726/blog/1439803064

http://dev.mysql.com/doc/refman/5.6/en/optimize-overview.html

8.1 Optimization Overview

Optimizing at the Database Level
 The most important factor in making a database application fast is its basic design:

• Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns.

• Are the right indexes in place to make queries efficient?

• Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use? In particular, the choice of a transactional storage engine such as InnoDB or a nontransactional one such as MyISAM can be very important for performance and scalability.

  Note

  In MySQL 5.5 and higher, InnoDB is the default storage engine for new tables. In practice, the advanced InnoDBperformance features mean that InnoDB tables often outperform the simpler MyISAM tables, especially for a busy database.

• Does each table use an appropriate row format? This choice also depends on the storage engine used for the table. In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB tables, and for read-only MyISAM tables.

• Does the application use an appropriate locking strategy? For example, by allowing shared access when possible so that database operations can run concurrently, and requesting exclusive access when appropriate so that critical operations get top priority. Again, the choice of storage engine is significant. The InnoDB storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code.

• Are all memory areas used for caching sized correctly? That is, large enough to hold frequently accessed data, but not so large that they overload physical memory and cause paging. The main memory areas to configure are theInnoDB buffer pool, the MyISAM key cache, and the MySQL query cache.

 

Optimizing at the Hardware Level
  The way to optimize seek time is to distribute the data onto more than one disk.

• Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10–20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.

• When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem. 

 Balancing Portability and Performance
To use performance-oriented SQL extensions in a portable MySQL program, you can wrap MySQL-specific keywords in a statement within /*! */ comment delimiters. Other SQL servers ignore the commented keywords. For information about writing comments, see Section 9.6, “Comment Syntax”. 

 8.2 Optimizing SQL Statements 

8.2.1.1 Speed of SELECT Statements

  To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.

Indexes are especially important for queries that reference different tables, using features such as joins and foreign keys. You can use the EXPLAIN statement to determine which indexes are used for a SELECT
Learn the tuning techniques, indexing techniques, and configuration parameters that are specific to the storage engine for each table. Both InnoDB and MyISAM have sets of guidelines for enabling and sustaining high performance in queries. For details, see Section 8.5.6, “Optimizing InnoDB Queries” and Section 8.6.1, “Optimizing MyISAM Queries”.



8.2.1.2 How MySQL Optimizes WHERE Clauses

• In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.

•   
  8.2.2 Optimizing DML Statements

 To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

• Connecting: (3)

• Sending query to server: (2)

• Parsing query: (2)

• Inserting row: (1 × size of row)

• Inserting indexes: (1 × number of indexes)

• Closing: (1)

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log