数据库知识网

补充：

1.表结构设计（常用三范式）：

表结构设计

• 范式设计，消除冗余

  数据库范式是确保数据库结构合理，满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表，称为规范化表，范式产生于20世纪70年代初，一般表设计满足前三范式就可以，在这里简单介绍一下前三范式：

　　第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割； 　　

        第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识。 　　

        第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

• 反范式设计，适当冗余

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点，满足范式的表一定是规范化的表，但不一定是最佳的设计。

2.索引

2.1 什么是索引

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

2.2 索引的分类

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引：一个索引包括多个列

2.3 索引的优势

1）提高数据检索效率，降低磁盘IO成本

2）通过对数据的排序，降低排序成本

2.4. 索引的劣势

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

2.5 什么情况下适合建立索引

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

2.6  什么情况下不适合建立索引

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

2.7 什么时候会出现索引失效

1）like以通配符开头('%abc')会导致索引失效，违反最左前缀法则

最左前缀法则：

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

3.存储过程（数据库 SQL 语言层面的代码封装与重用）

3.1 什么是存储过程？

由MySQL5.0 版本开始支持存储过程。

如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

2.存储过程的基本使用

• 格式

-- 创建存储过程
delimiter 自定义结束符号  ||  $$
create procedure `数据库名`.`存储过程名`([in , out , inout] 参数名 数据类型。。。)
begin
    sql语句
    //select name from emp where id = ?;
end 自定义结束符号  || $$ 
delimiter ;
-- 调用存储过程
call 存储过程名;

4. 触发器

• 概念：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。注意，不能同时在一个表上建立两个形同类型的触发器，因此在一个表上最多可以建立6个触发器。

• 作用：

触发器经常用于加强数据的完整性约束和业务规则等;

可在写入数据前，强制检验或者转换数据(保证安全性);

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

• 语法

CREATE TRIGGER trigger_name trigger_time before trigger_event insert
    ON tbl_name FOR EACH ROW trigger_stmt
    
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·         INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·         UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
·         DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
tbl_name：表示建立触发器的表名，即在哪张表上建立触发器。
trigger_stmt：表示触发器程序体，可以是一条SQL语句，也可以是BEGIN和END包含的多条语句。即BEFORT INSERT、BEFORT UPDATE、BEFORT DELETE、AFTER INSERT 、AFTER UPDATE、AFTER DELETE。

5.锁

MySQL的InnoDB锁机制分为表级锁和行级锁

① 行级锁 行级锁中有共享锁和排它锁。

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

select * from t_user where id =10 lock in share mode;

排他锁又称为写锁(独占锁)，简称X锁，顾名思义，排他锁就是不能与其他锁并存。

如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，

包括共享锁和排他锁。

select * from t_user where id =10 for update;

MySQL InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

实例如下：

select * from t_user where id=10 for update; # 错误
select * from t_user where id=10 lock in share mode; # 错误
select * from t_user where id=1 # 正常获取数据

②InnoDB 中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

  意向锁是 InnoDB 自动加的，不需要用户干预。

  再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;

③优劣

表锁的优势：开销小；加锁快；无死锁 表锁的劣势：锁粒度大，并发处理能力低

行锁的劣势：开销大；加锁慢；会出现死锁 行锁的优势：锁的粒度小，处理并发的能力强