1.关系型数据库的设计

数据库基础结构

要设计一个关系型数据库首先要将其划分成2大部分，一部分是存储部分，该部分类似与一个文件系统，来将数据持久化到存储设备当中，
第二部分是程序的实例部分，来对存储进行逻辑上的管理，程序的实例部分包括，

存储管理：数据的逻辑关系转换为物理关系的存储管理模块
缓存管理：优化执行效率
SQL解析：将SQL语句进行解析的SQL解析模块
日志管理：记录操作的日志管理模块
权限划分：进行多用户管理的权限划分模块
容灾机制：灾难恢复模块(数据库恢复)
索引管理：优化数据查询
锁管理：使得数据库支持并发操作的锁模块

2.索引模块

一.索引基本概念

1..为什么要使用索引？

存储的最小单位是块或者页，我们在查询的时候会现将一个块或者多个块加载到内存当中，然后逐个块的去查询出来我们需要的数据。即全表扫描只适用于数据较小的情况。所以引入了索引，
根据关键信息来查找信息的方式，速度很快，所以这些关键信息就组成了我们所说的索引。

索引可以避免全表扫描，提升查询效率

2.哪些信息可以成为索引？

主键，唯一键等只要是能使数据具有一定区分性的字段都可以作为索引

3.索引的优缺点及特点？

索引的特点：
1.索引可以加快数据库的检索速度，降低增删改数据的速度
2.索引只能创建在表上，不能创建在视图上
3.及可以直接创建也可以间接创建
4.使用查询处理器查询SQL语句，在一个表上一次只能使用一个索引

image.png

image.png

5.索引的分类

索引的分类从不同的角度看，有不同的分类，下面只是个人看法

索引一般可以分为四类单列索引、组合索引、空间索引、全文索引
全文索引：目前只有MYISAM引擎支持全文索引，全文索引是为了解决模糊查询问题出现的"%word%".
空间索引：目前只有MYISAM引擎支持全文索引，并且字段不能为空。
组合索引：在表当中的多个字段上建立索引，只有查询条件当中使用了组合索引的最左边的时候，索引才会起效果，即最左匹配原则。
单列索引：一个索引只包含单个列，一个表中可以有多个单列索引

5.索引使用的注意事项

不走索引的情况：
-- 不走索引，因为所有索引参与了运算
select name from student where age+10=36;

-- 不走索引，因为使用了函数运算
select name from student where left(date,4)>1992;

-- 不走索引
select name from student where name like '%zs%';
-- 走索引
select name from student where name like 'zs%';

-- 正则表达式不走索引，所以在SQL语句当中很少看到regexp关键字

-- 字符串和数字比较不使用索引
create tabble stu(a varchar(20))      在a上加索引;
select * from stu a='10';    -- 走索引
select * from stu a=1    ;-- 不走索引

-- 如果条件当中有or，那么即是条件当中有索引，也是不会使用的，换而言之，在使用or的时候，所有的条件都必须要带有索引，才会使用索引。
select * from stu where dname="zs" or dept=12;

-- 如果MySQL估计使用全表扫描锁使用的时间比使用索引化的时间要少，就不使用索引

image.png

索引失效的情况：
1.没有遵循最左匹配原则。
2.一些关键字会导致索引失效，例如 or， ！= ， not in，is null ,is not unll
3.like查询是以%开头
4.隐式转换会导致索引失效。
5.对索引应用内部函数，索引字段进行了运算。

6.索引的适用场景

当建立在2张表的连接属性上时可加速表的连接

性别不适应用建立索引?为什么?
因为你访问索引需要付出额外的 IO 开销，你从索引中拿到的只是地址，要想真正访问 到数据还是要对表进行一次 IO。假如你要从表的 100 万行数据中取几个数据，那么利用索 引迅速定位，访问索引的这 IO 开销就非常值了。但如果你是从 100 万行数据中取 50 万行 数据，就比如性别字段，那你相对需要访问 50 万次索引，再访问 50 万次表，加起来的开 销并不会比直接对表进行一次完整扫描小。

二.索引的数据结构

索引的数据结构即指的是索引的具体实现方式，索引是在存储引擎中实现的，因此每种存储引擎对索引的实现都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL中的数据用各种不同的技术存储在文件(或者内存)中。
这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。

通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
而存储引擎说白了就是
如何存储数据
如何为存储的数据建立索引
如何更新、查询数据 等技术的实现方法。

常见的索引的数据结构有二叉树、B-tree、B+tree、BitMap、hash等等等等，下面进行一些简单的介绍

在实际开发过程当中最常用的就是在创建索引的时候指定索引所使用的数据结构，如果不指定，就会使用该引擎默认的数据结构，下面的例子当中由于InnoDB引擎默认的对索引的实现就是btree，所以在创建KEY user_id index('user-id') USING BTREE索引的时候也可以不指定，因为默认即使btree。

CREATE TABLE mmall_cart（
    id int(11) NOT NULL AUTO INCREMENT,
    user_id int(11) NOT NULL,
    product_id int(11) DEFAULT NULL COMMENT '商品id',
    quantity int(1) DEFAULT NULL COMMENT '数量’,
    checked int(11) DEFAULT NULL COMMENT '是否选择,1=已勾选,0=未勾选',
    create_time datetime DEFAULT NULL COMMENT '创建时间',
    update_time datetime DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (id),
    KEY user_id index('user-id') USING BTREE
）ENGINE=InnoDB AUTO INCREMENT=121 DEFAULT CHARSET=utf8

1.二叉查找树

间复杂度是O(logn)

影响程序运行速率的瓶颈是IO，如果树很深的话，要进行IO的次数就会好多次，这样检索性能就会比全表扫描差很多，存储的最小单位是块，数据库索引是存储在磁盘上，当表中的数据量比较大时，索引的大小也跟着增长，达到几个G甚至更多。当我们利用索引进行查询的时候，不可能把索引全部加载到内存中，只能逐一加载每个磁盘页，这里的磁盘页就对应索引树的节点

image.png

2.B-Tree

m阶B-Tree满足以下条件：
1、每个节点最多拥有m个子树
2、根节点至少有2个子树
3、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
4、所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且key以升序排列

如下有一个3阶的B树，观察查找元素21的过程：

B-Tree

B-Tree比二叉树要矮多的，进行的IO次数也要少的多，所以效率要高很多。

B-TREE索引使用场景

全值匹配的查询SQL，如 where act_id= '1111_act'
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
匹配模糊查询的前匹配，如where act_name like '11_act%'
匹配范围值的SQL查询，如where act_date > '9865123547215'（not in和<>无法使用索引）
覆盖索引的SQL查询，就是说select出来的字段都建立了索引

3.B+Tree

B+Tree是B树的变种，有着比B树更高的查询性能，来看下m阶B+Tree特征：
1、有m个子树的节点包含有m个元素（B-Tree中是m-1）
2、根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中。由于分支节点不存储数据，故可以存储更多的关键字了，所以更矮了，B树则不行，因为它可能将数据存储在分支上了。
3、所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素。
4、所有的叶子节点都有一个链指针指向下一个叶子节点，并且叶子节点本身是根据关键字的大小从小到大顺序链接。

B+Tree

B+Tree叶子节点的特征：
1.所有叶子节点当中的关键字按照大小顺序排列
2.相邻的叶子节点组成一个顺序链表
3.所有的叶子节点都处在同一层
4.所有的叶子节点包含了全部的关键字和关键字所指向的指针

链接起来之后可以方便我们做范围统计，支持范围统计，一旦定位到一个叶子节点，就可以横向的侉子树去做统计

image.png

4.建立Hash结构进行查找

image.png

5.BitMap位图索引(MySql不支持)

基于InnoDB和MyISAM的MySQL数据库不支持BitMap结构

6.索引为什么采用 B+树

1.B+树更有利于对数据库的扫描
  B 树在提高了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题，而 B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，
所以对于数据库中频繁使 用的 range query(范围统计)，B+树有着更高的性能。
2.B+树的磁盘读写代价更低
  B+树当中内部节点并没有指向关键字具体信息的指针，所以因此其内部结点相对 B 树更 小，可以存放更多的key，树的高度越低，IO代价越低。
3.B+树的查询效率更加稳定
  由于B+树当中的节点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以所有关键字的查找，都会是从根节点到叶子节点的路程，所以每一个数据的查询效率相当。

7. B+树与 B-树的区别

答:
1.内部节点中，关键字的个数与其子树的个数相同，不像 B 树种，子树的个数 总比关键字个数多 1 个
2.所有指向文件的关键字及其指针都在叶子节点中，不像 B 树，有的指向文件的关 键字是在内部节点中。换句话说，B+树中，内部节点仅仅起到索引的作用，
3.B+在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而 是继续向下搜索这个分支，B+为了找到这个关键字的指针。

三.索引的分类

[https://www.cnblogs.com/heyonggang/p/6610526.html]

1.索引分类

 组合索引:实质上是将多个字段建到一个索引里，列值的组合必须唯一
 聚集索引:定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。
 非聚集索引:唯一索引 普通索引 主键索引 全文索引
 UNIQUE(唯一索引):不可以出现相同的值，可以有 NULL 值
 INDEX(普通索引):允许出现相同的索引内容
 PROMARY KEY(主键索引):不允许出现相同的值
 fulltext index(全文索引):可以针对值中的某个单词，但效率确实不敢恭维 

2.索引的创建

创建唯一，主键，普通，全文索引

**********************使用ALTER TABLE语句创建索性:******************************


ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;

ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。
*****************使用CREATE INDEX语句对表增加索引*****************************
CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引。

CREATE INDEX index_name ON table_name(username(length)); 
//如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

创建组合索引

组合索引

建立索引(a,b,c)
select * from table where a = '1'  ;//走索引
select * from table where a = '1' and b = ‘2’  ；//走索引
select * from table where a = '1' and b = ‘2’  and c='3'；//走索引
select * from table where a = '1' and c= ‘2’；//只走a索引

image.png

查询优化器优化索引为可以识别的形式：即在实际执行过程当中，优化为 area=‘’ and title=’’;

最左匹配原则的成因：
MySQL在创建符合索引的规则是，会对最左边，即第一个进行排序，在第一个排序的基础上再次进行第二个索引的排序。

3,索引类型

I.直接创建索引和间接创建索引

直接创建索引:CREATE INDEX mycolumn_index ON mytable (myclumn)
间接创建索引:定义主键约束或者唯一性键约束，可以间接创建索引

II.普通索引和唯一性索引

普通索引:CREATE INDEX mycolumn_index ON mytable (myclumn) 
唯一性索引:保证索引列当中的数据是唯一的
                CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)，
唯一索引的好处：
1.简化了对索引的管理工作，是索引更加高效
2.MySQL会在有新记录插入时，自动检测表当中是否已经存在这个记录，如果存在就拒绝加入新的记录，即：唯一索引可以保证数据的唯一性，事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出 现重复。

III.单个索引和复合索引

单个索引:即非复合索引 复合索引:又叫组合索引，在索引建立语句中同时包含多个字段名，最多 16 个字段 CREATE INDEX name_index ON username(firstname,lastname)

IV.聚簇索引和非聚簇索引(聚集索引，群集索引)/密集索引和稀疏索引

1.密集索引


2.稀疏索引

四.MySQL的MyISAM和InnoDB引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

聚集索引和非聚集索引

    首先，无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下,举个例子，
如果我们将字典的汉字随即打乱，没有前面的按照拼音或者部首查询，那么我们想找一个字，按照顺序的方式去一页页的找，这样效率有多底，大家可以想象。

    聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，其实理解起来非常简单，
还是举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c， 聚集索引就是这样的，
他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。
非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，
但是这两个其实一个在100页，一个在1000页，（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。

    原理明白了，那他们是怎么存储的呢？在这里简单的说一下，聚集索引就是在数据库被开辟一个物理空间存放他的排列的值，
例如1-100，所以当插入数据时，他会重新排列整个整个物理空间，而非聚集索引其实可以看作是一个含有聚集索引的表，他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针，其实就有点和堆栈差不多的感觉了

聚集索引：索引顺序和数据库表记录排列的顺序是一样的
非聚集索引：索引顺序和数据库表记录排列的顺序是不一样的。

image.png

1.MyISAM

image.png

MyISAM的索引使用B+Tree，辅助键和主键是完全独立的

InnoDB

image.png

image.png

3.SQL优化

1.explain定位并查询慢SQL

需要知道该 SQL 的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN 去完成。explain命令是查询优化器如何执行查询的命令

查询慢SQL日志：
-- 查询和慢日志相关的信息，即执行的相对较慢的sql
show variables like '%quer%';

show variables like '%quer%'

-- 查询和慢至相关的信息，即执行的相对较慢的sql
show variables like '%quer%';

-- 查看慢状态,慢查询的SQL语句数量
show status like '%slow_queries%';

-- 打开慢查询日志
set global slow_query_log=on;

-- 设置慢查询时间
set global long_query_time=1;

sudo  vim 慢日志地址（然后输入当前的数据库密码）

在控制台查询慢日志具体信息：通过慢日志去俘获慢SQL，然后分析为什么这么慢，然后进行调优

image.png

2.使用explain分析查询性能

image.png

I.type

type

MySQL找到需要的数据行的方式，方式有很多，我们值考虑性能，性能从最优到最差依次从左到右排序

II.Extra

image.png

Using filesort:MySQL会对记过使用一个外部索引排序，用不到表里面的任何索引，而是借助MySQL外部的一些方式进行排序，这样效率也会低下
Using temporary：将排序的结果存储到一张临时表里，方便后面使用，但是他是使用order by 造成的，没有使用索引，因此效率也不好。

III.key

显示MySQL在实际查询当中所使用的索引，如没有使用索引，则是null;

3.通过修改SQL进行SQL优化

I.SQL语句及索引优化

image.png

image.png

II.数据库表结构优化

III.锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

IV.使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

V.选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

4.数据库语法

1.各种连接 left join、right join、inner join，full join cross join

sql 之 left join、right join、inner join 的区别
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) 只返回两个表中联结字段相等的行

表 A 记录如下:                                      表 B 记录如下:
aID aNum                                              bID bName                           
1 a20050111                                          1 2006032401
2 a20050112                                          2 2006032402
3 a20050113                                          3 2006032403
4 a20050114                                          4 2006032404
5 a20050115                                          8 2006032408
--------------------------------------------------------------------------------------------

1.left join
select * from A left join B on A.aID = B.bID
结果如下:
aID aNum        bID bName
1 a20050111        1 2006032401
2 a20050112        2 2006032402
3 a20050113        3 2006032403
4 a20050114        4 2006032404
5 a20050115        NULL NULL

结果说明:
left join 是以 A 表的记录为基础的,A 可以看成左表,B 可以看成右表,left join 是以左表为准的. 换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID). B 表记录不足的地方均为 NULL.

2.right join
select * from A right join B on A.aID = B.bID
结果如下:
结果如下:
aID aNum bID     bName
1 a20050111 1   2006032401
2 a20050112 2    2006032402
3 a20050113 3    2006032403
4 a20050114 4   2006032404
NULL NULL 8    2006032408
  
(所影响的行数为 5 行)
结果说明:
仔细观察一下,就会发现,和 left join 的结果刚好相反,这次是以右表(B)为基础的,A 表不足的地方用 NULL 填充.

3.inner join
select * from A inner join B on A.aID = B.bID
结果如下:
aID aNum      bID bName
1 a20050111      1 2006032401
2 a20050112      2 2006032402
3 a20050113      3 2006032403 
4 a20050114      4 2006032404 

结果说明:
很明显,这里只显示出了 A.aID = B.bID 的记录.这说明 inner join并不以谁为基础,它只显示符合条件的记录.

4.全外连接(full join ...on...)
select * from table1 a full join table2 b on a.id=b.id 全外连接其实是左连接和右 连接的一个合集，也就是说他会查询出左表和右表的全部数据，匹配不上的会显示为 null;

5.交叉连接(cross join...)也称为笛卡尔积
select * from table1 a ， table2 b on a.id=b.id
查询返回结果的行数等于两个表行数的乘积，我们在做2表关联的时候，一般使用的就是交叉连接

2.GROUP BY

image.png

-- 查询所有学生的学号，选课数，总成绩
select  student_id,count(course_id),sun(score) 
from score
group by student_id;


-- 查询所有学生的学号，姓名，选课数，总成绩
select sc.student_id,st.studentName,count(course_id),sun(score) 
from score sc,student st
where sc.id=st.student_id
GROUP BY sc.student_id

3.having

image.png