数据库总结

1. ORACLE和MySQL的区别
2. 是大型数据库而Mysql是中小型数据库，Oracle市场占有率达50%，Mysql只有50%左右，同时Mysql是开源的而Oracle维护价格非常高。
3. Oracle支持大并发，大访问量，是OLTP最好的工具。(OLTP[联机事务处理])(OLAP[数据仓库处理 有了基础大数据后 根据数据分析 市场定位])
4. 安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。
   4.Oracle也Mysql操作上的一些区别【开发人员接触的】
   ①主键
   Mysql一般使用自动增长类型，在创建表时只要指定表的主键为auto increment,插入记录时，不需要再指定该记录的主键值，Mysql将自动增长；Oracle没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值付给该字段即可；只是ORM框架是只要是native主键生成策略即可。
   ②单引号的处理
   MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。
   ③翻页的SQL语句的处理 name=‘zs’
   MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80
   ⑤空字符的处理
   MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。
   ⑥字符串的模糊比较
   MYSQL里用 字段名 like ‘%字符串%’,ORACLE里也可以用 字段名 like ‘%字符串%’ 但这种方法不能使用索引, 速度不快。【like ‘%’开头 无法使用索引 不使用开头 可以使用索引】
   ⑦Oracle实现了ANSII SQL中大部分功能，如，事务的隔离级别、传播特性等而Mysql在这方面还是比较的弱
5. 存储过程和函数的区别【重点】
   1). 可以理解函数是存储过程的一种 ，都是预编译的 【存储过程块 一次编译多次运行 效率更高】
   2). 函数一定需要一个返回值，存储过程不需要返回值 如果需要返回值 只能添加一个out 修饰的参数
   3). 在sql数据操纵（DML）语句中只能调用函数而不能调用存储过程

存储过程
DELIMITER /
CREATE PROCEDURE addNumber(p1 INT,p2 INT,OUT p3 INT)
BEGIN
SET p3=p1+p2;
END
/
函数
DELIMITER /
CREATE FUNCTION addnumber1(p1 INT ,p2 INT)
RETURNS INT
BEGIN
RETURN p1+p2;
END

3. mysql导入和导出方式
   备份：mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql
   还原：mysql -hhostname -uusername -ppassword databasename < backupfile.sql
   登录后 use 数据库， source 文件

4. Mysql引擎
   数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进 行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是存储引擎。
   InnoDB存储引擎是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎
   MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物
   MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问

5. 优化mysql数据库，有几种方式（重点）
   1>参数进行优化
   3:设置最大连接数
   mysql>set globle max_connections = 5000;
   程序中使用连接池
   2>对SQL语句以及表优化MySQL参数优化
   ① 为查询缓存优化查询(sql标准 select * from a select * from A)
   ② EXPLAIN 我们的SELECT查询(可以查看执行的行数)
   Select * from ii where i=10 and b=’zs’
   ③ 当只要一行数据时使用LIMIT 100
   不管查询的记录是怎样 都要带上limit关键字
   ④ 为搜索字段建立索引
   ④ 在Join表的时候使用相当类型的列，并将其索引
   ⑤ 千万不要 ORDER BY RAND ()
   ⑦ 避免SELECT *
   ⑧ 永远为每张表设置一个主键
   ⑨ 可以使用ENUM 而不要VARCHAR
   ⑩ 尽可能的使用NOT NULL （确保值非空）
   ⑪ 固定长度的表(所有列固定长度)会更快
   ⑫ 垂直分割 不同的业务拆分不同的表
   车源表
   新车
   二手车
   租车
   ⑭ 越小的列会越快
   ⑮ 选择正确的存储引擎
   线上碰到性能问题的三把利
   1 使用explain查看sql语句执行rows，判断是否使用索引
   2 打开慢查询，获取查询慢的sql，进行优化
   3 定位到慢的sql语句，使用profile查看执行时间，资源开销，诸如IO，上下文，CPU，MEMORY等。mysql是怎样分页的？
   使用limit关键字

6. mysql怎样存储文件？
   将上传文件存储在文件系统中，使用一个路径关联，将路径写到数据库中

7. 解释冷备份和热备份的不同点以及各自的优点？
   冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
   热备份是在数据库运行的情况下，采用归档方式备份数据的方法
   冷备的优缺点：
   1)．是非常快速的备份方法（只需拷贝文件）
   2)．容易归档（简单拷贝即可）
   3)．容易恢复到某个时间点上（只需将文件再拷贝回去）
   4)．能与归档方法相结合，作数据库“最新状态”的恢复。
   5)．低度维护，高度安全。
   冷备份不足：
   1)．单独使用时，只能提供到“某一时间点上”的恢复。
   2)．在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
   3)．若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
   4)．不能按表或按用户恢复。

备份方式：（备份所有的数据文件 控制文件 重做日期文件和密码文件 ）

热备的优缺点
1)．可在表空间或数据文件级备份，备份时间短。
2)．备份时数据库仍可使用。
3)．可达到秒级恢复（恢复到某一时间点上）。
4)．可对几乎所有数据库实体作恢复。
5)．恢复是快速的，在大多数情况下在数据库仍工作时恢复。
热备份的不足是：
1)．不能出错，否则后果严重。
2)．若热备份不成功，所得结果不可用于时间点的恢复。
3)．因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

8. 比较truncate和delete命令 ？

1). Truncate 和delete都可以将数据实体删掉，truncate 的操作并不记录到 rollback日志，所以操作速度较快，但同时这个数据不能恢复
2). Delete操作不腾出表空间的空间
3). Truncate 不能对视图等进行删除
4). Truncate是数据定义语言（DDL），而delete是数据操纵语言(DML)
5）delete带条件删除
9. . 解释什么是慢查询？

MySQL中的日志包括：错误日志、二进制日志、通用查询日志、慢查询日志等等。优化sql经常使用到慢查询日志。
MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件）。默认情况下，MySQL数据库是不开启慢查询日志的，long_query_time的默认值为10（即10秒，通常设置为1秒），即运行10秒以上的语句是慢查询语句。

一般来说，慢查询发生在大表（比如：一个表的数据量有几百万），且查询条件的字段没有建立索引，此时，要匹配查询条件的字段会进行全表扫描，耗时查过long_query_time，

慢查询如何优化
1 如果是关联查询慢，可以将关联查询拆开 逐个执行， 找到具体关联慢的那两张表，将关联慢的两张表单独执行
查看分析数据量的大小(查看执行计划)，确定两张表的关联关系是否建立了主外键 主外键数据类型是否一致，单表查询慢检查查询条件是否使用了索引 一般几千万数据使用索引，一般查询都不会太慢 ，索引无法解决 就要考虑当前表数据是否一部分数据可以移除，比如 删除时 只是逻辑删除将某个标志位设置为0就表示删除了 依然占据数据空间 可以关联时加上删除条件（删除条件建立位图索引）或者将删除数据移除到历史记录表，数据量依然大可以考虑 将某些不常用的历史记录数据移除到历史记录表或者按照时间分区 查询条件必须带上时间，依然不行 根据业务，水平分表，比如文章数据 可以根据用户id分表 1-1000用户的文章在A表 2000-5000用户的文章在B表
如果在进行外关联时，尽量通过条件降低主表数据的量 在与从表关联 因为单表查询速度快
比如 进行分页 可以先将主表分页再与从表关联 实际上左表只有10条记录和右表关联
select * from from arcticle a left join userinfo b on a.id=b.id
比如 select * from (select * from arcticle limit 1,10) a left join userinfo b on a.id=b.id
比如 select * from (select * from arcticle where createdate=curdate()) a left join userinfo b on a.id=b.id
2 如果是子查询发现很慢 考虑将sql换成关联查询 如果能确定是常量的直接用常量不使用子查询 比如
select * from userinfo where sex=0 //建议
select * from userinfo where sex=(select sex from se where sname=’男’) //不建议

10. mysql的最大存储量

MySQL 3.22 限制的表大小为4GB。由于在MySQL 3.23 中使用了MyISAM 存储引擎，最大表尺寸增加到了65536TB（2567 – 1字节）。由于允许的表尺寸更大，MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，而不是由MySQL内部限制决定的。

InnoDB 存储引擎将InnoDB 表保存在一个表空间内，该表空间可由数个文件创建。这样，表的大小就能超过单独文件的最大容量。表空间可包括原始磁盘分区，从而使得很大的表成为可能。表空间的最大容量为64TB。
MySQL单表大约在2千万条记录（4G）下能够良好运行，经过数据库的优化后5千万条记录（10G）下运行良好 ,数据量过大考虑分区 分表 分库
11. mysql的sql语句执行过程
一般执行计划用于查看sql执行过程消耗，是否使用索引等
比如 ：
EXPLAIN SELECT * FROM tb_user_info u INNER JOIN tb_user_role_relation ur ON u.userid=ur.userid WHERE u.userid=1

Id 表示关联表的执行过程 id越大 最先被执行 。
rows：表示关联查询时，当前表付出的代价，使用了多少行 。
type： 表示访问类型，sql查询优化中一个很重要的指标 比如
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，好的sql查询至少达到range级别，最好能达到ref
1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const

3 eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
4. ALL：Full Table Scan，遍历全表以找到匹配的行

12. 简述sql中 dml、ddl、dcl的使用
    Dml 数据操纵语言，如select、update、delete，insert
    Ddl 数据定义语言，如create table 、drop table 等等
    Dcl 数据控制语言， 如 grant、 invoke等
    TCL 事务控制语言 如 commit、 rollback、checkpoint

13. 说说mysql中的经常使用到得函数
    常用字符串函数：
    insert(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的字串替换为字符串instr
    concat(s1,s2,s3,…sn) 连接s1,s2…sn为一个字符串
    lower(str) 将字符串str中所有字符变为小写
    upper(str) 将字符串str中所有字符变为大写
    left(str,x) 返回字符串str最左边的x 个字符
    right(str,x) 返回字符串str最右边的x个字符
    lpad(str,n,pad) 用字符串pad对str最左边进行填充，直到长度为n个字符长度
    rpad(str,n,pad) 用字符串pad对str最右边进行填充，直到长度为n个字符长度
    ltrim(str) 去掉字符串str左侧空格
    rtrim(str) 去掉字符串str行尾的空格
    repeat(str,x) 返回str重复x次的结果
    replace(str,a,b） 用字符串b替换字符串str中所有的a
    strcmp(s1,s2) 比较字符串s1和s2
    trim(str) 去掉字符串尾部和首部的空格
    substring(str,x,y) 返回从字符串str的x位置起y个字符长度的字串

14. 乐观锁和悲观锁

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
Int i=10 version=1
线程A 10 -3 version=2
线程B 10 -2 失败
分布式系统

Zs 下单 衣服 100 version

启动订单处理线程（异步队列 kafka rabbitmq activemq）

15. 使用mysql删除表中重复记录：
    Delete table t where t.主键!=(select max(t1. 主键) from table1 t1 where t1.name=t.name)

16. 如何设计数据库
    使用POWERDESINER设计er图（概念模型图），设计实体和实体的关系（一对一，一对n），一般满足第三范式就可以了。

17. 什么是数据库设计三范式和ACID特性
    1 列是不可再分
    2 一张表只能做一件事情
    3 列不允许间接依赖
    什么是事务ACID特性：
    (1)原子性
    指一个事务要么全执行,要么全不执行.也就是说一个事务不可能执行到一半就停止了.比如:你去买东西,钱付掉了,东西没拿.这两步必须同时执行 ,要么都不执行.
    (2)一致性
    指事务的运行和业务的规则是一致的 比如设置列为not null， 插入的数据就不能插入null值
    (3)隔离性
    多个事务操作是互相隔离的，事务操作拥有自己独立的空间 互不影响 比如事务A未提交的数据 事务B是无法查看
    (4) 持久性
    事务运行成功之后数据库的更新是永久的,不会无缘无故的回滚.

18. 数据的隔离级别是什么分别解决什么问题
    数据库事务的隔离级别有4个，
    由低到高依次为Read uncommitted、
    Read committed、
    Repeatable read、
    Serializable，
    这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

19. 什么是视图
    针对多个物理表的sql查询的映射虚表 用来控制列的权限问题，更清晰表达列的数据

20. 索引的优缺点，分类，使用原则
    优点：快速查询
    缺点：数据量大 需要加大索引的维护量

索引分为以下几类:
1.普通索引 是最基本的索引，它没有任何限制
CREATE INDEX index_name ON table(column(username))
2.唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
CREATE UNIQUE INDEX indexName ON table(column(length))
3.主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) NOT NULL ,
PRIMARY KEY (id)
);
4.组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE table ADD INDEX name_city_age (name,city,age);
5.全文索引 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like
CREATE FULLTEXT INDEX index_content ON article(content)

设计原则如下:
1．唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

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

3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，

4．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文，检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索，会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

21. 说一下 什么是笛卡尔积，内连接，左外连接，右外连接，全连接 并且举例
    笛卡尔积 不同表之间行的笛卡尔积
    内连接 笛卡尔积的结果 满足条件的保留
    左外连接 笛卡尔积的结果 如果左边一条记录和右边无关联 保留左边数据
    右外连接 笛卡尔积的结果 如果右边一条记录和左边无关联 保留右边数据
    全连接 左外连和右外连的结果合并去重复
    Emp（雇员表）
    雇员编号 雇员名称 部门编号
    1 zs 1
    2 ls 3

dept(部门表)
部门编号 部门名称
1 研发部
2 测试部

22. 描述你写过最大的存储过程
    一般因为没写过 所以你可以解释为 偶尔写 大的存储过程一般较少 总结一下存储过程的优缺点 ，一般存储过程预编译所以执行速度比一般sql执行速度更快 ，但是不同数据库之间需要迁移的情况下 因为plsql的不一致 所以很难维护 ，同时业务变更频繁的情况下 存储过程需要大量的修改 代码量大 因为公司人员的迭代 导致维护的难道增加等 都不太适应使用，存储过程执行 消耗数据库的资源 导致数据库崩溃 java的方法替代

23. union和union all的区别
    {1,2} 交集 {2,3}=2 intersect
    {1,2} 并集{2,3} 1 3 2 union union all
    {1,2} 差集 {2,3} 1 minus

union：对两个结果集进行并集操作，不包括重复行
union all：对两个结果集进行并集操作，包括重复行，不进行排序；
比如
select ‘1’，’zs’ union select ‘1’，’zs’ union select ‘2’，’ls’
结果是
1 zs
2 ls
select ‘1’，’zs’ union all select ‘1’，’zs’ union all select ‘2’，’ls’
结果是
1 zs
1 zs
2 ls

24. mysql的主从结构和读写分离

主从结构就是一种集群模式,主数据库负责写入,写入后自动同步到所有的从服务器 从服务器负责读 解决服务器的压力和高并发的问题

javaWeb应用如果切换主从数据库 ，使用反向代理 常用的数据库反向代理有以下两种：
mysqlproxy
mycat
数据库反向代理等价于一个mysql服务器 数据库反向代理配置所有的数据库的角色（主从）
web应用连接到反向代理服务器 web应用发起select语句到反向代理服务器是，反向代理服务器自动负载均衡转发到从数据库查询数据后返回给应用，发起update，delete，insert语句时 将sql语句转发到主数据库中，从数据库自动同步主数据库数据

Mycat相对于mysqlproxy来说 多了一个分表分库的功能 可以在mycat中配置分表策略
比如 按照用户id范围分表
Arc（id ,title,content,userid）{
1 zs ddf 1
2 zs ddf 1
}
mycat中配置分表策略
1-100000 Arc A数据库
200000 -500000 Arc B数据库