mysql使用总结

之前使用mysql都是用它的一些常用功能，现在由于最近一个项目的需要，后台的mysql数据服务器基本是由我在管理，学习到了很多以前没有接触过的mysql功能，总结一下。

 

由于业务需要，数据库引擎用的是innodb，版本是5.5。mysql的基本功能我就不说了，主要说说学到的以前不是和了解的方面。

 

一.独立表空间和共享表空间

　　共享表空间是mysql的默认方式，也算是推荐方式了，数据库的所有所有数据都在一个（或几个）文件中，比较好管理，而且数据库操作的时候打开这一个文件就可以了，相对来说代价较小。但是当数据的量级达到G后，一个很大的数据文件并不利于管理，读写都需要耗费巨大的资源。

　　独立表空间每个表都有自己独立的多个数据文件，做到了索引和数据分离，数据拷贝的时候能够更加的灵活。但是这个模式下，当表的数量很多的时候，每次数据库操作可能涉及多个文件，而且这种数据文件的利用率不是很高。

　　由于我们的数据量非常的大，而且表之间的联合查询不算太多，所以选择了独立表空间。下面讲讲共享表空间向独立表空间转换并保留已有数据的方法：

1) 查看数据库是共享表空间还是独立表空间

show variables like "%per_table%";

　　如果 innodb_file_per_table 是OFF则是共享表空间，是ON代表独立表空间。或者查看硬盘上的数据文件是否是每个表都对应自己的数据。

2) 备份需要转移的数据

mysqldump -uxxx -pxxx database_name > xxx.sql

3) 停掉mysql服务

/etc/init.d/mysqld stop

4) 修改mysql配置文件my.cnf(通常在/etc/my.cnf或者/etc/mysql/my.cnf)

　　在[mysqld]下面加上一行：

innodb-file-per-table=1

5) 删除需要转移的数据库以前的数据以及日志

cd /var/lib/mysql            //数据库文件位置，以实际情况为主，供参考

rm -f ib*                        //删除日志和空间

rm -rf database_name  //删除旧的数据库文件 

6) 重新启动mysql

/etc/init.d/mysqld start

7) 导入之前备份的需要转移的数据库文件

mysql -uxxx -pxxx < xxx.sql

8) 查看结果

　　可以看到现在innodb_file_per_table=on了，而且每个表都对应自己的数据文件了。

 

二.mysql表分区

 mysql从5.1版本开始支持表分区，说说分区的一些优点：

1）与单个磁盘或者文件系统分区相比，可以存储更多的数据。

2）可以单独删除分区将一些特别的巨鹿删除，增加也容易。

3）优化查询，因为数据已经分区了，查询的时候只用在特定的分区里面找就行。

4）SUM()和COUNT()这样的聚合查询也很容易并行处理，在每个分区上单独进行，然后汇总。

5） 多磁盘分散数据，获得更高的吞吐率

支持的分区类型：

1）RANGE分区：基于属于一个给定连续区间的列值分配

PARTITION BY RANGE(XXX) (
    PARTITION P0 VALUES LESS THAN (XXX),    
    PARTITION P1 VALUES LESS THAN (XXX), 
    PARTITION P2 VALUES LESS THAH MAXVALUE 
)

 

2）LIST分区： 类似于RANGE， 区别在于LIST每个分区的定义是基于某列的值从属于一个集合，RANGE是一个连续的区间

PARTITION BY LIST(XXX) (
    PARTITION P0 VALUES IN (1，3，5，7，9),
    PARTITION P1 VALUES IN (2，4，6，8，10)           
)

3) HASH分区： 基于用户定义的表达式的返回值选择分区

PARTITION BY HASH(XXX)
PARTITION 4;

4)KEY分区： 按key的值进行分区选择，类似HASH，HASH用用户自定义的表达式，KEY用MYSQL提供的

PARTITION BY KEY(XXX)
PARTITION 4;

在项目中我用的是RANGE分区，分区之后的每个表的每个分区会对应一个数据文件。唯一觉得不足的地方是分区的改变一定要人为的操作，有一种需求是随着数据的增多，分区的数量自动按一定间隔增长。不知道是我自己没有找到方法还是mysql不支持。当然写一个程序式也可以解决，但是要定期查询数据库，这样又额外增加了数据库的负担。

 

三.慢查询

数据库是很容易产生瓶颈的地方，由于项目数据量很大，对mysql的读写性能要很有要求，对于经常查询的内容一般都会建立缩阴，因为不是在线分析数据，所以插入稍慢一点也可以接受。有时还是会发现数据库操作变得很慢，但是也不知道是哪条语句造成的，这样mysql的慢查询功能就能发挥作用。

首先查询数据库是否打开了慢查询功能：

show variables like "%slow%";

如果log_slow_queries 和slow_query_log 都是OFF的话就没有打开，可以通过set global slow_query_log='ON';语句打开慢查询或者通过修改my.cnf配置文件，在[mysqld]下面加上

long_query_time = 2  
log-slow-queries = /usr/local/mysql/mysql-slow.log 

然后重启数据库服务。

这样我们就能够查看slow query的日志文件知道是什么sql语句导致了数据库的操作缓慢。可以简单的通过自己查看或者mysql自带的mysqldumpslow工具来进行分析。

 

以上三点是暂时想到的新接触的mysql功能，以后想到再做记录。