SQL语句及优化

# SQL语句分为哪几类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索

数据查询语言DQL（Data Query Language）SELECT

即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种被划分到DQL中。

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

# 约束类型（Constraint）

主键（Primary Key）约束，唯一约束（Unique），检查约束，非空约束，外键（Foreign Key）约束。

# SQL约束

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

# 超键、候选键、主键、外键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

# 关联查询

用下面这个表作为例子

# 交叉连接（CROSS JOIN）

也叫笛卡尔积，说的通俗点就是R表中每个元素分别和S表中的每个元素进行连接，所以总记录数有3*3=9条

# 内连接（INNER JOIN）

其实就是返回两个表中相同的部分（两个集合的交集），因为S和R是通过C产生关系的，所以我们这里就是找到两个中相同的部分（这里就是C1和C2了，R和S中都有）

# 外连接（LEFT JOIN/RIGHT JOIN）

包括左连接和右连接

# 左连接

左连接其实就是左边保持完整，然后右边根据内连接的规则把其余的部分补齐

# 右连接

右连接和左连接相反

# 联合查询（UNION与UNION ALL）

联合查询和子查询是相反的操作，就是将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)

下面这三个图就很好的解释了，什么是联合查询。（UNION ALL其实就是保留所有，UNION会进行去重操作）

# UNION与UNION ALL的区别

如果使用UNION ALL，不会合并重复的记录行
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

# 全连接（FULL JOIN）

MySQL不支持，Oracle支持

其实就是左右连接结合，把所有的数据全部连接到一起

# 子查询

有下面两种情况

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果
嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

# in和exists区别

in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

# Drop/Delete/Truncate的区别

Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器（包含一个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1；
Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除；不能回滚，不会触发触发器；

# SQL生命周期

应用服务器与数据库服务器建立一个连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

# 大表查询如何优化

优化shema、sql语句+索引；
加缓存，memcached, redis；
主从复制，读写分离；
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
水平切分，针对数据量大的表（比较麻烦）

# MySQL分页

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. 
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

1
2
3

# 超大分页处理

数据库层面可以优化查询语句：select * from table where age > 20 limit 1000000,10可以改成select * from table where id in (select id from table where age > 20 limit 1000000,10)（因为索引覆盖），如果ID是连续的，我们可以select * from table where id > 1000000 limit 10
从需求层面，我们可以减少这种请求，或者通过缓存来提前缓存数据

# 慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

# 主键使用自增ID还是UUID

推荐使用自增ID，不要使用UUID。因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。总之，在数据量大一些的情况下，用自增主键性能会好一些。

如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

# 字段为什么要求定义为not null

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

# 数据库优化的几种方式

SQL语句优化
索引优化
表结构优化（三大范式、合适的数据类型、水平切分和垂直切分）
系统配置优化（添加TCP队列数，修改缓冲池大小和个数）
硬件优化（磁盘、CPU、内存）

# SQL缓存

MySQL查询缓存是MySQL中比较独特的一个缓存区域，用来缓存特定Query的整个结果集信息，且共享给所有客户端。为了提高完全相同的Query语句的响应速度，MySQL Server会对查询语句进行Hash计算后，把得到的hash值与Query查询的结果集对应存放在Query Cache中。当MySQL Server打开Query Cache之后，MySQL Server会对接收到的每一个SELECT 语句通过特定的Hash算法计算该Query的Hash值，然后通过该hashi值到Query Cache中去匹配。

玩转MySQL之四]MySQL缓存机制 - 知乎 (zhihu.com) (opens new window)