mysql学习整理

1.mysql架构

 

InnoDB存储引擎的逻辑存储结构和Oracle几乎一样，从大到小分别为：表空间、段、区、页，它们的关系如下图所示：

 

 

2.mysql运行流程

 

3.schema设计规则

  

 

4.索引设计优化

4.1 索引匹配原则

           

 1.等值匹配
 2.组合索引最左前缀匹配
 3.匹配列前缀
 4.匹配范围值

 

 

            

4.2 innodb索引类型

 主键索引：primary key('id')

 secondary索引 ： 

   唯一索引：unique key('col1')

   单列索引：key 'single_idx' ('col1')

   组合索引：key 'muti_idx'('col1','col2')

    

  组合索引原则：
            组合索引最左原则；
            基数大的尽量在前；
            范围查询列靠后：范围查询包括：<,>, between and等
            in(常量表) 不算范围查询，当作等值连接
            排序字段靠后

例如：addkey idx_a_b(a,b); B+树结构：

很显然，对于where a = xxx and b=xxx 这样的语句是可以使用这个复合索引的。现在看看对单个列的情况，where a = xxx也是可以使用该复合索引，因为a列在复合索引中也是有序的，但对于where b =xxx 这样的语句是无法使用该复合索引，因为它是无序的。

前缀索引：   key (title(7))

全文索引：   fulltext key('col1')

 

5.SQL分析和优化 

SQL分析

    1.单SQL不同阶段执行顺序理解：

 

select 列集合 --4
from 表/数据集 --1
where 条件 --2
group by 按列标示分组 --3
having 分组结果限制条件 --5
order by 排序列/标示 --6
limit 偏移量,结果数 --7

    2.复合SQL理解:

       2.1子查询

       2.2 联接查询

 SQL优化:

 

id	SELECT识别符。这是SELECT的查询序列号
select_type	SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)
table	输出的行所引用的表
type	联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref	显示使用哪个列或常数与key一起从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

 

5.索引模型

 

mysql b+树结构

      先看看几种树形结构：

　　1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结构。

　　2 B树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：┌m/2┐ - 1 <= j <= m - 1；一个节点的子节点数量会比关键字个数多1，这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间，非常形象，也容易和后面的 B+树区分。由于数据同时存在于叶子节点和非叶子结点中，无法简单完成按顺序遍历B树中的关键字，必须用中序遍历的方法。

　　3 B+树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：┌m/2┐ - 1 <= j <= m；子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中，且叶子节点间增加了横向的指针，这样顺序 遍历所有数据将变得非常容易。

　　4 B*树：一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是1每个非根节点所包含的关键字个数 j 满足：┌m2/3┐ - 1 <= j <= m；2非叶节点间添加了横向指针。

 

B+树适合作为数据库的基础结构，完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问（随机访问即给出任意一个地址，要求返回这个地址存储的数据）但是容量较小。而硬盘的随机访问要经过机械动作（1磁头移动 2盘片转动），访问效率比内存低几个数量级，但是硬盘容量较大。典型的数据库容量大大超过可用内存大小，这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。如下图所示：通常向下读取一个节点的动作可能会是一次磁盘IO操作，不过非叶节点通常会在初始阶段载入内存以加快访问速度。同时为提高在节点间横向遍历速度，真实数据库中可能会将图中蓝色的CPU计算/内存读取优化成二叉搜索树（InnoDB中的page directory机制）。

　　真实数据库中的B+树应该是非常扁平的，可以通过向表中顺序插入足够数据的方式来验证InnoDB中的B+树到底有多扁平。我们通过如下图的CREATE语句建立一个只有简单字段的测试表，然后不断添加数据来填充这个表。通过下图的统计数据（来源见参考文献1）可以分析出几个直观的结论，这几个结论宏观的展现了数据库里B+树的尺度。

　　1 每个叶子节点存储了468行数据，每个非叶子节点存储了大约1200个键值，这是一棵平衡的1200路搜索树！

　　2 对于一个22.1G容量的表，也只需要高度为3的B+树就能存储了，这个容量大概能满足很多应用的需要了。如果把高度增大到4，则B+树的存储容量立刻增大到25.9T之巨！

　　3 对于一个22.1G容量的表，B+树的高度是3，如果要把非叶节点全部加载到内存也只需要少于18.8M的内存（如何得出的这个结论？因为对于高度为2的树，1203个叶子节点也只需要18.8M空间，而22.1G从良表的高度是3，非叶节点1204个。同时我们假设叶子节点的尺寸是大于非叶节点的，因为叶子节点存储了行数据而非叶节点只有键和少量数据。），只使用如此少的内存就可以保证只需要一次磁盘IO操作就检索出所需的数据，效率是非常之高的。

 

聚簇索引和非聚簇索引：

        InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

　　MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

　　为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

　　我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

　　1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

　　2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

 

 

 

 

索引参考：

mysql索引存储原理

MySQL的InnoDB索引原理详解

b+树 算法

评论

1 楼 carlosfu 2016-05-05  

赞1！！