MySql部分（重要）

1.数据库的三范式是什么？

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式：任何非主属性不依赖于其它非主属性。

2.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

表类型如果是 MyISAM ，那 id 就是 8。

表类型如果是 InnoDB，那 id 就是 6。

3.如何获取当前数据库版本？

使用 select version() 获取当前 MySQL 数据库版本。

select version()

4.说一下 ACID 是什么？事务的特性

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

5.char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡

6.float 和 double 的区别是什么？

float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。

double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

7.mysql 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反

8.mysql 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的

9.怎么验证 mysql 的索引是否满足需求？

使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。

explain 语法：

explain select * from table where type=1

10.说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。

READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。

REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。

SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读：是指在一个事务内，多次读同一数据。

幻读：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了

11.说一下 mysql 常用的引擎？

InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率

MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选

12.说一下 mysql 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

13.说一下乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放

　　数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁

14.mysql 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息

使用 explain 命令查询 SQL 语句执行计划

开启慢查询日志，查看慢查询的 SQL

15.如何做 mysql 的性能优化？

为搜索字段创建索引

避免使用 select *，列出需要查询的字段

垂直分割分表

选择正确的存储引擎

16.Mysql怎么保证持久性的？

OK，是利用Innodb的redo log。

正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

怎么解决这个问题？

简单啊，事务提交前直接把数据写入磁盘就行啊。

这么做有什么问题？

只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。

毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下

redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。

redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

ps:不想具体去谈redo log具体长什么样，因为内容太多了。

问题四: Mysql怎么保证隔离性的？

OK,利用的是锁和MVCC机制。还是拿转账例子来说明，有一个账户表如下

表名t_balance

其中id是主键，user_id为账户名，balance为余额。还是以转账两次为例，如下图所示

至于MVCC,即多版本并发控制(Multi Version Concurrency Control),一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。

如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同，就不赘述了。

但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

17.Mysql怎么保证一致性的？

OK，这个问题分为两个层面来说。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

18.Mysql怎么保证原子性的？

OK，是利用Innodb的undo log。

undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

例如

(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据

(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作

(3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

ps:具体的undo log日志长啥样，这个可以写一篇文章了。而且写出来，看的人也不多，姑且先这么简单的理解吧。

19.MySQL、Redis、 MongoDB对比

MySQL

1.使用c和c++编写，并使用了多种编译器进行测试，保证源代码的可移植性2.支持多种操作系统3.为多种编程语言提供可API4.支持多线程，充分利用CPU资源优化的SQL查询算法，有效的提高查询速度5.提供多语言支持，常见的编码如: GB2312、 BIG5、 UTF8.提供TCP/IP、ODBC和JDBC等多种数据库连接途径提供用于管理、检查、优化数据库操作的管理工具7.大型的数据库。可以处理拥有上千万条记录的大型数据库8.支持多种存储引擎9. MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库10. MySQL使用标准的SQL数据语言形式11. Mysql是可以定制的，采用GPL协议，你可以修改源码来开发自己的MySQL系统12.在线DDL 更改功能13.复制全局事务标识14.复制无崩溃从机15.复制多线程从机

Redis
1. Redis支持数据的持久化，可以将内存中的数据保存在磁盘中，重启的时候可以再次加载进行使用。2. Redis不仅仅支持简单的key-va lue类型的数据，同时还提供list,set,在set,hash等数据结构的存储。3.Redis支持数据的备份，即mas ter-s lave模式的数据备份4.性能极高- Redis能读的速度是10000次/s,写的速度是81000次/s5.丰富的数据类型-Redis支持二进制案例的Strings, Lists, Hashes , Setes及OrderedSets数据类型操作。6.原子一Redis的所有操作都是原子性的，同时Redis还支持对几个操作全并后的原子性执行。7.丰富的特性- Redis还支持publish/subscribe,通知，key过期等等特性。

MongoDB

1.模式自由:可以把不同结构的文档存储在同-个数据库里2.面向集合的存储:适合存储JSON风格文件的形式3.完整的索引支持，对任何属性可索引4.复制和高可用性:支持服务器之间的数据复制，支持主-从模式及服务器之间的相互复制。复制的主要目的是提供冗余及自动故障转移5.自动分片:支持水平的数据库集群，可动态添加额外的机器6.丰富的查询:支持丰富的查询表达方式，查询指令使用JSON形式额标记，可轻易查询文档中的内嵌的对象及数组7.快速就地更新:查询优化器会分析查询表达式，并生成一个高效的查询计划8.高效的传统存储方式:支持二进制数据及大型对象

MongoDB适用于

①网站数据:适合实时的插入，更新与查询，并具备网站实时数据存储所需对的复制及高度伸缩性;②缓存:由于性能很高，也适合作为信息基础设施的缓存层，在系统重启之后，搭建的持久化缓存可以避免下层的数据源过载;③大尺寸、低价值的数据也是MongoDB的最佳选择，使用传统的关系数据库存储一些数据时可能会比较贵，再次之前很多程序员往往会选择传统的文件进行存储④高伸缩的场景，非常是个由数十或者数百台服务器组成的数据库⑤用于对象及j son数据的存储，MongoDB的bson数据格式非常适合文档格式化的存储及查询。

而mysql还是更加适用于##

①高度事务性的系统。例如银行或者会计系统，传统的关系型数据库目前还是更实用于需要大量原子性复杂事务的应用程序

②传统的商业智能应用，针对特定问题的BI数据库会对产生高度优化的查询方式，对于此类应用，数据仓库可能是更合适的选择

Red is应用场景:

1.用来做缓存-redis 的所有数据时放在内存中的2.可以在某些特定应用场景下替代传统数据库-比如社交类的应用3.在一些大型系统中，巧妙的实现一些特定的功能: session共享、购物车4. MongoDB不支持SQL语句

20.主键和唯一索引的区别？

在创建主键的同时会生成对应的唯一索引，主键在保证数据唯一性的同时不允许为空，而唯一可以有一个为空数据项，一个表中只能有一个主键，但是一个主键可以有多个字段，一个表中可以有多个唯一索引。

21.Preparedstatement和statement的区别

用Prepared statement进行开发。Prepared statement是预编译的，而statement不是，在每次执行sql语句的增删改时，如果是一条数据两者没差距，但如果数据量大于1，那么每次执行sql语句statement都要重新编译一次，而Prepared statement不用，Prepared statement的运行效率大于statement；从代码的可维护性和可读性来说，虽然用Prepared statement来代替statement会使代码多出几行，但这样的代码无论从可读性还是可维护性来说，都比直接使用statement的代码高很多档次；最重要的一点，从安全角度来说，使用Prepared statement可以大大提高程序的安全性，因为Prepared statement是用‘？’传参,可以防止sql注入，具有安全性，而statement用的是‘+’字符串拼接，安全性较低。

22.视图概述

视图可以视为“虚拟表”或“存储的查询”

创建视图所依据的表称为“基表”

视图的优点：

提供了另外一种级别的表安全性:隐藏了一些关键的字段

简化的用户的SQL命令

隔离基表结构的改变

23.存储过程概述

存储过程（Stored Procedure）

　　可以包含逻辑判断的sql语句集合。

　　是经过预编译，存在于数据库中。

　　通过调用指定存储过程的名字（可有参，可无参）来执行。

优点：

　　简化了复杂的业务逻辑，根据需要可重复使用

　　屏蔽了底层细节，不暴露表信息即可完成操作

　　降低网络的通信量，多条语句可以封装成一个存储过程来执行

　　设置访问权限来提高安全性

　　提高执行效率，因为它是预编译以及存储在数据库中

缺点：

　　可移植性差，相同的存储过程并不能跨多个数据库进行操作

　　大量使用存储过程后，首先会使服务器压力增大，而且维护难度逐渐增加

存储过程的语法：

--下面是在oracle数据库下最基本的语法

--仅创建一个名为testProcedure 的无参的存储过程

--IS也可以是AS

--如果已经存在名为 testProcedure 的存储过程，下面的语法会出现名称已被使用的错误

--解决办法：

--第一句可以写成 create or replace procedure testProcedure

--这样会替换原有的存储过程

--NULL表示任何可以正确执行的sql 语句，但至少一句

create procedure testProcedure

BEGIN

NULL

END;

存储过程的参数的分类:

OUT

INOUT

注意：

　　存储过程之间可相互调用

　　存储过程一般修改后，立即生效。

24.索引概述

1.索引的概念

索引就是为了提高数据的检索速度。

数据库的索引类似于书籍的索引。

在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。

在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库.

2、索引的优点

　　1.创建唯一性索引，保证数据库表中每一行数据的唯一性

　　2.大大加快数据的检索速度，这也是创建索引的最主要的原因

　　3.减少磁盘IO（向字典一样可以直接定位）

3、索引的缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

2.索引需要占用额外的物理空间

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

4、索引的分类

　　1.普通索引和唯一性索引

　　普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)

　　唯一性索引：保证在索引列中的全部数据是唯一的

　　 CREATE unique INDEX mycolumn_index ON mytable (myclumn)

2. 单个索引和复合索引

　　单个索引：对单个字段建立索引

　　复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，

　　最多16个字段

　　 CREATE INDEX name_index ON userInfo(firstname,lastname)

3.顺序索引，散列索引,位图索引

25.左连接,右连接，内连接，外连接的区别

内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。外连接分为三种:左外连接，右外连接，全外连接。其中左外连接：left join是以左表的记录为基础的,例如A可以看成左表,B可以看成右表,它的结果集是A表中的数据，再加上A表和B表匹配的数据。其中A表的记录将会全部表示出来,而右表B只会显示符合搜索条件的记录。B表记录不足的地方均为NULL。右外连接与左外连接正好相反。全连接则是左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充

26.MySQL中in 和exsit区别

exists()后面的子查询被称做相关子查询，他是不返回列表的值的.只是返回一个ture或false的结果(所以一般exists中的子查询里写成"select 1 " 当然也可以select任何东西)

其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果，如果是ture则输出,反之则不输出.再根据主查询中的每一行去子查询里去查询. in()后面的子查询是返回结果集的,换句话说执行次序和exists()不一样.子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

27.数据库查询缓慢的常见原因以及优化方法？

查询速度慢的原因很多，常见如下几种：

(1)没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)

(2)I/O吞吐量小，形成了瓶颈效应。

(3)没有创建计算列导致查询不优化。

(4)内存不足,网络速度慢

(5)查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）

(6)锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)

(7)返回了不必要的行和列(8)查询语句不好，没有优化。

可以通过如下方法来优化查询 :

（1）把数据、日志、索引放到不同的I/O设备上，增加读取速度(

2)纵向、横向分割表，减少表的尺寸

(3)升级硬件

(4)根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。

(5)DB Server 和APPLication Server 分离

(6)优化SQL语句：1、SELECT子句中避免使用 ‘ * ‘： 2、用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(ROLLBACK SEGMENTS ) 用来存放可以被恢复的信息. 假如你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (注意: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML) 3、尽量多使用COMMIT：只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少 4、用Where子句替换HAVING子句 5、使用表的别名(Alias)：当在SQL语句中连接多个表时, 使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. 6、用EXISTS替代IN、用NOT EXISTS替代NOT IN：在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的。 7、用EXISTS替换DISTINCT： 8、SQL语句用大写的因为Orale总是先解析SQL语句，把小写的字母转换成大写的再执行 9、避免在索引列上使用NOT： 10、用>=替代> 11、用IN来替换OR 12、避免在索引列上使用IS NULL和IS NOT NULL 13、总是使用索引的第一个列等

28.SQLServer和Oracle的区别是什么？

（1）数据类型不同。sql server 的数据类型：int ,smallint ,char,varchar,nchar,nvarchar,ntext,datetime,smalldatetime,money,decima,float,bit；oracle 的数据类型：number(p,s),char,varchar2,Date,LOB（2）获得当前系统时间的函数不同。（3）在oracle中没有默认约束的说法（4）连接变量和字符串的方式不一样（5）oracle没有identity自动增长列，而是使用序列实现增长（6）条件语句if……else……的语法不同（7）case语句的语法不同（8）触发器创建语法不同

29.Oracle数据库怎样删除重复行,怎样根据条件筛选数据

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断；

　　select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

　　2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录；

　　DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

　　3、查找表中多余的重复记录（多个字段）；

　　select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

　　4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录；

　　delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

　　5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录；

　　select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

30.数据库中用什么关键字进行排序,升降序，分组，分组后查询

Order by排序，order by 列 desc|asc降升序，group by分组，having分组后查询

31.数据库中常用术语：

ddl:数据定义语言 Create Drop Alter

dml:数据操纵语言 insert update delete select

dcl:数据控制语言 grant revoke

tcl:事务控制语言 commit rollback

32.Sql语句优化部分：

1.我们项目组是不提倡使用存储过程的，因为我们之前的的后台管理是.net ，之后数据量大的时候发现速度很慢，就打算改成java，而且数据库的结构也有一些变化，看到.net那边有很多的存储过程，由于数据结构的改变，我们放弃了之前的存储过程，修改为代码里sql去完成，因为存储过程的移植性差，而且不易于维护。-->对于互联网项目模型变更频繁，用存储过程局限性太强。而且分布式项目大量使用存储过程后，首先会使服务器压力增大，数据库多的话，只要有一个地方发生了修改，那么其他的地方都要进行修改，这样开发的效率反而低了，而且容易出错。

举例：如果表中有个字段用的是clob或者是blob这种大数据字段的话，他们的查询应该根据需要来进行指定字段的查询，切记勿直接用*

2.(重点) 删除重复记录(mysql)：

3. 用 >= 替换>

如一个表有100万记录，一个数值型字段A，

A=0时，有30万条；

A=1时，有30万条；

A=2时，有39万条；

A=3时，有1万记录。

那么执行 A>2 与 A>=3 的效果就有很大的区别了，因为 A>2 时，

ORACLE会先找出为2的记录索引再进行比较，

而A>=3时ORACLE则直接找到=3的记录索引。

4.(重点)尽量多使用COMMIT

如对大数据量的分段批量提交

5. (重点)用NOT EXISTS 或（外连接+判断为空）方案替换 NOT IN操作符

此操作是强列推荐不使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

6.(重点必须说)LIKE操作符(大数据的全文检索使用luncene)(solr)

因为使用like不当，会导致性能问题，原因是like在左右两边都有

%的时候，不会使用索引。

如LIKE '%5400%' 这种查询不会引用索引，

而LIKE 'X5400%' 则会引用范围索引。

一个实际例子：

查询营业编号 YY_BH LIKE '%5400%' 这个条件会产生全表扫描，

如果改成 YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%'

则会利用 YY_BH 的索引进行两个范围的查询，性能肯定大大提高。

7.(重点,必须说)避免在索引列上使用计算和函数,这样索引就不能使用

举例:

低效：SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

8.(重点必须说)用UNION-ALL 替换UNION，

因为UNION-ALL不会过滤重复数据而且不会自动排序，所执行效率要快于UNION。

9. （优化,重点,3个方面 a.缓存 b.分段批量 c.存储过程）减少访问数据库的次数

举例:如果批量删除多条数据，可以用 delete from tableName where id in (1,2,3)

而不要用多条delete语句进行删除

10.（重点必须说）用 TRUNCATE 替代DELETE

TRUNCATE不记录日志，DELETE记录日志，所以TRUNCATE要快于DELETE

但是一旦用TRUNCATE进行删除就不能进行恢复,TRUNCATE是删除整张表的数据不能加where条件。

mysql,sqlserver中如果id为自增类型，那么如果用TRUNCATE删除，则id字段再插入数据时从1开始，如果delete删除的话，则从删除之前的id的值继续增长。

33.防sql注入

针对防sql注入，我们通常是这样做的：

首先在前台页面对用户输入信息进行js验证，对一些特殊字符进行屏蔽，

比如：or ,单引号，--，= ，还有就是限制用户名输入的长度，我们一般将其限制在6---13位。另外，对于用户的敏感信息我们进行Md5加密，还有，为了增加用户体验度和用户友好度，为了不使用户看到一些详细的异常信息，我们会进行错误信息页面的定制，像404,500错误。另一个我层面讲，这样也是为了保护我们的一些重要信息。此外，我们会给特定的人分配定定的权限，而不是给其分配管理员权限！

33.存储过程的优缺点：

优点：

　　简化了复杂的业务逻辑，根据需要可重复使用

　　屏蔽了底层细节，不暴露表信息即可完成操作

　　降低网络的通信量，多条语句可以封装成一个存储过程来执行

　　设置访问权限来提高安全性

　　提高执行效率，因为它是预编译以及存储在数据库中

缺点：

　　可移植性差，相同的存储过程并不能跨多个数据库进行操作

　　大量使用存储过程后，首先会使服务器压力增大，而且维护难度逐渐增加

34.如何定位到有问题的sql?

当程序运行慢的时候，我们会根据接口访问的时间（这个我们是通过Aop实现的），定位到超时时间的接口，然后分析接口的业务，如果接口的业务中有sql的话，在拿出相应的sql做分析。若不是sql的问题，而是数据的并发量导致数据库慢的话，可以采用mysql的读写分离来解决，因为大多数的场景都是读多，写少的场景，这样主库的写的压力就会减轻很多。如果是调用其他服务导致查询变慢，那么我们就通知他们调式接口。

35.MySQL 主从复制（读写分离）的原理和配置

1)主从复制解决了什么问题？

优点：

1.减轻主库的压力（因为一般的业务都是读多写少的。让写的任务都落到主库，读的任务都落到从，这样主库的压力就会减轻，从而提高网站的吞吐量）

2.数据备份

缺点：

1.【异步复制】不一致时间窗口（会导致从库查不到，变更的数据）

2.【同步复制】牺牲性能

2)注意点

1.master写二进制日志，也是有事物，（和持久化到数据库是同一个事物）

主从复制的原理：

主从复制有三种类型：

分为

1.同步复制【保证了数据的强一致性，但是牺牲了性能，高并发不建议采用】

master接受一个变更数据的请求（增，删，改），将变更的信息sql保存到自己的二进制日志中，因为是同步复制，所以要等到从库同步过这条sql才给用户返回成功

2.异步复制（提高了性能，但是有可能看不到新变更的数据）

master接受一个变更数据的请求（增，删，改），将变更的信息sql保存到自己的二进制日志中，就给用户返回成功，之后从库一个监听主库二进制日志的线程，读取到改变的sql,同步到从库中继日志中，然后由执行sql的一个线程，执行这个sql这样从库就有了这条数据。（但是有一个不一致时间段）

3.半同步复制（可以将同步复制的机器作为备机）

master接受一个变更数据的请求（增，删，改），将变更的信息sql保存到自己的二进制日志中，因为是半同步复制，所以要等到其中一个从库同步过这条sql才给用户返回成功，其他都是异步的同步的从库中

36.你们项目sql是如何优化的？

我们为了定位到有问题的sql,我们的controller方法执行时都回通过时间拦截器记录方法执行的时间。而且我们在安装mysql服务器的时候都会开启慢查询，我们可以查看慢查询的日志，找到运行慢的sql, 然后可以利用mysql的执行计划Explain+sql 根据显示的参数，来定位这条sql是否用到了全表扫描，在数据量大的情况下，全表扫描会严重影响sql的效率。然后分析一下这个sql是否可以简化（把* 修改为需要查询的字段），或者是建立相关的索引，如果sql采用的是子查询，我们尽量把他修改为连接查询，因为子查询要创建临时表和删除临时表，这样的话会影响效率。

如果在并发量大的时候，只通过sql优化是解决不了数据库的压力，因为大部分的场景都是读多写少的，这样就可以采用mysql的读写分离。

在使用索引的过程中

（1）在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效，

（2）OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效

（EXPLAIN SELECT * from t_product WHERE PRODUCT_CODE='PN201610120023'; # 0.012s --> 0.003）

（3）WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引

（4）如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引

2.使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

EXPLAIN SELECT id,name,age from

(SELECT id,name,age from t_user WHERE age=19) b

WHERE b.age=19;

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

未完待续。。。。将不定时更新