08mysql 优化 - 第二天 - 1

最新推荐文章于 2021-01-31 03:22:51 发布

PandaMirs

最新推荐文章于 2021-01-31 03:22:51 发布

阅读量230

点赞数

本文链接：https://blog.csdn.net/qq_34608447/article/details/104385203

版权

一、查询缓存

1、具体使用

什么是查询缓存？

mysql服务器提供的，用于缓存select语句结果的一种内部内存缓存系统。

如果开启了查询缓存，将所有的查询结果，都缓存起来，使用同样的select语句，再次查询时，直接返回缓存的结果即可

查看缓存设置情况，并给缓存空间设置大小：

show variables  like 'query_cache%';   //查看缓存使用情况

query_cache_size:缓存空间大小

query_cache_type:是否有开启缓存

如何开启查询缓存，并设置缓存空间大小？

在my.ini中对上边的两个变量进行配置：

query_cache_size=134217728

query_cache_type=1

配置完成，之后需要重启mysql，

查看缓存开启成功：show variables like ‘query_cache%’;

sql语句第一次执行没有缓存，之后就有缓存了：

2、无缓存

（1）缓存失效

数据表的数据(数据有修改)有变化或者数据表结构(字段的增、减)有变化，则会清空全部的缓存数据，即缓存失效。

update emp set job=’123456’ where empno=123456;

上图，执行了一个update语句，导致之前存在缓存(empno=1234567)被清空了

（2）不使用缓存

sql语句有变化表达式，则不会生成/使用缓存。

例如有时间信息、随机数等

select ename,job,now() from emp where empno=123456;

上图，在sql语句中有“时间”变化的表达式，则不使用缓存

select * from emp order by rand() limit 4;

上图，sql语句中有“随机数”的表达式，不给使用缓存

（3）生成多个缓存

生成缓存的sql语句对“空格”、“大小写”比较敏感

相同结果的sql语句，由于空格、大小写问题就会分别生成多个缓存。

注意：相同结果的sql语句，由于大小写问题会分别生成缓存：

（4）禁用缓存

sql_no_cache 不进行缓存

select sql_no_cache * from emp where empno=123456;

意思是当前查询结果不使用查询缓存；

3、查看缓存空间使用情况

如下图，再次使用一个缓存，并读取一次，发现缓存相关参数有变化：

二．分区技术

1、分区介绍

基本概念，把一个表，从逻辑上分成多个区域，便于存储数据。

采用分区的前提，数据量非常大。

如果数据表的记录非常多，比如达到上亿条，数据表的活性就大大降低，数据表的运行速度就比较慢、效率低下，影响mysql数据库的整体性能，就可以采用分区解决，分区是mysql本身就支持的技术。

查看当前mysql软件是否支持分区；

show variables like '%partition%';

以上的结构，在创建（修改）表时，可以指定表，可以被分成几个区域。

利用表选项：partition 完成。

create table table_name(

字段信息，

索引，

)engine myisam charser utf8

partition by 分区算法（分区字段）（

分区选项

）；

分区算法：

条件分区：list (列表) range(范围) 取模轮询（hash,key）

2、分区算法

（1）list分区

list :条件值为一个数据列表。

通过预定义的列表的值来对数据进行分割

例子：假如你创建一个如下的一个表，该表保存有全国20家分公司的职员记录，这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域，如下表所示：

职员表：emp

id  name   store_id(分公司的id)

12   小宝     1

14   二宝     6

北部    1,4,5,6,17,18

南部    2,7,9,10,11,13

东部    3,12,19,20

西部    8,14,15,16



insert into emp values(12,’xiaobao’,14)

insert into emp values(15,’二bao’,17)





create table p_list(

    id int,

    name varchar(32),

    store_id int

)engine myisam charset utf8

partition by list (store_id)(

    partition p_north values in (1,4,5,6,17,18),

    partition p_east values in(2,7,9,10,11,13),

    partition p_south values in(3,12,19,20),

    partition p_west values in(8,14,15,16)

);

创建分区表后查看文件，

添加几条数据，测试是否用到了分区：

explain partitions select * from p_list where store_id=20\G

注意：在使用分区时，where后面的字段必须是分区字段，才能使用到分区。

如下查询，没有分区条件，则会到所有的分区里面去查找，即便如此，查询效率也要比单表查询高。

（2）Range（范围）

这种模式允许将数据划分不同范围。例如可以将一个表通过月份划分成若干个分区

create table p_range(

    id int,

    name varchar(32),

    birthday date

)engine myisam charset utf8

partition by range (month(birthday))(

    partition p_1 values less than (4),

    partition p_2 values less than(7),

    partition p_3 values less than(10),

    partition p_4 values less than MAXVALUE

);

less than   小于;

MAXVALUE 可能的最大值

insert into p_range values(1,’xiaobao’,’2016-09-09’);

insert into p_range values(1,’xiaobao’,’2016-11-09’);

插入的数据如下：

分区的效果如下：

（3）Hash（哈希）

这种模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

create table p_hash(

id int,

name varchar(20),

birthday date

)engine myisam charset utf8

partition by hash(month(birthday)) partitions 5;

分区效果如下：

（4）Key（键值）

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

create table p_key(

id int,

name varchar(32),

birthday date

)engine myisam charset utf8

partition by key (id) partitions 5;

3、分区管理

具体就是对已经存在的分区进行增加、减少操作。

（1）删除分区

删除分区：

① 在key/hash领域不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去)

② 在range/list领域会造成数据丢失

求余方式(key/hash):

>alter table 表名 coalesce partition 数量;

范围方式(range/list):

>alter table 表名 drop partition 分区名称;

1）删除hash类型分区

删除分区之前，数据如下

执行删除分区的操作：alter table p_hash coalesce partition 4

上图，把5个分表中的4个都删除，只剩下一个

剩余一个分表效果：

并且，数据没有减少：

剩余唯一一个分区的时候，就禁止删除了，但是可以drop掉整个数据表，如下图：

alter table p_hash coalesce partition 1;

2）删除list类型分表(数据有对应丢失)

alter table p_list drop partition p_north;

（2）增加分区

求余方式： key/hash

> alter table 表名 add partition partitions 数量;

范围方式： range/list

> alter table 表名 add partition(

partition 名称 values less than (常量)

或

partition 名称 values in (n,n,n)

);

1) 给p_hash 增加hash分表

alter table p_hash add partition partitions 6;

增加后，一共有7个分表体现：

上图，分表增加好后，又把数据平均地分配给各个分表存储。

4、特别注意；

create table p_range2(

    id int primary key auto_increment,

    name varchar(32),

    birthday date

)engine myisam charset utf8

partition by range (month(birthday))(

    partition p_1 values less than (4),

    partition p_2 values less than(7),

    partition p_3 values less than(10),

    partition p_4 values less than MAXVALUE

);

注意：创建分区的字段必须是主键或唯一索引的一部分

primary key(id,birthday)

不等价于如下量行代码；

primary key(id)

primary key(birthday)

create table p_range2(

        id  int auto_increment,

        name varchar(32),

        birthday date,

        primary key(id,birthday)

)engine myisam charset utf8

partition by range (month(birthday))(

        partition p_1 values less than (4),

        partition p_2 values less than (7),

        partition p_3 values less than(10),

        partition p_4 values less than MAXVALUE

);