1、mysql复制原理及流程

1.数据库有个bin-log二进制文件，记录了所有sql语句。
2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。
3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可
流程：
1.binlog dump线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。#在slave上设置CHANGE MASTER TO MASTER_DELAY = N 来实现延迟复制。

3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

2、mysql 一致性延迟时，数据如何恢复

问题处理方案：
1、主要目标总则：确保业务不中断，缩短业务影响时间
2、处理思路：
  对于读写分离的场景，在不知道具体延迟原因的情况下，为了快速恢复业务，可以切花从库，将业务恢复正常，后再具体分析处理延迟库。
  对于读写不分离的场景，从库复制延迟对业务没有影响，其实延不延迟无关紧要，只要能保证数据完整备份至从库即可。
3、避免出现数据延迟甚至数据不一致的情况，可以使用数据一执行的工具percona-toolkit。通过脚本的方式，定时进行数据一致性检查主从延迟监控并做一致性修复。

主从数据灾难恢复：
#最小数据丢失预防方案
 - 定期数据库全备并同步到备份服务器，并通过同步工具（rsync）每分钟同步binlog到备份服务器。这样可以做到数据丢失控制在一分钟之内。
#恢复方案
 - 如果不是机器损坏，直接通过全备+binlog在恢复
 - 如果机器正常，只是mysql服务挂掉
    - 主服务器挂掉，binlog停止更新，直接重启数据库即可，不需要做数据恢复
    - 从服务器挂掉，找出挂掉时间点，通过binlog日志进行恢复到从库，再建立主从。

https://www.jianshu.com/p/132ba602f58e
工具：percona-toolkit
    1. pt-table-checksum 负责检测MySQL主从数据一致性
    2. pt-table-sync 负责当从数据不一致时修复数据，让他们保存数据的一致性
    3. pt-heartbeat 负责监控MySQL主从同步延迟

1.在 MySQL5.5 以及之前， slave 的 SQL 线程执行的 relay log 的位置只能保存在文件（ relay-log.info）里面，并且该文件默认每执行 10000 次事务做一次同步到磁盘， 这意味着 slave 意外 crash 重启时， SQL 线程执行到的位置和数据库的数据是不一致的，将导致复制报错，如果不重搭复制，则有可能会
导致数据不一致。 MySQL 5.6 引入参数 relay_log_info_repository，将该参数设置为 TABLE 时， MySQL 将 SQL 线程执行到的位置存到mysql.slave_relay_log_info 表，这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务，这样 slave 意外宕机后， slave 通过 innodb 的崩溃
恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。
2. MySQL 5.6 引入 GTID 复制，每个 GTID 对应的事务在每个实例上面最多执行一次， 这极大地提高了复制的数据一致性；
3. MySQL 5.5 引入半同步复制， 用户安装半同步复制插件并且开启参数后，设置超时时间，可保证在超时时间内如果 binlog 不传到 slave 上面，那么用户提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前用户线程提交时在 master 上面等待的时候，事务已经提交，该事务对 master
上面的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上面该事务又不可见了，该问题直到 5.7 才解决；
4. MySQL 5.7 引入无损半同步复制，引入参 rpl_semi_sync_master_wait_point，该参数默认为 after_sync，指的是在切成半同步之前，事务不提交，而是接收到 slave 的 ACK 确认之后才提交该事务，从此，复制真正可以做到无损的了。
5.可以再说一下 5.7 的无损复制情况下， master 意外宕机，重启后发现有 binlog没传到 slave 上面，这部分 binlog 怎么办？？？分 2 种情况讨论， 1 宕机时已经切成异步了， 2 是宕机时还没切成异步？？？ 这个怎么判断宕机时有没有切成异步呢？？？ 分别怎么处理？？？

3、工作中mysql 一些bug的解决

https://blog.csdn.net/qq_38056704/article/details/80730537
1.MySQL 中的mysql>变成了‘>怎么办？
答：再输入一个 ’号，然后；。

2、如何删除带有外键的数据表？
答：SET foreign_key_checks=0;
delete from 需要删除的表名
SET foreign_key_checks=1;

3、忘记root密码
4、恢复是提示ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is 
        empty.
5、建立主从时，IO Connection
6、

4、mysql中myisam与innodb的区别

MyISAM
Heap
Merge
INNODB
ISAM

存储结构，存储空间，事务支持，锁机制，外键，全文索引
1、存储结构
 MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD(MYD)。索引文件的扩展名是.MYI(MYIndex)。
 InnoDB:所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB，默认存放在ibdata1文件中
 
2. 存储空间
     MyISAM:可被压缩，存储空间较小。支持三种不同的存储格式：静态表（默认，但是注意数据末尾不能有空格，会被去掉）、动态表、压缩表。
     InnoDB:需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
 
3. 事物支持
    MyISAM:强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提供事物支持。
    InnoDB:提供事务支持，外部键等高级数据库功能。具有事务（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）的事务安全（transaction-safe ACID compliant）型表

4、锁机制
    InnoDB支持行级锁，而MyISAM支持表级锁

5、外键：
    InnoDB支持外键，MyISAM不支持
    
6、InnoDB不支持全文索引，而MyISAM支持


innodb引擎的4大特性
1.插入缓冲（insert buffer)
2.二次写(double write)
3.自适应哈希索引(ahi)
4.预读(read ahead)

各种不同 mysql 版本的Innodb的改进
MySQL5.6 下 Innodb 引擎的主要改进：
（ 1） online DDL
（ 2） memcached NoSQL 接口
（ 3） transportable tablespace（ alter table discard/import tablespace）
（ 4） MySQL 正常关闭时，可以 dump 出 buffer pool 的（ space， page_no），重启时 reload，加快预热速度
（ 5） 索引和表的统计信息持久化到 mysql.innodb_table_stats 和mysql.innodb_index_stats，可提供稳定的执行计划
（ 6） Compressed row format 支持压缩表

MySQL 5.7 innodb 引擎主要改进
（ 1） 修改 varchar 字段长度有时可以使用 online DDL
（ 2） Buffer pool 支持在线改变大小
（ 3） Buffer pool 支持导出部分比例
（ 4） 支持新建 innodb tablespace，并可以在其中创建多张表
（ 5） 磁盘临时表采用 innodb 存储，并且存储在 innodb temp tablespace 里面，以前是 myisam 存储
（ 6） 透明表空间压缩功能

5、mysql 各主要版本之间的差异

https://blog.csdn.net/xiongping_/article/details/45478073

各版本的常用命令差异：
5.1之前：show innodb status
5.5:之后：show engines innodb status

MySQL 4.1/5.0/5.1/5.5/5.6各版本的主要区别
　mysql-server-4.1增加了子查询的支持，字符集增加UTF-8，GROUP BY语句增加了ROLLUP，mysql.user表采用了更好的加密算法,innodb开始支持单独的表空间
　

6、mysql中varchar与char的区别以及varchar(50)中的50代表的涵义

问题一：区别
1、定长和变长
 char表示定长、长度固定，varchanr表示变长，即长度可变
 即char类型是规定多少字长则必须存储多少字长，超过的长度的字段则只能截取出对应的长度进行存储，相对于要求字长长度不够的字段则用空格补齐。
而varchar类型则是只要在规定字长之内，有多少存多少，无需补齐；超出的部分和char一样，舍去即可

2、存储容量不同
对于char类型来说，最多只能存放的字符个数为255，和编码无关。
varchar最多能存放65532个字符。VARCHAR的最大有效长度由最大行大小和使用的字符集来确定。整体最大长度是65，532字节

问题二：varchar(50)中50的含义
 表示字符数，表示最大存储50个字符，varchar(5)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)
 
 
问题三：int（20）中20的涵义，
20表示最大显示宽度为20，但仍占4字节存储，存储范围不变。

问题四、为什么这样设计

7、innodb的事务与日志的实现方式

https://blog.csdn.net/qq_41946557/article/details/104011119
事务：ACID
A:Atomicity原子性
C:Consistency一致性
I:Isolation隔离性
D:Durability持久性

1、有多少种日志
    错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
    查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
    慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
    二进制日志：记录对数据库执行更改的所有操作。
    中继日志：中继日志也是二进制日志，用来给slave 库恢复
    事务日志：重做日志redo和回滚日志undo
 
redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata*文件里面， 5.6 之后，可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata*之外。

2、事务隔离级别
    读未提交(RU)：最低级别，任何情况都无法保证
    读已提交(RC)：可避免脏读的发生
    可重复读(RR)：可避免脏读、不可重复读的发生
    串行：可避免脏读、不可重复读、幻读的发生

3、事务是如何通过日志来实现
    事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号;
    当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；
    当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”
    
4、binglog 的集中日志录入格式及区别
    Statement：每一条会修改数据的sql都会记录在binlog中。
    Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
    Mixed：以上两种level的混合使用。

8、mysql数据库cpu飙升到500%的话他怎么处理

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，
看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，
有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

9、数据库优化思路，索引生效规则

硬件
linux 系统层面
数据库配置，表结构
sql，索引

1、硬件
2、linux 系统层面
 内核参数优化
 
3、数据库配置，表结构
选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
事务处理
锁定表、优化事务处理
适用外键，优化锁定表
建立索引
优化查询语句

1）配置优化
2）表结构优化


4、sql优化
(1)、explain出来的各种item的意义；
select_type 
表示查询中每个select子句的类型
type
表示MySQL在表中找到所需行的方式，又称“访问类型”
possible_keys 
指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 
Extra
包含不适合在其他列中显示但十分重要的额外信息

(2)、profile的意义以及使用场景；set global profiling = 1;
show profiles;
show profile for query 7;
查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等

优化方法：
（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。
（3） 避免在索引列上使用计算
（4）避免在索引列上使用IS NULL和IS NOT NULL
（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
https://www.cnblogs.com/zhengdongdong/p/8086094.html



------------------------------------------------
2）索引生效规则
 * 以下情况无法使用索引
   - 以“%”开头的LIKE语句，模糊匹配
   - OR语句前后没有同时使用索引
   - 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
(0)    select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2)    select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

   
 

10、备份计划，mysqldump以及xtranbackup的实现原理

1、备份计划
视库的大小决定，一般100G内的库，可以使用mysqldump来备份，因为mysqldump更加轻巧灵活，备份时间选择业务低峰期，可以每天进行全量备份
100G以上的库，可以使用xtranbackup备份速度要比mysqldump要快。一般选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期

2、原理
mysqldump 属于逻辑备份，xtranbackup属于物理备份，直接拷贝表空间文件，同事不断扫描产生的redo日志并保存下来，最后完成innodb的备份后，会做一个flush engine logs 操作，确保所有的redo log 都已经落盘。

3、备份时间
20G 2分钟（mysqldump）
80G 30分钟（mysqldump）
111G 30分钟(mysqldump)
288G 3小时（xtra）
3T 4小时（xtra）

4、mysql 备份恢复失败如何处理
首先在恢复之前就做好充足的准备工作，避免恢复的时候出错。比如说备份之前就有效性检查，权限检查，空间检查等。如果有报错，再跟进报错提示来进行相应的调整。


如何从mysqldump产生的全库备份中只恢复某一个库、某一张表
--one-database 

11、500台db,如何在最快时间内重启

dsh 分布式shell，在多台机器上运行linux命令
ansible

12、innodb的读写参数优化

(1)、读取参数
global buffer pool以及 local buffer；

(2)、写入参数；
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

(3)、与IO相关的参数；
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

(4)、缓存参数以及缓存的适用场景。
query cache/query_cache_type
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。
第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。
第三个：小网站或者没有高并发的无所谓，高并发下，会看到 很多 qcache 锁 等待，所以一般高并发下，不建议打开query cache

13、mysql慢查询监控

使用工具：zabbix

监控方法：

Com_update：mysql执行的更新个数

Com_select：mysql执行的查询个数

Com_insert：mysql执行插入的个数

Com_delete：执行删除的个数

Com_rollback：执行回滚的操作个数

Bytes_received:接受的字节数

Bytes_sent：发送的字节数

Slow_queries：慢查询语句的个数

监控脚本参考：https://www.cnblogs.com/shenjianyu/p/6627843.html

14、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做

工具：pt-table-checksum，checksum,mysqldiff

怎么做：
1、编写shell脚本来检查同步数据
2、通过cratab 做定时任务，每日进行一致性校验

15、你们数据库是否支持emoji表情，如果不支持，如何操作？

如果是utf8字符集的话，需要升级至utf8_mb4方可支持

16、如何维护数据库的数据字典的

直接在生产库COMMENT进行注释，利用工具导出成excel方便流通
https://www.cnblogs.com/ooo0/p/12254827.html
CREATE TABLE `tb_area` (
  `area_id` int(2) NOT NULL AUTO_INCREMENT COMMENT '地点id',
  `area_name` varchar(200) DEFAULT NULL COMMENT '地点name',
  `priority` int(2) DEFAULT '0' COMMENT '优先级',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `last_edit_time` datetime DEFAULT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `UK_AREA` (`area_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='地区表';
什么是数据字典：
  是指对数据的数据项，数据结构，数据流，数据存储，处理逻辑，外部实体进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明，使用数据字典为简单的建模项目。
  select TABLE_SCHEMA,TABLE_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='test'

17、表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问

1、选择拆分成子表，还是继续放一起
拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的

InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

19、MHA原理

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA
Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA
Node运行在每台MySQL服务器上，MHA
Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL
5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

manager做了几件事情
1、监控主和从

2、主宕机之后，manager会做几件事情

找到新的主
如果指定候选，直接使用候选作为新的主
如果没有指定候选，或者指定了多个候选，选择延迟最小的作为主
从旧的主保存binlog
应用binlog到新的主
在新的主上启动新的地址，也就是地址的漂移

19、mysql基本架构

image.png

• 服务层：经典的C/S架构,主要是处理连接和安全验证。
• 核心层：处理MySQL核心业务。
• 查询分析，优化，缓存和内置函数 。
• 内建的视图，存储过程，触发器。
• 存储引擎层：存储引擎负责数据的存储和提取。核心层通过存储引擎的 API 与存储引擎通信,来遮蔽不同存储引擎的差异 , 使得差异对上层透明化。

20、sql 调优必备参数优化

#连接数、会话数和线程数
max_connections     
查看：show variables like "max_connections";
修改：set global max_connections = 200;

# 数据包和缓存
max_allowed_packet：限制server允许通信的最大数据包大小，有时候可能因为这个参数设置过小，比较大的insert或者update操作会失败，所以参数应该设置大一些
key_buffer_size：关键词缓冲区大小，缓存MyISAM索引块 ，决定索引处理速度，读取索引处理。

thread_cache_size：此参数用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求

sort_buffer_size：每个连接需要使用 buffer 时分配的内存大小，不是越大越好

query_cache_size：查询缓存大小，再查询时返回缓存，缓存期间表必须没有被更改，否则缓存失效，多写入操作的话设置大了会影响写入效率。

query_cache_size:查询缓存大小，再查询时返回缓存，缓存期间表必须没有被更改，否则缓存失效，多写入操作的话设置大了会影响写入效率。

innodb_buffer_pool_size:InnoDB 使用缓存保存索引，保存原始数据的缓存大小，可以有效减少读取数据所需的磁盘IO。

# 日志和事务相关
innodb_logfile_size:数据日志文件大小，大的值可以提高性能，但增加了恢复故障数据库的时间

innodb_log_buffer_size:日志文件缓存

innodb_flush_logattrx_commit:
 
innodb_lock_wait_timeout

21、mysql一主多从，主库宕机，如何切换到从库

1）登陆所有从库查看post信息，使用POST最大的做为新的主库，然后将从为提升为新的主库，登陆从库（新的主库）执行stop slave，(mysqlbinlog查看relay-log)

2）修改my.cnf配置文件，开启log-bin并重新启动数据库服务，登陆数据库执行restet master ,show master status\G;查看主库信息，最后创建授权同步用户与权限和网站使用数据库的用户与权限，同步所有机器的/etc/hosts文件（这时就体现了之前全网用域名则不是用IP的作用了，不然还得修改网站程序切换到新主库服务器IP上，否则无法连接到数据库）；

3）登陆其它从库，执行change master操作，查看同步状态。

22、Mysql中有哪几种锁

1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

3.页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

https://blog.csdn.net/bingdianone/article/details/84661182

23、BLOB和TEXT有什么区别

Memory存储引擎中的数据类型。memory存储引擎又称之为HEAP存储引擎
BLOB是一个二进制对象，可以容纳可变数量的数据。TEXT是一个不区分大小写的BLOB。
BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

24、什么是SQL通用函数

适用于任何数据类型，同时也使用于空值
CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
FORMAT(X, D)- 格式化数字X到D有效数字。
CURRDATE(), CURRTIME()- 返回当前日期或时间。
NOW（） – 将当前日期和时间作为一个值返回。
MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。
HOUR（），MINUTE（），SECOND（） – 从时间值中提取给定数据。
DATEDIFF（A，B） – 确定两个日期之间的差异，通常用于计算年龄
SUBTIMES（A，B） – 确定两次之间的差异。
FROMDAYS（INT） – 将整数天数转换为日期值

25、mysql 数据库模式sql_mode

1）ANSI模式：宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告。--缺省模式，是不支持事务的。
    但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。

2）TRADITIONAL模式：严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事物时，会进行事物的回滚。

3）STRICT_TRANS_TABLES模式：严格模式，进行数据的严格校验，错误数据不能插入，报error错误。


如何查看数据库是那种模式：select @@session.sql_mode
修改：set @@session.sql_mode='xx_mode'

26、mysql 数据类型

https://www.runoob.com/mysql/mysql-data-types.html

用于记录货币数据类型：numeric,decimal

27、MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。
d. 找规律分表，减少单表中的数据量提高查询速度。
e。添加缓存机制，比如memcached，redis等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

28、锁的优化策略

读写分离

分段加锁

减少锁持有的时间

多个线程尽量以相同的顺序去获取资源

不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

29、SQL注入漏洞产生的原因

SQL注入产生的原因：程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

防止SQL注入的方式：
开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

执行sql语句时使用addslashes进行sql语句转换

Sql语句书写尽量不要省略双引号和单引号。

过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。

提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的

30、完整性约束包括哪些

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

分为以下四类：

实体完整性：规定表的每一行在表中是惟一的实体。

域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 

31、如何通俗地理解三个范式

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

32、mysql分区表与分区类型

1、分区表

2、分区表类型
1)ANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
# 分区原理
# 分区实现


2)LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
# 分区原理
# 分区实现

3)HASH分区 ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
# 分区原理
# 分区实现


4)KEY分区 ：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的
# 分区原理
# 分区实现

33、key和index的区别

key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等

34、数据库表创建注意事项

1、字段名及字段配制合理性

剔除关系不密切的字段；

字段命名要有规则及相对应的含义（不要一部分英文，一部分拼音，还有类似a.b.c这样不明含义的字段）；

字段命名尽量不要使用缩写（大多数缩写都不能明确字段含义）；

字段不要大小写混用（想要具有可读性，多个英文单词可使用下划线形式连接）；

字段名不要使用保留字或者关键字；

保持字段名和类型的一致性；

慎重选择数字类型；

给文本字段留足余量；



2、系统特殊字段处理及建成后建议

添加删除标记（例如操作人、删除时间）；

建立版本机制；



3、表结构合理性配置

多型字段的处理，就是表中是否存在字段能够分解成更小独立的几部分（例如：人可以分为男人和女人）；

多值字段的处理，可以将表分为三张表，这样使得检索和排序更加有调理，且保证数据的完整性！


4、其它建议

对于大数据字段，独立表进行存储，以便影响性能（例如：简介字段）；

使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的；

给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响；

避免表字段运行为null，建议设置默认值（例如：int类型设置默认值为0）在索引查询上，效率立显；

建立索引，最好建立在唯一和非空的字段上，建立太多的索引对后期插入、更新都存在一定的影响（考虑实际情况来创建）；

 

35、mysql数据库用的是主从读写分离，主库写，从库读，假如从库无法读取了、或者从库读取特别慢，如何解决

（1）为了保障业务，可以先添加从库，临时解决问题
（2）然后抓取slow log日志，分析sql语句，该优化的优化处理，慢要不是硬件跟不上，就是软件需要调试优化。
https://www.sohu.com/a/331887831_120043609

36、mysql连接池问题

37

38

39

40