三、数据库

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...on...left join...where...group by...avg()/sum()...having..select...

order by...asc/desc...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.索引

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

概念：

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

优点：

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

1. 索引的分类（必会）

       1.普通索引：最基本的索引，它没有任何限制。

       2.唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

       3.主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般primary key 来约束。

       4.联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。

       5.全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

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

1. 索引结构

索引是在Mysql的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引类型的, Mysql目前提供了以下4种索引:

B+Tree 索引: 最常见的索引类型, 大部分索引都支持B+树索引.

Hash 索引: 只有Memory引擎支持, 使用场景简单.

1. Tree索引(空间索引): 空间索引是MyISAM引擎的一个特殊索引类型, 主要地理空间数据, 使用也很少.

Full-text(全文索引): 全文索引也是MyISAM的一个特殊索引类型, 主要用于全文索引, InnoDB从Mysql5.6版本开始支持全文索引.

2. 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, 中间节点分裂父节点,两边节点分裂.

3.B+Tree 结构

B+Tree为BTree的变种, B+Tree与BTree的区别:

1.B+Tree的叶子节点保存所有的key信息, 依key大小顺序排列.

2.B+Tree叶子节点元素维护了一个单项链表.

所有的非叶子节点都可以看作是key的索引部分.

MySql中的B+Tree索引结构示意图:

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

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是大于号, 范围查询.

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

比如在索引上使用切割函数, 就会使索引失效.

select * from tb_seller where substring(name, 3, 2) = "科技";

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

如果索引列是字符串类型的整数, 条件查询的时候不加引号会造成索引失效. Mysql内置的优化会有隐式转换.

索引失效案例

select * from tb_seller where name = "小米科技" and status = 1

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

如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用select *

select sellerid, name, status from
 tb_seller where name = "小米科技" and
 staus = "1" and address = "西安市"; 

5.or关键字连接

用or分割开的条件, 如果or前面的列有索引, or后面的列没有索引, 那么查询的时候前后索引都会失效

如果一定要用or查询, 可以考虑下or连接的条件列都加索引, 这样就不会失效了.

索引失效案例:

select * from tb_seller where 
name = "小米科技" 
or createTiem = "2018-01-01 00:00:00";

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

1.行锁和表锁

1.主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁

行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

2.行锁 和 表锁 的区别：

表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2.悲观锁和乐观锁

（1）悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

（2）乐观锁： 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

10.MySql优化(高薪常问)

1.

定位执行效率慢的sql语句.(了解)

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

A：哪些步骤花费的成本比较高

      B：哪些步骤产生的数据量多，数据量的多少用线条的粗细表示，很直观

C：这条sql语句是否走索引

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

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

优化索引(高薪)

索引设计原则

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

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

复合索引命名规则 index_表名_列名1_列名2_列明3

比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

2.2 避免索引失效

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

Sql语句调优(高薪)

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

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

 

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

 合理的数据库设计(了解)

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

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

注意：没有最好的设计，只有最合适的设计，所以不要过分注重理论。三范式可以作为一个基本依据，不要生搬硬套。有时候可以根据场景合理地反规范化：

A：保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。

B：增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间, 前提是这个列经常被用到, 这也就是反第三范式。

C：分割表。

数据表拆分：主要就是垂直拆分和水平拆分。

水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。

垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

D: 字段设计

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