数据库加强

01.视图

什么是视图

视图是逻辑上来自一个或多个表的数据集合。或者说：视图是经过定制的方式显示来自一张或多张表中的数据。
视图也可以看做是“虚拟表”，或者“存储的查询”。
1.2.为什么要使用视图

为什么要使用视图：

● 视图可以限制其它用户对数据库表的访问，因为视图可以有选择性的显示数据库表的一部分；
● 视图容易实现复杂的查询；
● 对于相同的数据可以产生不同的视图；

视图的应用场合：

● 比如根据需求要查询某张表或某几张表中的某些字段数据。
● 比如根据不同的权限让不同的用户查询不同的数据等等。

视图的特点

1. 创建视图所依据的表叫做“基表”。
2. 基表可以是一个，也可以是多个。
3. 在视图上也可以修改数据。（基表有多个时不能修改）
4. 如果修改违反了约束，也不能修改。
5. 视图的优点：安全性、隐藏复杂性、简化sql命令。

视图语法

创建视图

创建视图的语法：

create view 视图名 as 查询语句;

实例：创建一个视图v_emp10，通过该视图只能查看10号部门的员工编号，员工姓名，职位。

create view v_emp10
as select * from emp where deptno = 10

02.索引

索引概述

什么是索引

在关系数据库中，索引是一种对数据库表中一列或多列的值进行排序的一种存储结构，用于快速找出在某个列中有一特定值的行。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引的优点与缺点：

优点：
所有的MySql字段类型都可以被索引，也就是可以给任意字段设置索引。
索引可以大大加快数据的查询速度。
缺点：
创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引也需要占空间，如果存在大量索引，索引文件可能会比数据文件更快达到上限值。
当对表中的数据进行增删除改时，索引也需要动态的维护，降低了数据的维护速度。
常用索引类型有：普通索引、唯一索引、主键索引、全文索引、空间索引等。

索引使用原则

并不是每个字段都设置索引就好，也不是索引越多越好，而是需要合理的使用。
对经常更新的表就避免对其进行过多的索引。
对经常用于查询的字段应该创建索引。
数据量小的表最好不要使用索引。
相同值较多的字段上不要建立索引（比如"性别"字段）。相反的，在不同值较多的字段上可以建立索引。

索引类型

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：
普通索引（索引名：index）
普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
唯一索引（索引名：unique）
顾名思义，唯一索引就是专门为具有唯一约束字段创建的索引。当一个字段被设置唯一约束时，MySql会自动为此字段创建唯一索引。唯一索引列的值必须唯一，但允许有空值。
主键索引（索引名：primary key）
顾名思义，主键索引就是专门为主键字段创建的索引。当一个字段被设置为主键时，MySql会自动为此字段创建主键索引。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
全文索引（索引名：fulltext）
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
空间索引（索引名：spatial）
空间索引是对地理空间数据类型 GEOMETRY的字段建立的索引。创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。对于初学者来说，这类索引很少会用到。

索引语法

创建索引

创建索引语法：

create 索引类型 索引名 on 表名(列名);

实例：给emp表中的ename创建普通索引：

create index ename_index on emp(ename);``

删除索引

删除索引语法：

drop index 索引名 ON 表名;

实例：删除emp表中的ename字段上的普通索引：

drop index ename_index on emp;

查看索引

查看一个表中的索引：

show index from emp;    -- emp是表名

03.存储过程

什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程的优缺点

优点：
● 存储过程在服务器端运行，执行速度快。
● 存储过程执行一次后，经过第一次编译后就不需要再次编译，提高了系统性能。
● 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限

缺点：
● 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
● 存储过程的性能调校与撰写，受限于各种数据库系统。

存储过程语法

创建存储过程

创建存储过程语法：

create procedure 存储过程名(参数列表)
begin
    -- 过程体
end;

实例：根据部门编号，返回此部门所有员工的总工资。

drop procedure if exists getsalbydeptno;
create procedure getsalbydeptno(in dno int,out salsum decimal(7,2))
begin
    select sum(sal) into salsum from emp where deptno=dno;
end;

1. 存储过程参数有三种模式：in，out和inout。
   ○ in：传入参数（默认）
   ○ out：传出参数
   ○ inout：此参数是in和out参数的组合。这意味着存储过程可以修改此参数并将新值传递回调用程序。
2. 注意：参数名不能与字段名重名。
3. 在select语句中，使用into关键词给传出参数赋值。

调用存储过程

调用存储过程语法：

call getsalbydeptno(10,@salsum); 
select @salsum;

1. 使用call关键词调用存储过程。
2. 传出参数使用 @salsum 的形式声明。

删除存储过程

删除存储过程语法：

drop procedure [if exists] 存储过程名;

删除getsalbydeptno存储过程：

drop procedure if exists getsalbydeptno;

查看存储过程

查看数据库中的所有存储过程：

show procedure status;

04.触发器

触发器概述

触发器是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。
触发器类型有：insert触发器、delete触发器、update触发器。

触发器语法

创建触发器

创建触发器语法：

create trigger 触发器名 触发时机 触发事件 on 表 for each row
begin
    -- 触发器业务代码
end;

● 触发事件：insert、update、delete
● 触发时机：before（触发事件之前）、after（触发事件之后）
● for each row：对于其中的每个数据行，只要符合触发条件，均激活一次，也就是行级触发器。（oracle触发器中分行级触发器和语句级触发器，可不写for each row，无论影响多少行都只执行一次。但mysql不支持语句触发器,所以必须写for each row）
实例：使用触发器实现dept表与emp表的级联删除（即：当删除dept表中的一条记录时，同时将emp表中的相关记录一起删除）。

drop trigger if exists dept_del;
create trigger dept_del after delete on dept for each row
begin
    delete from emp where deptno=old.deptno;
end;

● new.deptno：向dept表中插入记录时的主键。
● old.deptno：删除dept表中记录时的主键。
● 注意：如果dept表与emp表建立了主外键约束，那么触发时机应为 before。

删除触发器

删除触发器语法：

drop trigger 触发器名;

删除dept_del触发器：

drop trigger dept_del;

查看触发器

查看数据库中的所有触发器：

show triggers;

05.数据库优化

MySql数据库优化总结

数据库设计优化

适度的违反范式
在开发应用程序时，数据库设计要最大程度的遵守三范式。当 然，三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式。也就是适度的做冗余，以达到提高查询效率的目的。注意这里的反范式是适度的。
适当建立索引
索引可以有效的提高查询速度，但这个提高是以插入、更新、删除的速度为代价的。由于索引的存储结构不同于表的存储，一个表的索引所占空间比可能数据所占空间还大。这意味着我们在写数据库的时候做了很多额外的工作，而这个工作只是为了提高读的效率。因此，我们建立一个索引，必须保证这个索引不会“亏本”。一般需要遵守这样的规则：
对经常更新的表就避免对其进行过多的索引。
对经常用于查询的字段应该创建索引。
数据量小的表最好不要使用索引。
相同值较多的字段上不要建立索引（比如"性别"字段）。相反的，在不同值较多的字段上可以建立索引。
对表进行水平划分
如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。
比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。
对表进行垂直划分
有些表记录数并不多，但是字段却很多，导致表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。解决方案是：可以把一部分字段拆分到另一个表，并且该表与原表是一对一的关系。
选择合适的引擎
在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎。比如：
InnoDB存储引擎支持外键、支持事务、支持全文检索；
MyISAM存储引擎可以被压缩、不支持事务、不支持外键；如果需要执行大量的select语句，MyISAM存储引擎是更好的选择。
MEMORY存储引擎主要用于那些内容稳定的表，或者作为统计操作的中间表。

SQL语句优化

尽量使用批量操作
尽量使用MySql中的批量操作语句，这样可以避免频繁读写操作。比如：批量插入、批量更新、批量删除。

/* 使用多个insert语句效率低 */
insert into dept values(null,'技术部','沈阳市')；
insert into dept values(null,'销售部','上海市')；
insert into dept values(null,'总部','北京市')；

/* 使用批量insert语句效率高 */
insert into dept values(null,'技术部','沈阳市'),(null,'销售部','上海市'),(null,'总部','北京市');

2. 选择适当的数据类型
   选择字段的数据类型的一般原则是尽量使用占用字节小的数据类型。
   比如主键， 强烈建议用自增类型，既节省空间，又能满足大多数需求。
3. 文件、图片等大文件用文件系统存储，不要使用数据库
   数据库只存储文件路径。这是一个基本原则！
4. 使用连接（JOIN）来代替子查询(Sub-Queries)
   MySQL从4.1开始支持SQL的子查询。但是，子查询可以被更有效率的连接替代。连接之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成需要多个步骤才能完成的子查询。

/* 查询部门地址在"NEW YORK"的所有员工 */

/* 子查询方式效率低 */
select * from emp where deptno in(select deptno from dept where loc='NEW YORK')

/* 多表连接方式效率高 */
select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='NEW YORK'

5. 对查询语句进行优化
   绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。
   ○ 首先，最好是在相同类型的字段间进行比较的操作，可以避免转型的步骤。
   ○ 其次，在建有索引的字段上尽量不要使用函数进行操作。使用函数后索引将失效。
   ○ 在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
   ○ 应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

数据库参数配置优化

MySql安装之后，默认参数并不能满足所有生产场合下的需求。所以，还需要根据需求，修改Mysql服务器的系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。比如：
● wait_timeout：MySQL客户端的数据库连接闲置最大时间值。
● max_connections：MySQL的最大连接数。
● max_user_connections：每个数据库用户的最大连接。
● thread_concurrency：线程最大并发数。
● default-storage-engine：设置MySQL默认存储引擎

-- 查询MySQL的最大连接数。
show global variables like '%max_connections%';
-- 设置MySQL的最大连接数。
set global max_connections=1024;

硬件和系统的优化

1. 合理采用操作系统
   如果服务器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql
2. 读写分离
   如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
   比如：主库用来写入，从库都用来做select，那么每个数据库分担的压力小了很多。当然，要实现这种方式，需要程序特别设计，给程序开发带来了额外负担。不过现在已经有中间件来实现这些功能。