C++服务端面试准备（2）MySQL相关

声明：本文内容纯属博主自己查找和归纳的个人所需的知识点，仅作参考，如有错误，博主强烈希望您指出。如果您是某个知识点的原创博主，如有需要，可联系本人加上链接。本文内容会根据博主所需进行更新，希望大家多多关照。

MySQL底层数据结构

       B+树

存储引擎之二：InnoDB与Myisam

	InnoDB	Myisam
事务	支持	不支持
外键	支持	不支持
锁	表锁、行锁	表锁
总行数	需要扫描整个表	直接读取
文件	.idb数据文件和它的.frm文件，表的大小只受限于操作系统文件的大小，一般为 2GB	.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
使用环境	都符合要求的情况下，改动多的时候	都符合要求的情况下，查找多的时候
自增长	必须为索引，计数器存在内存，8.0版本后写入到redo log中	没有限制，还能建立联合索引

范式

       范式背景：
       1.解决数据冗余
       2.数据的优化与存储
       3.保存数据之后，可以通过关系得到的数据，不再存储

• 第一范式：字段能直接拿来用，不需要分割或者其他操作，即原子性
• 第二范式：第二范式就是在满足第一范式的前提下，解决部分依赖的问题，比如某些字段只依赖复合主键的其中一个，解决的方法是使用逻辑主键等，使这些字段直接或者间接依赖主键，但是会出现传递依赖的问题
• 第三范式：第三范式就是在满足第二范式的前提下，解决传递依赖的问题，也就是间接依赖主键，解决的方法是将存在传递依赖的字段以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应信息的时候，使用对应的实体表的主键加进来

连接

• 内连接：获取两个表相同字段都有的数据

       SELECT a.xxx, b.xxx FROM table1 a INNER JOIN table2 b ON a.xxx = b.xxx;
       等价于
       SELECT a.xxx, b.xxx FROM table1 a, table2 b WHERE a.xxx = b.xxx;

• 左连接：获取左表指定的数据，即使右表没有

              SELECT a.xxx, b.xxx FROM table1 a LEFT JOIN table2 b ON a.xxx = b.xxx;

• 右连接：获取右表指定的数据，即使左表没有

       SELECT a.xxx, b.xxx FROM table1 a RIGHT JOIN table2 b ON a.xxx = b.xxx;

• UNION，UNION ALL：将两个或多个表中相同列的数据展示出来

       SELECT column FROM table1 UNION SELECT column FROM table2 ORDER BY column；

• 注意UNION的列数和顺序要相同，类型最好也相同，而内、外连接SELECT里没硬性要求

存储过程和自定义函数的区别

1. 存储过程能有多个返回值，而函数只能有一个返回值
2. 存储过程声明时不需要描述返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句
3. 存储过程的参数有IN、OUT、INOUT类型，而函数只有IN类型
4. 存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为sql语句的一个部分来调用
5. 一般来说，存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强

• 存储过程的好处

       存储过程说白了就是把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要的时候从数据库中直接调用,省去了编译的过程.，提高了运行速度， 同时降低网络数据传输量（传输名字和参数即可）

什么是事务

       事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务的四大特性

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中

索引

• 索引分为两大类：B树类型索引和Hash类型索引
• B树类型索引分B+树和B-树，InnoDB和Myisam支持B树类型索引，不支持Hash类型索引
• B+树层数越多，数据量指数值增长，InnoDB和Myisam默认使用
• Hash索引查询单条快，范围查询慢，Memory、NDB支持
• 常用索引：

       普通索引INDEX、唯一索引UNIQUE INDEX、主键索引PRIMARY KEY、组合索引INDEX(column1, column2, ...)和全文索引FULLTEXT INDEX

全文索引

       1. 只支持 char、varchar、text 类型；

       2. 性能比like高很多，但可能存在精度问题。

       3. 表中必须有 4 条以上的记录

       4. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引

       5. 自然全文索引：SELECT xxx FROM table WHERE MATCH(fulltext_column) AGAINST('info');

       6. 布尔全文索引：SELECT xxx FROM table WHERE MATCH(fulltext_column) AGAINST('info' in boolean mode);

       info里使用修饰符：
       '+'：必须包含该词
       '-'：必须不包含该词
       '>'：提高该词的相关性，查询的结果靠前
       '<'：降低该词的相关性，查询的结果靠后
       '*'：通配符，只能接在词后面

       7. 查看引擎搜索长度：show variables like '%ft%';

       8. 配置搜索长度：打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面，根据查看引擎搜索长度的格式，追加相应内容，并且重启MySQL，输入修复命令repair table test quick;

视图及其优点

• 视图是一个虚拟表，实质是一条预编译的SQL语句，并不保存实际数据
• 视图通常只用作查询，避免修改操作。若进行修改操作，单表的视图会将修改同步到源表，多表的视图修改可能会出错，因此修改数据操作应在表上进行

优点：

• 简单。对使用视图的用户来说视图已经是过滤好的复合条件的结果集，完全不需要关心后面对应的表的结构、关联条件和筛选条件
• 安全。使用视图的用户只能访问他们被允许查询的特定的行或列，也就是视图本身的内容，不会涉及到视图以外的数据
• 数据独立。一旦视图的结构确定了，就相当于有两个表，用户使用视图可以屏蔽源表结构变化的影响，源表发生变化时，可以修改视图同步变化

视图与临时表的区别

1. 视图实质是一条预编译的SQL语句，不保存实际数据，而临时表是个真实的表
2. 视图只存在于单个查询当中，每次使用该视图名，其虚拟表就会根据现有的数据重新被创建，而临时表只存在于它被创建的整个数据库会话过程中
3. 视图的内容是在该视图每次被使用的时候才生成，所以数据和数据库中的数据是同步的，而临时表一经创建只能手动更新
4. 建立视图时可将原本数据隐藏起来换成另一种表达形式，更为安全还能改善应用体验，而临时表只能直接复制

应用场景

• 视图用于查询多的情况，需要安全性能更高的情况
• 临时表用于更新多的情况，或者需要复制表的情况

SQL实现数据表的复制

       select into from 和insert into select都是用来复制表.
       两者的主要区别为：
       select into from 要求目标表不存在，因为在插入时会自动创建
       insert into select from 要求目标表存在
       两者的语法：
       SELECT vale1, value2 into Table2 from Table1
       Insert into Table2(field1,field2,...) select value1,value2,... from Table1

NULL值的处理

• IS NULL：变量为NULL
• IS NOT NULL：变量不为NULL
• <=>：这才是MySQL语句的比较操作符