MySQL分析

MySQL查询

一条SQL语句的执行过程：

1：建立连接

     同步和异步

      同步：同步通信依赖被调用方，受限于被调用方的性能，也就是说应用操作数据库线程会阻塞，等待数据库的返回。一般只能做到一对一，很难做到一对多的通信

      异步：可以避免应用阻塞等待，但是不能节省SQL执行的时间，如果异步存在并发，每一个SQL都要单独建立一个连接，避免数据的混乱，这样会给服务端带来巨大压力，还会带来编码的复杂度，一般不使用

所以一般连接数据库都使用同步连接

      长连接和短连接

       MySQL支持长连接也支持短连接，短连接就是操作完成后马上close，长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接，一般在连接池中使用长连接，MySQL默认28800秒(8小时)

 

 

通信方式：

    单工：数据传输是单向的，遥控器

    半双工：数据传输是双向的，对讲机

    全双工：数据传输是双向的，并且可以同时传输，打电话

MySQL采用了半双工通信方式，所以客户端发送SQL语句给服务端时候，数据是不能分成小块传输，都是一次性发送，如果数据量特别大需要调整MySQL中的max_allowed_packet参数的值(默认4M)

 

2：查询缓存

   MySQL内部自带了一个缓存模块，默认是关闭的，因为它要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的SQL，当表里面任何一条数据发生变化时候，这张表的缓存都会失效，所以缓存还是交给ORM框架

 

3：语法解析和预处理

       词法解析：将一个完整的SQL解析成一个个单词

        语法解析：对SQL做一些语法检查，比如单引号没闭合，然后根据MySQL定义的语法规则生成一个数据结构(解析树)

        预处理器：检查生成的解析树，解决解析器无法解析的语义，比如检查表和列名是否存在，检查名字和别名等，得到一个新的解析树

 

4：查询优化与查询执行计划

    查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL里面使用的是基于开销的优化器，哪种执行计划开销最小，就使用哪种

    优化器最终会将解析树变成一个查询执行计划(是一个数据结构)，这个执行计划不一定是最优的执行计划，因为MySQL也有可能覆盖不到所有的执行计划，通过explain可以查看执行计划的信息，explain的结果也不一定是最终执行的方式

 

5：存储引擎

  在关系型数据库中，数据放在表Table里面，在存储数据的同时还要组织数据的存储结构

  MySQL中每创建一张表可以指定它的存储引擎，是以表为单位

 

存储引擎的比较：

    常见的存储引擎：InnoDB(支持事务，支持行级别的锁)，  MyISAM(MySQL自带)

    MyISAM(3个文件):

         应用范围较小，表级锁定限制了读/写的性能，因此通常用于只读或者以读为主的工作

          特点：

                支持表级别的锁(插入和更新会锁表)，不支持事务

                拥有较高的插入和查询速度

                存储了表的行数(count速度快)

    InnoDB(两个文件)：

           MySQL中默认的存储引擎，是一个事务安全的MySQL存储引擎，具有提交，回滚和崩溃恢复功能，将用户数据存储在聚集索引中，以减少基于主键的常见查询I/O,还支持外键引用的完整性约束

            特点：

                  支持事务，支持外键，因此数据的完整性，一致性更高

                  支持行级别的锁和表级别的锁

                  支持读写并发，写不阻塞读(MVCC)

                  特殊的索引存放方式，可以减少IO,提升查询效率

适合经常更新的表，存在并发读写或者有事务处理的业务系统

     

          Memory(一个文件)：只适合做临时表

          CSV(三个文件)

 

 

6：执行引擎，返回结果

     存储引擎是我们存储数据的形式，执行引擎利用存储引擎提供的API来完成相关操作，返回结果

 

 

 

MySQL更新

一条更新SQL是如何执行的？

基本流程和查询SQL是相同的，经过解析器，优化器的处理，最后交给执行器，区别在于拿到符合条件的数据之后的操作

 

1：缓冲池 Buffer Pool

   InnoDB的数据都放在磁盘上，InnoDB操作数据最小的逻辑单位是页，对于数据的操作不是每次都直接操作磁盘，因为磁盘速度太慢了，InnoDB使用了缓冲池的技术，也就是将磁盘读到的页放到一块内存区域中，下次读取相同的页先判断是否在缓冲池中，如果在则直接读取，不用再次访问磁盘，修改数据的时候，先修改缓冲池里面的页，内存数据和缓冲池数据不一致时候，我们叫做脏页，InnoDB中有专门的后台线程将Buffer Pool的数据写入磁盘，每隔一段时间就一次性的把多个修改写入磁盘，这个动作叫刷脏

InnoDB内存结构和磁盘结构

 

Buffer Pool分为几个部分：Buffer Pool, Change Buffer ,  Adaptive Hash Index，Log Buffer

        Buffer Pool：缓存的是页面信息(数据页，索引页)，默认大小128M,如果内存满了InnoDB使用LRU(不是传统的LRU，分成young和old)来管理缓冲池，经过淘汰的数据就是热点数据

当需要更新一个数据页的时候，如果数据页在Buffer Pool中存在，则直接更新，否则需要从磁盘加载到内存，再对内存数据页进行操作，也就是说如果没有命中缓冲池，至少要产生一次磁盘IO，优化方式？？？

 

        Change Buffer(写缓冲)：如果这个数据页不是唯一索引，不存在数据重复的情况，也就是不需要从磁盘加载索引页判断数据是不是重复(唯一性检查)。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句的执行速度

          将Change Buffer记录到数据页的操作叫merge

          何时发生merge?

           1：访问这个数据页的时候，通过后台线程，数据库shut down redo log写满时触发

       Log Buffer(redo log)：如果Buffer Pool里面的脏页还未刷入到磁盘时候，数据库宕机或者重启，这些数据丢失，如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用，为了避免这个问题，InnoDB将所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时候从这个文件进行恢复操作(实现事务的持久性)，这个文件就是磁盘的redo log(重做日志)

       这种日志和磁盘配合的整个过程就是MySQL中的WAL技术(Write-Ahead Logging),关键点在于先写日志，后写磁盘

 

为什么先写日志再写磁盘？

刷盘是随机IO,而记录日志是顺序IO，顺序IO效率更高，因此先将修改写入日志，可以延迟刷盘时机，进而提升系统吞吐

 

注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件

 

Log Buffer什么时候写入log file?

写入数据到磁盘时候，操作系统本身就有缓存，flush就是将操作系统缓冲区写入到磁盘，log buffer写入磁盘的时机由一个参数控制

 

 

redo log分成内存和磁盘两部分

特点：

    1：redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有

    2：不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志

   3：redo log的大小是固定的，前面的内容会被覆盖

check point：当前要覆盖的位置，如果write pos和check point重叠，说明redo log已经写满了，这时候需要同步redo log到磁盘中

 

 

 

2：磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中，InnoDB表空间分为5大类

 

2.1:系统表空间(system tablespace):

      InnoDB数据字典:内部系统表组成，存储表和索引的元数据信息(定义信息)

     双写缓冲区(InnoDB的一大特性)：InnoDB的页和操作系统的页大小不一致，如果存储引擎在写入页的数据时候发生了宕机可能出现页只写了一部分的数据(写失效)，可能会导致数据丢失，如果这个页本身已经损坏了，使用redo log来做崩溃恢复是无意义的，所以在引用redo log之前需要一个页的副本，如果出现了写试下，就用页的副本来还原这个页，然后再应用redo log,这个页的副本就是double  write

     Change Buffer

      Undo logs

 

 

2.2：独占表空间

  每张表会开辟一个表空间，就是数据目录下的ibd文件，存放表的索引和数据，其他信息(undo信息)，系统事务信息，二次写缓冲都在共享表空间中

 

2.3：通用表空间

2.4：临时表空间：包括用户创建的临时表，磁盘的内部临时表

 

undo log(撤销日志或回滚日志)：

       记录了事务发生之前的数据状态(不包括select),如果修改数据时出现异常，可以用undo log来实现回滚操作(保持原子性)，执行undo时候仅仅是将数据从逻辑上恢复到事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志

redo log和undo log与事务密切相关，统称为事务日志

 

更新操作流程：name原值是1

update user set name='2' where id=1

1：事务开始，从内存或者磁盘读取到这条数据，返回给Server执行器

2：执行器修改这一行数据的值为2

3：记录name=2到undo log

4：记录name=1到redo log

5：调用存储引擎接口，在内存(Buffer pool)中修改name=2

6：提交事务，刷脏

 

 

Bing log：

   以事件的形式记录了所有的DDL和DML语句(记录的是操作而不是数据值，属于逻辑日志)，可以用来做主从复制和数据恢复

   和redo log不同，它文件内容可以追加的，没有固定大小限制，开启了binlog功能情况下，可以把binlog导出SQL语句，把所有操作重放一遍，实现数据恢复

   另外一个功能就是实现主从复制

 

有了biglog后的update流程

 

1：先记录到内存，再写日志文件

2：记录redo log分为两个阶段(prepare和commit)

3：存储引擎和Server记录不同的日志

4：先记录redo再记录binlog

 

 

   

 

MySQL的索引

InnoDB中有三种：、

    普通索引：非唯一索引，没有任何限制

    唯一索引(主键索引是特殊的唯一索引)：要求键值不能重复，主键索引多了一个条件就是不能为空

    全文索引：针对比较大的数据，只有文本类型的字段才创建全文索引(varchar char text)

 

MySQL的存储结构分为5级：表空间，段，簇，页，行

 

表空间：由各个段组成，所有数据都放在表空间中，分为系统表空间，独占表空间，通用表空间，临时表空间，Undo表空间

段：包括数据段，索引段，回滚段，一个ibd文件(独立表空间文件)由多个段组成，

        创建一个索引会创建两个段，一个是索引段，一个是数据段，索引段管理非叶子节点的数据，数据段管理叶子节点的数据，一个表的段数就是索引的个数*2

 

簇(区)：一个段由很多簇组成，每个区大小是1MB(64个连续的页)，一个段至少会有一个簇，一个段管理的空间大小是无限的，可以一直扩展辖区，扩展最小单位是簇

页：簇是由连续的页组成的空间，一个簇中有64个连续的页，这些页在物理上和逻辑上是连续的，每个页默认16KB

 

 

往表中插入数据时候，如果一个页面已经写完，产生一个新的页，如果一个簇的所有页面都被用完，会从当前页面所在的段重新分配一个簇，如果数据不是连续的，往已经写满的页中插入数据，会导致叶页面分裂

 

索引结构的演变：

数组---->二叉树----->AVL数---->多路平衡查找树(B Tree)---->加强版多路平衡查找树(B+ 树)

 

红黑树：从根节点到叶子节点的最长路径(红黑相间的路径)不大于最短路径(全部是黑色节点)的2倍，一般只放在内存中，例如TreeMap

不用红黑树原因是红黑树只有两路，不够平衡

 

InnoDB内部使用哈希索引来实现自适应哈希索引特性，InnoDB只支持显示创建B+ Tree索引，对于一些热点数据页，InnoDB会自动建立自适应Hash索引，也就是在B+ Tree索引基础上建立Hash索引，这个过程对客户端是不可控制，隐式的

 

 

 

MyISAM:

    MyISAM中有两个文件，一个是.MYD文件(MyISAM数据文件)，存放数据记录，一个是.MYI文件(索引文件)，MyISAM的B+ Tree中，叶子及诶单那存储的是数据文件对应的磁盘地址，从索引文件中找到键值后会从数据文件中获取对应的数据记录，辅助索引检索数据方式相同

 

InnoDB：

    只有一个.ibd文件，以主键为索引来组织数据的存储，InnoDB的主键索引的叶子节点上，直接存储了我们的数据

    聚集索引：索引键值的逻辑顺序和表数据行的物理存储顺序是一致的(主键索引都是聚集索引，非主键索引都是非聚集索引)

    辅助索引存储索引和主键值，然后通过主键值在主键索引中查询到数据

 

如果一张表没有主键怎么办？

1：如果我们定义了主键，则InnoDB会选择主键作为聚集索引

2：如果没有显式定义主键，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引

3：如果没有这样的唯一索引，InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，会随着行记录的写入而主键递增

 

 

 

列的离散度：重复列越多，离散度就越低，重复值就越少，离散度就越高

联合索引最左匹配：先使用联合索引的最左边字段进行查询

覆盖索引：回表-->非主键索引，先通过索引找到主键索引，然后通过主键索引找到数据

索引下推(ICP需要手动开启)：在first_name和last_name上建立联合索引，此时select * from employees where last_name='wang' and first_name                        LIKE '%zi' ;这样会有两种执行方式

                  1：根据联合索引查询出wang的数据，然后回表，到主键上查询全部符合的数据，然后再Server层进行过滤

                  2：根据联合索引查询出wang的数据，然后从二级索引中筛选出符合条件的数据，再回表到主键索引上查询全部符合                          条件的数据，返回给Server

Extra中Using where表示从存储引擎获取的数据不全满足条件，需要在Server层过滤

 

索引的创建原则：

1：在where判断order排序和join的(on)字段上创建索引

2：索引个数不要过多

3：区分度低的字段不要建立索引

4：频繁更新的值不要作为主键或者索引(页分裂)

5：组合索引将散列性高的值放在前面

6：创建组合索引而不是修改单列索引

 

索引失效：

1：索引列上使用函数，表达式，计算

2：字符串不加引号，出现隐式转换

3：like条件中前面带%

4：负向查询 not like

 

前缀索引：当字段值比较长的时候，建立索引会很消耗空间，可以通过截取前面一部分内容建立索引

 

 

MySQL中的锁

 

事务：是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

四大特性：ACID

原子性：不可再分，InnoDB中通过undo log实现，记录数据修改之前的值

一致性：数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态(比如主键必须唯一，字段长度符合要求)

隔离性：多个事务对表或者行的并发操作应该是透明的，互相不干扰

持久性：对数据库的任意操作，只要事务提交成功，结果就是永久性的，通过redo log和double write实现

 

并发事务带来的问题：

脏读：一个事务读取到另外一个事务未提交的数据

不可重复读：一个事务读取到了另外一个事务已经提交的数据，导致前后两次读取数据不一致的情况

幻读：一个事务执行范围查询时候，查到满足数据只有一条，第二个事务中插入一条数据并且提交了，第一个事务再次查询时候             发现多了数据

 

四大隔离级别：

     读未提交RU：一个事务可以读取到其他事务未提交的数据，没有解决任何问题

      读已提交RC：一个事务可以读取到其他事务已经提交的数据，解决脏读问题

      可重复读RR：一个事务多次读取相同数据的结果是一样的，解决不可重复读问题

      串行化Serialiazble:所有事务都是串行执行，解决所有问题

 

MySQL InnoDB对数据库事务隔离级别的支持程度

解决读一致性的问题，保证一个事务中前后两次读取数据结果一致，实现事务隔离总体上有两个方案

LBCC(基于锁的并发控制)：读取数据的时候，锁定我要操作的数据，不允许其他的事务修改。这样就意味着不支持并发的读写                                                 操作，会影响操作数据的效率

MVCC(多版本的并发控制)：通过Undo log实现，可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删                                                  除了，在我这个事务之后新增的数据查询不到

 

InnoDB为每行记录都实现了两个隐藏字段：

DB_TRX_ID：6字节，插入或者更新行的最后一个事务的事务ID(创建版本号)，在数据新增或者修改为新数据时候，记录当前事务ID

DB_ROLL_PTR：7字节，回滚指针(删除版本号，数据被删除或者记录为旧数据时候，记录当前事务ID)

查找规则：只能查询创建版本小于等于当前事务ID的数据和删除时间大于等于当前事务ID的行(未删除)

 

 

共享锁Shared Locks：读锁， select .... lock in share mode;  

                                     只要事务结束，锁会自动释放，包括提交事务和结束事务

 

排他锁Exclusive Locks：用于操作数据，只要一个事务获取了一行数据的排他锁，其他事务就补鞥呢再获取这一行数据的共享锁                                          和排他锁，

                                        获取方式有两种：1：自动加上排他锁，在操作数据时候，包括增删改，会默认加上一个排他锁

                                                                      2：手工加锁，使用for update

 

(表锁)意向锁：数据库自己维护的，当我们给一行数据加上共享锁之前，数据库会自动在这张表上加上一个意向共享锁，当我们                            给一行数据加上排他锁之前，数据库会自动在这张表上面加上一个意向排他锁

             存在意义：

                 1：有了表级别的锁，在InnoDB里面就可以支持更多粒度的锁

                  2：可以直接通过判断这张表上是否有意向锁来检测是否可以直接加锁

 

行锁原理：

InnoDB的行锁是通过锁着索引来实现的

1：当表里没有显示定义索引时候，会锁表示因为它使用了内部的ROWID字段作为索引，会进行全表扫描，然后将每一个隐藏的聚集索引都锁住了

2：通过唯一索引给数据行加锁，主键索引也会被锁住？

通过辅助索引查询数据是经过回表的，通过主键值找到主键索引，然后再查询数据，此时主键索引也会被锁住

 

锁的算法：

记录锁(Record)：锁住主键

                             对于唯一性的索引(包括唯一索引和主键索引)使用等值查询，精准匹配到一条记录的时候，这时候使用记录锁

间隙锁(Gap)：根据主键，这些存在的Record隔开的数据不存在的区间，是一个左开右开区间

                        当我们的查询记录不存在，没有命中任何一个record，无论是用等值查询还是范围查询时候，它都使用间隙锁

                        注意是查询记录不存在时候，使用间隙锁，主要是阻塞insert，相同间隙锁之间并不冲突   

                         Gap Lock只在RR级别时候存在，关闭间隙锁可以将事务隔离级别设置成RC，并且设置                       i                                            innodb_locks_unsafe_for_binlog=on 

临键锁(Next-key)：间隙连同它左边的记录，叫做临键的区间，左开右闭区间

                           相当于记录锁加上间隙锁

                            两种退化的情况：唯一性索引，等值查询匹配到一条记录时候，退化成记录锁，没有匹配到任何记录时候退化                                                           成间隙锁

                          select * from t2 where id >5 and id <=7 for update; -- 锁住(4,7]和(7,10]
                          select * from t2 where id >8 and id <=10 for update; -- 锁住 (7,10]， (10,+∞)

                          锁住下一个左开右闭的区间是为了解决幻读的问题

 

MySQL四个事务隔离级别的实现：

RU隔离级别：不加锁

Serializable：所有的select语句会被转换成select...in share mode,会和update,delete互斥

RR隔离级别：

                  普通的select使用快照读，底层使用MVCC来实现

                  加锁的select(select....in share mode/select....for update)以及更新操作update,delete等语句使用当前读，底层使用记                   录锁或者间隙锁或者临键锁

 

RC隔离级别：

                普通的使用快照读，底层使用MVCC

                 加锁的select都使用记录锁，因为没有Gap Lock

 

RC和RR的区别：

1：RR的间隙锁会导致锁定范围的扩大

2：条件列未使用到索引，RR锁表，RC锁行

3：RC的 半一致性 读可以增加update操作的并发性

 

 

死锁：

MySQL有一个参数来控制获取锁的等待时间，默认是50s

产生条件：

    1：同一个时刻只能有一个事务持有这把锁

     2：其他的事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺

     3：当多个事务形成等待环路时候，即发生死锁

 

 

MySQL优化：

 

 

1：连接---配置优化

      从服务端来说，可以增加服务端的可用连接数，修改配置参数增加可用连接数(修改max_connections大小)，或者及时释放不活动的连接，交互式和非交互式的默认超时时间是8小时，调小这个值

      从客户端来说，可以引入连接池

      

2：缓存

     引入缓存减少数据库的查询，如果单台数据库服务满足不了访问需求，可以做数据库集群方案

     主从复制是通过binlog，从服务器会获取主服务器的binlog文件，然后解析里面的SQL，然后执行一遍

 

做了主从复制的方案后，我们只需要将数据写入到master节点，而读的请求可以分担到slave节点，这种方案就是读写分离，该方案可以一定程度减轻数据库服务器的访问压力，但是需要注意主从数据一致性的问题，写入master，从slave查询，此时数据还未同步过来怎么办？

 

异步：在主从复制过程中，MySQL默认是异步复制的，对于master节点来说写入binlog，事务结束，对于slave来说，接收到binlog，结束

全同步复制：从库写完数据，主库才返回给客户端，这种方式可以保证在读之前数据已经同步成功了，但是事务执行时间变成，master节点性能下降

半同步复制：master在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到binlog并写入relay log中才返回给客户端，需要额外安装插件

 

多库并行复制：

分库分表：

     垂直分库：减少并发压力

                        将一个数据库按照业务拆分成不同的数据库

     水平分库：解决存储瓶颈

                       将单张表的数据按照一定的规则分布到多个数据库

 

 

 

高可用方案：

           主从复制：传统的HAProxy+keepalived的方案

           NDB Cluster

            Galera

 

 

优化器---SQL语句分析和优化

      1：慢查询日志(slow_query=on)

      2：mysqldumpslow工具

      3：使用explain执行计划查看

           id:先查询大的后查询小的，id值相同时候从上往下执行，MySQL会根据笛卡尔积决定，会优先选择中间结果数据量比较小                 的数据

           select type(查询类型)：SIMPLE(简单查询，没有子查询和关联查询)   PRIMARY(主查询，最外面的那层查询)

                                                 SUBQUERY(内层查询)

           

            type(连接类型)：system>const>eq_ref>ref>range>index>all     const(主键索引或者唯一索引，只能查询到一条数据)

                          一般来说查询至少要达到range级别，最好在ref级别，ALL(全表扫描)  Index(查询全部索引)都是需要优化的

            possible_key,key：可能用到的索引和实际用到的索引，NULL表示没有用到索引，有可能possible_key为空，key不为空                                            (覆盖索引的情况)

           key_len：索引的长度

           rows：MySQL认为多少行才能返回请求的数据

           filtered：存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)

           ref：使用哪个列或者常数和索引一起从表中筛选数据

          Extra：执行计划的额外说明

                    Using Index:使用了覆盖索引，不需要回表

                    Using Where：使用了where过滤，表示存储引擎返回的记录并不是所有都满足查询条件

                    Using index condition(索引条件下推)

                    Using filesort：不能使用索引来排序，用到了额外的排序(需要优化)

 

          SQL索引和优化：https://dev.mysql.com/doc/refman/5.7/en/optimization.html

 

存储引擎：

      插入操作多的选择MyISAM,临时数据使用Memeroy,常规的InnoDB

      分区或者分表：按月分表

      字段定义：为每一列选择合适的字段类型，不要使用外键，触发器，视图，不要大文件存储