每日问答7

1 描述主键、外键、候选主键、超键是什么

超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键

候选键(candidate key):不含有多余属性的超键称为候选键

主键(primary key):用户选作元组标识的一个候选键程序主键

外键(foreign key)如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键,则该属性集是关系模式R1的外键。

2 数据库设计的三大范式

第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；

第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；

第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。

3 drop,delete与truncate的区别

drop：drop table 表名

​ 删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。

truncate (清空表中的数据)：truncate table 表名

​ 删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。

​ truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。

delete：delete from 表名 （where 列名 = 值）

​ 与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。

注意

1.delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作

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

3.delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

4.truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发trigger。

5.truncate语句执行以后，id标识列还是按顺序排列，保持连续；而delete语句执行后，ID标识列不连续

4 SQL UNION 和 UNION ALL

UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。

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

5 exists、in、any、all区别

exists和in

exists和in都适用于连接子查询的。什么叫子查询呢？就是我们在where语句中嵌套一个select语句。他们之间的区别在于：

1. 使用in的时候，会把子句中的查询作为结果缓存下来，然后对主查询中的每个记录进行查询。

2. 使用exists的时候，不在对子查询的结果进行缓存，子查询的返回的结果并不重要。使用exists的时候，我们使先对主查询进行查询，然后根据子查询的结果是否为真来决定是否返回。在使用exists的时候，如果子查询返回结果集的时候为真，主查询返回结果。在使用not exists的时候，如果子查询不返回结果集，那么主查询为真，返回结果。

all 和 any

其实在sql中，还有一个是some，那么这三个的用法有什么差距呢？

some：表示满足一个条件即可。就像英语一样，some一半适用于 = 的情况

ALL：只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

any：只要满足内层查询语句返回的结果中的任意一个，就可以通过该条件来执行外层查询语句。

几个常见的等价：

in 和 =any 即满足一个即可

not in 和 <> all 即不等于所有，每个都不相等。但是<>any 只要有一个不相等即为true

6 sql语句的执行顺序

1 from 2 join 3 on 4 where 5 group by 6 avg,sum... 7 having 8 select 9 distinct 10 order by 11 limit

7 count(*)和count(1)和count(id)区别

count(*) 和count(1) 都是统计行数，而count(col) 是统计col列非null的行数

执行效率方面：

count(*)>count(1)>count(id)

因为mysql本身对于count(*)做了特别的优化处理,count(1)在每行添加一个新列值为1,效率略低,count(id)最低，因为在扫描数据表的同时，还要排除null

 

8 SQL语句优化

1 对查询进行优化,应避免全表扫描,首先考虑在where及order by涉及的列上建立索引

2 避免在where子句中使用!=和<>,否则搜索引擎将放弃使用索引而进行全表扫描

3 避免在where子句中对字段进行null值判断,否则搜索引擎将放弃使用索引而进行全表扫描

4 避免在where子句中使用or来连接条件,否则搜索引擎将放弃使用索引而进行全表扫描

5 避免在where子句中使用进行表达式操作,否则搜索引擎将放弃索引而进行全盘扫描

6 避免在where子句中对字段进行函数操作,否则搜索引擎将放弃索引而进行全盘扫描

7 下面的查询也将导致全表扫描：select id from t where name like '%abc%'

8 不要使用select * from table,使用具体字段代替*

9 mysql explain是什么？有什么作用？

explain mysql是用于解释SQL语句的语法

​ 模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。---------------------------------------------------------------

EXPLAIN命令是查看查询优化器如何决定执行查询的主要方法,

使用EXPLAIN,只需要在查询中的SELECT关键字之前增加EXPLAIN这个词即可,MYSQL会在查询上设置一个标记,当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,而不是执行它,它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序,从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。

作用：

• 分析出表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

  Explain + SQL语句即可,如下:   
      explain select * from tbl_dept;

10 什么是索引？索引的种类？索引的优缺点?

索引是对数据库表中的一列或多列的值进行排序的结构

索引的种类: 聚簇索引和非聚簇索引

聚簇索引: 是按照数据存放的物理位置为顺序

聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快

索引的优缺点

优点: 通过创建唯一性索引,可以保证数据库表中每一行的数据的唯一性

​ 可以大大加快数据检索的速度,这也是创建索引的最主要原因

​ 可以加快表与表之间的连接

​ 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间

缺点: 创建和维护索引要耗费时间,这种时间随着数据量的增加而增加

​ 索引需要占物理空间,除了数据库表占空间之外,每一个索引还要占一定的物理空间,如果需要建立聚簇索引,那么需要的空间就会越大

​ 当对表中的数据进行增加,删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度

11 设计索引的原则？

1 为经常需要排序、分组和联合操作的字段建立索引

2 尽量使用前缀来索引

3 使用唯一索引,索引的列的基数越大,效果越好

4 最左原则;不在频繁改动的列上设置索引;

5 尽量在数值型列上设置索引如果在字符型列设置索引要指定一个前缀长度;

 

12 什么情况下索引会失效？

​ 1、使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like ‘%文’–索引不起作用）

2、使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效

3、使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。

4、尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。

6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：　　select id from t where num/2=100　　应改为:　　select id from t where num=100*2

7、尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。

8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

9、并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引，如一表中有字段　　sex，male,female几乎个一半，那么即使在sex上建立了索引也对查询效率起不了作用。

10、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，　　因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，　　若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

11、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。　　这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

12、mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。　　 因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。13、order by 索引 ，不起作用的问题（除了主键索引之外）：　

　1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；

​ 2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！

13 什么是存储过程？有什么优缺点？

存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库

优点

存储过程是一个预编译的代码块，执行效率比较高

存储过程在服务器端运行,减少客户端的压力

允许模块化程序设计,只需要创建一次过程,以后在程序中就可以调用该过程任意次,类似方法的复用

一个存储过程代替大量的sql语句,可以降低网络通信质量,提高通信的效率

可以在一定程度上保证数据安全

缺点

调试麻烦

可移植性不灵活(因为存储过程过分依赖具体的数据库)

14 数据库优化方案？

a: 表的设计合理化(符合3NF)

b: 优化SQL语句（索引）

c: 分表技术(水平分割、垂直分割)、分区技术

d: 读写[写: update/delete/add]分离

e: 存储过程 [模块化编程，可以提高速度]

f: 对mysql配置优化 [配置最大并发数, 调整缓存大小 ]

g: mysql服务器硬件升级

h: 定时的去清除不需要的数据,定时进行碎片整理