mysql精简记录

一、Mysql架构介绍

1.1、MySQL逻辑架构

逻辑架构示意图

• Connectors：指的是不同语言中与SQL的交互。
• Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层。
• Management Serveices & Utilities：系统管理和控制工具。备份、安全、复制、集群等等。
• SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
• Parser：SQL语句解析器。
• Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。For Example： select uid,name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
• Caches & Buffers：查询缓存。
• Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎(注意：存储引擎是基于表的，而不是数据库)。
• File System：数据落地到磁盘上，就是文件的存储。

和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。 

逻辑架构分层

• 连接层：最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。
• 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
• 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

1.2、SQL性能下降的原因

• 查询语句写的差

• 索引失效：索引建了，但是没有用上

• 关联 查询太多join（设计缺陷或者不得已的需求）

• 服务器调优以及各个参数的设置（缓冲、线程数等）

1.3、SQL执行顺序

select              # 7
from                # 1
join                # 3
on                  # 2
where               # 4 
group by            # 5
having              # 6
distinct            # 8
order by            # 9
limit               # 10

SQL执行顺序分析

二、索引优化分析

2.1、索引的优势和劣势

优势

• 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本
• 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
• 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

重点：索引会影响到MySQL==查找(WHERE的查询条件)和排序(ORDER BY)==两大功能！

2.2 索引的查询方式

• 基于页的形式进行管理索引

如 ：查询id=4的 直接先比较页 先去页目录中找,再去 数据目录中找

B-Tree（B树）与B+Tree的区别是什么呢？

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

​ 从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果

data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查

询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子

节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗3）。**也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录**。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将==根节点常驻内存==的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

1次IO的情况：我们的B+Tree只有2层，由于顶层常驻内存，所以只有查找数据的时候进行一次磁盘IO ；
2次IO情况：B+Tree有三层，同样是顶层查询走内存，所以查到数据需要进行2次磁盘IO ；
3次IO情况：B+Tree有四层，或者B+Tree有三层采用Innodb存储引擎，索引是辅助索引！

聚簇索引和非聚簇索引的区别：

 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置
注意：在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、单值索引、唯一索引。**辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找**我们把==回到主键索引树搜索的过程，我们称为回表

2.3、索引性能分析

        

2.3.1、MySQL Query Optimizer

MySQL中专门负责优化SELECT语句的优化器模块

主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

2.3.2 explain

EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

EXPLAIN怎么使用

• 语法：explain + SQL语句

测试

默认以表的形式展示

EXPLAIN能干嘛

可以查看以下信息

• • id：表的读取顺序
  • select_type：数据读取操作的操作类型
• • possible_keys：哪些索引可以使用
  • key：哪些索引被实际使用
• • ref：表之间的引用
  • rows：每张表有多少行被优化器查询

2.3.5 explain字段详解

id：包含一组数字，表示查询中执行select子句或操作表的顺序

值有以下三种情况

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

select_type：表示查询中每个select子句的类型（简单OR复杂）

该字段存在以下的6个值 ：

• SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。

select * from table ;  #就是最简单的查询

• PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为：PRIMARY。

select * from t1 where (select * from t2) ; #对t1表进行的就是primary查询

• SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY

select * from t1 where (select * from t2) ;   #对t2的表的查询就是子查询

• DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。

select * from t1 join (select * from s1) t2 on t2.id = t1.id  #会将t2这个临时表看为一个虚表也就是drived

• UNION：如果第二个SELECT出现在UNION之后则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

select * from t1 union select * from t2 ;  #对t2的查询类型就是union

• UNION RESULT：从UNION表获取结果的SELECT。

type：表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下

从最好到最次依次是**System > const > eq_ref > ref > range > index > all**除了ALL没有用到索引，其他级别都用到索引了。

一般来说，得保证查询至少达到range级别，最好达到ref

• system ：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。【表中只有一条记录】
• const : 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where中，MySQL就能将该查询转化为一个常量 【条件写死，只查一条数据】
• eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。除了 system 和const 类型之外, 这是最好的联接类型。 【只有一条满足条件的数据】
• ref ：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。 【有多条满足条件记录】
• range ： 只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。 【在某个范围之间的查询】
• index ：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。【全扫描，扫描的是索引树】
• 也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。 【全扫描，扫描的是磁盘】
• ALL：Full Table Scan，没有用到索引，全表扫描。
   

possible_keys : 显示可能应用在这张表中的索引，一个或者多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key : 实际使用的索引。

如果为NULL，则没建或没有使用索引，即索引失效。查询中如果使用了覆盖索引，则该索引仅仅出现在key**列表中。**与Extra有关

覆盖索引： 表中为字段添加的索引和要查询的字段一致

key_len : 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的 用于表示本

次查询中，所选择的索引长度有多少字节，通常我们可借此判断联合索引有多少列被选择了。**key_len越短越好，**说白了就是字段类型越

短越好

ref : 显示索引的哪一列被使用了，如果可能的话，是一个常数。

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值**【当前表中的索引字段，引用了谁！】**

rows ： 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

这个rows就是mysql认为必须要逐行去检查和判断的记录的条数。 rows越少越好

举个例子来说，假如有一个语句 select * from t where column_a = 1 and column_b = 2;

全表假设有100条记录，column_a字段有索引（非联合索引），column_b没有索引。

column_a = 1 的记录有20条， column_a = 1 and column_b = 2 的记录有5条。

那么最终查询结果应该显示5条记录。 explain结果中的rows应该是20. 因为这20条记录mysql引擎必须逐行检查是否满足where条件。

Extra ： 包含不适合在其他列显示，但十分重要的额外信息

Using filesort ：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary

要解决查询，MySQL需要创建一个临时表来保存结果。 如果查询包含不同列的GROUP BY和ORDER BY子句，则通常会发生这种情况。官方解释：”为了解决查询，MySQL需要建立临时表(temporary table)来暂存中间结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过家里临时表来辅助处理；

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

原理：select col 1 from t group by col1 order by col2 ,在此sql语句当中我们的order by 执行在select 之后，会按照col2分组，但是我们的select只能查出col1，不存在col2字段所以无法分组会报错！

 Using Index：查询时不需要回表查询，直接通过索引就可以获取查询的数据

• Using where：表明使用了WHERE过滤。
• Using join buffer：使用了连接缓存。
• impossible where：WHERE子句的值总是false，不能用来获取任何元组。

参考文章

• explain中type字段中ref和eq_ref的区别：
• explain字段详解：【强烈推荐】
• explain中的ref字段
• explain中的key_len字段
• explain中的rows字段
• explain中Extra字段详解