关键词：
高性能MySQL卷一之架构分析
Mysql架构
- 最上层负责处理连接处理，授权认证和安全等
- 第二层负责查询解析,分析，优化，缓存以及所有的内置函数，所有跨存储引擎的功能都在这层实现: 存储过程，触发器，视图。
- 第三层包含了存储引擎，负责MySQL中数据的存储和提取，服务器规定了一组通用的API接口，不同的存储引擎底层实现可以不同，但是都要实现相关接口才可以。存储引擎包含了十几个底层函数，主要负责处理: 事务的开始，根据主键提取记录等，但存储引擎不会去解析SQL。
优化与执行
MySQ会解析查询，并创建内部数据结构(解析树),然后进行各种优化，包括重写查询，决定表读取顺序，选择合适的索引等。
用户可以通过特殊的关键字提示优化器，影响它的决策过程，也可以请求优化器解释(explain)优化过程的各个因素，使用户知道服务器是如何进行优化决策的，便于进行参考，并进行查询重构等手动优化操作。
优化器不关心底层的存储引擎是哪种，但是存储引擎会影响查询的优化，优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息,例如: 某些存储引擎的某种索引可能对特定的查询有优化。
对于select查询语句，在解析查询前，服务器会先检查查询缓存，如果有缓存对应的插件，那服务器就直接返回查询缓存中的结果集，而不需要执行查询解析，优化和执行的整个过程了。
并发控制
无论何时，只要有多个查询在同一时刻修改数据，都会产生并发控制问题
下面我想从两个层面来剖析MySQL是如何处理并发控制的，这两个层面分别是: 服务器层和存储引擎层
读写锁
MySQL中有两种类型的锁，分别是共享锁(读锁)，排它锁(写锁)。
读锁是共享的，而写锁会阻塞其他的写锁和读锁。
在实际的数据库系统中，每时每刻都在发生锁定，当用户在修改一部分数据的时候，MYSQL会通过锁定防止其他用户读取同一个数据，大多数时候，MYSQL锁的管理都是透明的。
锁粒度
在给定的资源上，锁定的数据量越少，系统并发度越高。
表锁
表锁是MYSQL中最基本的锁策略,并且是开销最小的策略。
表锁会锁定整张表,一个用户再对表进行写操作前，先需要获得写锁，这会阻塞其他用户对当前表的读写操作，而在灭有写锁的时候，用户才能在读取的时候获得读锁，读锁之间是不会相互阻塞的
写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到锁队列中读锁的前面
尽管存储引擎可以管理自己的锁，MYSQL本身还是会使用各种有效的表锁来实现不同的目的。例如：服务器会为ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制
行级锁
行级锁可以最大程度支持并发操作，但同时也带来了最大的锁开销。
在innoDB和XtraDB以及一些其他的存储引擎中实现了行级锁，行级锁只在存储引擎层实现，而MySQL服务器层没有实现，服务器层完全不了解存储引擎中的锁实现
事务
事务就是一组原子性的SQL语句，事务内的语句要么全部执行成功，要么全部执行失败
事务有ACID四大特性:
- 原子性: 一个事务必须被视为一个不可分割的最小工作单元，事务内的语句要么全部执行成功，要么全部执行失败
- 一致性: 如果在事务执行过程中，事务中的一条sql语句报错，那么因为事务没有提交，因此事务中所做的修改不会保存到数据库中
- 隔离性：一个事务做出的修改，在最终提交前，对其他事务不可见
- 持久性:事务一旦提交，做出的修改就会保存在数据库中
一个实现了ACID的数据库往往比没有实现ACID的数据库需要更好的CPU处理能力和磁盘空间
对于一些只需要查询的业务，可以选择非事务型的存储引擎来获得更高的性能，即使存储引擎不支持事务，也可以通过LOCK TABLES语句来为应用提供一定程度的保护
隔离级别
- 读未提交: 事务中做出的修改，在没有提交的情况下，对其他事务也是可见的。这样会导致其他事务可以读取到当前事务修改但是还未提交的数据，这也被称为脏读。
- 提交读: 事务中做出的修改，直到提交后，才会对其他事务可见。但是会导致其他事务在当前事务提交事务前后，两次查询结果的不同，这也被称为不可重复读。
- 可重复读: 保证了同一个事务中多次读取同样的记录，结果都是一致的。但是如果其他事务在当前事务读取某个范围内的记录时，在该范围内又插入一条新记录，那么当前事务在插入动作发生后，会读取到该新插入的记录，也被称为幻行，即幻读
- 可串行化:强制事务串行执行，避免了幻读的发生，但是该级别会在读取每一行记录时，都上锁，所以会导致大量的超时和锁争用问题。
死锁
当两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源时，会产生死锁现象
举例:
#事务1:
start transaction;
update dhy set name="xpy1" where id=1;
update dhy set name="xpy2" where id=2;
commit;
#事务2:
start transaction;
update dhy set name="xpy2" where id=2;
update dhy set name="xpy1" where id=1;
commit;
同时执行第一条更新语句后，双方在尝试去执行第二行，发现资源被对方锁定，造成死锁
解决死锁方式有很多，例如: 检测到死锁，返回一个错误，或者锁等待超时后放弃锁
上面的解决方法不够优雅，innoDB的做法是: 将持有最少行排它锁的事务进行回滚
死锁产生一般有两种原因: 1.真正的数据冲突 2.存储引擎执行方式不同导致的
大多数情况下，只需要重新执行因为死锁回滚的事务即可
事务日志
事务日志可以提高事务的效率，使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。
事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序IO，而不是像随机IO需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对较快。
事务日志持久后，内存中被修改的数据在后台可以慢慢刷回磁盘，现在大多数存储引擎都是这样实现的，我们称之为"预写式日志"，修改数据需要写两次磁盘。
数据的修改已经写到了事务日志并持久化，但是数据本身还没有写回磁盘，此时系统崩溃，存储引擎可以在重启后自动恢复这部分修改的数据项。
MYSQL中的事务
自动提交
MYSQL默认采用自动提交，即如果不是显式开启一个事务，那么每个查询都会被当做一个事务提交操作，可以通过AUTOCOMMNIT变量来启用或者禁止自动提交模式。
set AUTOCOMMNIT=1或者ON :启用事务,默认值
set AUTOCOMMNIT=0或者OFF :关闭事务
当AUTOCOMMIT值为0的时候，所有的查询都是在一个事务中，直到显式地执行COMMIT或者ROLLBACK回顾，该事务结束，同时开启一个新事务
修改AUTOCOMMIT的值对于非事务类型的表，例如:MyISAM或者内存表来说，没有影响。
有些命令会在执行前，强制当前事务提交，例如会导致大量数据改变的操作，比如ALTER TABLE,LOCK TABLE等
我们可以通过
set session transaction isolation level read committed
来设置隔离级别，新的隔离级别会在下一个事务开始的时候生效，可以在配置文件中设置整个数据库的隔离级别或者当前会话的隔离级别
在事务中混合使用存储引擎
MySQL服务器层不管理事务，事务是通过下层的存储引擎实现的，所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在事务中混合使用了事务型和非事务型的表，会导致事务在回滚的时候，非事务型上的变更无法撤销，进而导致数据库处于不一致的状态，因此需要为每张表选择合适的存储引擎。
隐式和显示锁定
InnoDB采用的是两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK时，才会被释放，并且所有的锁是在同一时刻被释放的。
前面描述的锁定都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。
MYSQL支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层面实现的，和存储引擎无关，他们有自己的用途，但是不能代替事务处理，如果需要使用到事务，还是应该选择事务型存储引擎。
LOCK TABLES的显示使用，不但没必要，还会拖垮性能，InnoDB的行级锁工作性能更好。
建议除了事务中禁用了AUTOCOMMIT外，可以使用LOCK TABLES，其他任何时候，都不要显示使用LOCK TABLES,不管用的是什么存储引擎。
多版本并发控制
多版本并发控制(MVCC)是行级锁的一个变种，他可以在很多情况下避免加锁操作，开销更低。
虽然实现机制有所不同，但是大多都实现了非阻塞读操作，写操作也只锁定必要的行。
MVCC通过保存数据在某个时间点的快照实现，不管需要执行多长时间，每个事物看到的数据都是一致的，根据事物开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的
不同存储引擎对MVCC实现是不同的，主要有以下两种：
- 乐观并发控制
- 悲观并发控制
InnoDB的MVCC是通过在每行记录后面保存两个隐藏列实现的，这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号。
每开始一个新的事务，系统版本号都会自增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
下面看一下在可重复读的隔离级别下，MVCC具体是如何操作的:
- SELECT
1.InnoDB只会查询版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过的
2.行的删除版本要么未定义，要么大于当前事务的版本号，这可以确保事务读取到的行，在事务开始前未被删除。
只有符合上面两个条件的记录，才能返回作为查询结果
- INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号
- DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识
- UPDATE
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识
MVCC只在可重复读和读提交下面生效，其他两个隔离级别都和MVCC不兼容，因为读未提交总是读取到最新的数据行，而不是符合当前事务版本的数据行，而串行化则会对所有读取的行都加锁
存储引擎
MySQL将每个数据库保存为数据目录下的一个子目录，创建表时，MYSQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。
不同的存储引擎保存数据和索引的方式不同，但表的定义是在MYSQL服务层统一处理的，
可以通过
show table status like 表名
命令显示表的相关信息。
下面重点对InnoDB引擎和MyISAM引擎做一下介绍，当然也只是介绍，不会深入讲解，后面系列会深入进行剖析
InnoDB存储引擎
InnoDB是MYSQL默认存储引擎，他被设计用来处理大量短期的事务，短期事务大部分都是正常提交且很少回滚的。
InnoDB的性能和自动奔溃恢复特性，使得它在非事务存储的需求也很流行。
- InnoDB的数据存储在表空间，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。
- InnoDB可将每个表的数据和索引存放在单独的文件中。
- InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离接级别，默认为可重复读，并且通过间隙锁来防止幻读发生。
间隙锁使得InnoDB不仅仅可以锁定查询涉及行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
- InnoDB基于聚簇索引建立，聚簇索引对主键查询有很高的性能，不过他的二级索引(非主键索引)中必须包含主键列，所以如果主键列很大的话，其他所有的索引都会很大，因此，主键应当尽可能的小为好。
InnoDB其他特性这里暂时不讲，后面深入分析时再聊
MyISAM存储引擎
在MYSQL 5.1版本之前，MYISAM是默认的存储引擎，MYISAM提供了大量的特性，包括全文索引，压缩，空间函数等，但其不支持事务和行级锁，而且故障后不能安全恢复,所以建议还是使用INNODB，除非表只读很少写，并且可以忍受修复操作，可以考虑使用
MYISAM最大的问题在于锁的粒度，MYISAM对整张表加锁，而不是针对行
这里对MYISAM的新特性就不展开讲解了
转换表引擎
alter table mytable engine = InnoDB
上面的语法适合于所有存储引擎，但是这个命令会非常耗时，因此MYSQL需要将数据从原表复制到一张新表中，在复制期间可能会消耗系统所有的I/O能力，同时原表上会加上读锁，因此我们可以采用手动进行表的复制方法，来作为一个替代方案。
转换表的引擎后，将会失去和原本引擎相关的特性，例如: INNODB—>MYISAM—>INNODB会导致INNODB表上面的所有外键丢失
下面介绍两种转换表引擎的方式
导入和导出
通过mysqldump将数据导出到文件，然后修改文件中的create table语句的存储引擎选项，并修改表名。
注意:mysqldump会自动在create table前面加上drop table语句,不注意这点，可能会导致数据丢失
创建和查询
先创建一个新的存储引擎的表，然后利用insert…select语法来导数据
create table innodb_table like myisam_table;
alter table innodb_table engine=innodb;
insert into innodb_table select * from myisam_table;
如果数据量很大，可以考虑分批处理，针对每一段数据进行事务提交，避免大事务产生过多的undo
start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit;
操作完成后，得到的是原表的全量复制而来的新表，如果有必要，可以考虑对原表加锁，确保原表和新表的数据一致。
《高性能mysql》读书笔记(代码片段)
文章目录前言MySQL架构与历史MySQL逻辑架构连接管理优化与执行并发控制锁粒度表锁行级锁事务隔离级别事务日志性能优化简介通过性能剖析进行优化理解性能剖析剖析MySQL查询慢查询日志pt-query-digest优秀资料慢SQL定位分析选择优... 查看详情
mysql性能管理及架构设计：什么影响了数据库查询速度什么影响了mysql性能(代码片段)
一、什么影响了数据库查询速度1.1影响数据库查询速度的四个因素1.2风险分析QPS：QueriesPerSecond意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标... 查看详情
kafka系列一之架构介绍和安装(代码片段)
Kafka架构介绍和安装写在前面还是那句话，当你学习一个新的东西之前，你总得知道这个东西是什么？这个东西可以用来做什么？然后你才会去学习它，使用它。简单来说，kafka既是一个消息队列，如今，它也演变为一个分布式... 查看详情
建议收藏15755字，讲透mysql性能优化（包含mysql架构存储引擎调优工具sql索引建议等等）(代码片段)
0.目录1MySQL总体架构介绍1.1MySQL总体架构介绍引言MySQL是一个关系型数据库应用十分广泛在学习任何一门知识之前对其架构有一个概括性的了解是非常重要的比如索引、sql是在哪个地方执行的流程是什么样的今天我们就先来学习一... 查看详情
mysql数据库主从同步的多种架构数据一致性问题解决方案(代码片段)
...这个过程对业务层是透明的，无需修改代码或配置。高性能分析：读写都操作主库，很容易产生瓶颈。大部分 查看详情
使用trace分析mysql性能问题(代码片段)
...一、perf二、strace三、pstack一、perf perf是一款Linux性能分析工具。Linux性能计数器是一个新的基于内核的子系统，它提供一个性能分析框架，比如硬件（CPU、PMU(PerformanceMonitoringUnit)）功能和软件(软件计数器、trace... 查看详情
性能分析之sql性能分析（mysql）(代码片段)
在性能分析中，SQL的分析那是必须的。不同的库又有着不同的命令，但是总体来说，同一类型的数据库，除了有一些手段上的不同之外，给出的结果基本上都是类似的。今天说一下MySQL的。每次我都会说，... 查看详情
mysql复制与高可用水平扩展架构实战(代码片段)
...什么是MySQL复制1、什么是复制MySQL的复制是构建大规模、高性能应用程序的基础，称为“水平扩展”架构。生产环境通常为服务器配置一个或多个备用数据库以同步数据。复制是将一台服务器（即MySQL数据库）的数据... 查看详情
mysql之性能分析（mysqlreport工具）(代码片段)
一、mysqlreport作用进行MySQL的配置优化，首先必须找出MySQL的性能瓶颈所在；而SHOWSTATUS输出的报告正是用来计算性能瓶颈的参考数据。mysqlreport不像SHOWSTATUS那样简单的罗列数据，而是对这些参考数据加以融合计算，整理成一个个... 查看详情
mysql性能分析explain(代码片段)
...化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划ps：MySQL认为最优的数据检索方式，但不见得是DBA认为是最优当客户端向MySQL请求一... 查看详情
mysql性能分析explain(代码片段)
...化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划ps：MySQL认为最优的数据检索方式，但不见得是DBA认为是最优当客户端向MySQL请求一... 查看详情
mysql性能分析explain(代码片段)
...化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划ps：MySQL认为最优的数据检索方式，但不见得是DBA认为是最优当客户端向MySQL请求一... 查看详情
java代码格式规范，架构师必备！
...影响MySQLServer性能的相关因素商业需求对性能的影响系统架构及实现对性能的影响Query语句对系统性能的影响Schema设计对系统的性能影响硬件环境对系统性能的影响MySQL数据库锁定机制MySQL锁定机制简介各种锁定机制分析合理利用... 查看详情
mysql高级学习笔记(代码片段)
文章目录第1章MySQL的架构介绍1.1MySQL简介1.2在Linux上安装MySQL1.3MySQL配置文件1.3MySQL逻辑架构介绍1.4MySQL存储引擎1.4.1查看命令1.4.2MyISAM和InnoDB第2章索引优化分析2.1概述2.1.1性能下降原因2.1.2常用的join查询2.1.2.1SQL执行顺序2.1.2.2join图2.... 查看详情
mysql：使用showprofiles分析sql性能(代码片段)
使用showprofiles分析SQL性能介绍如何查看执行SQL的耗时的步骤：开启profile、发送sql、查看profile的资源开销结果、关闭profile。profile默认是不打开的验证修改后的结果开启profile，然后测试获取profile的帮助获取SQL语句的开销... 查看详情
mysql系列-性能优化神器explain使用介绍及分析(代码片段)
...ySQL提供了一个EXPLAIN命令,它可以对 SELECT 语句进行分析,并输出 SELECT 执行的详细信息,以供开发人员针对性优化。 EXPLAIN命令用法十分简单,在SELECT语句前加上Explain就可以了,例如:EXPLAINSELECT*fromuser_infoWHEREid<300;下... 查看详情
mysql性能分析案例-慢查询(代码片段)
1、开启慢查询日志查看慢查询日志配置信息：showvariableslike‘slow_query%’;参数说明：slow_query_log:是否开启慢查询日志，ON：是；OFF；否slow_query_log_file:慢查询日志路径开启慢查询日志：1setglobalslow_query_log 查看详情
terichdb架构简介(代码片段)
TerichDB是一款高性能和高压缩率的存储引擎，既可以单独作为数据库使用，也可以作为已有数据库的存储引擎使用（如MySQL/MongoDB）TerichDB的定位类似于WiredTiger、RocksDB或LevelDB1.为什么使用TerichDB高性能的同时具有高... 查看详情