学习笔记_mysql(三)

MySQL优化（三）

1. MySQL内置缓存
   1.1 什么是MySQL缓存
   MySQL自己提供缓存技术，能够保存执行过的SQL语句的结果。
   1.2 设置MySQL缓存
   

打开缓存，以上两个变量都要配置：

修改配置文件：

第一次执行SQL：

第二次执行SQL：

1.3 缓存失效： 数据表内数据发生变化
修改过数据表之后，缓存会失效。

修改数据之后，重新执行SQL：

1.4清空缓存
reset query cache;


1.5设置不缓存表
sql_no_cache, 加入到SQL语句当中

Select sql_no_cache * from emp where ename like ‘%ec%’;


1.6 查看缓存空间使用情况


1.7注意事项：
SQL语句中空格会影响SQL缓存。

Select * from emp limit 0,30; //添加了缓存
Select * from emp limit 0,30; // 因为select和* 之间有了两个空格，那么就不会从缓存中取数据。

2. 分区（分表）
   2.1 为什么要分区？
   文件尺寸越大访问效率就会越低。
   将大表拆分为若干个小表，能够提高SQL效率。
   2.2 分区的方式（逻辑/水平分表）
   逻辑分表：使用MySQL为我们提供内置分表方法。
   水平分表：横向分表，例如：有一个1亿条数据的表，拆分为10张表，每张1000W条数据
   求余(主键字段)：
   key 利用某个字段的值，进行求余，完成分区
   hash 利用某个整型表达式的值，完成分区
   条件：
   range 值在某个范围内的分区
   list 值在指定列表内的分区
   2.3 key分区
   
   
   2.4 hash分区

分区SQL语句几乎一致，只是在分区方式上有所区别。

2.5 range分区

2.6 list分区

注意：使用逻辑分表时，增删改查的操作没有发生改变。

2.7 扩展分区（对现有表增加分区）
Key/hash方式： alter table 表名 add partition partitions 数量;

Range方式：

注意：不能再增加80hou之前的分表了。

2.8 删除分区
Key/hash方式： 删除分区，被删除分区当中数据会自动迁移到其他的分表当中。

User1表：

执行删除分区操作：alter table 表名 coalesce partition 数量;

Alter table user1 coalesce partition 2;

删除分区之后数据依然存在

Range/list方式：删除分区时，会丢失原来保存的数据
alter table 表名 drop partition 分区名称;

2.9 其他分表方式（垂直分表和物理分表）
2.9.1垂直分表
按字段来拆分数据表：

将tp_user表拆分：
id username password role_id email
tp_user_ext表（用户信息扩展表）:
id nickname truename dept_id sex birthday tel address addtime

手动完成。

2.9.2物理分表

手动分表，水平分表方式。

Create table user0(
Uid int priamry key,
Uname varchar(20) not null unique
);
Create table user1(
Uid int priamry key,
Uname varchar(20) not null unique
);
Create table user2(
Uid int priamry key,
Uname varchar(20) not null unique
);

问题： 如何向这三个表中保存数据？

查询： 根据$id和进行求余确定要查询的数据表。

插入操作： 创建一个tmp表，只有id字段，记录最大的id值。

1)从tmp表中取得当前的最大id
2)对id自增1，就是下一条数据的id值
3)对id求取确定插入哪个数据表
4)执行插入SQL
5)更新tmp将最新的id覆盖