1.连接查询（必会）

1.左连接
（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
2.右连接
（右外连接）以右表为基准进行查询,右表数据会全部显示出来,右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;

2.聚合函数（必会）

1.聚合函数
SQL中提供的聚合函数可以用来统计、求和、求最值等等。
2.分类
COUNT：统计行数量
SUM：获取单个列的合计值
AVG：计算某个列的平均值
MAX：计算列的最大值
MIN：计算列的最小值

3.SQL关键字(必会)

1.分页
MySQL的分页关键词limit
SELECT FROM student3 LIMIT 2,6; 查询学生表中数据，从第三条开始显示，显示6条
2.分组
MySQL的分组关键字：group by
SELECT sex, count() FROM student3 GROUP BY sex;
3. 去重
去重关键字：distinct
select DISTINCT NAME FROM student3;

4. SQL Select 语句完整的执行顺序: (必会)

查询中用到的关键词主要包含如下展示，并且他们的顺序依次为form…join…on…where…group by…avg()/sum()…having..select…distinct…
order by…limit…
from: 需要从哪个数据表检索数据
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组算结果
order by : 按照什么样的顺序来查看返回的数据

5. 数据库三范式(必会)

第一范式：1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；
第二范式：2NF 唯一性，一张表只说一件事，是对记录的惟一性约束，要求记录有惟一标识，
第三范式：3NF 直接性，数据不能存在传递关系，即每个属性都跟主键有直接关系，而不是间接关系。

6. 存储引擎 （高薪常问）

1.MyISAM存储引擎

主要特点：
MySQL5.5版本之前的默认存储引擎
支持表级锁（表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；
不支持事务，外键。
适用场景：对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。
特点：
1、不支持事务、外键
2、每个myisam在磁盘上存储为3个文件，文件名和表名相同，扩展名分别是
.frm ———-存储表定义
.MYD ————MYData，存储数据
.MYI ————MYIndex，存储索引

2.InnoDB存储引擎

主要特点：
MySQL5.5版本之后的默认存储引擎；
支持事务；
支持行级锁（行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁）；
支持聚集索引方式存储数据。

7.数据库事务（必会）

1.事务特性

原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态
隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

2.隔离级别

（1）读未提交（read Uncommited）:
在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用， 安全性太差；
(2) 读已提交（read commited）:
这是大多数数据库默认的隔离级别，但是不是MySQL的默认隔离级别；这个隔离级别满足了简单的隔离要求：一个事务只能看见已经提交事务所做的改变，所以会避免脏读问题；由于一个事务可以看到别的事务已经提交的数据，于是随之而来产生了不可重复读和虚读等问题（下面详细介绍这种问题，结合问题来理解隔离级别的含义）；
(3 ) 可重复读（Repeatable read）：
这是MySQL的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

(4) 可串行化（serializable）：
事物的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个；

8.索引

4 索引的概念和优点（了解）

概念：
索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。
优点：

  1. 创建唯一性索引，保证数据库表中每一行数据的唯一性
  2. 大大加快数据的检索速度，这也是创建索引的最主要的原因
  3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

    5 索引的分类（必会）

    1.普通索引：最基本的索引，它没有任何限制。
    2.唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
    3.主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般primary key 来约束。
    4.联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
    5.全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

6 索引的底层实现原理（高薪常问）

  1. 索引结构

索引是在Mysql的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的, Mysql目前提供了以下4种索引:
B+Tree 索引: 最常见的索引类型, 大部分索引都支持B+树索引.
Hash 索引: 只有Memory引擎支持, 使用场景简单.

  1. Tree索引(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.
  2. Full-text(全文索引): 全文索引也是MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB从Mysql5.6版本开始支持全文索引.
    7、Mysql - 图1
  3. BTree结构

B+Tree是在BTree基础上进行演变的, 所以我们先来看看BTree, BTree又叫多路平衡搜索树, 一颗m叉BTree特性如下:

  1. 树中每个节点最多包含m个孩子.
  2. 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil函数指向上取整).
  3. 若根节点不是叶子节点, 则至少有两个孩子.
  4. 每个非叶子节点由n个Key和n+1个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1.

以5叉BTree为例, key的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1.
所以 2 <= n <= 4, 中间节点分裂为父节点，两边节点分裂为子节点.
7、Mysql - 图2
3.B+Tree 结构
B+Tree为BTree的变种, B+Tree与BTree的区别:
1.B+Tree的叶子节点保存所有的key信息, 依key大小顺序排列.
2.B+Tree叶子节点元素维护了一个单项链表.
所有的非叶子节点都可以看作是key的索引部分.
7、Mysql - 图3
由于B+Tree只有叶子节点保存key信息, 查询任何key都要从root走的叶子. 所以B+Tree查询效率更稳定.
Mysql中的B+Tree
MySql索引数据结构对经典的B+Tree进行了优化, 在原B+Tree的基础上, 增加了一个指向相邻叶子节点的链表指针, 就形成了带有顺序指针的B+Tree, 提高区间访问的性能.
MySql中的B+Tree索引结构示意图:
7、Mysql - 图4

7 如何避免索引失效（高薪常问）

  1. 范围查询, 右边的列不能使用索引, 否则右边的索引也会失效.

索引生效案例
select from tb_seller where name = “小米科技” and status = “1” and address = “北京市”;
select from tb_seller where name = “小米科技” and status >= “1” and address = “北京市”;
索引失效案例
select * from tb_seller where name = “小米科技” and status > “1” and address = “北京市”;
address索引失效, 因为status是大于号, 范围查询.

  1. 不要在索引上使用运算, 否则索引也会失效.

比如在索引上使用切割函数, 就会使索引失效.
select * from tb_seller where substring(name, 3, 2) = “科技”;

  1. 字符串不加引号, 造成索引失效.

如果索引列是字符串类型的整数, 条件查询的时候不加引号会造成索引失效. Mysql内置的优化会有隐式转换.
索引失效案例
select * from tb_seller where name = “小米科技” and status = 1

  1. 尽量使用覆盖索引, 避免select *, 这样能提高查询效率.

如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用select *
select sellerid, name, status from tb_seller where name = “小米科技” and staus = “1” and address = “西安市”;

  1. or关键字连接

用or分割开的条件, 如果or前面的列有索引, or后面的列没有索引, 那么查询的时候前后索引都会失效
如果一定要用or查询, 可以考虑下or连接的条件列都加索引, 这样就不会失效了.
索引失效案例:
select * from tb_seller where name = “小米科技” or createTiem = “2018-01-01 00:00:00”;
7、Mysql - 图5

9.数据库锁(高薪常问)

1.行锁和表锁

1.主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁
行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
2.行锁 和 表锁 的区别：
表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.悲观锁和乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

10.MySql优化(高薪常问)

7、Mysql - 图6

  1. 定位执行效率慢的sql语句.(了解)
  • 命令:show status like ‘Com__‘,通过这条命令, 我们可以知道当前数据库是以查询为主还是更新为主. 如果是查询为主, 就重点查询; 如果增删改多就重点优化写入操作.
  • explain + sql语句查询sql执行过程, 通过执行计划，我们能得到哪些信息：

A：哪些步骤花费的成本比较高
B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观
C：这条sql语句是否走索引

  • show profile分析SQL,可以查看所有sql语句的执行效率(所用时间). 前提是这个命令需要被打开, 严格的说也就是打开这个命令后执行的所有sql语句, 它都能记录下执行时间, 并展示出来. 可以通过这个命令分析哪些sql语句执行效率低. 耗时长, 就更有针对性的优化这条sql.
  • 慢查询日志(常用的工具)

慢查询日志记录了所有执行时间超过参数 long_query_time的sql语句的日志, long_query_time默认为10秒(可以通过配置文件设置), 日志保存在 /var/lib/mysql/目录下, 有个slow_query.log文件,

2) 优化索引(高薪)

2.1 索引设计原则
索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.

  • 对查询频次较高, 且数据量比较大的表, 建立索引.
  • 索引字段的选择, 最佳候选列应当从where子句的条件中提取, 如果where子句中的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
  • 使用唯一索引, 区分度越高, 使用索引的效率越高.
  • 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率.
  • 使用短索引, 提高索引访问时的I/O效率, 因此也相应提升了Mysql查询效率.
  • 如果where后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法则, N个列组合而成的复合索引, 相当于创建了N个索引.

复合索引命名规则 index表名列名1列名2列明3
比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)
2.2 避免索引失效

  • 如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开始, 并且不能跳过索引中的列.
  • 尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  • 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
  • 不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数.计算表达式等, 都会是索引失效.
  • 查询 like，如果是 ‘%aaa’ 也会造成索引失效.
  • 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

3) Sql语句调优(高薪)

  • 根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率.
  • 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.
  • where条件字段上需要建立索引, 但Where条件上不要使用运算函数，以免索引失效.
  • 排序字段上, 因为排序效率低, 添加索引能提高查询效率.
  • 优化insert语句: 批量列插入数据要比单个列插入数据效率高.
  • 优化order by语句: 在使用order by语句时, 不要使用select *, select 后面要查有索引的列, 如果一条sql语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升序或同时用降序.
  • 优化group by语句: 在我们对某一个字段进行分组的时候, Mysql默认就进行了排序, 但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止排序, 使用order by null禁用.

select age, count(*) from emp group by age order by null

  • 尽量避免子查询, 可以将子查询优化为join多表连接查询.

4) 合理的数据库设计(了解)
根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询, 遵循数据库三范式：

  1.   1. **第一范式**：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
  2.   1. **第二范式**：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键； 
  3.   1. **第三范式**：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。

注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：
A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。
C：分割表。
数据表拆分：主要就是垂直拆分和水平拆分。
水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
D: 字段设计

  1. 表的字段尽可能用NOT NULL
  2. 字段长度固定的表查询会更快
  3. 把数据库的大表按时间或一些标志分成小表

    11.深入了解Mysql原理

    1)mysql的底层架构
    了解底层架构是什么样子?select name from user where id =1
    1、SQL接口:sql的入口，当线程监听到 sql发送过来之后，mysql其实根本不知道这是啥意思，他会把sql交给sql的入口,sql接口
    2、SQL解析器：相当于知道这条sql到底想干嘛这条sql 想从user表中 去拿id =1 的 name的这个字段 ，但是问题在于怎么样去把这个事给做好呢？
    3、SQL优化器：选择一个最优解两种做法：
    1. 先提取出来所有user表的数据，然后再去过滤出来id=1 的这条记录，然后再去拿name字段的值 2. 先提取出来所有user表的数据，去拿到所有的name字段的值，然后再去找这么多字段里边id = 1的这个字段通过咱们sql优化器，他就选取一个最优的获得数据方式去拿到这个数据
    4、你要知道咱们这个数据库其实说白了，他还是要在去操作磁盘数据，或者是内存数据，那么现在就有问题，你已经知道做什么事情，怎么做这件事情，但是呢，你只是站在sql的角度上明白，比如说这个数据到底在哪儿呢？这些问题其实有待解决，我们就可以使用 存储引擎
    7、Mysql - 图7
    2)mysql的undolog和redolog还有binglog分别是什么？
    只要我操作了数据，我们mysql就会将数据从磁盘上边读取到innodb 引擎中buffer pool，以后如果要读取相同数据，此时其实只需要到Buffer pool里边去读取undolog redolog binglog 日志update user set name =b where id = 1比如：当你这把id =1 name =a 这条记录加载到内存，你把这条记录改变了b，假设你现在想要事务的回滚—>b 回滚成a的样子，mysql其实当把这个数据 id =1 name =a 加载到内存来之后 ，现在就会把这个原来的值记录到undolog日志中但是假如说：你现在呢事务已经提交了，但是此时宕机了，那么此时buffer pool 里边数据不就丢了吗？如果丢了的话，那你怎么能说你的事务没有问题呢？所以在完成前三步之后，就会基于 redolog的落盘策略去保证 就是说事务提交之后，然后当前修改过的数据必须要落盘binglog日志文件，他其实也是用于恢复数据时候但是他和redolog不一样的点： 三个点
    1.binglog日志他是基于mysql级别的，但是redolog其实他是基于innodb引擎，也就是说，如果不是innodb引擎那么他其实就没有redolog日志
    2.binglog 他可以用户删除库删除表之后的数据恢复，还可以用于 主从之间的 数据同步
    3.binglog 他是没有大小限制的，这个redolog日志大小是有限的， 如果超过限定的量，他这个时候，就会出现覆盖，而我们binglog日志其实他是一个文件接着一个文件的写，此时这个数据其实就不会覆盖咱们这里边1,2,3,4 属于非事务操作咱们 5,6 其实上他们已经属于事务操作了
    7、Mysql - 图8
    8 sql优化(重要)
    一、第一定位慢sql
    两种可能
    1、执行完了的
    通过慢查询日志 ：在慢查询日志中可以规定超过多长时间可以作为慢sql利用mysqldumpslow来分析慢查询日志
    2、没有执行完的
    并show profile查看哪些sql执行时间比较慢，并kill进行分析
    二、通过以上两种方式定位出来后
    利用explain先查看索引是否失效possible_keys ：判断你可能使用到了索引key：具体用到了哪个索引
    看possible_keys 是否有值
    1、没有值：大概率是没有创建索引，为其创建索引
    2、possible_keys:有值 但是key没有值索引失效了
    失效的原因：（随便记几个常见的）
    0.没有使用到最左匹配原则(重点)
    1.like：%号在前索引会失效
    2.用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 3. in 走索引， not in 索引失效
    4. varchar字段不带’’ 也会失效
    5.在索引列上进行运算操作
    6.范围查询右边的列，不能使用索引

三、如果有创建索引，但性能依然低
1、尽量对于要查询的字段使用复合索引,并且利用覆盖索引,减少回表的查询
2、然后是复合索引，mysql5.6以后对复合索引还做了进一步的优化，索引下推，可以通过索引包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数(索引下推，需要学习一下)原来他查询辅助列索引时，会忽略掉一列，然后，再拿到结果后，再去向主键列索引去发起多次回表，筛选另一列条件，而现在，一次就从辅助列中拿到数据了
3、考虑索引的过滤性，对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快，如果过滤性因为业务确实不高，但业务查询频繁，也要作为联合索引的组成部分,因为如果没有索引，此时你的sql就必须将这些查询条件放在最后，当利用联合索引查出其他数据后，还需要将这些数据再次加载到内存去过滤掉 这些非索引数据
4、调大buffer pool
5、尽量最大限度的使用到最左匹配原则，让所有字段都匹配上，如果有用户发起的sql不满足sql匹配原则，
比如：sex字段，用户在没有查询的时候，我们可以在sql 处凭借 sex in( 0,1 ) 使不满足最左匹配原则的数据，匹配上完整索引
6、对于辅助索引的查询，会进行回表查询，并且磁盘是进行随机io读写，这时候可以开启MRR，mrr可以在回表的时候，mrr可以将随机io读变成了顺序io读，提高效率。 MRR，全称「Multi-Range Read Optimization」。 简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。