mysql优化

技能专题：

熟悉mysql，sqlsever，oracle数据库，熟悉redis，memcache等nosql

 

存储过程--》事务--》锁--》结构--》sql执行流程--》索引理解--》主从--》

分库分表

 

熟悉mysql，sqlserver，oracle数据库，熟悉redis，memcache，等nosql

简历讲究一个点 技能描述：细致，熟悉程度

 

熟悉mysql数据库，对于mysql结构

以及sql的执行有过深入细致的了解，能根据慢查询日志对于执行慢的sql，

进行分析，同时会根据页面+表结构+做索引

 

熟悉mysql数据库，对于mysql结构以及sql的执行有做过深入的了解，

熟悉慢查询分析，索引的结构，有搭建主从。

 

2：主从的好处

 

mysql做主从在多少数据量下考虑？

主从不能够提升mysql的sql执行速度，服务抗压能力

一定要到数据达到多少才做这个实际（和数据有点关系），项目运行，读：写，

比例

 

mysql：最大连接数

回答面试官：

mysql问题，多不要立即回答问题点

 

1：影响mysql的性能cpu i3-8 1000W select 30s - 5m

2：项目的运行比例 读：写 =》1000：20 | 20：1000 短时间内500

3：服务系统-》版本-》cpu 5000w 主从 数据库 5g

缓解服务器并发压力

 

 

主从表如何设置索引：

主库可以不设置索引，从库设置索引

 

主从复制几个模式：

sql复制模式，行模式，混合模式

 

1：主库配置

开启binlog server_id = 1

需要给以从库能操作的账号 - 创建一个账号

分配账号的权限

2：从库配置

开启binlog 设置sever_id 不能与主库相等

指定主库的信息（ip，password，host等）

 

误操作：对于数据删库，可以根据binlog恢复数据

 

binlog日志过大怎么办？

迁移备份，删除久远无用的历史记录

 

1：主库本来有数据，从库怎么同步

冷：不一定关闭服务器，而是指停止服务器写操作 mydumper

2：一致性检查

检测并解决同步

热：在服务不停止写的时候，去备份 xtracbackup

3：延迟问题

4：服务器死机

 

数据库层面：配置innodb_xx_commit sync_binlog

硬件：升级从库配置，宽带

 

 

主从mysql proxy

建立数据库集群

 

 

pt-table-sync：主从同步

 

pt-table_usage :检测可用

 

复制的方式：主从是如何复制数据？

 

1：基于sql语句的复制 - 传统型 默认

从库 - 根据主库执行的sql再执行一次

优点：灵活性较大，不要求主从版本一致

缺点：主库执行的sql需要获取系统时间 - 从库产生了延迟,使用now（）

函数时。

 

mysql5.1后

2：基于行的复制

优点：可以有效避免now（）执行的误差

缺点：对于数据库要求版本一致（尽量），不能确定执行的sql，

修改表结构不能同步

 

3：混合式复制 两者的混合

通过配置binlog_format = mixed 参数

 

对于表修改主库影响从库

 

 

 

分表查询问题：分页，关联

 

已经有个很大的数据表，很慢，并且正在使用，你怎么且不影响现在的查询

1：项目正在运行，现在的查询只能说勉强ok，问题项目正在运行，不敢去动

 

1解决：数据备份=》热备份=》通过热备份到测试服务器=》数据表结构的梳理，

根据业务选择分表规则=》因为数据量很大，普通范围查询必然很慢=》

那么可以根据规则选择对应的主键作为查询的条件=》也可以选择对于分片的字段

建立索引获取相关的主键=》主键作为条件查询导入对应分表中

 

表分好后，查询可以使用mycat=》在凌晨对项目修改

1：不能随便删除数据

2：要知道数据对于业务的重要性

3：可以做个切分

 

表分区（分片） 分区可以配合与分表一起使用

物理切分 规则range，list，hash，key

 

场景：

表非常大无法全部存在内存

维护=》批量删除

 

限制：1024可能随版本改变而改变

无法使用外键--数据库存储引擎一致

 

如果分区有主键或唯一索引的字段，那么所有主键和唯一索引就包含进来

 

range：根据范围分区，

例子：

CREATE TABLE teacher(

`id` INT,

`name` VARCHAR(20),

`class` VARCHAR(20),

age INT,

bir DATE

)PARTITION BY RANGE(age)

(

PARTITION p1 VALUES LESS THAN(20),

PARTITION p2 VALUES LESS THAN(40),

PARTITION p3 VALUES LESS THAN(60),

PARTITION p4 VALUES LESS THAN(80),

PARTITION p5 VALUES LESS THAN(MAXVALUE)

);

 

p1 => (age < 20)

p2 => (age < 40)

p3 => (age < 60)

p4 => (age < 80)

 

当插入时自动分区：

 

INSERT INTO teacher VALUES(1,'s','php',18,'2001-10-10');

INSERT INTO teacher VALUES(1,'s','php',20,'1999-10-10');

INSERT INTO teacher VALUES(1,'s','php',38,'1979-10-10');

INSERT INTO teacher VALUES(1,'s','php',48,'1969-10-10');

INSERT INTO teacher VALUES(1,'s','php',58,'1959-10-10');

INSERT INTO teacher VALUES(1,'s','php',68,'1949-10-10');

INSERT INTO teacher VALUES(1,'s','php',78,'1939-10-10');

 

 

'range' 根据给定的值去计算范围做分区 between

‘list’ 指定分区的类型值

 

CREATE TABLE teacher(

`id` INT,

`name` VARCHAR(20),

`class` VARCHAR(20),

age INT,

bir DATE

)PARTITION BY list(age)

(

PARTITION p1 VALUES in(20,40),

PARTITION p2 VALUES in(60,80),

PARTITION p3 VALUES in(90),

);

 

‘hash’ =>针对函数的value分区=》分区（规则）是mysql自动完成的

 

 

CREATE TABLE teacher(

`id` INT,

`name` VARCHAR(20),

`class` VARCHAR(20),

age INT,

bir DATE

)PARTITION BY hash(year(bir))

partition 4

 

explain select * from teacher partition (p0)

 

'key' => 把hash改为key

 

分区有点类似于分表=》他自己实现了数据的处理，项目使用中会分表分区结合

一起，可提高单表查询能力

 

分区与分表区别，分区的原理

 

 

 

 

场景：单表太大，内存不能一下存放

读写： select