mysql高级---mysql存储引擎、性能下降原因、7种join图解

Mysql逻辑架构

总体概览

最上面一层是客户端的连接服务，主要完成客户端的连接处理，授权认证。在该层有一个线程池，每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

中间这一层可以完成查询解析，分析，优化，缓存。存储过程，触发器，视图也是在这一层。

最下面一层是存储引擎，负责mysql中数据的存储和提取。

和其它数据库有所不同，mysql的存储引擎是可插拔的，可以根据不同的场合使用不同的存储引擎。

Mysql存储引擎

存储引擎就是存储数据，更新查询数据，建立索引等等的技术实现方式，mysql5.0支持的存储引擎包括: InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中使用最多的是InnoDB 和MyISAM，mysql5.5之后InnoDB是默认的存储引擎

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

MyISAM 与 InnoDB 区别

1、InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM就不可以了；

2、MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及安全性较高的应用；

3、InnoDB 支持外键，MyISAM 不支持；

4、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM表中可以和其他字段一起建立联合索引；

5、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表。

查看mysql现在已提供什么存储引擎

Show engines;

查看mysql当前默认的存储引擎

Show variables like ‘%storage_engine%’;

sql性能下降的原因

性能下降：执行时间长/等待时间长

• 查询语句写的烂
• 索引失效 （单值/复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲/线程数等）

常见通用的7种join查询

SQL的执行顺序

手写顺序

1. Select
2. From
3. Join
4. on
5. Where
6. Group by
7. Having
8. Order by
9. Limit

机读顺序

2 From
4 on
3 Join
5 Where
6 Group by
7 Having
1 Select
Distinct
8 Order by
9 Limit

Join图解

左连接left

A left join B on A.key = B.key;

A left join B on A.key = B.key where B.key = null;

右连接right

A right join B on A.key = B.key;

A right join B on A.key = B.key where A.key = null;

内连接inner

A inner join B on A.key = B.key;

全外连接Full outer

A Left join b union b right join a where a.key = null;
B Right join a union a left join b where b.key = null;

A Left join b where b.key = null union b right join a where a.key = null;