全面对mysql的了解_了解MySql

抱歉,从印象笔记拷贝出来,格式乱了,但知识点还是很全面的,抱拳!

逻辑架构

连接器：管理连接，权限验证

分析器：语法分析，

优化器：执行计划生成，索引选择

执行器：操作引擎，返回结果

查询缓存：命中则直接返回结果

存储引擎：存储数据，提供读写接口

服务层:

order by:全字段排序(Using filesort);

rowid排序(只有需要排序的字段和id)

order by rand():使用了内存临时表,内存临时表排序的时候使用了rowid排序方法;

order buffer;join buffer;

redo log(重做日志)

位置：InnoDB引擎特有的日志

作用: 保证在即使数据库发生异常重启，之前提交的记录不会丢掉，这种能力叫做crash-safe;

物理日志,记录是"在某个数据页做了什么修改";

循环写，空间固定会用完；

主要节省的是随机写磁盘的io消耗(转成顺序写),而change buffer减少随机读盘的io消耗;

WAL技术：Write-Ahead-Logging,先写日志，再写(flush)磁盘(刷脏页);

脏页:当内存数据页跟磁盘数据页内容不一致的时候,我们称这个为"脏页";

干净页:内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为"干净页";

bin log(归档日志)

位置：Server层自有的日志

作用：保证在即使数据库发生异常重启，之前提交的记录不会丢掉，这种能力叫做crash-safe;

逻辑日志,记录的是这个语句的原始逻辑,比如"给 ID=2 这一行的 c 字段加 1";

追加写,binlog文件写到一定大小后会切换到下个,不会覆盖以前的日志';

binlog_format: row 或 statement(记录的是sql语句);

更新操作:先写入redolog,prepare,再写入binlog,commit,这就是所谓的"两阶段提交";

事务

在实现上,数据库会创建一个视图,访问的时候已视图的逻辑结果为主;

隔离级别:

可重复读(mysql默认级别),视图在事务启动的时候创建

读提交,视图是在每个sql语句开始执行的时候创建

读未提交,直接返回记录上的最新值,没有视图概念

串行化,直接用加锁的方式来避免并行访问

可重复读-实现:

在mysql中,实际上每条记录更新的时候会同时记录一条回滚操作;记录上的最新值,可以通过回滚操作,都可以得到前一个状态的值;

当没有事务再需要到这些回滚日志(undo log)时,回滚日志会删除;存放位置:5.5之前,ibdata;

可重复读-开启:

显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

set autocommit=1的情况下,用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

幻读:

原因:行锁只能锁住行,但新插入记录这个动作,要更新的是记录之间的"间隙";为了解决幻读问题,InnoDB只好引入新的锁;也就是间隙锁(GapLock);

锁

数据库锁设计的初衷是处理并发问题;作为多用户共享资源,当出现并发访问的时候,需要合理地控制资源的访问规则;而锁就是用来实现这些访问规则的重要数据结构;

加锁原则:

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

根据加锁的范围,mysql里面的锁大致可以分为:

全局锁:对整个数据库实例加锁;

1.全局读锁:命令(Flush tables with read lock);会让整个库处于只读状态,以下语句会被阻塞:数据更新语句(数据的增删改),数据定义语句(建表,修 改表结构)和更新类事务提交语句;使用场景:全库逻辑备份;

表级锁

1.表锁:命令(lock tables ... read/write;unlock tables;)

2.元数据锁:metadata lock(MDL);不需要显式使用,在访问一个表的时候会被自动加上,作用是为了保证写的正确性;

当对一个表做增删改查操作的时候,加MDL读锁;

当对一个表做结构更变操作的时候,加入MDL写锁;

读锁之间不互斥,可以多个线程同时对一张表增删改查;

读写锁,写锁之间是互斥的,用来保证变更表结构操作的安全性;

行锁:在引擎层,由各个引擎自己实现的,InnoDB支持行锁;不支持行锁意味着只能使用表级锁,意味着同一张表上任意时刻只能有一个更新在执行,会影响业务并发度;

1.在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立即释放,而是等到事务结束时才释放;这就是两阶段锁协议;

2.死锁和死锁检测:提交通过参数 innodb_lock_wait_timeout 来设置超时时间,默认是50s;提交发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑;

3.即使把所有记录都加上锁,还是阻止不了新插入进来的记录,这也是为什么"幻读"被单拿出来解决的原因;

间隙锁:行锁只能锁住行,但新插入记录这个动作,要更新的是记录之间的"间隙";为了解决幻读问题,InnoDB只好引入新的锁;也就是间隙锁(GapLock) ;

作用:跟间隙锁存在冲突关系的,是"往这个间隙中插入一个记录"这个操作;间隙锁之间不会存在冲突关系;

间隙锁和行锁合称next-key-lock;前开后闭区间;lock in share mode,for update,insert...select;都会对一个区间段的数据加上间隙锁;

作用:间隙锁和next-key-lock的引入,帮我们解决了幻读的问题;但是,间隙锁的引入,可能会导致同样对的语句锁住更大的范围,这其实影响了必发度的;

索引

目的:提高查询效率;InnoDB的数据是按数据页为单位来读写的,也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将 其整体读入内存,默认每个数据页大小为16KB;

数据结构:

哈希表,适用于等值查询的场景,做区间查询的速度很慢;

有序数组,适用于静态存储引擎,在等值查询和区间查询的性能非常优秀,但在更新数据的时候就非常麻烦,插入一条记录必须挪动后面所有的记录;

搜索树:B+树,时间复杂度O(log(N)),为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树;为了减少读取磁盘的数据,降低树的高度,引入N叉树;

InnoDB引擎把数据放在主键索引上,其他索引上保存的是主键id;这种方式,我们称之为"索引组织表";

Memory引擎采用的是把数据单独存放,索引上保存数据位置的数据组织形式,我们称之为"堆组织表";

类型:

主键索引:叶子节点是整行数据;主键的长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小;

普通索引:叶子节点是主键的值;

覆盖索引:索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据;

前缀索引:可以定义字符串的一部分作为索引;定义好长度,既可以节省空间,又不用额外增加太多的查询成本;

提交回表查询:回到主键索引树搜索的过程,称之为回表;

提交最左前缀原则:B+树这种索引结构,可以利用索引的"最左前缀",来定位记录;

1. 索引的复用能力:如果可以调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的;

2.索引占用的空间;

索引维护:

数据页分裂;

数据页合并:相领两个数据页由于删除数据,利用率很低之后,会将两个数据页合并;

更新操作缓存在change buffer,定期merge;用的是buffer pool里的内存;

删除数据会造成空洞,插入数据也会;

如果你创建的表没有主键,或者把一个表的主键删除了,InnoDB会生成一个长度为6字节的rowid来作为主键;

索引使用:

对索引字段做函数操作,可能破坏索引值的有序性,因此优化器就决定放弃走树搜索功能;

隐身类型转换:提交数据类型转换:在mysql中,字符串和数字比较的话,是将字符串转换成数字;多表查询,关联字段类型不一致;提交隐式字符编码转换:两个表编码集不一致,做多表查询,连接过程中要求在被驱动表的索引字段上加函数操作;

常见问题

长事务:

长事务意味着系统里面会存在很老的事务视图;由于这些事务随时可能访问数据库里面的任何数据,所以在事务提交前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间;

常用sql

show variables like 'transaction_isolation'; 查看数据事务级别

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60; 查看持续时间超过60秒的事务

alter table tbl_name nowait add column ...;

alter table tbl_name wait n add column ...;更新表结构,设置拿到MDL锁的时间,时间内拿不到锁,也不会阻塞其他业务语句;

start transaction with consistent snapshot; 想要马上启动一个事务;

begin/start transaction; 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句(第一个快照读语句)，事务才真正启动。

innodb_stats_persistent;存储索引统计的方式;on表示统计信息会持久化存储;off表示统计信息只存储在内存中;

select * from t where id=5 for update;查询后会加上一个写锁,在事务提交的时候释放;是当前读;

create temporary table temp_t;创建内存临时表

tmp_table_size;配置内存表的大小,默认16M;如果临时表大小超过这个配置,那么内存临时表就会转成磁盘临时表;

insert into … on duplicate key update;这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句

select SQL_BIG_RESULT id%100 as m, count() as c from t1 group by m;告诉优化器,这个语句涉及的数据量很大,可以直接使用磁盘临时表;

select * from where id = 1 lock in share mode;是当前读;select * from where id = 1 是一致性读;(第19篇的问题没有理解)

analyze table t;重新统计索引信息;

optimize table t;重建表,重新统计索引信息;

force index(xxx) ;强制选择xxx索引;

innodb_file_per_table;OFF表示表的数据放在系统共享表空间,和数据字典放在一起;ON表示每个InnoDB表数据存储在一个以.ibd为后缀的文件中;推荐设置为ON;

innodb_flush_neighbors;控制刷脏页连带"邻居"一起刷的行为;值为1表示会有上述"连坐"机制;值为0时表示不找邻居,自己刷自己;8.0版本以后,默认为0;

innodb_io_capacity;告诉InnoDB你的磁盘能力,建议设置成磁盘的IOPS;磁盘的IOPS可以通过工具fio来测试;

sort_buffer_size;mysql排序开辟的内存大小;如果排序的数据量小于该值,排序就在内存中完后;如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件排序;

max_length_for_sort_data;控制排序行数据长度的一个参数;如果超过这个值,则换排序算法;

innodb_max_dirty_pages_pct;脏页比例上限,默认是0.75,来控制刷脏页的速度;脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的;

create user 'ua'@'%' identified by 'pa’;创建一个用户ua;密码是pa;这时候用户没有任何权限;

grant all privileges on . to 'ua'@'%' with grant option;给ua用户所有权限;生产上为了安全我们应该限制用户的ip;

revoke all privileges on . from 'ua'@'%';回收用户的所有权限;

grant all privileges on db1. to 'ua'@'%' with grant option;让用户拥有库db1的所有权限;

flush privileges;刷新缓存权限;当表和换成权限不一致的时候;不要直接操作用户权限表;

show warnings;

创建分区表

CREATE TABLE t (

ftime datetime NOT NULL,

c int(11) DEFAULT NULL,

KEY (ftime)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(ftime))

(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,

PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

insert into t values('2017-4-1',1),('2018-4-1',1);

备份

官方自带逻辑备份工具:mysqldump;使用参数–single-transaction,使导数据前只开启一个事务,确保拿到一致性视图;(引擎必须支持该隔离级别)

全局读锁

架构

M(master);S(slave);

M-S架构(主--->从)

双MM架构

问题

全局读锁和set global readonly=true;怎么选择?

答:两者都可以让全库进入只读状态;readonly在某些系统被用来做其他逻辑,比如判断一个库是主库还是备库,修改全局变量的方式影响面更大;在异常处理机制方面,全局读锁在客户端异常断开后,会自动释放这个全局锁,整个库可以正常更新的状态,而readonly状态,会导致整个库长时间处于不可写状态,风险较高;

在可重复读隔离级别下,一致性视图(一致性读)和两阶段锁协议到底冲突吗?

答:事务在开启时,会基于库"拍个快照",也就是版本号;每行数据都有多个版本号;能够看到所有提交的事务结果,但之后,这个事务执行期间,其他事务的更新对它不可见;InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力;但:更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”(current read)。若"当前读"的数据,有其他事务的写锁还没有释放,这时候"两阶段锁协议"上场了,必须等其他事务释放了这个锁,才能继续当前读;

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

在 MySQL 里，有两个“视图”的概念：

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view ... ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC(Read Committed，读提交)和 RR(Repeatable Read，可重复读)隔离级别的实现。

4.怎么收缩表空间?

答:重建表;Online DDL;

5.select * from where id +1 = 10000;

select * from where id = 10000 -1; 两者一个不会走id索引,一个会走;

6.MySQL 什么时候会使用内部临时表？

答:如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；

join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；

如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

7.group by使用知道原则:

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；

如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果

8.如何快速复制一张表?

1.mysqldump

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

这条命令中，主要参数含义如下：

–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；

–no-create-info 的意思是，不需要导出表结构；

–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；

–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。

mysqldump -h

port -u

secure_file_priv

mysqldump 提供了一个–tab 参数，可以同时导出表结构定义文件和 csv 数据文件;这条命令会在 $secure_file_priv 定义的目录下，创建一个 t.sql 文件保存建表语句，同时创建一个 t.txt 文件保存 CSV 数据

2.csv

select * from db1.t where a>900 into outfile '/server_tmp/t.csv’;

需要注意如下几点。

1.这条语句会将结果保存在服务端。如果你执行命令的客户端和 MySQL 服务端不在同一个机器上，客户端机器的临时目录下是不会生成 t.csv 文件的。

2,into outfile 指定了文件的生成位置(/server_tmp/)，这个位置必须受参数 secure_file_priv 的限制。参数 secure_file_priv 的可选值和作用分别是：

3,如果设置为 empty，表示不限制文件生成的位置，这是不安全的设置；

4.如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；

5.如果设置为 NULL，就表示禁止在这个 MySQL 实例上执行 select … into outfile 操作。

6.这条命令不会帮你覆盖文件，因此你需要确保 /server_tmp/t.csv 这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。

7.这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符， 这样就可以跟字段之间、数据行之间的分隔符区分开。

8.select …into outfile 方法不会生成表结构文件

得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中

load data infile '/server_tmp/t.csv' into table db2.t;//读取mysql服务端目录

load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`。//读取mysql客户端目录

3.物理拷贝

在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法

假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r，具体的执行步骤如下：

执行 create table r like t，创建一个相同表结构的空表；

执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；

在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令(这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限)；

执行 unlock tables，这时候 t.cfg 文件会被删除；

执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据

关于拷贝表的这个流程，有以下几个注意点：

在第 3 步执行完 flsuh table 命令之后，db1.t 整个表处于只读状态，直到执行 unlock tables 命令后才释放读锁；

在执行 import tablespace 的时候，为了让文件里的表空间 id 和数据字典中的一致，会修改 r.ibd 的表空间 id。而这个表空间 id 存在于每一个数据页中。因此，如果是一个很大的文件(比如 TB 级别)，每个数据页都需要修改，所以你会看到这个 import 语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import 语句的耗时是非常短的。

比一下这三种方法的优缺点:

1.物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是， 这种方法的使用也有一定的局限性：必须是全表拷贝，不能只拷贝部分数据；需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用；由于是通过拷贝物理文件实现的，源表和目标表都是使用 InnoDB 引擎时才能使用。

2.用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。

3.用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

后两种方式都是逻辑备份方式，是可以跨引擎使用的。

9.join 语句的两种算法，分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。

这些优化方法中：

BKA 优化是 MySQL 已经内置支持的，建议你默认使用；

BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；

基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；

MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

— 如果一条语句extra字段什么都没写的话,表示用的是NLJ算法;在使用left join时候,左边表不一定是驱动表;