Mysql学习笔记

1、Mysql请求处理过程：

• 客户端发送请求
• 服务端处理连接、认证及权限校验，分配一个线程专门用于处理该客户端的请求
• 查询缓存
• SQL语法解析
• 查询优化，生成执行计划
• 存储引擎根据生成的执行计划实现数据的操作

2、启动选项与系统变量：
（1）启动选项：

• 命令行参数：启动服务端时可通过--启动选项1[=值1] --启动选项2[=值2]...的形式指定启动参数，但仅本次启动有效。
• 配置文件：默认位置/etc/my.cnf、/etc/mysql/my.cnf、$MYSQL_HOME/my.cnf、defaults-extra-file（命令行指定）等；
• 如果同一个启动选项既出现在命令行中,又出现在配置文件中,那么以命令行中的启动选项为准。

（2）系统变量：

• 查看：show variables [like 通配符]
• 设置：set [GLOBAL|SESSION] 变量名 = 值

（3）常用参数：

• max-connections：最大连接数；
• max_connect_errors：同一主机最大连接错误数；
• query-cache-size：查询缓存大小；
• default-storage-engine：默认存储引擎；
• join_buffer_size：连接查询缓存区大小，用于存储驱动表结果集记录
• lower-case-table-name：是否忽略表名大小写；
• default-character-set:字符集设置;
• max_allowed_packet：单条数据大小限制；
• thread_cache_size：连接线程缓存(2^(n+1))；
• innodb_buffer_pool_size：innodb缓冲池大小；
• innodb_file_per_table：开启独立表空间；
• innodb_stats_persistent：innodb数据库统计信息持久化；
• transaction_isolation：事务隔离级别
• ...

3、字符编码：
（1）常用字符集：

• ASCII：使用一个字节进行编码
• GBK：对在ASCII字符集中的字符则采用1字节编码，否则采用2字节编码
• UTF-8：Unicode字符集可以采用utf8、utf16、utf32这几种编码方案,utf8使用1~3个字节编码一个字符【使用三个字节编码汉字】,utf16使用2个或4个字节编码一个字符,utf32使用4个字节编码一个字符；（ Mysql中的utf8指utf8mb3，使用一到三个字节编码字符；如果要存放类似于emoji表情之类的字符，需要使用utf8mb4编码）

（2）mysql请求过程中的编码转换：

• character_set_client：客户使用操作系统字符集编码请求字符串
• character_set_connection：将character_set_client转换为character_set_connection；再从character_set_connection转换为对应列使用的编码；查询完毕再将结果从具体列的编码转为character_set_results类型的字符串返回；
• 使用操作系统的字符编码解码响应字符串。

(1)InnoDB存储引引擎磁盘与内存实现数据交换的最小单位是`数据页`，大小为`16K`
(2)innoDB中一个行中的所有列(不包括隐藏列和记录头信息)占用的字节⻓度加起来不能超过65535个字节`；即如果表中只有一个变长字段`VARCHAR(M)`，则该字段所能容纳的最大字节数为65535，该列所能容纳的最大字符数量为`65535/字符集表示一个字符最多需要的字节数。

4、InnoDB记录行格式((row_format)：

• Compact：

  • 额外信息：变长非Null字段占用字节数逆序、Null值字段列表逆序、记录头信息]；

  • 真实数据；

  • 隐藏列：row_id、transaction_id、roll_pointer；

    （1）transaction_id（trx_id）：每次一个事务对某条聚簇索引记录进行改动时,都会把该事务的事务id赋值给trx_id隐藏列；
    （2）roll_pointer：对应undo log中记录的指针信息

• Redundant：额外信息(字段偏移量列表、记录头信息) + 真实数据

  注：在Compact和Reduntant行格式中,对于占用存储空间非常大的列,在记录的真实数据处只会存储该列的一部分数据,把剩余的数据 分散存储在几个其他的⻚中,然后记录的真实数据处用20个字节存储指向这些⻚的地址(当然这20个字节中还包括这些分散在其他⻚ 面中的数据的占用的字节数),从而可以找到剩余数据所在的⻚

• Dynamic(默认)、Compressed：记录行在存放真实数据时如果发生行溢出，则真实记录数仅保存真正存放数据的数据页的地址。

• SQL：
  CREATE TABLE 表名(...) ROW_FORMAT = 行记录格式
alter table 表名 ROW_FORMAT = 行记录格式

5、InnoDB记录头信息：

• 预留位：两个，占用两个字节，未使用；
• delete_mask：标记当前记录是否被删除，避免直接删除导致数据页重排列的性能消耗；delete_mask为1的记录连接成为一个垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间,之后如果有新记录插入到表中的话,可能把这些被删除的记录占用的存储空间覆盖掉。
• min_rec_mask：标记每层非叶子节点中的最小记录
• n_owned：Page Directory分组中当前记录所属组中的记录数
• heap_no：记录在本数据页中的位置
• record_type：记录类型，0表示普通记录,1表示B+树非叶节点记录,2表示最小记录,3表示最大记录。
• next_record：从当前记录的真实数据到下一条记录的真实数据的地址偏移量（字节数）

6、InnoDB数据页结构：

• File Header：各数据页的通用信息，如：页号、页面类型、前/后页号等，所有的数据页形成一个双向列表；
• Page Header：记录数据页中记录的各种状态信息，如记录数、槽的数量、最后插入记录位置、第一条被删除的记录位置等；
• Infimum + Supremum：两个虚拟的伪记录,分别表示⻚中的最小和最大记录`
• User Records：真实数据
• Free Space：空闲区域
• Page Directory：数据页中的记录通过next_record根据主键值大小形成一个链表，为了提高数据页内的查找效率，数据被按主键顺序分至不同的组(slot)；由各slot中主键的最大值构成了Page Directory，因此在数据页中查找数据时的分为：通过二分法确定该记录所在的槽,并找到该槽中主键值最小的那条记录； 通过记录的next_record属性遍历该槽所在的组中的各个记录。）；`
• File Trailer：通过校验和保证内存与磁盘数据页的一致性；

7、B+树索引：

• 目录项记录：主键值 (页内主键最小值)+ 页号；

• 多数据页查找指定用户记录：先到存储目录项记录的⻚中通过二分法快速定位到对应目录项，即数据页；再在页中根据二分法快速定位到用户记录。

• Ｂ+数索引内节点存放目录项记录，叶子节点存放用户记录

• 聚簇索引：
  （１）使用记录主键值的大小进行数据页及页内记录的排序；
  （２）Ｂ+树叶子存储完整的用户记录；
  （３）聚簇索引就是数据的存储方式(所有的用户记录都存储在了叶子节点),也就是所谓的索引即数据,数据即索引。

• 二级索引：
  （１）使用记录索引列的大小进行记录和⻚的排序；
  （２）B+树的叶子节点存储的并不是完整的用户记录,而只是索引列 + 主键这两个列的值；
  （３）目录项记录中变成了索引列 + ⻚号的搭配；
  （４）使用二级索引查询用户记录的过程：
            - 确定目录项记录⻚
            - 通过目录项记录⻚确定用户记录真实所在的⻚
            - 在真实存储用户记录的⻚中定位到具体的记录
            -根据叶节点中的主键值去聚簇索引中再查找一遍完整的用户记录（回表）

• 联合索引：同时以多个列的大小作为排序规则建立索引

• 索引树注意事项：
  （１）B+树索引的根节点默认加载至内存中不会移动
  （２）内节点中目录项记录的唯一性：对于二级索引树的内节点，由于索引键值不唯一，所以在内节点中除了列值和页号需要添加主键值以保证同一层内节点的目录项记录除⻚号这个字段以外唯一，作为新插入数据查找插入数据页的依据。
  （３）一个⻚面最少存储2条记录

• MyISAM索引：
  （１）数据与索引分开存放
  （２）MyISAM会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值 + 行号的组合。也就是先通过索引找到对应的行号（偏移量）,再通过行号去找对应的记录

８、Ｂ+树索引的使用：

• 全值匹配
• 最左前缀匹配
• 匹配列前缀(模糊查询)
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 排序
• 使用联合索引进行排序需要注意排序顺序
• 不可以使用索引进行排序的几种情况：asc与desc混用、WHERE子句中出现非排序使用到的索引列、排序列包含非同一个索引的列、排序列使用了表达式
• 分组
• 使用覆盖索引减少回表的性能损耗
• 适合建立索引的场景：
  （１）用于搜索、排序或分组的列；
  （２）列基数大；
  （３）索引列的类型尽量小；
  （４）使用索引列值前缀建立索引；
  （５）主键列优点递增；

9、Ｍysql数据目录：

• InnoDB：系统表空间(ibdata*)与独立表空间(*.ibd)
• MyISAM：*.frm、*.MYD、*.MYI

10、单表访问方法：

• const：利用聚簇索引或唯一二级索引的等值查询；
• ref：利用普通二级索引或联合索引的左前缀等值查询；
• ref_or_null：与前者相比，多了对索引Null值的判断；
• range：利用索引进行范围匹配；
• index：遍历二级索引记录获取结果；
• all：全表扫描
• 通常只能利用单个二级索引执行查询：当存在多个索引条件时，查询优化器会选择扫描行少的索引进行查询执行回表操作；然后对回表的结果再根据其他条件进行过滤。
• 在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE即可；将根据索引回表查询到的记录返回后再根据对应的条件进行过滤；
• 一个使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

11、连接(join)：

• 内连接：驱动表中的记录在被驱动表中找不到匹配的记录,该记录不会加入到最后的结果集。
• 外连接：
  （１）where条件：通常为针对单表记录的过滤条件，不符合条件的记录不会出现在结果集中；
  （２）on条件：连接条件，对于外连接的驱动表的记录来说,如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录,那么该记录仍然会被加入到结果集中,对应的被驱动表记录的各个字段使用NULL值填充；连接查询中驱动表访问一遍，被驱动表访问多次。
  （３）通常考虑在被驱动表连接条件on的字段建立索引提高查询效率；
  （４）join buffer：用于存放连接查询时驱动表的多条记录，以便在被驱动表中数据加载至内存时一次性与多条驱动表记录进行匹配，从而减少被驱动表数据加载至内存又再次释放的次数。

12、Ｍysql基于成本的优化：

• 成本常数：读取一个数据页花费的成本为1.0；读取一条记录的成本是0.2；
• show table status like '表名'：查看指定表的统计信息，如：记录数、占用空间大小等；
• show index from 表名：查看指定表的索引信息
• InnoDB在单表查询优化过程中会分别计算全表扫描以及根据不同索引进行查询所花费的成本，包括了读取的数据页数量、扫描记录的行数等，选择成本最低的查询生成执行计划；
• 连接查询的成本：单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

13、子查询优化：
Ｍysql执行In子查询时，会为子查询结果集建立临时表(物化表)，并为该表中的列建立索引(数据较少时在内存中建立hash索引，数据量较大时在磁盘上建立B+树索引)；然后执行物化转连接，将in条件查询转为成本最低的内连接查询【需要注意的是Mysql内部对于子查询转连接查询后可能出现多条重复记录的情况还应用了半查询、重复值消除等手段对结果集去重】；

14、Explain：

• id：
  （1）连接查询的执行计划中,每个表都会对应一条记录,这些记录的id列的值是相同的,出现在前边的表表示驱动表,出现在后边的表表示被驱动表；
  （2）查询优化器可能对涉及子查询的查询语句进行重写,从而转换为连接查询。
• select_type：
  （1）查询语句中不包含UNION或者子查询的查询都是SIMPLE类型；
  （2）查询语句中包含UNION ALL，除了第一个小查询类型为SIMPLE，其他查询的select_type都是union；如果查询语句中包含的是UNION，除了子查询的union查询类型外，还需要对结果集去重的UNION RESULT；
  （3）子查询采用物化子查询时的select_type值为SUBQUERY；
  （4）DEPENDENT SUBQUERY、DEPENDENT UNION、DERIVED、MATERIALIZED、
• partitions：分区
• type：单表小查询类型
  （1）eq_ref：如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问，则被驱动表查询类型为eq_ref；
  （2）full_text、index_merge、unique_subquery、index_subquery
• possible_keys与key：可能用到的索引和实际用到的索引；
• key_len：实际使用索引列值的最大长度
• ref：与索引列作等值匹配的东西
• rows：扫描记录行数
• filtered：如果使用的是全表扫描的方式执行的单表查询,那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条；如果使用的是索引执行的单表扫描,那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
• Extra：额外信息
• json格式化执行计划：explain format = json SQL语句；
• 查看优化后的SQL语句：在命令行中执行完查看执行计划的语句后，紧接着执行show warnings \G 即可查看优化后的SQL语句

15、利用OPTIMIZER_TRACE查看查询优化过程：

• SET optimizer_trace="enabled=on"：开启optimizer trace功能；
• SELECT ...：执行查询语句；
• SELECT * FROM information_schema.OPTIMIZER_TRACE：从OPTIMIZER_TRACE表中查看上一个查询的优化过程
• SET optimizer_trace="enabled=off"：关闭optimizer trace功能；

16、Buffer pool：

• 缓存页：Mysql加载硬盘上的数据是按页加载的，为了减少磁盘I/O的次数，将加载的数据页缓存在Buffer Pool中；Buffer Pool中存放数据页缓存的单位称为缓存页；每个缓存页对应着一个控制块，存放缓存页信息；
• free链表：所有未使用的缓存页组成一个free链表，同时存在一个存放free链表信息的基节点；为了快速找到缓冲区中的缓存页，对缓存⻚进行了哈希处理；
• flush链表：凡是修改过的缓存⻚对应的控制块都会作为一个节点加入到一个链表中,因为这个链表节点对应的缓存⻚都是需要被刷新到磁盘上的,所以也叫flush链表；
• LRU链表：缓冲区溢出时的缓存页淘汰策略
• Mysql后台会启动一个线程定时的将缓存页中的脏页刷新至磁盘中；

17、事务：

• ACID
• MyISAM不支持事务
• 隐式提交：DDL、隐式使用或修改mysql数据库中的表、未提交或回滚又开启一个新的事务等；
• 保存点：在事务执行的多条语句中添加保存点，可以在回滚时只回滚到保存点的状态而无需回滚至事务开始的状态；
• redo log：
  （1）redo log文件记录事务已提交的修改，把事务在执行过程中对数据库所做的所有修改都记录下来,在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来。
  （2）redo log是逻辑日志，redo log日志的写入是顺序IO；
  （3）通常插入或修改一条记录可能涉及到多条redo log日志记录，比如：用户记录数据页的修改、目录项记录页的修改等；为记录这一次操作所做的修改需要记录多条redo log日志信息，它们被置于同一组中，写入具有原子性；
  （4）redo log并非直接写入磁盘文件，服务器中存在一块区域redo log buffer，在某些特定的时刻，如：提交事务、每秒定时刷盘、redo log buffer占用超过一半等，就会将redo log buffer刷入磁盘文件中；
  （5）磁盘上的redo log以文件组的形式存在，大小为innodb_log_file_size × innodb_log_files_in_group；
  （6）redo log文件的大小是有限的，因此需要重复利用，判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏⻚是否已经刷新到磁盘里；
  （7）redo log保证了事务的持久性
• undo log：
  （1）undo log用于事务回滚时对数据修改的恢复；
  （2）对于新增的数据，undo log记录其主键值；删除数据在事务提交前所做的操作是delete_mark，会在undo log中记录该记录的信息；更新操作时根据是否更新主键以及更新前后字段占用空间是否变化分别执行不同的操作，但都会在undo log中记录下数据更新前的状态；
  （3）每次对某条聚簇索引记录进行改动时,都会把旧的版本写入到undo日志中,然后roll_pointer隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。
  （4）undo log是物理日志，记录数据操作前的状态；
  （5）一个事务可能包含了对多条记录的改动，因此需要记录的undo log日志可能需要多个Undo⻚面进行存储；在一个事务执行过程中,可能混着执行INSERT、DELETE、UPDATE语句,也就意味着会产生不同类型的undo日志。但是我们前边又强调过,同一个Undo⻚面要么只存储TRX_UNDO_INSERT大类的undo日志,要么只存储TRX_UNDO_UPDATE大类的undo日志,反正不能混着存,所以在一个事务执行过程中就可能需要2个Undo⻚面的链表,一个称之为insert undo链表,另一个称之为update undo链表；又由于普通表和临时表的记录改动时产生的undo日志要分别记录,所以在一个事务中最多有4个以Undo⻚面为节点组成的链表。
  （6）undo log页的释放与重用：事务提交后undo log所占的空间会被系统释放或者被其他事务重用【需要注意的是并非一个事务可以直接使用上一个事务提交所释放的所有页面链表；并且对于不同类型的undo log页面链表，其重用条件也不一样】。
  （7）insert undo在事务提交之后就可以被释放掉了,而update undo由于还需要支持MVCC,不能立即删除掉。为了支持MVCC,对于delete mark操作来说,仅仅是在记录上打一个删除标记,并没有真正将它删除掉。

18、事务隔离级别与MVCC：

• 并发事务会出现的问题：脏写、脏读、不可重复读、幻读；

• 四种隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ(默认)、SERIALIZABLE；

• 修改事务隔离级别：SET global|session TRANSACTION ISOLATION LEVEL 隔离级别;

• MVCC原理：
  （1）每一次对数据记录的修改会将旧值放到一条undo日志中,就算是该记录的一个旧版本,随着更新次数的增多,所有的版本都会被roll_pointer属性连接成一个链表,我们把这个链表称之为版本链,版本链的头节点就是当前记录最新的值。另外,每个版本中还包含生成该版本时对应的事务id；
  （2）ReadView：

  • m_ids:表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
  • min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值。
  • max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值。
  • creator_trx_id：表示生成该ReadView的事务的事务id。

  （3） 在在访问某条记录时,按照下边的步骤判断记录的某个版本是否可⻅:

  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同,意味着当前事务在访问它自己修改
    过的记录,所以该版本可以被当前事务访问；
  • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值,表明生成该版本的事务在当前事务生成ReadView前已经提交,所以该版本可以被当前事务访问；如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值,表明生成该版本的事务在当前事务生成ReadView后才开启,所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间,那就需要判断一下trx_id属性值是不是在m_ids列表中：如果在,说明创建ReadView时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建ReadView时生成该版本的事务已经被提交,该版本可以被访问；
  • 如果某个版本的数据对当前事务不可⻅的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可⻅性,依此类推,直到版本链中的最后一个版本。如果最后一个版本也不可⻅的话,那么就意味着该条记录对该事务完全不可⻅,查询结果就不包含该记录。

  （4）READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同：

  • READ COMMITTED —— 使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView；
  • REPEATABLE READ —— 只在第一次读取数据时生成一个ReadView

  （5）所谓的MVCC(Multi-Version Concurrency Control ,多版本并发控制)指的就是在使用READCOMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程,这样子可以使不同事务的读-写、写-读操作并发执行,从而提升系统性能。READCOMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是:生成ReadView的时机不同,READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView,而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView,之后的查询操作都重复使用这个ReadView就好了。

19、锁

• 结构与原理：
  （1）结构：tx_id + is_waiting；
  （2）原理：当一个事务对某条记录进行写操作时需要先判断内存中是否存在与当前记录关联的锁结构：如果没有则创建关联，is_waiting值为false，表示加锁成功；如果有则创建锁结构且is_waiting=true，表示加锁失败，线程等待锁释放；当加锁成功的事务提交时会释放锁，并把其他事务对应该记录的锁结构的is_waiting属性设置为false,然后把该事务对应的线程唤醒,让它继续执行；
  （3）一致性读(MVCC机制保障)、锁定读(独占锁(...for update)与共享锁(...LOCK in share mode));

• 锁的分类：读锁、写锁、意向锁（IS、IX锁是表级锁,它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,以避免用遍历的方式来查看表中有没有上锁的记录,也就是说其实IS锁和IX锁是兼容的,IX锁和IX锁是兼容的）；

• 锁的粒度：

  • 表锁(MyISAM、MEMORY、MERGE等存储引擎)：对整个表加读锁或写锁；加锁消耗小但并发度低
  • 行级锁(InnoDB存储引擎)：
    （1）record lock：普通记录锁，如：update操作；
    （2）gap lock：间隙锁，防止幻读而提出的，防止在当前记录与前一条记录之间插入新的记录
    （3）next-key：前两者的结合，既能防止当前记录被操作，也能防止当前记录之前插入新的记录
    （4）Insert Intention Locks：插入意向锁，等待间隙锁的释放

• InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁