Mysql面试题

详述mysql主从复制原理及配置主从的完整步骤

主从复制的原理如下：

主库开启binlog功能并授权从库连接主库，从库通过change master得到主库的相关同步信息然后连接主库进行验证，
主库io线程根据从库slave线程的请求，从master.info开始记录的位置点向下开始取信息，
同时把取到的位置点和最新的位置与binlog信息一同发给从库io线程,从库将相关的sql语句存放在relay-log里面，
最终从库的sql线程将relay-log里的sql语句应用到从库上，至此整个同步过程完成，之后将是无限重复上述过程

完整步骤如下：

1、主库开启binlog功能，并进行全备，将全备文件推送到从库服务器上

2、show master status\g 记录下当前的位置信息及二进制文件名

3、登陆从库恢复全备文件

4、执行change master to 语句

5、执行start slave and show slave status\g

mysql的复制原理以及流程

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

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；

从：sql执行线程——执行relay log中的语句；

mysql中myisam与innodb的区别

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

innodb引擎的4大特性

插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

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

有多少种日志；

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
慢查询日志：设置一个阈值，将运行时间超过该值的所有sql语句都记录到慢查询的日志文件中。
二进制日志：记录对数据库执行更改的所有操作。
中继日志：
事务日志：

事物的4种隔离级别

隔离级别
读未提交(ru)
读已提交(rc)
可重复读(rr)
串行

sql语句分类

 

ddl  数据定义语言，用来定义数据库对象：库、表、列

    代表性关键字：create alter drop

dml  数据操作语言，用来定义数据库记录

    代表性关键字:insert delete update

dcl  数据控制语言，用来定义访问权限和安全级别

    代表性关键字:grant deny revoke

dql  数据查询语言，用来查询记录数据

    代表性关键字:select 

 

事务是如何通过日志来实现的

事务日志是通过redo和innodb的存储引擎日志缓冲（innodb log buffer）来实现的，
当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 
当事务执行时，会往innodb存储引擎的日志的日志缓存里面插入事务日志；
当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），
也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

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

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

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

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

mysql数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

1、索引加快数据库的检索速度
2、索引降低了插入、删除、修改等维护任务的速度
3、唯一索引可以确保每一行数据的唯一性
4、通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
5、索引需要占物理和数据空间

什么是事务？

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

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

数据库管理系统（dbms）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

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

通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.

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中,事务提交之后才生效;
4、如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,
   原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

三个范式

第一范式（1nf）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2nf）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），
               也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3nf）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。
               所谓传递函数依赖，指的是如 果存在"a → b → c"的决定关系，则c传递函数依赖于a。
               因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

 

mysql存储引擎

1、innodb存储引擎

默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀。

数据存储在共享表空间，可以通过配置分开。也就是多个表和索引都存储在一个表空间中，可以通过配置文件改变此配置。

对主键查询的性能高于其他类型的存储引擎。

内部做了很多优化，从磁盘读取数据时会自动构建hash索引，插入数据时自动构建插入缓冲区。

通过一些机制和工具支持真正的热备份。

支持崩溃后的安全恢复。

支持行级锁。

支持外键。

2、myisam存储引擎

拥有全文索引、压缩、空间函数。

不支持事务和行级锁、不支持崩溃后的安全恢复。

表存储在两个文件，myd和myi。

设计简单，某些场景下性能很好，例如获取整个表有多少条数据，性能很高。

全文索引不是很常用，不如使用外部的elasticsearch或lucene。

mysql锁机制

表锁是日常开发中的常见问题，因此也是面试当中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。共享锁和排他锁，就是读锁和写锁。

共享锁，不堵塞，多个用户可以同时读一个资源，互不干扰。

排他锁，一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

锁的粒度
表锁，系统开销最小，会锁定整张表，myisam使用表锁。

行锁，最大程度的支持并发处理，但是也带来了最大的锁开销，innodb使用行锁。

索引对性能的影响：

大大减少服务器需要扫描的数据量。

帮助服务器避免排序和临时表。

将随机i/o变顺序i/o。

大大提高查询速度。

降低写的速度（不良影响）。

磁盘占用（不良影响）。

索引的使用场景：

对于非常小的表，大部分情况下全表扫描效率更高。

中到大型表，索引非常有效。

特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

索引的类型：

索引很多种类型，是在mysql的存储引擎实现的。

普通索引：最基本的索引，没有任何约束限制。

唯一索引：和普通索引类似，但是具有唯一性约束。

主键索引：特殊的唯一索引，不允许有空值。