goMars的学习随记

短时提供性能的方法短连接风暴正常的短连接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。 解决方案：第一种方法：先处理掉那些占着连接但是不工作的线程：怎么判断哪些是事务外空闲的呢？ 执行show processlist 图中id=4和id=5的两个会话都是Sleep 状态。而要看事务具体状态的话，你可以查information_schema库的innodb_trx表。 这个结果里，trx_mysql_thread_id=4，表示id=4的线程还处在事务中。 因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。 从服务端断开连接使用的是kill connection + id的命令， 一个客户端处于sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server du ...

幻读问题这里，我需要对“幻读”做一个说明： 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。 产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是**间隙锁(Gap Lock)**。 这样，当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。但是，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。所以间隙锁是在可重复读隔离级别下才会生效的 间隙锁的加 ...

某些执行单条语句很慢的场景第一类：查询长时间不返回 等MDL锁： 出现这个状态表示的是，现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了。 等flush： 出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。 等行锁： 由于访问id=1这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的select语句就会被堵住。 第二类：查询慢 行数多导致的慢查询： mysql> select * from t where c=50000 limit 1;由于字段c上没有索引，这个语句只能走id主键顺序扫描，因此需要扫描5万行。 由于mysql的mvcc导致的慢查询： mysql> select * from t where id=1；虽然扫描行数是1，但执行时间却长达800毫秒。 例如session A先用start transaction with consistent snapshot命令启动了一个事务，之后sessio ...

“orderby”是怎么工作的例如： select city,name,age from t where city='杭州' order by name limit 1000 ; 全字段排序：MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。 通常情况下，这个语句执行流程如下所示 ： 初始化sort_buffer，确定放入name、city、age这三个字段； 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X； 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中； 从索引city取下一个记录的主键id； 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y； 对sort_buffer中的数据按照字段name做快速排序； 按照排序结果取前1000行返回给客户端。 图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。 sort_buffer_size ...

MySQL语句执行过程及count(*)效率问题SQL的执行过程下面我给出的是MySQL的基本架构示意图，并且清楚的展示了SQL语句的执行过程： 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。客户端如果太长时间没动静，连接器就会自动将它断开。 查询缓存：将之前查询的结构进行缓存，key是查询语句。但是MySQL 8.0版本直接将查询缓存的整块功能删掉了。 分析器：对SQL语句先进行词法分析再进行语法分析。 优化器：会对语句进行优化判断。 执行器：先校验权限，如果校验通过就会根据表的引擎定义，去使用这个引擎提供的接口，最后将结果组成结果集返回给客户端。 count(*)这么慢你首先要明确的是，在不同的MySQL引擎中，count(*)有不同的实现方式： MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高； 而InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。 由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的 按照效 ...

MySQL中的锁根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。 全局锁全局锁是对整个数据库实例加锁。加了这个锁之后整个数据库都处于只读状态。通过Flush tables with read lock开启。 之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。 全局锁的典型使用场景是：做全库逻辑备份。 危害： 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆； 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。 解决方案（但是仅限于使用事务的引擎，如InnoDB引擎）： 所以官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。 表级锁MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。 表锁使用的语法是lock ta ...

事务隔离级别当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。 SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。 读提交：一个事务提交之后，它做的变更才会被其他事务看到。（在执行语句之前创建多版本视图） 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（在开启事务之前创建多版本视图） 串行化：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。 通过show variables like 'transaction_isol ...

MySQL索引(二)一般索引和唯一索引我们来比较一下在不同过程一般索引和唯一索引的区别。 查询阶段：例如：select id from T where k=5并且要在k上面加索引。 结构为： 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。 但是因为InnoDB的数据是按数据页为单位来读写的，所以多读几条数据对性能的影响是微乎其微的（数据在下一页的情况很少很少）。 更新过程：为了说明普通索引和唯一索引对更新语句性能的影响这个问题，需要先了解一下change buffer。 change buffer： 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关 ...

索引索引模型 哈希表 哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。但是不可避免的就是哈希冲突，常用的是用拉链法（每个key节点后面拉一个链表） 缺点：只适用于等值查询的场景 有序数组 一个按照一定顺序排序的数组，有序数组在等值查询和范围查询场景中的性能就都非常优秀。 缺点：只适用于静态存储引擎，因为插入删除效率很低。 搜索树 二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。为了维持O(log(N))的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。但是二叉树的存储量太低了，所以一般使用的都是N叉树；例如B树、B+树。 InnoDB 的索引模型在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。 例子： 创表语句： 12345678mysql> create table T (ID int primar ...

日志的写入机制binlog的写入机制binlog的写入逻辑比较简单： 事务执行过程中，先把日志写到binlog cache， 事务提交的时候，再把binlog cache写到binlog文件中。 系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。 write 和fsync的时机，是由参数sync_binlog控制的： sync_binlog=0：表示每次提交事务都只write，不fsync；(一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。) sync_binlog=1：表示每次提交事务都会执行fsync； **sync_binlog=N(N>1)**：表示每次提交事务都write，但累积N个事务后才fsync。(对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。) redo log的写入机制redo log可能存在的三种状态说起： 存在redo log ...