作为SQL Boy，基础部分不会有人不会吧？面试也不怎么问，基础掌握不错的小伙伴可以跳过这一部分。当然，可能会现场写一些SQL语句，SQ语句可以通过牛客、LeetCode、LintCode之类的网站来练习。
MySQL的连接主要分为内连接和外连接，外连接常用的有左连接、右连接。
三大范式的作用是为了控制数据库的冗余，是对空间的节省，实际上，一般互联网公司的设计都是反范式的，通过冗余一些数据，避免跨表跨库，利用空间换时间，提高性能。
char：
varchar：
日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。
相同点：
网站资讯
渠道合作
解决方案
更多服务YYYY-MM-DD HH:MM:SS区别：
日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC到 ``2038-01-09 03:14:07.999999 UTC
存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)
MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。我们可能认为exists比in语句的效率要高，这种说法其实是不准确的，要区分情景：
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
货币在数据库中MySQL常用Decimal和Numric类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与货币有关的数据。
例如salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99。
DECIMAL和NUMERIC值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。
之所以不使用float或者double的原因：因为float和double是以二进制存储的，所以有一定的误差。
MySQL可以直接使用字符串存储emoji。
但是需要注意的，utf8 编码是不行的，MySQL中的utf8是阉割版的 utf8，它最多只用 3 个字节存储字符，所以存储不了表情。那该怎么办？
需要使用utf8mb4编码。
alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;
三者都表示删除，但是三者有一些差别：
| delete | truncate | drop | |
|---|---|---|---|
| 类型 | 属于DML | 属于DDL | 属于DDL |
| 回滚 | 可回滚 | 不可回滚 | 不可回滚 |
| 删除内容 | 表结构还在，删除表的全部或者一部分数据行 | 表结构还在，删除表中的所有数据 | 从数据库中删除表，所有数据行，索引和权限也会被删除 |
| 删除速度 | 删除速度慢，需要逐行删除 | 删除速度快 | 删除速度最快 |
因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。
执行效果：
执行速度：
FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积（Cartesianproduct），产生虚拟表VT1
ON：对虚拟表VT1应用ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2中
JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止
WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入虚拟表VT4中
GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5
CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6
HAVING：对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才被插入虚拟表VT7中。
SELECT：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中
DISTINCT：去除重复数据，产生虚拟表VT9
ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10。11）
LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户
MySQL逻辑架构图主要分三层：
是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存 (MySQL8.0 版本以前)。语法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。调用数据库引擎接口，返回执行结果。
主要存储引擎以及功能如下：
| 功能 | MylSAM | MEMORY | InnoDB |
|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB |
| 支持事务 | No | No | Yes |
| 支持全文索引 | Yes | No | Yes |
| 支持树索引 | Yes | Yes | Yes |
| 支持哈希索引 | No | Yes | Yes |
| 支持数据缓存 | No | N/A | Yes |
| 支持外键 | No | No | Yes |
MySQL5.5之前，默认存储引擎是MylSAM，5.5之后变成了InnoDB。
InnoDB支持的哈希索引是自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。
MySQL 5.6开始InnoDB支持全文索引。
大致上可以这么选择：
使用哪一种引擎可以根据需要灵活选择，因为存储引擎是基于表的，所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。
PS:MySQL8.0都开始慢慢流行了，如果不是面试，MylSAM其实可以不用怎么了解。
1. 存储结构：每个MyISAM在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2. 事务支持：MyISAM不提供事务支持；InnoDB提供事务支持事务，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全特性。
3 最小锁粒度：MyISAM只支持表级锁，更新时会锁住整张表，导致其它查询和更新都会被阻塞InnoDB支持行级锁。
4. 索引类型：MyISAM的索引为聚簇索引，数据结构是B树；InnoDB的索引是非聚簇索引，数据结构是B+树。
5. 主键必需：MyISAM允许没有任何索引和主键的表存在；InnoDB如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见) ，数据是主索引的一部分，附加索引保存的是主索引的值。
6. 表的具体行数：MyISAM保存了表的总行数，如果select count() from table;会直接取出出该值; InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表;但是在加了wehre条件后，MyISAM和InnoDB处理的方式都一样。
7. 外键支持：MyISAM不支持外键；InnoDB支持外键。
MySQL日志文件有很多，包括 ：
还有两个InnoDB存储引擎特有的日志文件：
更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志。
执行器先找引擎获取ID=2这一行。ID是主键，存储引擎检索数据，找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。
不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入。
为什么要两阶段提交呢？直接提交不行吗？
我们可以假设不采用两阶段提交的方式，而是采用“单阶段”进行提交，即要么先写入redo log，后写入binlog；要么先写入binlog，后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。
先写入redo log，后写入binlog：
在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。
先写入binlog，后写入redo log：
写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2这一行的数据并没有更新。
简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。
什么时候会刷入磁盘？
在如下的一些情况中，log buffer的数据会刷入磁盘：
log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
重做日志缓存、重做日志文件都是以块（block） 的方式进行保存的，称之为重做日志块（redo log block） ,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。
它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。
其中有两个标记位置：
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。
当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。
所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。
慢SQL的监控主要通过两个途径：
慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。
这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。
在数据量比较大，分页比较深的情况下，需要考虑分页的优化。
例如：
select * from table where type = 2 and level = 9 order by id asc limit 190289,10;
优化方案：
延迟关联
先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行
例如：
select a.* from table a,
(select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
where a.id = b.id
书签方式
书签方式就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit
例如：
select * from table where id >
(select * from table where type = 2 and level = 9 order by id asc limit 190
合理地设计和使用索引，是优化慢SQL的利器。
利用覆盖索引
InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引
例如对于如下查询：
select name from test where city='上海'
我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取
alter table test add index idx_city_name (city, name);
低版本避免使用or查询
在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。
避免使用 != 或者 <> 操作符
SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引
解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描
例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了
适当使用前缀索引
适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
alter table test add index index2(email(6));
PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引
避免列上函数运算
要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率
select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;
正确使用联合索引
使用联合索引的时候，注意最左匹配原则。
优化子查询
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大
小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。
比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。
select name from A left join B ;
适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略
避免使用JOIN关联太多的表
《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。
如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。
利用索引扫描做排序
MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的
但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行
例如：
--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序
条件下推
MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。
explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。
直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。
id 列：MySQL会为每个select语句分配一个唯一的id值
select_type 列，查询的类型，根据关联、union、子查询等等分类，常见的查询类型有SIMPLE、PRIMARY。
table 列：表示 explain 的一行正在访问哪个表。
type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。
性能从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system
system： 当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快
const
const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
eq_ref
eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。
ref_or_null
ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
index_merge
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery
unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
index_subquery
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range
range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
index
index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。
ALL
就不用多说了，全表扫描。
possible_keys 列：显示查询可能使用哪些索引来查找，使用索引优化sql的时候比较重要。
key 列：这一列显示 mysql 实际采用哪个索引来优化对该表的访问，判断索引是否失效的时候常用。
key_len 列：显示了 MySQL使用
ref 列：ref 列展示的就是与索引列作等值匹配的值，常见的有：const（常量），func，NULL，字段名。
rows 列：这也是一个重要的字段，MySQL查询优化器根据统计信息，估算SQL要查到结果集需要扫描读取的数据行数，这个值非常直观显示SQL的效率好坏，原则上rows越少越好。
Extra 列：显示不适合在其它列的额外信息，虽然叫额外，但是也有一些重要的信息：
索引可以说是MySQL面试中的重中之重，一定要彻底拿下。
从三个不同维度对索引分类：
例如从基本使用使用的角度来讲：
传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。
在我们添加完索引之后，MySQL一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，在比较小的索引数据里查找，然后映射到对应的数据，能大幅提升查找的效率。
和我们通过书的目录，去查找对应的内容，一样的道理。
索引虽然是sql性能优化的利器，但是索引的维护也是需要成本的，所以创建索引，也要注意：
索引应该建在查询应用频繁的字段
在用于 where 判断、 order 排序和 join 的(on)字段上创建索引。
索引的个数应该适量
索引需要占用空间；更新时候也需要维护。
区分度低的字段，例如性别，不要建索引。
离散度太低的字段，扫描的行数降低的有限。
频繁更新的值，不要作为主键或者索引
维护索引文件需要成本；还会导致页分裂，IO次数增多。
组合索引把散列性高(区分度高)的值放在前面
为了满足最左前缀匹配原则
创建组合索引，而不是修改单列索引。
组合索引代替多个单列索引（对于单列索引，MySQL基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
过长的字段，使用前缀索引。 当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
不建议用无序的值(例如身份证、UUID )作为索引
当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化
当然不是。
MySQL的默认存储引擎是InnoDB，它采用的是B+树结构的索引。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：support@lcayun.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
