MySql进阶学习笔记

一、配置文件

1、日志

• 二进制日志log-bin。（主从复制）
• 错误日志log-error。（默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等）
• 查询日志log。（默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也需要消耗系统资源）

2、数据文件

存储引擎是myisam, 在data目录下会看到3类文件：.frm、.myi、.myd

• frm文件：存放表结构
• myd文件：存放表数据
• myi文件：存放表索引

存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd

• frm文件：存放表结构
• ibd文件：存放表数据和索引文件

二、逻辑架构

和其它数据库相比，MySql有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1、连接层

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

2、服务层

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

3、引擎层

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

4、存储层

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

三、存储引擎

使用命令查看存储引擎

# 查看mysql现提供的存储引擎
show engines;

# 查看mysql当前默认的存储引擎
show variables like '%storage_engine%';

1、MyISAM与InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真是数据	不仅缓存索引，还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务

2、Percona

Percona为MySQL数据库服务器进行了改进，在功能和性能上叫MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能，为DBA提供一些非常有用的性能诊断工具。另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎XtraDB完全可以替代InnoDB，并且在性能和并发上做得更好；阿里巴巴大部分MySQL数据库起始使用的percona的原型加以修改。

四、索引优化分析

性能下降SQL慢、执行时间长、等待时间长

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

1、常见的join查询

SQL执行加载顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

1. 内连接：inner join

   共有部分

   select A.*,B.* from A inner join B on A.id = B.id
   

2. 左连接：left join

   共有部分+右表没有匹配补null

   select A.*,B.* from A left join B on A.id = B.id
   

   坐标独有

   select A.*,B.* from A left join B on A.id = B.id
   where B.id is null
   
   

3. 右连接：right join

   共有部分+左表没有匹配补null

   select A.*,B.* from A right join B on A.id = B.id
   

   右表独有

   select A.*,B.* from A right join B on A.id = B.id
   where A.id is null
   
   

4. 全连接：union

   合并去重

   select A.*,B.* from A left join B on A.id = B.id
   union
   select A.*,B.* from A right join B on A.id = B.id
   # MySQL不支持全连接写法
   select * from a FULL OUTER JOIN b on a.id = b.id
   # MySQL不支持
   

   左右表独有

   select A.*,B.* from A left join B on A.id = B.id
   where B.id is null
   union
   select A.*,B.* from A right join B on A.id = B.id
   where A.id is null
   # MySQL不支持全连接写法
   select * from a FULL OUTER JOIN b on a.id = b.id where a.id is null or b.id is null
   # MySQL不支持
   
   

<