mysql实战45讲学习心得（基础篇）

个人学习的理解，不对的地方望指正。小白一枚

一讲，mysql架构

客户端：可以是我们在cmd中把位置调整到mysql安装目录下的bin里面通过(mysql -u <username> -p，连接使用的是tcp/ip，所以可以支持连接远程服务器上的数据库，加参数-h 和ip就好了) ,然后输入密码连接，或者使用可视化工具sqlyong，navicat连接数据库。

服务器：安装完毕mysql后需要启动它，可以通过图形界面双击bin里面的运行程序，或者用cmd输入命令来完成启动 net start mysql ，启动后一般就是默认自动运行在你的电脑里面了，服务器又分为server层和存储引擎层，处理你用客户端发来的命令，图形化界面底层也是使用命令接口来实现。学过编译原理的伙伴可能更好理解，服务器有点相似与一个编译器，把你发来的命令做词法分析语法分析，然后执行，简单来讲就是通过你的命名来改变文件内容，因为你创建的数据库实际就是文件，不过是mysql自己定义的数据结构，文件类型。mysql还使用了缓存，插件化的思想，等很多巧妙的结构。

server层：和开发web程序思想一致，逻辑判断之类的东西就放在server层来处理，实际对数据库的改动查询放在存储引擎来实现。server主要任务概括为解析与优化，过程中还有查询缓存一步，类似于主存与硬盘的关系，引入了缓存，但是就要解决数据一致性问题，缓存与硬盘中数据不一致的问题，解决这个问题将浪费大量时间性能，在mysql8中已经删除了查询缓存。语法解析即编译原理那一套，就是从命令中提取出要查那个表那个字段，查询条件等信息，查询优化，有时候提出完的命令并不是最佳的，通过优化提高性能，1+2+...到100可以转为公式计算，优化就类似于这个功能，但比着强的多，这里只是举一个例子，mysql中数据结构的关系进行优化。

存储引擎层：通过server层的执行器来操作存储引擎，存储引擎完成数据的存储，提取等功能，主要是把数据持久化到硬盘，不同存储引擎各有特点，根据需要选取不同的存储引擎，也可以自己开发存取引擎来使用，常见有InnoDB（支持事务），MyISAM，MEMORY，其中innodb使用较为广泛。

二讲，日志系统

 redolog和binlog

redolog是innodb特有的日志系统，binlog是server层实现的。innodb是以页为单位来进行磁盘io的，每次修改一行也会导致io一个页，在系统繁忙的时候明显是不现实的，需要一个日志redolog记录操作过程，redolog是在磁盘，面对突发状况不丢失的特性，且连续地址顺序io，大小有限制，类似一个循环队列，随着记录清除记录循环使用这块空间，记录的是物理日志，在哪个页面做了怎样修改，当写满的时候，即使很忙也要去更新磁盘了根据redolog。

binlog是server层实现的日志，供多种存储引擎层使用，记录的是逻辑日志，可以理解为记录的东西是sql语句序列，binlog类似于队列，可以追加，写满一个文件，开启另一个，不会覆盖掉之前的内容。常用来实现主从复制，使得主机与从机保持数据一致。

binlog与redolog配合使用：

可以看到redolog分成两部分夹着binlog，无论是redolog或者binlog在前若中间故障，都会造成数据不一致

11 prepare阶段 2 写binlog 3 commit

当在2之前崩溃时 重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。 一致

当在3之前崩溃 重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

三讲，事务隔离

事务是一个抽象的概念，把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务

事务并发执行遇到的问题：

一个事务修改了另一个未提交事务修改过的数据即脏写

一个事务读到了另一个未提交事务修改过的数据即脏读

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值即不可重复读

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来即幻读

为应对这些问题，从而产生以下几种隔离级别概念（达到隔离与效率的平衡，选取不同隔离级别满足自己的要求）：

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
串行：我的事务尚未提交，别人就别想改数据。

脏写造成影响很大，这种情况不被任意一种隔离级别允许

四讲五讲，索引

使用索引的目地就是提高查找速度，从数据结构中我们知道，使用特殊数据结构的特性可以增加查找速度，哈希表用链表解决冲突，顺序表的折半查找，二叉搜索树的方式增加查找速度。哈希表中链表是无序的，就不适合范围查找，折半查找速度很快，也适合范围查找，不适合增减内容。二叉搜索树缺点是大量数据时，树很高查找速度大大下降，改用多叉搜索树来解决这个问题，即（innodb的索引模型）b+树来维护索引，当然其他数据库可能使用别的数据结构，如跳表等，也可以根据数据库使用的数据模型来选择数据库。

分析innodb的索引模型

主键索引也被称为聚簇索引，索引树叶子节点存储的是整行数据，故根据主键索引查询时查询一次即可以查到整条数据（在我们创建一个表时会选择一个不会重复的值作为主键，或者增加一个自增的列作为主键，主键索引就是根据索引的值建的索引，建表时自动完成，没有主键的表，innodb会给默认创建一个Rowid做主键）

非主键索引也被称为二级索引，索引树叶子节点存储的是主键的值，知道了主键的值，再根据它去查询主键索引获取整条数据，一般要查询两次索引树，第二次查找也被称为回表。（非主键索引存储的是主键的值，索引选择主键时选择占内存较小的比较合适，减少非主键索引的占用空间，选择自增主键的优势保证了递增插入，减少了当大量数据加入时b+树分裂造成的开销也）

覆盖索引在非主键索引中我们如果要做一个这样的查询select ID from T where k between 3 and 5（ID为主键，k为非主键索引），正常方式是通过k查询得到ID然后回表，然后二次才能查到需要的内容，我们查找的3到5之间的那么回表就更多了，但是非主键索引中叶子节点就存储着ID，和我们查找的内容一致，故只查找非主键索引就行了。实际也就是这样做的，这就是覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

在理解了覆盖索引后，试想可不可以方向利用这个特性，在（id,id_card,name,age,ismale)这样一张表中，由于业务需求，根据身份证查询姓名这个功能的需要，会频繁的根据身份证号查询姓名这个条sql，这时建立一个联合索引（id_card，name）就可以使得不会回表来节省大量时间，根据功能来建立索引表，通过空间换时间，所以说也不是只要建立索引就可以加快查询速度，要根据功能建立合适的索引，达到时间与空间的平衡。

最左前缀原则，在建立一个（name,age)的索引，查询条件为where namelike ‘张 %'，也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。（name,age）索引不仅可以做到覆盖查询，同时他又是可以满足name的单个索引的功能，但是查询age的时候就还要创建age的索引了根据需求，（a,b,c)的索引就可以完成（a),(a,b)索引的功能，就是可以完成前缀的功能，所以叫最左前缀原则吧。

索引下推还是（id,id_card,name,age,ismale)这个表，一个(name,age)索引，当查询条件为 where name like '张 %' and age=10 and ismale=1，我们就去查询(name,age)索引，找到张开头的然后去回表，但是其实可以在(name,age)索引里面进一步判断，age是否为10不为10不回表，节省了回表次数。在mysql5.6之前没有这种机制，5.6之后才采用这种机制称为索引下推优化。

补：

where name like '张 %' and age=10 and ismale=1，

查询条件任意换顺序无所谓的，优化器会调整，where name like  age=10 and '张 %'  and ismale=1会调整成上面sql逻辑。

高频查询，可以建立联合索引来使用覆盖索引，不用回表。
非高频查询，再已有的联合索引基础上，使用最左前缀原则来快速查询。

六讲，七讲，全局锁和表锁，行锁

全局锁

全局锁就是对整个数据库实例加锁，命令是 Flush tables with read lock， 全局锁的典型使用场景是，做全库逻辑备份。

官方自带的逻辑备份工具是 mysqldump，前提是引擎要支持这个隔离级别。对于不支持事务的引擎只能是使用全局锁的方式备份了。

备份文件设置全局只读set global readonly=true 的方式的缺陷：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁（分为表锁和元数据锁）

表锁：lock tables t1 read, t2 write; 对t1读锁，对t2写锁，读锁允许本线程和其他线程读，本线程写报错，其他线程写要等unlock；写锁允许本线程读写，其他线程读写要等unlock。表锁是最常用的处理并发的方式，而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。

元数据锁：元数据锁是server层的锁，表级锁，每执行一条DML（数据操作语言  insert  ,       delete ,  update,select等）、DDL（数据定义语言Create,Alter和Drop等）语句时都会申请metadata锁，DML操作需要metadata读锁，DDL操作需要metadata写锁，metadata加锁过程是系统自动控制，无法直接干预，读锁和写锁的阻塞关系如下：

1. 读锁和写锁之间相互阻塞，即同一个表上的DML和DDL之间互相阻塞。
2. 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
3. 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中大家看到的DML之间的锁等待是innodb行锁引起的，和metadata lock无关。

想要对表进行DDL语句如加字段可能遇到的问题？

1. 长事务长时间占用元数据锁，考虑先暂停修改，或者 kill 掉这个长事务。
2. 频繁被访问的热点表，kill 可能未必管用，因为新的请求马上就来了，在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

行锁：行锁是由引擎层实现的，不是所以的存储引擎都支持行锁，MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁。行锁就是针对数据表中行记录的锁。在需要的时候加锁，在事务提交之后才会释放。在事务中在不影响业务逻辑的情况下尽可能把频繁访问行的操作放最后，减少持有锁的时间，增加并发度。例如多个客户买车票，逻辑是，1客户钱少，2车站钱多，3交易记录生成，当多个客户买票时，车站钱多这个操作频繁使用尽量放在事务后面，减少持有锁的时间。

死锁：类似于操作系统的死锁，俩进程都持有资源都不放弃，都等待。一种策略是，直接进入等待（innodb_lock_wait_timeout控制等待时间），直到超时，默认50s一般等待50秒让人崩溃了都，设置小了1s又可能正常的锁等待也被处理了。另一种策略（常用）是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。新问题是死锁检测要耗费大量的 CPU 资源，解决方法为一就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉，有风险。另一个思路是控制并发度，如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

八讲，事务undolog视图数据版本关系

找找资料再比较综合这一章，先空着后面再补充吧。