SQL优化：
sql优化 首先得晓得是个啥 做它的目的 是为了解决什么？
sql优化总来说就是: 1、缩短响应时间; 2、提升系统吞吐量; 3、提升系统负载能力。要使用多种手段,在提升系统吞吐量和增加系统负载能力,提高单个SQL效率之间寻求一种平衡。就是要尽量减少一条SQL需要访问的资源总量,比如走索引更好,那么不要使用全表扫描。 其中优化格式为 t=s/v 执行效率或者一般说的执行时间,是和完成一次SQL所需要访问的资源总量 (S)成正比以及单位时间内能够访问的资源量(V)成反比, S越大,效率越低, V越大效率越高。比如通过并行查询,则可以提升单位时间内访问的资源星。
SQL的存储过程
一组为了完成特定功能的SQL 语句集， 可以实现一些比较复杂的逻辑功能，存储在数据库中，经过第一次编译后再次调用不需要再次编译 ，存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用。
mysq 跟 Oracle
Mysql：
DELIMITER ;;
CREATE DEFINER=root@http://localhost PROCEDURE proc_if(IN type int)
BEGIN
#Routine body goes here…
DECLARE c varchar(500);
IF type = 0 THEN
set c = ‘param is 0’;
ELSEIF type = 1 THEN
set c = ‘param is 1’;
ELSE
set c = ‘param is others, not 0 or 1’;
END IF;
select c;
END
;;
DELIMITER ;
Oracle：
create [or replace] procedure 过程名 [(参数名 in|out 数据类型)]
as|is
变量的声明
begin
plsql的子程序体;
end;//如果是is，end 后面要加上过程名。
面试中可做到随意写一个存储过程即可
SQL优化方法
1.应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断
否则将导致引擎放弃使用索引而进行全表扫描
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件
7.应尽量避免在 where 子句中对字段进行表达式操作，
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
10.选取最适用的字段属性
11.事务 :要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束
11.锁定表
优化中必问索引
（1）B-Tree索引
（2）哈希索引
（3）空间数据索引（R-Tree）
（4）全文索引
（5）其他索引类别
与B-Tree索引相关 重点 用自己的话明白
B-Tree索引是最常见的索引类型，它使用B-Tree数据结构来存储数据，大多数MySQL引擎都支持这种索引。（Archive引擎是一个例外：5.1之前Archive不支持任何索引，直到5.1才开始支持单个自增列AUTO_INCREMENT的索引。）
“ ” 比如有500w数据 正常情况是一条一条的去查询 然后查到500条效率极低
B-Tree 的效率是远高于正常的
问 如果我们用2倍查询一千万条数据从中间分开 他的树是比较深的 一般是小的放左边 大的放右边 这个时候如果深度过深 的时候他可能也要查询500w次 那b+tree他做到什么优化吗
创建索引的时候尽量使用唯一性大的列来创建索引 ，同时让一个节点能存放更多的值，索引列尽量在整数类型上创建，如果必须使用字符类型，也应该使用长度较少的字符类型
太难 可以去多多了解底层
Myisam跟InnoDB的区别
Myisam 主要做一些读操作 那种读操作比较多的 它不需要保证数据的安全 并且不支持事务
InnoDB： 支持事务所以在选择的时候一般选择InnoDB 特殊情况选择Myisam 并且在安装数据库的时候默认选择的也是InnoDB
Innodb的行锁模式有以下几种：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁。
注意：当语句没有使用索引，innodb不能确定操作的行，这个时候就使用的意向锁，也就是表锁
关于死锁：
什么是死锁？当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。
解决死锁的方法：
1、 数据库参数
2、 应用中尽量约定程序读取表的顺序一样
3、 应用中处理一个表时，尽量对处理的顺序排序
4、 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）
Myisam跟InnoDB在索引上面的区别
MyISM和InnoDB索引都是由B+树实现的，但在索引管理数据方式上却有所不同
InnoDB是聚集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据 = 整个表数据文件
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
在底层可以自己去 推荐一个超 牛逼的的开源网站
链接:牛逼的的开源网站
触发器：
对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程
常见的触发器有三种：分别应用于Insert , Update , Delete
举例子 张三认识的朋友 改了张三 他的朋友也会跟着改变
触发器。对于1，创建一个Update触发器：
Create Trigger truStudent
On Student --在Student表中创建触发器
for Update --为什么事件触发
As --事件触发后所要做的事情
if Update(StudentID)
begin
Update BorrowRecord
Set StudentID=i.StudentID
From BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted临时表
Where br.StudentID=d.StudentID
end
Oracle中的一个小知识点
如果不用distinct Oracle去重
用distinct关键字只能过滤查询字段中所以纪录相同的 是可以消除重复的记录 但是这个只能用一个字段 如果 其他字段没有用上 它也不会报错 但是会没有效果
group by 就可以完美的解决这个问题
其中
在同一个表的 同样的数据下
select name count() from tab_user group by name having count()=1
select distinct name from tab_user
Oracle 查重 大佬指导的查重
with temp as(
select ‘1’ as ab from dual union all
select ‘1’ as ab from dual union all
select ‘1’ as ab from dual union all
select ‘2’ as ab from dual )
select ab,count(ab) from temp group by ab having count(ab) > 1
下面 说几个面试总整理的面试题
问：请说出SQL执行顺序
from
(2) join
(3) on
(4) where
(5)group by
(6) avg,sum… （组函数）
(7)having
(8) select
(9) distinct
(10) order by
问 ：假设我有一个小表 一个大表 连接查询的时候 是小表驱动大表 还是大表驱动小表
大表驱动小表(从表驱动主表,外键表驱动主键表)更快。主表驱动从表应该在从表的外键上建立索引。说那么多，实际情况下有可能不是主键关联,没有建立索引等等
explain 必懂
1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在 不损失精确性的情况下，长度越短越好
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
需动手 便可明白
问：如何保证在一个事务中，同时插入数据，且保证两个表插入成功
TransactionAspectSupport.currentTransactionStatus()获得当前事务，实现回滚
下面有2个sql 可自我理解
--查询没学过“湛蓝”老师课的同学的学号、姓名
select * from student s1 where s1.sno not in(
---对于她学生的学号可以是显示出你想要的数据，相等还是不等的
select distinct s2.sno from sc s2 where s2.cno
---提取出她所教的学生的学号，以从之前的查询里提取出来的工号
in (select c.cno from course c where c.tno =
---提取出她所教的课程，以他的工号来相等出来
(select tno from teacher where tname = '谌燕' )))
--最里面的提取出她的姓名
查询出“计算机系“教师所教课程的成绩表。
select s1.degree,c.cname
from scores s1 join students s2 on s1.sno=s2.sno
join courses c on s1.cno=c.cno
join teachers t on c.tno=t.tno
where depart = '计算机系';
扫一扫
225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
