MySQL是怎样运行的？打卡汇总

启动选项和系统变量

  1. mysql配置方式之一，在命令行上使用选项配置。有长形式和短形式之分，同时等号=前后不能有空格。对于常用的选项提供短形式，也就是选项首字母前加‘-’前缀。这种方式只对当次启动有效。

  2. mysql配置方式之二，在配置文件中使用选项。会按照一定路径寻找配置文件，ini，cnf都为配置文件。配置文件内容可以分组。可以通过命令行指定配置文件的优先级，也可以指定使用特定的配置文件。

  3. mysq配置系统变量。查询系统变量，SHOW VARIABLES [LIKE 匹配的模式]; 可以通过启动选项或配置文件来设置系统变量。

  4. mysql系统变量的作用范围，GLOBAL和SESSION.

  5. mysql状态变量，自动生成，只支持查看。

 

字符集和比较规则

  1. 打卡： 1.字符集和比较规则，字符集需要界定清楚字符范围，映射规则，比较规则是转化成二进制后比较大小的规则，同一种字符集可以有多种比较规则。
  2. 一些重要的字符集。ASCII < ISO 8859-1 < GB2312 < GBK < utf8
  3. MySQL支持的字符集和排序规则。基本所有字符集都支持，但常用的就两个utf8mb3和utf8mb4；比较规则查看 SHOW COLLATION LIKE 'utf8\_%', 名称后缀意味着该规则是否区分重音和大小写；
  4. MySQL四个级别的字符集和比较规则，服务器级别，数据库级别，表级别，列级别。当插入的数据不是字符集所支持的数据，则会报错或乱码。
  5. 修改字符集或比较规则的任一方，另外一方都会自动修改。
  6. MySQL中字符集的转换，系统变量character_set_client为服务器解码请求时使用的字符集，系统变量character_set_connection 为服务器处理请求时会把请求字符串转为这个字符集，系统变量character_set_results为服务器向客户端返回数据时使用的字符集。统一设置这三个系统变量的值为同一个字符集 SET NAMES utf8;
  7. 如果character_set_connection代表的字符集和具体操作列的字符集一致，则直接进行相关操作，否则得先转换为具体操作的列的字符集之后再进行操作

InnoDB记录结构

  1. innoDB页概念，以页为单位在磁盘和内存中进行交互，一页大小为16kb
  2. innoDB行格式，存放数据到磁盘的方式，有四种，compact、Redundant、Dynamic、Compressed，其中innoDB默认的为Dynamic。指定行格式的语法为create table test（）row_format=compact;
  3. COMPACT行格式，变长长字段长度列表，NULL值列表，记录头信息，记录真实数据。其中边长字段长度列表记录变长字段（VARCHAR(M)、VARBINARY(M)， TEXT真实数据占用的字节长度按照列的顺序，逆序存放。并且只存放非NULL的列内容占用的长度。
  4. NULL值列表，统计允许为NULL值的列中，为null值则存1，不为null则存1，逆序存储在二进制位里，最少一个字节，不足补0，不够则加一个字节。
  5. 记录头信息，固定的5个字节组成，存储其他信息
  6. 记录真实数据，三个隐藏列（row_id(主键id，6字节，自己设置了，则这个没有), transaction_id(事务ID， 6字节), roll_pointer(回滚指针， 7字节)）
  7. CHAR(M)列的存储格式，存储一个空字符串也会占用字符集对应的最少字节数，这样设计不容易产生碎片但比较占空间。
  8. Redundant行格式。字段长度偏移列表（所有列都会存长度信息，按照字段的偏移量来存长度）。对NULL值处理时，将列对应的偏移量值的第一个比特位作为是否为NULL的依据，如果为1则是NULL，否则不是。
  9. 行溢出数据。数据量超过16kb则会出现行溢出，本页存储768个字节，剩下数据分散存储到其他页，本页20个字节记录其他页地址。
  10.  一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。
  11. Dynamic行格式，跟上面所说的compact行格式的区别是，Dynamic行格式会把所有的真实数据存储到其他的页面中。

InnoDB数据页结构

  1. 数据页结构，总共7个部分，实际存储行数据是按照设定的行格式存储到User Records。

  2. 行记录里面的记录头信息，6个重要的属性，其中n_owned表示当前组拥有的记录数。next_record表示下一条记录的相对位置。

  3. 数据页结构中的页目录 Page Directory, 将数据页里面的数据分为几个组，每个组最后一条记录的头信息的n_owned记录该组多少条记录，将每个组最后一条记录的地址偏移量去除按顺序存储在页的尾部也就是页目录，称地址偏移量为槽。

  4. 根据页目录使用二分法查找数据。

  5. 数据页结构中的页面头部 Page Header，记录数据页中存储的记录的状态信息。

  6. 数据页结构中的文件头部 File Header，记录页本上的信息和与外界的联系属性。针对各种类型的页都通用

  7. 数据页结构中的文件尾部 File Trailer， 校验同步是否成功的用途。有校验和、日志序列位置。针对各种类型的页都通用。

 

B+树索引

  1. innoDB中的索引方案，插入数据的时候首先会根据主键的大小顺序进行排序，按顺序插入到用户记录数据页中，再生成一个新的目录项记录页存放每个用户记录数据页里的最小id和页号，层数依次递增，最上层有个根页面。其中存放真是用户记录数据页的那一层称为叶子节点，其余用来存放目录项的为非叶子节点或内节点，最上层的为根节点。

  2. 聚簇索引，两个特点，①使用记录主键值的大小进行记录和页的排序，②B+树的叶子节点存储的是完整的用户记录。InnoDB会默认自动创建聚簇索引，同时在InnoDB中，聚簇索引就是数据的存储方式，也就是所谓的索引既数据，数据既索引。

  3. 二级索引，使用除主键外的列创建索引，这个索引会另外建颗B+树，里面叶子节点存储这个列的值和主键列的值。根据二分法获取到主键后，再去聚簇索引中查找一遍完整的用户记录。这个过程也称为回表。

  4. 联合索引，先把各个记录和页按照列1进行排序，列1相同的则按照列2进行排序，B+树叶子节点处的记录由列1、列2和主键组成，获取到主键再去聚簇索引查找一遍完成数用户记录。联合索引本质上也是二级索引。

  5. 根节点万年不动窝。

  6. MyISAM中的索引方案，将数据按照插入顺序存储到一个文件中，再使用MyISAM引擎为表的主键创建一个索引放到 索引文件 中，索引的叶子节点存储的是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的用户记录。意味着MyISAM中建立的索引全部都是二级索引。

  7. 创建和删除索引的语句。

 

B+树索引的使用

  1. 索引的代价，每建立一个索引，都需要建立一颗B+树，建多了就非常占用空间；每次对表的增删改，都需要修改各个B+树索引排列顺序，导致性能损耗。

  2. B+树索引适用的条件，包括【全值匹配，匹配左边的列，匹配列字符串的前缀，匹配范围值，精确匹配某一列并范围匹配另外一列，排序，分组】

  3. 回表的代价，需要回表的记录越多，使用二级索引的性能就越低。MySQL有自带的查询优化器，如果需要回表的记录太多则会直接使用聚簇索引进行扫描。

  4. 为了防止回表带来的性能损耗，建议在查询列表里查询只包含索引列的字段，这样就不需要使用到聚簇索引回表，这个叫覆盖查询。

  5. 在什么列上创建索引？①经常搜索、排序、分组的列上创建索引，②基数大的列上创建索引，③列的类型尽量小的列上创建索引，比如这几个列约往左越适合创建索引（TINYINT < MEDIUMINT < INT < BIGINT）。④只对字符串值的前几个字符创建索引，语法 KEY idx_name (name(10))。⑤让索引列在比较表达式中单独出现。

 

单表访问方法：

　　1.通过主键或者唯一二级索引列来定位一条记录的访问方法定义为 const，注意点：和常数进行等值比较，如果唯一二级索引是联合索引，那么索引的每个列都需要与常数比较。查询到的数据只有一条

　　2. 搜索条件为二级索引列与常数等值比较的方法为 ref，查询到的数据有多条连续的数据

　　3. 当使用二级索引的方式查询 or关系且包含Null值的查询，称之为ref_or _null 访问方法

　　4. 利用索引进行范围匹配的访问方法，称为 range

　　5. 采用遍历二级索引记录的执行方式称之为 index

　　6. 使用全表扫描执行查询的方式称之为 all

　　注意事项

　　1. 如何简化sql语句的搜索条件。在为某个索引确定范围取键的时候只需要把用不到相关索引的搜索条件替换为 TRUE 就好了，待到徽标操作时再使用它们进行过滤。　　

　　2. 复杂搜索条件下找出范围匹配的区间。 首先查看where子句中的搜索条件都涉及到哪些列，哪些列可能使用到索引。 对于哪些可能用到的索引，分析他们的范围区间。 假设使用其中一个索引执行查询，把其他条件以TRUE 替代

　　3. 索引合并，一般情况下执行一个查询时最多只会用到单个二级索引，但特殊情况可能会再一个查询中使用多个二级索引。index merge Intersection合并，and交集，先从多个二级索引查询到结果取交集，and条件等值完全匹配。主键列范围匹配。因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的（方便求交集）。 Union合并， or并集 Sort-Union合并， 这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

 

 

连接的概念

　　1. 连接的本质是多表相连产生的笛卡尔积结果集

　　2. 驱动表和被驱动表，驱动表只访问一次，被驱动表访问多次

　　3. 内连接，左连接，右连接（inner join，left join, right join）

　　4. 内连接中的WHERE子句和ON子句是等价的

　　5. 左连接和右连接，必须使用ON子句来指出连接条件

　　6. 左连接是指以左边的表为准匹配右边表内容，没内容则null取代

　　连接的原理

　　1. 嵌套循环连接。（for 嵌套的方式）

　　2. 使用索引加快连接速度。

　　3. 基于块的嵌套循环连接。提前划出一块内存（join buffer）存储驱动表结果集中的记录，然后开始扫描被驱动表，每一条被驱动表的记录一次性和这块内存中的多条驱动表记录匹配，可以显著减少被驱动表的I/O操作。

 

posted @ 2020-01-04 17:57  wanglai  阅读(875)  评论(0)  编辑  收藏  举报