MySQL

参考文献 极客时间–SQL必知必会(陈旸) 数据库缓冲池 缓冲池是如何读取数据的? 缓冲池管理器会尽量将经常使用的数据保存起来,在数据库在进行页读操作的时候,首先会判断该页是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或磁盘将页存放到缓冲池中,再进行读取. 缓存在数据库中的结构和作用如下图 执行SQL语句的时候更新了缓冲池的数据,那么这些数据是否会同步到磁盘上? 对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上.注意并不是发生更新操作,都会立即进行磁盘回写.缓冲池会采用与一种叫做checkpoint的机制将数据写到磁盘上,这样做的好处就是提升了数据库的整体性能. 当缓冲池不够用时,相应释放掉一些不常用的页,就可以强行采用checkpoint的方式,将不常用的脏页写会到磁盘上,然后再从缓冲池中将这些页释放掉. 脏页指的是缓冲池中被修改过的页,与磁盘上的数据页不一致. 查看缓冲池的大小 MySQL MyISAM存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为key_buffer_s ...

参考文献 MySQL:互联网公司常用分库分表方案汇总！ 为什么要分表 不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值.在业务Service来看就是,可用数据库连接少甚至无连接可用.接下来就可以想象了吧(并发量、吞吐量、崩溃). IO瓶颈 磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度 -> 分库和垂直分表. 网络IO瓶颈,请求的数据太多,网络带宽不够 -> 分库. CPU瓶颈 SQL问题,如SQL中包含join,group by,order by,非索引字段条件查询等,增加CPU运算的操作 -> SQL优化,建立合适的索引,在业务Service层进行业务计算. 单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 -> 水平分表. 分库分表 水平分库 概念: 以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中 将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表 ...

参考文献 极客时间–MySQL实战45讲(林晓斌) 极客时间–SQL必知必会(陈旸) 解决死锁之路 - 学习事务与隔离级别 MySQL日志15连问,redo log与biglog 数据库事务隔离发展历史 MVCC 原理 图文解读MySQL InnoDB Undo log The basics of the InnoDB undo logging and history system ACID Atomicity原子性 事务是最小的执行单位,不允许分割.事务的原子性确保动作要么全部完成,要么完全不起作用; Consistency 一致性 一致性指事务将数据库从一种状态变为下一种一致的状态.在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏. 例如数据库表中有个字段有唯一性约束,事务中对该字段进行了修改,但在事务提交或事务操作发生回滚后,该字段的值变得不唯一了,这就破坏了事务的一致性要求,即事务将数据库从一种状态变为了一种不一致的状态. 事务的一致性的单位,如果事务中某个动作失败了,系统可以自动撤销事务–返回初始化的状态. Isolation 隔离性 ...

参考文献 高性能MySQL(第三版) 极客时间–MySQL实战45讲 数据库索引设计与优化 表和索引结构 索引页和表页 表和索引行都被存储在页中.页的大小仅仅决定了一个页可以存储多少个索引行,表行,以及一共需要多少个页来存储表或者索引. 当表和索引被加载或重组时,每个页都会预留出一定比例的空闲空间,以满足向其添加新的表行或索引行的需求. 缓冲池和I/O活动都是基于页的.例如一次将一个完整的页读取到缓冲池.这意味着一次I/O会读入多条记录到缓冲池,而不仅仅是一条. 索引行 索引行在评估访问路径的时候是一个非常拥有的概念. 对于唯一索引,一个索引行等同于叶子页中的一个索引条目.字段的值从表中复制到索引上,并加上一个指向表中记录的指针.通常,表页的编号是这个指针的组成部分. 对于非唯一索引,一个特定的索引值对应的索引行一个被想象成独立的索引条目,每一个都含有相同的值,但是却有不同的指针.大多数情况下,非唯一索引的实际存储方式是一个值后带着多个指针. 索引结构 非叶子页通常包含着一个键值,以及一个指向下一层级的指针,该键值是下一层级页中的最大键值. 表行 每一个索 ...

参考文献 高性能MySQL(第三版) 极客时间–MySQL实战45讲 数据库索引设计与优化 MySQL 优化之 index merge(索引合并) 索引 概念 关键字与数据的映射关系称为索引(包含关键字和对应的记录在磁盘中的地址).关键字是从数据当中提取的用于标识、检索数据的特定内容. 索引的出现是为了提高查询的效率; 索引用来快速地寻找那些具有特定值的记录.如果没有索引,一般来说执行查询时遍历整张表. 索引的原理很简单,就是把无序的数据变成有序的查询 把创建了索引的列的内容进行排序 对排序结果生成倒排表 在倒排表内容上拼上数据地址链 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据 索引检索为什么快？ 关键字相对于数据本身,数据量小 关键字是有序的,二分查找可快速确定位置 索引操作 创建索引 在创建表的时候对字段进行指定索引 12345678CREATE TABLE user_index ( id INT auto_increment PRIMARY KEY, first_name VARCHAR ( 16 ), last_name VARC ...

参考文献 MySQL 8 Cookbook 存储过程 存储的函数(function)和过程(procedure)都称为存储例程(routine); 要创建存储过程,应该具有CREATE ROUTINE权限 存储函数有返回值,存储过程没有返回值. 所有代码都写在BEGIN和END之间 存储函数可以直接在SELECT语句中调用 可以使用CALL语句调用存储过程. 123456789101112131415161718-- 修改最外层语句结束符delimiter 自定义结束符号 SQL语句自定义结束符号delimiter ; -- 修改回原来的分号-- 语句块包裹begin 语句块end-- 特殊的执行1. 只要添加记录，就会触发程序。2. Insert into on duplicate key update 语法会触发： 如果没有重复记录，会触发 before insert, after insert; 如果有重复记录并更新，会触发 before insert, before update, after update; 如果有重复记录但是没有 ...

MySQL常见函数 字符函数 数值运算符与函数 比较运算符与函数 日期时间函数 信息函数 聚合函数 加密函数 字符函数 函数名称 描述 CONCAT() 字符连接 CONCAT_WS() 使用指定的分隔符进行字符连接 FORMAT() 数字格式化函数 LOWER() 转换为小写字母 UPPER() 转换为大写字母 LEFT() 获取左侧字符 RIGHT() 获取右侧字符 LENGTH() 获取字符串长度 LTRIM() 删除前导空格 RTRIM() 删除后导空格 SUBSTRING() 字符串截取 [NOT] LIKE 模式匹配 REPLACE() 字符串替换 数值运算符与函数 函数名称 描述 CEIL() 进一取整(向上取整) FLOOR() 舍一取整(向下取整) DIV 整数除法 MOD 取模 POWER() 幂运算 ROUND() 四舍五入 TRUNCATE() 数字截取 ABS() 取绝对值 比较运算符与函数 函数名称 描述 [NOT] BETW ...

参考文献 MySQL 数据库面试题(2021最新版) 极客时间–MySQL实战45讲 MySQL技术内幕 InnoDB存储引擎 MySQL 设计与开发规范,很详细,你该注意了 MySQL技术内幕 SQL编程 SQL四种语言: DDL,DML,DCL,TCL DDL DDL(Data Definition Language ) 数据库定义语言 statements are used to define the database structure or schema. DDL是SQL语言的四大功能之一.用于定义数据库的三级结构,包括外模式、概念模式、内模式及其相互之间的映像,定义数据的完整性、安全控制等约束DDL 不需要commit. 123456CREATEALTERDROPTRUNCATECOMMENTRENAME DML DML(Data Manipulation Language)数据操纵语言statements are used for managing data within schema objects. 由DBMS提供,用于让用户或程序员使用, ...

MySQL中utf8与utf8mb4的区别 utf8mb4是MySQL在5.3.3后增加的编码,其中mb4的意思是: most byte 4,专门兼容四字节的Unicode 原来的MySQL支持的utf8编码最大字符长度为3字节,若遇到4字节的宽字符就会插入异常 3字节的utf8最大能编码的Unicode字符为Oxffff,即Unicode中基本多文种平面(BMP),即任何不在基本多文种平面的Unicode字符都无法使用MySQL的utf8字符集进行存储,包括Emoji表情和很多不常用的汉字 当使用utf8字符集时,需要保留长度就是utf8最长字符乘以字符串长度,例CHAR(100),MySQL会保留300字节长度 utf8升级utf8mb4步骤 首先将数据库默认字符集有utf8改为utf8mb4,对应的表默认字符集也要改为utf8mb4,已经存储的字段默认字符集也要进行相应调整; 123456-- 修改数据库ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;- ...

参考文献 命令行接口 mysql --column-type-info 显示结果集元数据 123456789101112131415161718192021-- 字段名Field 11: `produced_time`-- 目录名称Catalog: `def`-- 数据库名称Database: `test`-- 数据表名称, 当使用select field_name from table_name as alias_name语法时,这里显示的是表的别名Table: `orders`-- 原始表名,当前面行显示的别名,知道这个就非常有用了Org_table: `orders`-- 前面的行显示字段类型Type: DATETIME-- 排序规则Collation: binary (63)-- 表定义中定义的字段长度Length: 26-- 返回结果集字段长度的最大值Max_length: 0-- 如果是一个整数类型,则表示该字段中小数点后的位数Decimals: 6Flags: BINARY MySQL沙箱 参考文献 ...