第三部分:Mysql的高级特性
数据备份与还原
- 使用mysqkdump工具
- 直接复制mysql目录下的data文件
- 使用mysqlhotcopy工具快速备份
Mysql查询缓存
查询缓存(三级缓存)通过缓存SQL语句和查询结果,如果运行相同的SQL语句,服务器会直接从缓存中获取结果。
如何开启查询缓存(默认是关闭的) @@通常是系统变量
在/etc/my.cnf中加入 type=1是开启 type=0 是关闭
query_cache_type=1
在mysql命令中
select @@query_cache_type; #查询是否开启查询缓存
其他关于缓存的参数
have_query_cache 表示这个mysql版本是否支持查询缓存。
query_cache_limit 表示单个结果集所被允许缓存的最大值。
query_cache_min_res_unit 每个被缓存的结果集要占用的最小内存。
query_cache_size 用于查询缓存的内存大小。
关于缓存命中率的查询
在mysql命中:
#show status like '查询缓存参数';
Qcache_free_memory 查询缓存目前剩余空间大小。
Qcache_hits 查询缓存的命中次数。
Qcache_inserts 查询缓存插入的次数。
也就是说缓存的命中率为 Qcache_hits/(Qcache_hits+Qcache_inserts)
查询缓存的优化:
1.尽量使用小表而不使用大表
2.启动缓存的情况下 写入数据尽可能一次写入 重复写入会导致服务器崩溃
3.尽量不要在数据库或者表的基础上使用查询缓存 只针对一些查询语句进行缓存
a.query_cache_type=DEMAND
b. 在进行缓存的SQL语句 加上SQL_CACHE 其他加上SQL_NO_CACHE
合并表和分区表
合并表(逻辑上的概念):
a.利用主表作为查询的接口,表一表二作为存储数据的实际表单
b.只适应MyISAM 引擎
c.通过union将表合并 一定程度上回影响性能
第一步:创建子表1和子表2
DROP table IF EXISTS tb_member1;
create table tb_member1(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP table IF EXISTS tb_member2;
create table tb_member2(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
//创建tb_member2也可以用下面的语句 create table tb_member2 like tb_member1;
第二步:创建主表
DROP table IF EXISTS tb_member;
create table tb_member(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;
第三步:数据存储与查询
向表一插入数据:insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0; //这里区分表一表二
向表二插入数据:insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
查看一下主表的数据:select * from tb_member;
分区表(物理上 可以由多个物理子表组成)
水平分区表
range分区:使用 values less than
list分区:使用集合
hash分区:给被hash的值 添加一个值或者表达式 从而进行分区
线性hash分区
key分区
复合分区
ranger分区:
create table t1(
int id,
int year(10)
)
partition by range(year)
(
partition p1 values less than(10), #0-10
partition p1 values less than(20), #10-20
partition p1 values less than maxvalue#20以上
);
list分区
create table t2(
int id,
int year(10)
)
partition by list(year)
(
partition p1 values in (1,2,3)#1、2、3
partition p1 values in(4),#4
partition p1 values in(5)#5
);
垂直分区表:
a.将数据分为热数据和冷数据,热数据(账号、密码)使用一张表,冷数据(用户地址)使用另一张表。
b.使用id 进行记录关联