分类： Mysql/postgreSQL
2010-01-20 13:49:43
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.
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.
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) To find the rows matching a WHERE clause quickly.
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.
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;
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.
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.
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
