MySQL性能优化的详细说明（1）
一、MySQL的主要应用场景1、web网站系统
2。日志记录系统
三.数据仓库系统
4、嵌入式系统
二、MySQL体系结构图：
三，MySQL存储引擎概述
1）MyISAM存储引擎
MyISAM存储引擎的表在数据库中，每个表存储为三物理文件的表的名字命名的。首先，必须有任何存储引擎，用于存储表结构，不可或缺。定义信息FRM文件，and.myd and.myi文件，分别用于存储表数据（。MYD）和索引数据（。我）。每个表有三个文件被存储为MyISAM存储类型表。也就是说，无论多少指标表，它存储在same.myi文件。
MyISAM支持以下三种类型的指标：
1、B树索引
B树索引，顾名思义，就是所有的索引节点根据该balancetree数据结构存储，所有指标数据节点是叶节点。
2、R树索引
存储索引B树索引的方式是不同的。它主要用于索引存储空间和多维数据的字段，因此目前的MySQL版本只支持几何类型字段进行索引。
3，全文索引
全文索引是我们长期的全文索引，其存储结构也b-tree.the主要目的是解决我们需要使用查询效率低下的问题。
2）InnoDB存储引擎
1。支持交易的安装
2。数据读取的多个版本
3、闭锁机构的改进
4。外键的实现
3）ndbcluster存储引擎
NDB存储引擎，也被称为ndbcluster存储引擎，主要用于mysqlcluster分布式集群环境。集群是MySQL从5版提供的一个新函数。
4）合并存储引擎
合并存储引擎，在MySQL的用户手册中也提到，也被称为mrg_myisam引擎。为什么因为合并存储引擎可以简单的了解其功能是实现对MyISAM表相同的结构，并通过特殊的包装在一定程度上提供了一个单一的访问入口，从而降低应用程序的复杂性。创建一个合并表格，不只是基表的结构是完全一致的，包括的领域秩序，与基表的索引必须完全一致。
5）内存存储引擎
内存存储引擎，通过它的名字，很容易让人知道他是一个存储引擎存储在内存中的数据，存储引擎不在磁盘上存储任何数据，只有商店。一个表结构相关的磁盘上的信息文件，所以一旦mysqlcrash或主机崩溃，在内存表只剩下一个结构。表内存支持指数，并支持哈希和BTree格式的两个指标。因为它是存储在内存中，在一个固定长度的存储空间存储数据，不支持BLOB和文本类型的字段，存储引擎实现了页面级锁定。
6）BDB存储引擎
BDB存储引擎也叫BerkeleyDB存储引擎。它也是一个存储引擎，MySQL开发和实施本身，就像InnoDB。它是由sleepycatsoftware，提供，当然，也是一个开放源代码的存储引擎，同时也支持交易安全。
7）联邦存储引擎
由联邦存储引擎实现的功能基本上是类似于Oracle的dblink，它主要用来提供访问在远程服务器的数据。如果我们使用源代码编译安装mysql，我们必须手动指定联邦存储引擎，因为MySQL默认不使用存储引擎。
8）归档存储引擎
档案存储引擎主要是用来存放过期的历史数据通过存储空间小。档案表不支持索引，通过定义文件结构。FRM，存储在它a.arz数据压缩文件和一个数据的特殊性the.arm.because元信息文件，档案表不支持删除和修改练习。
仅支持插入和查询操作。锁定机制是行级锁定。
9）黑洞的存储引擎
黑洞的存储引擎是一个非常有趣的存储引擎的功能就像它的名字一样是一个黑洞。像我们的UNIX系统/ dev /空设备，无论我们写的是永远不会返回任何信息。
10）CSV存储引擎
The CSV storage engine actually operates a standard CSV file, which does not support the index.The main purpose is that we may sometimes need a report file by exporting the data in the database, and the CSV file is a more standard format supports a lot of software, so we can first establish a CVS table in the database, then the report information is inserted into the table. 你可以得到一个CSV文件。
四。影响mysqlserver性能的相关因素
1企业需求对绩效的影响
典型要求：论坛发帖总量统计，要求：实时更新。
2系统体系结构及其对性能的影响
下列类型的数据不适合在数据库中存储：
二进制多媒体数据
流水线数据
超大型文本数据
通过缓存技术提高系统性能。
系统的各种配置和规则数据；
主动用户的基本信息数据；
主动用户的个性化信息数据；
准实时统计信息数据；
其他频繁访问但变化较小的数据；
3个查询语句对系统性能的影响
要求：拿出一组（假设ID 1）用户ID（ID），绰号（nick_name），并加入一组时间（user_group。gmt_create）拆卸顺序相反，前20。
解决方案之一：
复制代码代码如下：选择ID，nick_name用户，user_group哪里user_group。group_id = 1和user_group。user_id = User.ID秩序，user_group。user_id = User.ID，和正确的方法。
方案二：
选择user.nick_name User.ID，从（
选择user_id
从user_group
在user_group group_id = 1。
通过gmt_create倒序
极限100,20），用户
在t.user_id = User.ID；
通过比较这两种解决计划的执行，我们可以在第一次参加和用户通过统计数据形式的MySQL记录数31156看估计的解决方案，这是通过user_group表满足所有group_id = 1返回的记录数（实际系统中数据是20000）。在第二方案的执行计划，有那个用户表参与加入数据只有20件，和它们之间的区别是非常大的。我们认为第二中间解应该比第一解好。
4种方案设计对系统性能的影响
尽量减少对数据库访问的请求。
最小化无用数据的查询请求。
5硬件环境对系统性能的影响
1。典型的OLTP应用系统
对各种数据库系统环境中，最常见的OLTP系统的特点是大并发量和数据量大。然而，每次访问的数据相对较少，访问的数据是离散的。主动数据对总数据的比例不是太大，这类系统的数据库实际上是最难维护、最难优化和对主机性能要求最高的数据库，因为不仅访问量高，而且数据量也不小。
在上述特征的分析来看，我们可以得到系统的总的方向。
虽然系统的总数据量很大，但总数据中活动数据的百分比并不很大，因此我们可以将内存容量扩展到尽可能快地将活动数据缓存到内存中。
虽然IO访问是频繁的，但所访问的数据量是小而离散的。所以我们对磁盘存储有很好的需求。IOPS性能良好，吞吐量是次要因素。
并发性很高，CPU每秒的请求量很大，所以CPU的处理能力必须很强；
虽然与客户机交互的数据量不是很大，但网络交互非常频繁，因此主机和客户端交互的网络设备不能太弱，不能满足流量的要求。
2。典型OLAP应用系统
用于数据分析的OLAP系统的主要特点是数据量大，并发访问量不大，但每次访问的数据量较大，数据访问相对集中，没有明显的活动数据概念。
基于OLAP系统的各种特点和相应的分析，OLAP系统硬件优化的一般策略如下：
数据量非常大，因此磁盘存储系统的单元容量需要尽可能大。
单个访问数据很大，访问数据是集中的。因此，IO系统的性能要求与每秒IO吞吐量一样大。因此，我们应该选择每秒吞吐量最大的磁盘。
虽然IO的性能要求比较高，但并发请求很少，因此CPU处理能力几乎不是性能瓶颈，因此CPU处理能力不太严格。
虽然每次访问的请求量很大，但是执行过程中的大部分数据不会返回给客户机，返回给客户机的数据量更小。因此，与客户机交互的网络设备的要求不高。
此外，由于OLAP系统由于每个操作的过程可以被并行化，很好，所以一般的OLAP系统是一个由多个主机集群，以及主机和一般的主机之间的数据交换量非常大，所以在群体的需求之间的主机网络设备是非常高的。
3，除了上述两个典型应用外，还有一个比较特殊的应用系统。它们的数据量不是很大，但是访问请求及其频繁的，大多数是读请求，每秒可能需要几十甚至数万个请求。每个请求都很简单。它们中的大多数只能由一个或几个较小的记录返回。例如，基于数据库的DNS服务就是这样一种服务。
虽然数据量很小，但访问非常频繁，所以高速缓存可以通过大内存来存储大部分数据。这样可以确保命中率非常高，并且磁盘IO量相对较小，因此磁盘不需要额外的高性能。
并发请求非常频繁，可以处理比需要更强的CPU处理能力更好的请求。
虽然应用程序和数据库之间的交互量很大，但交互数据和总流量比较少，但一般来说，普通千兆以太网卡就足够了。
五、MySQL锁定机制简介
行级锁定（行级）
表级锁定（表级）
页面级锁定（页面级）
在MySQL数据库中，使用表级锁主要是MyISAM，记忆，CSV和其他一些非事务性存储引擎，和行级锁主要用于InnoDB存储引擎和ndbcluster存储引擎，页级锁主锁模式BerkeleyDB存储引擎。
六和mysql查询的优化
查询语句的优化思想和原则主要体现在以下几个方面：
1。优化需要优化的查询；
2。位置优化对象的性能瓶颈；
三.优化目标明确；
4。从解释开始；
超过5。使用简介
6。总是使用小的结果集来驱动大的结果集；
7。尽可能地在索引中完成排序；
8。取出他们需要的列；
9。只使用最有效的过滤条件；
10。尽可能避免复杂的连接和子查询；
指数的合理设计与应用
1）B-树索引
一般来说，物理文件的B树索引在MySQL存储在balancetree结构，这是所有数据的实际需要的叶结点存储在树和最短路径长度的叶结点任何一个是完全一样的，所以我们都称之为当然的B树索引，所有的（所有可能的数据库存储引擎或MySQL）储存时他们会在B树索引存储结构的转变。如B-树索引InnoDB存储引擎的存储结构，实际使用的是B+树，是树的数据结构的基础上进行改造是非常小的，在存储在索引键的相关信息，每个叶结点，还存储了一个指针信息叶结点点和相邻的LeafNode之后，这主要是为了加快多个相邻的叶结点检索效率。
2）散列索引
MySQL中不使用哈希索引。目前主要使用的存储引擎，和哈希索引作为存储引擎的默认索引类型。事实上所谓的哈希索引，，是通过一定的hash算法，我们需要索引键值Hash运算，然后哈希值存储在哈希表。然后，每我们需要检索，将检索同一算法的哈希算法的条件，然后用哈希表的哈希值进行比较，并得到相应的信息。
散列索引只满足=、内和查询，并且不使用范围查询；
不能使用哈希索引来避免数据的排序。
哈希索引不能使用部分索引键查询；
散列索引在任何时候都不能避免表面扫描。
Hash索引的性能不一定比B树索引时大量的哈希值相等。
3）全文索引
全文索引也是我们通常所说的全文索引。目前，MySQL只有MyISAM存储引擎支持，并不是所有的数据类型的支持全文索引。目前，只有三的数据类型，CHAR，VARCHAR和文本列，可以建立全文索引。
该索引可以大大提高数据检索效率，也可以改进分组的排序操作，但不能忽视的一个问题是索引是数据的一部分，完全独立于计算量的基本数据，更新IO数据会带来资源消耗引起的调整指数。
是否创建索引有几个原则。作为查询条件的频繁字段应该创建索引。唯一性较差的字段不适合单独创建索引，即使它们经常用作查询条件；频繁更新字段不适合创建索引。
不在WHERE子句中出现的字段不应该创建索引；
连接语句的优化
尽量减少在声明加入共nestedloop次数；总是开大的结果集的一个小的结果集，永远。
nestedloop内部循环的优化。
确保联接语句中驱动表上的联接条件字段已被编入索引；
当驱动表的连接条件字段索引和存储资源是充足的，不要对JoinBuffer的设定太小气。
顺序、分组和明显的优化
1）订单的实现和优化
在查询语句中对订单进行优化时，使用现有索引避免实际排序计算是非常重要的，可以大大提高操作的顺序性能。
优化排序：
1。增加max_length_for_sort_data参数设置。
2。删除不必要的返回字段；
三.增加sort_buffer_size参数设置；
2）集团的实施与优化
因为组其实也需要排序操作，并比较排序，组主要只是一种分组操作。当然，如果其他聚合函数用于在分组的时候，一些计算聚合函数是必要的。因此，在集团实施，该指数可以可以作为顺序。
3）差异化的实现与优化
不同于组的操作实际上非常相似，但分组后只取出一个记录，所以实现的不同和组的实现几乎相同，没有很大差别，也可以通过松散索引扫描或紧凑索引扫描来实现。当然，MySQL只能完成通过临时表时明显是不能够使用指数。然而，不同的是，不同的组不需要排序，也就是说，当查询是不同的操作，如果MySQL不能通过指标单独操作，MySQL将使用临时表对数据进行缓存，但它不会对临时表的数据操作。
以上是本文的全部内容，希望能对大家有所帮助。