03-如何建表更符合业务

1、什么叫索引组织表？

1.1、索引组织表的概念

1、索引组织表不是一种“组织表〞。
2、索引组织表是由索引 “组织起来的〞表。
3、InnoDB中，表都是根据主键顺序组织存放的。

1.2、索引 (Index)

1、索引是数据库中对某一列或多个列的值进行预排序的数据结构。
2、索引可以理解为数据的 “目录〞。
3、InnoDB中，主键是一个特殊索引字段。主键ID不是主索引,主键ID进行排序生成的叫主索引。

1.3、主键 (Primary Key)

1、InnoDB存储引擎表中，每张表都有一个主键。
2、若表中有一个非空唯一索引 (Unique NOT NULL)，即为主键。
3、若有多个非空唯一索引，选择第一个定义的索引。
4、若无，InnoDB自动创建一个6字节的指针，作为主键。

1.3.1、演示主键

1、下面的SQL建表语句中，哪一列是主键？

CREATE TABLE my_test (a INT NOT NULL,b INT NULL,c INT NOT NULL,d INT NOT NULL,
UNIQUE KEY (b),UNIQUE KEY (d),UNIQUE KEY (c) );

答案：没有设置primary key 的情况下，b、c、d都是唯一索引，b可以为空，所以排除，c、d都是唯一索引并且不为空，因为d先声明的，所以唯一索引是d。

过程：

>创建数据库：右键->新建数据库。 

字符集：utf8mb4。

排序规则：utf8mb4_general_ci。

>创建数据表

//创建数据表
CREATE TABLE my_test (a INT NOT NULL,b INT NULL,c INT NOT NULL,d INT NOT NULL,
UNIQUE KEY (b),UNIQUE KEY (d),UNIQUE KEY (c) );

>查看my_test的详细信息

-- 查看my_test的详细信息
DESC my_test;

 1.4、总结

1、InnoDB数据表均为索引组织表。
2、索引组织表中的数据，被主键的索引组织起来。

2、B+ 树

Innodb的索引组织表的索引用的算法：B+ 树。

2.1、主流索引查找算法

1、线性查找 Linear Search
2、二分查找 Binary Search
3、二叉查找树 Binary Search Tree
4、平衡二叉树 AVL Tree
5、B树   B Tree
6、B+ 树    B+ Tree  

2.1.1、数据结构可视化

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

旧金山大学数据结构可视化页面。

2.1.2、线性查找 Linear Search

1、时间复杂度O(N)

        n 的值为多少，程序就运行多少次，类似于下图：

2、从第一个数据开始，逐个匹配。

 

 2.1.3、二分查找 Binary Search

1、时间复杂度O(logN)。
2、拿出有序数列中点位置作为比较对象。
3、根据中点数据大小，选取一半数据作为新的数列。
4、每次可以将数据量减小一半。

缺陷：磁密中的数据不是连续读写的，我们不知道中间的那个数据在哪里，所以这个二分查找就很
麻烦。

2.1.4、二叉查找树 Binary Search Tree

1、时间复杂度O(logN)。
2、使用经典的二叉树数据结构。
3、由根节点开始查找。
4、可能退化为线性查找。

2.1.5、平衡二叉树 AVL Tree 

1、查找时，与二叉查找树相同。
2、增删改时，通过旋转操作，维护树的平衡。
3、AVL树可以保证不会退化成线性查找。

演示：我们在插入一个数据66，他会进行旋转操作，维护树的平衡。

 

4、平衡二叉树效率已经非常高了，索引为什么不使用平衡二叉树？

        内存、硬盘的读写的最小基本单位，机器硬盘最小512B,SSD最小是4k，或8k,一个节点存放的数据占用4k，是不可行的。节点放的数据太少。

2.1.6、B树   B Tree

1、B树的结构

2、B树是线性数据结构和树的结合。
3、B树通过多数据节点大大降低了树的高度。
4、B树不需要旋转就可以保证树的平衡。

5、缺点：

        范围查找的时候效率非常慢。

2.1.7、B+ 树    B+ Tree  

1、B+树是由B树发展而来的一种数据结构。
2、B+树的所有数据均在叶子节点。
3、B+树的所有数据形成了一个线性表 。

2.1.8、总结

1、B+树是目前最主流的数据库索引算法。
2、B+树由线性表、二叉树、B树发展而来。
3、B+树集成了线性表、平衡二叉树的优势。

3、为什么说InnoDB索引即数据？

3.1、B+树索引

1、InnoDB使用B+树作为索引的数据结构。
2、B+树的高度一般为2-4层，查找速度非常快。

        一个节点就可以存上万条数据，2到4层就可以实现几百万条数据。
3、InnoDB索引分为聚族索引（主索引） 和辅助索引。

3.1.1、聚簇索引 Clustered Index

1、根据表的主键构造一个B+树。
2、叶子节点直接存放行数据，而不是指针。
3、索引组织表中，数据也是B+树的一部分。

         数据也放到了主索引的位置，所以叫聚簇索引。

3.1.2、辅助索引 Secondary Index

1、每张表可以有多个辅助索引。
2、叶子节点并不包含行数据。
3、叶子节点记录了行数据的主键，用来指示数据位置。

3.1.3、回表

辅助索引存的是主键的地址，我们根据主键id的地址，在去聚簇索引中搜索这一条数据。

3.2、总结

1、InnoDB索引分为聚族索引（主索引） 和辅助索引。
2、在同层B+树节点之间，为双向链表。
3、在B+树节点之内，数据条目之间为单向链表。
4、所谓索引即数据，是把数据直接记录在了主索引里。

4、InnoDB数据表是如何存储的？

1、InnoDB的逻辑存储结构为表空间、段、区、页、行。
2、InnoDB的逻辑存储结构充分考虑了以基于B+树的表结构。
3、InnoDB中的页是lnnoDB自身的逻辑概念，与硬件的页无关。

4.1、表空间 (tablespace    也叫idb文件)

1、表空间指的是数据表在硬盘上的存储空间。
2、默认，所有表的数据都存在共享表空间。
3、每个表的数据也可以放在独占表空间(ibd文件）。

        配置独占表空间：当表崩溃、进行恢复的时候就会很方便。

4.2、段 (segment)

InnoDB中，段由存储引擎自动管理。

4.3、 区 (extent)

1、区是由连续页组成的空间，大小为1MB。
2、一次从磁盘申请4~5个区。
3、一般来讲含有64个页 (Page)。

 4.4、页 (page)

1、页是InnoDB中磁盘读写的最小逻辑单位，默认16KB。
2、一个数据页就是一个B+树的节点 (B+ Tree Node)。
3、页的大小充分考虑了机械硬盘和SSD的最小单元 （512B和4KB）。

4、page与page之间使用指针进行关联。

5、为什么页不能太大，比如16MB？

> 一次将磁密空间16MB读到内存中，磁盘压力太大；

> 16MB的数据量大大，因为这个页是链表结构，如果找一条数据太困难。
6、为什么页不能太小，比如16B？

        机械硬盘和SSD的最小单元（512B和4KB），如果页太小的话，这个页就占不满机械硬盘和SSD的最小单元，就会照成浪费。

4.5、 数据行

4.5.1、InnoDB中的变长列

1、长度不固定的数据类型：
VARCHAR, VARBINARY(存的二进制), BLOB(二进制), TEXT
不常用

> 占用空间大于768Byte的不变长类型： CHAR
> 变长编码下的 CHAR

4.5.2、行溢出数据

1、由于InnoDB每个数据页容量有限，导致数据字段也是有限的。
2、当数据字段过大时，InnoDB会使用行溢出机制。
3、行溢出机制会把超长字段放入单独开辟的BLOB页。

 解决方案：

4.6、InnoDB行记录格式 Row Format

1、InnoDB行记录格式主要分为两个时代：
Redundant / Compact (Antelope 文件格式）了解
Dynamic / Compressed (Barracuda 文件格式）

2、行记录格式进化的核心需求是节约行记录空间。
3、节约行记录空间从而增加每个页的数据行数，提高查询效率。

4.6.1、Dynamic

MySQL 5.7之后默认的Row Format。

 4.6.2、Compressed(了解)

物理结构上与Dynamic类似。

5、索引有哪些  "左侧用法"  ？

5.1、联合索引

1、使用两个或以上字段生成的索引。
2、联合索引也可以加速  “最左前缀〞的查询。
3、联合索引可以代替最左侧字段的单独索引。

 5.2、字符串的前缀索引

1、如果字符串过长，可以考虑使用前缀索引节约空间。        比如邮箱地址。
2、如果前缀区分度太小，可以考虑两种变通方法                   比如身份证号码
        > 倒序存储
        > 新建Hash字段，将原来的字段hash到这个字段。
3、创建方式： alter table user add index index2(email(6))

5.3、字符串like

1、( like %关键字％ ）( like ％关键字 ）会使索引失效。

        如果数据量大，要就行全局搜索的时候，可以使用es搜索引擎。
2、( like 关键字%）左模糊才可以使用索引。

6、如何约束数据？

InnoDB约束数据的方法:
1、Primary Key / Unique Key
2、Foreign Key
3、Default / NOT NULL
4、触发器

6.1、Primary Key / Unique Key

1、通过将数据字段设置为索引，约束数据内容
2、Primary Key：唯一,不为NULL
3、Unique Key:  唯一

4、唯一约束插入时的性能开销较大，插入数据的时候会进行校验。

6.2、Foreign Key

1、外键可以对数据的正确性实现约束

公司中谨慎使用。数据修复、恢复的时候会有很多问题。

6.3、Default / NOT NULL

1、Default ：数据默认值。
2、NOT NULL：数据不为空。

3、如果这个字段要建辅助索引的时候，就要让这个字段不能为null。因为效率会很低。

4、MySQL在严格模式下开启NOT NULL这种行为，不能插入null;不严格模式下这个字段设置为NOT NULL也可以插入null。

查看MySQL是否开启严格模式：SHOW VARIABLES LIKE 'innodb_strict_mode';

6.4、触发器

1、插入、修改数据时，使用触发器校验数据。
2、容易千扰业务，使用很少

7、如何使用不存在的数据表？

7.1、视图 View

1、使用视图可以创建不存在的虚拟表。
2、视图的原理是预设一个SELECT语句。
3、SELECT语句的查询结果作为虚拟表的数据。

7.2、视图算法的选择

1、MERGE， 将视图SQL合并到主查询SQL中。
2、TEMPTABLE，将视图作临时表（中间结果） 来处理。
3、一般来讲，MERGE的性能优 于TEMPTABLE。

eg:数据表my_test如图所示：

 -- 创建视图myView2
CREATE ALGORITHM=MERGE VIEW myView2 AS SELECT * from my_test WHERE c > 20;

-- 查询视图
SELECT * FROM myView2 WHERE a = 11;

实际执行的时候SQL会merge到一起，查询视图执行的SQL语句就相当于：SELECT * from my_test WHERE c > 20 AND a = 11;

7.3、无法使用MERGE的SQL

聚集函数
DISTINCT
GROUP BY
HAVING
UNION, UNION ALL
子查询

8、理论和实际中的规范

1、理论：增加每页(page)数据量：
     实际规范：       

                尽量做到冷热数据分离，减小表的宽度。
                优先选择符合存储需要的最小的数据类型。

2、理论：避免行溢出：
      实际规范：       

                把BLOB 或是 TEXT列分离到单独的扩展表中。
                禁止在数据库中存储图片，文件等大的二进制数据。

3、理论：控制B＋树高度：
     实际规范：     

                尽量控制单表数据量的大小，建议控制在 500 万以内。