MySQL基础篇

数据库引擎

什么是数据库引擎？
1、 数据库引擎：用于存储、处理、保护数据的核心服务。
2、 当你访问数据库时，不管是手工访问，还是程序访问，都不是直接读写数据库文件，而是通过据库引擎去访问数据库文件。
3、 常见的两种存储引擎：MyISAM和InnoDB。

MyIsAM和InnoDB区别？
构成上，MyIsAM的表在磁盘中有三个文件组成，分别是表定义文件（.frm）、数据文件（.MYD）、索引文件（.MYI），而InnoDB的表由表定义文件（.frm）、索引和数据文件（.ibd）组成。
安全方面，MyIsAM强调的是性能，其查询效率较高，但不支持事务和外键等安全性方面的功能，而InnoDB支持事务和外键等高级功能，查询效率稍低。
对锁的支持，MyIsAM只支持表锁，而InnoDB支持表锁和行锁。

如何选择合适的引擎
1、 是否要支持事务，需要选择InnoDB，不需要可以考虑MyIsAM。
2、 如果表中绝大多数只是查询，可以考虑MyIsAM，如果既有读也有写，使用InnoDB。
3、 系统崩溃后，MyIsAM恢复起来比较困难，是否可以接受。
4、 MySql5.5版本开始InnoDB已经成为默认引擎，说明优势是有目共睹的，如果不知道用啥，就用InnoDB，至少不会差。

索引

索引本质
索引是帮助MySQL高效获取数据的排好序的数据结构。

索引作用
提高数据的检索速度。

索引数据结构
二叉树、红黑树、Hash表、B Tree、B+Tree

B树：
1、所有键值分布在整个树中。
2、任何关键字出现且只出现在一个节点中。
3、搜索有可能在非叶子节点结束。

B+树：在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
1、所有的关键字都存储在叶子节点，非叶子节点不存储真正的data。
2、为所有叶子节点增加了一个链指针。

索引种类
1、普通索引：无限制。
2、唯一索引：列值唯一，可以为null。
3、主键索引：列值唯一，表中只有一个，不能为null 。
4、组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（例：索引行中按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)、(id)、(id，age)只有id可以使用，如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如(name，age)、(age)组合就不会使用索引查询。
5、全文索引：只有在MyIsAM引擎上才能使用，只能在char、varchar、text类型字段上使用全文索引，针对模糊查询LIKE提高查询效率（5.7版本后InnoDB支持全文索引）。

为什么InnoDB必须有主键
表数据文件本身就是按B+Tree组织的一个索引结构文件，如果不建主键，MySQL会自动从第一列开始找数据唯一的列做为主键索引维护B+Tree索引结构文件，如果找不到，MySQL会在后台底层自动创建隐藏列，会影响MySQL性能。

索引性能分析
Explain（执行计划）
使用：EXPLAIN + sql语句；

查询索引：
desc 表名（缺点：不显示索引名）
show index from 表名
show keys from 表名

索引回表
B+树索引主要可以分为两种索引：聚集索引和非聚集索引。
聚集索引：也就是平常我们说的主键索引，在B+树中叶子节点存的是整行数据。
非聚集索引：也叫二级索引，也就是一般的普通索引，在B+树中叶子节点存的是主键的值。
如果直接用主键查找，用的是聚集索引，能找到全部数据。如果用非聚集索引，并且索引里不包含全部要查找的字段，则需要根据索引叶子节点存的主键值，再到聚集索引里查找需要的字段，这个过程就是索引回表。
避免方法：在满足需求的情况下，尽量使非聚集索引里有要查询的索引字段，建立两列以上的索引，即可查询覆盖索引里的列的数据而不需要进行回表二次查询。

建立索引的原则
1、定义主键的数据列一定要建立索引；
2、定义有外键的数据列一定要建立索引；
3、对于经常查询的数据列最好建立索引；
4、经常出现在关键字order by、group by后面的字段，建立索引；
5、限制表上的索引数目，对一个存在大量更新操作的表，一般不要超过3个，最多不要超过5个；

索引失效
1、索引列不独立（在索引列做了计算、函数、类型转换等操作）；
2、使用了左模糊查询（’%aa’）；
3、使用了or查询；
4、字符串条件没有使用单引号；
5、不符合最左前缀原则的查询；
6、索引字段没有添加 not null 约束；
7、关联表的两个字段类型不一致发生隐式转换；

索引的优缺点
优点：
1、大大加快数据的检索速度。
2、创建唯一性索引，保证数据库表中每一行数据的唯一性。
3、加速表和表之间的连接。
4、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
1、索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。
2、当对表中的数据进行增删改时，索引也要动态的维护，会降低更新表的速度。

select执行过程
1、客户端：用来访问MySQL的server层。
2、连接器：获取登录信息后验证权限，以及连接的管理。
3、缓存：执行一条select，如果没有禁用缓存的话，MySQL会先到缓存中查找是否执行过这条SQL，有则视为命中缓存并且返回数据，没有命中则交由分析器处理。
4、分析器：对SQL进行解析，检查语法是否能正常被执行器执行。
5、优化器：当一条SQL复杂了，其实MySQL有很多执行方案，优化后的SQL有了MySQL认为高效的执行步骤后就交付给执行器。
6、执行器：执行器首先要做的是检查是否有权限访问这张表，然后再根据建表时选用的存储引擎去调用该存储引擎的读写接口。

NULL值
NULL 表示未知值，这个值是未知的，NULL值查询使用is null/is not null查询。

什么是存储过程？
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

数据类型

char和varchar区别？
1、 char长度是固定的，插入长度小于定义长度，则用空格填充，varchar长度是可变的。
2、 char每次修改的数据长度相同，效率高，占用空间大，varchar每次修改的数据长度不同，效率低，占用空间小。
3、 存储容量不同，char最大容量255个字符，varchar最大容量65532个字符

为表中的字段选择合适的数据类型
1、当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期和二进制类型，最后是字符类型。
2、对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

Union与Union all 用法区别
使用Union语句，两个表的结构必须相同。
用于合并两个或多个 SELECT 语句的结果集。
Union只会选取不同的值，并且还会自动排序。

in 和 exists的区别
in后面的子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表去，符合要求的输出，反之则不输出。
exists后面的子查询不返回结果集，只返回一个true或false，其运行方式是先运行主查询一次，再根据主查询中的每一行去子查询里去查询，如果存在是true则输出，反之则不输出。

得出结论：
1、in适合B表比A表数据小的情况；
2、exists适合B表比A表数据大的情况；
3、当A表数据与B表数据一样大时，in与exists效率差不多,可任选一个使用。

MySQL 中的锁

什么是锁？MySQL 中提供了几类锁？
锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能正常运行。
InnoDB支持表级锁和行级锁，默认为行级锁，MyISAM只支持表级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

MySQL中InnoDB引擎的行锁是怎么实现的？
InnoDB是基于索引来完成行锁。
例：select * from table where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁。