MySQL性能优化总结___本⽂乃《MySQL性能调优与架构设计》
读书笔记！
⼀、MySQL的主要适⽤场景
1、Web⽹站系统
2、⽇志记录系统
3、数据仓库系统
4、嵌⼊式系统
⼆、MySQL架构图：
三、MySQL存储引擎概述
1）MyISAM存储引擎
MyISAM存储引擎的表在数据库中，每⼀个表都被存放为三个以表名命名的物理⽂件。⾸先肯定会有任
何存储引擎都不可缺少的存放表结构定义信息的.frm⽂件，另外还有.MYD和.MYI⽂件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个⽂件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同⼀个.MYI⽂件中。
MyISAM⽀持以下三种类型的索引：
1、B-Tree索引
B-Tree索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构来存储，所有的索引数据节点都在叶节点。
2、R-Tree索引
R-Tree索引的存储⽅式和b-tree索引有⼀些区别，主要设计⽤于为存储空间和多维数据的字段做索引，所以⽬前的MySQL版本来说，也仅⽀持geometry类型的字段作索引。
3、Full-text索引
Full-text索引就是我们长说的全⽂索引，他的存储结构也是b-tree。主要是为了解决在我们需要⽤like查询的低效问题。
2）Innodb 存储引擎
1、⽀持事务安装
2、数据多版本读取
3、锁定机制的改进
4、实现外键
3）NDBCluster存储引擎
NDB存储引擎也叫NDBCluster存储引擎，主要⽤于MySQLCluster分布式集环境，Cluster是MySQL从5.0版本才开始提供的新功能。
4）Merge存储引擎
MERGE存储引擎，在MySQL⽤户⼿册中也提到了，也被⼤家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过⼀些特殊的包装对外提供⼀个单⼀的访问⼊⼝，以达到减⼩应⽤的复杂度的⽬的。要创建MERGE表，不仅仅基表的结构要完全⼀致，包括字段的顺序，基表的索引也必须完全⼀致。
5）Memory存储引擎
Memory存储引擎，通过名字就很容易让⼈知道，他是⼀个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了⼀个表结构相关信息的.frm⽂件在磁盘上⾯。所以⼀旦MySQLCrash或者主机Crash之后，Memory的表就只剩下⼀个结构了。Memory表⽀持索引，并且同时⽀持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，⽽且不⽀持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。
6）BDB存储引擎
BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb⼀样，也不是MySQL⾃⼰开发实现的⼀个存储引擎，⽽是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样⽀持事务安全。
7）FEDERATED存储引擎
FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要⽤来提供对远程MySQL服务器上⾯的数据的访问接⼝。如果我们使⽤源码编译来安装MySQL，那么必须⼿⼯指定启⽤FEDERATED存储引擎才⾏，因为MySQL默认是不起⽤该存储引擎的。
8）ARCHIVE存储引擎
ARCHIVE存储引擎主要⽤于通过较⼩的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不⽀持索引，通过⼀个.frm的结构定义⽂件，⼀个.ARZ的数据压缩⽂件还有⼀个.ARM的meta信息⽂件。由于其所存放的数据的特殊性，ARCHIVE表不⽀持删除，修改操
作，仅⽀持插⼊和查询操作。锁定机制为⾏级锁定。
9）BLACKHOLE存储引擎
BLACKHOLE存储引擎是⼀个⾮常有意思的存储引擎，功能恰如其名，就是⼀个“⿊洞”。就像我们unix系统下⾯的“/dev/null”设备⼀样，不管我们写⼊任何信息，都是有去⽆回。
10）CSV存储引擎
CSV存储引擎实际上操作的就是⼀个标准的CSV⽂件，他不⽀持索引。起主要⽤途就是⼤家有些时候可能会需要通过数据库中的数据导出成⼀份报表⽂件，⽽CSV⽂件是很多软件都⽀持的⼀种较为标准的格式，所以我们可以通过先在数据库中建⽴⼀张CVS表，然后将⽣成的报表信息插⼊到该表，即可得到⼀份CSV报表⽂件了。
四、影响MySQLServer性能的相关因素
1商业需求对性能的影响
典型需求：⼀个论坛帖⼦总量的统计，要求：实时更新。
mysql存储文档2系统架构及实现对性能的影响
以下⼏类数据都是不适合在数据库中存放的：
⼆进制多媒体数据
流⽔队列数据
超⼤⽂本数据
通过Cache技术来提⾼系统性能：
系统各种配置及规则数据；
活跃⽤户的基本信息数据；
活跃⽤户的个性化定制信息数据；
准实时的统计信息数据；
其他⼀些访问频繁但变更较少的数据；
3 Query语句对系统性能的影响
需求：取出某个group（假设id为1）下的⽤户编号（id），⽤户昵称（nick_name），并按照加⼊组的时间（_create）来进⾏倒序排列，取出前20个。
解决⽅案⼀：
SELECT id,nick_name FROM user,user_group WHERE up_id=1 and user_group.user_id=user.id ORDER BY _create desc limit 100,20;
解决⽅案⼆：
SELECT user.id,user.nick_name FROM(
SELECT user_id
FROM user_group
WHERE up_id=1
ORDER BY gmt_create desc
limit 100,20)t,user
WHERE t.user_id=user.id;
通过⽐较两个解决⽅案的执⾏计划，我们可以看到第⼀中解决⽅案中需要和user表参与Join的记录数MySQL通过统计数据估算出来是31156，也就是通过user_group表返回的所有满⾜group_id=1的记录数（系统中的实际数据是20000）。⽽第⼆种解决⽅案的执⾏计划
中，user表参与Join的数据就只有20条，两者相差很⼤，我们认为第⼆中解决⽅案应该明显优于第⼀种解决⽅案。
4 Schema设计对系统的性能影响
尽量减少对数据库访问的请求。
尽量减少⽆⽤数据的查询请求。
5硬件环境对系统性能的影响
1、典型OLTP应⽤系统
对于各种数据库系统环境中⼤家最常见的OLTP系统，其特点是并发量⼤，整体数据量⽐较多，但每次访问的数据⽐较少，且访问的数据⽐较离散，活跃数据占总体数据的⽐例不是太⼤。对于这类系统的数据库实际上是最难维护，最难以优化的，对主机整体性能要求也是最⾼的。因为不仅访问量很⾼，数据量也不⼩。
针对上⾯的这些特点和分析，我们可以对OLTP的得出⼀个⼤致的⽅向。
虽然系统总体数据量较⼤，但是系统活跃数据在数据总量中所占的⽐例不⼤，那么我们可以通过扩⼤内存容量来尽可能多的将活跃数据cache到内存中；
虽然IO访问⾮常频繁，但是每次访问的数据量较少且很离散，那么我们对磁盘存储的要求是IOPS表现要很好，吞吐量是次要因素；
并发量很⾼，CPU每秒所要处理的请求⾃然也就很多，所以CPU处理能⼒需要⽐较强劲；
虽然与客户端的每次交互的数据量并不是特别⼤，但是⽹络交互⾮常频繁，所以主机与客户端交互的⽹络设备对流量能⼒也要求不能太弱。
2、典型OLAP应⽤系统
⽤于数据分析的OLAP系统的主要特点就是数据量⾮常⼤，并发访问不多，但每次访问所需要检索的数据量都⽐较多，⽽且数据访问相对较为集中，没有太明显的活跃数据概念。
基于OLAP系统的各种特点和相应的分析，针对OLAP系统硬件优化的⼤致策略如下：
数据量⾮常⼤，所以磁盘存储系统的单位容量需要尽量⼤⼀些；
单次访问数据量较⼤，⽽且访问数据⽐较集中，那么对IO系统的性能要求是需要有尽可能⼤的每秒IO吞吐量，所以应该选⽤每秒吞吐量尽可能⼤的磁盘；
虽然IO性能要求也⽐较⾼，但是并发请求较少，所以CPU处理能⼒较难成为性能瓶颈，所以CPU处理能⼒没有太苛刻的要求；
虽然每次请求的访问量很⼤，但是执⾏过程中的数据⼤都不会返回给客户端，最终返回给客户端的数据量都较⼩，所以和客户端交互的⽹络设备要求并不是太⾼；
此外，由于OLAP系统由于其每次运算过程较长，可以很好的并⾏化，所以⼀般的OLAP系统都是由多台主机构成的⼀个集，⽽集中主机与主机之间的数据交互量⼀般来说都是⾮常⼤的，所以在集中主机之间的⽹络设备要求很⾼。
3、除了以上两个典型应⽤之外，还有⼀类⽐较特殊的应⽤系统，他们的数据量不是特别⼤，但是访问请求及其频繁，⽽且⼤部分是读请求。可能每秒需要提供上万甚⾄⼏万次请求，每次请求都⾮常简单，可能⼤部分都只有⼀条或者⼏条⽐较⼩的记录返回，就⽐如基于数据库的DNS服务就是这样类型的服务。
虽然数据量⼩，但是访问极其频繁，所以可以通过较⼤的内存来cache住⼤部分的数据，这能够保证⾮常⾼的命中率，磁盘IO量⽐较⼩，所以磁盘也不需要特别⾼性能的；
并发请求⾮常频繁，⽐需要较强的CPU处理能⼒才能处理；
虽然应⽤与数据库交互量⾮常⼤，但是每次交互数据较少，总体流量虽然也会较⼤，但是⼀般来说普通的千兆⽹卡已经⾜够了。
五、MySQL 锁定机制简介
⾏级锁定（row-level）
表级锁定（table-level）
页级锁定（page-level）
在MySQL数据库中，使⽤表级锁定的主要是MyISAM，Memory，CSV等⼀些⾮事务性存储引擎，⽽使⽤⾏级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定⽅式。
六、MySQL Query的优化
Query语句的优化思路和原则主要提现在以下⼏个⽅⾯：
1. 优化更需要优化的Query；
2. 定位优化对象的性能瓶颈；
3. 明确的优化⽬标；
4. 从Explain⼊⼿；
5. 多使⽤profile
6. 永远⽤⼩结果集驱动⼤的结果集；
7. 尽可能在索引中完成排序；
8. 只取出⾃⼰需要的Columns；
9. 仅仅使⽤最有效的过滤条件；
10.尽可能避免复杂的Join和⼦查询；
合理设计并利⽤索引
1）B-Tree索引
⼀般来说，MySQL中的B-Tree索引的物理⽂件⼤多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，⽽且到任何⼀个LeafNode的最短路径的长度都是完全相同的，所以我们⼤家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放⾃⼰的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使⽤的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很⼩的改造，在每⼀个LeafNode上⾯出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后⼀个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。
2）Hash索引
Hash索引在MySQL中使⽤的并不是很多，⽬前主要是Memory存储引擎使⽤，⽽且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过⼀定的Hash算法，将需要索引的键值进⾏Hash运算，然后将得到的Hash值存⼊⼀个Hash表中。然后每次需要检索的时候，都会将检索条件进⾏相同算法的Hash运算，然后再和Hash表中的Hash值进⾏⽐较并得出相应的信息。
Hash索引仅仅只能满⾜“=”,“IN”和“<=>”查询，不能使⽤范围查询；
Hash索引⽆法被利⽤来避免数据的排序操作；
Hash索引不能利⽤部分索引键查询；
Hash索引在任何时候都不能避免表扫⾯；
Hash索引遇到⼤量Hash值相等的情况后性能并不⼀定就会⽐B-Tree索引⾼；
3）Full-text索引
Full-text索引也就是我们常说的全⽂索引，⽬前在MySQL中仅有MyISAM存储引擎⽀持，⽽且也并不是所有的数据类型都⽀持全⽂索引。⽬前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。
索引能够极⼤的提⾼数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的⼀个问题就是索引是完全独⽴于基础数据之外的⼀部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。
是否需要创建索引，⼏点原则：较频繁的作为查询条件的字段应该创建索引；唯⼀性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新⾮常频繁的字段不适合创建索引；
不会出现在WHERE⼦句中的字段不该创建索引；
Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数；“永远⽤⼩结果集驱动⼤的结果集”。
优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；
当⽆法保证被驱动表的Join条件字段被索引且内存资源充⾜的前提下，不要太吝惜JoinBuffer的设置；
ORDER BY，GROUP BY和DISTINCT优化
1）ORDER BY的实现与优化
优化Query语句中的ORDER BY的时候，尽可能利⽤已有的索引来避免实际的排序计算，可以很⼤幅度的提升ORDER BY操作的性能。
优化排序：
1.加⼤max_length_for_sort_data参数的设置；
2.去掉不必要的返回字段；
3.增⼤sort_buffer_size参数设置；
2）GROUP BY的实现与优化
由于GROUP BY实际上也同样需要进⾏排序操作，⽽且与ORDER BY相⽐，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使⽤了其他的⼀些聚合函数，那么还需要⼀些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY⼀样也可以利⽤到索引。
3）DISTINCT的实现与优化
DISTINCT实际上和GROUP BY的操作⾮常相似，只不过是在GROUP BY之后的每组中只取出⼀条记录⽽已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太⼤的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在⽆法仅仅使⽤索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有⼀点差别的是，DISTINCT并不需要进⾏排序。也就是说，在仅仅只是DISTINCT操作的Query如果⽆法仅仅利⽤索引完成操作的时候，MySQL会利⽤临时表来做⼀次数据的“缓存”，但是不会对临时表中的数据进⾏filesort操作。
七、MySQL数据库Schema设计的性能优化
⾼效的模型设计
适度冗余-让Query尽两减少Join
⼤字段垂直分拆-summary表优化
⼤表⽔平分拆-基于类型的分拆优化
统计表-准实时优化
合适的数据类型
时间存储格式总类并不是太多，我们常⽤的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，⽽其他两种数据类型都是⼋个字节，多了⼀倍。⽽TIMESTAMP的缺点在于他只能存储从1970年之后的时间，⽽另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使⽤1970年之前的时间，最好尽量使⽤TIMESTAMP来减少存储空间的占⽤。
字符存储类型
CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最⼤存储长度为255字节，但是如果使⽤gbk则最⼤存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使⽤CHAR类型来存放。
VARCHAR[(M)]属于动态存储长度类型，仅存占⽤实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于⼀种存储⽅式，都是动态存储长度类型，不同的仅仅是最⼤长度的限制。
事务优化
1. 脏读：脏读就是指当⼀个事务正在访问数据，并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时，另外⼀个事务也访问这个数据，然后使⽤了这个数据。
2. 不可重复读：是指在⼀个事务内，多次读同⼀数据。在这个事务还没有结束时，另外⼀个事务也访问该同⼀数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改，那么第⼀个事务两次读到的的数据可能是不⼀样的。这样就发⽣了在⼀个事务内两次读到的数据是不⼀样的，因此称为是不可重复读。
3. 幻读：是指当事务不是独⽴执⾏时发⽣的⼀种现象，例如第⼀个事务对⼀个表中的数据进⾏了修改，这种修改涉及到表中的全部数据⾏。同时，第⼆个事务也修改这个表中的数据，这种修改是向表中插⼊⼀⾏新数据。那么，以后就会发⽣操作第⼀个事务的⽤户发现表中还有没有修改的数据⾏，就好象发⽣了幻觉⼀样。
Innodb在事务隔离级别⽅⾯⽀持的信息如下：
1.READ UNCOMMITTED
常被成为Dirty Reads（脏读），可以说是事务上的最低隔离级别：在普通的⾮锁定模式下SELECT的执⾏使我们看到的数据可能并不是查询发起时间点的数据，因⽽在这个隔离度下是⾮Consistent Reads（⼀致性读）；
2.READ COMMITTED
这⼀隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads（幻读）。
3. REPEATABLE READ
REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。
4.SERIALIZABLE

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。