Xjzzon的博客

《MySQL实战45讲》读后笔记MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。Innodbbin logMySQL的Server层的日志，binlog只能用于归档，不能用于crash-safe，所以MyISAM不具备crash-safe能力。redolog和binlog比较:1.redolog是物理日志,记录的是某一页做了什么修改。binlog是逻辑日志，记录了sql。2.redoglog会循环覆盖，binlo

MySQL物理文件的概念一行数据在磁盘上存储的时候，包含哪些东西？变长字段的长度列表，null值列表，数据头，column01，column02的值，column0n的值如果有多个变长字段，如何存放他们的长度?varchar(10) varchar(5) varchar(20) char(1) char(1)，一共五个字段，其中三个是变长字段。磁盘中存储的，必须在他开头的变长字段列表中存储几个变长字段的长度，是逆序存储。带NULL值是如何存储?一行数据里的NULL值不能直接存储，因为没必要，

线上数据库不确定性的性能抖动优化实践可能导致性能抖动的两个原因:执行一个查询语句，需要查询大量的数据到缓存页里去，此时就可能导致内存里大量的脏页需要淘汰出去刷入磁盘上，才能腾出足够的内存空间来执行这条查询语句。所以语句可能出现延迟，因为要等待大量脏页flush到磁盘，然后语句才能执行!redo log的日志文件在全部被写满的情况下，也会触发一次脏页的刷新。为什么呢？因为假设第一个日志文件的一些redo log对应的内存里的缓存页的数据没被刷新到磁盘缓存页中，如果mysql崩溃了，也没有 redo l

联合索引查询原理以及全值匹配规则等值匹配规则，和where字段的顺序无关最左侧列匹配，意味着不能跳过字段最左前缀匹配，意味着like只能匹配左确定部分范围查找规则，对联合索引最左侧的列进行范围查询才能使用索引。等值匹配+范围匹配当我们在SQL里进行分组时，如何才能使用索引对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来，这样的话，可以运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以。本质上就是利用索引已经排好序的特点，避免针对

MySQL生产环境部署架构常用的分库分表架构按业务id分库分表，建立索引映射表同时进行分库分表，数据同步到ES做复杂搜索。分库分表下如何分页假设用户现在要查询自己的订单，同时订单要求要支持分页，该怎么做？方案一:因为同一个用户的订单可能是分布在不同的库不同表的，需要建立一个索引表，(user_id, order_id),这个表对user_id进行分库分表后，同一个用户的数据就会进入到同一张表里，既然在同一张表里就可以利用mysql自带的分页机制。方案二:加载到内存之中，在内存中分页。对于主

执行计划和性能优化const：意味着性能超高的常量级，直接可以通过聚簇索引或者二级索引+聚簇索引回源，轻松查到数据。这种情况的二级索引，必须是唯一索引。ref:select * from table where name = xxx,如果name是普通索引，则是ref。如果包含多个列的普通查询，那么必须是索引最左侧开始连续多个列都是等值比较才可以是属于ref方式。针对name is NULL，即使name是主键或者唯一索引，还是只能走ref，但是如果针对一个二级索引同时比较了一个值还有限定了IS NU

通过explain命令得到SQL执行计划ref:既然你是针对二级索引进行等值匹配，那么跟index_x1索引进行等值匹配的是什么？是一个常量或者是别的字段。const则是指常量值跟index_x1索引里的值进行等值匹配。rows:意思是返回给MySQL服务层的数据条数，即在数据库引擎层筛选后的数据。filtered:13.0，意思是MySQL服务层过滤掉部分，即rows经过其他条件筛选之后得到的数据量。rows * fitlered = 最终数据量。extra: using index意味着只是仅仅

一条更新SQL的执行全过程为什么不将修改落盘,而是使用BufferPool？因为数据库的update操作是随机IO，随机IO效率很低，如果直接落盘，那么MySQL支持的并发数量可能就会很少，而redo日志这些事顺序IO，比随机IO性能高。undo日志做什么用？各种日志如何控制大小，何时会去删除？普通的Java应用系统部署在机器上能抗多少并发?以大量的高并发线上系统的生产经验观察而言，一般Java应用系统部署在4核8G的机器上，每秒钟抗下500左右的并发访问量，差不多是比较合适的。但是如果你每个.

Undo日志INSERT语句的undo log日志对应的类型是TRX_UNDO_INSERT_REC这条日志的开始位置主键的各列长度和值表idundo log日志编号undo log日志类型这条日志的结束位置假设现在需要回滚一条insert语句，只需要把undo log拿出来，就知道在哪个表里插入的数据，主键是什么，直接定位到哪个表和主键对应的缓存页，从里面删除掉之前insert语句插入进去的数据就可以了。MySQL运行时多个事务同时执行read uncommitted，读未提交

数据库命令规范所有数据库对象必须使用小写字母并是用下划线分割所有数据库对象名称禁止使用MySQL的保留关键字(否则查询时需要将其用单引号括起来)数据库对象的命名要清晰临时表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期为后缀所有存储相同数据的列名和类型必须一致，因为作为关联列，不一致可能会造成索引失效数据库基本设计规范所有表必须使用Innodb存储引擎数据库和表的字符集统一使用UTF8，emoji表情需要使用utf8mb4字符集所有表和字段都需要注释尽量控制

IS NULL 的影响不影响使用索引，MySQL可以对col_name IS NULL执行与col_name=constant_value相同的优化。例如，MySQL可以使用索引和范围来使用IS NULL搜索NULL。例子SELECT * FROM tbl_name WHERE key_col IS NULL;SELECT * FROM tbl_name WHERE key_col ...

数据库事务四大特性原子性 隔离性 持久性 一致性原子性一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。一致性事务操作成功后，数据库所处的状态和他的业务规则是一致的，即数据不会被破坏。如A账户转账100元到B账户，不管操作成功与否，A和B账户的存款总额是不变的。隔离性事务之间有一定的隔离级别，保障不同程度的数据一致性。持久性一旦事务提交成...

EXPLAIN用法-- 实际SQL，查找用户名为Jefabc的员工select * from emp where name = 'Jefabc';-- 查看SQL是否使用索引，前面加上explain即可explain select * from emp where name = 'Jefabc';[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n4WOJe...

MySQL慢查询定位方式:查看是否开启慢查询功能:mysql> show variables like 'slow_query%';+---------------------+------------------------------------+| Variable_name | Value |+---...

Mysql的日期类型mysql有三种日期类型:Date/DateTime/TimestampDate格式数据存储格式 YYYY-MM-DD，它支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’如果插入带了time字段，它会丢弃该部分的内容，并提示一个warningDatetime格式数据存储格式YYYY-MM-DD HH:MM:SS...

外键InnoDB是目前MySQL中唯一支持外键的内置存储引擎，外键引用的列应该具有逐渐约束或者唯一性约束。使用外键的成本:外键通常要求每次在修改数据时都要在另外一张表多执行一次查找操作。虽然外键有索引，但是如果选择性外键列选择性比较低，其实索引无意义。如果向字表中写入一条记录外键约束会让InnoDB检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会再这个事务完...

视图进阶视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问，它返回的数据是MySQL从其他表生成的。视图和表示在同一个命名空间，很多时候两者等同对待，但是不能对视图创建触发器，也不能使用DROP TABLE命令删除视图。创建视图CRATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH ...

分区表对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。在下面的场景中,分区可以起到非常大的作用:表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。分区表的数据更容易维护。例如，想批量删除大量...

选择正确数据类型的依据:1、更小的通常更好,因为它们占用更少的磁盘、内存和CPU缓存。2、简单的数据类型2、避免使用null，

剖析服务器负载方法步骤:1、捕获MySQL的查询到日志文件，建议使用慢查询日志2、根据查询日志生产剖析报告，利用pt-query-digest可将日志生成报告。根据上述方法可以定位到比较慢的sql，进行单条查询问题剖析剖析单条查询使用show profile测量语句耗费的时间1、set profiling = 1;2、show profiles;3、show profile fo...

为什么查询会慢查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中"执行"可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。优化数据访问对于低效的查询，通过下面两个步骤来分析总是很有效:1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问...

索引是在存储引擎层而不是服务器层实现的B-Tree索引B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。可以使用B-Tree索引的查询类型:全键值、键值范围、或键前缀查找。CREATE TABLE People (last_nam...

一、为什么要分库分表分表单表到达几千万数据的时候，会极大影响sql执行的性能,将一个表的数据放到多个表中，这样可以控制每个表的数据量在可控范围内。分库一个库按经验而言，最多支撑到并发2000，一定要扩容，而且一个健康的单库并发值最好保持在每秒1000左右，不要太大。总结分表能够解决数据量过大带来的查询效率下降的问题;分库可以解决面对高并发的读写访问压力的问题。二、如何对数据库进行垂直...

此篇博客主要是讲述MySql(仅限innodb)的两阶段加锁(2PL)协议,而非两阶段提交(2PC)协议,区别如下:2PL,两阶段加锁协议:主要用于单机事务中的一致性与隔离性。2PC,两阶段提交协议:主要用于分布式事务。MySql本身针对性能，还有一个MVCC(多版本控制)控制,本文不考虑此种技术，仅仅考虑MySql本身的加锁协议。什么时候会加锁在对记录更新操作或者(select ...

索引的管理索引的增删改:增加:CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length));ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length));CREATE TABLE `table`...

1.1MySQL服务器逻辑架构图第一层:连接处理、授权认证、安全等；第二层:MySQL的核心层，包括查询解析、分析、优化、缓存以及所有的内置函数(例如,日期、日期、数学和加密函数)，所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。第三层:存储引擎，负责MySQL中数据的存储和提取。存储引擎API包含十几个底层函数,用于执行诸如"开始一个事务&