建站笔记（二）

技术需求

参考笔记原文链接

一、技术栈

1.前端

JS框架：JQuery
CSS框架：Semantic UI官网
Markdown编辑器：编辑器 Markdown
代码高亮：代码高亮 prism
动画效果：动画 animate.css
文章目录：目录生成 Tocbot
音乐盒：zplayer
照片墙：lightbox插件

2.后端

核心框架：SpringBoot 2.2.5
项目构建：jdk1.8、Maven 3
持久层框架：Mybatis
模板框架：Thymeleaf
分页插件：PageHelper
加密：MD5加密
部署工具：Docker、Nginx
运行环境：阿里云Centos7

3.数据库

MySQL 5.7

二、功能需求

因为是个人博客，所以没有做用户权限管理，只是简单的区分了一下普通用户和管理员用户，这里就根据普通用户和管理员用户来讲述功能需求，其实从上一篇博文的前端页面就能大致的看出需求了

1.普通用户（游客）

查看文章信息：文章列表、推荐文章、文章标题、文章内容、发布时间、访问量以及评论等信息
查看分类文章：分类列表、分类文章信息
查看时间轴：按照文章时间发布顺序查看文章
搜索文章：导航栏右边搜索框根据关键字搜索
听音乐：上一曲、下一曲、音量控制、播放顺序控制、查看歌词等
留言：留言并回复
查看友链：查看并访问博主在友链页面添加的友链连接
查看相册信息：相册列表、照片名称、照片拍摄地点、时间、照片描述

2.管理员用户（栈主）

拥有普通用户所有功能权限
登录：在主页路径下加“/admin”，可进入登录页面，根据数据库的用户名和密码进行登录
文章管理：查询文章列表、新增文章、编辑文章、删除文章、搜索文章
分类管理：查询分类列表、新增分类、编辑分类、删除分类
友链管理：查询友链列表、新增友链、编辑友链、删除友链
相册管理：查询相册列表、新增照片、编辑照片、删除照片
消息管理：登录后恢复评论留言会显示栈主的头像信息，并能显示删除消息按键，可以对消息进行删除

三、数据库设计

1.数据表

博客数据表：t_blog
分类数据表：t_type
用户数据表：t_user
评论数据表：t_comment
留言数据表：t_message
友链数据表：t_friend
相册数据表：t_picture

2.实体关系

博客和分类是多对一的关系：一个博客对应一个分类，一个分类可以对应多个博客
博客和用户是多对一的关系：一个博客对应一个用户，一个用户可以对应多个博客
博客和评论是一对多的关系：一个博客可以对应多个评论，一个评论对应一个博客
评论和回复是一对多的关系：一个评论可以对应多个回复，一个回复对应一个评论

留言和评论是一样的，还有友链和相册数据表和其他表没有关联

3.实体属性

博客属性：

分类属性：

用户属性：

评论属性：

留言属性：

友链属性：

相册属性：

博客属性：标题、内容、首图、标记、浏览次数、赞赏开启、版权开启、评论开启、是否发布、创建时间、更新时间、描述
分类属性：分类名称
用户属性：昵称、用户名、密码、邮箱、类型、头像、创建时间、更新时间
评论属性：昵称、邮箱、头像、评论内容、创建时间、博客id、父评论id、管理员id
留言属性：昵称、邮箱、头像、留言内容、创建时间、父留言id、管理员id
友链属性：网址、名称、创建时间、图片地址
相册属性：图片地址、图片描述、图片名称、拍摄时间地点

4.表结构

博客表：

分类表：

用户表：

评论表：

留言表：

友链表：

相册表：

4.建表语句

/*
 Navicat MySQL Data Transfer

 Source Server         : myblog-localhost
 Source Server Type    : MySQL
 Source Server Version : 50717
 Source Host           : localhost:3306
 Source Schema         : myblog

 Target Server Type    : MySQL
 Target Server Version : 50717
 File Encoding         : 65001

 Date: 30/04/2020 17:02:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_blog
-- ----------------------------
DROP TABLE IF EXISTS `t_blog`;
CREATE TABLE `t_blog`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `appreciation` bit(1) NOT NULL,
  `commentabled` bit(1) NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `first_picture` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `published` bit(1) NOT NULL,
  `recommend` bit(1) NOT NULL,
  `share_statement` bit(1) NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `views` int(11) NULL DEFAULT NULL,
  `type_id` bigint(20) NULL DEFAULT NULL,
  `user_id` bigint(20) NULL DEFAULT NULL,
  `comment_count` int(255) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK292449gwg5yf7ocdlmswv9w4j`(`type_id`) USING BTREE,
  INDEX `FK8ky5rrsxh01nkhctmo7d48p82`(`user_id`) USING BTREE,
  CONSTRAINT `FK292449gwg5yf7ocdlmswv9w4j` FOREIGN KEY (`type_id`) REFERENCES `t_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK8ky5rrsxh01nkhctmo7d48p82` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 62 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_comment
-- ----------------------------
DROP TABLE IF EXISTS `t_comment`;
CREATE TABLE `t_comment`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `blog_id` bigint(20) NULL DEFAULT NULL,
  `parent_comment_id` bigint(20) NULL DEFAULT NULL,
  `admin_comment` bit(1) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 28 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_friend
-- ----------------------------
DROP TABLE IF EXISTS `t_friend`;
CREATE TABLE `t_friend`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `blogaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `blogname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `pictureaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_message
-- ----------------------------
DROP TABLE IF EXISTS `t_message`;
CREATE TABLE `t_message`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `parent_message_id` bigint(20) NULL DEFAULT NULL,
  `admin_message` bit(1) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_picture
-- ----------------------------
DROP TABLE IF EXISTS `t_picture`;
CREATE TABLE `t_picture`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pictureaddress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `picturedescription` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `picturename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `picturetime` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_type
-- ----------------------------
DROP TABLE IF EXISTS `t_type`;
CREATE TABLE `t_type`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 58 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `avatar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `nickname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `type` int(11) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

笔记：

MySQL中的级联删除与更新策略on delete restrict on update restrict:

ON DELETE操作 restrict(约束): 当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。

no action: 意思同restrict.即如果存在从数据，不允许删除主数据。

cascade(级联): 当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表(即包含外键的表)中的记录。

set null: 当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)。

ON UPDATE操作

restrict(约束): 当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。

no action: 意思同restrict.

cascade(级联): 当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表(即包含外键的表)中的记录。

set null: 当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)。

NO ACTION和RESTRICT的区别: 只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

mysql索引-Btree索引-hash索引-使用场景

B-tree索引特点：

按定义索引时列的顺序排序

myisam中：存储行物理位置,使用前缀压缩技术使得索引更小；

innodb中：存储主键值，使用B+Tree索引；

加快数据的查询速度
适合范围查找

使用场景：

全值匹配
匹配最左前缀
列前缀
范围值
精确匹配左前列并范围匹配另一列
只访问索引的查询
order by

btree限制:

不是最左列开始查找则无法使用
不能跳过索引列
not in <>无法使用
某个列使用了范围查找则其右边的所有列无法使用索引

hash索引:

memory引擎默认索引类型，支持非唯一哈希索引，哈希冲突采用链式法

InnoDB自适应hash索引，基于b-tree之上建立，可关闭

伪哈希索引，将较长的字段通过自定义哈希函数（CRC32、Md5等）得到长度固定的另一列做为索引列**）**

hash索引包含hash值和行指针，结构紧凑，速度快
基于hash表实现对所有索引列计算一个hash码，哈希索引将所有哈希码存储在索引中，同时在哈希表保存指向每个数据行的指针
只有精确匹配hash索引中所有列才能使用到
无法排序不支持部分索引和范围查找存在hash冲突，只支持等值查找：=、in()、<=>

索引优缺点:

减少扫描数据量
帮助排序避免使用临时表
把随机io转为顺序io
增加写成本
索引太多增加了查询优化器的选择时间

索引限制：

查询时索引(左)列上不能使用表达式或函数
索引长度限制: 使用前缀索引 (选择性要高)

小表不需要索引，中到大型非常有效，特大型-分区，数量特多-建立元数据信息表

IGNORE INDEX

联合索引:

ORDER BY\GROUP BY \DISTINCT

常用列优先
选择性高优先
宽度小的列优先

覆盖索引:（包含所查询的所有列）

优化缓存减少io
随机io转顺序io
避免对innodb主键索引的二次查询
避免myisam表进行系统调用

覆盖索引限制：

覆盖索引需要存储索引列值，所有只能使用btree索引
无法使用:不支持覆盖索引(memory）
查询中太多列无法覆盖
双百分号无法使用索引

使用索引优化排序:(type=index) explain分析查询语句

需满足要求：

索引列顺序和orderby顺序及升降序需完全一致
orderby的字段全部在关联表中的第一张表中

使用二级索引优化排序联合索引顺序升降一致左前列不能使用范围查找

模拟hash索引:(innodb不能人为建立hash索引，若能认为能优化效率则采用自适应hash索引)

使用md5对长列加密再对加密列建立索引
全值匹配有hash冲突(采用查询两列)

减少锁定行（for update加排他锁模拟）

加快处理速度锁释放速度

查看冗余索引： pt-duplicate-key-checker h=127.0.0.1
查看索引：performance_scheme information_scheme
分析：analyze table table_name
碎片：optimize table table_name