mysql面试题

发表于 | 分类于

MySQL的复制原理以及流程

基本原理流程，3个线程以及之间的关联；
  主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
  从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
  从：sql执行线程——执行relay log中的语句；

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

5点不同
  1>.InnoDB支持事物，而MyISAM不支持事物
  2>.InnoDB支持行级锁，而MyISAM支持表级锁
  3>.InnoDB支持MVCC, 而MyISAM不支持
      MVCC是版本控制？
  4>.InnoDB支持外键，而MyISAM不支持
  5>.InnoDB不支持全文索引，而MyISAM支持。
 innodb引擎的4大特性
  插入缓冲（insert buffer)
  二次写(double write)
  自适应哈希索引(ahi)
  预读(read ahead)
2者selectcount(*)哪个更快，为什么
  myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

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的参数，它不会在前面加0
  20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；
(4)、mysql为什么这么设计
  对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

问了innodb的事务与日志的实现方式

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

MySQL binlog的几种日志录入格式以及区别

Statement：每一条会修改数据的sql都会记录在binlog中。
  优点
      不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
  缺点
      由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的 一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。
      使用以下函数的语句也有问题：
              sleep()函数
              last_insert_id()
              user-defined functions(udf)
              LOAD_FILE()
              UUID()
              USER()
              FOUND_ROWS()
              SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
      同时在INSERT …SELECT 会产生比 RBR 更多的行级锁
Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。
  优点
      binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了
      rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
  缺点
      可能会产生大量的日志内容
Mixedlevel: 是以上两种level的混合使用
  一般的语句修改使用statment格式保存binlog
  一些函数，statement无法完成主从复制的操作，则 采用row格式保存binlog

MySQL数据库cpu飙升到500%的话他怎么处理？

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

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 花多少时间等等

备份计划，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会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

500台db，在最快时间之内重启

使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。 
也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务器

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

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

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

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

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

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

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

你是如何维护数据库的数据字典的？

一般是直接在生产库进行注释，利用工具导出成excel方便流通

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

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

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

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

开放性问题：据说是腾讯的

一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
  1、如果A表TID是自增长,并且是连续的,B表的ID为索引
  select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
  2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
  select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

什么是存储过程？有哪些优缺点？

存储过程是一些预编译的SQL语句。
1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

索引有哪些数据结构

Hash、B+
去创建索引的时候，可以选择索引的类型

索引是什么？有什么作用以及优缺点？

1、索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构
2、索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引
  1、索引加快数据库的检索速度

  2、索引降低了插入、删除、修改等维护任务的速度

  3、唯一索引可以确保每一行数据的唯一性

  4、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

  5、索引需要占物理和数据空间

什么是事务？

事务（Transaction）是并发控制的基本单位。
它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

使用索引查询一定能提高查询的性能吗？为什么

通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
1、索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
2、基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
3、基于非唯一性索引的检索

简单说一说drop、delete与truncate的区

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别
1、delete和truncate只删除表的数据不删除表的结构
2、速度,一般来说: drop> truncate >delete
3、delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.
4、 truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

drop、delete与truncate分别在什么场景之下使用？

1、不再需要一张表的时候，用drop
2、想删除部分数据行时候，用delete，并且带上where子句
3、保留表而删除所有数据的时候用truncate

超键、候选键、主键、外键分别是什么？

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

什么是视图？以及视图的使用场景有哪些？

1、视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
2、只暴露部分字段给访问者，所以就建一个虚表，就是视图。
3、查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异

说一说三个范式。

第一范式（1NF）：属性唯一
  数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：记录唯一
  2:要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情
第三范式（3NF）：表唯一
  3:表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）

数据库的乐观锁和悲观锁是什么？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

Mysql中有哪几种锁？

1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

mysql有哪些引擎

 MyISAM
Heap
Merge
INNODB
ISAM

简述在MySQL数据库中MyISAM和InnoDB的区别

MyISAM：
  不支持事务，但是每次查询都是原子的；
  支持表级锁，即每次操作是对整个表加锁；
  存储表的总行数；
  一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
  采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
InnoDb
  支持ACID的事务，支持事务的四种隔离级别；
  支持行级锁及外键约束：因此可以支持写并发；
  不存储总行数；
  一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
  主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。
主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

myisamchk是用来做什么的？

它用来压缩MyISAM表，这减少了磁盘或内存使用。

MyISAM Static和MyISAM Dynamic有什么区别？

在MyISAM Static上的所有字段有固定宽度。动态MyISAM表将具有像TEXT，BLOB等字段，以适应不同长度的数据类型。

MyISAM Static在受损情况下更容易恢复。

如果一个表有一列定义为TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID将返回由Auto_increment分配的最后一个值，并且不需要指定表名称。

你怎么看到为表格定义的所有索引？

SHOW INDEX FROM

LIKE声明中的％和_是什么意思？

％对应于0个或更多字符，_只是LIKE语句中的一个字符。
子主题 2

如何在Unix和Mysql时间戳之间进行转换？

UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令
FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令

列对比运算符是什么？

在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。TEXT是一个不区分大小写的BLOB。
BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

mysql_fetch_array和mysql_fetch_object的区别是什么？

mysql_fetch_array（） – 将结果行作为关联数组或来自数据库的常规数组返回。
mysql_fetch_object – 从数据库返回结果行作为对象。

MyISAM表格将在哪里存储，并且还提供其存储格式？

每个MyISAM表格以三种格式存储在磁盘上：
·“.frm”文件存储表定义
·数据文件具有“.MYD”（MYData）扩展名
索引文件具有“.MYI”（MYIndex）扩展名

Mysql如何优化DISTINCT？

DISTINCT在所有列上转换为GROUP BY，并与ORDER BY子句结合使用。

如何显示前50行？

SELECT*FROM LIMIT 0,50;

可以使用多少列创建索引？

任何标准表最多可以创建16个索引列。

NOW（）和CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
CURRENT_DATE（）仅显示当前年份，月份和日期。

什么是非标准字符串类型？

TINYTEXT
 TEXT
 MEDIUMTEXT
 LONGTEXT

什么是通用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） – 将整数天数转换为日期值。

MYSQL支持事务吗？

在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。
但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，
你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式，
在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。

mysql里记录货币用什么字段类型好

NUMERIC
DECIMAL
  salary DECIMAL(9,2)
  9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。
  因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99。

mysql有关权限的表都有哪几个？

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。
user
db
table_priv
columns_priv
host

列的字符串类型可以是什么？

字符串类型是：
 SET
 BLOB
 ENUM
 CHAR
 TEXT

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

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

锁的优化策略

1. 读写分离
2. 分段加锁
3. 减少锁持有的时间
4. 多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

索引的底层实现原理和优化

B+树，经过优化的B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

什么情况下设置了索引但无法使用

1.以“%”开头的LIKE语句，模糊匹配
2. OR语句前后没有同时使用索引
3. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

实践中如何优化MySQL

1.SQL语句及索引的优化
2. 数据库表结构的优化
3.系统配置的优化
4.硬件的优化

优化数据库的方法

选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
事务处理
锁定表、优化事务处理
适用外键，优化锁定表
建立索引
优化查询语句
读了有收获就请肥宅喝瓶怡宝吧!