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

时间:2021-07-27 13:51:48

一.索引

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

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

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

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

1.创建索引:

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

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

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

有这样一张表:

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

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

 

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

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

CREATE INDEX index_pname ON t_product(productName)

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

效果如下图

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

 

 

 

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. 减慢增删改数据的速度
  2. 占用磁盘空间
  3. 增加查询优化器负担

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

 

 

二.视图

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

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

  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上建了索引页会查询效率起不了太大作用

 

 

 

 

四、数据库存储过程

什么是存储过程?

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

存储过程的优点

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

l  存储过程的缺点

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

 

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')