荐 【MySQL】MySQL概念解析（二）

一、MySQL逻辑架构

【1】连接层

最上层是一些客户端和连接服务，包含本地socket通信和大多数几乎客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

【2】服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如存储过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统性能

【3】引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据得到存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

【4】存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

【5】架构图

二、MySQL存储引擎

1、查看MySQL所有支持的存储引擎

show engines;

2、查看当前MySQL默认存储引擎

show variables like "%storage_engine%"

3、最常用的两个存储引擎对比

三、索引优化分析

【1】性能下降的原因

1、性能下降SQL慢
	查询语句写的烂
	索引失效（单值、复合）
2、执行时间长
	关联查询太多join（设计缺陷或不得已的需求）
3、等待时间长
	服务器调优及各个参数设置（缓冲、线程数等）

【2】常见通用的join查询

1、SQL执行顺序

手写
	select 
	distinct
	from
	on
	where
	group by 
	having
	order by
	limit

机读
	from
	on
	where
	group by
	having
	select
	distinct
	order by
	limit

2、join图

【3】索引简介

1、索引是什么？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

索引就是排好序的快速查询数据结构

数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等

2、索引优势

提高数据检索效率，降低数据库的IO成本

降低数据排序的成本，降低了CPU的消耗

3、索引劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询

【4】索引分类

1、单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

2、唯一索引

索引列的值必须唯一，但允许有空值

3、复合索引

即一个索引包含多个列

4、基本语法

创建
create [unique] index indexName on tableName (columnname(length));
alter tableName add [unique] index [indexName] on  (columnname(length));

删除
drop index [indexName] on tableName;

查看
show index from tableName

【5】索引结构

1、B树索引
2、Hash索引
3、full-text全文索引
4、R-Tree索引

【6】哪些情况需要创建索引？

1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）
5、where条件里面用不到的字段不创建索引
6、单键/组合索引的选择问题（高并发下倾向创建组合索引）
7、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8、查询中统计或者分组字段

【7】哪些情况需要不创建索引？

1、表记录太少
2、经常增删改的表
3、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引

【8】性能分析

Explain

1、Explain是什么？

使用EXPLAIN关键词可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句，分析你的查询语句或是表结构的性能瓶颈

2、Explain作用

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

3、Explain使用

Explain + SQL语句

4、各字段解释

id
	select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
	三种情况：
		id相同，执行顺序由上至下
		id不同， 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
		id相同和不同，同时存在 （derived ：衍生）：不同id从大到小执行，相同从上到下执行

select_type
	常见值：simple、primary、subquery、derived、union、union result
	查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
		simple：简单的select查询，查询中不包含子查询或者union
		primary：查询中若包含任何复杂的子部分，最外层标记为primary
		subquery：在select、where中的子查询标记为subquery
		derived：在from中的子查询标记为 derived
		union：若第二个select出现在union之后，则被标记为union
		union result：从union表获取结果的 select ，则被标记为 union result

table
	显示这一行的数据是关于哪张表的

type
	显示查询使用了什么类型
	常见最好到最差依次是:system > const > eq_ref > ref > range > index > all
	一般来说，需要保证查询至少到range级别，最好能达到ref
		system:表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
		const:表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就能将查询转换为一个常量
		eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  		ref:
  		range:
  		index:
  		all:
  		
possible_keys

key

key_len

ref

rows

extra

本文地址：https://blog.csdn.net/qq_43733123/article/details/107146570