SQL

DDL

数据库定义语言：我们可以创建，修改，删除数据库的表结构

DML

数据库操作语言：用它操作和数据库相关的记录，比如增加，删除，插入表中的记录

DCL

数据库控制语言：用它来定义访问权限和安全级别

DQL

数据查询语言：用它来查询想要的数据，他是sql语言的重中之重，因为实际业务中，主要是和查询打交道

属于关系型数据库

SQL为声明性语言：说明自己想要做什么，不需要指写出来如何做，比如先做权限校验，在取出全部收据，在for循环拿出需要的数据，再返回数据

我们一般采用ER图，也就是实体关系模式来创建数据库

ER图来描述现实世界中的概念模型，主要有三个要素：实体，关系，属性

实体就是对象，比如学生，学校

属性就是对象有哪些字段，比如学生有 学号，姓名等

关系就是两个实体之间有什么关联，例如学生和学校之间是多对一的关系，还有 一对一和一对多

SQL大小写问题：

库名，表名，表别名，字段名，字段别名都是小写字母

SQL保留字，函数名，绑定变量等都用大写字母

在Linux下，mysql区分表名和数据库名的大小写，window下不区分

DBMS是数据库管理系统=多个数据库+管理程序，MYSQL和ORACLE就是数据库管理系统

DB是数据库，也就是存储数据的集合，它里边包含多张数据表

DBS是数据库系统：他更大，包含了 DBMS，DB，以及数据库管理人员DBA

非关系型数据库

键值对数据库

通过key-value的键值对方式来存储数据，key和value都可以是复杂或简单的对象，key是唯一标识符，可以通过key快速找到我们要的数据，前提是我们要知道key的具体值，因为他不能像sql一样使用where进行复杂的他条件过滤。因此如果我们不知道要查找的数据的具体key就需要遍历所有的key，只一点非常消耗计算机资源，

• Redis是最流行的键值对数据库

• 非常时候再知道具体要查询的数据（知道key）的时候使用，这样速度最快

文档型数据库

在数据库中文档为处理信息的基本单位，一个文档就相当于 一条记录

• MongoDB是最流行的

  搜索引擎

  搜索引擎就采用了全文索引技术，针对全文索引的效率很高，弥补了数据库在这方便的低效率，核心技术为：倒排索引

  列式数据库

  mysql等数据库都是行式数据库，还有对应的列式数据库，就是按照列存储数据，好处是能大大降低I/O，适合分布式文件系统，不足在于功能有限

• 列式数据库之所以能降低I/O 因为他把一列的数据串起来，进行存储，然后在存储下一列，因为每一列的数据类型是一样的，方便进行压缩，并且在读取的时候，只需要读取需要的列到内存中，杭式数据库如果要某一列的数据却还要读取许多的没用的其他数据，因此，列式数据库在这种只需要某一列数据的情况下就能降低IO

• 比如HDFS

图形数据库

利用图这种数据结构来存储对象之间的关系，比如社交中的人的关系，典型的模型就是以节点和边（关系）来实现，能高效的解决复杂关系问题

• Neo4j就是图形数据库

  时序数据库

  主要存储时间序列的信息，主要用于时间顺序预测等场景，主要是字段这个特征中有时间这个维度，比如股市的预测，比特币预测，天气预测，交通流量预测等，都是时间维度有关系

• 比如InfluxDB数据库

  NoSQL对SQL功能不足的一种很好的补充

  SQL是如何执行的

  Oracle中的SQL执行

  

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

• 1.语法检查

• 检查语法是否正确，不准确会报语法错误

• 2.语义检查

• 检查SQL中要访问的对象是否存在，例如表名，列名等，如果不存在会报错

• 3.权限检查

• 查看用户是否具备访问该数据的权限

• 4.共享池检查

• 共享池是一块内存池，主要是缓存SQL语句和该语句的执行计划，ORACLE通过检查共享池是否存在SQL的执行计划来进行判断是进行软解析，还是硬解析

• 5.优化器

• 主要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划

• 6，执行器

• 当有了解析树和执行计划，就知道了sql语句怎么被执行，这样就能在执行器中执行SQL语句

• 保证SQL语句没有错误

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

• 数据字典缓冲区

• 存储oracle中对象的定义：比如 表，索引，视图等对象，当sql解析的时候需要相关的数据，就会在这里边获取

• 库缓存

• 主要是为硬解析服务，我们应该避免硬解析，因为创建解析树和执行计划很耗费资源
• 通过绑定变量的方式，来尽可能规避到硬解析，缺点是可能造成的执行计划不够优化
• 举个例子，我们可以使用下面的查询语句：SQL> select from player where player_id = 10001; 你也可以使用<绑定变量>，如： SQL> select from player where player_id = :player_id;
• 使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。

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

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

MySQL中的SQL执行

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

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

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

• 1.查询缓存

• Server如果在查询缓存中发现了sql语句，就直接返回结果，如果没有发现，则进入解析器阶段
• 查询缓存效果不好，mysql8中已经停用了

• 2.解析器

• 对sql语句进行语法分析，语义分析

• 3.优化器

• 确定sql语句的执行路径，比如是全表扫描，或者根据索引检索

• 4.执行器

• 执行之前会先查看是否拥有权限，如果拥有权限就查询数据并返回，mysql8之前，还会将结果存储查询缓存中

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

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

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

• mysql查询引擎以插件的形式提供，可以根据需要自由选择哪一种
• InnoDB：5.5以后默认的引擎，最大的特点是支持事务，行级锁，外键约束
• MylSAM：5.5之前默认的引擎，它不支持事务，行级锁，外键，但是速度快，占用资源少

• 不需要事务，主要为读的时候可以用，但是他在系统崩溃的时候，恢复比较困难

• Memory：使用系统内存作为存储介质，已获得更快的响应速度，但是如果mysql进程崩溃，就会导致数据全部丢失。
• NDB，也叫NDB，Cluster引擎，主要用于Mysql Cluster分布式集群环境

• Mysql Cluster 介绍https://blog.csdn.net/qq_36807862/article/details/81289143

• Archive：他又很好的压缩机制，用于文档归档，在写入时候会进行压缩，因此也常作为仓库

• 两个引擎的区别参考：https://www.jianshu.com/p/a957b18ba40d

  执行时间分析：

• 语句 select @@profiling; 查看是否开启了 profiling

• 开启这个我们可以看到sql的执行情况与各个节点的耗时（一个sql语句的执行会经历不同的模块）
• 结果为1标识开启，结果为0标识关闭
• set profiling=1; 开启profiling

• 查询所有会话所产生的的 profile ：SHOW PROFILES;

• 结果如图所示：

• 我们可以通过 show profiles for id 来查询执行的profile

• 查看上一次会话所产生的的profile：show profile;

• 结果如图

• 字段释义
• Opening tables：线程正在试图打开一张表
• init：这个线程出现在线程初始化，ALTER TABLE,INSERT,UPDATE,SELECT语句之前，他会进行刷新全日志，Innodb日志，和一些查询缓存清理工作
• System Lock：这个线程正在请求或者等待一个内部的或外部的系统表锁
• optimizing：服务器正在执行初步的查询优化
• statistics：服务器计算统计去规划一个查询，如果一个线程长时间处于这个状态，这个服务器的磁盘可能在执行其他操作
• preparing：正在执行查询优化工作
• Creating tmp table ：创建临时表
• Sorting result：对应一个select 语句对结果进行排序所花时间，是对非临时表
• executing：开始执行第一条语句
• Sending data：最重要的一个过程：线程正在读取和处理一个select的行，并将数据发送到客户端，在此期间可能执行大量的磁盘访问操作，这个操作在一个指定的生命周期中的耗时往往最长
• limit就是在这个阶段减少传递时间
• Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”
• mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端
• Creating sort index：线程正在使用内部临时表处理一个select操作
• end： 这个状态出现在结束时，但是在对ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, 或者 UPDATE 语句进行清理之前。
• query end：这个状态出现在处理一个查询之后，但是在freeing items状态之前
• removeing tmp table：线程正在移出一个内部临时表，如果没有使用到，这个过程就相当于不存在
• closing tables：线程正在将变更的表中的数据刷新到磁盘上并正在关闭使用过的表。这应该是一个快速的操作。如果不是这样的话 则应该检查硬盘空间是否已满或者硬盘IO是否达到瓶颈。
• freeing items：线程已经执行了命令，在这个状态中执行到的线程缓存可以得到一些释放，这个状态通常后面跟随cleaning up状态
• cleaning up：线程处理一个命令，并正准备释放内存和重置某些状态变量

数据库也是一种软件

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

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

DDL

基础语法

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

• 在执行DDL的时候不需要commit，就可以完成执行任务

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

  对数据表进行定义

• 创建：CREATE TABLE table_name;(语句最后一;结尾，定义最后一个字段时候，结尾没有逗号）

• 示例：
•

• 复杂的表的创建语句

• 字符编码是 utf8，排序规则是utf8_general_ci
• utf8_general_ci表示对大小写不明感，设置为utf8_bin表示对大小写敏感
• 注意 mysql中 utf-8mb4才是真正的utf8编码，能支持符号表情
• mysql的编码utf-8是3个字节的，它和我们传统的utf-8编码是不一样的，mysql中不是用了utf-8mb4才对应4字节的utf-8编码
• USING BTREE 标识 索引使用的是 b+树
• UNIQUE INDEX 标识设置唯一索引
• NORMAL INDEX 标识设置普通索引
• 区别在于是否对字段的唯一性进行了约束

• 修改：ALTER TABLE

• 添加字段：ALTER TABLE tablename ADD (age int(11));
• 修改字段：ALTER TABLE tablename RENAME COLUMN age to player_age
• 修改字段的数据类型：ALTER TABLE tablename MODIFY (player_age float(3,1));
• 删除字段：ALTER TABLE tablename DROP COLUMN player_age

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

数据表的常见约束

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

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

是否使用外键确实会有一些争议。我来解释下关于外键的使用：首先，外键本身是为了实现强一致性，所以如果需要正确性>性能的话，还是建议使用外键，它可以让我们在数据库的层面保证数据的完整性和一致性。当然不用外键，你也可以在业务层进行实现。不过，这样做也同样存在一定的风险，因为这样，就会让业务逻辑会与数据具备一定的耦合性。也就是业务逻辑和数据必须同时修改。而且在工作中，业务层可能会经常发生变化。当然，很多互联网的公司，尤其是超大型的数据应用场景，大量的插入，更新和删除在外键的约束下会降低性能，同时数据库在水平拆分和分库的情况下，数据库端也做不到执行外键约束。另外，在高并发的情况下，外键的存在也会造成额外的开销。因为每次更新数据，都需要检查另外一张表的数据，也容易造成死锁。所以在这种情况下，尤其是大型项目中后期，可以采用业务层来实现，取消外键提高效率。不过在SQL学习之初，包括在系统最初设计的时候，还是建议你采用规范的数据库设计，也就是采用外键来对数据表进行约束。因为这样可以建立一个强一致性，可靠性高的数据库结构，也不需要在业务层来实现过多的检查。当然在项目后期，业务量增大的情况下，你需要更多考虑到数据库性能问题，可以取消外键的约束，转移到业务层来实现。而且在大型互联网项目中，考虑到分库分表的情况，也会降低外键的使用。不过在SQL学习，以及项目早期，还是建议你使用外键。在项目后期，你可以分析有哪些外键造成了过多的性能消耗。一般遵循2/8原则，会有20%的外键造成80%的资源效率，你可以只把这20%的外键进行开放，采用业务层逻辑来进行实现，当然你需要保证业务层的实现没有错误。不同阶段，考虑的问题不同。当用户和业务量增大的时候，对于大型互联网应用，也会通过减少外键的使用，来减低死锁发生的概率，提高并发处理能力。

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

• 唯一性约束相当于创建了一个约束和普通索引

• 唯一性索引中允许NULL值的存在

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

DEFAULT：字段默认值

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

• mysql8.0.16以后才被支持

  SELECT检索数据

  基础语法

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

  使用列别名进行检索：AS

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

• select ‘陈明超’ as name from hero 这样就会多出来name一列，每一行这一列的值都是陈明超

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

• 需要放到所有列的前面

• DISTINCT对所有列名的数据结果的组合去重，

  排序检索数据

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

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

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

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

• 当order中有多个列的时候，会先按照第一个列进行排序，如果第一个列相同，再按照第二个列进行排序

• 默认asc递增排序
• 如果在字段前加上 BINARY就代表这个字段查询或排序的时候要区分大小写

SELECT查询效率

尽量避免使用SELECT *

• 因为*会返回所有的列，如果我们不需要所有的列，多余的列不仅没用，还会增加网络传输负担，

• 建议生成环境下，不建议使用select *

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

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

SELECT语句的执行顺序

关键字的顺序：

• SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

  SELECT的执行顺序

• FROM > WHERE > GROUP BY >使用聚集函数进行计算> HAVING>计算所有表达式 > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

  一个样例

  

• 这些步骤每一步都会产生一个 <虚拟表>,然后这个虚拟表就会作为下一步骤的输入

• 步骤详解
• 1，FROM：假设为两张表的时候
• 1.首相通过 CROSS JOIN 求笛卡尔积，得到虚拟表vt1-1
• 2.在虚拟表1-1的基础上，进行ON筛选，得到虚拟表v1-2
• 添加外部行，如果用到了左右全连接的情况下，就会涉及到外部行，在虚拟表v1-2的基础上添加外部行
• 如果是两张以上的表，就会重复这个步骤
• 然后得到最终虚拟表vt1
• 2.WHERE:对虚拟表vt1进行条件过滤，得到虚拟表vt2
• 3,GROUP BY ，使用聚集函数,HAVING 阶段，在虚拟表vt2的基础上，进行分组使用聚集函数（例如COUNT,SUM等)和分组过滤，得到虚拟表vt3和vt4
• 4.计算所有的表达式，比如select中 使用了 3+4
• 5.SELECT和DISTINCT:首先在SELECT阶段获取自己想要的数据，然后在DISTINCT阶段对数据进行过滤，得到虚拟表vt5-1和vt5-2
• 6.ORDER BY:在我们获取到想要的数据以后，就可以对数据进行排序得到虚拟表vt6
• 7.在vt6的基础上取出需要的行，得到虚拟表vt7

• 如果某个关键字不存在，对应的执行阶段就会省略

  关于COUNT的统计效率问题

  关于COUNT()的效率是一个很好的问题，欢迎探讨：在MySQL InnoDB存储引擎中，COUNT()和COUNT(1)都是对的所有结果进行的COUNT。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计。如果没有WHERE子句，则是对数据表的数据行数进行统计。因此COUNT()和COUNT(1)本质上没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O(1)复杂度，这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。而InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，只维护一个row_count变量。因此就需要采用扫描全表，进行循环+计数的方式来完成统计。需要注意的是，在实际执行中COUNT()和COUNT(1)执行时间可能略有差别，不过你还是可以把这两个在执行效率上看成是相等的。另外在InnoDB引擎中，如果是采用COUNT()和COUNT(1)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于查找具体的行来说，采用主键索引效率更高。而对于COUNT()和COUNT(1)这种，不需要查找具体的行，只是统计行数来说，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引的时候，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。优化总结：1、一般情况下：COUNT() = COUNT(1) > COUNT(字段)所以尽量使用COUNT()，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。2、如果要统计COUNT()，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

  WHERE数据过滤

  比较运算符

  等于=，不等于!= <>，小于<，小于等于<=，大于>，大于等于>=

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

  为空值：IS NULL

  逻辑运算符

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

  并且：AND

  或者：OR

  在指定条件范围内：IN

  非（否定）：NOT

• NOT BETWEEN |NOT IN

  (hp_max+mp_max) > 8000 WHERE中，还能这么进行比较

  AND的优先级高于OR

  使用通配符进行过滤

  LIKE操作符

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

  通配符_：匹配单个字符

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

• 因为他需要消耗更长的时间去匹配，甚至会让索引失效（如果以通配符开头的话，以通配符结尾的不会）

  LIKE ‘_%太%’

  什么时候用DATE函数

  解答一下对使用DATE函数的疑问： birthdate字段可能会有时间包含在里面，如2019-01-01 00:00:00，如果直接和2019-01-01比较是会失败的，用DATE函数可以提取出原始数据的日期部分 作者回复: 解释的很好，关于为什么使用DATE函数的说明可以看下这个

  同一行的不同列直接也可以进行比较 例如 a > b 就是 a和b是同一行的不同列的数据，他们也能比较 选出来符合 这个条件的行

  SQL函数

  算数函数

  字符串函数

  日期函数

  转换函数

  关键字和函数名是不区分大小写的

  一个命名规则

  关键字和函数名称全部大写；数据库名、表名、字段名称全部小写；SQL 语句必须以分号结尾。

  细说NULL

  NULL是什么

  NULL得意思是没有数据（空也代表了有数据）

  NULL的长度

  NULL的长度为NULL

  NULL与查询

  查询一个NULL的时候，如果用 =null 和 != null 都会直接返回null 也就不能用 =和!= 要用 is null 和 is not null

  如果 我们用 abc !=2 这种条件查询数据 如果 abc的值为null 那么这个字段也将不会显示，就相当于用了过滤语句 abc != 2 and abc is not null

  NULL也不能进行范围查询： a = null, a > null ,a <>null 返回的结果都会是null，同理 对于 a>2这种来说，也相当于是进行了 a>2 and a is not null的操作

  NULL与索引

  MYSQL会对NULL字段也进行索引，但是只有 IS NULL 才会使用索引

  唯一索引允许插入多条NULL记录

  NULL与数据类型，数据运算

  如果允许NULL，那么该字段的数据类型，从程序的角度上来说，是不统一的，你很难把NULL跟整数，或者NULL跟字符串当作同一个类型处理

  NULL与其他值相加，结果总为NULL， select 1+null = null select concat(‘abc’, null) 结果也为null

  NULL与COUNT

  COUNT(*):不管字段为什么，计算存在的行

  COUNT(column_name)：结果不包含字段为NULL的记录

  NULL与排序

  ASC：NULL值在所有其他值之前

  DESC：NULL值在所有的其他值之后

  这只是一种约定，不是NULL值最小

  NULL与分区

  GROUP BY 会导致所有的NULL都在 一个分区

  Mysql隐式类型转换规则：

  聚集函数

  对一组数据进行汇总的函数

  输入的是一组函数的集合

  对于复杂的数据可以先进行分组，然后在进行筛选，然后在进行聚集运算

  聚集函数种类

  COUNT

• 总行数

• 如果我们用的COUNT(列名）那么如果这一列的值为NULL 就不会被统计，如果我们用的是COUNT() 那么他就只会统计行数，不管某个字段是否为null
• 函数内部可以用 distinct关键字进行去重，然后再进行统计
• SQL: SELECT COUNT(DISTINCT hp_max) FROM heros
• SELECT COUNT(DISTINCT ) FROM staff
• 但是不能用 COUNT(distinct *) 会报错 可以用 COUNT(distinct 1) 和COUNT(DISTINCT 列名)

SUM

• 求和

• DISTINCT也可以用于此函数中

MAX

• 最大值

• 自动忽略要求的列中为null 的值
• 也可用于字符串类型的数据统计
• 如果是字母 按照A-Z的顺序，越往后越大，如果是汉字，则按照全拼拼音进行排列
• 也可以用DISTINC但是没必要

MIN

• 最小值

• 自动忽略要求的列中为null 的值
• 也可以用DISTINC但是没必要

AVG

• 平均值

• 自动忽略要求的列中为null 的值
• DISTINCT也可以用于此函数中
• SQL: SELECT ROUND(AVG(DISTINCT hp_max), 2) FROM heros

可以对数据先进行分组，然后在使用统计函数

• SQL: SELECT COUNT(*), role_main FROM heros GROUP BY role_main

• 着就是先按照角色分组，然后 对每个角色的数量进行统计，也就是说使用GROUP BY语句后，SELECT中的聚集函数都是对分组后的每个组的数据进行统计，而不是对整体数据进行统计了

注意，这些聚集函数只会对其所在列进行操作，如果还带了其他列的数据，那么其他列的数据默认就是第一行的那些数据

• 例如 当你select name max(age)的时候，他选出来的name并不是最大的按个age对应的人，而是默认的第一行数据的那个name

•

• 之行为SELECT emp_no ,MIN(salary) FROM salaries
•

• 明显数据不对

SELECT中可以用多种聚集函数

SQL: SELECT COUNT(*), AVG(hp_max), MAX(mp_max), MIN(attack_max), SUM(defense_max) FROM heros WHERE role_main = ‘射手’ or role_assist = ‘射手’

分组：

进行GROUP BY的时候，如果后边的列中有为null的数据，那么所有null的数据会被单独列为一个分组

SELECT COUNT(*), role_assist FROM heros GROUP BY role_assist

HAVING分组过滤与WHERE的区别

where用于所有行的数据进行过滤，having用于对分组后的每一组的数据进行过滤

• 例如我们先按照 年级进行分组，然后筛选出来年级人数多余100的 SELECT count() as num, grade from calss group by grade having count() > 100 这里 count(*)和count(grade)效果一样

• 他还等价于： SELECT count() num, grade from calss group by grade having num > 5
• 这里之所以他们等价，原因是 ，虽然 count（）在select中，但是 <实际上 聚集函数的计算，是在group by 之后 having之前，也就是说 count(*)虽然在select中，但是实际上 group by之后 聚集函数已经完成了>

• class表存的是所有的学生，grade是其对应的年级号

  having 支持所有where支持的操作，能用having做的都能用where

• 之所以这么说，是因为 group by分组后，我们选择出来的相同的数据都归为一行，然后我们也就是相当于对这分组后归为一行的数据进行操作和没分组归一之前的where的用处一样就，where就是对没分组之前的所有数据都有进行处理，其实本质上是一样的，就是where对没有做相同数据归纳的行进行处理，having是对进行过去重（分组）的剩余的所有行进行处理，因此我们group by没有选的字段，having也就没法使用，也就报错了

  ORDER BY是对记录进行排序，但是分组后，已经吧相同的数据归为一条，也就是对分组后的数据进行排序了

  子查询

  有时候我们无法直接从表中获取到想要的结果，需要先做一次查询，然后从查询的结果集中再次查询，这个查询结果集就是子查询

  关联子查询

  子查询通过类似循环的方式要执行多次，先从外部开始，每次都进入子查询中进行查询，将结果反馈给外部，

• 类似于双重for循环，内部的for就是子查询

  如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次

• SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

• 这里就是先选出每个队伍的平均身高，但是要选出来是那个队伍的，需要依赖外部循环中传入的队伍id，然后 在找出此队伍中高于平均身高的队员
• 这里发现，从句的计算结果不固定，因此不是费关联子查询

非关联子查询

子查询从数据表中查询了数据结果，如果这个查询造作只执行一次，然后就作为主查询的条件进行执行

• 示例：SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

• 先求出来 这个player中的最高身高，然后在依次为基础选出来 拥有这个身高的人是谁

关联子查询和EXISTS

关联子查询经常和EXISTS一起使用，EXISTS判断条件是否满足，满足返回true，不满足返回false

• SQL：SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

• 比如我们想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。在这个统计中，是否出场是通过 player_score 这张表中的球员出场表现来统计的，如果某个球员在 player_score 中有出场记录则代表他出场过

NOT EXISTS 与EXISTS相反

集合比较子查询

与另一个查询结果集进行比较，我们可以在子查询中使用 IN，ANY，ALL，SOME

• IN

• 判断是否在集合中
• select * from person where person_id in (select student_id from student)
• 判断是否是学生
• 子IN不能判断null

• ANY

• 需要与比较操作符一起使用，与子查询中的任何值进行比较
• 用法
• SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

• ALL

• 需要与比较操作符一起使用，与子查询中的全不值进行比较
• 用法
• SQL: SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)

• SOME

• ANY的别名，效果一样

IN和EXISTS的选择

IN

• SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

• 在cc列简历索引的情况下：
• 如果A表比B表小，我们会用B表中的cc类中的索引，因为B表数据量少，做B表中的全表扫描更快，in就是 in内部的表做全表扫描

EXISTS

• SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

• 在CC列简历索引的情况
• 如果A表比B表达，exists就会用A表中的cc列索引，也就是全盘扫描A表中的数据，然后exists中的b表我们只每次单独查询某条数据

n的内表全扫描，exist外表全扫描。 外表小时用exist，内表小时用in。核心是尽量扫描小的数据，将大的数据使用索引

• 就是大表的数据尽可能选这用索引查，小表来做全表扫描

  子查询作为主查询的计算字段

  SQL: SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team

• 查询每个球队的球员数，就是将select中的字段作为参数传入一个子查询中

  SQL92中的连接

  关系数据库中的核心之一就是多个表的连接

  最重要的就是SQL92和SQL99中的标准

  SQL92的连接

  笛卡尔积

• 笛卡尔积就是两个表的所有可能的连接集合

• 例如A表中有三行，B表中有三行，A笛卡尔积B就是九行数据
• sql92中的写法： select * from A,B

等值连接

• 就是用两张表中都存在的列中的相等的数据进行连接

• select * from a,b where a.account_id = b.account_id

非等值连接

• 就是不用等号连接的情况下，都算左侧非等值连接

  外链接

• 左连接

• 左边的表是主表，要显示左边表的全部数据，而右边的是从表，(+)表示那个表是从表
• select * from a,b where a,account_id = b.account_id(+)
• 相当于sql99的left join

• 右链接

• 同理如上
• 相当于是sql99中的right join

• sql92没有全外连接

• mysql中不支持sql92这种(+)的外链接操作

  自连接

• 可以对多个表进行操作，也可以对同一个表进行操作，也就是说查询条件使用了当前表的字段

• SQL：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = ‘布雷克-格里芬’ and a.height < b.height
• 这里用了两个player表，设置别名 a,b 这里就是叫自连接

sql99中的连接

笛卡尔积（也叫交叉连接）

CROSS JOIN

• SELECT * FROM a CROSS JOIN b

• SQL99中的写法

自然连接（也就是等值连接）

NATURAL JOIN

• SELECT * FROM a NATURAL JOIN b 这里他们会用两个都有的所有同名列进行关联

• 基本不用

ON连接

select *from a join b on a.account_id = b.account_id

• 相当于用ON进行了等值连接

• 当然也可以进行非等值连接

  USING

  用USING指定数据表中的同名字段进行等值连接

• SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)

• USING使用了具体的字段名

外链接

左外连接

• LEFT JOIN或LEFT OUTER JOIN

• FROM A LEFT JOIN B ON …

右外连接

• RIGHT JOIN 或 RIGHT OUTER JOIN

• FROM A RIGHT JOIN B ON …

全外连接

• FULL JOIN 或 FULL OUTER JOIN

• FROM A FULL JOIN B ON ….

• MYSQL 不支持全外连接

• 可以通过 left join union right join 模拟

自连接

和sql92的差不多 ，只不过把连接条件改成了 join on 的方式

• SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = ‘布雷克-格里芬’ and a.height < b.height

  SQL99的表达式更强

  SELECT …FROM table1 JOIN table2 ON table1和table2的连接条件 JOIN table3 ON table2和table3的连接条件

  JOIN就是 INNER JOIN的简写

  视图

  视图就是虚拟表

  本身不具备数据

  可以整合多张表的数据，也可以展示部分表的数据

  可以针对不同的用户提供不同维度的查询视图

  视图作为一张虚拟表，帮我们封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。视图的这一特点，可以帮我们简化复杂的 SQL 查询，比如在编写视图后，我们就可以直接重用它，而不需要考虑视图中包含的基础查询的细节。同样，我们也可以根据需要更改数据格式，返回与底层数据表格式不同的数据

  通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮助我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便。创建视图：CREATE VIEW

  创建视图

  CREATE VIEW view_name ASSELECT column1, column2FROM tableWHERE condition

  使用视图

  SELECT * FROM viewName

  还可以以视图为基础，再继续创建视图

  修改视图

  ALTER VIEW view_name ASSELECT column1, column2FROM tableWHERE condition

  删除视图

  DROP VIEW view_name

  事务

  要么全部执行，要么全部不执行

  事务保证了一次处理的完整性，也保证了数据库中的数据一致性，如果我们在增，删，改的某一环节出现错误，它还允许我们回滚复原

  事务的ACID

  A：原子性

• 一个事物要么全部成功，要么全部失败，不可能一半成功，一般失败，保证了事务的完整性 不可分割性

  C：一致性

• 一致性是指我们在事务完成之前，看到的数据处于一种一样的状态，例如 变性 变性操作之前 自己的性别和身份证上的性别都是 男，当变性完成之后，自己的性别和身份证上的性别都变成女，不会出现 看到自己变成了女的，身份证上却还是男的

  I:隔离性

• 每个事务的执行和其使用的数据都是相互独立的，不互相影响，并且一个事务提交之前，对其他事务不可见

  D:持久性

• 事务提交之后对数据的修改是持久性的，即使在系统出故障的情况下，比如系统崩溃或者存储介质发生故障，数据的修改依然是有效的。因为当事务完成，数据库的日志就会被更新，这时可以通过日志，让系统恢复到最后一次成功的更新状态。

• 重做日志和回滚日志

事务的控制

START TRANSACTION 或 BEGIN 开启事务

提交事务：COMMIT，提交后是对数据库的修改是永久的

ROLLBACK 或 ROLLBACK TO [SAVEPOINT] 回滚事务，撤销所有没提交的事务

RELEASE SAVEPOINT 删除某个保存点

SET TRANSACTION 设置事务的隔离级别

显示事务

自动提交事务

• MYSQL 默认自动提交

• set autocommit = 0 //关闭自动提交
• set autocommit = 1 // 开启自动提交
• 这里注意，这里前提是我们没有通过 begin 或者 start transaction 手动开启事务，如果手动开启事务，就需要我们手动的提交或者回滚
• 实例：主动开启事务

• 实例：使用默认自动提交

• ROLLBACK回滚的时候，事务已经提交了

隐式事务

completion_type 的三大作用

completion_type = 0

• 默认情况，执行commit的时候会提交事务，执行下一个事务的时候，还需要我们使用 start transaction 或 begin 开启事务

  completion_type =1

• 当我们提交事务的时候， 开启一个commit_chain 开启一个事务链，自动开启一个相同隔离级别的事务

• 就相当于我们commit以后 又写了一个 start transaction

completion_type = 2

• 相当于 commit and release 就是提交后自动与服务器断开连接

  连续的begin而不用 commit ，每个下一个begin前都会自动提交前一个begin的事务

  实例

  

  概要

  事务的隔离级别

  为了满足隔离性的要求

  三种异常

  脏读

• A事务读到B事务中还未提交的数据，就是B事务才进行 insert，update,delete后，还没有进行commit的时候，A就看到了B中操作的数据

• 假设A先select了一次数据，为3条，此时 B执行事务却没提交，然后A在SELECT了一次数据，发现变成了4条

不可重复读

• A事务读到B事务update后提交事务的数据

• 假设A先select了一次数据，发现id为2的学生名字叫张三，此时 B执行一次对id为2的学生的名字进行修改的事务 <并且提交了事务>， A又进行了一次SELECT 发现 张三的名字变了
• 对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除
• 这里应该是针对UPDATE，INSERT和DELETE
• 这里要注意，虽然我们查询的时候显示的数据没有变化，此时数据可能要已经别别的事务给 插入了一条新数据或者删除了一条老数据，此时如果我们select发现数据没有变化的时候，选择插入数据，但是实际上 ，别的事务已经插入了一条一样的，就会报错，同样的是delete

幻读

• 幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，结果显示不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读

• 当你INSERT的时候，也需要隐式的读取，比如插入数据时需要读取有没有主键冲突，然后再决定是否能执行插入。如果这时发现已经有这个记录了，就没法插入
• 在这个隔离级别下，就算事务B的修改已经提交，事务A读到的数据依旧是一致的。当事务B插入一条新数据并提交之后，事务A查询不到当前数据，查询不到就以为不存在，但是事务A却可以更新这条数据成功，并且更新后再次查询，数据出现了。一开始查询不到，但能修改，再次查询又出现了，跟幻觉一样，所以称为　幻读
• 说白了，就是 我们多次select的都是来自于第一次selete后的数据的快照。因此有些数据和操作虽然我们看不到，但是已经确实发生了和存在了，当我们 update delete insert的时候，就会和那些看不到那是却已发生的操作起冲突、

四种隔离级别

读未提交(READ UNCOMMITTED)

• 允许脏读，允许不可重复读，允许幻读

• 可以读到未提交的数据

• SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  读已提交(READ COMMITTED)

• 不允许脏读，允许不可重复读，允许幻读

• 就是只能读到已提交的数据

可重复读(REPEATABLE READ)

• 不允许脏读与不可重复读，允许幻读

• 对于读取到的数据，多次select数据的内容也不会发生变化，针对 insert update delete的所有操作

可串行化(SERIALIZABLE)

• 全都不允许

• 把所有的事务放到一个队列里，串行执行，这样会造成效率低下的我问题

数据库的调优维度

数据库调优的目的：

一般来说都是响应时间更快，吞吐量更大

如何确定调优的方向

用户反馈

• 用户是我们系统的直接用户，他们不会给很粗技术建议，但是会反馈直观的体验，我们要重视用户的反馈

  日志分析

• 可以通过数据库或者操作系统的日志来查看瓶颈

  通过监控的运行状态来查看数据库的运行情况

  通过监控服务器的 CPU、内存、I/O 等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比

  在数据库的监控中，活动会话（Active Session）监控是一个重要的指标。通过它，你可以清楚地了解数据库当前是否处于非常繁忙的状态，是否存在 SQL 堆积等

  数据调优的维度

  1.选择合适的DBMS

• 如果对事务性处理和安全性要求高的话，可以选择商业性的数据库

• 他们在事务处理和查询性能上，都比较强，例如SQLServer 单表上亿条数据，完全没压力

• 例如 列式数据库可以大幅度降低系统I/O,适合分布式文件系统和OLAP，但频繁的增删改则不行

  2.优化表结构设计

• 表要尽量负责三范式原则

• 如果分析查询应用比较多，尤其是需要多表联查的时候，可以进行反范式优化

• 字段类型的选择，关系到查询效率的高低和存储的的大小，可以用数字，就不用字符串，字符长度要短一点，固定字段用char 不固定字段用varchar

  3.优化逻辑查询

• 逻辑sql查询优化

• 通过改变sql语句的书写方式，对sql语句进行等价变换，使的sql语句更高效
• 子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套连接消除
• 例如 where中用过字段使用了函数，就没法使用，可以讲这个函数优化掉

• 物理sql查询优化

• 讲逻辑查询的内容变成可以被执行的物理操作符
• 他的核心就是高效简历索引，并通过索引进行优化
• 什么情况下不适合使用索引
• 如果数据重复度高，就不需要创建索引，比如重复度超过10%
• 因为会引起大量的回表，可能回不划算
• where中 对索引字段进行表达式计算，会是索引失效
• 要注意联合索引的 字段顺序 如果创建的x,y,z的话，那么如果 用z,y,x会使索引失效
• 索引不是越多越好，因为索引也会占用存储时间，会增加优化器筛选最优索引的时间
• 多表连接的时候，要注意连接顺序，因为不同的顺序，占用的空间资源也不同，不好的顺序会占用很大的空间顺序，好的顺序能最优化空间使用

4.使用Redis和Memcached优化缓存

• 将常用的键值对 存储到内存中，能提高效率

• redis比Memcached 功能要多，比如支持 持久化，set list hash等格式，Memcached只支持 key-value键值对，如果只需要简单的key-value键值对 Memcached就很合适

  5.库级优化

• 可以进行读写分离的方式，让主库进行写，从库进行读

• 分库分表

• 垂直分库
• 如果数据库中的表过多，可以进行垂直分库，将相关联的表放入一个数据库中
• 垂直分表
• 如果一个表中的列过多，可以将表分成多张，将经常一起使用的相同列放到一个表中
• 水平分表
• 如果一个数据表中的数据太多，达到了千万或者已级，可以采用水平分表的方式，将数据分到多个表中，例如可以按照年份分表，2018,2019,2020各自站一个表
• 垂直切分，就是将一个表按照列切成多个表，水平节分是在表中的数据很多的情况下，将数据平摊到多个表中，如果平摊到N张表，那么每个表的压力就变成了M/N

我们还可以减少链表，通过每次查询单表，然后再根据需要，比如通过主键去另一个需要的表中查询数据，在应用中将两个表中的数据拼接起来

数据表的范式

对关系表中，需要对关系内部，各属性之间联系的合理化程度进行定义

数据表设计的范式越高，冗余也就越低，同时高阶的范式一定满足低阶的范式

有时候为了提高查询性能，我们还要进行反范式优化

数据表中的那些键

超键

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

  候选键

• 如果超键不包含多余属性，那么就是候选键

  主键

• 用户从多个候选键中选一个，作为主键

  外键

• 如果一个属性在R1表中不是主键，但是在R2表中是主键

  主属性

• 包含在任一候选键中的属性，为主属性

  非主属性

• 不包含在任一候选键中的属性

  键可能有多个属性组成

  数据表中的范式

  1NF

• 数据表中，任何属性都是原子的不可再分的

• 任何DBS都会满足这一点要求

• 例如，属性X不会再分为X-1和X-2

  2NF

• 任何表中的非主属性和都要和这个表中的候选键有完全依赖关系

• 完全依赖也就不是部分依赖，也就是要依赖候选键的全部属性，而不是部分属性
• 这里我举一个没有满足 2NF 的例子，比如说我们设计一张球员比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键来决定如下的关系：(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)
• 这里比赛场地依赖比赛编号，着就是部分依赖，不符合2NF范式

• 不满足2NF产生的问题

• 数据冗余
• 如果一个球员要参加m产比赛，那么球员的相关信息就重复了N次
• 插入异常
• 如果这时候我们要添加一场新的比赛，但是因为确定球员，导致无法添加
• 删除异常
• 如果我们要删除某个球员，如果没有单独保存比赛表，就会同时吧比赛信息删掉
• 更新异常
• 如果要调整比赛的时间，那么这个表中所有这场比赛的时间都要调整，否则就会出现某一次比赛时间不准确

• 2NF告诉我们 一张表就是一个对象，只表达一个意思，就是单一职责，一张表只有一个变化的原因

  3NF

• 任何非主属性都不能传递性的依赖候选键

• 也就是不存在非主属性A依赖于非主属性B，非主属性B依赖于候选键
• 非主属性，同时另一个非主属性球队教练依赖于球队名称

反范式设计

BCNF范式（巴斯范式）

它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

• 仓库名和管理员是一一对应关系

• 这里 候选键为（仓库名，物品名）和（管理员，物品名），非主属性为 数量

• 上述表中存在问题有

• 如果增加了一个仓库，却没有存放物品，那么 因为主键不能为空值，这就发生了异常
• 如果仓库更换了管理员，就要修改多条数据
• 如果一个仓库的东西卖没了，那么这个仓库也就没了

• 这是因为 仓库名这个主属性和 （管理员，物品名）这个候选键存在部分依赖关系（管理员决定了仓库名）

  反范式

  允许少量的数据容易，用空间换时间，提高查询的效率，减少连表操作

• 比如一个评论表，肯定会关联用户表，如果这两个表的数据都很大，为了获取用户名称进行一次这两个表的连接，可能非常耗时，这是后我们可以讲用户名称作为冗余数据放入评论表中，也就不用在链表了

• 子主题 1

存在的问题

• 数据量小的情况下，不能提现反范式的性能优势，而且如果被冗余的数据被频繁的变动，还会增加更新的复杂度

  适合的场景

• 比如需要当我们生成订单的时候，订单中的地址都需要不随着用户改变地址耳边，就需要做成冗余

• 如果反范式能显著提到性能

• 在数据仓库的设计中，因为数据仓库中的数据主要是历史数据用来分析，对增删改的需求不高，主要是分析数据，就非常是和丛数据冗余历史

  数据仓库和数据库的使用区别

  数据库的目的是捕获数据，数据仓库的目的是分析数据

  数据库对增删改要求较高，一般存储的在线数据，数据仓库存储的都是历史数据

  数据库一般要符合3NF范式，必要的时候 做冗余，数据仓库更倾向于采用反范式

  sql索引概述

  索引就像一本书的目录，他可以帮我们快速进行特定值定位于查找，从而加快数据的查询效率

  通过一颗B+树，减少查询的次数

• 如果不用索引，就要从第一条数据一个个的查看

  索引的价值是让我们在海量的数据中找到想要的数据，如果数据了很少，用不用索引区别都不打

  数据量重复度很大的情况下，例如超过10%也不需要创建索引，假设性别这个字段，100万中一半一半，如果加索引，要访问五十万次索引，在进行50万次回表，不一定比不用索引效率高

  功能逻辑上说：索引的种类

  普通索引

• 普通索引就是单纯的为了提高查询效率，没有做任何约束

  唯一索引

• 在普通索引的基础上，增加了唯一性约束，但可以为null

• 一张表中可以有多个唯一索引

主键索引

• 在唯一索引的基础上增加了 不能为null的约束

• 只能有一个主键索引，这是由主键索引的物理实实现方式决定的，因为，数据只能按照一种顺序进行存储

全文索引

• mysql自带的全文索引只支持英文，全文索引有专门的工具，比如es和solr

  他们三个都是一类索引，只不过约束不同

  物理结构上索引的种类

  聚集索引

• 按照主键来排序存储数据，主键索引后边直接跟着的就是数据，不用在进行回表查询

• 聚集索引指表中数据行按索引的排序方式进行存储，对查找行很有效。只有当表包含聚集索引时，表内的数据行才会按找索引列的值在磁盘上进行物理排序和存储。每一个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

非聚集索引

• 又成为 二级索引或者辅助索引

• 在数据库中，会有单独的空间存放非聚集索引，这些索引也是按照顺序存储（B+树）但是索引指向的内容并不具备和聚集索引一样的顺序，也就是说用聚集索引会进行两次操作，第一次在聚集索引树中找到我们要查询的索引，然后根据索引指向的数据位置，再去获取数据。
• 非聚集索引不像聚集索引一样，将数据放到索引的后边，而是维护单独的索引表（只有索引，没有索引指向的数据），位数据检索提供方便
• 例如我们要查找name叫“CMCC”的人，我们会现在随表表中查找到这个值，然后再根据所以指向的地址，去该地址获取到除了name以外的其他的数据

聚集索引和非聚集索引的区别

• 聚集索引叶子节点存储的就是我们真正的数据，非聚集索引叶子结点存储的是数据的位置，非聚集索引不会影响表的物理存储顺序
• 一个表只能有一个聚集索引，因为只能按照一种顺序存储，但是可以有多个非聚集索引目录，也就是多个索引目录提供检索
• 使用聚集索引的查询效率高，使用非聚集索引的 增加，修改，删除的效率高
• 聚集索引的查询效率要高于非聚集索引

• 对where中的查询条件建立索引，能大幅提高查询效率

  按照字段进行划分

• 单一索引

• 索引列为一列

• 联合索引

• 索引列为多列
• 要注意创建的索引列和使用的索引列的顺序的问题
• 如果创建的时候 索引顺序为 x,y,z 使用的时候确实 z,y,x 那么联合索引就不会生效
• 最左匹配原则
• 也就是按照最左优先的方式进行索引的匹配。比如刚才举例的 (x, y, z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3或者 x=1 AND y =2 或者 x=1，都可以匹配上联合索引；如果查询条件是 WHERE y=2，就无法匹配上联合索引。

索引的原理

我们之所以将索引放到硬盘上，是因为硬盘上的数据是永久存储的

因为我们查询索引的时候，必定会产生IO操作

IO操作耗时比内存中要长的多

我们通过索引来查询数据的时候，需要计算产生的磁盘IO次数，当IO次数多的时候，耗时就会变长

所以如果索引产生的IO次数少，就变相的提高了效率

为什么不用二叉树

1：因为二叉树的深度太大了，这样就会产生过多的IO 因此不适合

2：二叉树不稳定，极端情况下会退化成一条链表，导致产生的IO次数就更多了

3：就算我们用平衡二叉树，因为每个节点只能存储一个数据，依然会造成树的深度很深，IO的操作会很多

B树

B树不止有两个子节点，而是有M个子节点 M >2

B树叫：平衡的多路搜索树

B树的每一个节点，最多 有M个子节点，M叫做B树的阶，从图中可以发现，每个节点都包含x个关键字，和x+1个指针，如果x=100 那么3层高度的B树就可以存储100万的数据

• B树的高度很矮，适合做索引，可以减少IO次数

  B数据的特性：

• 根节点的儿子个数为[2，M]

• 每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为[ceil(M/2), M]。

• 这里之所以是 M/2 是因为 B树是通过分裂来产生新的节点的，每次分裂产生的两个新节点的数据数量为M/2

• 叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为[ceil(M/2), M]。
• 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]<Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …, P[k]，其中 P[1]指向关键字小于 Key[1]的子树，P[i]指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1]的子树。

• 所有叶子节点位于同一层。

  B树的查找过程：（如B树中的图所示）

• 1.确定要查找的关键字9

• 2.和根节点的值相比，比17小，进入p1节点指向的孩子
• 3.9比8大比12小，进入p2指针指向的孩子节点

• 4.在这个模块中找到了9这个节点，将这个节点的数据返回

  B树一个节点中存储了多个数据，并且树的高度矮，发生的IO次数比二叉树更少

  B+树

  

  B+树是在B树基础做出了改进

• 有k个关键字就又k个孩子节点，B树孩子节点比关键字的数量多1

• 非叶子节点中的关键字，也会出现在叶子节点中，并且在叶子节点中位于最大/最小的位置
• 非叶子节点不保存数据，所有的数据都保存在叶子节点中，B树中非叶子节点也会保存数据

• 所有的叶子节点构成一个链表，并且叶子节点中的数据也是按照关键字的大小进行从小到大顺序排序

  查找过程（如图）

• 1.假设查找关键字16

• 2.发现比1大比18小，进入1对应的子节点
• 3.发现比14大，进入14指向的子节点

• 4.在叶子节点发现了我们要的数据

  B+树非叶子节点不存储数据的好处

• 1.B+树访问的效率更稳定，必须都要到叶子节点才能真正需要找到的数据，而B树在非叶子节点也能找到，导致查询不稳定

• 2.B树的查询效率更高，因为B+树比B树更胖更矮，查询所述的IO次数少，同样的磁盘页，B+树存储的索引更多
• 3.仅是对单个关键字的查询上，在查询范围上，B+ 树的效率也比 B 树高。这是因为所有关键字都出现在 B+ 树的叶子节点中，并通过有序链表进行了链接。而在 B 树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多
• 这里之所以这样是因为，每读取一个节点，节点中的数量大小最好不要超过一个磁盘页，因为B树中非叶子节点也会存储数据，就会导致每个非叶子节点会存储的关键字少，也就是变相的说明，B+树的非叶子节点能存储更多的数据，他比B树的高度要低

• 其次，关系数据库中还会大量使用范围查询、有序查询等，比如某时间范围内的用户交易数据。范围查询，这种查询的特点是会大量使用排序，比较，返回结果也往往是多条。 如果使用b树的话，需要使用中序遍历，因为数据节点不在同一层上，会频繁引起io，从而导致整体速度下降。而在b+树中，所有的数据节点都在叶子节点，相近的叶子节点之间也存在着链接，因此会节约io时间。这样，b+树整体上就比b树要快。 其实，b+树主要应用于关系型数据库中。也有使用b树做索引的数据库，比如mangodb，做索引
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash索引

原理

相同的输入，永远得到相同的输出，如果输入的内容有微小的不同，输出也会产生很大的偏差

使用确定性的算法，将输入变成输出，比如MD5

例如可以用来比较两个文件是否相同

MYSQL中的hash索引

对于单个数据的查询，Hash索引的效率非常高，比B+树要快，因为他不用进行那么多次的IO查询

键值 key 通过 Hash 映射找到桶 bucket。在这里桶（bucket）指的是一个能存储一条或多条记录的存储单位。一个桶的结构包含了一个内存指针数组，桶中的每行数据都会指向下一行，形成链表结构，当遇到 Hash 冲突时，会在桶中进行键值的查找。

HASH索引和B+树索引的区别

hash索引不能进行范围查询，但是B+树可以，因为Hash索引是无序的，B+树是有序的

hash索引不支持 最左匹配原则，因为hash索引在计算的时候，会把几个列的值拼到一起，看成一个值，进行统一计算，而不会想B+树那样，会单独计算每个列的索引，因此用到联合索引中的一个或几个列的时候，联合索引无法被使用

hash索引也无法使用 模糊查询和排序，因为hash索引是无序的，必须通过确定的key才能找到对应的value，B+树可以排序并且 可以 ‘xxx%’格式的数据进行索引查询

hash索引不会用到重复数据很多的列上，这样会产生大量的hash冲突，例如性别字段

Redis的核心就是 hash表

mysql只有memory引擎支持hash存储，如果我们用到查找的临时表就可以用 memory引擎

把某个字段设置为 Hash 索引，比如字符串类型的字段，进行 Hash 计算之后长度可以缩短到几个字节

mysql的innodb引擎在发现某个索引使用的频率很高的时候，就会在b+树的基础上为其建立hash索引，

hash索引只能满足 > < = IN的查询，不能进行范围查询

B+树索引是MySQL的默认索引机制，也是大部分因为可以使用范围搜索，可以很容易对数据进行排序操作，在联合索引中也可以利用部分索引建进行查询。这些情况下，我们都没法使用Hash索引，是因为Hash索引仅能满足=, <>, IN查询，不能使用范围查询，同时因为数据的存储是没有顺序的，所以在ORDER BY的情况下，还需要对数据重新进行排序。而对于联合索引的情况，Hash值是针对联合索引建合并后一起来计算Hash值，因此无法对单独的一个键或者几个索引键进行查询。

那什么情况下才会使用自适应Hash索引呢？如果某个数据经常会访问到，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。需要说明的是：

• 默认使用B+树作为索引是因为B+树存在着以上的优点，那为什么还需要自适当Hash索引呢？这里，需要了解Hash索引的特点，因为Hash索引结构的特点，导致它的检索数据效率非常高，通常只需要O(1)的复杂度，也就是一次就可以完成数据的检索。虽然Hash索引的使用场景有很多限制，但是优点也很明显，所以MySQL提供了一个自适当Hash索引的功能（Adaptive Hash index）。注意，这里的自适应指的是不需要人工来制定，而是系统根据情况来自动完成的。
• 1）自适应哈希索引只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，可以让自适应Hash放到缓冲池中，也就是InnoDB buffer pool，进一步提升查找效率。
• 2）InnoDB中的自适应Hash相当于是“索引的索引”，采用Hash索引存储的是B+树索引中的页面的地址。这也就是为什么可以称自适应Hash为索引的索引。采用自适应Hash索引目的是可以根据SQL的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应Hash索引可以提高数据的检索效率。
• 3）自适应Hash采用Hash函数映射到一个哈希表中，所以对于字典类型的数据查找非常方便哈希表是数组+链表的形式。通过Hash函数可以计算索引键值所对应的bucket（桶）的位置，如果产生Hash冲突，如果产生哈希冲突，就需要遍历链表来解决。

• 4）是否开启了自适应Hash，可以通过innodb_adaptive_hash_index变量来查看，比如：mysql> show variables like ‘%adaptive_hash_index’;

  所以，总结下InnoDB本身不支持Hash，但是提供自适应Hash索引，不需要用户来操作，而是存储引擎自动完成的。自适应Hash也是InnoDB三大关键特性之一，另外两个分别是插入缓冲（Insert Buffer）和二次写(Double Write)。

  索引的使用原则

  创建索引的规律

  1.字段的值有唯一性的限制，比如登录名

• 这样我们可以创建唯一索引或者主键索引

  2.频繁作为where查询的条件，尤其是在表数据量大的情况下

• 创建普通索引就能大幅度的提高效率

  3.需要group by和order by的列

• 索引就是按照某种顺序对数据进行存储和检索

• 因此，需要被group by和order by的列，都可以建立索引加快顺序

4.多个单列索引在多条件查询的时候，只会生效一个（mysql会选择限制最严格的哪一个索引）

• 索引在创建多条件联合查询的时候，最好创建联合索引

• 例如 group by a order by b 就创建 a,b的联合索引（因为查询的时候，先 group by 再 order by）

5.update，delete的where条件，一般也要创建索引

• 因为我们需要先通过where后边的条件，将这条数据检索出来以后，才能进行更新或删除操作，对于更新来说，如果更新的字段不是索引字段，效率会更高，因为不用维护索引

  6，如果索引太多，在更新数据的时候，会设计到很多索引的同步更新，会消耗更多的资源

  7.DISTINCT字段也需要创建索引

• 这是因为，索引会对数据进行排序，去重的时候，也就会快很多

  在多表join操作的时候，要注意的事项为：

  链表的数量最好不要超过三个

  对于连接的字段创建索引，并且连接的字段要保持一种类型比如都为int

  什么时候不需要创建索引

  where字段（包括 GROUP BY、ORDER BY）中用不到的字段，不需要创建索引，创建索引的价值是快速定位数据

  如果表的记录很少，也不需要创建索引

  对于大量重复的字段，也不需要创建索引，不如性别字段，但是也不能一概而论

  频繁更新的字段，也不一定要创建索引，因为跟新字段的同时还要去维护索引，也会造成性能损耗

  个人理解：在进行大范围查询的时候，尤其是没有用聚集索引，效率不一定比全表扫描更高。例如 a like ‘234%’ or b > 2000这种 或 a > ‘sadf’ 因为非聚集索引需要大量的回表

  索引失效

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

• 例如：WHERE comment_id+1 = 90000

• 因为我们需要把索引字段的取值都取出来，然后进行表达式的计算来进行条件判断，因此也就是采用的全表扫描的方式，运行消耗也就高很多

  2.如果使用了函数，索引也会失效

• 例如：WHERE SUBSTRING(comment_text, 1,3)=’abc’

  3.在where条件中，如果对or前的列设置了索引，对or后的列没有用索引，也会导致索引失效

• 因为 要对or前后的字段都进行过滤，因此没有设置索引的列就要进行全表扫描，那么设置索引的列也就没有了使用索引的意义，就会导致索引失效

• 注意这里不一定非要用联合索引

4.使用like的时候，如果前面是 % 就会导致索引失效

• 这个很好理解，如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了

  5.索引列尽量设置为 not null 约束

• mysql 文档也建议我们 尽量将数据表的字段设置为not null，这样可以更好的使用索引和节省空间，设置加速sql的运行

• 判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 (‘’)。
• null值会占用更多的字节,且会在程序中造成很多与预期不符的情况

6.在使用联合索引的时候，要注意最左匹配原则

• 一条语句可以只是用联合索引的一部分，但是要注意从最左边开始

  我们要尽可能扩展索引，而不是新建索引，因为索引数量过多需要维护的成本也会变大，导致写效率变低。同时，我们还需要定期查询使用率低的索引，对于从未使用过的索引可以进行删除，这样才能让索引在 SQL 查询中发挥最大价值。

  从数据页的角度理解B+树

  索引信息和数据记录都存储在页结构上

  页是数据库存储空间的基本单位

  数据库读取数据，无论是读取一行还是多行，每次读取的都是一个数据页

• 因此，数据页是数据库存储的最小单位

  一个页可以存储多行记录

  数据页的结构

• 数据页（保存B+树节点），系统页，Undo页和事务页

• 数据页使我们最常使用的页

表页的长度限定了表行的最大值，

一个表空间包含多个段，一个段包含多个区，一个区包含多个页，一个页包含多行数据

区

区是比页大一级的单位，一个区可以放64个连续的页，innode中页的大小默认为16k，因此 区的大小为1M

段

一个或多个区组成

• 区在文件系统是一个连续分配的空间，但是段不要求区和区之间是连续的，
• 段是数据库的分配单位，不同类型的数据库对象以不同的段形式存在

• 例如创建一个索引的时候，就会创建一个索引段，创建一个表的时候就会创建一个表段

表空间

表空间是一个逻辑容器，存储的内容是段

• 一个段只能属于一个表空间，但是一个表空间可以有多个段

  数据库又一个或多个表空间构成

• 系统表空间，用户表空间，撤销表空间，临时表空间

  共享表空间

• 多张表共用一个表空间

  独立表空间

• 每张表用一个表空间

• 也就是数据和索引信息都会保存在自己的表空间中
• 独立的表空间可以咋不同的数据库之间进行迁移

innodb_file_per_table=ON，这就意味着每张表都会单独保存为一个.ibd 文件（也就是默认是独立表空间）

数据页的结构

数据页结构描述

• File Header

• 38字节
• 文件头，描述页的信息

• Page Header

• 56字节
• 页头，页的状态信息

• Infimum+Supremum

• 26字节
• 最小和最大记录，这是两个虚拟的行记录

• User Records

• 不确定
• 用户记录，存储行记录内容

• Free Space

• 不确定
• 页中还没有被使用的空间

• Page Directory

• 不确定
• 页目录，存储用户记录的相对位置

• File Trailer

• 8字节
• 文件尾，校验页是否完整

文件头通用部分

• 文件头和文件尾
• 类似于集装箱，将页的内容进行封装，通过文件头和文件尾校验的方式来确保页的传输是完整的。

• 通过hash算法来校验文件的完整性

• 文件头中有两个指针，分别指向前一个数据页和后一个数据页，连起来的数据页相当于双向链表

•

• 链表的方式，可以让数据页之间保持逻辑连续不用物理连续

记录部分

• 页主要是记录用户数据

• 最小最大记录，用户记录占用了页的大部分空间
• 空闲空间在有新的记录插入的时候，就会使用此部分空间数据

索引部分

• 主要是指 “页目录”这个空间
• 因为记录的存储是以单向链表的方式存储的，查找起来不方便，因此页目录提供了二分查找的功能，用来提高查找的效率

• 将所有记录分为n组，这些记录包含最小记录和最大记录，但不包含标记为已删除的记录
• 第一组，也就是最小记录所在的组只有一条数据，最大记录所在的组有1-8条数据，其他组会有4-8条数据之间，这样除了第一组，其他组的数据几乎平分
• 在<每个组>中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。
• 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录
•

• 页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找。这里我以上面的图示进行举例，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 9 的用户记录，我们初始化查找的槽的下限编号，设置为 low=0，然后设置查找的槽的上限编号 high=4，然后采用二分查找法进行查找。首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2，这时我们取编号为 2 的槽对应的分组记录中最大的记录，取出关键字为 8。因为 9 大于 8，所以应该会在槽编号为 (p,high]的范围进行查找接着重新计算中间位置 p’=(p+high)/2=(2+4)/2=3，我们查找编号为 3 的槽对应的分组记录中最大的记录，取出关键字为 12。因为 9 小于 12，所以应该在槽 3 中进行查找。遍历槽 3 中的所有记录，找到关键字为 9 的记录，取出该条记录的信息即为我们想要查找的内容。

从数据页的角度看B+树的构造

• 每个节点就是一个页，每次新建节点，就会申请一个页空间
• 同一层的节点还会进行双向链表的连接
• 非叶子节点，存在多个索引行，每个索引行存储索引和指向下一层的指针

• 叶子节点，它存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表，但是对记录进行查找，则可以通过页目录采用二分查找的方式来进行

  检索过程

• 通过根节点层层向下筛选，直到叶子节点位置

• 将要找的叶子节点的数据页加载到内存中

• 通过二分查找的方式，找到一个分组，在从分组中遍历获取我们需要的数据

  普通索引和唯一索引查询效率的不同

  InnoDB 存储引擎的页大小为 16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次“判断下一条记录”的操作，对于 CPU 来说，这些操作所消耗的时间是可以忽略不计的。所以对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

  从磁盘IO角度理解SQL查询成本

  数据库的基本存储单位是页，每次数据库都会读取一个页到缓存中

  我们读取一行，会加载一个页，是因为页是数据库的最小存储单位

  数据库会采用缓存池的方式提高页的查找效率

  数据库缓冲池

  数据库会尽量将经常使用的页保存起来，放入缓冲区中，在进行sql查找的时候，先去看缓冲区中是否有对应的数据，有就直接使用，没有就去磁盘找，找到后放入缓冲区进行使用

• 缓存在数据库中的结构和作用

•

他能让磁盘iO最小化，减少与磁盘交互而浪费的时间，提高查询的效率

当我们对数据库中的数据进行修改的时候，数据会立刻同步到磁盘？

• 当我们对数据库进行修改的时候，它会先修改缓冲区中的数据，然后以一定的频率将数据刷到磁盘，以提高效率

• 采用了checkPoint的机制回写到磁盘
• 比如当缓冲区不够用的时候，就会采用checkPoint的方式，将脏页刷新到磁盘。
• checkPoint就是将缓冲池中的脏页刷新回磁盘，不同之处在于每次从哪里取多少脏页刷新到磁盘，以及什么时候触发checkpoint
• 当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间
• 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。
• 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的，重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

缓冲池污染

• 某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

  缓冲区的大小

  MyISAM

• 改引擎只缓存索引，不缓存数据，可以用key_buffer_size关键字查看

• SHOW VARIABLES LIKE ‘key_buffer_size’

InnoDB

• 可以容如下命令查看

• show variables like ‘innodb_buffer_pool_size’
• 默认只有 8388608/1024/1024=8MB
• 可以用 set global innodb_buffer_pool_size = 134217728将大小设置为128m

• 在这个引擎中，我们可以开启多个 缓冲区

• show variables like ‘innodb_buffer_pool_instances’
• 你能看到当前只有一个缓冲池。实际上innodb_buffer_pool_instances默认情况下为 8，为什么只显示只有一个呢？这里需要说明的是，如果想要开启多个缓冲池，你首先需要将innodb_buffer_pool_size参数设置为大于等于 1GB，这时innodb_buffer_pool_instances才会大于 1。你可以在 MySQL 的配置文件中对innodb_buffer_pool_size进行设置，大于等于 1GB，然后再针对innodb_buffer_pool_instances参数进行修改。

数据页加载的三种方式

如果缓冲池中没有该页数据，那么缓冲池有以下三种读取数据的方式，每种方式的读取效率都是不同

• 内存读取

• 如果该数据存在于内存中，基本上执行时间在 1ms 左右，效率还是很高的
•

• 随机读取

• 可以看到一次读取要花费将近10ms的时间，而且这还只是读取一个页
•

• 顺序读取

• 他是批量读取，相当于是预读取，这其实是一种 数据临时使用技术，就是当一个数据被使用的时候，他附近的数据很可能也被使用
• 顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。如果一个磁盘的吞吐量是 40MB/S，那么对于一个 16KB 大小的页来说，一次可以顺序读取 2560（40MB/16KB）个页，相当于一个页的读取时间为 0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

通过 last_query_cost 统计sql的查询成本

一条sql运行的时候，需要确定查询计划，mysql会在多个查询计划中选择花费最少的一个，最为最终的执行计划

当我们想要查看sql的执行成本的时候，可以再这条sql执行完后，通过查看当前会话的中 last_query_cost变量来查看

• 这个成本对应要查询的页的数量
• SHOW STATUS LIKE ‘last_query_cost’;

• 比如先执行 select * from xxx 然后就执行这个语句
• 举例比如我们先执行
•

•

• 虽然这里多了很多页，但是查询时间并没有变化太多，这就是因为顺序查找

为什么没有理想的索引

索引片

就是sql语句查询的过程中需要扫描的一个索引片段

• 它包含两种索引

• 窄索引
• 索引片只包含一到两列
•

• 宽索引
• 索引片包含多列
•

如果索引片越宽，那么顺序访问的索引页也多

• 如果索引片越窄，就会减少索引访问的开销

  非聚集索引都会保存主键，用来通过主键进行回表查询数据

• 因此无论是窄索引还是宽索引都包含了主键

  通过宽索引避免回表

  虽然宽索引要顺序扫描的索引页很多，但是他在一定情况下可以避免根据主键回表

• 这是因为如果我们查询的索引列都在索引中，那么就不用回表了

  过滤因子

  他描述了谓词的选择性

• 在where条件中，每个过滤条件都称为一个谓词

• 谓词的选择性等于满足这个谓词条件的条数/总条数

比如 有一张 职工表，他有 id 性别 年龄 职位

• 性别 = 男就不是一个好的过滤因子，因为他只能过滤50%
• 职位=经理 就是个好的过滤条件，因为经理更少
• 如果我们吧 性别 和 职位 作为一个联合索引，那么过滤性就根强

• 联合索引的各个列之间尽可能的独立
• 例如 性别和孕妇就不是一个好的联合过滤 因子

你能看到过滤因子决定了索引片的大小（注意这里不是窄索引和宽索引），过滤因子的条件过滤能力越强，满足条件的记录数就越少，SQL 查询需要扫描的索引片也就越小

如何设计索引

首先一张表的索引不宜过多，否则对这张表的增加和修改，都会产生额外的负担

当我们新增索引的时候，考虑在原有的索引片上增加索引，也就是采用符合索引的方式，而不是新增索引

同时我们还要定时的检查索引的使用情况，对不常用的索引及时删除，从而减少索引的数量

• 除非非条件列字段少，或经常用到

  innodb中 默认单列索引的长多最多为767byte，如果超过了这个长度，就会取前255个字符

• 尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引

锁

实现加锁是为了保证数据一致性，让数据的中间状态对外不可见

锁粒度的大小上来划分

行锁

• 就是按照行的粒度对数据进行锁定

• 锁的粒度小，冲突概率低，并发度高
• 但是锁的开销比较大，加锁比较慢，容易发生死锁

页锁

• 就是对一页的数加锁，会出现少量的数据浪费，因为页中会有多行数据，并发度，锁粒度都介于行锁和表锁之间，也会出现死锁

  表锁

• 就是锁定一整张表，锁的粒度很大，锁冲突的概率很高，数据访问并发度低，

•

innodb只支持行锁和表锁

每个层级的锁的数量都是有限的，因为锁要占用内存空间，锁空间大小是有限的，当某一层级的锁找过允许的范围的时候，就会发生锁升级

对于innodb来说，也就是行锁升级为表锁

• 这样占用的锁空间小了，但并发度也下来了

  从数据库管理的角度对锁进行分解

  共享锁

• 页脚读锁或S锁

• 被共享锁锁定的数据只能被读取，不能被需改
• 在进行select的时候会对资源进行锁定，当数据读取完后释放共享锁
• 这样保证数据被读取时不被需改

• 比如我要想给product_comment表上加上共享锁可以用这个语句

• LOCK TABLE product_comment READ;
• 此时更新这个表中的数据 ，就会返回一下错误
• RROR 1099 (HY000): Table ‘product_comment’ was locked with a READ lock and can’t be updated

• 解锁语句

• UNLOCK TABLE;

• 如果我们想给某一行加上共享锁，可以用下边这个语句

• SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 LOCK IN SHARE MODE

排它锁

• 也叫独占锁，写锁或者X锁

• 排它锁的数据只允许进行锁定操作的事务使用，其他事务无法对锁定的数据进行修改和查询

• 用如下语句给表 product_cooment加上独占锁

• LOCK TABLE product_comment WRITE;
• 此时其他事务如果想要在 product_comment 表上查询数据，则需要等待

• 用 UNLOCK TABLE;来释放锁
• 同样我们也可以在某行数据上添加排它锁

• SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 FOR UPDATE;

• 而且当我们使用 INSERT UPDATE DELETE操作某行数据的时候，数据库会自动在这行数据上加上排它锁

  意向锁

• 意向排它锁

• 当我们想要获取某个数据表的排它锁的时候，需要先看下这张数据表有没有上了排它锁。如果这个数据表中的某个数据行被上了行锁，我们就无法获取排它锁。这时需要对数据表中的行逐一排查，检查是否有行锁，如果没有，才可以获取这张数据表的排它锁。这个过程是不是有些麻烦
• 意向锁就是用来解决这个问题
• 如果我们给某一行加了锁，数据库就会自动的在更大一级的空间，比如数据页或者数据表上加上意向排他锁，告诉别人这里已经有人上过排它锁了，别人想要获取数据表排它锁的时候，只需要检查是否已经有人上了这个表的意向排它锁就行了
• 事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁

• 意向共享锁

• 如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁

• 意向共享锁和意向排它锁会告诉其他事务，已经有人锁定了表中的某些记录，不能对整个进行全表扫描

  乐观锁和悲观锁

  乐观锁

• 乐观锁就是根据版本号或者时间戳的原理，来判断更新是否冲突

• 乐观锁适合读多的场景，相对来说写比较少

• 乐观锁

• 悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

，乐观锁和悲观锁并不是锁，而是锁的设计思想

死锁

在客户端 1 获取某数据行共享锁的同时，另一个客户端 2 也获取了该数据行的共享锁，这时任何一个客户端都没法对这个数据进行更新，因为共享锁会阻止其他事务对数据的更新，当某个客户端想要对锁定的数据进行更新的时候，就出现了死锁的情况。当死锁发生的时候，就需要一个事务进行回滚，另一个事务获取锁完成事务，然后将锁释放掉，很像交通堵塞时候的解决方案。

以下为锁和死锁

锁的类型和加锁原理

表锁

一般都是DDL处理的时候使用，对整个表加锁，比如ALERT操作

由Mysql Server实现

• MyISAM还能用Mysql Server提供的表锁

  也可以明确指定加表锁

• lock tables user read/write

• wirte可以读写表
• read只能读表

• 多个表加锁

• lock tables user read,role read

• 当我们添加一个表锁以后，在这个事务中，便不能读取其他没有在这个事务中加锁的表

•

• 只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁

读锁之间互相也阻塞

释放表锁方法

• unlock tables 语句释放
• 会话持有其他表锁的时候，执行 lock table 语句，会释放会话之前持有的表锁

• 会话持有表锁时，执行 start transaction或begin的时候，也会释放锁

  行锁

  锁定一行或某几行，或行与行之间的间隙

  由存储引擎实现

• Innodb支持行锁

  mysql使用的是聚簇索引，对于二级索引，他需要主键去聚簇索引中去找对应的数据

• 因此对于非聚簇索引来说，他会同时锁住非聚簇索引对应的索引行，以及主键索引对应的索引行

•

• 使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁

  读锁（共享锁，S锁）

• 读锁允许多个事务共同持有，但是不能修改，也就是和写锁互斥

• select * from table lock in shared modle

写锁又称排它锁或互斥锁

• 读锁与读锁互斥，也与写锁互斥，一个事务对某一行加锁读锁，便不能被其他任何事务访问这行数据

• 插入，更新，删除等DML语句都会触发互斥锁
• select * from table for update

读写意向锁

• 表示表中的行是否加了读锁，或者写锁
• 想加表锁的时候，不需要遍历所有的行
• 意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。
• 读意向锁：IS

• 写意向锁：IX

  AUTO_INC自增锁

  它是一种表锁，当有自增主键的时候存在，为了防止出现主键冲突

  mysql5.1.2以后，他的效率有了很大的提升，具体表现在，当有一批要插入的数据的时候，他不在是一个个的分配id，而是一批一批的分配id

  各个锁之前的兼容矩阵

  

• 意向锁之间互不冲突

• S锁只和S/IS锁兼容，和其他锁都冲突
• X锁和其他所有锁都冲突

• AI锁只和意向锁兼容

  行锁的分类

  Record Lock 记录锁

• 只锁住对应的记录

• 例如 update book set author = ‘wf’ where id = 10 只锁id等于10这条记录

Gap Lock 间隙锁

• 锁住记录与记录之间的间隙

• 还是上边id=10的这个例子：如果id=10这条记录不存在，在RC隔离级别下不加锁，在RR隔离级别下会加间隙锁
• 间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。
• 间隙锁和间隙锁之间是不冲突的，他的唯一目的就是为了防止其他事务的插入

Next-Key Lock

• 锁住记录以及记录之前的间隙

• 它指的是加在某条记录以及这条记录前面间隙上的锁

• 15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下

•

• 此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录
• 假设我们更新的是49（非唯一索引） 那么它锁的范围是 以49为中心的前后空隙和49这条记录，其他的记录不会缩

• 只有RR的隔离级别下才有

  插入意向 GAP 锁

• 称为 II GAP

• 只有插入的时候，才有这个锁

• 插入意向锁和插入意向锁之间不冲突
• 插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

• 插入意向锁和 X锁和S锁不兼容

  

  不同类型的锁的兼容矩阵

• 
  

• 插入意向锁不影响其他事务加其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务是没有任何影响的
• 插入意向锁与间隙锁和 Next-key 锁冲突。也就是说，一个事务想要获取插入意向锁，如果有其他事务已经加了间隙锁或 Next-key 锁，则会阻塞。
• 间隙锁不和其他锁（不包括插入意向锁）冲突
• 记录锁和记录锁冲突，Next-key 锁和 Next-key 锁冲突，记录锁和 Next-key 锁冲突

• 第一行表示已有的锁，第一列表示要加的锁

  对于更新的时候，where后边的条件是非索引的话，那么他会锁表，只不过他会把表中每一行数据都锁上，表面上还是行锁，但是实际上锁了这个表

  

  这里X锁的哪一行，就是用的非索引，S锁的哪一行就是用的索引，而且，为了区分，我选了两个间想差2万行的数据，但是依然是无法更新，因此可以推断锁的是整个表，但是用的不是表锁，还是行锁

  如果先用 lock table float4锁表的话，在 INNODB_LOCK中看不见任何记录

• 
  

• 用这个语句发现锁表了

  死锁

  数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式，锁类型和锁数量

  

  不客户从复读的级别下，其实就已经解决了幻读问题

  通过锁和MVCC

  快照读

  RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）

  当前读

  不在读取快照版本，并对数据进行加锁

• SELECT … LOCK IN SHARE MODE：加共享(S)锁

• SELECT … FOR UPDATE：加排他(X)锁

• INSERT / UPDATE / DELETE：加排他(X)锁

  RC只会加记录锁，RR除了会加记录锁，还会加间隙锁，用来解决幻读问题

  不同sql语句对加锁的影响

  SELECT … 语句正常情况下为快照读，不加锁；

  SELECT … LOCK IN SHARE MODE 语句为当前读，加 S 锁；

  SELECT … FOR UPDATE 语句为当前读，加 X 锁；

  常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；

  常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚

  索引也会对加什么锁产生印象

  数据库中的数据也会对索引产生影响

• 例如更新时使用主键，主键存在只加记录锁，不存在，不存在则加间隙锁

  具体场景分析

  

• 其中 id 为主键，ISBN（书号）为二级唯一索引，Author（作者）为二级非唯一索引，score（评分）无索引

  UPDATE语句场景分析

• 分析原则也适用于 UPDATE,DELETE 和 SELECT … FOR UPDATE等当前读

• 聚簇索引，查询命中

• UPDATE book SET score = 9.2 WHERE ID = 10
• RR和RC级别下，都对id=10这条记录加排他记录锁

• 聚簇索引，查询未命中

• UPDATE book SET score = 9.2 WHERE ID = 16 16在表中不存在
• RC级别下，不需要加锁
• 在RR级别下，在id=16前后都加间隙锁（10,18）
• 间隙锁之间不冲突，他的唯一目的就是防止插入，产生幻读
•

• 二级唯一索引，查询命中

• UPDATE book SET score = 9.2 WHERE ISBN = ‘N0003’
• 二级索引保存着主键索引的值，然后拿着主键索引去获取真正的数据
• 所以此时二级索引和主键索引都加排它锁
•

• RR和RC都加排它锁

• 二级唯一索引，查询未命中

• 因为 N0008 大于 N0007，所以要锁住 (N0007,正无穷)这段区间，而 InnoDB 的索引一般都使用 Suprenum Record 和 Infimum Record 来分别表示记录的上下边界。Infimum 是比该页中任何记录都要小的值，而 Supremum 比该页中最大的记录值还要大，这两条记录在创建页的时候就有了，并且不会删除。
• 所以在N0007和supernum Record之间加上间隙锁
•

• 这里不需要在主键上加上间隙锁
• 因为他说的区间是根据我们更新的唯一索引的间隙锁，无论怎么更新，只要落在这个间隙里边，就不行，因此不需要加主键锁

• 二级非唯一索引，查询命中

• UPDATE book SET score = 9.2 WHERE Author = ‘Tom’
• 在RC的情况下
•

• 在涉及的二级索引和对应的主键索引上加上排他记录锁
• 在RR的情况下
• 他不仅对涉及的二级索引和主键索引加了排它锁，还在非唯一二级索引上加了三个间隙锁
• 锁住的是两个Tom索引值相关的三个范围
•

• 间隙锁会和右侧的记录锁组合成为Next-key锁
• 所以实际上有两个Next-Key 和一个间隙锁（Tom，正无穷）
• 唯一索引和主键索引都已经保证了该索引值肯定只有一条记录，所以不需要加间隙锁

• 二级非唯一索引，查询未命中

• UPDATE book SET score = 9.2 WHERE Author = ‘Sarah’
• 在RR隔离级别下
• 他会在二级索引Rose和Tom之间加间隙锁
•

• 在RC隔离级别下
• 不需要加锁

• 无索引

• 当where从句中的条件不使用索引的时候，则会对全表进行扫描
• RC隔离级别
• 对所有的数据加排他记录锁
• RR隔离界别
• 加所有数据的排他记录锁，以及记录于记录之间的间隙锁
• RR和RC加锁图示
•

• 间隙锁会和左侧的记录组成Next-key锁

• 聚簇索引，范围查询

• 这里分析范围查询的的加锁情况
• UPDATE book SET score = 9.2 WHERE ID <= 25
•

• RR和RC情况下的加锁
• 在RR情况下
• 加上间隙锁，和对应的记录锁合并称为 Next-Key 锁。除此之外，它还会在(25, 30] 上分别加 Next-Key 锁。
• （25，,3]上加的锁很奇怪
• 在RC情况下
• 只会对存在的每条记录加行锁

• 二级索引，范围查询

• UPDATE book SET ISBN = N0001 WHERE score <= 7.9
•

• 这里 score扮演的是二级索引的角色
• 他在二级索引上加了Next-key锁，在主键索引上加了排他记录锁

• 修改索引值

•

• 这里 下边的图片意思是，原本的数据中，就有了一个 id=60和Author=John了
• UPDATE book SET Author = ‘John’ WHERE ID = 10
• 原ID=10中，author的值为 Bob
• 因此这个语句会把 主键id=10的记录进行排他行锁，然后还会对 二级索引上 Bob和John这两个索引值对应的记录加排他行锁
• 这里要求ID是主键索引，Author是二级唯一索引

• delete加锁分析

• 一般来说，DELETE 的加锁和 SELECT FOR UPDATE 或 UPDATE 并没有太大的差异。因为，在 MySQL 数据库中，执行 DELETE 语句其实并没有直接删除记录，而是在记录上打上一个删除标记，然后通过后台的一个叫做 purge 的线程来清理。从这一点来看，DELETE 和 UPDATE 确实是非常相像。事实上，DELETE 和 UPDATE 的加锁也几乎是一样的。

• Insert 加锁情况

• Insert语句在两种情况下会加锁
• 为了防止幻读，如果记录之间加有间隙锁，此时不能insert
• 如果Insert的记录和已有记录造成唯一键冲突，
• Insert语句的锁都是隐式锁
• 隐式锁是Innodb实现的一种延迟加载的机制，来减少加锁的数量
• 特点：只有在发生可能发生冲突时才加锁，减少了锁的数量
• 具体的Instert语句加锁流程
• 首先对插入的间隙加插入意向锁
• 如果这个间隙已经被加上了 Next-Key 或者间隙锁，则加锁失败
• 如果没有，则加锁成功，可以插入数据
• 判断插入记录是否有唯一键，如果有，则进行唯一性约束
• 如果不存在相同键值，则完成插入
• 如果存在相同键值，则判断该键值是否加锁
• 如果没有锁，判断该记录是否被标记为删除
• 如果标记为删除，说明事务已经提交，还没来得及 purge，这时加 S 锁等待；
• 如果没有标记删除，则报 duplicate key 错误；
• 如果有锁，证明这个唯一键值已经存在，可能正在被新增，也可能要在被修改和删除，且事务还未提交，加S锁等待
• 插入记录，并对插入的记录做X锁

用 Explain 命令分析 MySQL 的 SQL 执行

explain的关键输出项

select_type

• simple

• 简单查询，不包含子查询和union，就是最简单的查询语句
•

• 包含子查询

• primary
• 是sql中包含复杂的子查询，此时最外层查询标记为该值
• derived
• SQL中from子句中包含的子查询被标记为该值，MYSQL会递归执行这些子查询，把结果放入临时表
•

• subquery
• 在select或where中包含的子查询，被标记为该值
•

• dependent subquery
• 子查询中的第一个 select，取决于外侧的查询，一般是 in 中的子查询。
•

• union查询

•

• union
• 是 SQL 在出现在 union 关键字之后的第二个 select ，被标记为该值；若 union 包含在 from 的子查询中，外层select 被标记为 derived
• union result
• 从 union 表获取结果的 select
• 这里我测试的是 只有在union下才可行，union all不会有这个 union result行
• dependent union
• 也是 union 关键字之后的第二个或者后边的那个 select 语句，和 dependent subquery 一样，取决于外面的查询
•

type

• 连接类型

• 性能从高到低排序为：
• system，const，eq_ref，ref，range，index 和 all
• system
• 表示表只有一条数据，相当于系统表
• 如图例所示，因为 from 的子查询派生的表只有一行数据，所以 primary 的表连接类型为 system。
•

• const
• 通过索引一次就找到，只匹配一行数据，用于常数值比较PRIMARY KEY 或者 UNIQUE索引
•

• eq_ref
• 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描。对于每个来自前边的表的行组合，从该表中读取一行。它是除了 const 类型外最好的连接类型
•

• 对表 t1 查询的 type 是 ALL，表示全表扫描，然后 t1 中每一行数据都来跟 t2.id 这个主键索引进行对比，所以 t2 表的查询就是 eq_ref
• 这里就要注意要谁去all表了
• ref
• 非唯一性索引扫描，返回匹配某个单独值的所有行，和 eq_ref 的区别是索引是非唯一索引
•

• range
• 只检查给定范围的行，使用一个索引来选择行，当使用 =, between, >, <, 和 in 等操作符，并使用常数比较关键列时
•

• 上图中：id为唯一索引，val为费唯一索引
• index
• index 与 ALL 类型类似，唯一区别就是只遍历索引树读取索引值，比 ALL 读取所有数据行要稍微快一些，因为索引文件通常比数据文件小。这里涉及 MySQL 的索引覆盖
• select id from book
• id为主键，就会发生索引树的遍历

possiblekeys，key 和 keylen

• possiblekeys

• possible_key 列指出 MySQL 可能使用哪个索引在该表中查找。如果该列为 NULL，则没有使用相关索引。需要检查 where 子句条件来创建合适的索引提高查询效率

• key

• key 列显示 MySQL 实际决定使用的索引。如果没有选择索引，则值为 NULL。

• key_len

• 显示 MySQL 决定使用索引的长度。如果键为 NULL，则本列也为 NULL，使用的索引长度，在保证精确度的情况下，越短越好。因为越短，索引文件越小，需要的 I/O次数也越少。

• 这是我们分析加锁场景的回收，最关心的字段

  ref

• 表示使用其他表的哪个列或者常数来从表中选择行

•

• t2 读取数据时，要判断 t2.id = t1.id，所以 ref 就是 mysql.t1.id

row

• 表示mysql认为他执行查询时必须检查的行数

  filtered

• 表明了 SQL 语句执行后返回结果的行数占读取行数的百分比，值越大越好。MySQL 会使用 Table Filter 来读取出来的行数据进行过滤，理论上，读取出来的行等于返回结果的行数时效率最高，过滤的比率越多，效率越低。

•

• t1表中有三条数据，rows 为 3，表示所有行都要读取出来。根据 val = 3 这个 table filter 过滤，只返回一行数据，所以 filtered 比例为33.33%

extra

• 包含了不在其他列中，但是十分重要的其他信息
• using index

• 表示select语句使用了索引，避免了扫描表的数据行，效果不错
• 这里说明，获取数据的时候不用回表

• using where

• 读取数据后，使用了 Table Filter 进行过滤
• 使用了using where 不代表没有用索引，用没用索引主要看type列

• 因为 id 和 val 都是有索引的，所以 select * 也是可以直接使用覆盖索引读取数据，所以 extra 中有 using index。而因为只使用 val 索引读取了3行数据，还是通过 where 子句进行过滤，filtered为 55%，所以 extra 中使用了 using where

using filesort

• MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，若出现该值，应该优化 SQL 语句
• 
  

• 其中 val 列没有索引，所以无法使用索引顺序排序读取。

using join buffer

• 使用了连接缓存

  Using temporary

• mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化

•

• 这里 operater_username 没有建立索引，所以创建了一张临时表

distinct

• 一旦mysql找到了与行相联合匹配的行，就不再搜索了

  强制索引概念和使用网址，待整理

  https://www.jb51.net/article/49807.htm