[size=large][b]Store Engine[/b][/size]
In a database, different tables can use different store engines.
[list=1]
[*] MyISAM
Not Support: Transaction, Row level locking
Support: Full-text index
So MyISAM is good at big amount data query.
[*] InnoDB (Default store engine from MySQL 5.5)
Not Support: Full-text index
Support: Transaction, Row level locking
InnoDB is good at DML operations (Delete, insert, update). For most of cases, use InnoDB is better choice.
[/list]
[size=large][b]Configuration[/b][/size]
[list=1]
[*] show variables : Display static parameters
[*] show status : Display dynamic parameters
[/list]
[size=large][b]Storage[/b][/size]
[list=1]
[*] MySQL data default location is /usr/local/mysql/data. Use an other partition store data. To avoid the disk operation competition.
[*] *.myd is MyISAM data file. *.myi is MyISAM index file.
[*] Data, index and log files should not store in the root partition, since the performance reason.
[*] Store data files and index files separately. (InnoDB cannot do that)
[/list]
[size=large][b]Partitioning (Horizon)[/b][/size]
MySQL 5.5 has build-in partitioning function.
Range Partitioning:
HASH Partitioning:
In a database, different tables can use different store engines.
[list=1]
[*] MyISAM
Not Support: Transaction, Row level locking
Support: Full-text index
So MyISAM is good at big amount data query.
[*] InnoDB (Default store engine from MySQL 5.5)
Not Support: Full-text index
Support: Transaction, Row level locking
InnoDB is good at DML operations (Delete, insert, update). For most of cases, use InnoDB is better choice.
[/list]
[size=large][b]Configuration[/b][/size]
[list=1]
[*] show variables : Display static parameters
[*] show status : Display dynamic parameters
[/list]
[size=large][b]Storage[/b][/size]
[list=1]
[*] MySQL data default location is /usr/local/mysql/data. Use an other partition store data. To avoid the disk operation competition.
[*] *.myd is MyISAM data file. *.myi is MyISAM index file.
[*] Data, index and log files should not store in the root partition, since the performance reason.
[*] Store data files and index files separately. (InnoDB cannot do that)
[/list]
[size=large][b]Partitioning (Horizon)[/b][/size]
MySQL 5.5 has build-in partitioning function.
Range Partitioning:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
HASH Partitioning:
CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;