数据库的基本信息

一、数据库的基本信息

1.数据库读取数据方式及特点

在innerdb中数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，操作系统在读取数据的时候会以页为最小单位，并且帮你预读后4kb的数据（磁盘io的预读能力 也叫空间局部性原则） innerdb就很好的利用这种特性在，存储和读取数据的时候以页（16kb）作为交互的基本单位

2.常见树结构以及特点

平衡二叉树：节点之间高度不能大于一 通过左旋保证高度差不会超过1
缺点：太深了 数据处的深度决定着他的io操作次数 i哦操作耗时大 2太小了 每一个磁盘快（节点/页）保存的数据量太小了 但是每次对比都需要从磁盘中加载出出来进行对比 增加了很多io次数
多路平衡查找树：（绝对平衡二叉树 子节点之间的不能有高度差）b-tree 3路分叉 比二叉树多了指向中间的子节点的物理地址 中间的子节点
加强版多路平衡查找树 ：b+tree（b-tree的加强版） 1，叶子结点是顺序排列，并且存储用户的所有数据2.非叶子节点不保存数据相关信息，只保存关键字和子节点的引用

3.innerdb的b+树特点

1.每个索引都对应一棵B+树。用户记录都存储在B+树的叶子节点，所有目录记录都存储在非叶子节点。

2.InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

3 可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

4.B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

5 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中的查找非常快
所以innerdb是由主键和数据组成的聚集索引和普通二级索引组成的

6.一个千万级别的数据树的高度也大概在3到四层之间

4.聚集索引和二级索引区别

聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。二级索引与聚簇索引有几处不同：1. 按指定的索引列的值来进行排序2. 叶子节点存储的不是完整的用户记录，而只是索引列+主键。3. 目录项记录中不是主键+页号，变成了索引列+页号。4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫回表

二、查询优化器

1.打开查询优化器以及查看sql的trace

set optimizer_trace="enabled=on"命令
开启查询优化器，然后执行sql后 通过select * from information_schema.OPTIMIZER_TRACE 查看sql的执行trace 优化器怎么去选择索引，trace主要记录了先是对sql 的优化如常量传递（a = 1 and b =1 ）和等值传递(a=5 and b=5 and c=5),还有对索引和全量表扫描的计算成本
trace解析链接: link.

2.innerdb 全量表扫描成本和索引扫描成本计算

读取以及检测一条记录是否符合搜索条件的成本默认是0.2，io成本 就是讲数据读到内存的成本 和cpu成本 对结果集进行判断 InnoDB存储引擎规定读取一个页（16k）花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2，查询优化器对索引的选择 主要区分为

1.全量表扫描成本

对于全表扫描来说聚集索引所占的页乘以页的大小的数据量 对于其他索引来说主要分为读取附注索引的和回表，查询优化器通过判断不同方式的成本大小去选择最合适的方式，
SHOW TABLES TATUS LIKE ‘titles’;可以看到这个表的数据量的大小Data_length 以及多少行total 所以全表扫描的成本就是Data_length/16/1024 + total *0.2

2.二级索引扫描成本

我们从索引中查询记录时，不管是=、in、>、<这些操作都需要从索引中确定一个范围，不论这个范围区间的索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间个数section的I/O成本和读取一个页面是相同的。本例中使用PRIMARY的范围区间只有一个：(10101, 20000)，所以相当于访问这个范围区间的索引付出的I/O成本
所以二级索引扫描的成本是 selection + 聚集索引成本

链接: link.

2.in中字段太多对查询优化器影响

对于in里面的条件来说，mysql有个限制超过200个 他去预估的时候会算一个平均值，mysql中会维护两个表，一个是记录对应表的行数，所占的大小等信息，show index form“表名”，可以看到索引列中不重复的个数的平均值，然后根据平均值乘以in条件的个数去估算，这样的就会不准。

3.查询优化器对于sql的优化

1.join优化

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的

连接的大致原理是：1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

上面的过程就像是一个嵌套的循环,所以这种驱动表只访问一次,但被驱动表却可能被多次访问,访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接 所以我们要尽量减少被驱动表的被查的次数

1.join buffe

Mysql中有一个叫做join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和joinbuffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价
驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在join buffer中放置更多的记录，可以设置join buffer的大小

2.外链接消除

对于内链接来说 查询优化器会选择成本最小的方式来动表和被驱动表的位置转换 但是对于外链接来说 驱动表和被驱动表的位置固定 优化器没法自动切换

1.询物化（in）

在mysql中，不直接将不相关子查询in的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是很快的。如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size的默认值，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。查询优化器就可以评估不同连接顺序需要的成本是多少，根据合适方式再将这个物化的表于外层表进行内链接。

将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能
使用限制：由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询

2.半链接（in）

对于in来说在其实可以视为 对于子表去重之后的内链接 所以如果当t2表查的那个列是主键或者唯一索引的时候可以改为内联， 而对于非主键或者非唯一索引，会才用一种半连接semi join
半连接主要有几种实现方式
uplicateWeedout： 建立历史表 将结果集 如果有重复的就不添加semi-join结果集中的重复值的方式称之为
first_match：将t1表的数据向临时表匹配找到了 就返回没有找到就丢弃
使用限制：
1.外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来
2.使用NOT IN而不是IN的情况
3.子查询中包含GROUP BY、HAVING或者聚集函数的情况
4.子查询中包含UNION的情

3.exists

对于不能使用半链接 也不能使用物化的sql，还可将sql转为exists
例子：
selectfromt1whereain(selectafromt2wheret2.e=t1.e)
转为：selectfromt1whereexists(select1fromt2wheret2.e=t1.eandt1.a=t2.a）
如果a字段有索引就可用到索引

3.派生表的优化

列子：select * from (selecta, b from t1) as t
上面这个sql，子查询是放在from后面的，这个子查询的结果相当于一个派生表派生表,表的名称是t，有a，b两个字段。对于派生表，有两种执行方式