2023年1月20日 11:36 周五转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
索引优化相关
- in 代替 or
- not exist 代替 not in
数据库命名规范
#
- 所有数据库对象名称必须使用小写字母并用下划线分割
- 所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）
- 数据库对象的命名要能做到见名识意，并且最好不要超过 32 个字符
- 临时库表必须以
tmp_ 为前缀并以日期为后缀，备份表必须以 bak_ 为前缀并以日期 (时间戳) 为后缀 - 所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）
数据库基本设计规范
#
所有表必须使用InnoDB存储引擎
#
- 没有特殊要求（即 InnoDB 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 InnoDB 存储引擎（MySQL5.5 之前默认使用 Myisam，5.6 以后默认的为 InnoDB）。
- InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好
数据库和表的字符集统一使用UTF-8
#
兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。
参考文章：
所有表和字段都需要添加注释
#
使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护
尽量控制单表数据量的大小，建议控制在500万以内
#
2023年1月20日 11:36 周五 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!====
MySQL基础
#
关系型数据库介绍
#
- 关系型数据库，建立在关系模型的基础上的数据库。表明数据库中所存储的数据之间的联系（一对一、一对多、多对多）
- 关系型数据库中，我们的数据都被存放在各种表中（比如用户表），表中的每一行存放着一条数据（比如一个用户的信息）
- 大部分关系型数据库都使用SQL来操作数据库中的数据，并且大部分关系型数据库都支持事务的四大特性（ACID）
常见的关系型数据库
MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ……
MySQL介绍
#
MySQL基础架构
#
- MySQL的一个简要机构图，客户端的一条SQL语句在MySQL内部如何执行
- MySQL主要由几部分构成
- 连接器：身份认证和权限相关（登录MySQL的时候）
- 查询缓存：执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，因为这个功能不太实用）
- 分析器：没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的SQL语句要干嘛，再检查你的SQL语句语法是否正确
- 优化器：按照MySQL认为最优的方案去执行
- 执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限，就会报错
- 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持InnoDB、MyISAM、Memory等多种存储引擎
MySQL存储引擎
#
MySQL核心在于存储引擎
MySQL支持哪些存储引擎？默认使用哪个？
#
MySQL支持多种存储引擎，可以通过show engines命令来查看MySQL支持的所有存储引擎
默认存储引擎为InnoDB，并且，所有存储引擎中只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事务
这里使用MySQL 8.x
MySQL 5.5.5之前，MyISAM是MySQL的默认存储引擎；5.5.5之后，InnoDB是MySQL的默认存储引擎，可以通过select version()命令查看你的MySQL版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
使用show variables like %storage_engine%命令直接查看MySQL当前默认的存储引擎
...
2023年1月19日 17:10 周四 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
本篇文章基于MySQL 5.7.26，原文：https://www.guitu18.com/post/2019/11/24/61.html
前言
#
- 关于数据库优化，最常见的莫过于索引失效，数据量多的时候比较明显，处理不及时会造成雪球效应，最终导致数据库卡死甚至瘫痪。
- 这里说的是隐式转换造成的索引失效
数据准备
#
-- 创建测试数据表
DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`num1` int(11) NOT NULL DEFAULT '0',
`num2` varchar(11) NOT NULL DEFAULT '',
`type1` int(4) NOT NULL DEFAULT '0',
`type2` int(4) NOT NULL DEFAULT '0',
`str1` varchar(100) NOT NULL DEFAULT '',
`str2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `num1` (`num1`),
KEY `num2` (`num2`),
KEY `type1` (`type1`),
KEY `str1` (`str1`),
KEY `str2` (`str2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 创建存储过程
DROP PROCEDURE IF EXISTS pre_test1;
DELIMITER //
CREATE PROCEDURE `pre_test1`()
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
WHILE i < 10000000 DO
SET i = i + 1;
SET @str1 = SUBSTRING(MD5(RAND()),1,20);
-- 每100条数据str2产生一个null值
IF i % 100 = 0 THEN
SET @str2 = NULL;
ELSE
SET @str2 = @str1;
END IF;
INSERT INTO test1 (`id`, `num1`, `num2`,
`type1`, `type2`, `str1`, `str2`)
VALUES (CONCAT('', i), CONCAT('', i),
CONCAT('', i), i%5, i%5, @str1, @str2);
-- 事务优化，每一万条数据提交一次事务
IF i % 10000 = 0 THEN
COMMIT;
END IF;
END WHILE;
END;
// DELIMITER ;
-- 执行存储过程
CALL pre_test1();
其中，七个字段，首先使用存储过程生成 1000 万条测试数据， 测试表一共建立了 7 个字段（包括主键），num1和num2保存的是和ID一样的顺序数字，其中num2是字符串类型。 type1和type2保存的都是主键对 5 的取模，目的是模拟实际应用中常用类似 type 类型的数据，但是**type2是没有建立索引的。 str1和str2都是保存了一个 20 位长度的随机字符串，str1不能为NULL，str2允许为NULL，相应的生成测试数据的时候我也会在str2字段生产少量NULL值**（每 100 条数据产生一个NULL值）。
...
2023年1月19日 17:10 周四 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
不要用字符串存储日期
#
- 优点：简单直白
- 缺点
- 字符串占有的空间更大
- 字符串存储的日期效率比较低（逐个字符进行比较），无法用日期相关的API进行计算和比较
Datetime和Timestamp之间抉择
#
Datetime 和 Timestamp 是 MySQL 提供的两种比较相似的保存时间的数据类型。他们两者究竟该如何选择呢？
通常我们都会首选 Timestamp
Datetime类型没有时区信息
#
- DateTime 类型是没有时区信息的（时区无关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。这样就会有什么问题呢？当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。不要小看这个问题，很多系统就是因为这个问题闹出了很多笑话。
- Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样
案例
-- 建表
CREATE TABLE `time_zone_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NULL,
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());
-- 查看数据
select date_time,time_stamp from time_zone_test;
-- 结果
/*
+---------------------+---------------------+
| date_time | time_stamp |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+
------
*/
修改时区并查看数据
...
2023年1月19日 10:20 周四 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
原文
https://github.com/kinglaw1204 感谢作者
- 本篇文章会分析一个SQL语句在MySQL的执行流程，包括SQL的查询在MySQL内部会怎么流转，SQL语句的更新是怎么完成的
- 分析之前先看看MySQL的基础架构，知道了MySQL由哪些组件组成以及这些组件的作用是什么，可以帮助我们理解和解决这些问题
MySQL基础架构分析
#
MySQL基本架构概览
#
- 下图是MySQL的简要架构图，从下图可以看到用户的SQL语句在MySQL内部是如何执行的
- 先简单介绍一个下图涉及的一些组件的基本作用
- 连接器： 身份认证和权限相关（登录MySQL的时候）
- 查询缓存：执行查询语句的时候，会先查询缓存（MySQL8.0版本后移除，因为这个功能不太实用）
- 分析器：没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的SQL语句干嘛，再检查你的SQL语句语法是否正确
- 优化器：按照MySQL认为最优的方案去执行
- 执行器：执行语句，然后从存储引擎返回数据
- 简单来说 MySQL 主要分为 Server 层和存储引擎层：
- Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
- 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了
Server层基本组件介绍
#
连接器
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样
主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即使管理员修改了该用户的权限，该用户也是不受影响的。
查询缓存（MySQL8.0 版本后移除）
查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。
连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 SQL 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。
MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。
MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了
...
2023年1月16日 19:23 周一 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
一致性非锁定读和锁定读
#
一致性非锁定读
#
★★非锁定★★
- 对于一致性非锁定读（Consistent Nonlocking Reads）的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号+1或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见
- InnoDB存储引擎中，多版本控制（multi versioning）即是非锁定读的实现。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会去等待行上 锁的释放.相反地，Inn哦DB存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读（snapshot read）。
- 在
Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读
锁定读
#
2023年1月16日 01:00 周一 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
事务隔离级别总结
#
SQL标准定义了四个隔离级别
- READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
- READ-COMMITED(读取已提交)：允许读取并发事务 已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
- REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
- SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|
| READ-UNCOMMITTED | √ | √ | √ |
| READ-COMMITTED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）
使用命令查看，通过SELECT @@tx_isolation;。
MySQL 8.0 该命令改为SELECT @@transaction_isolation;
MySQL> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
从上面对SQL标准定义了四个隔离级别的介绍可以看出，标准的SQL隔离级别里，REPEATABLE-READ(可重复读)是不可以防止幻读的。但是，InnoDB实现的REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，分两种情况
- 快照读：由MVCC机制来保证不出现幻读
- 当前读：使用Next-Key Lock进行加锁来保证不出现幻读，Next-Key Lock是行锁（Record Lock ）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁 (只用间隙锁不行，因为间隙锁是 > 或 < ，不包括等于，所以再可重复读下原记录可能会被删掉)
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失。
...
2023年1月14日 17:31 周六 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
前言
#
2023年1月10日 22:09 周二 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
补充索引基础知识(引自b站sgg视频)
#
- 存储引擎，数据的基本单位是页，如果数据很少，只有一页，那就简单，是直接二分查找(不涉及磁盘IO)；如果数据很多，有好几个页，那么需要对页建立一种数据结构，能够最快定位到哪一页，然后减少磁盘IO
索引介绍
#
索引的优缺点
#
优点：
- 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间
索引一定会提高查询性能吗
- 多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升
索引的底层数据结构
#
Hash表
#
哈希表是键值对的集合，通过键（key）即可快速取出对应的值（value），因此哈希表可以快速检索数据（接近O(1))
为何能够通过key快速取出value呢？原因在于哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到key对应的index，找到了index也就找到了对应的value
hash = hashfunc(key)
index = hash % array_size
注意，图中keys[天蓝色]是字符串，不是什么莫名其妙的人
哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
为了减少 Hash 冲突的发生，一个好的哈希函数应该**“均匀地”将数据分布**在整个可能的哈希值集合中
由于Hash索引不支持顺序和范围查询，假如要对表中的数据进行排序或者进行范围查询，那Hash索引就不行了，并且，每次IO只能取一个
例如： SELECT * FROM tb1 WHERE id < 500 ;
...
2023年1月10日 16:48 周二 转载自https://github.com/Snailclimb/JavaGuide（添加小部分笔记）感谢作者!
图示总结
- MySQL字符编码集有两套UTF-8编码实现：utf-8 和 utf8mb4
而其中，utf-8 不支持存储emoji符号和一些比较复杂的汉字、繁体字，会出错
何为字符集
#
有哪些常见的字符集
#
- 常见的字符集有ASCLL、GB2312、GBK、UTF-8
- 不同的字符集的主要区别在于
- 可以表示的字符范围
- 编码方式
ASCLL
#
ASCII (American Standard Code for Information Interchange，美国信息交换标准代码) 是一套主要用于现代美国英语的字符集（这也是 ASCII 字符集的局限性所在）
为什么 ASCII 字符集没有考虑到中文等其他字符呢？ 因为计算机是美国人发明的，当时，计算机的发展还处于比较雏形的时代，还未在其他国家大规模使用。因此，美国发布 ASCII 字符集的时候没有考虑兼容其他国家的语言
ASCII 字符集至今为止共定义了 128 个字符，其中有 33 个控制字符（比如回车、删除）无法显示
一个 ASCII 码长度是一个字节也就是 8 个 bit，比如“a”对应的 ASCII 码是“01100001”。不过，最高位是 0 仅仅作为校验位，其余 7 位使用 0 和 1 进行组合，所以，ASCII 字符集可以定义 128（2^7）个字符
由于，ASCII 码可以表示的字符实在是太少了。后来，人们对其进行了扩展得到了 ASCII 扩展字符集 。ASCII 扩展字符集使用 8 位（bits）表示一个字符，所以，ASCII 扩展字符集可以定义 256（2^8）个字符
...
