mysql总结

1.MySQL的复制原理以及流程

基本原理流程，3个线程以及之间的关联；

1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；

2、MySQL中myisam与innodb的区别，至少5点

(1)、问5点不同；

1>.InnoDB支持事物，而MyISAM不支持事物 
2>.InnoDB支持行级锁，而MyISAM支持表级锁 
3>.InnoDB支持MVCC, 而MyISAM不支持 
4>.InnoDB支持外键，而MyISAM不支持 
5>.InnoDB不支持全文索引，而MyISAM支持。

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

(3)、2者selectcount(*)哪个更快，为什么 myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

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

(1)、varchar与char的区别char是一种固定长度的类型，varchar则是一种可变长度的类型 
(2)、varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样) 
(3)、int（20）中20的涵义是指显示字符的长度但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变； 
(4)、mysql为什么这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

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

(1)、有多少种日志；错误日志：记录出错信息，也记录一些警告信息或者正确的信息。查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。二进制日志：记录对数据库执行更改的所有操作。中继日志：事务日志： 
(2)、事物的4种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行 
(3)、事务是如何通过日志来实现的，说得越深入越好。事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

5、问了下MySQL数据库cpu飙升到500%的话他怎么处理？

(1)、没有经验的，可以不问； 
(2)、有经验的，问他们的处理思路。列出所有进程 show processlist 观察所有进程 多秒没有状态变化的(干掉)查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)

6、sql优化

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

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

(1)、备份计划；这里每个公司都不一样，您别说那种1小时1全备什么的就行 
(2)、备份恢复时间；这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考20G的2分钟（mysqldump）80G的30分钟(mysqldump)111G的30分钟（mysqldump)288G的3小时（xtra)3T的4小时（xtra)逻辑导入时间一般是备份时间的5倍以上 
(3)、xtrabackup实现原理在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

8、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

监控的工具有很多，例如zabbix，lepus，我这里用的是lepus

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

主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

 

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

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

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

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

12、数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
1.原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2.一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
3.隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
4.持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

13, 主键 超键 候选键 外键

主 键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
超 键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
外 键：在一个表中存在的另一个表的主键称此表的外键

14 .数据库范式

1 第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3 第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

15.数据库优化的思路

1.SQL语句优化
1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化
看上文索引

3.数据库结构优化
1）范式优化： 比如消除冗余（节省空间。。） 2）反范式优化：比如适当加冗余等（减少join） 3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

4.服务器硬件优化

16. 简述触发器、函数、视图、存储过程？

触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

create trigger trigger_name
after/before insert /update/delete on 表名
for each row
begin
sql语句：（触发的语句一句或多句）
end
 函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数创建语法：

创建：CREATE FUNCTION 函数名称(参数列表) 　
RETURNS 返回值类型 　函数体
修改： ALTER FUNCTION 函数名称 [characteristic ...]
删除：DROP FUNCTION [IF EXISTS] 函数名称
调用：SELECT 函数名称(参数列表)
视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影

create view view_name as select 语句
存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）

create procedure 存储过程名(参数,参数,…)
begin
//代码
end

17. 如何开启慢查询日志？

1 执行 SHOW VARIABLES LIKE “%slow%”，获知 mysql 是否开启慢查询 slow_query_log 慢查询开启状态 OFF 未开启 ON 为开启 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

2 修改配置文件（ 放在[mysqld]下），重启 long_query_time 查询超过多少秒才记录

3 测试是否成功

4 慢查询日志文件的信息格式

作用：开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

18. 索引在什么情况下遵循最左前缀的规则？

在使用组合索引查询的时候需要遵循“最左前缀”规则
　　最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d= 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

19. 主键和外键的区别？

主键Primary key：数据库实体完整性的一种规则，唯一标示一个实体，取值非空唯一。比如，学生表的学号。

外键 Foreign key：数据库参照完整性的一种规则，将两表或者多张表联系起来，取值必须来自参照表的参照字段的值，可为空也可不为空。比如，选课表里的学号。

20. MySQL常见的函数？ 　　

聚合函数：

　　AVG(col)返回指定列的平均值

　　COUNT(col)返回指定列中非NULL值的个数

　　MIN(col)返回指定列的最小值

　　MAX(col)返回指定列的最大值

　　SUM(col)返回指定列的所有值之和

　　GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

数学函数：

　　ABS(x) 返回x的绝对值

　　BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
时间函数：
    CURDATE() 当前日期 2019-02-11
    DATE_SUB(CURDATE(),INTERVAL 30 DAY) 30日前
区间函数：
    BETWEEN 0 and 10 查找在0于10之间的数据

    
    

21. 列举 创建索引但是无法命中索引的8种情况。

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

2.对于多列索引，不是使用的第一部分，则不会使用索引

3.like查询是以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

6 对小表查询

7 提示不使用索引

8 统计数据不真实

9.单独引用复合索引里非第一位置的索引列.

22. 索引设计的原则？

1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2.基数较小的类，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

23.一致性哈希算法

一致性Hash算法也是使用取模的方法，只是，hash取模法是对服务器的数量进行取模，而一致性Hash算法是对2^32取模；同样一致性hash算法也会对数据有影响，只是影响后一个库（顺时针）