Mysql的逻辑架构
连接层
客户端连接器（Client Connectors）：提供与MySQL服务器建立连接的支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API与MySQL建立连接。
服务层
服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。
- 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等。
- 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
- SQL 接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
- 解析器（Parser）：负责将请求的SQL解析生成一个解析树。然后根据一些MySQL规则进一步检查解析树是否合法。
- 查询优化器（Optimizer）：当解析树通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
- 缓存（Cache&Buffffer）： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
引擎层
可插拔存储引擎层（Pluggable Storage Engines）：使用可插拔存储引擎架构，能够在运行的时候动态加载或者卸载这些存储引擎。存储引擎负责MySQL中的数据存储的与提取，与底层的系统文件进行交互。MySQL 存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信 ，接口屏蔽了不同的存储引擎之间的差异。现在有很多的存储引擎，常见的就是MyISAM和InnoDB。
查看MySQL中现在提供的存储引擎，在Support列中，YES表示当前版本支持这个存储引擎；DEFAULT表示该引擎是默认的引擎，即InnoDB。
show engines;
查看MySQL现在默认使用的存储引擎
show variables like '%storage_engine%';
| 引擎 | 说明 |
|---|---|
| InnoDB | MySQL默认的存储引擎，支持ACID事务，行锁。目前说目前最稳定的引擎。 |
| MyISAM | MyISAM版本早起版本存储引擎，无事务支持，表锁级别，5.6,5.7版本有些表还是这个引擎，系统表也存在引擎。 |
| Memory | 将所有数据存储在内存中，以便在需要快速查找非关键数据的环境中进行快速访问。这个引擎以前称为HEAP引擎。 |
| CSV | CSV表允许以CSV格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以在正常操作过程中，通常将数据保存在InnoDB表中，只在导入或导出阶段使用CSV表。 |
| Archive | 紧凑的、未索引的表用于存储和检索大量很少引用的历史、归档或安全审计信息。 |
| Blackhole | MySQL在5.x系列提供了Blackhole引擎：“黑洞”，不存储数据，但会正常的记录下Binlog，而且这些Binlog还会被正常的同步到Slave上。binlog分发引擎。 |
| NDB | 分片方式实现数据冗余，分片机制，表会在集群进程之间被复制，企业版特有引擎。 |
| MRG_MYISAM | 对一系列相同的MyISAM表进行逻辑分组，并将引用为一个对象。适合于数据仓库等环境。 |
| Federated | 从多个物理服务器创建一个逻辑数据库，类似dblink。 |
| Example | 该引擎在MySQL源代码中作为示例引擎，演示如何开始编写新的存储引擎。 |
MyISAM引擎和InnoDB引擎简单对比：
| MyISAM引擎 | InnoDB引擎 | |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，不适合高并发 | 行锁，适合高并发 |
| 缓存 | 只缓存索引 | 缓存索引和真实数据 |
| 表空间 | 小 | 大 |
| 关注点 | 性能，偏读 | 事务 |
| 默认安装 | 是 | 是 |
存储层
系统文件层（File System）：该层负责将数据库的数据和日志存储在文件系统上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志，数据文件，配置文件，pid 文件，socket 文件等。
日志文件
MySQL日志
MySQL的日志分类
- 错误日志
- -log-err （记录启动，运行，停止mysql时出现的信息）
- 二进制日志
- -log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用，不建议开启）
- 查询日志
- -log （记录建立的客户端连接和执行的语句）
- 慢查询日志
- -log-slow-queries （记录所有执行超过long_query_time秒的所有查询）
- 更新日志
- -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用）
查询当前日志记录的状况
show variables like 'log%';
错误日志
默认情况下，日志存放在C:\ProgramData\MySQL\MySQL Server 5.5\data下，文件名是：当前主机名.err，默认错误日志打开，无法关闭
慢查询日志
默认没有开启
临时开启：重启后失效
SET GLOBAL slow_query_log=1; # 开启了慢查询日志
SET GLOBAL long_query_time=3; # 设置阈值
SHOW VARIABLES LIKE '%slow_query_log%';
永久开启
修改MySQL安装目录中my.ini文件
# General and Slow logging.
log-output=FILE
general-log=1
general_log_file="general.log"
slow-query-log=1
slow_query_log_file="slow.log"
long_query_time=3
测试
SELECT SLEEP(3);
执行后，可以看见慢查询日志
Explain
可以模拟优化器执行SQL语句，从而知道Mysql是如何处理你的SQL语句的，分析你的SQL语句或者表结构的性能瓶颈
id
验证表的读取和加载顺序，select查询的序列号，包含一组数字，表示查询中，执行select子句或操作表的顺序
- 不同id的执行顺序
- id相同
- 执行顺序从上向下
- id不同
- 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id有相同也有不同
- 序号大的先执行，序号相同的，顺序执行
- id相同
select_type
查询的类型，主要是用于区别普通查询，联合查询，子查询等的复杂查询
| select type | 解释 | 测试sql |
|---|---|---|
| simple | 简单的select | select * from tbl_student 简单的查询，查询中不包含子查询或者UNION。 |
| primary | 需要union或者子查询 | EXPLAIN #年龄大于平均年龄的同学 SELECT * FROM tblstudent WHERE stuage>( SELECT AVG(stuage) FROM tblstudent t1 ) 查询中若包含任何复杂的子部分，最外层则被标记为PRIMARY,一般Primary也是最后被加载的那个 |
| subquery | 在select或者Where包含的子查询 | 上面的SQL就有subquery |
| derived | 派生表 | 在From列表中，包含的子查询被标记为derived,MYSQL会递归执行这些子查询，把结果放在哪临时表（变量内存交换）里面。 select * from (select * from tb_student) t #省市区 注意t1是一个虚表 EXPLAIN select t2.* from (SELECT cid FROM zone WHERE zname='未央区') t1,city t2 where t1.cid=t2.cid |
| union | union | 如第二个select出现在union之后，则被标记为UNION 若union包含在From子句的子查询中 ，外层select将被标记为Derived select * from tb_student union select * from tb_student |
| union result | union结果集 | 两种结果的合并，临时表 select * from tb_student union select * from tb_student |
| depend subquery | 类似depend union | select (select name from test.tb_student a where a.id=b.id) from test.tb_student b |
| dependent union | 查询与外部相关 | （mysql优化器会将in优化成exists） select * from tb_student where id in(select id from tb_student union select id from tb_student) select * from tb_student a where EXISTS (select 1 from tb_student where id = a.id union select id from tb_student where id = a.id) |
type
- System
- 表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，可以忽略不计
- const
- 表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量
- eq_ref
- 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引
- ref
- 非唯一性索引扫描，返回匹配某个单独值的所有行，简单说，用索引查出了多条记录
- range
- 只检索给定范围的行，使用一个索引来选择行，key列显示是用来那个索引，一般就是在where句中出现了between, < , > , in等的查询
- index
- Full Index Scan全索引扫描 ,index只遍历索引树，通常比All快，有些情况，索引会被加载到内存中。读索引肯定比权标扫描要快
- All
- Full Table Scan 全表扫描
查询效率的高低
system>const>eq_ref>ref>range>index>ALL
一般来说，保证查询至少达到range级别，最好达到ref级别
possible_keys&key
possible_keys
显示可能应用在这张表中的索引，一个或多个
查询设计到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。
key
实际使用到的索引，如果为null，则没有使用索引
查询中若是用了覆盖索引，则该索引仅出现在key列表中
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
Key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref
显示索引的那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
每张表有多少行被优化器查询
extra
包含不适合在其他列中显示，但是十分重要的信息
- Using fileSort文件内排序
- 在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序
- using temporary内部临时表
- 需要把数据先拷贝到临时表，最后再删除。也非常的慢，最好不要发生。如果要发生，要确保数据量
- 使用临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by，往往见于统计分析
- using index
- select操作中使用了覆盖索引，Covering Index，避免访问了表的数据行，效率高
- 如果同时出现using where ,表明索引被用来执行索引键值的查找
- using where
- 使用了条件查询
- using join buffer
- 使用了链接缓存
- impossible where
- where子句的值总是false，不能用来获取任何元素
- Using index condition
- 查询的列不全在索引中，where条件中是一个前导列的范围
索引优化
单表
例如，有商品表goods
执行sql语句，查询所有状态为1且价格大于20的商品，并对库存进行排序
EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice>20 ORDER BY gcount
发现结果为，此时不仅类型为全表扫描，而且进行了文件内排序
所以，建立索引，因为查询使用到的字段为gprice，gcount，gstate，所以添加这三个字段的联合索引
CREATE INDEX goods_idx_all ON goods(gstate,gprice,gcount)
再次执行sql语句
EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice>20 ORDER BY gcount
发现此时，虽然命中索引了，而且type也为range，但是还是进行了文件内排序
如果将sql语句中的范围查询改为固定常量
EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice=20 ORDER BY gcount
发现结果正常，没有文件内排序了，所以是范围查询引起的文件内排序，原因是：Btree的工作原理，在创建索引的时候，会先对gstate进行排序，如果gstate相同，对gprice进行排序，如果gprice相同，对gcount进行排序，而gprice处于联合索引中间的位置，如果进行范围查询，也就是range，那么范围查询字段后面的索引将失效
解决方法是
需要范围查询的字段，不参与联合索引的创建
多表
EXPLAIN
SELECT * FROM orders LEFT JOIN goods ON orders.git=goods.git
执行结果，orders表，进行了全表扫描，而goods表，因为有主键索引，索引命中
索引的建立规则
如果使用left join，那么关联字段的索引建立在右表上，right join，索引建立在左表上
索引失效的解决
1、最好全值匹配，索引怎么建立的，就怎么用，使用and连接的查询语句，可以不与索引顺序相同，因为sql优化器会自动进行优化
2、最佳左前缀法，查询时，要从索引建立的最左前列（第一列）开始，不可以跳过中间的列，例如
建立索引
CREATE INDEX goods_idx_all ON goods(gcount,gstate,gprice)
只查询前两列，gcount、gstate，结果正常
EXPLAIN
SELECT * FROM goods WHERE gcount=20 AND gstate=1
只查询第一列，gcount，结果正常
EXPLAIN
SELECT * FROM goods WHERE gcount=20
只查询第二列和第三列，gstate、gprice，进行了全表扫描，因为中间跳过了索引的第一列
EXPLAIN
SELECT * FROM goods WHERE gstate=1 AND gprice=35
只查询第一列和第三列，gcount、gprice，虽然使用了索引，但是索引的长度只使用了一个索引，也就是第一列的索引，并没有使用第三列的索引
EXPLAIN
SELECT * FROM goods WHERE gcount=20 AND gprice=35
3、不在索引列上做任何的操作（计算、函数、类型转换），会导致索引失效，全表扫描
4、不在索引列查询条件前面使用范围查询（>，<，between and），也就是范围查询后的索引全部失效
5、查询结果尽量覆盖索引，也就是，尽量只查询建立索引的字段，少使用select *
6、在MySQL中使用除等于=以外的运算符，会导致索引失效，在MySQL8以后，查询计划的type为all
7、is null和is not null会导致索引失效
8、like中通配符位置除了在右边，其他都会索引失效，即like '%M'、like '%M%'
9、字符串不加单引号，会索引失效，MySQL引擎会进行类型转换，和第三条意思一样
10、使用or连接，会导致索引失效