目录
内连接、外连接、交叉连接，笛卡尔积？
内连接：取得两张表中同时满足相同条件（连接关系）的记录
外连接：除了满足连接关系的记录，其他不满足的记录也会有
交叉连接：A表有m行，B表有n行，交叉就是m*n行（没有匹配关系的进行筛选）
笛卡尔积：也是一个A*B的概念
Mysql左外右外内连接区别
需要注意，我们现在一般联表都是左外，然后索引，索引一般作用在右表，因为左表是全局扫描的
数据库三大范式
第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)
第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)
第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
1NF
属性不可再分，即表中的每个列都不可以再进行拆分。
如下学生信息表(student)：
id、name(姓名)、sex_code(性别代号)、sex_desc(性别描述)、contact(联系方式)
primary key(id)
| id | name | sex_code | sex_desc | contact |
|---|---|---|---|---|
| 001 | 张三 | 0 | 男 | 17835201234_山西省运城市xx村 |
| 002 | 李四 | 0 | 男 | 17735204567_山西省吕梁市yy村 |
| 003 | 王五 | 1 | 女 | 18835207890_山西省太原市zz村 |
如果在查询学生表时经常用到学生的电话号，则应该将联系方式(contact)这一列分为电话号(phone)和地址(address)两列，这样才符合第一范式。
修改使表满足1NF后：
| id | name | sex_code | sex_desc | phone | address |
|---|---|---|---|---|---|
| 001 | 张三 | 0 | 男 | 17835201234 | 山西省运城市xx村 |
| 002 | 李四 | 0 | 男 | 17735204567 | 山西省吕梁市yy村 |
| 003 | 王五 | 1 | 女 | 18835207890 | 山西省太原市zz村 |
2NF
在满足1NF的前提下，表中不存在部分依赖，非主键列要完全依赖于主键。(主要是说在联合主键的情况下，非主键列不能只依赖于主键的一部分)
如下学生成绩表(score)：
stu_id(学生id)、kc_id(课程id)、score(分数)、kc_name(课程名)
primary key(stu_id, kc_id)
| stu_id | kc_id | score | kc_name |
|---|---|---|---|
| 001 | 1011 | 85 | 高数3-1 |
| 001 | 1022 | 79 | 计算机组成原理 |
| 002 | 1011 | 59.9 | 高数3-1 |
表中主键为stu_id和kc_id组成的联合主键。满足1NF；非主键列score完全依赖于主键，stu_id和kc_id两个值才能决定score的值；而kc_name只依赖于kc_id，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。
修改使表满足2NF后：
成绩表(score) primary key(stu_id)
| stu_id | kc_id | score |
|---|---|---|
| 001 | 1011 | 85 |
| 001 | 1022 | 79 |
| 002 | 1011 | 59.9 |
课程表(kc) primary key(kc_id)
| kc_id | kc_name |
|---|---|
| 1011 | 高数3-1 |
| 1022 | 计算机组成原理 |
将原来的成绩表(score)拆分为成绩表(score)和课程表(kc)，而且两个表都符合2NF。
3NF：
在满足2NF的前提下，不存在传递依赖。(A -> B, B -> C, A->C)
如下学生信息表(student)：
primary key(id)
| id | name | sex_code | sex_desc | phone | address |
|---|---|---|---|---|---|
| 001 | 张三 | 0 | 男 | 17835201234 | 山西省运城市xx村 |
| 002 | 李四 | 0 | 男 | 17735204567 | 山西省吕梁市yy村 |
| 003 | 王五 | 1 | 女 | 18835207890 | 山西省太原市zz村 |
表中sex_desc依赖于sex_code，而sex_code依赖于id(主键)，从而推出sex_desc依赖于id(主键)；sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。
修改表使满足3NF后：
学生表(student) primary key(id)
| id | name | sex_code | phone | address |
|---|---|---|---|---|
| 001 | 张三 | 0 | 17835201234 | 山西省运城市xx村 |
| 002 | 李四 | 0 | 17735204567 | 山西省吕梁市yy村 |
| 003 | 王五 | 1 | 18835207890 | 山西省太原市zz村 |
性别代码表(sexcode) primary key(sex_code)
| sex_code | sex_desc |
|---|---|
| 0 | 男 |
| 1 | 女 |
将原来的student表进行拆分后，两个表都满足3NF。
什么样的表越容易符合3NF？
非主键列越少的表。(1NF强调列不可再分；2NF和3NF强调非主属性列和主属性列之间的关系)
如代码表(sexcode)，非主键列只有一个sex_desc；
或者将学生表的主键设计为primary key(id,name,sex_code,phone)，这样非主键列只有address，更容易符合3NF。
varchar和char的区别
char：
最关键的就是空间固定，以空间换时间
varchar：
最关键的就是可变字符串，时间换空间
场景：固定长度的可以使用char，不明确长度的可以使用varchar
DATETIME和TIMESTAMP的区别
不同点：
TIMEStAMP范围小一点，但是存储字节空间也小一点，节省内存，而且默认是当前是时间就非常友好，DATETIME默认是null
Mysql中in和exists的区别
(47条消息) IN和EXISTS的区别和使用_沉泽·的博客-CSDN博客_in和exists的区别与作用
drop和delete和truncate的区别
delete是一行一行删除，truncate是直接把表删了然后创建一个空表，drop是全删了
一条sql的执行顺序
mysql基础架构
(46条消息) SQL高级_Fairy要carry的博客-CSDN博客
主要分为三层：1.客户端（连接处理、授权认证等），2.Server层：查询解析分析，优化，缓存，还包括mysql的内置函数（日期，时间函数等），还有触发器视图的实现等，3.存储引擎：负责mysql中数据的存储和提取
一条SQL的查询语句在mysql中如何执行的
1.先检查语句权限如果有的话——>2.先查询sql_cache缓存——>3.如果没有缓存那么分析器进行语法分析，比如sql中select等关键元素，然后判断是否含有语法错误——>4.语法解析后，mysql中的优化器会进行语句的优化，确定执行方案——>5.最后安装执行计划调用数据库引擎接口返回执行结果；
Mysql中常见的存储引擎
MylSAM用的表锁，一锁就是整个表，不适合高并发
InnoDB用的是行锁，对单条数据上锁，适合高并发操作，会出现死锁情况；
缓存方面：MyISAM的话只缓存索引，没有缓存真实数据；而InnoDB，都缓存了，不过对性能方面会有影响；
事务：MyISAM不支持事务，而InnoDB支持事务
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针
binlog和redolog、undolog的区别
(46条消息) Mysql的三大日志_Fairy要carry的博客-CSDN博客
一条更新语句是如何执行的
Server层+引擎层配合完成，数据除了要记录到表中还需要记录到相应的日志中
1.首先找到id=2这一行，存储引擎进行检索找到后返回这行数据——>2.执行器拿到这行数据，利用解析器与优化器将值+1然后再调用引擎的接口写入这行数据——>3.引擎将数据更新到内存中，同时记录到redo log中，状态为prepare，告知执行器已经执行完随时可以提交
4.执行器执行binlog，并把binlog写入磁盘保证数据的一致性——>5.执行器调用引擎提交事务的接口将redolog状态改为提交——>表示更新完成
为什么redolog要进行两段提交？
SQL优化
首先开启慢查询，然后通过mysqldunmpslow可以找到慢查询sql——>然后explain分析慢sql看是否用了索引，是否出现索引失效
(46条消息) sql分析（查询截取分析做sql优化）_Fairy要carry的博客-CSDN博客_sql 查询分析
1.避免select *
(46条消息) 我们为什么要使用索引+select *（面试）_Fairy要carry的博客-CSDN博客_select字段加索引
在sql查询时，只查询需要的列；避免回表操作
1.分页优化
3.其他
1.避免使用or查询，or可能会导致索引失效
2.避免使用！=或者<>操作符，这些会导致查询引擎放弃查询引擎->从而引起全表扫描
3.not in也是上述这样，我们可以使用not exist，但是占用内存小，4bit，节省空间
4.避免函数上运算影响效率
5.关联查询符合最左原则，索引从左到右优先匹配，左外连接，左边的驱动表是会被全局扫描的，所以索引我们一般是放在被驱动表上；
(46条消息) 索引+sql练习优化_Fairy要carry的博客-CSDN博客_sql优化练习
explain执行计划各个字段的含义
type:
1.Using index：表示Mysql使用覆盖索引，避免回表，index是遍历索引树读取，得到索引的值然后去主键索引tree寻找数据
2.Using ALL：全表扫描，而且是从硬盘中读取
3.Using range：针对索引选择行一般都是范围
索引的分类
主键索引：InnoDB主键是默认的索引，数据列不允许重复并且不能为null，一个表只能有一个主键
唯一索引：数据列不重复并且不能为null，一个表中允许多个列创建唯一索引
普通索引：没有约束随便创建
组合索引：多个列值组成的索引，用于组合搜索，满足最左优先匹配原则
简单理解B树和B+树_Fairy要carry的博客-CSDN博客
(46条消息) 聚簇索引和非聚簇索引到底有什么区别？_Linux小百科的博客-CSDN博客_聚簇索引和非聚簇索引的区别
为什么使用索引会加快查询效率
传统查询的方法都是按照表的顺序遍历，从头到尾遍历一遍
而添加了索引之后，MYSQL会通过BTREE算法生成索引文件，在查询数据库的时候，会先到索引文件进行遍历，相当于有了一个范围，在这个相对较小的索引数据中查找——>找到索引数据后映射到整行数据；类似根据目录查找对应内容
注意点：
1.索引应该创建在查询频繁的字段
2.索引的个数应该适量，毕竟索引需要占用空间，需要维护
3.经常crud的字段也不需要创建索引，维护成本太高了
索引失效的场景
Mysql索引使用的数据结构
(46条消息) 简单理解B树和B+树_Fairy要carry的博客-CSDN博客
默认使用的是B+树
B+树能存储多少条数据
2000多万数据，查询最多只需要访问三次磁盘
索引如果字段是bigint类型，长度为8bit，指针为6bit，所以一共是14bit，一页是16384，可以存储16384/14=1170指针
树的深度为2时，有1170^2……16=21902400
为什么要用B+树，而不用其他的树
普通二叉树
二叉树在极限情况下会出现退化成链表的情况，相当于全表扫描
平衡二叉树
缺点是每个节点存储一个键值对数据，B+树可以存储更多的节点数据，树的高度会相对要低很堵，也就是IO次数也就更少，每次从硬盘中读取的数据更多
Hash所有和B+树索引的区别是什么
1.B+树可以作为范围查询，但是Hash索引不能
2.B+树支持联合索引需要符合最左侧原则，而Hash所以不支持
3.B+树支持order by排序，Hash索引不支持
4.Hash所以在等值查询效率更高
5.Hash所以无法进行模糊查询
(46条消息) MySQL支持的索引类型（B-Tree索引、hash索引）_z_ryan的博客-CSDN博客
聚簇索引和非聚簇索引的区别
(46条消息) 聚簇索引和非聚簇索引到底有什么区别？_Linux小百科的博客-CSDN博客_聚簇索引和非聚簇索引的区别 首先聚簇索引是一种数据存储方式
索引的数据结构就是树，聚簇索引——>索引和数据存储在同一颗树上，树的叶子节点就是数据，我们可以根据索引的值确定数据的位置以及值
聚簇索引——>就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分
我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据
优点：
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据(重点)。
非聚簇索引（辅助索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先——>找到的是主键值，再通过主键值——>找到数据行的数据页，再通过数据页中的Page Directory找到数据行
总结就是叶子节点是主键值和物理地址，然后根据这个地址可以得到数据页的数据
所以说MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引
InnoDB例子
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据
问题：主键索引是聚集索引还是非聚集索引？
在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引
聚簇索引和非聚簇索引的区别
聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。
由于节子节点(数据页)只能按照一颗B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引
回表
(46条消息) 什么是回表查询_早安试言的博客-CSDN博客_回表查询
总结：简而言之就是当前查询语句没有主键值，只有一个普通的二级索引，那么就会先在辅助索引树上得到主键索引值然后——>去主键的树中遍历得到对应的数据
多扫了一颗索引树
覆盖索引了解吗
场景：不select * from xxx而是 select name from xxx，指定所要查询的字段，这就是覆盖索引，这些select的数据列只需要用辅助索引就可以获取，不需要查主键索引（可以结合第二范式去理解，我们用主键索引在select *场景使用无非就是其他的键都依赖于主键），这样我们只需要查询一遍辅助索引即可得到想要的数据——>避免了回表
什么是索引下推优化
(46条消息) 索引条件下推优化（ICP）_想要胖胖的豪的博客-CSDN博客_索引下推优化
(46条消息) 5分钟搞懂MySQL - 索引下推优化__陈哈哈的博客-CSDN博客
首先根据name找到辅助索引下的数据——>然后回表因为是select*，找到主键的id值以及对应的数据——>然后再根据age进行过滤（这样效率较低，回表后的数据还是两条，age此时没有起作用）
因为like模糊查询后面的索引失效
使用索引推优化，回表过滤得到的id直接就是age过滤后的 （将where条件放在引擎层执行）
Mysql中有哪几种锁
从粒度分析
表锁：并发度低，像MyISAM就是这种
行锁：并发度高，冲突概率低，但是开销大
页锁：会出现死锁，并发度和开销在两者之间，就是B+树叶子节点就是数据页
从兼容性
共享锁：也就是读锁，互不阻塞
排他锁：也就是写锁
MySql中的乐观锁和悲观锁
乐观锁：
就是cas操作，认为数据变动很小，事务字数据库中改数据时，会去除数据进行对比，如果和目标数据一致，就修改
悲观锁：
就是上锁，认为每次都会有人过来修改
Mysql死锁问题解决方案
其实可以通过jconsole进行分析
Mysql事务的四大特性
(46条消息) 什么是事务隔离级别，事务隔离级别详解_TPCloud的博客-CSDN博客_什么是事务的隔离级别
原子性：事务被作为一个整体执行，对数据库的操作要么全部执行，要么不执行——redolog实现的
一致性：指的是事务开始之前和事务结束以后，数据是不会被破坏的，比如说A账户给B账户转账，无论转账是否成功，A和B金额总和是不变的，也就是数据的一致性——>binlog保证的
隔离性：多个事务进行并发访问数据，事务之间是相互隔离的，毕竟有数据库锁资源，事务之间是不会被影响的——>数据库锁实现
持久性：表示事务完成之后，事务对数据库操作是永远保存的——>redolog
(46条消息) Mysql的三大日志_Fairy要carry的博客-CSDN博客
事务的隔离级别有哪些？Mysql默认隔离级别是什么
默认隔离级别
读未提交：一个事务还没有提交的时候，做的变更被其他的事务看到——>可能出现脏读，也就是读到其他事务未提交的数据
读已提交：与上面相反，一个事务提交之后它做的变更才能被其他事务看到
可重复读：
解决了脏读问题，也就是读取未提交数据这种问题解决了，保证了同一个事务中读取的数据是一样的——>出现幻读问题
重复读级别还是无法解决另外一个幻读（phantom read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。当之前的事务再次读取该范围的记录时，会产生幻行（phantom row)
A会话开启事务，并修改数据
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> update poor_user set password='888888';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
开启B会话，查看数据
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)
mysql> select * from poor_user;
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| UserID | UserName | Password | UserBirth | Descript |
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| bbe7faab-7c48-11e9-b4ae-80ce6248ca07 | 唐鹏程,tpc123 | 123456 | 1989-05-13 | 最帅气，最善良，最可爱的男人。|
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
1 row in set (0.00 sec)
发现在重复读下，B事务看到数据没发生改变——>Mysql的默认隔离级别（凸现出隔离性，事务之间是互不相干的）
然后对A数据进行提交，B事务查看
mysql> commit;
Query OK, 0 rows affected (0.01 sec)发现B还是没看到A中提交的数据
mysql> select * from poor_user;
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| UserID | UserName | Password | UserBirth | Descript |
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| bbe7faab-7c48-11e9-b4ae-80ce6248ca07 | 唐鹏程,tpc123 | 123456 | 1989-05-13 | 最帅气，最善良，最可爱的男人。|
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
1 row in set (0.00 sec)
当B进行事务提交后，再次select数据
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from poor_user;
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| UserID | UserName | Password | UserBirth | Descript |
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
| bbe7faab-7c48-11e9-b4ae-80ce6248ca07 | 唐鹏程,tpc123 | 888888 | 1989-05-13 | 最帅气，最善良，最可爱的男人。|
+--------------------------------------+------------------+----------+------------+-----------------------------------------------+
1 row in set (0.00 sec)
结论：当B事务提交后，他可以看到A事务中提交的数据了。可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。
MVCC了解吗？怎么实现的？
(49条消息) 【MySQL笔记】正确的理解MySQL的MVCC及实现原理_SnailMann的博客-CSDN博客_mysql的mvcc
数据库读写分离
主要是保证主从的数据一致性，利用binlog
基本实现：
1.master负责读写操作，从机负责读操作
2.主机通过复制将数据同步给从机
3.业务服务器将写操作给数据库主机，将读操作给到数据库从机
(49条消息) sql主从复制搭建_Fairy要carry的博客-CSDN博客
读写操作是如何实现的
我们将读写操作分开，然后访问不同的数据库服务器，一般有两种方式，程序代码封装和中间件封装。
程序代码封装
中间件封装
用了反向代理，对于业务服务器来说，访问中间件和访问数据库没什么区别，也就是说，中间件相当于一个数据库服务器
主从复制原理
slave从机会从master读取的binlog进行数据同步——>保证数据的一致性
1 master主机将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2 slave将master的binary log events拷贝到它的中继日志（relay log）；
3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的
(49条消息) sql主从复制搭建_Fairy要carry的博客-CSDN博客
statement模式：不能使用函数，比如说你的主机使用函数now()记录时间但是当同步到从机的时候，他的时间就会发生变化（明显不一致），从而导致主从复制数据不一致的情况发送（个人感觉有点像RDB，直接数据同步，会有数据不一致情况）；
行模式row：效率较低，记录每一行的改变，如果数据量达到一个层面就会很慢了（个人感觉有点像AOF，给从机每一行去执行）；
MIXED模式：前面两种模式的结合版本；
主从同步延迟处理
因为我们的主服务器master只有一个线程去推送binlog到slave，那么在多个客户端的情况下，并发操作很大，怎么样去同步我们的数据到从服务器中呢？毕竟只有一个线程
——>这里联想到了多路复用，引用NIO的思想，它主机也是一个线程，引用Selector和Channel通道和buffer，完成数据的推送
(49条消息) NIO学习_Fairy要carry的博客-CSDN博客
主从复制延迟的解决办法：
分库分表
(49条消息) Mycat+分库分表_Fairy要carry的博客-CSDN博客_mycat分库分表
百万数据如何删除
首先是删除索引，因为索引需要额外的维护成本，像咱们B+树，除了主键索引树之外，不是都有个辅助索引树吗，需要先对索引先删除了——>然后再删除其中的无用数据
另外，创建索引的速度和删除索引的速度是成正本的
百万级别的表如何添加字段
1.通过中间表进行转换过去——>创建一个临时的表，然后将旧表的结构完全复制过去，再添加字段（因为此时数据为空），此时结构添加字段完成——>最后修改表名，将数据insert进去
2.工具pt-online-schema-change
Mysql数据库cpu飙高的处理
Linux top命令详解：持续监听进程运行状态 (biancheng.net)
使用top命令观察是谁导致cpu飙高’
如果是的可以使用mysqldumpslow查看数据库sql情况，然后再用explain查看执行计划，最后kill线程
