《MySQL高性能》------总结

一：MySQL架构

MySQL服务器逻辑架构图：

第一层：最上层的服务器不是MySql所独有的，大多数基于网络的客户端/服务器工具或者服务都有类似的系统。比如链接处理，授权认证，安全等等。
第二层：大多数的MySql的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期，时间，数学和加密函数等）。所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图。
第三层：包含了存储引擎。存储引擎负责MySql中的数据存储和提取。服务器通过API和存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包含了几十个底层函数，用于执行诸如”开始一个事务“或者”根据主键提取一行记录“等操作。但存储引擎不会去解析SQL(InnoDB是一个例外，它会解析外键定义，因为MySQL服务器本身没有实现该功能)

1、并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。解决这类经典的问题方法就是并发控制，通过实现一个由两种类型的锁组成的锁系统来解决问题。

2、锁

共享锁：（读锁）
排他锁：（写锁）

行级锁：
表锁：

死锁：
死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

根据锁持有的时间粒度，分为
　　1. 内存级别：类似mutex，很快释放
　　2. 语句级别：statement结束，释放
　　3. 事务级别：transaction提交或者回滚才释放
　　4. 会话级别：session级别，连接断开才释放

3、事务

ACID:
原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元。
一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中。

4、隔离级别

READ UNCOMMITTED (未提交读)
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

READ COMMITTED(提交读)
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

REPEATABLE READ(可重复读)
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

SERIALIZABLE(可串行化)
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

5、多版本并发控制

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
不同存储引擎的MVCC的实现是不同的，典型的有乐观并发控制和悲观并发控制
InnoDB的MVCC，是通过在每行后面保存两个隐藏的列来实现的。这两个列，一个保存了行的的创建时间，一个保存了行的过期时间（或删除时间）。当然，存储的并不是实际的时间，而是系统版本号。每开始一个新的事务，系统版本号会自动递增。事务开始时刻的系统版本号作为该事务的版本号，用来和查询到的行的版本号进行比较。
MCVV的具体实现过程。

1，select

InnoDB会根据两个条件来检查每行记录。

a，InnoDB只查找版本早于当前事务版本号的行（也就是说，行的系统版本号，小于或等于当前事务的系统版本号），这样可以保证事务读取的行，要么是在事务开始之前已经存在的，要么是事务自身或者修改过的。

b，行的删除版本号要么未定义，要么大于当前事务版本号。这样可以保证，事务读取到的行，在事务开始之前未删除。

只有符合以上两个条件的数据，才会返回。

2，insert

InnoDB为插入的每一行数据保存当前的系统版本号作为行版本号。

3，delete

InnoDB为删除的每一行保存当前的系统版本号作为行的删除标志。

4，update

InnoDB插入一行新的数据，并保存当前的系统版本号作为新插入行的版本号，并且保存当前系统版本号到原来的行作为它的删除标志。

保存两个额外的系统版本号，使得大多数读操作都不用加锁。

二：Schema与数据类型优化

mysql支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储那种数据类型，下面几个简单的原则都有助于做出更好的选择。

• 更小的通常更好。
• 简单就好
• 尽量避免NULL

1、优化类型

整数类型

有两种类型的数字：整形（whole number）和实数（real number）。如果存储整数，可以使用这几种整数类型：TINYINT，SMALLINT，MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间。它们可以存储的范围从-2（N-1）到2（N-1） -1。一般情况下越小的列越好。

实数类型

实数是带有小数部分的数字。然而，它们不只是为了存储小数部分；也可以使用DECIMAL存储比BIGINT还打的整数。

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长字符串更节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

2、范式与反范式

范式的优点

• 范式化的数据库更新起来更加快；
• 范式化之后，只有很少的重复数据，只需要修改更少的数据
• 范式化的表更小，可以在内存中执行
• 很少的冗余数据，在查询的时候需要更少的distinct或者group by语句

范式的缺点

范式化的表，在查询的时候经常需要很多的关联，因为单独一个表内不存在冗余和重复数据。这导致，稍微复杂一些的查询语句在查询范式的schema上都可能需要较多次的关联。这会增加让查询的代价，也可能使一些索引策略无效。因为范式化将列存放在不同的表中，而这些列在一个表中本可以属于同一个索引

反范式的优点

• 可以避免关联，因为所有的数据几乎都可以在一张表上显示
• 可以设计有效的索引

反范式的缺点

表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

三：创建高性能的索引

1、索引的类型

1、B-Tree索引
2、哈希索引
3、空间数据索引（R-Tree）
4、全文索引
5、其他索引类别

2、索引的优点

1. 索引大大减少了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机I/O变为顺序I/O

索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排序顺序一直则获得二星；如果索引中的列包含了查询中需要的全部列则获得“三星”。

3、索引策略

• 独立的列：指的是索引列不能是表达式的一部分，也不能是函数的参数。

• 索引选择性：索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

• 前缀索引：对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

• 多列索引：在多个列上建立单独的列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

• 选择合适的索引列顺序： （B-Tree索引）在多列的B-Tree索引中，首先按照最左列进行排序，所以多列索引的列顺序至关重要，将选择性最高的列放到索引列最前列。

• 聚簇索引：InnoDB使用的聚簇索引，索引和数据存储到一起，MyISAM使用的是非聚簇索引，索引和数据分开存储。
  聚簇索引的优点：1、可以把相关的数据保存在一起。2、数据访问更快。3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

• 覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

• 使用索引扫描来做排序

• 压缩（前缀压缩）索引

• 冗余和重复索引

• 未使用的索引建议考虑删除

• 减少索引和数据的碎片：1行碎片 2行间碎片 3剩余空间碎片，可以通过执行OPTIMIZE TABLE或者到处再导入的方式来重新整理数据。也可以删除索引再增加索引来消除索引碎片化

四：查询性能优化

一、慢查询基础

查询性能低下最基本的原因是访问的数据太多。分析步骤：
1、确认应用程序是否存在检索大量超过需要的数据。
2、确认MySQL服务器层是否在分析大量超过需要的数据行。

二、是否向数据库请求了不需要的数据

1. 查询不需要的记录
2. 多表关联时返回全部列
3. 总是取出全部列
4. 重复查询相同的数据

三、衡量查询开销的三个指标

1. 响应时间
2. 扫描的行数和返回的行数
3. 扫描的行数和访问类型

四、重构查询的方式

1. 一个复杂查询还是多个简单查询
2. 切分查询
3. 分解关联查询：1、让缓存的效率更高。2、减少锁的竞争。3、在应用层做关联，更容易做到高性能和可扩展。4、查询本身效率也可能有所提升。5、可以减少冗余记录的查询

五、查询执行的基础

1. 客户端发送一条查询给服务器
2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将返回结果返回给客户端。

查询优化器

会导致MySQL优化器选择错误的执行计划：

• 统计信息不准确。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
• 执行计划中的成本估算不等同于实际执行的成本。
• MySQL的最优可能和你想的最优不一样。
• MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
• MySQL也并不是任何时候都是基于成本的优化。
• MySQL不会考虑不受其控制的操作的成本。

优化策略可以简单的分成两种：一种是静态优化，一种是动态优化。

MySQL能够处理的优化类型：

• 重新定义关联表的顺序
• 将外连接转化成内连接
• 使用等价变换规则
• 优化COUNT(),MIN(),MAX()
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表IN()的比较

5、mysql优化器的局限性

• 关联子查询
• UNION的限制
• 索引合并优化
• 等值传递
• 并行执行
• 哈希关联
• 松散索引扫描
• 最大值和最小值优化
• 在同一表上查询和更新

6、优化特定类型的查询

• 优化COUNT()查询
• 优化关联查询
• 优化子查询
• 优化GROUP BY和DISTINCT
• 优化LIMIT分页
• 优化SQL_CALC_FOUND_ROWS
• 优化UNION查询
• 静态查询分析
• 使用用户自定义变量

五：MySQL高级特性

1、分区表

原理：
分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

在分区表上的操作按照下面的操作逻辑进行：

select查询：

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作：

当写入一条记录时，分区层打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应的底层表

delete操作：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作：

当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作

在下面的场景中，分区可以起到非常大的作用：

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他都是历史数据

• 分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作

• 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

• 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

分区本身也有一些限制：

• 一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）
• 如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引
• 分区表中无法使用外键约束

分区的策略：

• 全量扫描数据，不要任何索引。
• 索引数据，并分离热点。

2、视图

3、外键

4、在MySQL内部存储代码

5、全文索引

原理：是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。

倒排索引
全文索引通常使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在的位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式。

6、查询缓存

MySQL查询缓存保存查询返回的完整结果。当查询命中缓存，MySQL会立刻返回结果，跳过了解析，优化和执行阶段。

查询缓存执行过程：

1.通过一个大小写不敏感的检查看看SQL语句是不是以SEL开头。

2.若是以SEL开头则获取缓存数据，若是命中则直接返回结果。

3.若没有命中，则通过SQL语句查询数据。

4.返回查询结果给客户端。同时存入查询缓存。

缓存未命中可能的情况：

1.由于查询语句中包含不确定的函数，或者查询结果太大，超过query_cache_limit的值，查询结果无法缓存。

2.MySQL从未处理过这个查询，查询结果没有缓存过。

3.之前缓存了查询结果，但是由于查询缓存内存不足，MySQL将某些缓存逐出，导致未命中。

4.缓存失效操作太多。数据修改，内存不足，缓存碎片都会导致缓存失效。

5.查询缓存还没有完成预热，MySQL还没有机会将查询结果都缓存起来。

不会缓存结果的情况：

1.当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数NOW()，CURRENT_DATE()等类似的函数，或者用户自定义的函数，存储函数，用户变量等都不会被缓存。

2.当查询的结果大于query_cache_limit设置的值时，结果不会被缓存。

3.对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务，会大大降低缓存命中率。