0.学习资料

参考书籍《深入浅出MySQL数据库开发、优化与管理维护》
参考视频《传智播客MySQL》

1.索引简介：

几乎所有的索引都是建立在字段之上的。

  1. 索引：系统根据已有的某种算法，将已有的数据（未来可能新增的数据）
    ，单独建立一个文件，该文件能够实现快速的匹配数据，并能够快速的找到对应表中的记录。
  2. 索引的意义：
       a.提升查询数据的效率
       b.约束数据的有效性
  3. 增加索引的副作用：
    增加索引的前提条件：索引本身会产生索引文件（有时候可能比数据还大），非常消耗磁盘空间（空间换效率）
    什么时候增加索引：
    如果某个字段需要作为查询的条件经常使用，可以使用索引（一般都会加）
    如果某个需要进行数据的有效性约束，也可能使用索引（唯一键，主键等）。
  4. Mysql中的索引：
    主键索引：primary key
    唯一索引：unique key
    全文索引：fulltest key （最复杂）
    普通索引：index
  5. 全文索引：
    对文章内部的关键字进行索引。
    全文索引最大的问题在于如何确定关键字：
    英文很容易：单词与单词之间有空格
    中文很麻烦：没有空格，而且中文可以各种随意组合（分词：sphinx）

2.关系

实体内部的关系，实体与实体之间的关系。实体与实体的关系分为3种：一对一，一对多（多对一），多对一。
所有的关系都是指表与表之间的关系：

  1. 一对一
    一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。
    如学生与身份证。
    例子：有一个员工表（id，姓名，性别，年龄，体重，身高，婚姻状况，籍贯，住址，联系人）
    （id，姓名，性别，年龄，体重，身高）是常用数据，（婚姻状况，籍贯，住址，联系人）是不常用数据。—-将常用信息和不常用信息分为两张表,要保证不常用数据和常用数据能够一一匹配，找一个具有唯一性的字段来共同管理连接两张表。
  2. 一对多
    一张表中有一条记录可以对应另外一张表中的多条记录，但是反过来另外一张表的一（这几）条记录只能对应这张表的一条记录（一对多或者多对一）
    如学生与银行卡。一个学生多张银行卡，一张银行卡只属于一个学生。
    学生表：（**学生id**，姓名，学号，性别，年龄...）
银行卡：（卡id，**学生id**，卡号，银行类型...）
    在银行卡中添加一个id唯一约束。一定要在多对一的多表中加，否则会很麻烦。
  3. 多对多
    一张表A的一条记录能够对应一张表B的多条记录，同时，
    另外一张表B中的一条记录也能对应这张表A中的多条记录。
    如老师和学生：一个老师教过多个学生，一个学生也被多个老师教过。
    学生：（stu_id,学号，姓名，性别，班级...）
老师：（tea_id,编号，学科，姓名...）
    在学生表加老师id或者在老师表加学生id都要保存多个与其他表有关的数据，不符合设计规范。
    解决方案：增加一张表专门维护两表之间的关系。
    中间关系表：（[id],stu_id,tea_id）
    增加中间表后学生对中间表，老师对中间表都是一对多的关系。
    老师->中间表->学生，反过来也一样。
    商城系统中的订单表就是一个中间表。

3.范式（很重要）

  1. 范式：Normal Format，普通的格式，是一种离散数学中的知识，是为了解决数据存储与优化的问题，保证数据存储之后，凡是能通过关系寻找出来的数据坚决不重复存储，终极目标是为了减少数据冗余。
    范式是一种分层结构的规范，分为六层：每一层都比上一层更麻烦，若要满足下一层范式必须满足上一层范式。
    六层范式：1NF,2NF,3NF…6NF。1NF最底层，6NF最严格。
    Mysql属于关系型数据库，有空间浪费，也是致力于节省空间，与范式解决的问题相同，设计数据库时会利用范式来指导设计。但是数据又要解决效率问题，所以一般只用前三个范式：1NF,2NF,3NF
    范式在数据库的设计中有指导意义，但是不是强制规范的。
  2. 第一范式：1NF
    在设计表存储数据时，表中设计的字段存储的数据，在读取出来使用之前还需要额外的处理(拆分)，那么说明表的设计不满足第一范式。
    第一范式要求表的设计具有原子性（不可再分）。
  3. 第二范式：2NF
    前提：满足第一范式
    在数据表设计的过程中如果有复合主键（多字段主键），且表中有字段不是由整个主键来确定，而是依赖主键中的部分字段的问题，成为部分依赖。
    第二范式不允许部分依赖，必须完全依赖主键。
    例子：

    解决方案1：可以将性别讲师单独成表，班级与教师单独成表。
    解决方案2：取消复合主键，使用逻辑主键(id)。
  4. 第三范式：3NF
    前提：满足第二范式
    第三范式：理论上将一张表中的所有字段都应该直接依赖主键（逻辑主键代表的是业务主键）。
    如果表设计中存在一个字段，并不是直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键，这种依赖关系是传递依赖。
    第三范式不允许出现传递依赖。

    上述表中id是代表的业务主键是讲师+班级，所以出现传递依赖。
    解决方案将讲师和教室分开存表,并在讲师带课表中增加讲师id与班级id：

    因为两个表的逻辑id代表着不同的业务，所以不出现传递依赖。
  5. 逆规范化（反范式）
    有时候在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以得到想要的数据信息，但是效率会很低，有时候我们会刻意的在这些表中不去保存另外表的主键（逻辑主键），而是直接保存想要的数据信息，这样在查询数据的时候一张表可以直接提供数据，而不需要额外的查询（多表查询，效率低），但是会导致数据冗余。
    如：商品表设计的时候额外添加一个商家用户名的字段，不点商家详情时就不用额外查询整个商家信息。
    逆规范化其实是效率和磁盘利用率的对抗。

4.算术运算符与比较运算符

1)算术运算符：
Mysql支持的算术运算符包括+，-，*，/和模运算。+-*就是这样写。
除：/，DIV(A,B)   – 返回商
模：%，MOD(A,B)   – 返回余数
简单例子：

除运算与模运算时除数为0返回值为NULL。
2)比较运算符：
常用于select查询的条件判断。

运算符作用

= 等于

<>或！=不等于

<=>Null安全的等于

<,<=,>,>=小于（等于），大于（等于）

BETWEEN..AND..存在于指定范围内

IN存在于指定集合

IS NULL是NULL

IS NOT NULL不为NULL

LIKE通配符匹配

REGEXP或RLIKE正则表达式匹配

  1. =，<>，！=，<=>的测试：
  2. 测试BETWEEN和IN：
  3. 测试LIKE与REGEXP:
    REGEXP的使用格式为str REGEXP str_pat。

5.逻辑运算符与位运算符

1)逻辑运算符：
返回的是布尔值

运算符作用

NOT或!逻辑非

AND或&&逻辑且，全1为1

OR或||逻辑或，有1为1

XOR逻辑异或，相同为0，否则为1

简单测试（NULL的情况）：

2)位运算符：
对二进制位的操作。

运算符作用

&按位与(位AND）

|按位非

^按位异或

~按位取反

>>按位右移

<<按位左移

简单测试：

3)运算符的优先级：

6.存储引擎(表类型)简介

1）存储引擎简介及查看：

  1. 插件式存储引擎是Mysql的重要特性之一，针对不同的需求可以选择最优的存储引擎。
    MySQL5.0支持的存储引擎有MyISAM，InnoDB，BDB，MEMORY，MERGE，EXAMPLE，NDB Cluster，ARCHIVE，CVS，BLACKHOLE，FEDERATED等，其中InnoDB和BDB提供了事务安全表，其他存储器是非事务安全表。
  2. MySQL5.5之前默认存储引擎是MyISAM,5.5之后改为了InnoDB。
    （登录后使用status;可查看数据库版本）
    也可以使用show variables like 'table_type';查看当前默认的存储引擎。（可以在参数文件中设置default-table-type的值）
  3. 查看数据库支持的存储引擎的两种方式：
    • 方式1：SHOW ENGINES \G
    • 方式2：SHOW VARIABLES LIKE 'have%';
  4. 上述方法查看的存储引擎若显示DISABLED说明支持但是数据库启动时被禁用。

2)创建表时指定存储引擎：
不指定则使用默认存储引擎，可以用ENGINE关键字指定:

CREATE TABLE te_eng(
id INT PRIMARY KEY auto_increment COMMENT '编号',
name VARCHAR(20) NOT NULL COMMENT '姓名'
)ENGINE=MyISAM DEFAULT CHARSET utf8;

使用show create table te_eng;查看建表语句:

3)修改已存在表的存储引擎：
可以使用alter table 表名 engine = 存储引擎来修改：

ALTER TABLE te_eng ENGINE = INNODB;

运行后再查看建表语句:

发现已经改成InnoDB了，就可以使用InnoDB的相关特性了。

7.常用的存储引擎的特性


1)MyISAM

  1. 简介
    不支持事务，也不支持外键，优势是访问速度很快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎创建表。
  2. MyISAM的存储:
    每个MysIAM在磁盘上存储成3个文件，文件名与表名相同，扩展名为
        .firm（存储表的定义）
        .MYD（MYData，存储数据）
        .firm（MYIndex，存储表的索引）
    默认是存放在一个文件夹里的(这是上个例子的磁盘目录):

    数据文件和索引文件可以放置在不同的目录，平均分布IO获得更快的速度。
    需要在创建表时时通过DATA DIRECTORY和INDEX DIRECTORY语句指定，不同的MyISAM表的索引文件和数据文件可以放置到不同路径下。文件路径需要时绝对路径，而且要有访问权限。
  3. MyISAM表可能会损坏，损坏后的表不能被访问，可以用CHECK TABLE语句来检查MyISAM表的健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。
  4. MyISAM支持三种不同的存储格式：（静态表，动态表，压缩表）
    • 静态表：默认的存储格式（如char,迅速易恢复但是会自动去空格）
    • 动态表：变长节省空间，但是会产生碎片且出错恢复困难
    • 压缩表：由mysiampack创建，每个记录单独压缩，访问开支小，占用磁盘空间小

2）InnoDB(最常用)

  1. 简介：
    InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM写数据的处理效率慢一些，并且会占用更多的磁盘空间保留数据和索引。
  2. 自动增长列的处理：
    InnoDB表的自动增长可以插入，但是插入得的值是0或null时实际将是自动增长后的值（详见Mysql学习笔记二），可以通过ALTER TABLE ** AUTO_INCREMENT=n;指定自动增长的初始值，但是仅存在内存中，数据库重启后会消失。
    可以使用select LAST_INSERT_ID()；查看最后一次插入的自动增长值。
    InnoDB自动增长列必须是索引，若是组合索引则必须是组合索引的第一列。而MyISAM表，自动增长的可以是组合索引其他列。
  3. 外键约束：
    MySQL中唯一支持外键的存储引擎只有InnoDB,在创建外键时，要求父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。
    关于外键的操作学到了再补充。
    可以通过show create table **或show table status **查看外键信息
  4. 存储方式(两种):
    • 共享空间存储：表结构存.frm中，数据和索引存在共同表空间中。
    • 使用多表空间存储：默认的方式，表结构存.frm中，数据和索引单独保存在.ibd中。

3）MEMORY:

  1. 简介：
    MEMORY使用存在内存中的内容来创建表。每个MEMORY文件实际只对应一个磁盘文件.firm。MEMORY表的访问速度非常快，因为它的数据是放在内存中的，且默认使用HASH索引，但是一旦服务关闭表中的数据就会丢失。
  2. 给MEMORY表创建索引时可以指定使用HASH索引还是B树(BTREE)索引（假设给MEMORY表te_me的city_id添加HASH索引）：
    CREATE INDEX men_hash USING HASH ON te_me （city_id）；
  3. 启动MySQL时使用--init-file选项吧INSERT INTO ...SELECT或LOAD DATA INFILE这样的语句放入这个文件中就可以在每次启动时从持久稳固的数据源加载表。
  4. 不需要MEMORY表时要释放内存，执行DELETE FROM或者TRUNCATE TABLE或者直接DROP删掉整个表。
  5. MEMORY表可以存多少数据受到max_heap_table_size这个系统变量的约束。初始值16M可以根据需要加大。定义MEMORY表时还可以通过MAX_ROWS子句指定表的最大行数。
  6. MEMORY类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间表，便于高效的对中间结果进行分析并的到最终的统计结果。

4）MERGE:
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同。MERGE表本身并没有数据，对它的操作其实就是对内部的MyISAM表的操作。

5）TokuDB:

  1. 第三方存储引擎，有列式存储引擎Infobright,还有高性能高压缩的TokuDB就是其中非常有代表性的两种。
  2. TokuDB是一个高性能，支持事务处理的MySQL和MariaDB的存储引擎，具有高扩展性，高压缩性，高效的写入性能，支持大多数的DDL操作。
  3. 和InnoDB相比的优势:

    使用Fractal树索引保证高效的插入性能；
    优秀的压缩特性，比InnoDB高近10倍；
    Hot Schem Changes特性支持在线创建索引和添加、删除属性列等DDL操作
    使用Bulk Loader达到快速加载大量数据
    主从延迟消除技术
    支持ACID和MVCC

  4. TokuDB适用的场景:

    日志数据，日志数据插入频繁且存储量大
    历史数据，通常不会再有写操作，可利用TokuDB高压缩性存储
    在线DDL较频繁的场景，增加系统可用性

8.存储引擎的适用环境及选择

  1. MyISAM：
    如果应用以读操作和插入操作为主只有很少更新和删除操作，且对事务完整性、并发要求不高那么这个存储引擎非常合适，在Web，数据仓库等环境下常用。
  2. InnoDB：
    用于事务处理应用程序，支持外键。如果应用对事务完整性有较高的要求，在并发下要求数据的一致性，数据操作有很多的CRUD，那么InnoDB比较合适。
    InnoDB除了有效降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于了类似计费系统或者财务系统等准确性比较高的系统较适合。
  3. MEMORY：
    将所有数据保存在RAM内，在需要快速定位记录和其他类型数据的环节下，可以提供极快的访问。MEMORY的缺点是对表的大小有限制，太大的表无法缓存到内存中。其次是要确保表的数据可以恢复。MEMORY通常用于更新不太频繁的小表，以快速得到访问结果。
  4. MERGE：
    用于将一系列等同的MySIAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问速度。如数据仓库等适用。
  5. 更多的存储引擎（如ARCHIV）等以后有用到再更吧。

最后更新： 2018年05月31日 07:04

原始链接： https://zjxkenshine.github.io/2018/03/16/MySQL数据库学习笔记（三）/

赏
× 请我吃糖~
打赏二维码