声明：本文大多数借鉴于网络，如有雷同，望见谅！！！
一：MySql逻辑架构
二：MySql逻辑架构-连接层
当MySQL启动（MySQL服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器都会新建一个线程处理（如果是线程池的话，则是分配一个空的线程），每个线程独立，拥有各自的内存处理空间，但是，如果这个请求只是查询，没关系，但是若是修改数据，很显然，当两个线程修改同一块内存是会引发数据同步问题的。
三：MySql逻辑架构-SQL处理层
连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）
这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。
1.如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
2.解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
3.优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。
四：MySQL缓存
转 https://blog.csdn.net/qzqanzc/article/details/80418125
mysql缓存机制就是缓存sql 文本及缓存结果，用KV形式保存在服务器内存中，如果运行相同的sql,服务器直接从缓存中去获取结果，不需要在再去解析、优化、执行sql。 如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存只得相关条目将被清空。表中得任何改变是值表中任何数据或者是结构的改变，包括insert,update,delete,truncate,alter table,drop table或者是drop database 包括那些映射到改变了的表的使用merge表的查询，显然，对于频繁更新的表，查询缓存不合适，对于一些不变的数据且有大量相同sql查询的表，查询缓存会节省很大的性能
当某个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态，在Innodb中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存
缓存存在一个hash表中，SQL上的任何字符的不同，如空格，注释，都会导致缓存不命中。如果查询有不确定的数据like now(),current_date()，那么查询完成后结果集不会被缓存，包含不确定的数的是不会放置到缓存中
缓存会在内存中开辟一块内存（query_cache_size）来维护缓存数据，其中大概有40K的空间是用来维护缓存数据的元数据的，例如空间内存，例如空间内存，数据表和查询结果映射，SQL和查询结果映射的。
mysql将这个大内存块分为小内存块（query_cache_min_res_unit),每个小块中存储自身的类型、大小和查询结果数据，还有前后内存块的指针。
mysql需要设置单个小存储块大小，在SQL查询开始（还未得到结果）时就去申请一块内存空间，所以即使你的缓存数据没有达到这个大小也需要这个大小的数据块去保存（like linux filesystem’s block)。如果超出这个内存块的大小，则需要再申请一个内存块。当查询完成发现申请的内存有富余，则会将富余的内存空间是放点，这就会造成内存碎片的问题
show variables like '%query_cache_type%'--------查看缓存是否开启
OFF: 关闭
ON: 总是打开
DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存
SET GLOBAL query_cache_size = 4000;------设置缓存内存
五：MySQL存储引擎
在mysql5.5.5以前，默认的存储引擎为MyISAM，之后版本默认为InnoDB
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
MyISAM存储引擎
适用场景：
非事务型应用（数据仓库，报表，日志数据）
只读类应用
空间类应用（空间函数，坐标）
MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中.frm.MYD,.MYI
MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。
MyISAM不支持事务。
MyISAM是基于非聚簇索引进行存储的。
MyISAM提供了大量的特性，包括全文索引，压缩，空间函数，延迟更新索引键等。
进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。
全文索引，是一种基于分词创建的索引，可以支持复杂的查询
延迟更新索引键，不会将更新的索引数据立即写入到磁盘，而是会写到内存中的缓冲区中，只有在清除缓冲区时候才会将对应的索引写入磁盘，这种方式大大提升了写入性能
存储引擎-Innodb
Innodb是一种事务性存储引擎， 完全支持事务的ACID特性
Redo Log 和 Undo Log，Innodb支持行级锁（并发程度更高）
innodb_file_per_table
ON:独立的表空间：tablename.ibd
OFF:系统表空间：ibdataX
mysql5.6以前默认为系统表空间
系统表空间和独立表空间
①.系统表空间无法简单的收缩文件大小
②.独立表空间可以通过optimize table 收缩系统文件
③.系统表空间会产生IO瓶颈
④.独立表空间可以同时向多个文件刷新数据
建议：Innodb使用独立表空间
如何在两种存储引擎中进行选择？
① 是否有事务操作？有，InnoDB。
②是否存储并发修改？有，InnoDB。
③是否追求快速查询，且数据修改较少？是，MyISAM。
④是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。
存储引擎-CSV
组成：
数据以文本方式存储在文件
① .csv文件存储内容
② .csm文件存储表得元数据如表状态和数据量
③ .frm 表结构
特点：
以csv格式进行数据存储
所有列都不能为null的
不支持索引（不适合大表，不适合在线处理）
可以对数据文件直接编辑（保存文本文件内容）
存储引擎-Archive
组成
以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点：
只支持insert和select操作
只允许在自增ID列上加索引
使用场景
日志和数据采集应用
存储引擎-Memory
文件系统存储特点
也称HEAP存储引擎，所以数据保存在内存中
支持HASH索引和BTree索引
所有字段都是固定长度 varchar(10) = char(10)
不支持Blog和Text等大字段
Memory存储引擎使用表级锁
最大大小由max_heap_table_size参数决定
使用场景
hash索引用于查找或者是映射表（邮编和地区的对应表）
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
memory数据易丢失，所以要求数据可再生
存储引擎-Ferderated
特点
提供了访问远程MySQL服务器上表的方法
本地不存储数据，数据全部放到远程服务器上
本地需要保存表结构和远程服务器的连接信息
使用场景
偶尔的统计分析及手工查询
如何使用
默认禁止，启用需要再启动时增加federated参数
mysql://user_name[:password]@hostname[:port_num]/db_name/table_name
MySQL锁
锁的概念
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁对数据库而言显得尤其重要，也更加复杂。
MySQL中的锁
MySQL的锁机制比较简单
其最显著的特点是不同的存储引擎支持不同的锁机制
比如：
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁
①表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
②行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
③页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如OLAP系统
行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
MyISAM的表锁
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
共享读锁语法：
加共享读锁
lock table 表名 read表独占写锁语法：
加独占写锁
lock table 表名 write总结：
①对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
②对MyISAM表的读操作，不会阻塞当前session对表读，当对表进行修改会保存
③一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；
④另外一个session可以查询表中的记录，但更新就会出现锁等待
⑤对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
⑥对 MyISAM表的写操作，当前session可以对本表做CRUD,但对其他表进行操作会报错
InnoDb行锁
在mysql 的 InnoDB引擎支持行锁
行锁:
共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。
排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。
语法
上共享锁的写法：lock in share mode
例如：select * from 表 where 条件 lock in share mode；
上排它锁的写法：for update
例如：select * from 表 where 条件 for update；
注意：
1.两个事务不能锁同一个索引。
2.insert ，delete ， update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
表锁
和MyISAM差别不大
注意：开启一个新事务的时候会解锁表
面试题：系统运行一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构
首先创建一个和你要执行的alter操作的表一样的空的表结构。
执行我们赋予的表结构的修改，然后copy原表中的数据到新表里面。
在原表上创建一个触发器在数据copy的过程中，将原表的更新数据的操作全部更新到新的表中来。
copy完成之后，用rename table 新表代替原表，默认删除原表。
MySQL事务
什么存储引擎支持事务
1.查看数据库是否支持事务（InnoDB支持）？
show engines;
2.查看mysql当前默认的存储引擎？
show variables like '%storage_engine%';
3.查看某张表的存储引擎？
show create table 表名 ;
4.对于表的存储结构的修改？
建立InnoDB 表：Create table .... type=InnoDB； Alter table table_name type=InnoDB;
事务的特性
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
①原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
②一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
③隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
④持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
事务并发问题
①脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
②不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
③幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
①未提交读（READ UNCOMMITED）脏读
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
②已提交读 （READ COMMITED）不可重复读
set SESSION TRANSACTION ISOLATION LEVEL read committed;
③可重复读（REPEATABLE READ）
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
④可串行化（SERIALIZABLE）
set SESSION TRANSACTION ISOLATION LEVEL serializable;
事务隔离级别
事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表
事务隔离级别为串行化时，读写数据都会锁住整张表
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能
事务的语法
开启事务
1、begin
2、START TRANSACTION（推荐）
3、begin work
事务回滚 rollback
事务提交 commit
还原点savepoint
三大范式
①第一范式： 数据库表中的所有字段都只具有单一属性
单一属性的列是由基本数据类型所构成的
设计出来的表都是简单的二维表
②：第二范式：要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系
③：第三范式：指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖
