如何查询树形结构的数据 如何查询树形结构的数据在遇到数据库保存的数据为树形结构，如多级分类列表或者评论等，此处以分类列表举例我们需要向前端返回如下部分数据[ { "id": "1-1", "name": "前端开发", "label": "前端开发", "parentid": "1", "isShow": 1, "orderby": 1, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-1-1", "name": "HTML/CSS", "label": "HTML/CSS", "parentid": "1-1", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-10", "name": "其它", "label": "其它", "parentid": "1-1", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-2", "name": "JavaScript", "label": "JavaScript", "parentid": "1-1", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-3", "name": "jQuery", "label": "jQuery", "parentid": "1-1", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-4", "name": "ExtJS", "label": "ExtJS", "parentid": "1-1", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-5", "name": "AngularJS", "label": "AngularJS", "parentid": "1-1", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-6", "name": "ReactJS", "label": "ReactJS", "parentid": "1-1", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-7", "name": "Bootstrap", "label": "Bootstrap", "parentid": "1-1", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-8", "name": "Node.js", "label": "Node.js", "parentid": "1-1", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-1-9", "name": "Vue", "label": "Vue", "parentid": "1-1", "isShow": 1, "orderby": 9, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-10", "name": "研发管理", "label": "研发管理", "parentid": "1", "isShow": 1, "orderby": 10, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-10-1", "name": "敏捷开发", "label": "敏捷开发", "parentid": "1-10", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-2", "name": "软件设计", "label": "软件设计", "parentid": "1-10", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-3", "name": "软件测试", "label": "软件测试", "parentid": "1-10", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-4", "name": "研发管理", "label": "研发管理", "parentid": "1-10", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-10-5", "name": "其它", "label": "其它", "parentid": "1-10", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-11", "name": "系统运维", "label": "系统运维", "parentid": "1", "isShow": 1, "orderby": 11, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-11-1", "name": "Linux", "label": "Linux", "parentid": "1-11", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-10", "name": "其它", "label": "其它", "parentid": "1-11", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-2", "name": "Windows", "label": "Windows", "parentid": "1-11", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-3", "name": "UNIX", "label": "UNIX", "parentid": "1-11", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-4", "name": "Mac OS", "label": "Mac OS", "parentid": "1-11", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-5", "name": "网络技术", "label": "网络技术", "parentid": "1-11", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-6", "name": "路由协议", "label": "路由协议", "parentid": "1-11", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-7", "name": "无线网络", "label": "无线网络", "parentid": "1-11", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-8", "name": "Ngnix", "label": "Ngnix", "parentid": "1-11", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-11-9", "name": "邮件服务器", "label": "邮件服务器", "parentid": "1-11", "isShow": 1, "orderby": 9, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-2", "name": "移动开发", "label": "移动开发", "parentid": "1", "isShow": 1, "orderby": 2, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-2-1", "name": "微信开发", "label": "微信开发", "parentid": "1-2", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-2", "name": "iOS", "label": "iOS", "parentid": "1-2", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-3", "name": "手游开发", "label": "手游开发", "parentid": "1-2", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-4", "name": "Swift", "label": "Swift", "parentid": "1-2", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-5", "name": "Android", "label": "Android", "parentid": "1-2", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-6", "name": "ReactNative", "label": "ReactNative", "parentid": "1-2", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-7", "name": "Cordova", "label": "Cordova", "parentid": "1-2", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-2-8", "name": "其它", "label": "其它", "parentid": "1-2", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-3", "name": "编程开发", "label": "编程开发", "parentid": "1", "isShow": 1, "orderby": 3, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-3-1", "name": "C/C++", "label": "C/C++", "parentid": "1-3", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-2", "name": "Java", "label": "Java", "parentid": "1-3", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-3", "name": ".NET", "label": ".NET", "parentid": "1-3", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-4", "name": "Objective-C", "label": "Objective-C", "parentid": "1-3", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-5", "name": "Go语言", "label": "Go语言", "parentid": "1-3", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-6", "name": "Python", "label": "Python", "parentid": "1-3", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-7", "name": "Ruby/Rails", "label": "Ruby/Rails", "parentid": "1-3", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-3-8", "name": "其它", "label": "其它", "parentid": "1-3", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-5", "name": "人工智能", "label": "人工智能", "parentid": "1", "isShow": 1, "orderby": 5, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-5-1", "name": "机器学习", "label": "机器学习", "parentid": "1-5", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-2", "name": "深度学习", "label": "深度学习", "parentid": "1-5", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-3", "name": "语音识别", "label": "语音识别", "parentid": "1-5", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-4", "name": "计算机视觉", "label": "计算机视觉", "parentid": "1-5", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-5", "name": "NLP", "label": "NLP", "parentid": "1-5", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-6", "name": "强化学习", "label": "强化学习", "parentid": "1-5", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-5-7", "name": "其它", "label": "其它", "parentid": "1-5", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null } ] }, { "id": "1-9", "name": "智能硬件/物联网", "label": "智能硬件/物联网", "parentid": "1", "isShow": 1, "orderby": 9, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-9-1", "name": "无线通信", "label": "无线通信", "parentid": "1-9", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-10", "name": "物联网技术", "label": "物联网技术", "parentid": "1-9", "isShow": 1, "orderby": 10, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-11", "name": "其它", "label": "其它", "parentid": "1-9", "isShow": 1, "orderby": 11, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-2", "name": "电子工程", "label": "电子工程", "parentid": "1-9", "isShow": 1, "orderby": 2, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-3", "name": "Arduino", "label": "Arduino", "parentid": "1-9", "isShow": 1, "orderby": 3, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-4", "name": "体感技术", "label": "体感技术", "parentid": "1-9", "isShow": 1, "orderby": 4, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-5", "name": "智能硬件", "label": "智能硬件", "parentid": "1-9", "isShow": 1, "orderby": 5, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-6", "name": "驱动/内核开发", "label": "驱动/内核开发", "parentid": "1-9", "isShow": 1, "orderby": 6, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-7", "name": "单片机/工控", "label": "单片机/工控", "parentid": "1-9", "isShow": 1, "orderby": 7, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-8", "name": "WinCE", "label": "WinCE", "parentid": "1-9", "isShow": 1, "orderby": 8, "isLeaf": 1, "childrenTreeNodes": null }, { "id": "1-9-9", "name": "嵌入式", "label": "嵌入式", "parentid": "1-9", "isShow": 1, "orderby": 9, "isLeaf": 0, "childrenTreeNodes": [ { "id": "1-9-9-1", "name": "嵌入式基础", "label": "嵌入式基础", "parentid": "1-9-9", "isShow": 1, "orderby": 1, "isLeaf": 1, "childrenTreeNodes": null } ] } ] } ]由于返回的是一个List集合，因此需要定义一个DTO类表示分类信息的模型类@Data public class CourseCategoryTreeDto extends CourseCategory { //子节点 List<CourseCategoryTreeDto> childrenTreeNodes; }定义Controller类@Resource private CourseCategoryService categoryService; @GetMapping("/course-category/tree-nodes") public List<CourseCategoryTreeDto> queryCatTreeNodes(){ return categoryService.queryCatTreeNodes("1"); //此处1为根节点 }定义Mapperpublic interface CourseCategoryMapper extends BaseMapper<CourseCategory> { public List<CourseCategoryTreeDto> selectTreeNodes(String id); }写递归SQL（用于不确定有几级层级） <!-- SQL递归查询数据库树形结构 --> <select id="selectTreeNodes" parameterType="String" resultType="com.xuecheng.content.model.dto.CourseCategoryTreeDto"> WITH recursive t1 as( SELECT * from course_category WHERE id = #{id} UNION ALL SELECT c2.* from course_category c2 INNER JOIN t1 on t1.id = c2.parentid ) SELECT * from t1 order by t1.id, t1.orderby </select>内连接查询（确定有多少级层级） select one.id one_id, one.name one_name, one.parentid one_parentid, one.orderby one_orderby, one.label one_label, two.id two_id, two.name two_name, two.parentid two_parentid, two.orderby two_orderby, two.label two_label from course_category one inner join course_category two on one.id = two.parentid where one.parentid = 1 and one.is_show = 1 and two.is_show = 1 order by one.orderby, two.orderbyService和其实现类//Service类 public interface CourseCategoryService extends IService<CourseCategory> { List<CourseCategoryTreeDto> queryCatTreeNodes(String id); } //实现类 @Service public class CourseCategoryServiceImpl extends ServiceImpl<CourseCategoryMapper, CourseCategory> implements CourseCategoryService { @Resource CourseCategoryMapper courseCategoryMapper; /** * 根据id获取课程分类列表 * @param id 根节点id * @return 课程分类列表 */ @Override public List<CourseCategoryTreeDto> queryCatTreeNodes(String id) { //执行SQL，获取查询数据 List<CourseCategoryTreeDto> treeDtos = courseCategoryMapper.selectTreeNodes(id); //将结果转为map，便于后续找根节点，同时可以过滤当前id节点 //.filter(item -> !id.equals(item.getId()))不显示根节点 Map<String, CourseCategoryTreeDto> map = treeDtos.stream().filter(item -> !id.equals(item.getId())).collect(Collectors.toMap(CourseCategory::getId, value -> value, (key1, key2) -> key2)); List<CourseCategoryTreeDto> list = new ArrayList<>(); //遍历返回最终结果,排除根节点 treeDtos.stream().filter(item -> !id.equals(item.getId())).forEach(item ->{ //先将根节点的一级子节点放入数组 if (item.getParentid().equals(id)){ list.add(item); } //如果不为一级节点，就找到它的父节点，通过之前保存的map CourseCategoryTreeDto treeParent = map.get(item.getParentid()); //如果存在父节点就将其放入它的父节点的list中 if (treeParent != null){ //如果它的父节点的子节点列表为空，就新建一个对象存储 if (treeParent.getChildrenTreeNodes() == null){ treeParent.setChildrenTreeNodes(new ArrayList<>()); } //不为空就放入子节点中 //注意这里是需要获取子节点列表然后插入进入 treeParent.getChildrenTreeNodes().add(item); } }); return list; } } 前端显示示例

Mysql学习总结 基础篇MySQL概述数据库相关概念数据库（DataBase DB）:存储数据的仓库，数据是有组织的进行存储数据库管理系统（DataBase Management System DBMS）:操纵和管理数据库的大型软件SQL(Structured Query Language)：操纵关系型数据库的编程语言，定义了一套操作关系型数据统一标准数据模型关系型数据库（RDBMS）概念：建立在关系模型基础上，有多涨相互连接的二维表组成的数据库特点：使用表存储数据，格式统一，便于维护使用SQL语言操作，标准统一，使用方便数据模型：我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包 含多行记录。SQLSQL通用语法SQL语句可以单行或多行书写，以分号结尾。SQL语句可以使用空格/缩进来增强语句的可读性。MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。注释：单行注释：-- 注释内容 或 # 注释内容多行注释：/ 注释内容 /SQL分类DDL（Data Definition Language）:数据定义语言，用来定义数据库对象(数据库，表，字段)DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的 访问权限DDL数据库操作查询所有数据库show databases;查询当前数据库select database();创建数据库[ ]中的内容可以填写，可以不填。if not exists用于有重复名的数据库就不创建create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];删除数据库drop database [ if exists ] 数据库名;切换数据库user 数据库;表操作查询创建查询当前数据所有表show tables;查看指定表结构desc 表名;查询指定表的建表语句show create table 表名;创建表结构CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ], 字段2 字段2类型 [COMMENT 字段2注释 ], 字段3 字段3类型 [COMMENT 字段3注释 ], ...... 字段n 字段n类型 [COMMENT 字段n注释 ] ) [ COMMENT 表注释 ];注意: [...] 内为可选参数，最后一个字段后面没有逗号，表名后面是()，不是{}数据类型MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。数值类型1). 年龄字段 -- 不会出现负数, 而且人的年龄不会太大 age tinyint unsigned -- unsigned非负数 2). 分数 -- 总分100分, 最多出现一位小数 score double(4,1) -- 4整体长度，1位小数字符串类型char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性 能会更高些。1). 用户名 username -- 长度不定, 最长不会超过50 username varchar(50) 2). 性别 gender -- 存储值, 不是男,就是女 gender char(1) 3). 手机号 phone -- 固定长度为11 phone char(11)日期时间类型1). 生日字段 birthday -- 只需记录年月日即可 birthday date 2). 创建时间 createtime -- 需要具体时间 createtime datetime修改添加字段 ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];修改数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 (长度) [ COMMENT 注释 ] [ 约束 ];删除字段ALTER TABLE 表名 DROP 字段名;修改表名ALTER TABLE 表名 RENAME TO 新表名;删除删除表DROP TABLE [ IF EXISTS ] 表名;删除指定表，并重新创建表( 可以理解为清空表中数据)TRUNCATE TABLE 表名;DML添加数据（insert）修改数据（update）删除数据（delete）添加数据给指定字段添加数据 INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);给全部字段添加数据INSERT INTO 表名 VALUES (值1, 值2, ...);批量添加数据INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; -- 全部字段都插入数据插入数据时，指定的字段顺序需要与值的顺序是一一对应的。字符串和日期型数据应该包含在引号中。插入的数据大小，应该在字段的规定范围内。修改数据UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据删除数据 DELETE FROM 表名 [ WHERE 条件 ] ;DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即 可)。当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击 Execute即可。DQL查询关键字：SELECT基本语法SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数基础查询查询多个字段SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; SELECT * FROM 表名; -- *代表查询所有字段，不直观且效率低字段设置别名SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;去除重复记录SELECT DISTINCT 字段列表 FROM 表名;条件查询SELECT 字段列表 FROM 表名 WHERE 条件列表 ;常见的条件聚合函数将一列数据作为一个整体，进行纵向计算 。 SELECT 聚合函数(字段列表) FROM 表名 ;注意 : NULL值是不参与所有聚合函数运算的。例如统计总数据时数据为null的不会参与统计分组查询SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];where与having区别:执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组 之后对结果进行过滤。判断条件不同：where不能对聚合函数进行判断，而having可以。分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。执行顺序: where > 聚合函数 > having 。支持多字段分组, 具体语法为 : group by columnA,columnB排序查询SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2;排序方式：ASC：升序（默认值）DESC：降序如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;分页查询SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。-- 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数 select * from emp limit 10,10;执行顺序DCL管理用户查询用户select * from mysql.user; -- %表示通配符创建用户CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';修改用户密码ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;删除用户DROP USER '用户名'@'主机名' ;权限控制查询权限SHOW GRANTS FOR '用户名'@'主机名' ;授予权限GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; -- 多个权限用逗号分隔，数据库和表名可以用*通配撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';函数函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中 已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。字符串函数常用字符串函数数值函数常见数值函数日期函数常见日期函数-- 获取当前年 select YEAR(now()); -- date_add 增加指定的时间间隔 select date_add(now(), INTERVAL 70 YEAR );流程函数程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。-- 演示 select if(false, 'OK', 'Error'); -- 如果为false,返回OK，否则返回Error select ifnull('Ok','Default'); select ifnull('','Default'); -- ''不为空，返回'' select ifnull(null,'Default'); -- 必须为null时才为空，'null'也不行 -- 需求，对学生成绩进行分级 select id, name, (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学', (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语', (case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end ) '语文' from score; -- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市) swith case select name,( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址' from emp;约束概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。目的：保证数据库中数据的正确、有效性和完整性分类：外键约束介绍：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。 (FOREIGN KEY)注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联， 所以是无法保证数据的一致性和完整性的。语法添加外键CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称删除/更新行为添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行 为有以下几种:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;多表查询多表关系一对多实现：在多的一方建立外键，指向一的一方的主键多对多实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键一对一关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，以提升操作效率实现： 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UIQUE）多表查询概述多表查询就是指从多张表中查询数据。分类内连接：相当于查询A、B交集部分数据外连接左外连接：查询左表所有数据，以及两张表交集部分数据右外连接：查询右表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名子查询内连接内连接查询的是两张表交集部分的数据。-- 隐式内连接 SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; -- 显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;注意表名使用了别名就不能继续使用原名了外连接-- 左外连接 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; -- 右外连接 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...; -- 右外连接可以用左外连接表示（同理左外连接也可以用右外连接表示，左外连接用的更多） SELECT 字段列表 FROM 表2 LEFT [ OUTER ] JOIN 表1 ON 条件 ... ; -- 与上述右外连接结果一致自连接就是自己连接自己，也就是把一张表连接查询多次。自连接查询SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...; -- 查询员工及其所属领导的名字 select a.name , b.name from emp a , emp b where a.managerid = b.id; -- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。联合查询对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....;对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。子查询概述SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); -- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。分类：子查询结果不同标量子查询（子查询结果为单个值）列子查询(子查询结果为一列)行子查询(子查询结果为一行)表子查询(子查询结果为多行多列)子查询位置WHERE之后FROM之后SELECT之后标量子查询子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：= <> > >= < <=select * from emp where dept_id = (select id from dept where name = '销售部'); -- 子查询只需要返回一个id列子查询子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALLselect * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部'); -- 返回的id不止一个，而是一列行子查询子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：= 、<> 、IN 、NOT INselect * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌'); -- 返回的是salary,managerid两个值，为一行多列表子查询子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符：INselect * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' ); -- job, salary 返回的是多行多列事务事务简介事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐 式的提交事务。事务操作控制事务一查看/设置事务提交方式SELECT @@autocommit ; SET @@autocommit = 0 ; -- 设置为手动提交提交事务COMMIT;回滚事务ROLLBACK;注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提 交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。控制事务二开启事务START TRANSACTION 或 BEGIN ;提交事务COMMIT;回滚事务ROLLBACK;事务四大特性(ACID)原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。并发事务问题脏读：一个事务读到另外一个事务还没有提交的数据。不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 "幻影"。幻读图片解释：由于设置了不可重复读，事务B插入了主键id=1，那么事务A插入时就会报错，同时他也会查不到id=1的信息事务隔离级别为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：从上往下事务隔离越高（x表示解决了该问题）查看事务隔离级别SELECT @@TRANSACTION_ISOLATION;设置事务隔离级别SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }注意：事务隔离级别越高，数据越安全，但是性能越低。进阶篇存储引擎MySQL体系结构连接层：最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务 器也会为安全接入的每个客户端验证它所具有的操作权限。服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大， 这样在解决大量读操作的环境中能够很好的提升系统的性能。引擎层：存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通 信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。存储层：数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。存储引擎介绍存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果 没有指定将自动选择默认的存储引擎。-- 建表时指定存储引擎 -- mysql5之后默认InnoDB CREATE TABLE 表名( 字段1 字段1类型 [ COMMENT 字段1注释 ] , ...... 字段n 字段n类型 [COMMENT 字段n注释 ] ) ENGINE = INNODB [ COMMENT 表注释 ] ; -- 查询当前数据库支持的存储引擎 show engines;存储引擎特点InnoDBInnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。特点：DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；文件：xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。参数：innodb_file_per_table 如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。（MySQL8默认打开）逻辑存储结构：表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以 包含多个Segment段。段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管 理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默 认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时 所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。MyISAMMyISAM是MySQL早期的默认存储引擎。特点：不支持事务，不支持外键支持表锁，不支持行锁访问速度快文件：xxx.sdi：存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引MemoryMemory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为 临时表或缓存使用。特点：内存存放hash索引（默认）文件：xxx.sdi：存储表结构信息区别InnoDB引擎与MyISAM引擎的区别 ? ①. InnoDB引擎, 支持事务, 而MyISAM不支持。 ②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。 ③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。存储引擎选择InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要 求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操 作，那么InnoDB存储引擎是比较合适的选择。MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完 整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是 对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。索引索引概述索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。特点：索引结构MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：不同的存储引擎对于索引结构的支持 情况：注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。二叉树如果选择二叉树作为索引结构，会存在以下缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树。但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：大数据量情况下，层级较深，检索速度慢。B-TreeB-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：知识小贴士: 树的度数指的是一个节点的子节点个数。(5度=4key=5指针)动画演示：https://www.cs.usfca.edu/~galles/visualization/BTree.html特点：5阶的B树，每一个节点最多存储4个key，对应5个指针。一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。在B树中，非叶子节点和叶子节点都会存放数据。B+TreeB+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一 下其结构示意图：绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。B+Tree 与 B-Tree相比，主要有以下三点区别：所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。Hash哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在 hash表中。特点：Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）无法利用索引完成排序操作查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索 引存储引擎支持: 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。为什么InnoDB存储引擎选择使用B+tree索引结构?相对于二叉树，层级更少，搜索效率高；对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；相对Hash索引，B+tree支持范围匹配及排序操作索引分类索引分类在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引聚集索引&二级索引在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：聚集索引选取规则:如果存在主键，主键索引就是聚集索引。如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。聚集索引的叶子节点下挂的是这一行的数据 。二级索引的叶子节点下挂的是该字段值对应的主键值回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询。思考题：以下两条SQL语句，那个执行效率高? 为什么?A. select * from user where id = 10 ; B. select * from user where name = 'Arm' ; 备注: id为主键，name字段创建的有索引；A 语句的执行性能要高于B 语句。 因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然 后再查询聚集索引，也就是需要进行回表查询。思考题：InnoDB主键索引的B+tree高度为多高呢?假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空 间，主键即使为bigint，占用字节数为8。高度为2： n 8 + (n + 1) 6 = 16 *1024 , 算出n约为 1170 , 1171* 16 = 18736 也就是说，如果树的高度为2，则可以存储 18000 多条记录。高度为3： 1171 1171 16 = 21939856 也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。索引语法创建索引CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ; CREATE INDEX idx_user_name ON tb_user(name); -- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。 CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone); -- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。 CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status); -- 为profession、age、status创建联合索引。 CREATE INDEX idx_email ON tb_user(email); -- 为email建立合适的索引来提升查询效率。查看索引SHOW INDEX FROM table_name ;删除索引DROP INDEX index_name ON table_name ;SQL性能分析SQL执行频率MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：SHOW GLOBAL STATUS LIKE 'Com_______';通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据 库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以 查询为主，那么就要考虑对数据库的索引进行优化了。慢查询日志慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：# 开启MySQL慢日志查询开关 slow_query_log=1 # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。profile详情show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数，能够看到当前MySQL是否支持profile操作：SELECT @@have_profiling ;可以通过set语句在 session/global级别开启profiling：(默认是关闭的)SET profiling = 1;-- 查看每一条SQL的耗时基本情况 show profiles; -- 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; -- 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;explain执行计划EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行 过程中表如何连接和连接的顺序。-- 直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;索引使用最左前缀法则如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是 第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。(如索引字段是ABC,条件编写是BCA 也是走ABC的索引的)范围查询联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0'; -- 这样status就不会走联合索引所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <索引失效情况索引列 运算-- 当根据phone字段(是索引)进行函数运算操作之后，索引失效。 explain select * from tb_user where substring(phone,10,2) = '15';字符串不加引号 -- phone为字符串,已创建索引 explain select * from tb_user where phone = '17799990015'; -- 走索引 explain select * from tb_user where phone = 17799990015; -- 不走索引模糊查询如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。explain select * from tb_user where profession like '软件%'; -- 不失效 explain select * from tb_user where profession like '%工程'; -- 失效 explain select * from tb_user where profession like '%工%'; -- 失效or连接条件用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会 被用到。-- id phone 有索引 explain select * from tb_user where id = 10 or age = 23; -- age无索引.索引失效;age有索引,索引生效 explain select * from tb_user where phone = '17799990017' or age = 23; -- age无索引.索引失效;age有索引,索引生效数据分布影响就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃 索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不 如走全表扫描来的快，此时索引就会失效。查询时MySQL会评估，走索引快，还是全表扫描快，如果全表 扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体 分析，并不是固定的。SQL提示SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优 化操作的目的。use index: 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进 行评估）。explain select * from tb_user use index(idx_user_pro) where profession = '软件工程'; -- 建议使用idx_user_pro索引ignore index: 忽略指定的索引。explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';-- 不使用idx_user_pro索引force index: 强制使用索引。explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';-- 强制使用idx_user_pro索引覆盖索引尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并 且需要返回的列，在该索引中已经全部能够找到 。因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段 profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主 键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引 直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据 了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表 查询（除非是根据主键查询，此时只会扫描聚集索引）。一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对 以下SQL语句进行优化, 该如何进行才是最优方案: select id,username,password from tb_user where username = 'itcast';--> 针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);前缀索引当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建 立索引，这样可以大大节约索引空间，从而提高索引效率。create index idx_xxxx on table_name(column(n)) ; -- (n)表示该字符串前n个字符可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）(count(distinct email))和数据表的记录总数(count(email))的比值 (count(distinct email)/count(email))， 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。select count(distinct email) / count(*) from tb_user ; select count(distinct substring(email,1,5)) / count(*) from tb_user ;单列索引和联合索引单列索引：即一个索引只包含单个列。 联合索引：即一个索引包含了多个列。在and连接的两个字段 phone、name上都是有单列索引的，但是 最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。在联合索引中包含 phone、name的信息，在叶子节点下挂的是对 应的主键id，所以查询是无需回表查询的。在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引。索引设计原则针对于数据量较大，且查询比较频繁的表建立索引。针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索 引。尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。SQL优化插入数据insert批量插入Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');手动控制事务start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;主键顺序插入，性能要高于乱序插入主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89大批量插入数据如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使 用MySQL数据库提供的load指令进行插入。操作如下：-- 客户端连接服务端时，加上参数 -–local-infile mysql –-local-infile -u root -p -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; -- 执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;在load时，主键顺序插入性能高于乱序插入主键优化数据组织方式在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。 那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储 到下一个页中，页与页之间会通过指针连接。页分裂页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。页合并当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间 变得允许被其他记录声明使用。主键设计原则满足业务需求的情况下，尽量降低主键的长度。插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键，如身份证号。业务操作时，避免对主键的修改。order by优化Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要 额外排序，操作效率高。对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。优化原则根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。尽量使用覆盖索引。多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。group by优化在分组操作时，可以通过索引来提高效率。分组操作时，索引的使用也是满足最左前缀法则的。limit优化通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记 录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查 询形式进行优化。explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;count优化MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个 数，效率很高； 但是如果是带条件的count，MyISAM也慢。 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出 来，然后累积计数。如果说要大幅度提升InnoDB表的count效率，主要的优化思路：自己计数(可以借助于redis这样的数 据库进行,但是如果是带条件的count又比较麻烦了)。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽 量使用 count(*)。update优化根据索引字段进行更新，否则可能行锁会升级为表锁，产生并发问题，性能降低。InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁 升级为表锁 。视图/存储过程/触发器视图视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视 图的查询中使用的表，并且是在使用视图时动态生成的。 通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作 就落在创建这条SQL查询语句上。语法-- 创建 CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED | LOCAL ] CHECK OPTION ] -- 查询 查看创建视图语句：SHOW CREATE VIEW 视图名称; 查看视图数据：SELECT * FROM 视图名称 ...... ; -- 修改 方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] 方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] -- 删除 DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...-- 演示 -- 创建视图 create or replace view stu_v_1 as select id,name from student where id <= 10; -- 查询视图 show create view stu_v_1; select * from stu_v_1; select * from stu_v_1 where id < 3; -- 修改视图 create or replace view stu_v_1 as select id,name,no from student where id <= 10; alter view stu_v_1 as select id,name from student where id <= 10; -- 删除视图 drop view if exists stu_v_1;检查选项当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插 入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视 图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。CASCADED级联，检查当前视图和所依赖的视图是否都满足条件（不管依赖的视图是否定义了条件）。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图 创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。LOCAL本地，检查当前视图和所依赖的视图是否都满足条件（依赖的视图未定义条件则不检查）。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创 建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。视图的更新要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项，则该视图不可更新：聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）DISTINCTGROUP BYHAVINGUNION 或者 UNION ALL视图的作用简单视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视 图，从而使得用户不必为以后的操作每次指定全部的条件。安全数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见 到的数据数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。-- 为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。 create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user; -- 查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。 create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;存储过程存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发 人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到 的时候直接调用即可。可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回 值。减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传 输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。基础语法-- 创建 CREATE PROCEDURE 存储过程名称 ([ 参数列表 ]) BEGIN -- SQL语句 END ; -- 调用 CALL 名称 ([ 参数 ]); -- 查看 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指定数据库的存储过程及状态信息 SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义 -- 删除 DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的 结束符。-- 演示 -- 创建 -- 命令行执行sql语句时，需要加 delimiter $$指定结束符号，这样后续所有语句结束都得以$$结束 create procedure p1() begin select count(*) from student; -- 命令行中此处不需要更换为$$ end; -- end$$ -- 调用 call p1(); -- 查看 select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast'; show create procedure p1; -- 删除 drop procedure if exists p1;变量系统变量-- 查看系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量 SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量 SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值 -- 设置系统变量 SET [ SESSION | GLOBAL ] 系统变量名 = 值 ; SET @@[SESSION | GLOBAL]系统变量名 = 值 ;如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。A. 全局变量(GLOBAL): 全局变量针对于所有的会话。 B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。用户定义变量-- 赋值 SET @var_name = expr [, @var_name = expr] ... ; -- [...]表示可以同时赋值多个变量 SET @var_name := expr [, @var_name := expr] ... ; -- := 和 =都可以，推荐 := SELECT @var_name := expr [, @var_name := expr] ... ; SELECT 字段名 INTO @var_name FROM 表名; -- 使用 SELECT @var_name ;注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的 局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。-- 声明 DECLARE 变量名 变量类型 [DEFAULT ... ] ; -- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等 -- 赋值 SET 变量名 = 值 ; SET 变量名 := 值 ; SELECT 字段名 INTO 变量名 FROM 表名 ... ;ifIF 条件1 THEN ..... ELSEIF 条件2 THEN -- 可选 ..... ELSE -- 可选 ..... END IF;-- 根据定义的分数score变量，判定当前分数对应的分数等级。 create procedure p3() begin declare score int default 58; declare result varchar(10); if score >= 85 then set result := '优秀'; elseif score >= 60 then set result := '及格'; else set result := '不及格'; end if; select result; end; call p3();参数IN：该类参数作为输入，也就是需要调用时传入值（默认）OUT：该类参数作为输出，也就是该参数可以作为返回值INOUT：该类参数作为输出，也就是该参数可以作为返回值CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ]) BEGIN -- SQL语句 END ;-- 示例 create procedure p4(in score int, out result varchar(10)) begin if score >= 85 then set result := '优秀'; elseif score >= 60 then set result := '及格'; else set result := '不及格'; end if; end; -- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明 call p4(18, @result); select @result;case-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list CASE case_value WHEN when_value1 THEN statement_list1 [ WHEN when_value2 THEN statement_list2] ... [ ELSE statement_list ] END CASE; -- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成立时，执行statement_list2， 否则就执行 statement_list CASE WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN statement_list2] ... [ELSE statement_list] END CASE;注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接whilewhile 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑 WHILE 条件 DO SQL逻辑... END WHILE;repeatrepeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环 。-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环 REPEAT SQL逻辑... UNTIL 条件 END REPEAT;loopLOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LEAVE ：配合循环使用，退出循环。ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。[begin_label:] LOOP SQL逻辑... END LOOP [end_label]; LEAVE label; -- 退出指定标记的循环体 ITERATE label; -- 直接进入下一次循环-- 计算从1累加到n的值，n为传入的参数值。 -- A. 定义局部变量, 记录累加之后的值; -- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx create procedure p9(in n int) begin declare total int default 0; sum:loop if n<=0 then leave sum; end if; set total := total + n; set n := n - 1; end loop sum; select total; end; call p9(100);游标游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进 行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。-- 声明游标 DECLARE 游标名称 CURSOR FOR 查询语句; -- 打开游标 OPEN 游标名称 ; -- 获取游标记录 FETCH 游标名称 INTO 变量 [, 变量 ] ; -- 关闭游标 CLOSE 游标名称 ;-- 案例 -- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。 create procedure p11(in uage int) begin declare uname varchar(100); declare upro varchar(100); declare u_cursor cursor for select name,profession from tb_user where age <= uage; drop table if exists tb_user_pro; create table if not exists tb_user_pro( id int primary key auto_increment, name varchar(100), profession varchar(100) ); open u_cursor; while true do fetch u_cursor into uname,upro; insert into tb_user_pro values (null, uname, upro); end while; close u_cursor; end; call p11(30);条件处理程序条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体 语法为：DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ; handler_action 的取值： CONTINUE: 继续执行当前程序 EXIT: 终止执行当前程序 condition_value 的取值： SQLSTATE sqlstate_value: 状态码，如 02000 SQLWARNING: 所有以01开头的SQLSTATE代码的简写 NOT FOUND: 所有以02开头的SQLSTATE代码的简写 SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写存储函数存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：CREATE FUNCTION 存储函数名称 ([ 参数列表 ]) RETURNS type [characteristic ...] BEGIN -- SQL语句 RETURN ...; END ;characteristic说明：DETERMINISTIC：相同的输入参数总是产生相同的结果NO SQL ：不包含 SQL 语句。READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。-- 计算从1累加到n的值，n为传入的参数值。 create function fun1(n int) returns int deterministic begin declare total int default 0; while n>0 do set total := total + n; set n := n - 1; end while; return total; end; select fun1(50);触发器触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触 发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还 只支持行级触发，不支持语句级触发。insert型触发器：NEW 表示将要或者已经新增的数据update型触发器：OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据delete型触发器：OLD 表示将要或者已经删除的数据语法-- 创建 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW -- 行级触发器 BEGIN trigger_stmt ; -- 触发执行的SQL语句 END; -- 查看 SHOW TRIGGERS ; -- 删除 DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。锁锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有 效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个 角度来说，锁对数据库而言显得尤其重要，也更加复杂。全局锁：锁定数据库中的所有表表级锁：每次操作锁住整张表行级锁：每次操作锁住对应的行数据全局锁全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语 句，已经更新操作的事务提交语句都将被阻塞。语法-- 加全局锁 flush tables with read lock ; -- 其他客户端只能读不能写 -- 数据备份 mysqldump -uroot –p1234 itcast > itcast.sql -- 不是SQL,是命令行语句 -- 释放锁 unlock tables ;特点如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导 致主从延迟。在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致 性数据备份。mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql表级锁表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、 InnoDB、BDB等存储引擎中。表锁表共享读锁（read lock） (读锁)表独占写锁（write lock）(写锁)结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。语法加锁：lock tables 表名... read/write; 释放锁：unlock tables; -- 客户端断开连接 。元数据锁meta data lock , 元数据锁，简写MDL。MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维 护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变 更操作的时候，加MDL写锁(排他)。意向锁为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行 数据是否加锁，使用意向锁来减少表锁的检查。意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁 (read)兼容，与表锁排他锁(write)互斥。意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共 享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。行级锁行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的 锁。行锁共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他 锁。针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记 录加锁，此时 就会升级为表锁。间隙锁/临键锁默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 临键锁进行搜 索和索引扫描，以防止幻读。索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁。InnoDB引擎逻辑存储结构架构事务原理事务基础事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。特性：原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环 境下运行。持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。其中的原子性、一致性、持久化，实际上是由InnoDB中的 两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁， 加上MVCC来保证的。redo log重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用 于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新 到磁盘呢 ?因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。 而redo log在 往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。 这 种先写日志的方式，称之为 WAL（Write-Ahead Logging）。undo log回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的 update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些 日志可能还用于MVCC。Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment。MVCC基本概念当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加 锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。快照读：简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据， 不加锁，是非阻塞读。Read Committed：每次select，都生成一个快照读。Repeatable Read：开启事务后第一个select语句才是快照读的地方。Serializable：快照读会退化为当前读。MVCC：全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本， 使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需 要依赖于数据库记录中的三个隐式字段、undo log日志、readView。隐藏字段DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版 本。DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。（如果有主键，则不会添加该隐藏字段）undo log回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。 当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即 被删除。undo log版本链readviewReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务 （未提交的）id。readview四个核心字段：m_ids，当前有哪些事务正在执行，且还没有提交，这些事务的 id 就会存在这里；min_trx_id，是指 m_ids 里最小的值；max_trx_id，是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大；creator_trx_id，每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id。一句话说：当trx_id在m_ids中，或者大于m_ids列表中最大的事务id的时候，这个版本就不能被访问。不同的隔离级别，生成ReadView的时机不同：READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。原理分析RC隔离级别RC隔离级别下，在事务中每一次执行快照读时生成ReadView。RR隔离级别RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。 而RR 是可 重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。运维篇日志错误日志错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过 程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日 志。show variables like '%log_error%';二进制日志二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但 不包括数据查询（SELECT、SHOW）语句。mysqlbinlog [ 参数选项 ] logfilename 参数选项： -d 指定数据库名称，只列出指定的数据库相关操作。 -o 忽略掉日志中的前n行命令。 -v 将行事件(数据变更)重构为SQL语句 -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息查询日志查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下， 查询日志是未开启的。# 如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容： #该选项用来开启查询日志 ， 可选值 ： 0 或者 1 ； 0 代表关闭， 1 代表开启 general_log=1 #设置日志的文件名 ， 如果没有指定， 默认的文件名为 host_name.log general_log_file=mysql_query.log慢日志查询慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。#如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数 #慢查询日志 slow_query_log=1 #执行时间参数 long_query_time=2 #默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。 #记录执行较慢的管理语句 log_slow_admin_statements =1 #记录执行较慢的未使用索引的语句 log_queries_not_using_indexes = 1主从复制主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这 些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。MySQL 复制的优点主要包含以下三个方面：主库出现问题，可以快速切换到从库提供服务。实现读写分离，降低主库的访问压力。可以在从库中执行备份，以避免备份期间影响主库服务。原理Master 主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log 。slave重做中继日志中的事件，将改变反映它自己的数据。详细搭建教程见https://blog.csdn.net/isis45454545454/article/details/126510152分库分表随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存 储，存在以下性能瓶颈：IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。 请求数据太多，带宽 不够，网络IO瓶颈。CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出 现瓶颈。分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能 问题，从而达到提升数据库性能的目的。拆分策略垂直拆分垂直分库以表为依据，根据业务将不同表拆分到不同库中。特点：每个库的表结构都不一样。每个库的数据也不一样。所有库的并集是全量数据。垂直分表以字段为依据，根据字段属性将不同字段拆分到不同表中。特点：每个表的结构都不一样。每个表的数据也不一样，一般通过一列（主键/外键）关联。所有表的并集是全量数据。水平拆分水平分库以字段为依据，按照一定策略，将一个库的数据拆分到多个库中。特点：每个库的表结构都一样。每个库的数据都不一样。所有库的并集是全量数据。水平分表以字段为依据，按照一定策略，将一个表的数据拆分到多个表中。特点：每个表的表结构都一样。每个表的数据都不一样。所有表的并集是全量数据。实现技术shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处 理。需要自行编码配置实现，只支持java语言，性能较高。MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前 者。MyCat概述Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用 mycat，对于开发人员来说根本感觉不到mycat的存在。详细见视频：https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=165ShardingSphere-JDBC采用Docker实现主从复制实现新增主节点配置文件mkdir -p /wolfcode/mysql/write_db/conf vi /wolfcode/mysql/write_db/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=1 binlog_format=STATEMENT log-bin=master-bin log-bin-index=master-bin.index启动MySQL主节点docker run -d --name write_db -p 3306:3306 \ -v /wolfcode/mysql/write_db/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/write_db/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql进行master容器，登录mysqldocker exec -it write_db /bin/bash mysql -uroot -proot #查看主节点状态 show master status;在mysql内部创建slave同步账号-- 创建slave用户 create user 'slave_user'@'%'; -- 设置密码 alter user 'slave_user'@'%' identified with mysql_native_password by 'root'; -- 授予复制权限 grant replication slave on *.* to 'slave_user'@'%'; -- 刷新权限 flush privileges;从节点1配置信息#退出mysql和docker exit; exit; mkdir -p /wolfcode/mysql/read_db0/conf vi /wolfcode/mysql/read_db0/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=2 relay-log-index=slave-relay-bin.index relay-log=slave-replay-bin #启动从节点1 docker run -d --name read_db0 -p 3307:3306 \ -v /wolfcode/mysql/read_db0/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/read_db0/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql #进入从节点容器，并登录mysql docker exec -it read_db0 /bin/bash mysql -uroot -proot #进入mysql执行命令 #docker inspect -f '{{.Name}} => {{.NetworkSettings.IPAddress }}' $(docker ps -aq) 查看ip change master to master_host='172.17.0.2', master_user='slave_user', master_password='root', master_port=3306, master_log_file='master-bin.000004', master_log_pos=156; #启动从节点1并查看状态 start slave; show slave status \G # Slave_IO_Running: Yes Slave_SQL_Running: Yes从节点2配置信息#退出mysql和docker exit; exit; mkdir -p /wolfcode/mysql/read_db1/conf vi /wolfcode/mysql/read_db1/conf/my.cnf #新增内容如下 [mysqld] #服务器唯一id，注意在集群中不要出现一样的 server-id=3 relay-log-index=slave-relay-bin.index relay-log=slave-replay-bin #启动从节点2 docker run -d --name read_db1 -p 3308:3306 \ -v /wolfcode/mysql/read_db1/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/read_db1/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ mysql #进入从节点容器，并登录mysql docker exec -it read_db1 /bin/bash mysql -uroot -proot #进入mysql执行命令 #docker inspect -f '{{.Name}} => {{.NetworkSettings.IPAddress }}' $(docker ps -aq) 查看ip #show master logs; 查看主节点log日志名 change master to master_host='172.17.0.2', master_user='slave_user', master_password='root', master_port=3306, master_log_file='master-bin.000004', master_log_pos=156; #启动从节点2并查看状态 start slave; show slave status \G # Slave_IO_Running: Yes Slave_SQL_Running: Yes创建完成后连接三个数据库，主表的变化，从表都会进行更新 --> Ip地址相同，端口不同3306，3307，3308使用ShardingSphere-JDBC（springboot框架）引入maven依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core</artifactId> <version>5.3.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.3.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.5.3.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.28</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.yaml</groupId> <artifactId>snakeyaml</artifactId> <version>1.33</version> </dependency>配置#在resources目录下新建sharding.yaml #数据源配置 dataSources: write_db: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3306/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root read_db0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3307/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root read_db1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3308/db_user?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root rules: # 读写分离 - !READWRITE_SPLITTING dataSources: readwrite_ds: staticStrategy: writeDataSourceName: write_db readDataSourceNames: - read_db0 - read_db1 loadBalancerName: random #负载均衡 loadBalancers: random: type: RANDOM #显示sql日志 props: sql-show: true #application.yml spring: datasource: driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver url: jdbc:shardingsphere:classpath:sharding.yamltest下新建一个Generator.java,用于mybatis-plus 代码生成public class Generator { @Test void generatorDomain() { FastAutoGenerator.create("jdbc:mysql://192.168.31.128:3306/db_user?serverTimezone=UTC&useSSL=false", "root", "root") .globalConfig(builder -> { builder.author("canace") // 设置作者 // .enableSwagger(f) // 开启 swagger 模式 .fileOverride() // 覆盖已生成文件 .outputDir("F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\java"); // 指定输出目录 }) .packageConfig(builder -> { builder.parent("com.study.shardingspherejdbc") // 设置父包名 .moduleName(null) // 设置父包模块名 .pathInfo(Collections.singletonMap(OutputFile.xml, "F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\resources")); // 设置mapperXml生成路径 }) .strategyConfig(builder -> { builder.addInclude("sys_user") // 设置需要生成的表名 .addTablePrefix("t_", "c_"); // 设置过滤表前缀 }) .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板 .execute(); } }springboot启动类上加mybatis-plus注解@SpringBootApplication @MapperScan("com.study.shardingspherejdbc.mapper") public class ShardingSphereJdbcApplication { public static void main(String[] args) { SpringApplication.run(ShardingSphereJdbcApplication.class, args); } }test文件内测试 @Autowired private ISysUserService iSysUserService; /** * 插入数据测试，三个数据库都保存了该数据 */ @Test void testInsert() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace1"); sysUser.setUserName("admin"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); } /** * 读取测试，数据从两个读数据库中随机读取 */ @Test void testRead(){ for (int i = 0; i < 5; i++) { List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); System.out.println("=========================="); } }事务情况测试 /** * 没有使用事务测试，存入写数据库write_db，从读数据中读取 */ @Test void testNoTransactional() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace2"); sysUser.setUserName("admin2"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); System.out.println("=========================="); List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); } /** * 使用事务测试，该数据由于事务没有提交，没有存入到任何数据库 * 但是在读取时会从读数据库中读，并且读到该数据 * Actual SQL: write_db ::: SELECT id,login_name,user_name,phone_name,password,salt FROM sys_user */ @Test @Transactional void testTransactional() { SysUser sysUser = new SysUser(); sysUser.setLoginName("canace3"); sysUser.setUserName("admin3"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); System.out.println("=========================="); List<SysUser> sysUsers = iSysUserService.list(); sysUsers.forEach(System.out::println); }负载均衡策略配置rules: # 读写分离 - !READWRITE_SPLITTING dataSources: readwrite_ds: staticStrategy: writeDataSourceName: write_db readDataSourceNames: - read_db0 - read_db1 #负载均衡算法名称 loadBalancerName: weight # loadBalancerName: random # loadBalancerName: round_robin # 负载均衡算法配置 loadBalancers: weight: type: WEIGHT #权重 props: read_db0: 1 #两个读数据库进行1:2的权重进行读取 read_db1: 2 # round_robin: # type: ROUND_ROBIN #轮转 # props: # transaction-read-query-strategy: FIXED_PRIMARY #transaction-read-query-strategy说明：所以策略都有这个，事务内读请求路由策略，可选值：FIXED_PRIMARY(路由到 primary)、FIXED_REPLICA(根据随机策略选择一个固定的 replica)、DYNAMIC_REPLICA(根据随机策略路由到不同的 replica)，默认值：FIXED_PRIMARY。 # random: # type: RANDOM #随机轮转负载均衡算法(ROUND_ROBIN)随即负载均衡算法(RANDOM)权重负载均衡算法(WEIGHT)分库分表实现创建新容器docker run -d \ -p 3309:3306 \ -v /wolfcode/mysql/customer_data0/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/customer_data0/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ --name customer_data0 \ mysql #进入从节点容器，并登录mysql docker exec -it customer_data0 /bin/bash mysql -uroot -proot创建数据库create database customer_data; use customer_data; CREATE TABLE `bus_statement` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;生成bus_satatement相关代码 @Test void generatorDomain() { FastAutoGenerator.create("jdbc:mysql://192.168.31.128:3309/customer_data?serverTimezone=UTC&useSSL=false", "root", "root") .globalConfig(builder -> { builder.author("canace") // 设置作者 // .enableSwagger(f) // 开启 swagger 模式 .fileOverride() // 覆盖已生成文件 .outputDir("F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\java"); // 指定输出目录 }) .packageConfig(builder -> { builder.parent("com.study.shardingspherejdbc") // 设置父包名 .moduleName(null) // 设置父包模块名 .pathInfo(Collections.singletonMap(OutputFile.xml, "F:\\JavaFile\\ShardingSphereJDBC\\src\\main\\resources\\mapper")); // 设置mapperXml生成路径 }) .strategyConfig(builder -> { builder.addInclude("bus_statement") // 设置需要生成的表名 .addTablePrefix("t_", "c_"); // 设置过滤表前缀 }) .templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker引擎模板，默认的是Velocity引擎模板 .execute(); }新增yaml信息 customer_data0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3309/customer_data?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root #进行分片 - !SHARDING tables: bus_statement: #表名 actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则）垂直分表 /** * 测试垂直分表，两个不同的表正确插入自己的表中 */ @Test void testVerticalShardingWrite(){ SysUser sysUser = new SysUser(); sysUser.setLoginName("canace4"); sysUser.setUserName("admin4"); sysUser.setPhoneName("12345678999"); sysUser.setPassword("123456"); sysUser.setSalt("1234"); iSysUserService.save(sysUser); BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace"); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); }分布式序列ID现在的服务基本是分布式、微服务形式的，而且大数据量也导致分库分表的产生，对于水平分表就需要保证表中 id 的全局唯一性。对于 MySQL 而言，一个表中的主键 id 一般使用自增的方式，但是如果进行水平分表之后，多个表中会生成重复的 id 值。那么如何保证水平分表后的多张表中的 id 是全局唯一性的呢？雪花算法是其中一个用于解决分布式 id 的高效方案，也是许多互联网公司在推荐使用的。#进行分片 - !SHARDING tables: bus_statement: #表名 actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 keyGenerators: snowflake: type: SNOWFLAKE /** * 测试雪花算法 */ @Test void testSnowFlake(){ BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace2"); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); }水平分表复制表bus_statement为bus_statement1，之前表改为bus_statement0#进行分片 - !SHARDING tables: bus_statement: #表名 # actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） actualDataNodes: customer_data0.bus_statement$->{0..1} #支持多表，即bus_statement0和bus_statement1 tableStrategy: # 分表策略，同分库策略 standard: # 用于单分片键的标准分片场景 shardingColumn: statement_no # 分表列名称 shardingAlgorithmName: t_mod # 分表算法名称 keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 # 分片算法配置 shardingAlgorithms: t_mod: type: MOD props: sharding-count: 2 keyGenerators: snowflake: type: SNOWFLAKE /** * 水平分表测试 */ @Test void testTableShardingInsert(){ for (int i = 0; i < 15; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace2"+ i); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } } /** * 水平分表测试数据是否都查询出来了 */ @Test void testTableShardingRead(){ List<BusStatement> list = iBusStatementService.list(); list.forEach(System.out::println); }水平分库创建新容器docker run -d \ -p 3310:3306 \ -v /wolfcode/mysql/customer_data1/conf:/etc/mysql/conf.d \ -v /wolfcode/mysql/customer_data1/data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=root \ --name customer_data1 \ mysql创建表create database customer_data; use customer_data; CREATE TABLE `bus_statement0` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; CREATE TABLE `bus_statement1` ( `statement_no` bigint(0) NOT NULL AUTO_INCREMENT, `customer_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `customer_phone` bigint(0) NULL DEFAULT NULL, `actual_arrival_time` datetime(0) NULL DEFAULT NULL, `service_type` bigint(0) NULL DEFAULT NULL, `status` int(255) UNSIGNED ZEROFILL NULL DEFAULT NULL, `total_amount` decimal(10, 0) NULL DEFAULT NULL, `create_time` datetime(0) NULL DEFAULT NULL, `info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`statement_no`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;配置 customer_data1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.jdbc.Driver jdbcUrl: jdbc:mysql://192.168.31.128:3310/customer_data?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true username: root password: root #进行分片 - !SHARDING tables: bus_statement: #表名 # actualDataNodes: customer_data0.bus_statement # 由数据源名 + 表名组成（参考 Inline 语法规则） actualDataNodes: customer_data$->{0..1}.bus_statement$->{0..1} #支持多表，即bus_statement0和bus_statement1 tableStrategy: # 分表策略 standard: # 用于单分表键的标准分表场景 shardingColumn: statement_no # 分表列名称 shardingAlgorithmName: t_mod # 分表算法名称 databaseStrategy: #分库策略 standard: # 用于单分片键的标准分片场景 shardingColumn: customer_phone # 分片列名称 shardingAlgorithmName: t_inline # 分片算法名称 keyGenerateStrategy: #分布式序列策略 column: statement_no # 自增列名称，缺省表示不使用自增主键生成器 keyGeneratorName: snowflake # 分布式序列算法名称 雪花算法 # 分片算法配置 shardingAlgorithms: t_mod: type: MOD props: sharding-count: 2 t_inline: type: INLINE props: algorithm-expression: customer_data$->{customer_phone % 2} keyGenerators: snowflake: type: SNOWFLAKE /** * 水平分片测试，查看是否按照手机号码将数据分片到两个数据库中 */ @Test void testDatabaseShardingInsert(){ for (int i = 0; i < 5; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("canace"+ i); busStatement.setCustomerPhone(12345678888L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(588)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } for (int i = 0; i < 10; i++) { BusStatement busStatement = new BusStatement(); busStatement.setCustomerName("dddd"+ i); busStatement.setCustomerPhone(99999999999L); busStatement.setActualArrivalTime(new Date()); busStatement.setCreateTime(new Date()); busStatement.setServiceType(0L); busStatement.setTotalAmount(new BigDecimal(688)); busStatement.setInfo("无"); iBusStatementService.save(busStatement); } } /** * 查看两个库两张表数据都查出来了 */ @Test void testDatabaseShardingRead(){ List<BusStatement> list = iBusStatementService.list(); list.forEach(System.out::println); }分片算法自动分片算法取模分片算法 MOD哈希取模分片算法 HASH_MOD基于分片容量的范围分片算法 VOLUME_RANGE基于分片边界的范围分片算法 BOUNDARY_RANGE自动时间段分片算法 AUTO_INTERVAL标准分片算法行表达式分片算法 INLINE时间范围分片算法 INTERVAL基于Cosld的固定时间范围的分片算法 COSID_INTERVAL基于Cosld的雪花ID固定时间范围的分片算法 COSID_INTERVAL_SNOWFLAKE基于Cosld的取模分片算法 COSID_MOD复合分片算法 COMPLEX_INLINEHint分片算法 HINT_INLINE自定义类分片算法详见：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding

MYSQL获取用户连续签到天数 表结构：data date,uid stringSELECT count( 1 ) FROM ( SELECT date_sub( a.data, INTERVAL 1 DAY ) signDate, ( @i := DATE_ADD( @i, INTERVAL - 1 DAY ) ) today FROM ( SELECT data FROM user_dakas WHERE uid = "1" ORDER BY data DESC ) a INNER JOIN ( SELECT @i := max( data ) AS signMax FROM user_dakas WHERE uid = "1" AND ( TO_DAYS( data ) = TO_DAYS( curdate()) OR TO_DAYS( data ) = TO_DAYS( DATE_ADD( curdate(), INTERVAL - 1 DAY ) ) ) ) b WHERE b.signMax IS NOT NULL AND TO_DAYS( DATE_ADD( @i, INTERVAL - 1 DAY )) = TO_DAYS( date_sub( a.data, INTERVAL 1 DAY ) ) ) c