索引、视图、SQL优化以及数据库存储过程

一.索引

索引是查询优化最有效和最常用的技术

索引是对数据库表中一个列或多个列进行排序的结构。

索引是一个单独的、物理的数据库结构，它是指向表中某一列或若干列上的指针列表。

mysql中，一个表的物理存储由两部分组成，一部分用于存放表的数据，另一部分存放索引，当进行数据搜索时，mysql会首先搜索索引，从中找到所需数据的起始位置的指针，再直接通过指针查找目标数据。

1.创建索引:

CREATE INDEX 索引名 on 表名(要添加索引的列名)

可以给一个表中的多个列添加索引

通过在查询sql语句前加一句Explain可以分析索引效率，

有这样一张表：

执行EXPLAIN SELECT*FROM t_product WHERE productName ='电视机'

同一条sql查询语句，在没建立索引时，可以看到扫描了9行

CREATE INDEX index_pname ON t_product(productName)

在建立索引之后，再次执行相同查询语句，发现只扫描了1行便得到了结果

效果如下图

2.删除索引:

Alter table 表名 drop index 索引名

如何选择索引列：

　 1.Where子句中常出现的列

select id from t_student where sName=’张三’

　 2.在join子句中常出现的列

select s.*,g.grade from t_student s join t_grade g on s.id=g.studentId

　 3.频繁进行排序或者分组的列

select s.*,g.grade from t_student s join t_grade g on s.id=g.studentId group by s.name order by g.grade

like使用索引列，like比较特别，mysql对其索引的情况是：操作数不以通配符开头。

select*from t_student where sName like ‘j%’ 会使用索引

select*from t_student where sName like ‘%j%’ 不会使用索引,仍会全表查询

索引的缺点：

减慢增删改数据的速度
占用磁盘空间
增加查询优化器负担

不能简单的认为“索引越多，性能越高”，不必对每个数据列都进行索引。如果很少使用或从不使用某个索引，建议删除该索引。

索引-我的理解：索引就是给数据库表排个序号，这样你找的时候直接喊号就行，不用一个一个再去找；

二．视图

视图是数据库中由一个或多个基本表导出的虚拟表。

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。　

　　1.创建视图

CREATE VIEW v_find AS SELECT id,productName,price,imgPath FROM t_product;

　　2.使用视图

直接从视图中查询

SELECT*FROM v_find;

对于复杂的连表查询，可以利用创建视图来使SQL语句变得简单。

CREATE VIEW v_join AS SELECT r.address,r.price,m.manName FROM test.t_man m,test.t_room r WHERE r.manId=m.id;

SELECT*FROM v_join where manName=’张三

　　3.删除视图

DROP VIEW v_find

注意:视图只适用于查询，它只是一张虚拟表！如果需要添加数据，那么必须加在真实表中。

视图-我的理解：使用视图就是只看你想看到的，不用管别不相关的字段，便捷操作，安全高效；

三.SQL语句优化策略

1．给适当的列加上索引。

2．尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

3．避免select *

从数据库里读出越多的数据，那么查询就会变得越慢

4．永远为每张表设置一个ID

使用VARCHAR类型来当主键会使得性能下降

5．使用EMUM而不是VARCHAR

sex ENUM(‘’男,’女’)

如果一个列只含有有限树木的特定值，比如：性别、状态等，尽量采用ENUM列举出所有可能的取值作为数据类型，enum列的值都是以标识数值标识，mysql会处理的更快。

6．尽量避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

7．模糊查询不要以通配符开始，否则会导致索引失效而进行全表扫描

select *from t_student where sName like ‘a%’

8．尽量避免在where子句中对字段进行表达式操作，这会导致引擎失效放弃使用索引而进行全表扫描。

select id from t where num/2=100

应该改为 select id from t where num=100*2

9. In和not in要慎用，否则可以导致全表扫描，可以用exists代替In。

Select num from a where num in(select num from b)

用下面的语句替换

Select num from a where exists(select 1 from b where num=a.num)

10.一个表的索引最好不要超过6个，若太多则应考虑删除一些不常使用的索引

11.尽量避免大事务操作，提高系统并发能力

12．并不是所有索引对查询都有效，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如果一表中有字段sex,male,female几乎各一半，那么即使在sex上建了索引页会查询效率起不了太大作用

四、数据库存储过程

l 什么是存储过程？

存储过程是一段写好的SQL代码，它是存在数据库的目录中，外部程序可以直接调用数据库里面定义好的存储过程。

l 存储过程的优点

性能上的提高，比起通过应用程序发送SQL语句给数据库执行，让数据库自己内部执行存储过程效率更高、速度更快。
减少了应用程序与数据库信息的交互频率。在一些业务中，应用程序发送多条SQL指令给服务器。而使用存储过程则只需要一条调用存储过程的语句，然后获取需要的数据就可以了。
存储过程重用性比较高，保存在数据库里面所以对任何应来说都可以使用。
存储过程是一种安全的做法，数据库管理员可以对那些没有权限访问数据库中的表格的应用，给他们使用存储过程的权限来获得数据服务。

l 存储过程的缺点

存储过程会使得数据库占用的系统资源加大。
因为存储过程依旧是sql，没办法像编程语言那样写出复杂业务逻辑对应的存储过程。
存储过程不容易进行调试
存储过程是写及维护难度都比较大。

存储过程-我的理解：存储过程就是把一堆sql代码封装起来，给个接口，增加复用性；

l 存储过程语法

#创建存储过程

DELIMITER//

CREATE PROCEDURE p_find()

BEGIN

SELECT* FROM test.t_man;

END;

//

#使用存储过程

CALL P_find();

#删除存储过程

DROP PROCEDURE p_find;

l 存储过程参数

#IN表示输入参数，OUT表示输出参数

DELIMITER//

CREATE PROCEDURE pro(IN mname VARCHAR(20),OUT jname VARCHAR(20))

BEGIN

SELECT *FROM test.t_man where manName=mname;

END;

//

CALL pro(‘张三’,@j);

SELECT @j;

l 存储过程定义变量

DECLARE t INT DEFAULT 0;

SET t=10;

SET t=t+1;

注意:存储过程只能在存储过程的开始定义

实例：

#在exercise数据库中有t_man表，包含字段员工名称，员工职位名称。输入一个公民姓名，根据职位名称，判断出所属等级（表中没有等级）

DELIMITER//

CREATE PROCEDURE p_get(IN mname VARCHAR(20),OUT info VARCHAR(20))

BEGIN

#定义变量

DECLARE mjob VARCHAR(20) DEFAULT '';

#查询指定姓名的员工的职务，并将职务赋值给mjob变量

SELECT job INTO mjob FROM exercise.t_man WHERE manName=mname;

IF mjob='经理' OR mjob='助理' THEN

SET info='高管';

ELSEIF mjob='会计' OR mjob='文员' THEN

SET info ='行政人员';

ELSE

SET info='办事人员';

END IF;

END

//

#两句可以一起执行，但是中间必须用”;”隔开

CALL p_get('张三丰',@m);

SELECT @m;

#动态条件查询

DELIMITER//

CREATE PROCEDURE p_dyna(IN mname VARCHAR(20),IN startDate DATE,IN endDate DATE)

BEGIN

DECLARE msql VARCHAR(200) DEFAULT 'select *from exercise.t_man where 1=1 ';

IF mname IS NOT NULL AND mname !='' THEN

SET msql=CONCAT(msql,"and manName like '",mname,"%' ");

END IF;

IF startDate IS NOT NULL THEN

SET msql=CONCAT(msql,"and birthday>='",startDate,"' ");

END IF;

IF endDate IS NOT NULL THEN

SET msql=CONCAT(msql,"and birthday<='",endDate,"'");

END IF;

#执行SQL语句，首先建一个全局变量

SET @ms=msql;

PREPARE st FROM @ms;

EXECUTE st;

DEALLOCATE PREPARE st;

END;

//

CALL p_dyna('张','1980-01-01','2016-01-01')

*****************************************************************

.事务处理
(1).什么是事务？
事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据更改均被清除。事务作为一个逻辑工作单元有4个属性，称为ACID（原子性、一致性、隔离性和持久性）属性。
<1>.原子性：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。
<2>.一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事物结束时，所有的内容数据结果都必须是正确的。
<3>.隔离性：由并发事务所作的修改必须与任何其他并发事务所作的修改隔离，保证事务查看数据时数据处于的状态，只能是另一并发事务修改它之前的状态或者是另一事务修改它之后的状态，而不能中间状态的数据。
<4>.持久性：事务完成之后对系统的影响是永久性的。
(2).事务分类
<1>.显式事务:用begin transaction明确指定事务的开始。
<2>.隐性事务：打开隐性事务：set implicit_transactions on，当以隐性事务模式操作时，SQL Servler将在提交或回滚事务后自动启动新事务。无法描述事务的开始，只需要提交或回滚事务。
<3>.自动提交事务：SQL Server的默认模式，它将每条单独的T-SQL语句视为一个事务。如果成功执行，则自动提交，否则回滚。
事务-我的理解：事务是规定一系列操作为一个单元模块，要不都执行，要不都不能执行，提高数据可靠性；