sql 优化有哪些? 如何创立索引? 创立索引的准则?
索引的优缺点?
sql 的优化有:
尽量避免应用 select *，返回无用的字段会升高效率。优化形式：只能应用具体的字段代替 select 具体字段，只返回应用到的字段。
尽量避免应用 in 和 not in，会导致数据库引擎放弃索引进行全表扫描。优化形式：如果是间断数值，能够用 betwween 代替，如果是子查问，能够用 exists 代替。
尽量避免在字段结尾含糊查问，会导致数据库引擎放弃索引进行全表扫描。优化形式: 尽量在字段前面应用含糊查问。
尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描。优化形式: 能够给字段增加默认值 0，对 0 值进行判断。
如何创立索引：
数据库索引，是数据库管理系统中一个排序的数据结构，索引的实现通常应用 B 树及其变种 B + 树。
创立索引有两种:
间接创立索引，例如应用 CREATE INDEX 语句或者应用创立索引向导。
间接创立索引，例如在表中定义主键束缚或者唯一性键束缚时，同时也创立了索引。
索引的作用:
帮助疾速查问、更新数据库表中数据。
为表设置索引要付出代价的：
一是减少了数据库的存储空间；二是在插入和批改数据时要花费较多的工夫（因为索引也要随之变动）。
创立索引能够大大提高零碎的性能（长处）:
通过创立唯一性索引，能够保障数据库表中每一行数据的唯一性。
能够大大放慢数据的检索速度，这也是创立索引的最次要的起因。
能够减速表和表之间的连贯，特地是在实现数据的参考完整性方面特地有意义。
在应用分组和排序子句进行数据检索时，同样能够显著缩小查问中分组和排序的工夫。
通过应用索引，能够在查问的过程中，应用优化暗藏器，进步零碎的性能。减少索引也有许多不利的方面 (毛病):
创立索引和保护索引要消耗工夫，这种工夫随着数据量的减少而减少。
索引须要占物理空间，除了数据表占数据空间之外，每一个索引还要占肯定的物理空间，如果要建设聚簇索引，那么须要的空间就会更大。
当对表中的数据进行减少、删除和批改的时候，索引也要动静的保护，这样就升高了数据的保护速度。
创立索引的准则：
最左前缀匹配准则（始终向右匹配直到遇到范畴查问就进行匹配）；
= 和 in 能够乱序（建设索引是能够任意程序的，mysql 的查问优化器会帮你优化成索引能够辨认的模式）；
尽量抉择区分度高的列作为索引（区分度的公式是 count(distinct col)/count(*)，示意字段不反复的比例，比例越大咱们扫描的记录数越少，惟一键的区分度是 1，）；
索引列不能参加计算（放弃列“洁净”，）；
尽量的扩大索引，不要新建索引（如 a ->（a，b）只须要批改原来的索引）；
抉择唯一性索引（唯一性索引的值是惟一的，能够更疾速的通过该索引来确定某条记录。）；
为常常须要排序、分组和联合操作的字段建设索引；
为常作为查问条件的字段建设索引；
限度索引的数目；
尽量应用数据量少的索引；
尽量应用前缀来索引；
删除不再应用或者很少应用的索引。
sql 语句关键词的执行程序？
FROM 子句, 组装来自不同数据源的数据；
WHERE 子句, 基于指定的条件对记录进行筛选
GROUP BY 子句, 将数据划分为多个分组
应用聚合函数进行计算
应用 HAVING 子句筛选分组
计算所有的表达式
应用 ORDER BY 对后果集进行排序
即：from—>where—>group by—>having—> 计算所有的表达式—>order by—>select 输入
sql 如何去重？
总的思路就是先找出表中反复数据中的一条数据，插入长期表中，删除所有的反复数据，而后再将长期表中的数据插入表中。
实现：
反复数据齐全一样，应用 distinct；
id 列不同,id 类型为 int, 自增字段, 应用聚合函数 max 或其余；
id 列不同,id 类型为 uniqueidentifier；应用 row_number() over()和 partition by 给每一组增加行号；将行号 = 1 的数据插入长期表中。
内连贯和外连贯的区别
天然连贯：是一种非凡的等值连贯，他要求两个关系表中进行比拟的必须是雷同的属性列，毋庸增加连贯条件，并且在后果中打消反复的属性列。
sql 语句：Select …… from 表 1 natural join 表 2
内连贯：根本与天然连贯雷同，不同之处在于天然连贯要求是同名属性列的比拟，而内连贯则不要求两属性列同名，能够用 using 或 on 来指定某两列字段雷同的连贯条件。
sql 语句：Select …… from 表 1 inner join 表 2 on 表 1.A= 表 2.E
左外连贯：是在两表进行天然连贯，右边表数据行全副保留，左边表保留合乎连贯条件的行。
sql 语句：Select …… from 表 1 left outer join 表 2 on 表 1.C= 表 2.C
右外连贯：是在两表进行天然连贯，左边表数据行全副保留，右边表保留合乎连贯条件的行。
Select …… from 表 1 rignt outer join 表 2 on 表 1.C= 表 2.C
Java 中如何应用 redis？redis 反对的数据类型及各种数据类型的应用场景？redis 如何解决数据过期？
如何应用 redis：
redis 的装置 (windows)；启动 redis，默认端口 6379；连贯 redis 输出 redis-cli.exe -h 127.0.0.1 -p 6379
java 中利用 jedis 连贯 redis
spring 集成 redis：
引入 spring-data-redis.jar 包；
redis.properties 配置文件；
spring-redis 配置文件 (此处蕴含了数据库和 mybatis 的配置)；
redis 工具类(此处 try catch 为避免 redis 宕机等问题时 办法能够继续执行)；
将 spring-redis.xml 蕴含到 web.xml 中；
在须要 redis 的业务类中注入 redisUtil。
redis 反对的数据类型及各种数据类型的应用场景：
redis 如何解决数据过期：
Redis 提供了两种的形式，用于删除过期的数据：
定期删除：Redis 默认 100ms 随即抽取局部设置过期工夫的 key，过期了就删除。长处是防止长时间的在扫描过期 key，毛病是有些过期 key 无奈被删除。
惰性删除：如果查问了某个过期 key，但定期删除没有删除掉，那就将其删除了。key 没过期就失常返回。
数据库表的设计注意事项有哪些？三大范式的理解？
数据库设计的注意事项：
字段的原子性：保障每列的原子性，不可合成，能用一个字段表白分明的绝不应用第二个字段。
主键设计：主键不要与业务逻辑有所关联，最好是毫无意义的一串独立不反复的数字。
字段应用次数：对于频繁批改的字段（个别是指状态类字段）最好用独立的数字或者单个字母去示意，不必应用汉字或长字符的英文。
字段长度：建表的时候，字段长度尽量要比理论业务的字段大 3 - 5 个字段左右，最好是 2 的 n 次方幂值。
对于外键：尽量不要建设外键，保障每个表的独立性。
动静拆散：最好做好动态表和动静表的拆散。
对于 code 的值：应用数字码或者字母去代替理论的名字，也就是尽量把 name 转换为 code。
对于 null 的值：尽量不要有 null 值，有 null 值的话，数据库在进行索引的时候查问的工夫更久，从而节约更多的工夫！能够在建表的时候设置一个默认值！
对于引擎的抉择：myisam 的理论查问速度要比 innodb 快，因为它不扫面全表，然而 myisam 不反对事务，没方法保证数据的 Acid。
资源存储：数据库不要存储任何资源文件。
与主键相干：依据数据库设计三大范式，尽量保障列数据和主键间接相干而不是间接相干
关系映射：多对一或者一对多的关系, 关联一张表最好通过 id 去建设关系。
预留字段：在设计一张表的时候应该预制一个空白字段，用于当前的扩大。
留下繁多字段确定是否可用：通过一个繁多字段去管制表是否可用。
删除字段：数据库是禁止应用 delete 命令的, 个别都不会真正删除数据，都是采纳改状态的形式，设置 state 字段，通过批改状态赋予它是否无效的逻辑含意！
三大范式的理解：
第一范式（1NF）: 确保每一列的原子性
数据表中的每一列都是最小的不可分割的单元
第二范式（2NF）：表中的记录是惟一的
表中的数据是能够通过主键来辨别的
第三范式（3NF）：表中数据不要有冗余
在一个表中不要呈现其余表中除了关键字段（主键）的其余字段，用外键进行关联
存储过程的理解和应用？
存储过程：就是作为可执行对象寄存在数据库中的一个或多个 SQL 命令。艰深来讲：存储过程其实就是能实现肯定操作的一组 SQL 语句。
长处：
存储过程可封装，并暗藏简单的商业逻辑。
存储过程能够回传值，并能够承受参数。
存储过程无奈应用 select 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程能够用在数据测验，强制履行商业逻辑等。
毛病：
存储过程，往往定制化于特定的数据库上，因为反对的编程语言不同。当切换到其余厂商的数据库系统时，须要重写原有的存储过程。
存储过程的性能调校和撰写，受限于各种数据库系统。
应用：
创立存储过程保留在数据库的数据字典中。
查问所有存储过程状态
查看对应数据库下所有存储过程状态
mysql 存储过程用 call 和过程名以及一个括号，括号外面依据须要，退出参数，参数包含输出参数、输入参数、输入输出参数调用。
数据库如何实现分页？
SQL Server
在分页查问上，我感觉 SQL Server 比拟吃力，没有一个专门的分页的语句，靠的是一种奇妙的办法实现分页查问。
MySQL
MySQL 有个专门针对查问出一段数据的语句 limit，应用起来十分的不便。
Oracle
Oracle 中有个 rownum，其含意更加显著，就是第几行的意思，这样咱们就能够通过 where 条件来进行分段查问了。
百万级量的数据分页查问如何优化？
– 办法 1: 间接应用数据库提供的 SQL 语句
– 语句款式: MySQL 中, 可用如下办法: SELECT * FROM 表名称 LIMIT M,N
– 适应场景: 实用于数据量较少的状况(元组百 / 千级)
– 起因 / 毛病: 全表扫描, 速度会很慢 且 有的数据库后果集返回不稳固(如某次返回 1,2,3, 另外的一次返回 2,1,3). Limit 限度的是从后果集的 M 地位处取出 N 条输入, 其余摈弃.
– 办法 2: 建设主键或惟一索引, 利用索引(假如每页 10 条)
– 语句款式: MySQL 中, 可用如下办法: SELECT _FROM 表名称 WHERE id_pk > (pageNum_10) LIMIT M
– 适应场景: 实用于数据量多的状况(元组数上万)
– 起因: 索引扫描, 速度会很快. 有敌人提出: 因为数据查问进去并不是依照 pk_id 排序的，所以会有漏掉数据的状况，只能办法 3
– 办法 3: 基于索引再排序
– 语句款式: MySQL 中, 可用如下办法: SELECT _FROM 表名称 WHERE id_pk > (pageNum_10) ORDER BY id_pk ASC LIMIT M
– 适应场景: 实用于数据量多的状况(元组数上万). 最好 ORDER BY 后的列对象是主键或惟一所以, 使得 ORDERBY 操作能利用索引被打消但后果集是稳固的(稳固的含意, 参见办法 1)
– 起因: 索引扫描, 速度会很快. 但 MySQL 的排序操作, 只有 ASC 没有 DESC(DESC 是假的, 将来会做真正的 DESC, 期待 …).
– 办法 4: 基于索引应用 prepare（第一个问号示意 pageNum，第二个？示意每页元组数）
– 语句款式: MySQL 中, 可用如下办法: PREPARE stmt_name FROM SELECT FROM 表名称 WHERE id_pk > (？？) ORDER BY id_pk ASC LIMIT M
– 适应场景: 大数据量
– 起因: 索引扫描, 速度会很快. prepare 语句又比个别的查问语句快一点。
– 办法 5: 利用 MySQL 反对 ORDER 操作能够利用索引疾速定位局部元组, 防止全表扫描
比方: 读第 1000 到 1019 行元组 (pk 是主键 / 惟一键).
SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20
– 办法 6: 利用 ” 子查问 / 连贯 + 索引 ” 疾速定位元组的地位, 而后再读取元组. 情理同办法 5
如(id 是主键 / 惟一键, 蓝色字体时变量):
数据库的乐观锁和乐观锁的了解和应用？
乐观锁，就是对数据的抵触采取一种乐观的态度，也就是说假如数据必定会抵触，所以在数据开始读取的时候就把数据锁定住。（数据锁定：数据将临时不会失去批改）
乐观锁，认为数据个别状况下不会造成抵触，所以在数据进行提交更新的时候，才会正式对数据的抵触与否进行检测，如果发现抵触了，则让用户返回谬误的信息。让用户决定如何去做。
应用：
乐观锁通常依附数据库提供的锁机制实现，比方 mysql 的排他锁，select … for update 来实现乐观锁。
乐观锁不依附数据库提供的锁机制，须要咱们自已实现，实现形式个别是记录数据版本，一种是通过版本号，一种是通过工夫戳。
数据库中字符串和日期的互相转换？
Oracle
工夫转字符串 to_char(date,format)
select to_char(sysdata,’YYYY” 年 ”MM” 月 ”DD” 日 ”‘) 工夫转字符串 from dual
字符串转工夫 to_date(str,format)
select to_date(‘2019-10-25 17:15:20′,’yyyy-MM-dd HH24:mi:ss’)
字符串转工夫 from dual
select to_date(‘2019-10-25 17:15:20′,’yyyy-MM-dd HH24:mi:ss’) 字符串转工夫 from dual
MySQL
MySQL 内置函数，在 MySQL 外面利用 str_to_date() 把字符串转换为日期
示例：分隔符统一，年月日要统一
字符串转日期
select str_to_date(‘2019-10-25 15:43:28′,’%Y-%m-%d %H:%i:%s’);
日期转字符串
select DATE_FORMAT(SYSDATE(),’%Y 年 %m 月 %d 日 ’) MySQL 日期转字符串 from DUAL；
union 和 unionAll 区别？
union 和 union all 的区别是,union 会主动压缩多个后果汇合中的反复后果，而 union all 则将所有的后果全副显示进去，不论是不是反复。
Union：对两个后果集进行并集操作，不包含反复行，同时进行默认规定的排序；— 在进行表链接后会筛选掉反复的记录，所以在表链接后会对所产生的后果集进行排序运算，删除反复的记录再返回后果
Union All：对两个后果集进行并集操作，包含反复行，不进行排序；— 如果返回的两个后果集中有反复的数据，那么返回的后果集就会蕴含反复的数据了。
MySQL 的存储引擎有哪些？
InnoDB,MyISAM,Memory,Merge,Archive,Federate,CSV,BLACKHOLE
事务隔离级别有哪些？mysql 和 oracle 默认的隔离级别是什么？
事务的四大个性：
原子性：事务是最小的执行单位，不容许宰割。事务的原子性确保动作要么全副实现，要么齐全不起作用。
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的后果是雷同的；
隔离性：并发拜访数据库时，一个用户的事务不被其余事务所烦扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的扭转是长久的，即便数据库产生故障也不应该对其有任何影响。
脏读？幻读？不可反复读？
脏读 (Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，因为某些起因，前一个 RollBack 了操作，则后一个事务所读取的数据就会是不正确的。
幻读 (Phantom Read): 在一个事务的两次查问中数据笔数不统一，例如有一个事务查问了几列(Row) 数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查问中，就会发现有几列数据是它先前所没有的。
不可反复读(Non-repeatable read): 在一个事务的两次查问之中数据不统一，这可能是两次查问过程两头插入了一个事务更新的原有的数据。
SQL 如何行转列和列转行？
行转列：
case when 以及 sum（max 也能够）
列转行：
应用 union 和 case when 以及 concat 来实现
如何查看 sql 的执行打算？
set statistics profile on
explain
oracle 中的剖析函数有哪些？
剖析函数是 Oracle 专门用于解决简单报表统计需要的功能强大的函数，它能够在数据中进行分组而后计算基于组的某种统计值，并且每一组的每一行都能够返回一个统计值。
剖析函数带有一个开窗函数 over()，蕴含三个剖析子句: 分组 (partition by), 排序(order by), 窗口(rows)；
first_value() 与 last_value()：求最值对应的其余属性
rank(),dense_rank() 与 row_number()：求排序
lag() 与 lead()：求之前或之后的第 N 行
rollup() 与 cube()：排列组合分组
max(),min(),sun() 与 avg()：求挪动的最值总和与平均值
数据库中除了聚合函数之外还有哪些罕用的函数？oracle 数据库的 merge（）函数的作用和应用？
oracle 数据库 merge（）函数的作用和应用：
通常咱们对数据库数据进行插入的时候，会判断数据是否曾经存在，如果存在就批改，不存在就插入，个别咱们会写两个 sql，一个 insert 一个 update, 那么其实 oracle 中存在 merge 函数，能够一次性解决这个问题
SQL 中 drop、truncate、delete 的区别？
MySQL 中如何疏忽表名的大小写？
mysql 在 windows 零碎下装置好后，默认是对表名大小写不敏感的。
在 linux 下，一些零碎须要手动设置。用 root 登录，关上并批改 /etc/my.cnf；在 [mysqld] 节点下，退出一行：lower_case_table_names=1。重启 mysql 服务 systemctl restart mysqld
having 和 where 的区别？
Where 过滤子句是在查问过程中对表中数据的过滤条件，不容许应用聚合函数作为过滤条件，起因在于机会不对。聚合函数是对表中数据查问后的后果集进行统计解决的，两者的执行机会不统一。
Having 过滤子句是对查问后果集进行过滤的，能够在该子句后应用聚合函数，因为机会雷同，聚合函数是处理结果集的，having 子句又是过滤后果集的，能够在一起应用，另外 having 不能独自应用，只能跟在 group by 分组子句前面应用。
游标的作用和应用？
SQL 的游标是一种长期的数据库对象，即能够用来寄存在数据库表中的数据行正本，也能够指向存储在数据库中的数据行的指针。游标提供了在逐行的根底上操作表中数据的办法。
游标的一个常见用处就是保留查问后果，以便当前应用。
游标的后果集是由 SELECT 语句产生，如果处理过程须要重复使用一个记录集，那么创立一次游标而重复使用若干次，比反复查询数据库要快的多。
应用：
%FOUND– 判断游标中是否还有数据，若有，返回 true，否则，返回 false。
%NOTFOUND– 与 %FOUND 相同
%ISOPEN– 判断游标是否为关上状态
%ROWCOUNT– 记录已从游标中取出的记录数
如何应用数据库中的定时器？触发器？定时工作？
Oracle 中如何实现递归查问？
start with 条件一 connect by prior 条件二 where 条件三
第一种:start with 子节点 ID=’…’connect by prior 子节点 ID= 父节点 ID— 依照条件对条件 (包含本人) 及其子节点进行递归查问, 查问后果本人所有后辈节点 (包含本人)
第二种:start with 子节点 ID=’…’connect by 子节点 ID = prior 父节点 ID—- 依照条件对于条件 (包含本人) 及其父节点进行递归查问, 查问后果本人所有的前代节点 (包含本人)
第三种: start with 父节点 ID=’…’connect by prior 子节点 ID= 父节点 ID— 依照条对条件（不包含本人）子节点进行递归查问, 查问后果本人所有的后辈节点,(不包含本人)
第四种: start with 父节点 ID=‘…‘connect by 子节点 ID = prior 父节点 ID— 依照条件对条件（包含本人）的第一代孩子们及其父节点进行递归查问, 查问后果是本人的第一代后节点, 和所有的前代节点 (包含本人)
如果有 where 条件,(4)执行程序为先执行 start with connect by prior, 而后再依照 where 条件进过滤
高并发下如何保障批改数据安全
应用 Synchronized 解决，给生成 ID 的代码加上同步代码块，胜利解决问题；
作用是：同一时刻，只有一个线程能够执行该代码块
应用 Lock 锁解决问题：给生成 ID 的代码加上 Lock 锁，胜利解决问题；
乐观锁 — 在批改数据的时候, 采纳锁定状态, 排挤内部申请的批改, 遇到加锁的状态, 就必须期待
弊病: 在高并发下, 每个申请都须要期待’锁’, 某些线程可能永远都没有机会抢到这个锁, 申请就会死在那里, 这种申请会很多, 霎时增零碎的均匀响应工夫, 后果时可用链接数被耗尽, 零碎陷入异样
FIFO 队列 —- 采纳 FIFO(First Input First Output，先进先出), 这样就不会导致某些申请永远获取不到锁
弊病: 申请很多, 很有可能一瞬间将队列内存”撑爆”, 零碎陷入到异样状态, 或者设计一个极大的内存队列, 然而零碎解决完一个队列, 内申请的速度根本无法和疯狂涌入队列中的数目相比, 也就是说, 队列内的申请会越积攒越多, 最终 WEB 零碎均匀响应时候还是会大幅降落，零碎还是陷入异样。
乐观锁 — 绝对于”乐观锁”采纳更为宽松的加锁机制, 大都是采纳带版本号更新, 实现就是, 这个数据所有申请都有资格去批改, 但会取得一个该数据的版本号, 只有版本号合乎能力更新胜利, 其余的返回抢购失败, 这样就不必了思考队列的问题, 会增大 CPU 的计算开销,
Oracle 中如何实现主键自增？
Oracle 没有这个”auto_increment”属性，所以它没法像 MySQL 般在表内定义自增主键。然而，Oracle 里的序列（SEQUENCE），可间接实现自增主键的作用。也能够创立触发器实现自增。
序列（Sequence），又叫序列生成器，用于提供一系列的数字，开发人员应用序列生成惟一键。每次拜访序列，序列依照肯定的法则减少或者缩小。
序列的定义存储在 SYSTEM 表空间中，序列不像表，它不会占用磁盘空间。
序列独立于事务，每次事务的提交和回滚都不会影响序列。
Delete 误删数据没有备份怎么复原？
flashback query 闪回查问
– 尝试应用 Oracle 10g 当前的 flashback Query 个性 闪回查问能够查问若干工夫之前的数据
logmnr 日志开掘
– 应用 logminer 日志开掘 把 delete 的 redo 挖出来看有没有 对应的 undo 回滚 SQL 可用
应用 ORACLE PRM-DUL 工具
–Oracle PRM-DUL 工具能够复原 数据库中表上 被删除的记录。
Oracle 死锁如何解决？