Mysql数据库学习（三）

痛定思痛。 2022-03-30 10:18 170阅读 0赞

# 第三部分：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 进行记录关联

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]: /images/20220330/d7332bbd38844f2ab8cfa15919582703.png