MySql 基本概念

mysql 是常用关系型数据库之一。

事务是数据库独立的单元，满足四个属性 （ACID），分别是

• 原子性：事务是最小的不可分割单元，数据要么全执行，要么全不执行
• 一致性：总是一致地从一个状态转换到另一个状态
• 隔离性：一个事务在改变最终提交之前，对其他事务是不可见的
• 持久性：事务的提交永久地保存到数据库中

在设计数据库的时候，需要通过范式设计正确的数据结构

• 1NF：数据库每一列都是不可分割的基本数据项
• 2NF：在 1NF 基础上，R 中每一个非主属性完全函数依赖于 R 中某个主属性
• 3NF：在 2NF 基础上，每个非主属性都不依赖于 R 的候选键
  • BCNF：在 3NF 基础上，每个主属性都不传递依赖于R 的候选建
• 4NF：多值依赖集合和超键概念，一般不使用。

一般来说，设计表的时候，要考虑减少数据冗余，需要到 BCNF 级别，但是实际为了提高性能，会有反范式的设计。

索引

常见索引分为 B-tree 索引 和 哈希索引 。

• B-tree 索引：大多数Mysql引擎都支持这种索引，InnoDB 使用的 B+Tree 数据结构。索引遵循以下限制
  • 最左前缀开始匹配
  • 顺序列从左到右查询
  • 如果有某个列的范围查询，那么右边的列无法使用索引优化

• 哈希索引：只有 Memory 引擎显式支持哈希索引。InnoDB 有一个特殊功能 自适应哈希索引 ，使用频繁会自动在 B-tree 索引之上建立一个哈希索引。
  • 哈希索引是使用列的全部内容来计算 hash值，非全列查询无法使用
  • 只能等值查询，不能排序
  • hash冲突高会影响性能
• 还有 R-Tree 索引 和 全文索引。

B-tree 索引分为聚簇索引（主键索引）和 非聚簇索引（二级索引），这是一种数据储存方式。

一个表只能有一个聚簇索引，数据行紧凑地储存在一起。一般是主键列，如果没有，会隐式定义一个主键列。

• 聚簇索引对于磁盘 I/O 有优势，但是对内存型数据库引擎没有优势
• 数据访问快，但是更新聚簇索引列代价很高
• 可能存在页分裂的问题

二级索引的叶节点保存的是主键值而不是物理指针，可能会存在两次查找（第二次也叫回表），但是也有 覆盖索引 ，指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

可以通过 explain 关键字来观察sql语句的执行过程，是否使用了索引，来优化。

索引语法

ALTER TABLE student 
ADD UNIQUE INDEX `id_index`(`id`) USING BTREE;
-- student表增加一个主键索引

游标

提供只读，单向的游标，只能在储存过程或更底层的API里面使用。

储存过程

储存过程将一些编译好的SQL语句，存储在数据库中，类似于编程中没有返回值的函数，可以直接调用。

没有网络通讯、解析开销和优化器开销，但是不好维护。

CREATE PROCEDURE delete_match_age (IN match_age INT)
BEGIN
DECLARE student_of_zhang_ids int(11); 
DECLARE student_is_zhang CURSOR 
			FOR SELECT id FROM student where stu_name = 'zhang';

OPEN student_is_zhang;
FETCH student_is_zhang INTO student_of_zhang_ids;

DELETE FROM student
				WHERE age = match_age AND id = student_of_zhang_ids;

CLOSE student_is_zhang;
END
-- 使用游标，选择名字是zhang的列
-- 使用 CALL delete_match_age(15) ，删除名字是zhang ，年龄等于15的列

触发器

触发器同样增加安全性，但是不好维护。

触发器分为事前触发和事后触发，语句触发和行级触发。

CREATE TRIGGER update_count_trigger BEFORE INSERT ON student
BEGIN
UPDATE class SET stu_count = stu_count + 1 WHERE id = 1;
END
-- 对于student的插入操作之后，统计数加一

视图

视图是一个虚拟表，本身不存放数据。

视图可以简化查询语句，提高数据安全性和逻辑性。

可更新视图指可以通过更新这个视图来更新涉及的相关表。

CREATE VIEW age_view AS SELECT student.stu_name,student.age,age_statistics.age_count_number 
    FROM student LEFT JOIN age_statistics ON student.age = age_statistics.age
-- 使用左连接的视图