MySQL

写在前面

最早接触的数据库就是MySQL，包括之前写项目的时候基本上用的也是这个，虽然之前学过数据库这门课，但是只停留在用的基础上。这次抽时间又从头理了一下，确实发现之前项目中数据库存在了很多问题，至于数据库中底层的原理，等以后深究数据结构时在进行总结，现在就只对数据库中主要的应用部分进行个梳理，以便日后可以正规地使用数据库。

MySQL综述

1、什么是数据库

定义：数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

作用：保存,并能安全管理数据(如:增删改查等),减少冗余...

数据库分类：

• 关系型数据库（SQL）：
  • MySQL , Oracle , SQL Server , SQLite , DB2 , ...
  • 关系型数据库通过外键关联来建立表与表之间的关系
• 非关系型数据库（NOSQL）：
  • Redis , MongoDB , ...
  • 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

2、MySQL存储引擎

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...

常见的为MyISAM 与 InnoDB 类型

名称	MyISAM	InnoDB
事务处理	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
数据锁	表锁，锁住整个表（不适合高并发场景）	行锁，只锁住某一行的操作（适合高并发场景）
表空间大小	较小	较大（约为2倍）
关注点	性能	事务
缓存	缓存索引，不缓存真实的对象	缓存索引和真实的数据，对内存要求高

适用场合

• MyISAM : 节约空间及相应速度
• InnoDB : 安全性 , 事务处理及多用户操作数据表

数据表的存储位置 :

• MySQL数据表以文件方式存放在磁盘中

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表 .

• 注意 :

  • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
  • MyISAM类型数据表对应三个文件 :
    • * . frm -- 表结构定义文件
    • * . MYD -- 数据文件(data)
    • * . MYI -- 索引文件(index)

3、MySQL事务

什么是事务

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

事务的ACID原则

• 原子性(Atomic)：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节，也不可能只执行对的操作。当事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，之前执行过的操作复原。

• 一致性(Consist)：事务必须始终保持系统整体处于一致的状态，不管在任何给定的时间并发事务有多少。相当于能量守恒定律，无论多个事务怎样操作，系统中的总量和是保持一致的

• 隔离性(Isolated)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

• 持久性(Durable)：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

4、数据库索引

作用 :

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

分类 :

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

索引类型

• hash类型的索引：查询单条快，范围查询慢
• btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

5、数据库设计

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

• 收集信息

  • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.

• 标识实体[Entity]

• • 标识数据库要管理的关键对象或实体,实体一般是名词

• 标识每个实体需要存储的详细信息[Attribute]

• 标识实体之间的关系[Relationship]

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

实例：参考文章

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询