MySQL专题
- 1.MySQL架构图
- 2.sql语句执行过程？
- 3.数据库的三范式是什么？
- 4.char 和 varchar 的区别？
- 5.谈谈你对索引的理解，mysql底层使用的是什么索引
- 6.为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？
- 7.为什么mysql索引使用b+树而不使用红黑树?
- 8.MyISAM 与 InnoDB 如何选择？
- 9.mysql索引失效的几种情况
- 10.MySQL 主从复制
- 11.MySQL事务
- 12.谈谈你对MVCC 的了解
- 13.说一下 MySQL 的行锁和表锁？
- 14.聚簇索引和非聚簇索引区别?
1.MySQL架构图
详情：link 总结：MySQL 可以分为 Server 层和存储引擎两部分。
2.sql语句执行过程？
1）通过连接器判断用户名和密码是否正确，若失败，直接返回用户被拒的错误；否则获取用户权限信息；
2）若查询缓存开启则通过sql语句查询缓存中是否有key为当前查询的sql，若存在，直接返回value，结束查询；
3）若缓存无结果返回，那么通过分析器，对sql语句进行解析，若sql语句有误，返回语法错误信息，结束查询；
4）sql语句正常经过分析器后，在优化器确认最终的执行方案，确定是用表中的索引以及表的连接顺序；
5）当到达执行器的时候判断用户对sql中的表有没有权限操作，若没有返回权限不足的错误信息，否则，调用存储引擎提供的数据查询接口查询数据，若缓存开启，则更新缓存，最后将结果返回。3.数据库的三范式是什么？
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。
第二范式（2NF）：每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
第三范式（3NF）：数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。4.char 和 varchar 的区别？
char(n) ：
固定长度类型，比如：订阅 char(10)，当你输入”abcde”5个字符的时候，它们占的空间还是 10 个字节，其他 5个是空字节。char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值（MD5加密），固定长度的，使用 char 非常合适。
varchar(n) ：
可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。注意：虽然使用varchar(100)和varchar(10)存储’hello12345’字符串占用的磁盘空间一样，但是消耗的内存不一样，更长的列消耗的内存会更多。
5.谈谈你对索引的理解，mysql底层使用的是什么索引
索引：
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
MySQL的索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用索引的数据结构和具体存储引擎的实现有关,，在MySQL中使用较多的索引有 Hash 索引、B+树索引等。而我们经常使用的 InnoDB 存储引擎的默认索引实现为 B+ 树索引。
6.为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？
B+树是B树的一个变体，B+树与B树最大的区别在于：
1.叶子结点包含全部关键字以及指向相应记录的指针，而且叶结点中的关键字按大小顺序排列，相邻叶结点用指针连接。
2.非叶结点仅存储其子树的最大（或最小）关键字，可以看成是索引。原因有：
①B+树更适合外部存储。B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，且每个结点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B树，I/O的次数相对减少。
②MySQL是一种关系型数据库，区间访问是常见的一种情况，B+树叶结点增加的链指针，加强了区间访问性，可使用在区间查询的场景；而使用B树则无法进行区间查找。
7.为什么mysql索引使用b+树而不使用红黑树?
b+树就是为文件存储而生的。如果数据库文件存储在主存中我认为两种结构的查询速度差距不是很大，因为主存的查找速度非常快。而数据库文件实际存储在磁盘中，定位一行信息需要查找该行文件所在柱面号，磁盘号，扇区号，页号这个阶段是很耗费时间的。每一次的定位请求意味着要做一次IO操作，也意味着成倍的时间消耗。因此减少IO查询的次数是提高查询性能的关键。而IO的查询次数就是索引树的高度，高度越低查询的次数越少。 同样的结点次数红黑树的高度最多为2log(n+1)，而B+树的高度最多为(logt (n+1)/2)+1,随着t增大高度会更小，IO次数也会减少。
8.MyISAM 与 InnoDB 如何选择？
一、InnoDB支持事务，MyISAM不支持，
二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
三、InnoDB支持外键，MyISAM不支持
四、InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表
五、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
六、MyISAM 只支持表锁，InnoDB 可以支持行锁。9.mysql索引失效的几种情况
1、OR引起的索引失效
2、运算符导致的索引失效
3、like查询以%开头，索引无效；4、当字段类型为字符串时，条件中数据没有使用引号引用。
4、函数导致的索引失效
5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引10.MySQL 主从复制
概念：
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
MySQL 主从复制的主要用途
1.读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2.数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换(主从切换)
提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据；
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:
binlog 线程 ：
负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：
负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
SQL 线程 ：
负责读取重放日志并重放其中的 SQL 语句。
11.MySQL事务
事务四大特征(ACID)：
原子性(A)：事务是最小单位，不可再分
一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：事务A和事务B之间具有隔离性
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)事务开启的标志？事务结束的标志？
- 任何一条DML语句(insert、update、delete)执行，标志事务的开启
- 结束标志(提交或者回滚)：
提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
回滚：失败的结束，将所有的DML语句操作历史记录全部清空
解释下什么叫脏读、不可重复读和幻读？
脏读 | 表示一个事务能够读取另一个事务中还未提交的数据 |
不可重复读 | 在同一个事务中，前后两次读取的数据不一致的现象 |
幻读 | 一个事务用一样的 SQL 多次查询，结果每次查询都会发现查到一些之前没看到过的数据 |
12.谈谈你对MVCC 的了解
MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。
在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。
MVCC 可以为数据库解决以下问题：
1.在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能；
2.同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。13.说一下 MySQL 的行锁和表锁？
MyISAM 只支持表锁；InnoDB 支持表锁和行锁，默认为行锁，在未使用索引字段查询时升级为表锁
表锁：开销小，加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率最高，并发量最低。
行锁：开销大，加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率小，并发度最高。InnoDB存储引擎有3种行锁的算法，分别是：
Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。14.聚簇索引和非聚簇索引区别?
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。