MySQL Training Summary

最新推荐文章于 2025-11-30 16:29:01 发布

原创最新推荐文章于 2025-11-30 16:29:01 发布 · 155 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

经验总结专栏收录该内容

4 篇文章

订阅专栏

本文深入探讨了数据库中不同存储引擎的特点与配置方法，包括MyISAM、InnoDB等存储引擎的区别，以及如何通过配置参数优化数据库性能。此外，文章还介绍了数据库分区技术及其应用，帮助读者更好地管理和优化数据库。

[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:


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;