MySQL性能调优（二）：MySQL体系架构（毗连层、Server层、存储引擎层） ...

个人主页：道友老李
欢迎加入社区：道友老李的学习社区
MySQL性能调优

MySQL 性能调优是一个复杂且多维度的过程，下面从数据库设计、查询优化、配置参数调整、硬件优化几个方面为你介绍相关的调优方法。
数据库设计优化

• 合理设计表结构：确保表结构遵循数据库设计范式，淘汰数据冗余，同时要根据现实业务需求机动调整，避免过度范式化导致的查询复杂度过高。
  
• 选择合适的数据类型：使用合适的数据类型可以淘汰存储空间，提高查询性能。例如，对于固定长度的字符串使用CHAR，对于可变长度的字符串使用VARCHAR；对于整数类型，根据取值范围选择合适的类型，如TINYINT、SMALLINT等。
  
• 建立适当的索引：索引可以加快数据的查找速率，但过多的索引会增长写操作的开销，因此必要根据查询需求建立适当的索引。例如，对于经常用于WHERE子句、JOIN条件和ORDER BY子句的列，可以思量创建索引。
  

查询优化

• 避免全表扫描：只管使用索引来避免全表扫描，例如在WHERE子句中使用索引列进行过滤。
  
• 优化子查询：子查询可能会导致性能题目，可以思量使用JOIN来替代子查询。
  
• 淘汰不必要的列：在查询时只选择必要的列，避免使用SELECT *。
  

配置参数调整

• 调整内存分配：根据服务器的硬件资源和业务需求，调整innodb_buffer_pool_size、key_buffer_size等参数，以提高缓存命中率。
  
• 调整日记参数：根据业务需求调整log_bin、innodb_log_file_size等参数，以平衡数据安全性和性能。
  

硬件优化

• 使用高速存储装备：如 SSD 可以显著提高磁盘 I/O 性能。
  
• 增长内存：足够的内存可以淘汰磁盘 I/O，提高查询性能。
  

1.MySQL体系架构

1.1.MySQL的分支与变种

MySQL变种有好几个，紧张有三个久经考验的主流变种：Percona Server，MariaDB和 Drizzle。它们都有活跃的用户社区和一些商业支持，均由独立的服务供应商支持。同时另有几个优秀的开源关系数据库，值得我们相识一下。
1.1.1.Drizzle

Drizzle是真正的MySQL分支，而且是完全开源的产品，而非只是个变种或加强版本。它并不与MySQL兼容不能简单地将MySQL后端更换为Drizzle。
Drizzle与MySQL有很大差别，进行了一些重大更改，甚至SQL语法的变化都非常大，设计目标之一是提供一种出色的办理方案来办理高可用性题目。在实现上，Drizzle清除了一些表现不佳和不必要的功能，将很多代码重写，对它们进行了优化，甚至将所用语言从C换成了C++。
此外，Drizzle另一个设计目标是能很好的顺应具有大量内容的多核服务器、运行Linux的64位机器、云盘算中使用的服务器、托管网站的服务器和每分钟接收数以万计点击率的服务器而且大幅度的减少服务器成本。
1.1.2.MariaDB

在Sun收购MySQL后，Monty Widenius，这位MySQL的创建者，因不认同MySQL开辟流程而离开Sun。他建立了Monty步调公司，创立了MariaDB。MariaDB的目标是社区开辟，Bug修复和很多的新特性现实上，可以将MariaDB视为MySQL的扩展集，它不仅提供MySQL提供的全部功能，还提供其他功能。MariaDB是原版MySQL的超集，因此已有的体系不必要任何修改就可以运行。
诸如Google，Facebook、维基百科等公司大概网站所使用了MariaDB。不过Monty公司不是以赢利为目的，而是由产品驱动的，这可能会带来题目，因为没有赢利的公司不愿定能长久维持下去。
1.1.3.Percona Server

由领先的MySQL咨询公司Percona发布，Percona公司的标语就是“The Database Performance Experts”，Percona的首创人也就是《高性能MySQL》书的作者。
Percona Server是个与MySQL向后兼容的替代品，它尽可能不改变SQL语法、客户端/服务器协议和磁盘上的文件格式。任何运行在MySQL上的都可以运行在Percona Server上而不必要修改。切换到Percona Server只必要关闭MySQL和启动PerconaServer，不必要导出和重新导入数据。
Percona Server有三个紧张的目标：透明，增长允许用户更细密地查看服务器内部信息和行为的方法。比如慢查询日记中特殊增长的详细信息；性能，Percona Server包含很多性能和可扩展性方面的改进，还加强了性能的可预测性和稳定性。其中紧张会合于InnoDB；操作机动性，Percona Server使操作人员和体系管理员在让MySQL作为架构的一部门而可靠并稳定运行时提供了很多便利。
一样平常来说，Percona Server中的很多特性会在后来的尺度MySQL中出现。
国内公司阿里内部就运行了上千个Percona Server的实例。
1.2.MySQL的替代

1.2.1.Postgre SQL

PostgreSQL称本身是世界上最先进的开源数据库，同时也是个一专多长的全栈数据库。最初是1985年在加利福尼亚大学伯克利分校开辟的。
PostgreSQL 的稳定性极强，在崩溃、断电之类的灾难场景下依然可以包管数据的正确；在高并发读写，负载迫近极限下，PostgreSQL的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再降落，表现的非常稳定，而 MySQL 显着出现一个波峰后下滑；
PostgreSQL多年来在GIS(地理信息)范畴处于优势地位，因为它有丰富的多少类型，现实上不止多少类型，PostgreSQL有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多。所以总的来说，PostgreSQL更学术化一些，在绝对必要可靠性和数据完备性的时候，PostgreSQL是更好的选择。但是从商业支持、文档资料、易用性，第三方支持来说，MySQL无疑更好些。
1.2.2.SQLite

SQLite是世界上摆设最广泛的数据库引擎，为物联网（IoT）下的数据库首选，而且是手机，PDA，甚至MP3播放器的下的首选。SQLite代码占用空间小，而且不必要数据库管理员的维护。SQLite没有单独的服务器进程，提供的事务也根本符合ACID。固然，简单也就意味着功能和性能受限。
2.MySql基础

2.1.MySQL体系架构

可以看出MySQL是由毗连池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件体系组成。
毗连池
由于每次建立建立必要消耗很多时间，毗连池的作用就是将这些毗连缓存下来，下次可以直接用已经建立好的毗连，提升服务器性能。
管理工具和服务
体系管理和控制工具，例如备份规复、Mysql复制、集群等
SQL接口
担当用户的SQL命令，而且返回用户必要查询的结果。比如select … from就是调用SQL接口
解析器
SQL命令通报到解析器的时候会被解析器验证和解析。解析器紧张功能：1、将SQL语句分解成数据结构，后续步骤的通报和处置惩罚就是基于这个结构的。2、将SQL语句分解成数据结构，后续步骤的通报和处置惩罚就是基于这个结构的。
优化器
查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
缓存器
查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。
存储引擎(后面会细讲)
文件体系(后面会细讲)
2.1.1.毗连层

当MySQL启动（MySQL服务器就是一个进程），等待客户端毗连，每一个客户端毗连请求，服务器进程会创建一个线程专门处置惩罚与这个客户端的交互。当客户端与该服务器断开之后，不会立即取消线程，只会把他缓存起来等待下一个客户端请求毗连的时候，将其分配给该客户端。每个线程独立，拥有各自的内存处置惩罚空间。

以下命令可以查看最大的毗连数：

1. show VARIABLES like '%max_connections%'

复制代码

毗连到服务器，服务器必要对其进行验证，也就是用户名、IP、密码验证，一旦毗连成功，还要验证是否具有执行某个特定查询的权限（例如，是否允许客户端对某个数据库某个表的某个操作）

2.1.2.Server层(SQL处置惩罚层)

这一层紧张功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等。
固然作为一个SQL的执行流程如下：

1.如果是查询语句（select语句），起首会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）
2.解析查询，创建一个内部数据结构（解析树），这个解析树紧张用来SQL语句的语义与语法解析；
3.优化：优化SQL语句，例如重写查询，决定表的读取次序，以及选择必要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会扣问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。
2.1.2.1.缓存（相识即可）

1. show variables like '%query_cache_type%'   -- 默认不开启
3. show variables like '%query_cache_size%'  --默认值1M
5. SET GLOBAL query_cache_type = 1; --会报错

复制代码

query_cache_type只能配置在my.cnf文件中！
缓存在生产环境建议不开启，除非经常有sql完全千篇一律的查询
缓存严格要求2次SQL请求要完全一样，包罗SQL语句，毗连的数据库、协议版本、字符集等因素都会影响
从8.0开始，MySQL不再使用查询缓存，那么放弃它的原因是什么呢？
MySQL查询缓存是查询结果缓存。它将以SEL开头的查询与哈希表进行比较，如果匹配，则返回上一次查询的结果。进行匹配时，查询必须逐字节匹配，例如 SELECT * FROM e1; 不等于select * from e1;
此外，一些不确定的查询结果无法被缓存，任何对表的修改都会导致这些表的全部缓存无效。因此，实用于查询缓存的最抱负的方案是只读，特殊是必要检查数百万行后仅返回数行的复杂查询。如果你的查询符合这样一个特点，开启查询缓存会提升你的查询性能。
随着技能的进步，经过时间的考验，MySQL的工程团队发现启用缓存的好处并不多。
起首，查询缓存的结果取决于缓存的命中率，只有命中缓存的查询结果才气有改善，因此无法预测其性能。
其次，查询缓存的另一个大题目是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。
通过基准测试发现，大多数工作负载最好禁用查询缓存(5.6的默认设置)：按照官方所说的：造成的题目比它办理题目要多的多，弊大于利就直接砍掉了。
2.1.3.存储引擎层

从体系结构图中可以发现，MySQL数据库区别于其他数据库的最紧张的一个特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列尺度的管理和服务支持，这些尺度与存储引擎本身无关，可能是每个数据库体系本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构和现实文件读写的实现，每个存储引擎开辟者可以按照本身的意愿来进行开辟。必要特殊注意的是，存储引擎是基于表的，而不是数据库。
插件式存储引擎的好处是，每个存储引擎都有各自的特点，能够根据详细的应用建立差别存储引擎表。由于MySQL数据库的开源特性，用户可以根据MySQL预定义的存储引擎接口编写本身的存储引擎。若用户对某一种存储引擎的性能或功能不满意，可以通过修改源码来得到想要的特性，这就是开源带给我们的方便与力量。
由于MySQL数据库开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。有些第三方存储引擎很强大，如大名鼎鼎的InnoDB存储引擎（最早是第三方存储引擎，后被Oracle收购)，其应用就极其广泛，甚至是MySQL数据库OLTP(Online Transaction Processing在线事务处置惩罚）应用中使用最广泛的存储引擎。
2.1.3.1.MySQL官方引擎概要

InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎，也是最紧张、使用最广泛的存储引擎。它被设计用来处置惩罚大量的短期(short-lived)事务，短期事务大部门环境是正常提交的，很少会被回滚。InnoDB的性能和主动崩溃规复特性，使得它在非事务型存储的需求中也很流行。除非有非常特殊的原因必要使用其他的存储引擎，否则应该优先思量InnoDB引擎。如果要学习存储引擎，InnoDB也是一个非常好的值得花最多的时间去深入学习的对象，收益肯定比将时间平均花在每个存储引擎的学习上要高得多。所以InnoDB引擎也将是我们学习的重点。
MylSAM存储引擎
在MySQL 5.1及之前的版本，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包罗全文索引、压缩、空间函数（GIS）等，但MyISAM不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全规复。尽管MyISAM引擎不支持事务、不支持崩溃后的安全规复，但它绝不是一无是处的。对于只读的数据，大概表比较小、可以忍受修复（repair）操作，则依然可以继承使用MyISAM（但请不要默认使用MyISAM，而是应当默认使用InnoDB)。但是MyISAM对整张表加锁，而不是针对行。读取时会对必要读到的全部表加共享锁,写入时则对表加排他锁。MyISAM很轻易因为表锁的题目导致典型的的性能题目。
Mrg_MylSAM
Merge存储引擎，是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。
Archive引擎
Archive存储引擎只支持INSERT和SELECT操作，在MySQL 5.1之前也不支持索引。Archive引擎会缓存全部的写并利用zlib对插入的行进行压缩，所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都必要执行全表扫描。所以Archive表适合日记和数据收罗类应用，这类应用做数据分析时往往必要全表扫描。大概在一些必要更快速的INSERT操作的场所下也可以使用。Archive引擎不是一个事务型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。
Blackhole引擎
Blackhole引擎没有实现任何的存储机制，它会扬弃全部插入的数据，不做任何生存。但是服务器会记录Blackhole表的日记，所以可以用于复制数据到备库，大概只是简单地记录到日记。这种特殊的存储引擎可以在一些特殊的复制架构和日记考核时发挥作用。但这种引擎在应用方式上有很多题目，因此并不保举。
CSV引擎
CSV引擎可以将普通的CSV文件(逗号分割值的文件）作为MySQL的表来处置惩罚，但这种表不支持索引。CSV引擎可以在数据库运行时拷入大概拷出文件。可以将Excel等的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL 中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部步调也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据互换的机制，非常有用。
Federated引擎
Federated引擎是访问其他MySQL服务器的一个署理，它会创建一个到远程MySQL服务器的客户端毗连，并将查询传输到远程服务器执行，然后提取大概发送必要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和 Oracle的雷同特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的机动性，但也经常带来题目，因此默认是禁用的。
Memory 引擎
如果必要快速地访问数据，而且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表(以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM 表要快一个数量级，因为每个基于MEMORY存储引擎的表现实对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处置惩罚，提高整个表的效率，不必要进行磁盘I/O。所以Memory表的结构在重启以后还会保留，但数据会丢失。
Memory表支持 Hash索引，因此查找操作非常快。虽然Memory表的速率非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，而且每行的长度是固定的，所以即使指定了VARCHAR 列，现实存储时也会转换成CHAR，这可能导致部门内存的浪费。
NDB集群引擎
使用MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing 的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（(MySQL Cluster)。
2.1.3.2.值得相识的第三方引擎

Percona的 XtraDB存储引擎
基于InnoDB引擎的一个改进版本，已经包含在Percona Server和 MariaDB中，它的改进点紧张会合在性能、可测量性和操作机动性方面。XtraDB可以作为InnoDB的一个完全的替代产品，甚至可以兼容地读写InnoDB的数据文件，并支持InnoDB的全部查询。
TokuDB引擎
使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的，因此即使其巨细超过内存性能也不会降落，也就没有内存生命周期和碎片的题目。TokuDB是一种大数据（Big Data)存储引擎，因为其拥有很高的压缩比，可以在很大的数据量上创建大量索引。现在该引擎也被Percona公司收购。
Tips ： 分形树，是一种写优化的磁盘索引数据结构。 分形树的写操作（Insert/Update/Delete）性能比较好，同时它还能包管读操作近似于B+树的读性能。据测试结果显示， TokuDB分形树的写性能优于InnoDB的B+树，读性能略低于B+树。分形树核心思想是利用节点的MessageBuffer缓存更新操作，充分利用数据局部性原理，将随机写转换为次序写，这样极大的提高了随机写的效率。
Infobright
MySQL默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处置惩罚的。而在大数据量处置惩罚时，面向列的方式可能效率更高，比如HBASE就是面向列存储的。
Infobright是最著名的面向列的存储引擎。在非常大的数据量（数十TB)时，该引擎工作良好。Infobright是为数据分析和数据仓库应用设计的。数据高度压缩，按照块进行排序，每个块都对应有一组元数据。在处置惩罚查询时，访问元数据可决定跳过该块，甚至可能只必要元数据即可满意查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引也很难发挥作用，而且块结构也是一种准索引 (quasi-index)。Infobright必要对MySQL服务器做定制，因为一些地方必要修改以顺应面向列存储的必要。如果查询无法在存储层使用面向列的模式执行，则必要在服务器层转换成按行处置惩罚，这个过程会很慢。Infobright有社区版和商业版两个版本。
2.1.3.3.选择合适的引擎

这么多存储引擎，我们怎么选择?大部门环境下，InnoDB都是正确的选择，所以在MySQL 5.5版本将InnoDB作为默认的存储引擎了。对于如何选择存储引擎，可以简单地归纳为一句话:“除非必要用到某些InnoDB不具备的特性，而且没有其他办法可以替代，否则都应该优先选择InnoDB引擎”。比如，MySQL中只有MyISAM支持地理空间搜刮。
固然，如果不必要用到InnoDB的特性，同时其他引擎的特性能够更好地满意需求，也可以思量一下其他存储引擎。举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失题目，却对InnoDB的空间占用过多比较敏感，这种场所下选择MyISAM就比较合适。
除非万不得已，否则建议不要混合使用多种存储引擎，否则可能带来一系列复杂的题目，以及一些潜在的bug和边界题目。存储引擎层和服务器层的交互已经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对划一性备份和服务器参数配置都带来了一些困难。
2.1.3.4.表引擎的转换

有很多种方法可以将表的存储引擎转换成别的一种引擎。每种方法都有其优点和缺点。常用的有三种方法
ALTER TABLE
将表从一个引擎修改为另一个引擎最简单的办法是使用ALTER TABLE 语句。下面的语句将mytable的引擎修改为InnoDB :

1. ALTER TABLE mytable ENGINE = InnoDB;

复制代码

上述语法可以实用任何存储引擎。但必要执行很长时间，在实现上，MySQL会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗体系全部的I/O能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作要特殊小心。
导出与导入
还可以使用mysqldump工具将数据导出到文件，然后修改文件中CREATE TABLE语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使它们使用的是差别的存储引擎。
CREATE和 SELECT
先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据:

1. CREATE TABLE innodb_table LIKE myisam_table;
2. ALTER TABLE innodb_table ENGINE=InnoDB;
3. INSERT INTO innodb_table SELECT * FROM myisam_table;

复制代码

如果数据量很大，则可以思量做分批处置惩罚，针对每一段数据执行事务提交操作。
2.1.3.5.检查MySQL的引擎

看我的MySQL现在已提供什么存储引擎:

1. show engines;

复制代码

看我的MySQL当前默认的存储引擎:

1. show variables like '%storage_engine%';

复制代码

2.1.3.6.MyISAM和InnoDB比较

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。