数据定义语言DDL（Data Definition 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这些命令对数据库安全性、完整性等操作，可以简单的理解为权限控制等。

超键、候选键、主键、外键分别是什么？

超键

在关系中能唯一标识元组的属性或属性集称为这个关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键

候选键是最小超键，即没有冗余元素的超键

主键

数据库表中对储存数据对象予以唯一和完整标识的属性或属性的组合。一个数据列只能有一个主键，且主键不能为空值（Null）。

骚戴理解：简单说，能够唯一标识元组的属性或属性集都是超键，可能会有多个超键，都可以唯一标识元组，然后这里面根据需求找一个作为主键，那么这里面其他的超键没被选中作主键的就都是候选键，也就是主键只有一个，候选键有多个

外键

在一个表中存在的另一个表的主键称此表的外键。

SQL 约束有哪几种？

SQL约束（Constraints）主要用于规定表中的数据规则，如果存在违反约束的数据行为，行为被约束终止。约束可以在创建表时规定（通过CREATE TABLE语句），或者在表创建之后规定（通过ALTER TABLE语句）。

在SQL中，有6种约束

NOT NULL

指示某列不能存储NULL值

UNIQUE

保证某列的每行必须具有唯一的值，可以空，但只能有一个

PRIMARY KEY

唯一并且非空，NOT NULL和UNIQUE的结合

FOREIGN KRY

保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK

保证列中的值符合指定的条件

DEFAULT

规定没有给列赋值时的默认值。

在默认的情况下，表的列接受 NULL 值。但是，NOT NULL 约束强制列不接受 NULL 值，并且强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

其次，UNIQUE 约束唯一标识数据库表中的每条记录。它和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证，并且PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束，但是我们要注意的是，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

#一共有五种约束:这里以学生表stuinfo为例
#1、添加主键约束
alter table stuinfo add costraint pk_stuno primary key (stuno)
#2、唯一约束
alter table stuinfo add constraint uq_stuid unique (stuid)
#3、添加默认约束
alter table stuinfo add constraint df_address default ('地址不详') for address
#4、添加检查约束
alter table stuinfo add constraint ck_age check (age between 15 and 40) 
#这时年龄在15到40之间
#5、添加外键约束
alter table stumarks add constraint fk_stuno foreign key (stuno) references stuinfo (stuno)
#这是一个成绩表(stumarks)引用学生信息表中的列-学号

五种关联查询

有5种关联查询

交叉连接（CROSS JOIN）;
内连接（INNER JOIN）;
外连接（LEFT JOIN/RIGHT JOIN）;
联合查询（UNION 与 UNION ALL）；
嵌套查询

交叉连接（CROSS JOIN）

SELECT * FROM A, B(, C) 或者

SELECT * FROM A CROSS JOIN B (CROSS JOIN C);

注：没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用。

内连接（INNER JOIN）

INNER JOIN 是 SQL 中最重要、最常用的表连接形式，只有当连接的两个或者多个表中都存在满足条件的记录时，才返回行。

SQL INNER JOIN 子句将 table1 和 table2 中的每一条记录进行比较，以找到满足条件的所有记录，然后将每一对满足条件的记录的字段值，合并为一条新的结果行。

INNER JOIN 是默认的表连接方式。当不加任何修饰性的关键字，只写 JOIN 时，默认就是 INNER JOIN 连接。

内连接分类

等值连接：ON A.id = B.id；
不等值连接：ON A.id > B.id;

等值连接演示

现在有如下所示的两个表，分别是客户表和订单表。

现在，让我们使用 INNER JOIN 连接这两个表，如下所示：

不等值连接和等值连接差不多

外连接

外链接又分为左外链接（LEFT JOIN）和右外链接（RIGHT JOIN）

1)左外连接

LEFT JOIN 和 RIGHT JOIN 是相对的，LEFT JOIN 将返回左表（table1）中的所有记录，即使右表（table2）中没有匹配的记录也是如此。当右表中没有匹配的记录时，LEFT JOIN 仍然返回一行，只是该行的左表字段有值，而右表字段以 NULL 填充。

LEFT JOIN 以左表为主，即左表中的所有记录都会被返回，具体分为以下三种情况：

如果 table1 中的某条记录在 table2 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。

如果 table1 中的某条记录在 table2 中有 N 条记录可以匹配，那么在返回结果中也会生成 N 个新的行，这些行所包含的 table1 的字段值是重复的。

如果 table1 中的某条记录在 table2 中没有匹配的记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 table2 的字段值都是 NULL。

简单说：LEFT JOIN以左表为主，先查询出左表，按照 ON 后的关联条件匹配右表，没有匹配到的用 NULL 填充

其实就是如果左表的id在右表中有，那就把右边的对应的id整合到左表中，如果没有，那就设置为null

2)右外连接

RIGHT JOIN 和 LEFT JOIN 是相对的，RIGHT JOIN 将返回右表（table2）中的所有记录，即使左表（table1）中没有匹配的记录也是如此。当左表中没有匹配的记录时，RIGHT JOIN 仍然返回一行，只是该行的右表字段有值，而左表字段以 NULL 填充。、

RIGHT JOIN 以右表为主，即右表中的所有记录都会被返回，具体分为以下三种情况：

如果 table2 中的某条记录在 table1 中刚好只有一条记录可以匹配，那么在返回的结果中会生成一个新的行。

如果 table2 中的某条记录在 table1 中有 N 条记录可以匹配，那么在返回的结果中也会生成 N 个新的行，这些行所包含的 table2 的字段值是重复的。

如果 table2 中的某条记录在 table1 中没有匹配记录，那么在返回结果中仍然会生成一个新的行，只是该行所包含的 table1 的字段值都是 NULL

简单说：RIGHT JOIN以右表为主，先查询出右表，按照 ON 后的关联条件匹配左表，没有匹配到的用 NULL 填充

什么时候用内连接什么时候用外连接？

需要查找两张表同时存在的数据（两个表匹配的记录都选取出来），使用内连接
需要查找两张表中一张表存在数据，另一张表不存在数据的时候使用左外连接或右外连接，是左还是右取决于那个表是放在左边还是右边。

解析

内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。比如，我们想知道每个学生的选课情况，包括已经选课的学生（这部分学生的学号在学生表中有，在选课表中也有，是满足连接条件的），也包括没有选课的学生（这部分学生的学号在学生表中有，但在选课表中没有，不满足连接条件），这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式

3)全外连接

全外连接FULL JOIN 将返回左表（table1）和右表（table1）中的所有记录，相当于 LEFT JOIN 和 RIGHT JOIN 的叠加。

FULL JOIN 先执行 LEFT JOIN 遍历左表，再执行 RIGHT JOIN 遍历右表，最后将 RIGHT JOIN 的结果直接追加到 LEFT JOIN 后面。注意，FULL JOIN 会返回重复的行，它们会被保留，不会被删除。

其实就是把左外连接和右外连接的结果合在一起就是全外连接

联合查询（UNION 与 UNION ALL）

SQL UNION 子句/运算符用于合并两个或者多个 SELECT 语句的结果集。

默认地，UNION 运算符会过滤掉两个结果集中重复的记录，只保留其中一条，也就是对两个结果集进行并集操作；此外，UNION 还会按照默认规则对结果集进行排序。

如果您希望保留原始结果，请使用 UNION ALL。UNION ALL 只是对结果集进行简单粗暴的合并，不会过滤重复的记录，也不会进行排序。

UNION 运算符使用注意事项

每个 SELECT 语句都必须拥有相同数量的字段， SELECT 语句的字段名不需要相同，SQL 会将第一个 SELECT 语句的字段名作为结果集的字段名。

就是把多个结果集集中在一起，UNION 前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并（如果前者 select 和后者 select 有重复，则会合并，且以前面的为基准）；

如果使用 UNION ALL，不会合并重复的记录行；

在效率方面，UNION 高于 UNION ALL。

每个 SELECT 语句都必须拥有相同数量的字段；

嵌套查询【不推荐使用，效率不可把控】

其中，match赛程表中的 hostTeamID 与 guestTeamID 都和 team表中的 teamID关联，查询 2006-6-1 到 2006-7-1 之间举行的所有比赛，并且用以下形式列出：拜仁 2：0 不莱梅 2006-6-21。

解题思路：

先找出我们的结果需求：主队名称比赛结果客队名称比赛日期

分析表与表之间的关联关系，match表中的主队ID（hostTeamID）和客队ID（guestTeamID）都和team表中的teamID关联，通过关联查询可以获得主队名称和客队名称

根据match表中的matchTime字段查询 2006-6-1到2006-7-1之间举行的比赛即可

SELECT
  t1.teamName,
  m.matchResult,
  t2.teamName,
  m.matchTime 
FROM
  `match` AS m 
  LEFT JOIN team AS t1 ON m.hostTeamID = t1.teamID
  LEFT JOIN team AS t2 ON m.guestTeamID = t2.teamID 
WHERE
  m.matchTime BETWEEN '2006-6-1' 
  AND '2006-7-1';
SELECT
  T1.teamName,
  M.matchResult,
  T2.teamName,
  M.matchTime 
FROM
  team T1,
  team T2,
  `match` M 
WHERE
  T1.teamId = M.hostTeamID 
  AND T2.teamId = M.guestTeamID 
  AND M.matchTime >= "2006-06-01“ and M.matchTime <=”2006-07-01";

什么是子查询

子查询，又叫内部查询。当一个查询的执行结果是另一个查询的查询条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。

子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT…INTO 语句、INSERT…INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。

子查询分类

根据子查询返回的结果进行分类

标量子查询：返回单一值的标量，如一个数字或一个字符串

列子查询：返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回

行子查询：返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

表子查询：返回的结果集是 N 行 N 列，该子查询的结果通常是一个表的数据

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询。

如果子查询返回一个标量值（就一个值），那么外部查询就可以使用：=、>、<、>=、<=和<>符号进行比较判断；如果子查询返回的不是一个标量值，而外部查询使用了比较符和子查询的结果进行了比较，那么就会抛出异常。

1、标量子查询

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧

示例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

2、MySQL 列子查询

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。

可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)

NOT IN 是 <> ALL 的别名，二者相同。

特殊情况：

如果 table2 为空表，则 ALL 后的结果为 TRUE；

如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 ALL 后的结果为 UNKNOWN 。

注意：对于 table2 空表的情况，下面的语句均返回 NULL：

SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)
SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)

3、MySQL 行子查询

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
//注：(1,2) 等同于 row(1,2)
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4、MySQL 表子查询

指子查询返回的结果集是 N 行 N 列的一个表数据。

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

mysql中 in 和 exists 区别？哪个时候用哪个？

mysql中 in 和 exists 区别？

（1）exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

（2）in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

（3）如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

in关键字

in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select * from user where user_id in (1, 2, 3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
select * from A where id in (select id from B)
#等价于
for select id from B：先执行；
子查询 for select id from A where A.id = B.id：再执行外面的查询；

执行过程：in是先查询内表【select id from B】，再把内表结果与外表【select * from A where id in …】匹配

小总结：in适合外部表数据大于子查询的表数据的业务场景

exists关键字

select ... from table where exists (select id from B where B.id = A.id)；
#等价于
for select id from A：先执行外层的查询；
for select id from B where B.id = A.id：再执行子查询；

可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。

如下：

执行过程：exists是对外表【select * from A where exists …】做loop循环，每次loop循环再对内表（子查询）【select 1 from B where B.id = A.id】进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（所以尽量用小表），故内表大的使用exists，可加快效率。

select * from A where exists (select 1 from B where B.id = A.id)
1)、表A中100000条数据，表B中100条数据，那么查询数据库的次数 = 1(表A查一次) + 100000(子查询：查询表B的次数)  ，一共 100001次；
2)、表A中 100条数据，表B中100000条，查询数据库次数 = 1(表A查一次) + 100(子查询次数)，一共 101次；

可见，只有当子查询的表数量远远大于外部表数据的时候，用exist查询效率好于in；

哪个时候用哪个？

当A表的数据集大于B表的数据集时，用in 优于 exists；

当A表的数据集小于B表的数据集，用 exists 优于 in【注意： A与B表的id 字段应该建立索引】；

如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in；

varchar(50)中50的涵义

最多存放50个字符（50是指字符数）

int(20)中20的涵义？mysql为什么这么设计int(20)？

int(20)中20的涵义？

20表示最大显示宽度为20，不是存储数据的大小，存储数据的大小仍是占4字节存储，存储范围不变；不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示，比如它是记录行数的id,插入10份数据，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

mysql为什么这么设计int(20)？

对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

varchar与char的区别

char存储的是固定字符串，不足补空格，varchar存储的是可变字符串

char的存取速度比varchar要快得多，因为其长度固定，方便程序的存储与查找；

varchar占据的空间比char更少，char表示存储定长的字符串，不足就用空格补齐，占用更多的存储空间，varchar存多少就占多少

char的存储方式是对英文字符(ASCII)占用1个字节，对一个汉字占用两个字节；varchar的存储方式是对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据

varchar空格也按一个字符存储，char的空格表示占位不算一个字符

FLOAT和DOUBLE的区别是什么？

在内存中占有的字节数不同, 单精度FLOAT内存占4个字节, 双精度DOUBLE内存占8个字节
有效数字位数不同(尾数) 单精度FLOAT小数点后有效位数7位, 双精度DOUBLE小数点后有效位数16位
在程序中处理速度不同,一般来说，CPU处理单精度浮点数的速度比处理双精度浮点数快

drop、delete与truncate的区别

执行速度，一般来说： drop> truncate > delete

delete是DML语句,操作完以后如果没有不想提交事务还可以回滚，不会自动提交。drop/truncate都是DDL语句,操作完马上生效，不能回滚，执行后会自动提交

truncate 和delete只删除数据（truncate 会释放空间，delete不会释放空间）， drop则删除整个表和数据（结构和数据）

truncate TABLE 不能用于参与有索引视图的表。

三者的介绍

drop：删除内容和定义，释放空间。（表结构和数据一同删除）

【drop语句将删除表的结构，被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。】

truncate：删除内容，释放空间，但不删除定义。（表结构还在，数据删除）

【truncate table 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。】

delete：删除内容，不删除定义，也不释放空间。

drop table user;
truncate table user;
delete from user;
注：user 为数据库表名

UNION与UNION ALL的区别？

union去重并排序，union all直接返回合并的结果，不去重也不排序；
union all比union性能好；

SQL优化

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划，对于查询语句，最重要的优化方式就是使用索引。而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

例如使用explain 命令可以用来分析select 语句的运行效果。

explain select * from mytest;

也可以直接在Navicat上直接解释对应的sql语句

explain分析结果说明

select_type：每个子查询的查询类型

table：查询的数据表

type：访问类型（非常重要，可以看出有没有走索引），有以下几个值：

possible_keys：可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

key_length：索引长度

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 rows 返回估算的结果集数目，并不是一个准确的值。

SQL性能优化的目标？

至少要达到 range 级别。

SQL的生命周期？

SQL由客户端发出后，经过连接和验证，发送到服务器，由服务器派发给线程处理

SQL的生命周期？

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

一条SQL中最重要的两个阶段是SQL解析和SQL优化（MySQL服务器自己对SQL做的优化，可能不是开发者所希望的）

对sql进行优化处理

【例如select语句的优化具体是在JOIN::optimise函数中完成。(MySQL针对select的处理是转换成JOIN操作处理的)】

select A.id, B.score from student A left join subject B on A.id=B.id where A.age > 10 and B.score > 60;

优化过程会将join的key也转换为一个where条件，经过处理后，上面的sql就有了3个where条件：

A.age > 10；

A.id = B.id；

B.score > 60；

sql执行

例如select语句的执行具体是在JOIN::exec（MySQL是将任何select都转换为JOIN来处理的）。即JOIN::exec函数，首先会调用send_fields函数，将最终结果的信息返回，<br>然后调用do_select。在do_select函数中，通过调用sub_select函数来具体实现join功能。

大表数据（表中数据很多）查询怎么优化

1. 索引优化

通过建立合理高效的索引,提高查询的速度..

2. SQL优化

组织优化SQL语句,使查询效率达到最优,在很多情况下要考虑索引的作用，务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

3. 水平拆表

如果表中的数据呈现出某一类特性,比如呈现时间特性,那么可以根据时间段将表拆分成多个。

比如按年划分、按季度划分、按月划分等等，查询时按时间段进行拆分查询，再把查询结果进行合并；

比如按地区将表拆分，不同地区的数据放在不同的表里面，然后对查询进行分拆，对查询结果进行合并。

4. 垂直拆表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。对于字段较多的表，如果有些字段的使用频率很低，则垂直分割该表，将原来的一个表分解为两个表。由于数据库每次查询都是以块为单位，而每块的容量是有限的，通常是十几K或几十K，将表按字段拆分后，单次IO所能检索到的行数通常会提高很多，查询效率就能提高上去。

垂直分割遵循以下原则：

把不常用的字段单独放在同一个表中
把大字段独立放入一个表中
把经常使用的字段放在一同一个表中

5. 建立中间表，以空间换时间

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

6. 用内存缓存数据，以空间换时间

将常用而且不常修改的数据加载到内存中，直接从内存查询则可。

可以使用热门的缓存技术，如Memcache、Redis、Ehcache等。

7. 使用其他辅助技术

Solr:一种基于Lucene的JAVA搜索引擎技术

8.增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

超大分页怎么处理？

1、使用覆盖索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

把分页的SQL语句的查询字段使用覆盖索引来提高性能

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

因为实际开发中，用SELECT查询一两列操作是非常少的，因此覆盖索引的适用范围就比较有限。

select * from t5 order by text limit 1000000, 10;
使用了覆盖索引后
select id, `text` from t5 order by text limit 1000000, 10;（text是加了索引的）

2、子查询优化

分页的SQL语句改写成子查询的方法获得性能上的提升

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性：

这种写法，要求主键ID必须是连续的
where后面加了其他条件就会导致效率降低

3、延迟关联

可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

在采用JOIN改写后，上面的两个局限性都已经解除了，而且SQL的执行效率也没有损失。

4、记录上次查询结束的位置

和上面使用的方法都不同，记录上次结束位置优化思路是使用某种变量记录上一次数据的位置，下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。

select * from t5 where id>=1000000 limit 10;

mysql 分页

什么是分页

一般在客户端实现分页功能的时候，要显示当前页的数据、当前所在页数、临近页面的按钮以及总页数等等。这些数据随着翻页的进行能够动态的变化，为了实现这样的效果，一般会采取两种办法：真分页和假分页。这样的划分方式是从与数据库的交互方式出发的，是每次翻页时都进行查询还是一次性查出所有的数据。

真分页

真分页指的是每次在进行翻页时都只查询出当前页面的数据，特点就是与数据库的交互次数较多，但是每次查询的数据量较少，数据也不需要一直保存在内存中。但是数据库的负担会很重，尤其是用户量大的情况下，适用于数据量比较大的场景，数据不适合全量查出的情况。

假分页

假分页指的是对于要显示的数据一次性全部查出，一直存在在服务端或客户端，在前端进行分页或由服务端控制分页。将根据当前所在页来计算应该显示的数据所在下标，用循环取出目标数据。只有当会话断开或页面关闭，相应的资源才会被释放。

缓存层

真分页和假分页都要和数据库进行交互，对于真分页来说不需要担心数据同步的问题，因为每次都是查询出最新的，但是数据库的负担会很重，尤其是用户量大的情况下。假分页可以在一定程度上减轻数据库的压力，但是数据不能及时得到同步，除非重新请求或页面刷新。一般在企业中会有缓存层的存在，既能有效降低数据库的压力，又能及时的进行数据同步。在对数据库中的数据进行修改后，要将变更后的数据及时同步到缓存层，在进行数据查询时从缓存层获取。

LIMIT用法

LIMIT出现在查询语句的最后，可以使用一个参数或两个参数来限制取出的数据。其中第一个参数代表偏移量：offset（可选参数），第二个参数代表取出的数据条数：rows。

单参数用法

当指定一个参数时，默认省略了偏移量，即偏移量为0，从第一行数据开始取，一共取rows条。

/* 查询前5条数据 */

SELECT * FROM Student LIMIT 5;

双参数用法

当指定两个参数时，需要注意偏移量的取值是从0开始的，此时可以有两种写法：

/* 查询第1-10条数据 */
SELECT * FROM Student LIMIT 0,10;
/* 查询第11-20条数据 */
SELECT * FROM Student LIMIT 10 OFFSET 10;

分页公式

在进行分页之前，我们需要先根据数据总量来得出总页数，这需要用到COUNT函数和向上取整函数CEIL，SQL如下：

/* 获得数据总条数 */
SELECT COUNT(*) FROM Student;
/* 假设每页显示10条，则直接进行除法运算，然后向上取整 */
SELECT CEIL(COUNT(*) / 10) AS pageTotal FROM Student;

核心信息

当前页：pageNumber
每页数据量：pageSize

在实际操作中，我们能够得到的信息有当前所在页以及每页的数据量，同时要注意一下是否超出了最大页数。以每页10条为例，则前三页的数据应为：

第1页：第1~10条，SQL写法：LIMIT 0,10

第2页：第11~20条，SQL写法：LIMIT 10,10

第3页：第21~30条，SQL写法：LIMIT 20,10

据此我们可以总结出，LIMIT所需要的两个参数计算公式如下：

offset：(pageNumber - 1) * pageSize
rows：pageSize

MySQL数据库面试题4

数据定义语言DDL（Data Definition Language）

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

数据操纵语言DML（Data Manipulation Language）

数据控制功能DCL（Data Control Language）

超键、候选键、主键、外键分别是什么？

超键

候选键

主键

外键

SQL 约束有哪几种？

五种关联查询

交叉连接（CROSS JOIN）

内连接（INNER JOIN）

外连接

联合查询（UNION 与 UNION ALL）

嵌套查询【不推荐使用，效率不可把控】

什么是子查询

子查询分类

1、 标量子查询

2、MySQL 列子查询

3、MySQL 行子查询

4、MySQL 表子查询

mysql中 in 和 exists 区别？哪个时候用哪个？

mysql中 in 和 exists 区别？

in关键字

exists关键字

哪个时候用哪个？

varchar(50)中50的涵义

int(20)中20的涵义？mysql为什么这么设计int(20)？

int(20)中20的涵义？

mysql为什么这么设计int(20)？

varchar与char的区别

FLOAT和DOUBLE的区别是什么？

drop、delete与truncate的区别

三者的介绍

UNION与UNION ALL的区别？

SQL优化

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

SQL的生命周期？

SQL的生命周期？

大表数据（表中数据很多）查询怎么优化

1. 索引优化

2. SQL优化

3. 水平拆表

4. 垂直拆表

5. 建立中间表，以空间换时间

6. 用内存缓存数据，以空间换时间

7. 使用其他辅助技术

8.增加冗余字段

超大分页怎么处理？

1、使用覆盖索引

2、子查询优化

3、延迟关联

4、记录上次查询结束的位置

mysql 分页

什么是分页

真分页

假分页

缓存层

LIMIT用法

分页公式

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

1、标量子查询