mysql实战

mysql逻辑架构

mysql处理流程 客户端->连接器->分析器->查询缓存->优化器->执行器->存储引擎

整个结构大致可分为server层存储层,不同的存储引擎使用同一个server层

存储引擎 作用:存储数据,提供读写接口;

连接器 作用: 管理连接,权限验证,我们经常使用(mysql -h ip -P port -u user -p pw)指令来进行server的连接 这一步不通过的会提示Access denied for user 的错误

一个用户如果建立连接后,即使管理员账户对这个用户权限做了修改,也不会影响已经存在的连接的权限,修改完成后新建的连接才会使用新的权限设置

(show processlist)指令查询连接用户的信息和状态

客户端如果太长时间没有动静,连接器会自动断开,这个时间由wait_timeout控制,默认8小时,连接被断开后会收到lost connection to mysql server during query的错误

长连接:连接成功后,客户端持续有请求,则一直使用同一个连接 短连接:每次执行完很少的几次查询就断开连接,下次查询再重新建立一个

建立连接的过程通常比较复杂,建议使用长连接,但是长连接会导致mysql占用内存涨得特别快,由于sql执行中的使用临时内存是管理连接对象里面的,最终导致为了释放内存,mysql会被异常重启,解决方案(1定期断开长连接 2 5.7版本后每次执行较大操作后,通过mysql——reset_connection重新初始化连接资源,而且不用重连和重新做权限验证)

查询缓存 查询请求会被以key-value存储在内存中,key是查询语句,value是查询结果,如果在查询中 命中则直接返回结果.建议不要使用查询缓存,如果必要使用query_cache_type设置成DEMAND进行按需获取缓存,select SQL_CACHE * FROM T WHERE ID =10; MYSQL8后完全删除了查询缓存模块

分析器 作用: 词法分析,语法分析.通过词法和语法分析识别语句的类型,解析表,字段,验证语法规则等操作,这一步不通过的会提示(sql syntax)的错误

优化器 作用:执行计划生成,索引选择.优化器进行索引的选择,多表连接时表连接顺序的选择等操作

执行器 作用:操作引擎,返回结果.开始执行时,要先判断你对操作表有没有执行查询的权限,若没有会返回(*select command denied to user ** for table **).然后打开表,根据表的引擎定义,使用引擎提供的接口,进行表数据一行一行的判断和读取,在慢查日志汇总有一个row_wxamined字段,表示这个语句扫描了多少行,但是这个并不完全正确的。

myql数据库的数据更新技术-WAL

再生产中,想恢复某个数据时间点的数据,除了通过备份数据外,还可通过redo log和binlog进行重放

redo log 和binlog mysql更新操作采用WAL(write-ahead logging)技术,使用 redo log,先将更新写入日志,空闲时在写入磁盘,表示记录这个数据页做了什么改动 Innodb的redo log是一组4个文件,每个文件1GB大小空间,有两个标志位wirte poscheckpoint

此处输入图片的描述

write pos表示当前记录的位置,checkpoint表示当前要擦除的位置,wirite pos不断的向后移动写入redo log, checkpoint的不断将rodo log数据写入磁盘,当writelog追上checkpoint后,就要等待checkpoint清理出一片空间后才能继续写入 从而保证了数据库发生异常重启,之前的记录都不会消失,被称为crash-safe,redo log作为引擎层特有日志

innodb_flush_log_at_trx_commit 设置成1,表示每次事务的redo log都直接持久化到磁盘。

server层由binlog做归档日志,记录所有的逻辑操作,所有引擎都可以使用,以前写的日志不会覆盖,写在redolog pre 和redolog commit之间,两阶段提交能够保证了数据存储的有效性。binlog有两种模式,记sql语句和记录行行内容前后的状态

redo log和bin log写入发生异常及恢复 此处输入图片的描述

如图:一条数据在写入的时候binlog和redolog的写入过程

那么mysql通过什么值得binlog是否完整的呢?1statement的binlog,最后会有commit;2row格式的binlog,最后会有一个XID event. mysql5.6.2版本后还引入了binlog-checksum参数,用于验证内容正确性 redolog和binlog通过什么关联?通过XID标识关联,binlog在写入后就会从从库取出来,所以主库也要通过redolog提交这个事务,从而保证数据一致性.

数据库恢复: 1首先找到最近的一次全量备份 2从上次开发备份时间开始,将备份的binlog依次取出来,进行重放。

sync_binlog 参数设置为1 表示每次事务的binlog都持久化到磁盘,保证mysql异常重启后binlog不丢失。

数据库隔离级别实现

未提交读,直接返回记录上的最新值

提交读和可重复读是使用视图来访问数据,可重复读是在事务启动的时候创建的,读提交级别是在每个sql启动时创建的。

串行化时直接进行加锁

每条记录在更新的时候会同时记录一条回滚操作,不同时刻启动的事务会有不同的read-view,在不同view里面一条记录有不同的值,这就是数据库多版本控制(MVCC). 当系统中没有比回滚日志更早的view时,日志就会删除,长事务会保存很老的事务视图,占用大量的存储空间。

事务启动方式

  1. set autocommit=0,关闭自动提交事务,然后使用commit提交事务
  2. 显式使用 begin/start transaction语句

查询大于60s的长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

索引搜索模型

常见索引类型 哈希表:通过链表结构存储索引数据,等值搜索很快,但是区间查找效率就不高 有序数组:等值和,区间查找效率都很高,但是不利于数据的更新,插入,删除记录会挪动其他数据,成本太高,只适合用于静态存储引擎。 搜索树:每一个索引在innodb里面对应一棵B+树。

索引的工作原理以及相关知识

全局锁,表级锁,行级锁

全局锁实现方式

表级锁实现方式

注意

热点查询表中执行DDL,很有可能造成整个数据库挂掉,原因是执行DDL线程时会阻塞后面的请求,而如果DDL线程前面的请求一直不释放MDL锁的话,客户端的不断重试,生成新session,数据库线程爆满。这种情况在做DDL时,先将长事务kill掉 事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

解锁: unlock tables

行级锁实现方式

两阶段锁协议 在innodb事务中,行锁是在需要的时候才加上的,但并不是不需要就立刻释放,而是等待事务结束后才释放.基于这个原理,在一个事务处理中,合理安排sql执行顺序减少事务之间的锁等待,能够有效的提高并发度

死锁 死锁的产生是由于事务之间的资源调用互相占用造成的,虽然可以通过执行相同的资源调用顺序来规避,但是我们在平时的开发中去记住各个资源的调用顺序是不现实的.通常使用两种策略:

  1. 通过设置等待超时来释放资源(innodb_lock_wait_timeout)
  2. 发起死锁检测,发现死锁后,主动回滚死锁链条中的某个事务((innodb_deadlock_detect = on()

这两种方法,前者超时时间设置长了会造成资源长期得不到利用;设置短了很容易造成长事务的误伤;后者是经常使用到的方式,但是也有缺点.线程在检测死锁时,需要去循环所有依赖线程,判断自己是否加入导致死锁,这个时间复杂度为O(n),当并发上去以后,会导致cpu大量用于检测死锁占用.

那么怎么解决这种问题的发生呢?

  1. 如果保证确保这个业务不会出现死锁,可以临时关闭死锁检测,但是会导致大量的超时,对业务有损
  2. 控制并发度,核心是确保单行的更新线程很少,这个并发度在哪里做的问题,就要具体问题具体分析了,一般服务端可以做,中间件也可以做,数据库库服务端也可以做,还有一个方案是单行数据拆分为多行数据,比如说某个账户拆分为N个子账户,需要进行更新时随机进行一条记录操作,但是要做好业务逻辑上的处理

快照在MVCC里的生成

Innodb 里面每个事务有一个唯一的事务ID,叫作trancactionid,事务开始的时候生成,且递增,每行数据也有数据版本,每次事务更新数据时,都会生成新的数据版本,并且把transaction id赋值给这个数据版本的事务ID,记为row_trx_id,旧版本也会直接保存在新版本中

如图: 版本V1到V4的trans id变化,图中虚线部分就是redo log日志,而v1到v3物理上是不存在的,是通过redo log计算出来的

此处输入图片的描述

可重复读的定义是当一个事务启动时,只会认可在此之前的数据版本.Innodb为每个事务构造一个数组,用来保存所有正在活跃(启动了没有提交)的事务ID. 数组里面事务ID最小值记为低水位,当前系统里面已经创建的事务ID最大值+1称为高水位,视图数组和高水位,构成了当前事务的一致性视图

此处输入图片的描述

当前事务启动瞬间,一个数据版本row_trx_id,有可能分布在上述绿,黄,红色部分中,如果在绿色:对于当前事务数据是可见的.如果在红色部分,这个版本时是来事务启动的,不可见,如果黄色部分而且row_tran_id在数组中,表示没提交事务生成,不可见;否则可见

如图 此处输入图片的描述

事务A读到的数据k值为1,那么事务B读到的值是1,但是更新操作得到的数据确实3,因为对于事务B来说,update操作都是先读后写的,而这个读只能读取当前的值,称为”当前读“.如果select语句加锁的话也会”当前读”.假设事务C的提交在事务B的更新之后,那么事务B更新时读到的K又是多少了,答案是两阶段锁协议会导致事务B阻塞等待事务C的完成.

总结:可重复读的核心就是一致性读(consistentread);而事务更新数据的时候,只能用当前读。如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待

而读提交和可重复读区别在于,前者是在每一个语句之前重新计算一个新的一致性视图,后者是在事务开始时创建,并在事务范围内其他查询共用.

唯一索引和普通索引的查询 Innodb的数据是按照数据页为单位来读写的,也就是说读取一条记录的时,以页为单位将其整体读入内存,唯一索引和普通索引的查询成本相当

唯一索引和普通索引的更新 change buffer

当需要更新一个数据页时,如果数据页在内存中就直接更新,如果不在,Innodb会将这些更新操作缓存在change buffer中,这样不需要从磁盘中读取数据页,下次有关这个数据页的操作时,将数据页读入内存,执行change buffer的操作,这个过程称为merge.除了访问数据页会触发,后台也会定期merge.change buffer在内存中有拷贝,也会被写入到磁盘.

唯一索引在插入数据时,都会去判断是否唯一,如果刚好插入页在内存中,则和普通索引没有区别;若不是则要去磁盘进行校验,相比普通索引则最多借助change buffer的帮助进行数据更新

change buffer只适用于普通索引的场景,且写多读少的业务,对于写入后马上就会进行查询的业务,需先记录changeBufer,然后merge,增加了change buffer的维护成本.

redolog在更新数据的时候会写入.

redolog主要是节省随机写磁盘的IO消耗,而change buffer主要节省的是随机读磁盘的IO消耗

索引选择

mysql也会有选错索引的情况特别是在平常不断删除历史新增数据的场景下,优化器在评估使用什么索引的情况下,首先是通过预估扫描行数,然后评估是否回表等等综合考虑

扫描行数预估 通过索引区分度来计算,一个索引上的值越多,区分度越高,这个值称为基数.

查看区分度 (show indes from t)中的cardinarty项,这个项mysql通过采样统计,先获取一个页的索引基数,然后乘以页数

回表因素 如果我们有一个索引B 和主键索引id,前一个查询扫描行有1000条,后者需要10000条,又是mysql会选择后者,因为mysql会觉得我10000条直接扫主键,不用回表,没有额外的代价

重建索引 大多数情况先如果我们发现msyql分析出来的扫描数和实际的差距过大,可以通过指令 (analyze table t)来重建索引,使mysql得到正确的扫描数

极少情况选错索引的修正

字符串怎么创建索引

hash和前缀索引

可以看出前缀索引唯一优势就是节省了索引的存储空间,所以定义好前缀的长度就显得尤其重要了,通过sql(select count(distinct column) as L from table;)查看某字段不同的值的数量,通常设计前缀索引所造成的损失区分度在5个百分点之内.

使用前缀索引会导致覆盖索引的查询性能优化失效.

倒序存储 使用在一些后面位数区分度高一些的字符串,例如 存储身份证号的时候把它倒过来存,每次查询的时候(select field_list from t where id_card = reverse(‘input_id_card_string’);)

使用hash字段 在表上再创建一个整数字段,来保存hash过的字符串校验码(mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);)

倒序和使用hash字段都不支持范围查询

mysql抖动

很懂场景下,你会发现,一条sql语句,正常情况下执行会特别快,但是有时会特别慢,这种发生时随机的,持续时间很短.大多数情况是由于mysql在刷脏页

脏页 前面讲到,innodb在处理更新语句的时候,会先记录redo log,随后有空再将redo log的更新到磁盘,称为 flush.那么我们称,内存数据页磁盘数据页内容不一致的时候,这个内存页称为脏页,内存经过flush到磁盘后,内存数据页和磁盘数据页内容一致后,称为 干净页.

flush的场景

innodb刷脏页的策略控制 innodb刷脏页的能力,决定在于主机的磁盘读写能力,通过 innodb_io_capacity参数的设置成主键的磁盘IOPS.(fio工具可以测试出IOPS)

表数据的存储 表数据可以存在共享表空间,也可以是单独的文件. innodb_file_per_table=OFF:表数据放在系统共享表空间,也就是跟数据字典一起

innodb_file_per_table=ON:表数据存储在一个以.ibd为后缀的文件中.

数据记录和数据页的可复用状态 有时我们会发现我们是哟功能delete语句删除了数据之后,发现表空间大小没有变化,这是由于delete语句只是将删除的记录置为可复用状态,磁盘文件大小并不会缩小,看起来就像是空洞,不仅删除会造成空洞,插入数据也会,如果数据不是按照索引递增顺序插入,而是随机插入的,就可能造成索引的数据页分裂.

重建表 为了使索引数据页看起来更紧凑,可以通过语句(alter table A engine=InnoDB)重建表.5.6之后引入了Online DDL,支持在表重建过程中保证新数据的写入安全

统计表的记录 Myasiam引擎把一个表的总行数存在了磁盘上,执行count(*)直接返回这个数 Innodb因为支持多事务MVCC版本控制所以需要一行一行从引擎读出来,累计计数.

innodb在执行 count(#) 时优化器会选择最小的那颗索引树来进行遍历. show table status语句也可统计行数,但是这个语句统计行数是通过采样方式计算出来的(数据页记录*页数),统计出来的数据是不准确的

count(id),count(col),count(1)等有不同的性能,在分析性能差别时,有几个原则:

  1. server要什么给什么
  2. Innodb只给必要的值
  3. 现在优化器只优化了count(*)的语义为 “取行数”,其他显而易见的优化并没有做

count(id):innodb遍历整张表,把每一行id取出来,返回server,server判断id不为空的,按行累加

count(1):遍历整张表,但不取值,对于返回每一行,放一个数字 1 ,按行累加.

count(col):如果字段允许为null,执行的时候,还需要把值取出来再判断一下,不是null才累加

按照效率排序的话,count(字段)<count(id)<count(1)~count(*)

其他统计记录的方式 把计数放redis里面:由于两个不同的存储构成的系统,不支持分布式事务,无法拿到精确一致的视图.不能保证计数和mysql表里数据精确一致.

order by实现流程 此处输入图片的描述 上述mysql排序可能在内存中进行,也可能会使用外部排序,取决于 sort_buffer_size设置的大小,如果需要排序的数据小于这个设置值,则在内存中进行;大于则将数据分成该设置值大小的外部文件,分别排序,最后合并

检查排序是否使用了临时文件

/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

如果排序返回字段过多 如果排序后返回的字段很多,会导致,排序文件和内存占用过大,那么就要使用其他算法.

SET max_length_for_sort_data = 16;

我们设置了排序的行长度不能超过16,如图,上述返回的字段超过了16,那么放入sort_buffer的字段就只有name和主键id,流程变成如下:

  1. 初始化sort_buffer,放入两个字段,name和id
  2. 从索引city取出第一个满足条件的主键id
  3. 通过主键id回表取出整行,取name,id字段,存入sort_buffer
  4. 从索引取符合条件的下一个主键id
  5. 重复3,4步骤
  6. 对sort_buffer数据按name排序
  7. 遍历排序,取前1000行
  8. 再次回表通过id取出其他字段数据

这个过程称为rowid排序,其中8不需要服务端再存入内存,而是直接返回给客户端

如果内存够,就要多利用内存,尽量减少磁盘访问,采用全字段排序,否则使用rowid排序

所有order by都会排序是mysql使用排序算法吗 如果我们建一个联合索引,如(city,name)那么上述的sql后面应用索引得到4步骤数据就已经是排好序的数据了

取随机数 mysql中我们使用rand()获取随机数,他的流程如下:

  1. 创建临时表,是哟功能memory引擎,表里有两个字段,一个是R,一个W.这个表没有索引
  2. 按主键顺序取出所有word,调用rand()函数生成一个大于0小于1的随机小数,将随机小数和word存入R和W中.
  3. 在临时表上按照字段R排序.
  4. 初始化sort_buffer,sort_buffer中也有两个字段,一个是double,另一个整形.
  5. 将临时表一行一行取出R和位置信息放入sort_buffer中
  6. 在sort_buffer中根据R排序,并取出所需数据位置信息

流程如如图: 此处输入图片的描述

先通过原理分析出扫描行数,再通过慢查日志,验证结论,是一种很好的方法

mysql定位一行数据的方式

我们把这种定位方式成为rowId排序,也就是上述的orderBy使用到的方式,而在rand函数执行,内存临时表排序时也使用了rowid排序方法

临时表也可以使用磁盘,tmp_table_size限制内存临时表大小,默认为16M.

有时候我们使用rand函数后后面还跟了个limit N,而这时mysql不会使用磁盘的归并排序,因为我们只需limit数量的数据,所以排序所有的数据不是mysql所希望的,这时采用优先队列排序,以N行数据为一个单位,拿单位边界外数据与单位内数据进行对比,一个遍历后单位内数据是我们所需得到的limit数据.(limit数据如果过于大,超过sort_buffer_size大小的话,就只能使用归并排序算法).

更具效率的排序方式 算法1

这个算法有个bug,就是如果id不连续,很有可能会得到一个空洞的数据

算法2

算法3