MySQL Training Summary
1860 ワード
Store Engine
In a database, different tables can use different store engines. 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.
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:
HASH Partitioning:
In a database, different tables can use different store engines.
Support: Full-text index
So MyISAM is good at big amount data query.
Support: Transaction, Row level locking
InnoDB is good at DML operations (Delete, insert, update). For most of cases, use InnoDB is better choice.
Configuration
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;