MySQL Training Summary
Store EngineIn a database, different tables can use different store engines.
[*] MyISAMNot 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.
Configuration
[*] show variables : Display static parameters
[*] show status : Display dynamic parameters
Storage
[*] 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)
Partitioning (Horizon)
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;
页:
[1]