SQL必知必会

SQL语言

SQL已经无处不在

对于不同职位的人来说都有价值：产品经理、开发人员、运营人员、数据分析师等

对于不同的技术来说，掌握SQL也很有价值：OLTP，OLAP，NoSQL，用于XML的SQL，用于JSON的SQL，用于地理位置信息的SQL，用于搜索的SQL，用于时间序列数据的SQL，用于流的SQL等

关注半衰期长的语言

与其关注那些变化的事，不如关注哪些事情是不变的

SQL语言很少变化，半衰期长

程序员的半衰期是15年，而大部分人在追逐最新的技术

20%的时间产生80%的收益，先将自己的时间和精力投入到这些不变的技术身上

入门SQL并不难

SQL语言的四个部分：DDL，DML，DCL，DQL

SQL 是为数不多的声明性语言，学习SQL就像是学习英文语法一样

一旦掌握会让你在日常工作中成就感倍增

在职场上我们的价值取决于别人依赖于我们的程度，而非我们依赖于别人的程度

DBMS

DB，DBS，DBMS区别

DB的英文是DataBase，也就是数据库，它是存储数据的集合，你可以理解为多个数据表。

DBS的英文是DataBase System，数据库系统，它是更大的概念，包括了数据库和数据库管理系统，以及数据库管理人员DBA。

DBMS的英文全称是DataBase Management System，数据库管理系统，实际上它是对多个数据库的管理，DBMS = 多个数据库（DB） + 管理程序。

排名前20的DBMS

关系型数据库绝对是DBMS的主流，其中使用最多的DBMS分别是Oracle，MySQL和SQL Server。

NoSQL泛指非关系型数据库

键值型数据库
文档型数据库
搜索引擎
列式数据库
图形数据库

SQL阵营与NoSQL阵营

NoSQL最早的提出是想远离SQL，但是随着发展却发现越来越离不开SQL

NoSQL是对SQL很好的补充

SQL阵营

Oracle：第一个商用关系型数据库，企业级软件霸主

MySQL：开源数据库，被Oracle收购，MariaDB是MySQL的分支

SQL Server：微软商用关系型数据库，同时Access是桌面数据库

SQL是如何执行的

Oracle中的SQL执行

流程：语法检查，语义检查，权限检查，共享池检查，优化器，执行器

共享池：Oracle中的术语，包括了库缓存（library cache），数据字典缓冲区等

软解析：在共享池中，首先对SQL语句进行Hash运算，然后根据Hash值在库缓存（library cache）中查找，如果存在SQL语句的执行计划，就直接拿来执行。也就是直接进入到执行器的环节。

硬解析：如果没有找到SQL语句和执行计划，就需要自己来创建解析树进行解析，生成执行计划。对应的是优化器这个步骤。

MySQL中的SQL执行

主要层次：连接层、SQL层、存储引擎层

连接层：客户端和server建立连接，客户端发送SQL至server。

SQL层：对SQL语句进行查询处理。

存储引擎层：负责数据的存储和读取，是与数据库文件打交道。

在SQL层中，会进行：解析器->优化器->执行器。8.0版本之前提供查询缓存，8.0版本之后不支持查询缓存

存储引擎：InnoDB、MyISAM、Memory、NDB、Archive等

数据库也是一种软件

从软件的思维了解执行的流程，比如通过MySQL profile来分析资源使用情况

不同的软件实现方式不同，但都有共性的地方，即：解析器->优化器->执行器

DDL

基础语法

DDL的英文全称是Data Definition Language，中文是数据库定义语言。它定义了数据库的结构和数据表的结构

对数据库进行定义：CREATE DATABASE，DROP DATABASE

对数据表进行定义

创建：CREATE TABLE table_name
修改：ALTER TABLE

可视化管理和设计工具：Navicat

数据表的常见约束

主键约束：主键起到唯一标识一条记录的作用，不能重复，不能为空。

外键约束：外键是确保了表与表之间引用完整性。一个表中的外键对应的另一张表的主键。外键可以是重复的，也可以为空。

唯一性约束：唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。

NOT NULL约束：字段的为空约束

DEFAULT：字段默认值

CHECK约束：用来检查特定字段取值范围的有效性

设计数据表的原则

数据表的个数越少越好

数据表中的字段个数越少越好

数据表中联合主键的字段个数越少越好

使用主键和外键越多越好

“三少一多”原则的核心就是“简单可复用”

SELECT检索数据

基础语法

SELECT检索一列，多列，所有列

使用列别名进行检索：AS

SELECT查询还可以对常数进行查询

从结果中去掉重复的行：DISTICT

排序检索数据

排序的列名：ORDER BY后面可以有一个或多个列名

排序的顺序：ORDER BY后面可以注明排序规则，ASC代表递增排序，DESC代表递减排序。

非选择列排序：ORDER BY是可以使用非选择列进行排序的

ORDER BY的位置：ORDER BY通常位于SELECT语句的最后一条子句

SELECT查询效率

尽量避免使用SELECT *

在查询过程中，我们可以约束返回结果的数量

不同DBMS约束返回数量的关键字不同

WHERE数据过滤

比较运算符

等于，不等于，小于，小于等于，大于，大于等于

在指定的两个数值之间：BETWEEN

为空值：IS NULL

逻辑运算符

如果我们存在多个WHERE条件子句，就需要使用逻辑运算符

并且：AND

或者：OR

在指定条件范围内：IN

非（否定）：NOT

使用通配符进行过滤

LIKE操作符

通配符%：匹配任意字符串出现任意次数

通配符_：匹配单个字符

尽量避免使用通配符，尤其是在LIKE后面就直接跟上通配符的情况

SQL标准&连接表

SQL标准

SQL-86，SQL-89，SQL:2003, SQL:2008, SQL:2011,SQL:2016

SQL92和SQL99是经典的SQL标准，也分别称为SQL-2和SQL-3

SQL92的标准有500页，而SQL99标准超过了1000页，只需要掌握核心内容即可

SQL92中的连接

笛卡尔积：X和Y的笛卡尔积就是X和Y的所有可能组合

等值连接：两张表的等值连接就是用两张表中都存在的列进行等值连接，结果为这两张表的交集部分。

非等值连接：进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

外连接：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

自连接：连接可以对多个表进行操作，也可以对同一个表进行操作，也就是自连接。

案例

NBA球员表，球队表，身高等级表：player, team, height_grades

掌握使用连接进行复杂查询

SQL标准&连接表

SQL标准

SQL-86，SQL-89，SQL:2003, SQL:2008, SQL:2011,SQL:2016

SQL92和SQL99是经典的SQL标准，也分别称为SQL-2和SQL-3

SQL92的标准有500页，而SQL99标准超过了1000页，只需要掌握核心内容即可

SQL92中的连接

笛卡尔积：X和Y的笛卡尔积就是X和Y的所有可能组合

等值连接：两张表的等值连接就是用两张表中都存在的列进行等值连接，结果为这两张表的交集部分。

非等值连接：进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

外连接：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

自连接：连接可以对多个表进行操作，也可以对同一个表进行操作，也就是自连接。

案例

NBA球员表，球队表，身高等级表：player, team, height_grades

掌握使用连接进行复杂查询

SQL99中如何使用连接

SQL92中的连接

交叉连接：笛卡尔积，即table1和table2的所有可能组合

自然连接：NATURAL JOIN，你可以把它理解为是SQL92中的等值连接。实际上它会帮你自动查询两张连接表中所有相同的字段进行等值连接。

ON连接：用来指定我们想要的连接条件

USING连接：可以用USING指定连接的同名字段进行等值连接

外连接：左外连接，右外连接，全外连接

自连接：自连接的原理在SQL92和SQL99中是一样的，只是表述方式不同。

SQL99与SQL92的区别

SQL92中的WHERE和SQL99中的JOIN

多表连接建议使用SQL99标准，因为层次性更强，可读性更强

SQL99在SQL92基础上提供了一些特殊语法，比如NATURAL JOIN和JOIN USING，在实际使用中还是比较常用的

不同DBMS中使用连接需要注意的地方

不是所有DBMS都支持全外连接

Oracle没有表别名AS

SQLite的外连接只有左连接

关于连接的性能问题

严格控制连接表的数量

在连接时不要忘记WHERE语句

使用自连接而不是子查询

视图

视图操作

视图就是一张虚拟表，它帮我们封装了底层与数据表的接口，我们通过设计视图的字段来定义我们想要从数据表中查询的内容

创建视图：CREATE VIEW

修改视图：ALTER VIEW

删除视图：DROP VIEW

嵌套视图：在视图之上还可以嵌套视图

如何使用视图简化SQL操作

视图的作用是：封装SQL查询，提升SQL复用率

利用视图完成复杂的连接

利用视图对数据进行格式化

使用视图和计算字段

视图 VS 临时表

视图是虚拟表，临时表是真实存在的数据表，但是是临时性的，用完了会删除

临时表只在当前连接存在，关闭连接后，临时表就会自动释放。

存储过程

如何创建存储过程

概念：存储过程可以说是由SQL语句和控制流语句构成的语句集合

创建：CREATE PROCEDURE 存储过程名称([参数列表])

删除：DROP PROCEDURE

更新：ALTER PROCEDURE

使用DELIMITER定义结束符，Navicat中不需要进行定义

存储过程的三种参数类型：IN，OUT，INOUT

流控制语句

BEGIN...END：BEGIN...END中间包含了多个语句，每个语句都是以;号为结束符

DECLARE：声明变量

SET：赋值语句

SELECT...INTO：把我们从数据表中查询的结果存放到变量中

IF...THEN...ENDIF：条件判断语句

CASE：我们在使用CASE的时候有两种方式，分别是CASE语句和CASE表达式

LOOP，LEAVE，ITERATE：LOOP是循环语句，LEAVE跳出循环，ITERATE来直接进入下一次循环

REPEAT...UNTIL...END REPEAT：循环语句，实现会先执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出

WHILE...DO...END WHILE：循环语句，和REPEAT循环不同的是，我们需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

关于存储过程的争议

有些公司对于大型项目要求使用存储过程，而有些则在手册中明确禁止使用存储过程

优点

一次编译多次使用，提升SQL的执行效率
减少开发工作量
安全性强
减少网络传输量

缺点

可移植性差
调试困难
版本管理困难
不适合高并发的场景

事务处理

事务的特性

概念：将多个数据库读或写的操作组成一个基本单元，ACID是其四大特性

A：原子性，英文是Atomicity，原子的概念就是不可分割，你可以理解它是组成物质的基本单位，也是我们进行数据库操作的基本单位。

C：一致性，英文是Consistency，一致性指的就是数据库在进行事务操作后，会由原来的一致状态（valid state），变成另一种一致的状态。

I：隔离性，英文是Isolation，它指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在commit之前，对其他事务都是不可见的。

D：持久性，英文是Durability，事务提交之后对数据的修改是持久性的。即使在系统出现故障的情况下，数据的修改依然是有效的。

事务的控制

事务的两种类型：隐式事务和显式事务

MySQL：隐式事务（默认），Oracle：显式事务（默认）

START TRANSACTION或者 BEGIN，作用是显式的开启一个事务

COMMIT，提交事务，当提交事务后，就会对数据库的修改成为永久性的

ROLLBACK或者ROLLBACK TO [SAVEPOINT]，回滚事务，就是撤销正在进行的所有没有提交的修改。或者将事务回滚到某个保存点

SAVEPOINT，在事务中创建保存点，方便后续针对保存点进行回滚，一个事务中可以存在多个保存点

RELEASE SAVEPOINT，删除某个保存点

SET TRANSACTION，设置事务的隔离级别

针对MySQL，我们还需要了解autocommit和completition_type的取值

事务的隔离级别

三种异常问题

脏读（dirty read）：读到了其他事务还没有提交的数据；

不可重复读（unrepeatable read）：对某数据进行读取发现两次读取的结果不同，也就是没有读到相同的内容。这是因为有其他事务对这个数据进行了修改或删除。

幻读（phantom read）：事务A根据条件查询得到了N条数据，但是此时事务B更改或者增加了M条符合事务A查询条件的数据，这样当事务A再次进行查询的时候发现会有N+M条数据，因此产生了幻读。

四种隔离级别

读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，也就是会产生脏读、不可重复度、幻读等情况。

读已提交，也就是只能读到已经提交的内容，可以避免脏读的情况方式，但是如果想要避免不可重复读，或者幻读，就需要我们在SQL查询的时候进行显示的加锁。

可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的。避免了“不可重复度”这种异常情况，同时也能避免脏读，但是无法避免幻读。

可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，牺牲了系统的并发性，但可以解决事务读取中所有可能出现的异常情况。

RDBMS默认隔离级别

Oracle, SQL Server：读已提交，属于在RDBMS中常见的默认隔离级别，
MySQL：可重复读

使用MySQL客户端来模拟三种异常

游标

什么是游标

在SQL中游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用。

游标是面向过程的编程方式，这与面向集合的编程方式不同

常用步骤

定义游标：DECLARE cursor_name CURSOR FOR select_statement

打开游标：OPEN cursor_name

从游标中取得数据：FETCH cursor_name INTO var_name ...

关闭游标：CLOSE cursor_name

释放游标：DEALLOCATE PREPARE

说明：不同的RDBMS的使用语法略有不同

游标性能

好处：灵活性强，可以解决复杂的数据处理问题，对数据行进行逐行扫描处理

不足：使用游标的过程中会对数据行进行加锁，当业务并发量大的时候，会影响到业务的效率。同时游标是在内存中进行的处理，会消耗系统资源，容易造成内存不足。

建议：通常游标有替代方案的时候，可以采用替代方案，如果实在绕不开有时候还是会用到游标

使用Python操作MySQL

DB API规范

作用：实现多种DBMS的统一访问，切换DBMS的成本低

主要对象：数据库连接对象connection, 数据库交互对象cursor, 数据库异常类exceptions

使用步骤：引入API模块，与数据库建立连接，执行SQL语句，关闭数据库连接

mysql-connector

MySQL 官方提供的驱动器

安装：pip install mysql-connector

connection的使用

通过指定host, user, passwd,port等参数来创建数据库连接
关闭数据库连接，使用db.close()
创建游标来操作数据库中的数据，使用db.cursor()
开启事务，使用db.begin()
事务提交及回滚，使用db.commit()和db.rollback()

cursor 的使用

执行数据库查询，使用cursor.execute(query_sql)
取出数据集中的一条数据，使用cursor.fetchone()
取出数据集中的所有行，使用cursor.fetchall()，返回一个元组tuples类型
取出数据集中的多条数据，使用cursor.fetchmany(n)，同样返回一个元组tuples
返回查询结果集中的行数，使用cursor.rowcount，如果没有查询到数据或者还没有查询则结果为-1，否则会返回查询得到的数据行数
关闭游标，使用cursor.close()

对数据表进行增删改查

使用Python对数据表进行CRUD操作

在打开数据库连接后，如果不再使用了需要关闭数据库连接，以免造成资源浪费

数据CRUD时，可能会出现异常，需要对异常信息进行捕获

SQLAlchemy

ORM框架

持久化就是将对象数据永久的存储在数据库中，ORM框架就是通过持久化层完成业务逻辑层和数据库层之间的转换

ORM的英文是Object Relation Mapping，中文叫对象关系映射。它是RDBMS和业务实体对象之间的一个映射

好处：简单可复用，不用关注底层的数据库访问细节，注意力关注到业务逻辑层。

不足：对于复杂的数据查询，采用ORM会力不从心，同时在效率上也会有损耗

Python中的ORM框架

Django：Python的WEB应用开发框架，本身是走大而全的方式，通过 models 实现数据库的增删改查操作

SQLALchemy：Python中最常用的ORM框架之一，如果你既想支持ORM方式，又想支持原生SQL，SQLALchemy是很好的选择，社区也很活跃

peewee，轻量级的ORM框架，API简单，且易于理解和使用，学习成本低

ORM与数据库的对应关系

类 <=> 数据表
类实例对象 <=> 数据行
属性 <=> 字段

SQLAlchemy使用

安装：pip install sqlalchemy

初始化数据库连接：engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/wucai')

查询数据：filter()

增加数据：session.add()，需要使用session.commit()进行提交

删除数据：先进行查询，然后session.delete()，再session.commit()进行提交

修改数据：先进行查询，然后对属性值进行修改，再进行session.commit()即可

数据库调优

调优目标

目标：让数据库运行的更快，也就响应的时间更快，同时吞吐量更大

微观反馈

用户的反馈：业务的使用者，有些问题往往是用户第一时间发现的
日志分析：通过数据库日志，操作系统日志等找出异常情况，定位问题位置

宏观反馈

服务器资源使用监控：CPU、内存、IO等使用情况
数据库内部状况监控：活动会话（Active Session）监控了解数据库是否当前处于非常繁忙的状态，是否存在SQL堆积等。还可以对事务、锁等待等进行监控

调优方法

Step1、选择适合的DBMS

Step2、表设计优化

尽量采用第三范式原则
适当进行反范式优化
表字段的数据类型选择

Step3、逻辑查询优化

采用SQL语句等价变换的方式，对查询进行重写，数学基础是关系代数。
SQL查询重写包括了：子查询优化，等价谓词重写，视图重写，条件简化，连接消除，嵌套连接消除等

Step4、物理查询优化

物理查询优化是将逻辑查询执行计划变成物理操作符，为执行器提供执行准备。
创建索引
- 如果数据重复度高，就不需要创建索引（比如重复度超过10%）
- 索引列的位置对使用索引的影响
- 联合索引对索引使用的影响
- 多个索引对索引使用的影响
确定访问路径
- 单表扫描：全表扫描，局部扫描
- 两张表的连接：嵌套循环连接，HASH连接，合并连接
- 多张表的连接：不同表的连接顺序导致不同的查询效率，及不同的搜索空间

Step5、加缓存

Redis：支持持久化，支持更多的数据类型
Memcached：适合简单的key-value存储

Step6、库级优化

原则：通过切分的方式提高单一数据库服务器的性能
主从复制：读写分离降低主数据库的负载
- master完成写操作
- slave完成读操作
使用MySQL自带的分区表
垂直切分
- 垂直分库：将一个库中的多个数据表进行拆分，相关联的数据表部署在一个库中
- 垂直分表：数据表中的列过多，采用垂直分表的方式，将数据表分拆成多张。
水平切分
- 如果数据表中的数据达到了亿级及以上，可以考虑进行水平切分，也就是大的数据表分拆成不同的子表。每张表保持相同的表结构

如何思考数据库调优

选择比努力更重要：选择不同的DBMS，数据表的设计

SQL查询优化：逻辑查询优化，物理查询优化

通过外援来增强性能：Redis/Memcached，主从复制，分库分表

设计范式

设计范式的种类

1NF（第一范式）、2NF（第二范式）、3NF（第三范式）、BCNF（巴斯范式）、4NF（第四范式）和5NF（第五范式，又叫做完美范式）

越高阶的范式数据库的冗余度越低

高阶的范式一定符合低阶范式的要求

键&属性的定义

超键，能唯一标识元组的属性集叫做超键

候选键，如果超键不包括多余的属性，那么这个超键就是候选键

主键，用户可以从候选键中选择一个作为主键

外键，如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那么这个属性集就是数据表R1的外键。

主属性：包含在任一候选键中的属性称主属性。

非主属性，与主属性相对的，指的是不包含在任何一个候选键中的属性。

三种设计范式

1NF指的是数据库表中的任何属性都是原子性的，不可再分。

2NF指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。

3NF首先需要满足2NF，然后对于任何非主属性都不传递依赖于候选键。

3NF的不足

BCNF

3NF存在的问题：即使数据表符合3NF的要求，同样可能存在插入，更新和删除的异常情况。

BCNF，也叫做巴斯范式，它在3NF的基础上消除主属性对于候选键的部分依赖或者传递依赖。

数据库与数据仓库的区别

数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据

数据库对数据的增删改实时性要求强，存储在线的用户数据，而数据仓库存储的一般是历史数据

数据库设计尽量避免冗余，但有时候为了提供查询效率也允许一定的冗余度，而数据仓库在设计上采用反范式更加常见

反范式设计

范式的目的是降低数据冗余度，而反范式会增加数据冗余度

如果多张表进行关联查询的时候，想要提升查询的效率，可以允许适当的数据冗余度

如果需要历史快照，可以考虑增加数据冗余度，采用反范式设计

数据仓库通常会采用反范式设计

实际工作中，需要根据需求将范式和反范式结合使用

索引概览

索引的作用

概念：索引就是帮助数据库管理系统高效获取数据的数据结构。

索引不是万能的

数据表中的数据行数少的情况下，不需要创建索引的（比如行数小于1000）
当数据重复度大，就不需要对这个字段使用索引（比如高于10%）

索引的种类

功能逻辑

普通索引：是基础的索引，没有任何约束，主要用于提高查询效率
唯一索引：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引
主键索引：在唯一索引的基础上增加了为空的约束，也就是NOT NULL+UNIQUE，同时一张表里最多只有一个主键索引
全文索引：MySQL自带的全文索引只能用在InnoDB和MyISAM，并且只能对英文进行全文索引。我们可以采用专门的全文搜索引擎，比如ES(ElasticSearch), Solr

物理实现

聚集索引：按照主键的维度来排序存储数据，对数据的查询效率高
非聚集索引：在数据库系统会有单独的存储空间存放非聚集索引，这些索引项是顺序存储的，但索引项指向的内容是随机存储的。

字段个数

单一索引：索引列为一列时为单一索引
联合索引：多个列组合在一起创建的索引

SQL查询实验

实验1：数据行数少的情况下，索引效率如何

实验2：性别（男或女）字段真的不应该创建索引么？

实验3：使用聚集索引和非聚集索引的查询效率

实验4：联合索引的最左原则

索引的数据结构

索引的数据结构评价

数据库服务器有两种存储介质：硬盘和内存。内存是临时存储，硬盘是永久存储介质

索引只能放到硬盘中，因此磁盘的IO次数决定了索引数据结构查询性能的好坏

二叉树

如果key大于根节点，则在右子树中进行查找；

如果key小于根节点，则在左子树中进行查找；

如果key等于根节点，也就是找到了这个节点，返回根节点即可

AVL树：平衡二分搜索树，每个节点的左子树和右子树的高度差不能超过1

采用二叉树的数据结构，树的高度大，磁盘IO操作多，影响查询效率

B树

B树：Balance Tree，也就是平衡的多路搜索树

特点：它的高度要远小于平衡二叉树的高度，所以在文件系统和数据库系统中的索引结构经常采用B树来实现。

B树的特性

根节点的儿子数的范围是[2,M]
每个中间节点包含k-1个关键字和k个孩子
叶子节点包括k-1个关键字（叶子节点没有孩子）
所有叶子节点位于同一层

B+树

B+树是基于B树的改进

与B树的差异

有 k 个孩子的节点就有k个关键字
非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
非叶子节点仅用于索引，不保存数据记录。跟记录有关的信息都放在叶子节点中
所有所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

查询效率对比

查询效率更稳定
查询效率更高
范围查询更方便

Hash索引

理解Hash

Hash函数，又称为散列函数，通过它可以大幅提升我们检索数据的效率。

Hash是现代密码学的核心，它被用于各种安全验证场景，比如密码存储，文件验证系统

MD5就是Hash函数的一种，Hash函数有多种映射方式

动手实现Hash检索对比

采用Python数组的形式添加10000个元素，并对元素进行检索

采用Python字典（Hash）形式添加10000个元素，并对元素进行检索

检索效率对比：数组检索用时1.2秒，字典检索用时1毫秒

MySQL中的Hash索引

Hash索引的流程：键值=>桶=>数据行

桶（bucket）指的是一个能存储一条或多条记录的存储单位。

Hash冲突：多个键值对应同一个桶，会在桶中进行键值的查找。

Hash值的字节数比较少，简单的4个字节就够了，多的16位或者32位

MySQL中采用的是“自适应Hash索引”的方式

B+树索引的区别

Hash索引不能进行范围查询

Hash索引不支持联合索引的最左侧原则，即联合索引的部分索引无法使用

Hash索引不支持ORDER BY排序

Hash索引不能进行模糊查询

如果字段的重复值较多，Hash索引效率可能比B+树索引效率低

索引的使用原则

什么情况下使用索引

字段的数值有唯一性的限制，比如用户名

频繁作为WHERE查询条件的字段，尤其数据表大的情况下效果更明显

需要经常GROUP BY和ORDER BY的列

UPDATE、DELETE的WHERE条件列，一般也需要创建索引

DISTINCT字段需要创建索引

多表连接JOIN时

连接的表的数量尽量不要超过3张
对WHERE条件创建索引
对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致

什么时候不需要创建索引

WHERE条件（包括GROUP BY, ORDER BY）里用不到的字段，不需要创建索引

表记录太少，比如少于1000，是不需要创建索引的

字段中如果有大量重复数据，就不用创建索引

频繁更新的字段不一定要创建索引，因为有很高的索引维护成本

采用二叉树的数据结构，树的高度大，磁盘IO操作多，影响查询效率

什么情况下索引失效

如果索引进行了表达式计算，则会失效

如果对索引使用函数，也会造成失效

在WHERE子句中，如果是OR条件，如果有一个条件列没有进行索引，那其他索引也会失效。

当我们使用LIKE进行模糊查询的时候，后面不能是%

索引列尽量设置为NOT NULL约束（可以更好的利用索引）

使用联合索引的时候要注意最左原则

数据页

数据库中的存储结构

页（Page）是数据库管理存储空间的基本单位

区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页

段（Segment）由一个或多个区组成，不同类型的数据库对象，段的形式不同。

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，因此在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。

数据页的结构

页（Page）如果按类型划分的话，常见的有数据页（保存B树节点），系统页，Undo页，事务数据页等

页的大小

InnoDB存储引擎中默认是16KB
SQL Server的页大小为8KB
Oracle中用术语“块”（Block）来代表“页”，支持的块大小为2KB，4KB，8KB，16KB，32KB和64KB

7个部分组成

File Header，文件头，描述页的信息
Page Header，页头，页的状态信息
Infimum + Supremum，最小和最大记录，这是两个虚拟的行记录
User Records，用户记录，存储行记录内容
Free Space，空闲空间，页中没有使用的空间
Page Directory，页目录，存储用户记录的相对位置
File Trailer，文件尾，校验页是否完整

作用上可以分成3个部分

文件通用部分，也就是文件头和文件尾，类似集装箱一样，将页的内容进行了封装
记录部分，页的主要作用是存储记录，占据主要空间
索引部分，这部分重点指的是页目录，起到了记录的索引作用

页内的记录查找方式：通过页目录（存储的是槽，槽相当于分组记录的索引）对记录进行二分查找

理解B+树的查询方式

页结构

在一棵B+树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间
同一层上的节点之间，通过页的结构构成了一个双向的链表（页文件头中的两个指针字段）。
非叶子节点，包括了多个索引行，每个所银行里存储这索引键和指向下一层页面的页面指针。
叶子节点，存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单项的链表，但是对记录进行查找可以通过页目录采用二分查找的方式来进行。

B+树中如何检索记录：

首先是从B+树的根开始，逐层检索直到找到叶子节点
找到叶子节点对应的数据页，将数据页加载到内存中，通过页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组
在分组中通过链表遍历的方式进行记录的查找

理解普通索引和唯一索引在检索上的效率为什么差别很小

磁盘IO

缓冲池

作用：DBMS会申请占用内存来作为数据缓冲池，最小化磁盘活动

如何读取数据：首先会判断该页面是否在缓冲池中，如果存在就直接读取，否则才会通过内存或磁盘将页面存放到缓冲池中进行读取。

如何更新数据：记录进行修改时，首先会修改缓冲池中的页里面的记录信息，然后缓冲池会采用一种checkpoint的机制以一定的频率刷新到磁盘上。

查看缓冲池的大小：在InnoDB中对应的 innodb_buffer_pool_size 变量

查看缓冲池的个数：在InnoDB中对应的 innodb_buffer_pool_instances 变量

数据页加载的三种方式

内存读取，如果该数据存在在内存中，基本上执行时间在1ms，效率还是很高的。

随机读取，如果没有在内存中，我们就需要在磁盘上对该页进行查找，整体时间预估在10ms

顺序读取，批量读取的方式，如果磁盘吞吐量是40MB/S，页大小为16KB，那么一页的读取时间在0.4ms

统计SQL语句查询成本

如何确定查询计划：如果存在多种查询计划的话，MySQL会计算每个查询计划所需要的成本，从中选择成本最小的一个作为最终执行的查询计划

last_query_cost

指令：SHOW STATUS LIKE 'last_query_cost'
作用：统计刚才执行的SQL语句需要加载多少个页面
如果采用顺序读取的方式，即使加载的页面多，查询时间变化也不大

理想的索引

索引片

索引片就是 SQL查询语句在执行中需要扫描的一个索引片段，你可以把它理解为定义索引匹配列的数量

宽索引：索引片中的匹配列数量多，大于2；窄索引：索引片中的匹配列少，1-2

如何通过宽索引避免回表：将SELECT中的字段添加到索引片中，避免回表

过滤因子的计算：这个满足这个谓词条件列的记录数除以总记录数的比例

过滤因子的条件过滤能力越强，满足条件的记录数就越少，SQL查询需要扫描的索引片也就越小。同理，如果没有选择好过滤因子，就会造成索引片中的记录数过多。

三星索引

Star1，在WHERE条件中，找到所有等值谓词中的条件列，作为索引片中的开始列

Star2，将 GROUP BY和ORDER BY中的列加入到索引中

Star3，需要将SELECT字段中剩余的列加入到索引中

原理：Star1 最小化索引片， Star2 避免排序，Star3 避免回表查询

理想的索引设计很难存在

采用三星索引会让索引变宽，这样每个页（page）能够存储的索引数据就会变少，从而增加了页加载的数量。

虽然提升了单个SQL查询的效率，但是增加了索引维护的成本。

设计合理的索引

一张表的索引个数不宜过多

在索引片中，需要控制索引列的数量

单列索引和复合索引的长度需要控制

没有理想的索引，只有适合的索引设计。需要在索引效率和维护成本中进行平衡

锁的划分

按照锁的粒度

行锁：按照行的粒度对数据进行锁定，锁定力度小，因此发生锁冲突概率低，可以实现并发度高。但是对于锁的开销比较大，加锁会比较慢。

页锁：页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

表锁：对数据表进行锁定，锁定粒度很大，同时发生锁冲突的概率也会较高，也就是数据访问的并发度低。不过好处在于对锁的使用开销小，加锁会很快。

MySQL中，InnoDB支持行锁，表锁；MyISAM支持表锁

Oracle支持行锁，表锁；SQL Server支持行锁、页锁、表锁

从数据库管理的角度

共享锁，也叫读锁或S锁，锁定的资源可以被其他用户读取，但不能修改

排他锁，也叫独占锁，写锁或X锁。锁定的数据只允许进行锁定操作的事务使用，而其他事务无法对已锁定的数据进行查询或修改。

意向锁，简单来说就是给更大一级别的空间示意里面是否已经上过锁。

当有多个事务对同一数据获得读锁的时候，可能会出现死锁的情况。

从程序员的角度

乐观锁，认为对同一数据的并发操作不会总发生，属于小概率事件，不采用数据库自身的锁机制，而是通过程序来实现：版本号机制，时间戳机制

悲观锁，对数据被其他事务的修改持保守态度。因此会通过数据库自身的锁机制来实现，从而保证数据操作的排他性。

适用场景：乐观锁适合读多的场景；悲观锁适合写多的场景

如何防止死锁的发生

如果事务涉及多个表，操作比较复杂，那么我们可以尽量一次锁定所有的资源，而不是逐步来获取

如果事务需要更新数据表中的大部分数据，而且数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁

不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序可以降低死锁发生的概率。

MVCC

什么是MVCC

MVCC，通过数据行的多个版本管理来实现数据库的并发控制。

使用MVCC的好处

读写互相不阻塞
降低了死锁的概率
解决一致性读的问题

快照读：读取的是快照数据（历史版本）

当前读：读取最新数据。加锁的SELECT，或者插入，删除，更新都会进行当前读

InnoDB中的MVCC

事务版本号：当我们每次开启一个事务，都会从数据库中获得一个事务ID，这个事务ID是自增长的，所以通过ID大小可以判断出来事务的时间顺序

行记录的隐藏列

db_row_id，隐藏的行ID
db_trx_id，操作这个数据的事务ID
db_roll_ptr，回滚指针

Undo Log，InnoDB将我们的行记录快照保存在了Undo Log里

Read View是如何工作的

作用：帮我们解决了行的可见性问题

Read View的结构

trx_ids，系统当前正在活跃的事务ID集合。
low_limit_id，活跃的事务中最大的事务ID。
up_limit_id，活跃的事务中最小的事务ID
creator_trx_id, 创建这个Read View的事务ID

Read View的原则

trx_id < up_limit_id
trx_id > low_limit_id
up_limit_id < trx_id < low_limit_id

查询数据的步骤

Step1，首先获取事务自己的版本号，也就是事务ID
Step2，获取Read View
Step3，查询得到的数据，然后与Read View中的事务版本号进行比较
Step4，如果不符合ReadView规则，就需要从Undo Log中获取历史快照
Step5，最后返回符合规则的数据

读已提交时，一个事务中的每一次SELECT查询都会获取一次Read View

可重复读时，一个事务只在第一次SELECT时会获取Read View，后面所有的SELECT都会复用这个Read View

InnoDB是如何解决幻读的

InnoDB的三种行锁的方式

记录锁，针对单个行记录添加锁。
间隙锁，锁住一个范围（索引之间的空隙），但不包括记录本身。可以防止幻读。
Next-Key锁：锁住一个范围，同时锁定记录本身，相当于间隙锁+记录锁，可以防止幻读。

采用Next-Key锁+MVCC避免幻读

查询优化器

什么是查询优化器

SQL查询的执行步骤

分析器：包括了语法分析，语义检查
优化器：逻辑查询优化，物理查询优化
执行器：将确定的执行计划进行执行

查询优化器的目标是找到执行SQL查询的最佳执行计划

执行计划就是查询树，它是由一系列物理操作符组成，这些操作符按照一定的运算关系组成查询的执行计划。

优化器的两个阶段

逻辑查询优化，通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。
物理查询优化，关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择最小的代价作为执行计划。

两种优化方式

基于规则的优化器：RBO，通过在优化器里面嵌入规则，来判断SQL查询符合哪种规则从而制定执行计划

基于代价的优化器：会根据代价评估模型，计算每条可能的执行计划的代价，也就是COST，从中选择代价最小的作为执行计划。

RBO像出租车老司机，从A到B的路径靠自己的经验来规划。而CBO更像是手机导航，通过数据来驱动选择最佳的执行路径。

SQL是面向集合的语言，是解释性语言，不同的数据库优化器的实现原理不同

CBO是如何统计代价的

大部分关系型数据库都支持基于代价的优化器（CBO），CBO随着版本的迭代也越来越成熟，但是CBO依然存在着缺陷

代价模型中可以配置的参数

Server层
- 对应mysql.server_cost数据表
- disk_temptable_create_cost，表示临时表文件（MyISAM或InnoDB）的创建代价，默认值为20
- disk_temptable_row_cost，表示临时表文件（MyISAM或InnoDB）的行代价，默认值0.5
- key_compare_cost，键比较的代价，键比较的次数越多，这项的代价就越大，是个重要的指标，默认值0.05
- memory_temptable_create_cost，内存中临时表的创建代价，默认值1
- memory_temptable_row_cost，内存中临时表的行代价，默认值0.1
- row_evaluate_cost，统计符合条件的行代价，如果符合条件的行数越多，那么这一项的代价就越大，因此这是个重要的指标，默认值0.1
Engine层
- 对应mysql.engine_cost数据表
- io_block_read_cost，从磁盘中读取一页数据的代价，默认是1
- memory_block_read_cost，从内存中读取一页数据的代价，默认是0.25

如何计算

总代价 = IO代价 + CPU代价
COST= PAGE FETCH +W * (RSI CALLS)
PAGE FETCH是IO代价，也就是页面加载的代价，包括了数据页和索引页的加载
W * (RSI CALLS)是CPU代价，其中W是个权重因子，表示了CPU到IO之间转化的相关系数，同时RSI CALLS代表了CPU的代价估算。
总代价 = IO代价 + CPU代价 + 内存代价 + 远程代价（MySQL5.7版本之后）