mysql基本原理面试_Mysql 原理-面试题-Go语言中文社区

一、基础架构

1、连接器管理

首先是数据库连接器，主要负责和客户端建立连接、权限获取、管理连接等，由于整个建连的过程比较复杂，所以尽量使用长连接。如果数据库发生异常后为了快速恢复，可重启系统重新建立连接。

2、Mysql缓存

mysql请求首先看缓存数据，key为sql语句value为查询的结果，如果存在则直接返回。如果没有则直接往下走。

注意：mysql缓存对于一些静态数据比较适合，对于实时性高的数据最好不要使用。

3、分析器

对你执行的sql语句进行解析，首先是词法分析包括一些关键字识别，然后语法分析，查看这条语句是否符合mysql语句

4、优化器

通过你的语句分析，发现那些查询命中索引，还有表之间的连接顺序等

5、执行器

通过上面一系列的验证，使用引擎提供的接口。经过不断的执行将查询的结果存放在结果集中，通过explain可以看到执行器具体扫描了多少行。

二、事务四大特性(ACID)

原子性：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执行事务之前的状态

一致性：如果在执行事务之前数据库是一致的，那么在执行事务之后数据库也还是一致的；

隔离性：事务操作之间彼此独立和透明互不影响。事务独立运行。这通常使用锁来实现。一个事务处理后的结果，影响了其                他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

持久性：事务一旦提交，其结果就是永久的。即便发生系统故障，也能恢复。

二、事务的并发问题

脏读：事务B修改数据但未提交，事务A读数据，然后B回滚，则A读到的是脏数据。

不可重复读：事务A第一次读取数据，事务B修改数据提交，事务A第二次读数据，两次数据不一致。

幻读：事务A update表的全部行，事务B插入一行，事务A就会发现表中还有未修改的行。(一般加间隙锁)

三、MySQL事务隔离级别

事务隔离级别

脏读

不可重复读

幻读

读未提交

会

会

会

读已提交

不会

会

会

可重复读

不会

不会

会

串行话

不会

不会

不会

查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’; ”

MySQL数据库(InnoDB引擎)默认使用可重复读( Repeatable read)

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足            条件的行，解决幻读需要锁表

四、数据库三范式：

第一范式：1NF是对属性的原子性约束，要求字段具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是在满足第一范式的前提下，非主键字段不能出现部分依赖主键；解决：消除复合主键就可避免出现部分以                    来，可增加单列关键字。

第三范式：3NF是在满足第二范式的前提下，非主键字段不能出现传递依赖，比如某个字段a依赖于主键，而一些字段依赖                      字 段a，这就是传递依赖。解决：将一个实体信息的数据放在一个表内实现。

五、日志的种类

错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)

二进制日志：-log-bin (记录所有更改数据的语句，还用于复制，恢复数据库用)

查询日志：-log (记录建立的客户端连接和执行的语句)

慢查询日志: -log-slow-queries (记录所有执行超过long_query_time秒的所有查询)

更新日志: -log-update (二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用

六、索引相关

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B_TREE。B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

1、MyISAM引擎

使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，即：MyISAM索引文件和数据文件是分离的，MyISAM的索引文件仅仅保存数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。

2、InnoDB引擎

使用B+Tree作为索引结构，但是InnoDB的数据文件本身就是索引文件，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚焦索引”。InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。InnoDB的索引实现后，不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。在Innodb中也不建议使用非单调的字段作为主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，建议使用自增字

MySQL数据库的四类索引:

index  ----  普通索引,数据可以重复，没有任何限制。

unique   ---- 唯一索引,要求索引列的值必须唯一，但允许有空值；如果是组合索引，那么列值的组合必须唯一。

primary key ---- 主键索引,是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的同时创建。

组合索引 ----在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

fulltext ---- 全文索引,是对于大表的文本域：char，varchar，text列才能创建全文索引，主要用于查找文本中的关键字，并不         是直接与索引中的值进行比较。fulltext更像是一个搜索引擎，配合match against操作使用，而不是一般的where语句加like。

注:全文索引目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引

所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括B型树索引和哈希索引。

索引可以提高查询的速度，但是创建和维护索引需要耗费时间，同时也会影响插入的速度，如果需要插入大量的数据时，最好是先删除索引，插入数据后再建立索引。

索引生效条件

假设index(a,b,c)

最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引

条件中有or，索引不会生效

a and c，a生效，c不生效

b and c，都不生效

a and b > 5 and c,a和b生效，c不生效。

检测索引的效果：

show status like '%handler_read%'越大越好

七、sql语句分类：

DDL：数据定义语言(create drop)

DML：数据操作语句(insert update delete)

DQL：数据查询语句(select )

DCL：数据控制语句，进行授权和权限回收(grant revoke)

TPL：数据事务语句(commit collback savapoint)

八、存储引擎 MyISAM 和 InnoDB区别：

InnoDB支持事务，MyISAM不支持。

MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。

InnoDB支持外键，MyISAM不支持。

从MySQL5.5.5以后，InnoDB是默认引擎。

MyISAM支持全文类型索引，而InnoDB不支持全文索引。

InnoDB中不保存表的总行数，select count(*) from table时，InnoDB需要扫描整个表计算有多少行，但MyISAM只需简单读出保存好的总行数即可。注：当count(*)语句包含where条件时MyISAM也需扫描整个表。

对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。

清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。MyisAM使用delete语句删除后并不会立刻清理磁盘空间，需要定时清理，命令：OPTIMIZE table dept;

InnoDB支持行锁(某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’)

Myisam创建表生成三个文件：.frm数据表结构 、 .myd数据文件 、 .myi索引文件，Innodb只生成一个 .frm文件，数据存放在ibdata1.log

现在一般都选用InnoDB，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。

应用场景：MyISAM不支持事务处理等高级功能，但它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

InnoDB用于需要事务处理的应用程序，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能

九、CHAR和VARCHAR的区别：

CHAR和VARCHAR类型在存储和检索方面有所不同

CHAR列长度固定为创建表时声明的长度，长度值范围是1到255

当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。

十、Mysql中的锁类型

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁

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

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高