MySQL Training Summary

wolong

Store Engine
In 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;