搞定mysql优化，十分钟就够了

缓存设置

有的被频繁执行的sql语句，比较消耗时间、消耗系统资源(没的优化可做)

并且每次获得数据还不太发生变化

那么就把这个sql语句获得信息给缓存起来，供后续执行使用

这样非常节省系统资源。

1. 具体使用

2. 缓存失效

数据表的数据有变化 或者 数据表结构有变化，则缓存失效。

3. 什么情况不使用缓存

sql语句每次获得数据有变化。

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

4. 生成多个缓存

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

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

5. 不使用缓存

sql_no_cache 不进行缓存

6. 查看缓存空间使用情况

分表设计

一个数据表里边存储的记录信息太多了，记录的条数达到1-2亿条信息。

这是该数据表的活性就大大降低，数据表的运行速度就比较慢、效率低下，影响mysql数据库的整体性能。

现在设置一个水平分割，把许多的记录信息分别存储到不同的数据表里边，这样每个表存储的记录就比较少，该表的活性大大提高。

分表设计的两种模式：

① 逻辑方式分表

mysql数据库本身就有分表技术，该方式的分表可以节省php的逻辑处理。

② 物理方式分表

自己手动创建多个数据表出来

php程序需要考虑分表算法：数据往哪个表写，从哪个表读

1. 演示逻辑分表设计

mysql本身就支持的分表技术

以上逻辑分表设计，在php程序里边无需设置额外算法代码，还像以往一样直接对goods数据表进行操作即可，mysql本身会考虑分表的算法。

2. 四种格式的逻辑分表

具体为：key hash ---->[求余方式]

range list ---->[范围方式]

注意：分表字段必须是主键 或 主键的一部分

2.1 key分表

partition by key(条件字段id) partitions 10;

2.2 hash分表

根据表达式/字段方式进行分表设计

partition by hash(表达式/字段) partitions 数量;

2.3 range分表

根据 字段/表达式 是否满足某个范围条件进行分表设计

partition by range(year(pubdate))(

partition hou70 values less than (1980),

partition hou80 values less than (1990),

partition hou90 values less than (2000),

partition hou00 values less than (2010)

)

2.4 list分表

根据 表达式/字段 的内容值是否在某个"列表"中进行分表设计。

partition by list(month(pubdate))(

partition spring values in (3,4,5),

partition summer values in (6,7,8),

partition autumn values in (9,10,11),

partition winter values in (12,1,2)

)

3. 分表管理

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

增加分表 不会引起数据丢失。

减少分表 在range/list领域会造成数据丢失

在key/hash领域不会造成数据丢失，

这两个领会在进行减少分表的同时就把全部数据重新整合到存在的表中，

key/hash两种分表与业务逻辑关联不紧密。

3.1 增加分表

求余方式： key/hash

> alter table 表名 add partitions 数量;

范围方式： range/list

> alter table 表名 add partition(

partition 名称 values less than (常量)

或

partition 名称 in (n,n,n)

);

3.2 删除分表

求余方式(key/hash):

>alter table 表名 coalesce partition 12;

删除分表不会造成数据丢失，每次分表数据都重新整合到存在的分表里边。

范围方式(range/list):

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

删除分表会造成数据丢失

① key/hash方式不会丢失数据

② range/list会丢失数据

给range分表写4条记录：

把"hou80"的分区删除,从下图查询结果看对应分表的数据也丢失了。

4. 物理分表设计

以上goods的物理分表需要增加php的算法逻辑：

需要计算记录从哪个表读、给哪个表写。

4.1 php对物理分表的操作

5. 垂直分表

水平分表：是把一个表的全部记录信息分别存储到不同的分表之中。

垂直分表：是把一个表的全部字段分别存储到不同的表里边。

有的时候，一个数据表设计好了，里边有许多字段，但是这些字段有的是经常使用的，有的是不常用的。

例如，一个数据表有20个字段，其中10个字段是常用的，后10个字段是不常用的

那么在操作常用字段的时候，就不得不给其他不常用的字段也分配一定的资源进行操作。

数据表：

会员表： user_id 登录名 密码 邮箱 手机号码 身高 体重 性别 家庭地址 身份证号码

以上表，红色是常用的，蓝色的是不常用的

为了使得常用字段运行速度更快、效率更高，把常用字段给跳出来,因此数据表做以下设计：

会员表(主)字段：user_id 登录名 密码 邮箱 手机号码

会员表(辅)字段：user_id 身高 体重 性别 家庭地址 身份证号码

以上把会员表根据字段是否常用给分为两个表的过程就是垂直分表。

架构设计

架构设计也称为集群设计：由多台mysql服务器共同支撑网站的运行，每台服务器分担的工作就比较少，运行速度快、效率高。

mysql数据库在运行的时候一般查询/写入的sql语句比例为：7/1

并且查询消耗的资源比写入要更多。

因此可以设计一个"主从模式"的集群，与之前redis的主从模式使用模式一直。

维护备份的集群架构：

主从模式的集群架构：

慢查询日志收集

我们要把系统里边一些执行速度非常慢的sql语句给收起起来，并做分析优化，使得其执行速度加快。

总结：

1. 水平分表设计

分的是数据记录

a) 逻辑分表

求余：key/hash 范围：range/list

创建/增加分表：新建表时、分表创建完毕还可以增加

删除分表：key/hash不会造成数据丢失

range/list能造成数据丢失

b) 物理分表

php代码需要增加算法逻辑

2. 垂直分表

分的是表的字段

3. 架构设计

架构集群有两种方式：

① 互为备份

② 主从模式[常用]