SQL简介

一、SQL简介

1. DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
2. DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。
3. DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。
4. DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。

1.1 DBMS前20

关系型数据库 (SQL)

关系模型基础上的数据库，SQL 就是关系型数据库的查询语言。

非关系型数据库(NoSQL)

非关系型数据库，包括了榜单上的键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。

1.2 SQL如何执行

SQL在ORACLE中执行

语法检查: 检查SQL拼写是否正确
语义检查: 检查SQL中的访问对象是否存在
权限检查: 用户是否具备访问该数据的权限
共享池检查:共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。
Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析
优化器: 创建解析树，生成执行计划
执行器: 执行器中执行语句

SQL在MYSQL中如何执行

1. 连接层：客户端和服务器端建立连接，客户端发送SQL至服务器端
2. SQL层：对 SQL 语句进行查询处理
3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。

Mysql SQL层的结构

Mysql储存引擎

1. InnoDB： 它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等
2. MyISAM：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少
3. Memory： 使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎
4. NDB：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。
5. Archive：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库

1.3 索引的原理

索引其实就是一种数据结构。
那么如何评价索引数据结构的好坏？
数据库服务器有两种存储介质，分别为硬盘和内存。内存属于临时存储，容量有限，而且当发生意外时（比如断电或者发生故障重启）会造成数据丢失；硬盘相当于永久存储介质，这也是为什么我们需要把数据保存到硬盘上。
当我们在硬盘上进行查询时，也就产生了硬盘的 I/O 操作。IO操作很耗时，当磁盘IO次数越多，消耗的时间就越大，所以我们的数据结构就是为了减少磁盘的IO操作。

1.4 索引的数据结构

1. 二叉树：
   使用二分查找法，时间复杂度O(log2n)，存在特殊情况，当二叉树的深度特别大，相当于链表的形式，时间复杂度就到了O(n)，因此不满足索引。
2. 平衡二叉树：（平衡二叉搜索树、红黑树、数堆、伸展树）
   平衡二叉树的时间复杂度O(log2n)， 当n比较大的情况下，磁盘IO的次数还是很多，树不过密集，在储存有限数据的情况下，树的深度就比较大。
3. B树：
   B树作为平衡的多路搜索树，每个节点最多可以包括M个子节点，M称为B树的阶。
4. B+树：
   孩子数=关键字数， B树 孩子数 = 关键字数+1
   非叶子节点的关键数同时也会存在子节点中，并且是子节点中所有关键字最大或最小
   非叶子节点仅用于索引，不保存数据记录，与记录有关的信息全部记录在叶子节点中。 B树非叶子节点保存索引也保存数据记录
   所有关键字

1.5 什么时候不需要创建索引

1. WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引
2. 如果表记录太少，比如少于1000
3. 字段中如果有大量重复数据
4. 频繁更新的字段不一定要创建索引

1.6 什么情况下索引失效

1. 索引进行了表达式计算
2. 对索引使用函数
3. 在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引
4. 使用LIKE进行模糊查询，最左边不能是% (例如: '%ab')
5. 索引列与 NULL 或者 NOT NULL 进行判断
6. 在使用联合索引的时候要注意最左原则

1.7 数据库查询优化

1. 观察服务器的状态是否存在周期性波动，缓存|缓存失效策略
2. 非周期性波动、缓存无法解决问题，分析查询延迟和卡顿的原因？开启慢查询，设置慢查询阈值(long_query_time)，通过分析工具对慢查询日志进行分析。
3. 查询到慢查询SQL，通过explaln查询SQL语句的执行计划，或使用show profile查看SQL中的每一个步骤的时间成本，了解是SQL的执行时间长还是等待时间长，等待时间长的话返回第二步。
4. 查询SQL执行时间长，表设计问题？索引优化问题？查询关联的数据表过多问题？
5. 数据库本身查询瓶颈？(例如：mysql单表千万级别的瓶颈) -> 增加服务器，读写分离，分表分库，垂直拆分|水平拆分。

# 查询慢查询是否开启(ON:开启 OFF:关闭)
show variables like '%slow_query_log';
# 把慢查询日志打开
set global slow_query_log = 'ON'
# 查询日志是否开启，以及慢查询日志文件的位置
show variables like '%slow_query_log%';
# 查询慢查询的时间阈值设置
show variables like '%long_query_time%';
# 设置慢查询阈值
set global long_query_time = 3;

通过mysqldumpslow工具查询慢SQL perl需要下载安装：Strawberry Perl for Windows

• -s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。
• -t：返回前 N 条数据 。
• -g：后面可以是正则表达式，对大小写不敏感。

perl mysqldumpslow.pl -s t -t 2 "c:\mysql_log\mysql-slow.log"

1.8 Mysql缓存