sql 优化有哪些? 如何创立索引? 创立索引的准则? 索引的优缺点?
sql 的优化有:
尽量避免应用 select *,返回无用的字段会升高效率。优化形式:只能应用具体的字段代替 select 具体字段,只返回应用到的字段。
尽量避免应用 in 和 not in,会导致数据库引擎放弃索引进行全表扫描。优化形式:如果是间断数值,能够用 betwween 代替,如果是子查问,能够用 exists 代替。
尽量避免在字段结尾含糊查问,会导致数据库引擎放弃索引进行全表扫描。优化形式: 尽量在字段前面应用含糊查问。
尽量避免进行 null 值的判断,会导致数据库引擎放弃索引进行全表扫描。优化形式: 能够给字段增加默认值 o,对О值进行判断。
如何创立索引:
数据库索引,是数据库管理系统中一个排序的数据结构,索引的实现通常应用 B 树及其变种 B + 树。
创立索引有两种:
间接创立索引,例如应用 CREATE INDEX 语句或者应用创立索引向导。
间接创立索引,例如在表中定义主键束缚或者唯一性键束缚时,同时也创立了索引。
索引的作用:
帮助疾速查问、更新数据库表中数据。
为表设置索引要付出代价的:
一是减少了数据库的存储空间;二是在插入和批改数据时要花费较多的工夫(因为索引也要随之变动)。
创立索引能够大大提高零碎的性能(长处):
通过创立唯一性索引,能够保障数据库表中每一行数据的唯一性。
能够大大放慢数据的检索速度,这也是创立索引的最次要的起因。
能够减速表和表之间的连贯,特地是在实现数据的参考完整性方面特地有意义。
在应用分组和排序子句进行数据检索时,同样能够显著缩小查问中分组和排序的工夫。
通过应用索引,能够在查问的过程中,应用优化暗藏器,进步零碎的性能。减少索引也有许多不利的方面 (毛病):
创立索引和保护索引要消耗工夫,这种工夫随着数据量的减少而减少。
索引须要占物理空间,除了数据表占数据空间之外,每一个索引还要占肯定的物理空间,如果要建设聚簇索引,那么须要的空间就会更大。
当对表中的数据进行减少、删除和批改的时候,索引也要动静的保护,这样就升高了数据的保护速度。
创立索引的准则:
最左前缀匹配准则;
= 和 in 能够乱序;
尽量抉择区分度高的列作为索引;
索引列不能参加计算;
尽量的扩大索引,不要新建索引;
抉择唯一性索引;
为常常须要排序、分组和联合操作的字段建设索引;
为常作为查问条件的字段建设索引;
限度索引的数目;
尽量应用数据量少的索引;
尽量应用前缀来索引;
删除不再应用或者很少应用的索引。
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;
内连贯和外连贯的区别
天然连贯:是一种非凡的等值连贯,他要求两个关系表中进行比拟的必须是雷同的属性列,毋庸增加连贯条件,并且在后果中打消反复的属性列。
sql 语句:Select …… from 表 1 natural join 表 2
内连贯:根本与天然连贯雷同,不同之处在于天然连贯要求是同名属性列的比拟,而内连贯则不要求两属性列同名,能够用 using 或 on 来指定某两列字段雷同的连贯条件。
sql 语句:Select …… from 表 1 inner join 表 2 on 表 1.A= 表 2.E
左外连贯:是在两表进行天然连贯,只把左表要舍弃的保留在后果集中,右表对应的列上填 null。
sql 语句:Select …… from 表 1 left outer join 表 2 on 表 1.C= 表 2.C
右外连贯:是在两表进行天然连贯,只把右表要舍弃的保留在后果集中,左表对应的列上填 null。
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 条件来进行分段查问了。
百万级量的数据分页查问如何优化?
间接应用数据库提供的 SQL 语句;
建设主键或惟一索引, 利用索引 (假如每页 10 条);
基于索引再排序;
基于索引应用 prepare(第一个问号示意 pageNum,第二个?示意每页元组数);
利用 MySQL 反对 ORDER 操作能够利用索引疾速定位局部元组, 防止全表扫描;
利用 ” 子查问 / 连贯 + 索引 ” 疾速定位元组的地位, 而后再读取元组。
数据库的乐观锁和乐观锁的了解和应用?
乐观锁,就是对数据的抵触采取一种乐观的态度,也就是说假如数据必定会抵触,所以在数据开始读取的时候就把数据锁定住。(数据锁定:数据将临时不会失去批改)
乐观锁,认为数据个别状况下不会造成抵触,所以在数据进行提交更新的时候,才会正式对数据的抵触与否进行检测,如果发现抵触了,则让用户返回谬误的信息。让用户决定如何去做。
应用:
乐观锁通常依附数据库提供的锁机制实现,比方 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
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 条件进过滤
高并发下如何保障批改数据安全
基于 JVM 解决:
应用 Synchronized 解决,给生成 ID 的代码加上同步代码块,胜利解决问题;
作用是:同一时刻,只有一个线程能够执行该代码块
应用 Lock 锁解决问题:给生成 ID 的代码加上 Lock 锁,胜利解决问题;
应用倡议:
某个线程在期待一个锁的控制权的这段时间须要中断
须要离开解决一些 wait-notify,ReentrantLock 外面的 Condition 利用,可能管制 notify 哪个线程
具备偏心锁性能,每个到来的线程都将排队等待
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 死锁如何解决?