数据库

java面试总结

SQL

SQL基础

drop、delete、truncate的区别

• delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
• truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
• drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

SQL优化

SQL语句的执行顺序

from --> where --> group by --> having --> 计算所有的表达式 --> order by --> select输出

1. from 子句组装来自不同数据源的数据；
2. where子句基于指定的条件对记录进行筛选；
3. group by子句将数据划分为多个分组；
4. 使用聚合函数进行计算（avg，count）；
5. 使用 having子句筛选分组；
6. 计算所有的表达式；
7. 使用order by对结果集进行排序；

SQL语句的优化

1. 怎么发现有问题的SQL？

   通过MySQL慢查询日志对有效率的问题进行监控。慢查询日志会记录MySQL中响应时间超过阈值（long_query_time）的语句，默认阈值为10s。可以查询出执行次数多占用时间长的SQL，可以通过mysql慢查询分析工具pt_query_disgest找出大IO的SQL以及没有命中索引的SQL。

2. 如何分析SQL的执行计划？

   通过explain查询和分析SQL的执行计划。

3. 如何优化？

   • 优化SQL语句

     • 用多少查多少，避免使用 select *；
     • 优化 insert 语句，一次插入多值；
     • 尽量避免在where中使用！=或< >操作符，因为不走索引全表扫描。
     • 尽量避免在 where 子句中对字段进行 null 值判断
     • 优化嵌套查询：子查询可以被更有效率的连接（join）代替；
     • 很多时候，用 exists 代替 in 更好

   • 索引优化

     • 在经常查询的字段使用索引

     • 少用 like 模糊匹配，因为不会走索引

数据库表结构的优化

• 选择合适的数据类型
• 表的范式的优化（遵循三大范式）
• 表垂直拆分和水平拆分
  
  • 不常用的字段和常用字段分开（拆表）
  • 大字段放一个表

系统配置的优化

操作系统配置的优化：增加TCP支持的队列数

mysql配置文件优化：Innodb缓存池设置( innodb_buffer_pool_size，推荐总内存的75%)和缓存池的个数（innodb_buffer_pool_instances）

MySQL

MySQL的两种存储引擎

• MyISAM（5.5之前）：不支持事务，不支持外键，表锁；插入数据时锁定整个表，查行数时无需整表扫描。主索引数据文件和索引文件分离；与主索引无区别；

• InnoDB：支持事务，外键，行锁，查表总行数时，全表扫描；主索引的数据文件本身就是索引文件；辅助索引记录主键的值；

MySQL分页

-- limit 当前页索引，每页的条数
select * from table1 where 条件 limit 0,5

Oracle分页

select * 
from (select rownum r1, e.* from (要分页的数据) e
      where rownum <= currPage*pageSize) A
where r1 > (currPage-1)*pageSize    

Oracle存储过程

-- 给指定的员工涨指定的薪水
create or replace procedure pro_addSal(eno number,addSal number) 
as
begin
    update emp set sal = sal + addSal where empno = eno;
end;
-- 使用PLSQL来调用存储过程,由于PLSQL中不需要定义变量declare可以省略。
begin
    pro_addSal(7369,10000);
    commit; --提交操作建议放在调用的时候，可以保证多个操作在一个事务中。
end;