Mysql原理-⾯试题
⼀、基础架构
1、连接器管理
⾸先是数据库连接器，主要负责和客户端建⽴连接、权限获取、管理连接等，由于整个建连的过程⽐较
复杂，所以尽量使⽤长连接。如果数据库发⽣异常后为了快速恢复，可重启系统重新建⽴连接。
2、Mysql缓存
mysql请求⾸先看缓存数据，key为sql语句value为查询的结果，如果存在则直接返回。如果没有则直接往下⾛。
注意：mysql缓存对于⼀些静态数据⽐较适合，对于实时性⾼的数据最好不要使⽤。
3、分析器
对你执⾏的sql语句进⾏解析，⾸先是词法分析包括⼀些关键字识别，然后语法分析，查看这条语句是否符合mysql语句
4、优化器
通过你的语句分析，发现那些查询命中索引，还有表之间的连接顺序等
5、执⾏器
通过上⾯⼀系列的验证，使⽤引擎提供的接⼝。经过不断的执⾏将查询的结果存放在结果集中，通过e
xplain可以看到执⾏器具体扫描了多少⾏。
⼆、事务四⼤特性（ACID）
原⼦性：不可分割的操作单元，事务中所有操作，要么全部成功；要么撤回到执⾏事务之前的状态
⼀致性：如果在执⾏事务之前数据库是⼀致的，那么在执⾏事务之后数据库也还是⼀致的；
隔离性：事务操作之间彼此独⽴和透明互不影响。事务独⽴运⾏。这通常使⽤锁来实现。⼀个事务处理后的结果，影响了其              他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
持久性：事务⼀旦提交，其结果就是永久的。即便发⽣系统故障，也能恢复。　
⼆、事务的并发问题
1. 脏读：事务B修改数据但未提交，事务A读数据，然后B回滚，则A读到的是脏数据。
2. 不可重复读：事务A第⼀次读取数据，事务B修改数据提交，事务A第⼆次读数据，两次数据不⼀致。
3. 幻读：事务A update表的全部⾏，事务B插⼊⼀⾏，事务A就会发现表中还有未修改的⾏。（⼀般加
间隙锁）
三、MySQL事务隔离级别
事务隔离级别脏读不可重复读幻读
读未提交会　会会
读已提交不会会会
可重复读不会不会会
串⾏话不会不会不会
查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’; ”
MySQL数据库(InnoDB引擎)默认使⽤可重复读（ Repeatable read)
1、未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
2、提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
3、可重复读(Repeated Read)：可重复读。在同⼀个事务内的查询都是事务开始时刻⼀致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读，但是innoDB解决了幻读
4、串⾏读(Serializable)：完全串⾏化的读，每次读都需要获得表级共享锁，读写相互都会阻塞
1.数据库默认隔离级别:  mysql ---repeatable,oracle,sql server ---read commited
mybatis3
3.为什么mysql⽤的是repeatable⽽不是read committed：在 5.0之前只有statement⼀种格式，⽽主从复制存在了⼤量的不⼀致，故选⽤repeatable
4.为什么默认的隔离级别都会选⽤read commited 原因有⼆：repeatable存在间隙锁会使死锁的概率增⼤，在RR隔离级别下，条件列未命中索引会锁表！⽽在RC隔离级别下，只锁⾏
5.在RC级⽤别下，主从复制⽤什么binlog格式：row格式，是基于⾏的复制！
⼩结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满⾜            条件的⾏，解决幻读需要锁表
四、数据库三范式：
第⼀范式：1NF是对属性的原⼦性约束，要求字段具有原⼦性，不可再分解；(只要是关系型数据库都满⾜1NF)
第⼆范式：2NF是在满⾜第⼀范式的前提下，⾮主键字段不能出现部分依赖主键；解决：消除复合主键就可避免出现部分以                  来，可增加单列关键字。
第三范式：3NF是在满⾜第⼆范式的前提下，⾮主键字段不能出现传递依赖，⽐如某个字段a依赖于主键，⽽⼀些字段依赖                    字 段a，这就是传递依赖。解决：将⼀个实体信息的数据放在⼀个表内实现。
五、⽇志的种类
1. 错误⽇志：-log-err (记录启动，运⾏，停⽌mysql时出现的信息)
2. ⼆进制⽇志：-log-bin （记录所有更改数据的语句，还⽤于复制，恢复数据库⽤）
3. 查询⽇志：-log （记录建⽴的客户端连接和执⾏的语句）
4. 慢查询⽇志: -log-slow-queries （记录所有执⾏超过long_query_time秒的所有查询）
5. 更新⽇志: -log-update （⼆进制⽇志已经代替了⽼的更新⽇志，更新⽇志在MySQL 5.1中不再使⽤
六、索引相关
数据库索引，是数据库管理系统中⼀个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使⽤ B_TREE。
B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了⼦节点的指针，存储引擎会根据指针快速寻数据。
1、MyISAM引擎jsp文件是干什么的
使⽤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%' 越⼤越好
div css布局 实验心得七、sql语句分类：
DDL：数据定义语⾔（create drop）
DML：数据操作语句（insert update delete）
DQL：数据查询语句（select ）
DCL：数据控制语句，进⾏授权和权限回收（grant revoke）
TPL：数据事务语句（commit collback savapoint）
⼋、存储引擎 MyISAM 和 InnoDB区别：
1. InnoDB⽀持事务，MyISAM不⽀持。
2. MyISAM适合查询以及插⼊为主的应⽤，InnoDB适合频繁修改以及涉及到安全性较⾼的应⽤。
3. InnoDB⽀持外键，MyISAM不⽀持。
4. 从MySQL
5.5.5以后，InnoDB是默认引擎。
5. MyISAM⽀持全⽂类型索引，⽽InnoDB不⽀持全⽂索引。
6. InnoDB中不保存表的总⾏数，select count(*) from table时，InnoDB需要扫描整个表计算有多少⾏，但MyISAM只需简单读出保
存好的总⾏数即可。注：当count(*)语句包含where条件时MyISAM也需扫描整个表。
7. 对于⾃增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段⼀起建⽴联合索引。
8. 清空整个表时，InnoDB是⼀⾏⼀⾏的删除，效率⾮常慢。MyISAM则会重建表。MyisAM使⽤delete语句删除后并不会⽴刻清理磁盘
空间，需要定时清理，命令：OPTIMIZE table dept;
mysql语句顺序9. InnoDB⽀持⾏锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’）
10. Myisam创建表⽣成三个⽂件：.frm 数据表结构 、 .myd 数据⽂件 、 .myi 索引⽂件，Innodb只⽣成⼀个 .frm⽂件，数据存放在
ibdata1.log
11. 现在⼀般都选⽤InnoDB，主要是MyISAM的全表锁，读写串⾏问题，并发效率锁表，效率低，MyISAM对于读写密集型应⽤⼀般是
不会去选⽤的。
12. 应⽤场景：
MyISAM不⽀持事务处理等⾼级功能，但它提供⾼速存储和检索，以及全⽂搜索能⼒。如果应⽤中需要执⾏⼤量的SELECT查询，那么MyISAM是更好的选择。
InnoDB⽤于需要事务处理的应⽤程序，包括ACID事务⽀持。如果应⽤中需要执⾏⼤量的INSERT或UPDATE操作，则应该使⽤InnoDB，这样可以提⾼多⽤户并发操作的性能
九、CHAR和VARCHAR的区别：
CHAR和VARCHAR类型在存储和检索⽅⾯有所不同
CHAR列长度固定为创建表时声明的长度，长度值范围是1到255
当CHAR值被存储时，它们被⽤空格填充到特定长度，检索CHAR值时需删除尾随空格。
⼗、Mysql中的锁类型
MyISAM⽀持表锁，InnoDB⽀持表锁和⾏锁，默认为⾏锁
表级锁：开销⼩，加锁快，不会出现死锁。锁定粒度⼤，发⽣锁冲突的概率最⾼，并发量最低
⾏级锁：开销⼤，加锁慢，会出现死锁。锁⼒度⼩，发⽣锁冲突的概率⼩，并发度最⾼
⼆、EXPLAIN结果-重要列
A、id
id相同，都为1，从上⾄下执⾏
id⼤优先级⾼（对应最内层⼦查询）优先执⾏；id相同从上到下执⾏
id值越⼤，优先级越⾼，越先执⾏；id如果相同，从上往下顺序执⾏
B、select_type
查询的类型，主要是⽤于区分普通查询、联合查询、⼦查询等复杂的查询。
SIMPLE：简单的SELECT查询，不包含⼦查询或者UNION查询
PRIMARY：查询中包含任何包含任何形式的⼦查询，最外层查询则被标记为PRIMARY
SUBQUERY：⼦查询
DEPENDENT SUBQUERY：依赖外部结构的⼦查询
DERIVED：出现在FROM⼦句中的⼦查询，把结果放在临时表
UNION：UNION操作的第⼆个或之后的查询为UNION
DEPENDENT UNION：UNION作为⼦查询时，第⼆个或之后的查询为DEPENDENT UNION
数据库系统工程师软考
UNION RESULT：UNION产⽣的结果集
C、type
访问类型，SQL查询优化中⼀个重要指标，查询性能从好到坏依次是
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
⼀般来说，好的SQL查询⾄少达到range级别，最好能达到ref。
system：const连接类型的特例，表只有⼀⾏记录（等于系统表，平时不会出现，可以忽略不计）
const：表中有且只有⼀个匹配⾏时使⽤，对主键或唯⼀索引的查询，效率最⾼，将主键置于WHERE列表中，MySQL就能将该查询转换为⼀个const
eq_ref：唯⼀性索引或主键查，对于每个索引键，表中只有⼀条记录与之匹配
前端工程师个人年度业绩汇总表ref：⾮唯⼀性索引查，返回匹配某个单独值的所有⾏（多⾏）
ref_null：类似ref类型，附加对NULL值列的查询
index_merge：索引合并优化⽅法（MySQL 5.6以后）
range：索引范围扫描，常见于bettween、<、>、in查询，这种索引列上的范围扫描⽐全索引扫描要好。只需要开始于某个点，结束于另⼀个点，不⽤扫描全部索引
index：全表索引扫描，使⽤索引⽽⾮数据⾏扫描
ALL：全表扫描
index与ALL区别：index类型只遍历索引树，索引⽂件通常⽐数据⽂件⼩（Index与ALL虽然都是读全表，但index是从索引中读取，⽽ALL是从硬盘读取）。
D、Extra
distinct：优化distinct操作，在到第⼀个匹配的元组后即停⽌同样值的动作
Not exists：使⽤not exists优化查询
Using filesort：使⽤额外操作进⾏排序，⽽不是按照索引进⾏排序，通常出现在order by或group by查询
Using index：使⽤覆盖索引进⾏查询，效率⾼
Using temporary：使⽤临时表处理查询，常⽤于排序、⼦查询、分组查询
Using where：MySQL服务器层使⽤WHERE条件过滤数据
select tables optimized away：直接通过索引获得数据，不⽤访问表

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。