MySQL语句执行过程及count(*)效率问题
MySQL语句执行过程及count(*)效率问题SQL的执行过程下面我给出的是MySQL的基本架构示意图,并且清楚的展示了SQL语句的执行过程:
连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接。客户端如果太长时间没动静,连接器就会自动将它断开。
查询缓存:将之前查询的结构进行缓存,key是查询语句。但是MySQL 8.0版本直接将查询缓存的整块功能删掉了。
分析器:对SQL语句先进行词法分析再进行语法分析。
优化器:会对语句进行优化判断。
执行器:先校验权限,如果校验通过就会根据表的引擎定义,去使用这个引擎提供的接口,最后将结果组成结果集返回给客户端。
count(*)这么慢你首先要明确的是,在不同的MySQL引擎中,count(*)有不同的实现方式:
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
而InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
由于多版本并发控制(MVCC)的原因,InnoDB表“应该返回多少行”也是不确定的
按照效 ...
MySQL锁
MySQL中的锁根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁全局锁是对整个数据库实例加锁。加了这个锁之后整个数据库都处于只读状态。通过Flush tables with read lock开启。
之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是:做全库逻辑备份。
危害:
如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;
如果你在从库上备份,那么备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。
解决方案(但是仅限于使用事务的引擎,如InnoDB引擎):
所以官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。
表级锁MySQL里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
表锁使用的语法是lock ta ...
MySQL事务
事务隔离级别当数据库上有多个事务同时执行的时候,就可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题,为了解决这些问题,就有了“隔离级别”的概念。
SQL标准的事务隔离级别包括:读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable )。
读未提交:一个事务还没提交时,它做的变更就能被别的事务看到。
读提交:一个事务提交之后,它做的变更才会被其他事务看到。(在执行语句之前创建多版本视图)
可重复读:一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。(在开启事务之前创建多版本视图)
串行化:顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。
通过show variables like 'transaction_isol ...
MySQL索引(二)
MySQL索引(二)一般索引和唯一索引我们来比较一下在不同过程一般索引和唯一索引的区别。
查询阶段:例如:select id from T where k=5并且要在k上面加索引。
结构为:
对于普通索引来说,查找到满足条件的第一个记录(5,500)后,需要查找下一个记录,直到碰到第一个不满足k=5条件的记录。
对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。
但是因为InnoDB的数据是按数据页为单位来读写的,所以多读几条数据对性能的影响是微乎其微的(数据在下一页的情况很少很少)。
更新过程:为了说明普通索引和唯一索引对更新语句性能的影响这个问题,需要先了解一下change buffer。
change buffer:
当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关 ...
MySQL索引
索引索引模型
哈希表
哈希表是一种以键-值(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 ...
RedoLog刷脏页
RedoLog刷脏页你不难想象,平时执行很快的更新操作,其实就是在写内存和日志,而MySQL偶尔“抖”一下的那个瞬间,可能就是在刷脏页(flush)。
什么情况会引发数据库的flush过程呢?
就是InnoDB的redo log写满了。这时候系统会停止所有更新操作,把checkpoint往前推进,redo log留出空间可以继续写。我在第二讲画了一个redo log的示意图,这里我改成环形,便于大家理解。
checkpoint可不是随便往前修改一下位置就可以的。比如图2中,把checkpoint位置从CP推进到CP’,就需要将两个点之间的日志(浅绿色部分),对应的所有脏页都flush到磁盘上。之后,图中从write pos到CP’之间就是可以再写入的redo log的区域。
就是系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘。
就是MySQL认为系统“空闲”的时候。
就是MySQL正常关闭的情况。这时候,MySQL会把内存的脏页都flush到磁盘上,这样下次MySQL启动的时候 ...
Mysql数据库表的空间回收
数据库表的空间回收为什么简单地删除表数据达不到表空间回收的效果?首先讲一下参数innodb_file_per_table,表数据既可以存在共享表空间里(调为OFF),也可以是单独的文件(调为ON)。
我建议你不论使用MySQL的哪个版本,都将这个值设置为ON。因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过drop table命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。
InnoDB里的数据都是用B+树的结构组织的。如果删除数据,InnoDB引擎只会把这个记录标记为删除。可能会复用这个位置。但是,磁盘文件的大小并不会缩小。
现在,你已经知道了InnoDB的数据是按页存储的,那么如果我们删掉了一个数据页上的所有记录,会怎么样?
答案是:整个数据页就可以被复用了。
通过delete命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。实际上,不止是删除数据会造成空洞,插入数据也会。(随机插入会导致,尽量保证索引递增)
解决方法:
可以使用alter table A engine=Inno ...
MySQL的两大日志
两大日志Redo logredo log(重做日志)是InnoDB存储引擎独有的,它让MySQL拥有了崩溃恢复能力。有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。
当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log里面,并更新内存,这个时候更新就算完成了。同时,InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做。
innodb_flush_log_at_trx_commit这个参数设置成1的时候,表示每次事务的redo log都直接持久化到磁盘。
BinlogMySQL数据库的数据备份、主备、主主、主从都离不开binlog(归档日志),需要依靠binlog来同步数据,保证数据一致性。
与redo log的区别:
redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用。
redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个 ...
链路层和局域网
链路层和局域网数据链路层提供相邻两个点的网络传输服务。物理层负责将数字信号转换为01的物理信号,并通过物理媒介(网线,光缆,电缆)传给相邻的网卡。
相关术语:
主机和路由器是节点(网桥和交换机也是):nodes
沿着通信路径,连接个相邻节点通信信道的是链路:links
有线链路
无线链路
局域网,共享性链路
第二层协议数据单元帧:frame
相关服务:
封装成桢,链路接入
将数据报封装在帧中,加上帧头、帧尾部
如果采用的是共享性介质,信道接入获得信道访问权
在帧头部使用“MAC”(物理)地址来标示源和目的
在(一个网络内)相邻两个节点完成可靠数据传递
流量控制
错误检测
差错纠正
差错检测和纠正差错检测(error detection),是指在发送的码序列(码字)中加入适当的冗余度以使得接收端能够发现传输中是否发生差错的技术。
EDC = 差错检测和纠正位(冗余位)
D = 数据由差错检测保护,可以包含头部字段
校验方式:
奇偶校验:根据校验和,检测单个bit级错误,检测和纠正单个bit错误
CRC(循环冗余校验):通过某种数学运算实现有效信息与校验位之间的循环校 ...