MySql 100120：MySQL Optimization 优化(2/2)

fang.zhang

  Normal
  0
  
  7.8 磅
  0
  2
  
  false
  false
  false
  
   
   
   
   
   
   
   
   
   
   
   
   
  
  MicrosoftInternetExplorer4



/* Style Definitions */
table.MsoNormalTable
        {mso-style-name:普通表格;
        mso-tstyle-rowband-size:0;
        mso-tstyle-colband-size:0;
        mso-style-noshow:yes;
        mso-style-parent:"";
        mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
        mso-para-margin:0cm;
        mso-para-margin-bottom:.0001pt;
        mso-pagination:widow-orphan;
        font-size:10.0pt;
        font-family:"Times New Roman";
        mso-ansi-language:#0400;
        mso-fareast-language:#0400;
        mso-bidi-language:#0400;}
MySql 100120：MySQL Optimization 优化(2/2)
@ http://zcatt.cublog.cn
1.   
Optimizing DB Structure
1.1 Make your Data as Small as Possible
1）Use the smallest data type possible.
2) Declare columns to be NOT NULL if
possible
3) The primary index of a table should be short
as possible.
4) Create only the indexes that you really
need.
5) If it is very likely that a string
column has a unique prefix on the first number of characters, it is better to
index only this prefix, using MySQL's support for creating an index on the
leftmost part of the column.
6)In some circumstances, it can be
beneficial to split into two a table that is scanned very often.

1.2 Column Indexes
Use of indexes on the relevant columns is
the best way to improve the performance of SELECT operations.
With col_name(N) syntax in an index
specification, you can create an index that uses only the first N characters of
a string column.
CREATE
TABLE test (blob_col BLOB, INDEX(blob_col(10)));
You can also create FULLTEXT index. Only
the MyISAM storage engine supports FULLTEXT indexes for CHAR, VARCHAR, and TEXT
columns.

1.3 Multiple-Column Indexes
MySQL can create composite indexes. An
index may consist of up to 16 columns.
A multiple-column index can be considered a
sorted array containing values that ar created by concatenating the values of
the indexed columns.
MySQL uses multiple-column indexes
in such a way that queries are fast when you specify a known quantity for the
first columnof the index in a WHERE clause, even if you do not specify values
for the other columns. For example, if you have a three-column index on (col1,
col2, col3), you have indexed search capabilities on (col1), (col1,col2), and
(col1, col2, col3).

1.4 How MySQL Uses Indexes
MySQL uses indexes for these operations:
1)     
To find the rows matching a
WHERE clause quickly.
2) To eliminate rows from consideration.
3) To retrieve rows from other tables when
performing joins. MySQL can use indexes on columns more efficiently if they are
declared as the same type and size.
4) To finde the MIN() or MAX() value for a
specific indexed column key_col.
5) To sort or group a table if the sorting
or grouping is done on a leftmost prefix of a usable key.
6) In some cases, a query can be optimized
to retrieve values without consulting the data rows.

1.5 The MyISAM Key Cache
To minimize disk I/O, the MyISAM storage
engine employs a cache mechanism to keep the most frequently accessed table
blocks in memory: for index blocks and data blocks.

To control the size of the key cache, use
the key_buffer_size system variable.

Threads can shared access key cache buffers
simultaneously.

MySQL also provides multiple key caches.
This feature enables you to assign different table indexes to different key
caches.

Manual suggests a busy server should
maintain three key caches: hot, cold,and warm.

If there are enough blocks in a key cache
to hold blocks of an entire index, or at least the blocks corresponding to its
nonleaf nodes, it makes sense to preload the key cache with index blocks before
starting to use it. To preload an index into a cache, use the LOAD INDEX INTO
CACHE statement. For example,
mysql>
LOAD INDEX INTO CACHE t1,t2 IGNORE LEAVES;

1.6 The InnoDB Buffer Pool
InnoDB maintains a buffer pool for caching
data and indexes in memory. InnoDB manages the pool as a list, using a LRU
algorithm incorporating a midpoint insertion strategy.

1.7 How MySQL Opens and Close Tables
MySQL is multi-threaded, so there may be
many clients issuing queries for a given table simultaneously. To minimize the
problem with multiple client sessions having different states on the same
table, the table is opened independently by each concurrent session.

2. Optimizing the MySQL Server
2.1 How Compiling and Linking Affects the Speed of MySQL
2.2 System Factors and Startup Parameter Tuning
2.3 Tuning Server Parameters
shell>
mysqld --verbose --help

This command produces a list of all mysqld
options and configurable system variables. These commands can also list some
parameters.

mysql>
SHOW VARIABLES;
mysql>
SHOW STATUS;
shell>
mysqladmin variables
shell>
mysqladmin extended-status























               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/8866/showart_2154726.html