1. MySQL的存储引擎有哪些,区别是什么?【重点】
MySQL常见的三种存储引擎:InnoDB、MyISAM 和MEMORY。
事务安全:
InnoDB支持事务安全,MyISAM和MEMORY两个不支持。
存储限制:
InnoDB有64TB 的存储限制,MyISAM和MEMORY要是具体情况而定。
空间使用:
InnoDB对空间使用程度较高,MyISAM和MEMORY对空间使用程度较低。
内存使用:
InnoDB和MEMORY对内存使用程度较高,MyISAM对内存使用程度较低。
插入数据的速度:
InnoDB插入数据的速度较低,MyISAM和MEMORY插入数据的速度较高。
对外键的支持:
InnoDB对外键支持情况较好,MyISAM和MEMORY两个不支持外键。
2. 什么是存储过程?用什么来调用?【了解】
存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
调用:
可以用一个命令对象来调用存储过程。
可以供外部程序调用,比如:Java程序。
示例:
--Oracle创建存储过程 CREATE PROCEDURE GET_EMPLOYEE_SALARIES (ID IN NUMBER, SALARY OUT NUMBER) IS BEGIN SELECT SALARY INTO SALARY FROM EMPLOYEE WHERE EMPLOYEE_ID = ID; END; --Oracle执行存储过程 DECLARE EMP_SALARY NUMBER; BEGIN GET_EMPLOYEE_SALARIES (100, EMP_SALARY); DBMS_OUTPUT.PUT_LINE(TO_CHAR(EMP_SALARY)); END; |
3. 存储过程的优缺点?【了解】
优点:
存储过程是预编译过的,执行效率高。
存储过程的代码直接存放于数据库中,通过存储过程名直接调用,减少网络通讯。
安全性高,执行存储过程需要有一定权限的用户。
存储过程可以重复使用,可减少数据库开发人员的工作量。
缺点:移植性差。
4. 触发器的作用?【了解】
触发器是一种特殊的存储过程,主要是通过事件来触发而被执行的。
作用:
强化约束,来维护数据的完整性和一致性;
跟踪数据库内的操作,从而不允许未经许可的更新和变化;
联级操作,如:某表上的触发器上包含对另一个表的数据操作。
Oracle语法:
CREATE [OR REPLACE] TIGGER <触发器名> <触发时间> <触发事件> ON <表名> [FOR EACH ROW] BEGIN --编写PL/SQL语句 END; --语法解释: 触发器名:触发器对象的名称。 触发时间: before:表示在数据库动作之前触发器执行; after:表示在数据库动作之后触发器执行。 触发事件: insert:数据库插入会触发此触发器; update:数据库修改会触发此触发器; delete:数据库删除会触发此触发器。 表名:数据库触发器所在的表。 FOR EACH ROW:对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。 |
示例:
--使用触发器实现序号自增 --创建一个测试表: create table tab_user( id number(11) primary key, username varchar(50), password varchar(50) ); --创建一个序列: create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20; --创建一个触发器: CREATE OR REPLACE TRIGGER MY_TGR BEFORE INSERT ON TAB_USER FOR EACH ROW DECLARE NEXT_ID NUMBER; BEGIN SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL; :NEW.ID := NEXT_ID; END; --:NEW表示新插入的那条记录 |
5. SQL 优化的具体操作?【重点】
尽量避免使用select * from table,返回无用的字段会降低查询效率。
优化方式:使用具体的字段代替*,只返回使用到的字段。
尽量避免使用in和not in,会导致数据库引擎放弃索引进行全表扫描。
优化方式:如果是连续数值,可以用between代替;如果是子查询,可以用exists代替。
尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。
优化方式:尽量在字段后面使用模糊查询。
尽量避免进行null 值的判断,会导致数据库引擎放弃索引进行全表扫描。
优化方式:可以给字段添加默认值0,对0值进行判断。
尽量不用“<>”或者“!=”操作符,不等于操作符是永远不会用到索引的,会进行全表扫描。
优化方式:比如:a<>0 改为 a>=1 or a<=-1。
用“>=”替代“>”。
优化方式:如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时会先找出为2的记录索引再进行比较,而A>=3时则直接找到=3的记录索引。
where后面的条件,表连接语句写在最前,可以过滤掉最大数量记录的条件居后。
在语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间。
进行了显式或隐式的运算的字段不能进行索引。
优化方式:例如age+20>50,优化为age>=31。
用union all代替union,union all操作不排除重复记录行,所以会快很多,如果数据本身重复行存在可能性较小时,用union all会比用union效率高很多!
6. 什么叫视图?游标是什么?【了解】
视图:
是一种虚拟的表,具有和物理表相同的功能。
可以对视图进行增、改、查操作,视图通常是有一个表或者多个表的行或列的子集。
对视图的修改会影响基本表。
相比多表查询,它使得我们获取数据更容易。
游标:
是对查询出来的结果集作为一个单元来有效的处理。
游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。
可以对结果集当前行做修改。
一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。
7. 视图的优缺点?【了解】
优点:
对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。
用户通过简单的查询可以从复杂查询中得到结果。
维护数据的独立性,视图可从多个表检索数据。
对于相同的数据可产生不同的视图。
缺点:
性能:查询视图时,必须把视图的查询转化成对基本表的查询。
如果视图是由一个复杂的多表查询所定义,那么就无法更改数据。
8. 事务的四个特性?【重点】
原子性(Atomicity):
原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。
一致性(Consistency):
事务开始前和结束后,数据库的完整性约束没有被破坏。比如A 向B 转账,不可能A 扣了钱,B 却没收到。
隔离性(Isolation):
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。
持久性(Durability):
持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
9. 数据库乐观锁,悲观锁的区别,应用场景?【重点】
悲观锁(Pessimistic Lock):
顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞挂起直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁,读锁,写锁等, 都是在做操作之前先上锁。
乐观锁(Optimistic Lock):
顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改数据,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,乐观锁适用于多读的应用类型,这样可以提高吞吐量。
两种锁各有优缺点,乐观锁适用于写比较少的情况下,即冲突真的很少发生的时候,这样就可以省去锁的开销,加大系统的整个吞吐量。但如果经常产生冲突,上层应用会不断的进行重试,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适。