chuixue24的博客

1、回顾一下Buffer Pool是个什么东西？数据库中的Buffer Pool是个什么东西？其实他是一个非常关键的组件，数据库中的数据实际上最终都是要存放在磁盘文件上的，如下图所示。但是我们在对数据库执行增删改操作的时候，不可能直接更新磁盘上的数据的，因为如果你对磁盘进行随机读写操作，那速度是相当的慢，随便一个大磁盘文件的随机读写操作，可能都要几百毫秒。如果要是那么搞的话，可能你的数据库每秒也就只能处理几百个请求了！

先说一下为什么需要备份MySQL数据？如果我们把数据只存储在一个地方，如果物理机器损坏，会导致数据丢失，无法恢复。还有就是我们每次手动修改线上数据之前，为了安全起见，都需要先备份数据。防止人为的误操作，导致弄脏数据或弄丢数据。

适用范围：对表结构进行操作，比如create、drop、alter、rename、truncate等。

一个事务读到其他事务未提交的数据。从上面的示例图中，可以看出，在事务2修改完数据，没有提交的情况。事务1已经读到事务2最新修改的数据，这种情况就属于脏读。

全称是（多版本并发控制），是一种并发控制的方法，通过维护一个数据的多个版本，减少读写操作的冲突。如果没有，想要实现同一条数据的并发读写，还要保证数据的安全性，就需要操作数据的时候加读锁和写锁，这样就降低了数据库的并发性能。有了，就相当于把同一份数据生成了多个版本，在操作的开始各生成一个快照，读写操作互不影响。无需加锁，也实现数据的安全性和事务的隔离性。事务的四大特性中隔离性就是基于实现的。说MVCC的实现原理之前，先说一下事务的隔离级别。

今天要讲的是MySQL的另一种查询性能优化方式 —，是MySQL5.6版本增加的特性。

你以为的答案是（a,b,c），其实答案是6个，abc三个的排列组合，（a,b,c）、（a,c,b）、（b,a,c）、（b,c,a）、（c,a,b）、（c,b,a）。像有些文章上面说的，在（a,b）两个字段上创建联合索引，就会创建两个索引，分别是（a）和（a,b），这其实是一种不恰当的表述，虽然结果是对的。b字段的值是全局无序的，分别有1,3,5,1,3,5，只有在a字段的值相等时才呈现出局部有序。当创建（a,c,b）和 （c,a,b）索引的时候，查询会用到3个字段的索引，效率更高。

但是，问题来了，当我们在where条件中，再加一个参数，变成了10个参数，预估扫描行数本应该增加，结果却大大减少了。当where语句in条件中参数个数大于等于这个值的时候，MySQL就采用另一种方式。where条件中有9个参数，重点关注一下执行计划中的预估扫描行数为279行。一般情况下，我们的where语句的in条件的参数不会太多，适合使用。之前的版本，这个值默认是10，之后的版本，这个值默认是200。实际是310行，预估扫描行数是30行，真是错到姥姥家了。的方式预估扫描行数，非常准确。

上篇文章讲到使用MySQL的Explain命令可以分析SQL性能瓶颈，优化SQL查询，以及查看是否用到了索引。我们都知道创建索引可以提高查询效率，但是具体该怎么创建索引？哪些字段适合创建索引？哪些字段又不适合创建索引？本文跟大家一块学习一下如何创建合适数据库索引。在创建索引之前了解一下MySQL有哪些索引，然后我们才能选择合适的索引。常见的索引有，普通索引、唯一索引、主键索引、联合索引、全文索引等。普通索引就是最基本的索引，没有任何限制。可以使用命令创建普通索引：唯一索引与普通索引不同，唯一索引的

上篇文章讲到使用MySQL的Explain命令可以分析SQL性能瓶颈，优化SQL查询，以及查看是否用到了索引。我们都知道创建索引可以提高查询效率，但是具体该怎么创建索引？哪些字段适合创建索引？哪些字段又不适合创建索引？本文跟大家一块学习一下如何创建合适数据库索引。在创建索引之前了解一下MySQL有哪些索引，然后我们才能选择合适的索引。常见的索引有，普通索引、唯一索引、主键索引、联合索引、全文索引等。普通索引就是最基本的索引，没有任何限制。可以使用命令创建普通索引：唯一索引与普通索引不同，唯一索引的

本文详细介绍了Explain使用方式，以及每种参数所代表的含义。无论是工作还是面试，使用Explain优化SQL查询，都是必备的技能，一定要牢记。下篇再一块学习一下SQL查询的其他优化方式，敬请期待。

本篇文章主要带大家了解了MySQL分层架构，以及每层的架构的作用。可以看出MySQL每层架构分工明确、逻辑清晰，深刻地体现了架构设计中“高内聚，低耦合”的设计思想。我们平时在做架构设计的时候，也要多学习一下这种分层架构的设计思想。

开发经常遇到分页查询的需求，但是当翻页过多的时候，就会产生深分页，导致查询效率急剧下降。有没有什么办法，能解决深分页的问题呢？本文总结了三种优化方案，查询直接效率提升10倍，一起学习一下。先创建一张用户表，只在create_time字段上加索引：然后往用户表中插入100万条测试数据，这里可以使用存储过程：2. 验证深分页问题每页10条，当我们查询第一页的时候，速度很快：在不到0.01秒内直接返回了，所以没显示出执行时间。当我们翻到第10000页的时候，查询效率急剧下降：执行时间变

工作中，经常遇到这样的问题，我明明在MySQL表上面加了索引，为什么执行SQL查询的时候却没有用到索引？同一条SQL有时候查询用到了索引，有时候却没用到索引，这是咋回事？原因可能是索引失效了，失效的原因有以下几种，看你有没有踩过类似的坑？有这么一张用户表，在name字段上建个索引：2. Explain详解：想要查看一条SQL是否用到索引？用到了哪种类型的索引？可以使用explain关键字，查看SQL执行计划。例如：可以看到type=const，表示使用了主键索引。explain的所有type

MySQL主从同步是基于Bin Log实现的，而Bin Log记录的是原始SQL语句。Bin Log共有三种日志格式，可以binlog_format配置参数指定。参数值含义Statement记录原始SQL语句，会导致更新时间与原库不一致。 比如 update_time=now()Row记录每行数据的变化，保证了数据与原库一致，缺点是数据量较大。MixedStatement和Row的混合模式，默认采用Statement模式，涉及日期、函数相关的时候采用Row模式，既减少了数据量，又保证了数据一致性。常见的主从

有了MySQL日志的基础，下篇就可以一块学习MySQL集群和主从同步了。

MySQL锁为了保证数据的安全性，还会向右遍历到不满足条件为止，还会再加一个间隙锁，也就是。看你简历上面写着精通MySQL，我问你一个MySQL锁相关的问题，你看一下这条SQL会对哪些数据加锁？小伙子回答得不错啊。如果已经存在age=5的数据，刚才的那条update语句会对哪些数据加锁？age是非唯一性索引，MySQL的锁是加在索引上面的，应该只会对age=10的数据加锁。当我们执行update语句的时候，age=2和age=8的数据范围都被加锁了。如果不存在id=5的数据，加锁范围跟上条SQL是一样的，

面试开始，直入正题。面试官： 看你简历上面写着精通MySQL，我先问你事务的特性是什么？我： 这个我知道，事务有四大特性，分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），简称ACID。面试官： 嗯，回答得不错。那你知道MySQL底层是怎么实现事务的四大特性？我： 原子性是基于undo log实现的，一致性是由代码逻辑层面保证的，隔离性是由mvcc实现的，持久性是基于redo log实现的。Redo Log记录的是物理日志，也

MySQL有两个核心的知识点，索引和锁。前几篇文章已经详细讲解了MySQL索引实现机制，今天再一起学习一下MySQL的锁。当多个事务并发操作同一批数据的时候，如果不加锁，就无法保证事务的隔离性，最后导致数据错乱。加锁是为了保证并发操作下数据的正确性。按锁的粒度可分为：表锁、页面锁、行锁、记录锁、间隙锁、临键锁按锁的属性可分为：共享锁、排它锁按加锁机制可分为：乐观锁、悲观锁下面依次介绍一下这几种锁：MyISAM和InnoDB引擎均支持表锁。优点：开销小，加锁快，不会出现死锁。缺点：锁定力度大，发生锁冲突概率高

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

我们先来思考一个问题，在Oracle，SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？Why?Why?Why?这个是有历史原因的，当然要从我们的主从复制开始讲起了！主从复制，是基于什么复制的？是基于binlog复制的！这里不想去搬binlog的概念了，就简单理解为binlog是一个记录数据库更改的文件吧～bin

MVCC多版本并发控制机制 Mysql在可重复读隔离级别下如何保证事务较高的隔离性同样的sql查询语句在一个事务 里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。 这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认 是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操 作加锁互斥来实现的。 Mysql在读已提交和可

MySQL的内部组件结构 大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。 Server层 主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数 （如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 Store层存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在 最.

第一种情况：查询所有数据库的总大小，方法如下：mysql> use information_schema;mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;+-----------+| data |+-----------+| 3052.76MB |+-----------+1 row in set (0.02 sec)统计一下所有库数...

mysql的InnoDB引擎的内存和磁盘架构如下图所示：内存区域中，有缓存池Buffer Pool，其结构如下图所示：free链表为空闲链表，保存了buffer pool中的空闲的内存空间，一个控制块对应于一个空白块，大小为一个也的大小，默认为16kb。...

联合索引属于辅助索引。假如有下图的数据，其中a列是主键：如果要创建一个联合索引（b，c，d），那么索引结构类似下图：注意，上图中，叶子节点也存了全部数据，这个结构图示以b，c，d为联合索引建立的，所谓联合索引就是将b，c，d的值连起来以后做排序。需要注意的是，上图中的结构并不是真正的mysql中的结构图，而是类似，在上边的描述中，也用了加粗标红的类似两个字来标注。这里说类似，是因为真正的辅助索引叶子节点不会存全部数据，如果存全部数据，首先占用空间很大，而且数据更新时，辅助索引树也需要..

页结果如下：其中的页目录是为了提高查找用户数据区域的效率。页中存在指针指向上一页和下一页。页目录（页目录的查找是用二分查找算法）和用户数据区域的数据都是有序的。每个也大小默认为16kb。当页很多时，比如下图所示指向select * from t1 where id= 6000；需要遍历页链表，效率很慢，再加上页是从硬盘读出来的，那效率就更慢了。为了提高效率，像位用户数据区加页目录这种方式，为所有页加一个索引页（大小也是16kb），其中所存数据为每一页的最小id。那么，这样的机构就是...

这里虽然说的是Mysql数据库，但对应其他数据库，原理没有什么差异。只是在具体实现和配置上不同。一、异步复制（Asynchronous replication）1、逻辑上MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。2、技术上主库将事务 Binlog 事件写入到 Binlog

一、联接过程介绍为了后面一些测试案例，我们事先创建了两张表，表数据如下： 1 2 3 4 CREATE TABLE t1 (m1 int, n1 char(1)); CREATE TABLE t2 (m2 int, n2 char(1)); INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c'); INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (

事务是MySQL等关系型数据库区别于NoSQL的重要方面，是保证数据一致性的重要手段。本文将首先介绍MySQL事务相关的基础概念，然后介绍事务的ACID特性，并分析其实现原理。MySQL博大精深，文章疏漏之处在所难免，欢迎批评指正。一、基础概念事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。作为一个关系型数据库，MySQL支持事务，本文介绍基于MySQL5.6。首先回顾一下MySQL事务的基础知识。1.

Mysql到底是怎么实现MVCC的？这个问题无数人都在问，但google中并无答案，本文尝试从Mysql源码中寻找答案。 在Mysql中MVCC是在Innodb存储引擎中得到支持的，Innodb为每行记录都实现了三个隐藏字段：6字节的事务ID（DB_TRX_ID） 7字节的回滚指针（DB_ROLL_PTR） 隐藏的ID6字节的事物ID用来标识该行所述的事务，7字节的回滚指针需要了解下Innodb的事务模型。1. Innodb的事务相关概念为了支持事务，Innbodb引...

日志系统主要有redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。图来自极客时间的mysql实践,该图是描述的是MySQL的逻辑架构。redo log日志模块redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和

一 什么是“索引条件下推”“索引条件下推”，称为Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。二“索引条件下推”的目的...

SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHER...

案例分析：select c.*from hotel_info_original c left join hotel_info_collection h on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id where h.hotel_id is null 　　这个s...

MySQL在以下几种情况会创建临时表：1、UNION查询；2、用到TEMPTABLE算法或者是UNION查询中的视图；3、ORDER BY和GROUP BY的子句不一样时；4、表连接中，ORDER BY的列不是驱动表中的；5、DISTINCT查询并且加上ORDER BY时；6、SQL中用到SQL_SMALL_RESULT选项时；7、FROM中的子查询；8、子查询或者semi-j...

MySQL InnoDB B-Tree索引使用Tips这里主要讨论一下InnoDB B-Tree索引的使用，不提设计，只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。这里讨论的均在MySQL-Server-5.1.42测试CREATE TABLE `friends` ( `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `...

语法：explain select ....变体：1.explain extended select ....将执行计划“反编译”成select语句；运行show warnings 可以得到被mysql优化器优化后的语句2.explain partitions select ...用于分区表的explain运行结果含义：type： all ，index，ra...