一、mysql逻辑架构
总体概述
和其他数据库相比，musql有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上。
插件式的存储引擎将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.连接层
最上的一些客户端和连接服务，包含本地的sock通信和大多数基于客户端/服务端工具实现的类似于连接处理、授权认证、及相关的安全方案。再该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于ssl的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
2.1 Management Serveices & Utilities:系统管理和控制工具
2.2 SQL Interface:SQL接口
接收用户的sql指令，并返回用户需要查询的结果。比如select from 就是调用 SQL Interface
2.3 Parser：解析器
SQL命令传递到解析器的时候会被破解析器验证和解析
2.4 Optimizer:查询优化器
SQL语句在查询之前会使用查询优化器，对查询进行优化
用一个例子就可以理解：select uid,name from user where gender = 1;
优化器来决定先投影还是先过滤。
2.5 Cache 和Buffer：查询缓存
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的,比如表缓存，记录缓存，key缓存，权限缓存等。
缓存就是负责读，缓冲负责写。
3.引擎层
存储引擎层，存储引擎正真的负责Mysql中数据库的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4.存储层
数据存储层，主要是将数据存储在运行与裸设备的文件系统之上，并完成与存储引擎的交互。
查询说明
查询流程图：
首先，mysql的查询流程大致是：mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中（一摸一样的sql才能命中），直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存（query cache）—— 它存储select语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理，首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询，预处理器则根据一些mysql规则进一步检查解析树是否合法。
查询优化器当解析树被认为是合法的，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中对号的执行计划。
然后，mysql默认使用的BTREE索引，并且一个大致方向是：无论怎么折腾sql，至少在目前来说，mysql最多用到表中的一个索引。
二、存储引擎
查看命令
#看你的mysql现在已提供什么存储引擎:
mysql> show engines;
#看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
MyISAM和InnoDB
1.InnoDB存储引擎
InnonDB是mysql的默认事务型引擎，它被设计用来处理大量的短期（short-lived）事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况
2.MyISAM存储引擎
MyISAM提供大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁（myisam改表时会将整个表锁住），有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
两者对比
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了**
6. MyISAM表格可以被压缩后进行查询操作
7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
索引优化
SQL查询慢？
① 查询数据过多：条件过滤尽量少
② 关联太多的表，太多join：join原理。用A表的每一条数据 扫描B表的所有数据 ，尽量先过滤
③ 没有利用到索引：
单值索引
复合索引: 条件多时，可以建共同索引（混合索引）。混合索引一般会偶先使用。有些情况下，就算有索引具体执行时也不会被使用
④ 服务器调优及各个参数设置（缓冲、线程数等）（DBA工作）
join和子查询对比
一般推荐使用join，子查询索引可能会失效
索引简介
满足特定查找算法的数据结构
优势：提高数据检索效率，降低数据排序成本
索引分类
主键索引（PRIMARY KEY）：
单值索引：一个索引只包含一个列
唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值
哪些情况不要创建索引
表记录太少
经常增删改的表
where条件里用不到的字段
数据重复且分布平局的表字段
性能分析（Explain）
索引失效
①查询条件where也尽量按照建立索引顺序
②左前缀法则
③不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
④尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
⑤mysql在使用不能与（!= 或者 <>）的时候无法使用索引会导致全表扫描
⑥is not null也无法使用索引，但是is null是可以使用索引的
⑦like以通配符开头（'%xxx'）mysql索引失效会变成全局扫描的操作，解决like '%xxx%'
⑧字符串不加单引号索引失效
⑥少用or，用它连接时索引失效
优化
①保证被驱动表的join字段已经被索引
②left join时，选择小表作为驱动表，大表作为被驱动表
③inner join时，mysql自己会把小结果集的表选为驱动表
④子查询尽量不要放在被驱动表，有可能使用不到索引
order by
尽量使用索引列上完成排序，遵循左前缀，避免使用FileSort
group by
实质是先排序后进行分组，组照索引键的最佳左前缀
去重
尽量不要使用distinct关键字去重
案例：
例子：select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 将产生重复数据，
select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 ) 使用 distinct 关键字去重消耗性能
优化： select kcdz form t_mall_sku where id in( 3,4,5,6,8 ) group by kcdz 能够利用到索引
锁机制
锁的分类
从对数据操作的类型（读\写）分
读锁（共享锁）针对同一份数据，多个读操作可以同时进行而不会相互影响
写锁（排它锁）当前写操作没有完成前，它会阻断其他写锁和读锁
从堆数据操作的粒度分
表锁
行锁
为了尽可能提高数据库的并发性，每次锁定的数据范围越小越好
特定
偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低并发度也最高
InnoDB与MYISAM的最大不同有两点：一是支持事务（Transcation），二是采用行级锁
事务（Transaction）及其ACID属性
事务是由一组sql语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事物的ACIS属性
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保证数据的完整性，事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的’独立‘环境执行。这意味着事务处理过程中的中间状态对外部是不可以见的，反之亦然
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持
并发事务处理带来的问题
更新丢失：和git冲突类似
脏读：一个事务修改还未提交，另一个事务来读
不可重复读：一个事务多次读同一个数据，另一个事务在修改，所以两个事务读的数据不同
幻读：事务A 读取到了事务B提交的新增数据，不符合隔离性
表锁（偏读）
【手动增加表锁】
lock table 表名字1 read(write)，表名字2 read(write)，其它;
【查看表上加过的锁】
show open tables;
【释放表锁】
unlock tables;
加读锁
我们为xxx表加读锁（读阻塞写例子）
session1 session2 获得表xxx的读锁定连接终端 当前session也可以查询该表的记录，当前sessin不可以查询其他没有锁定的表 其他session可以查询或者更新未锁定的表 当前session中插入或者更新锁定的表都会提示错误，其它session插入或者更新锁定表会一直等待获得锁；释放锁 session2获得锁，插入操作完成
加写锁
session1，session2获得表xx的写锁定待session1开启 写锁后，session2再连结终端当前session对锁定表的查询+更新+插入操作都可以执行；其他session对锁定表的查询被阻塞，需要等待锁被释放，在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住，释放锁session2获得锁，查询返回
特点：偏向MyISAM存储引擎，开销小，加锁快，无死锁；锁粒度度大，发生锁冲突的概率最高，并发度最低
#### 行锁（偏写）
特点：
偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突概率最低，并发度也最高。InnoDB与MyISAM的最大不同拥有两点：一是支持事务（Transaction），二是采用行级锁
行锁基本演示：
Session1，Session2更新但是不提交，没有手写commit；session2被阻塞，只能等待提交更新解除阻塞，更新正常进行commit命令执行下面试试session1会话更新a=1 下面实时session更新a=9
无索引行锁升级为表锁
Session1，Session2正常情况，各自锁定各自的行，互相不影响，一个200另一个3000由于在column字段b上面建立了索引，如果没有正常使用，会导致行锁变表锁，比如没加单引号导致索引失效，行锁变表锁被阻塞，等待。session1提交后才阻塞解除，完成更新
select也可以加锁
读锁：
共享锁(Share Lock)
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
用法
SELECT ... LOCK IN SHARE MODE;
在查询语句后面增加 LOCK IN SHARE MODE ，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表（行？），而且这些线程读取的是同一个版本的数据。
写锁：
排他锁（eXclusive Lock）
共享锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
用法
SELECT ... FOR UPDATE;
在查询语句后面增加 FOR UPDATE ，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。
间隙锁危害：
间隙锁带来的插入问题
Session_1Session_2阻塞产生，暂时不能插入commit;阻塞解除，完成插入
【什么是间隙锁】
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。
【危害】
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
