《高性能MySQL》——读书笔记

高性能mysql读书笔记

Mysql的架构可以在多种场景中应用，并发挥好的作用。

Mysql最与众不同的特性是：将数据的存储和提取相分离。

• Mysql架构与历史

1.1、Mysql逻辑架构

逻辑架构图：

第二层架构是mysql的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数都在这一层实现。

第三层包含了存储引擎，存储引擎负责Mysql中数据的存储和提取。服务器通过API与存储引擎进行通信，这些接口屏了不同存储引擎的差异，使得这些差异对查询过程透明。

1. 1. 1. 连接管理与安全性

客户端连接都会在服务器进程中拥有一个线程，连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。

1. 1. 1. 优化与执行

Mysql会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化。（重新查询、决定表的读取顺序、选择合适的索引）。

1.2、并发控制

当多个查询需要在同一时刻修改数据，都会出现并发控制问题。

1.2.1、读写锁

实现两种类型的锁组成的锁系统来解决有读也有写（改）的情况。

读锁（read lock） = 共享锁（shared lock）

写锁（write lock） = 排他锁（exclusive lock）

读锁是共享的，互不阻塞的。

写锁是排他的，会阻塞其他的写锁和读锁。

1.2.2、锁粒度

提高共享资源并发性的方式就是让锁定对象更有选择性，最好：只锁定需要修改的部分数据，而不是所用的资源。

锁定的数据量越少，系统的并发程度越高。

注意：加锁，也是消耗资源的。

锁策略：锁在开销和数据的安全性之间寻求平衡。

大部分数据库系统，都是在表上，施加行级锁（row level lock）。

而mysql提供多种选择：

表锁：（table lock）

是Mysql最基本的锁策略，并且是开销最小的策略，

用户对表进行写操作（cud），需要获得写锁，这会阻塞其他用户对表进行读、写。

并且写锁比读锁有更高的优先级。

行级锁：（row lock）

是最大程度支持并发处理（同时也是最大的锁开销）。

InnoDB、XtraDB都实现了行级锁。行级锁只在存储引擎层实现，而服务层没有实现。

1.3、事务

事务是一组原子性的SQL查询，或者说一个独立的工作单元。

系统要经过严格的ACID测试，否则空谈事务的概念是不够的。

原子性：

一致性：

隔离性

持久性：

对事务处理过程中额外的安全性，需要数据库做更多的额外工作。（和锁粒度升级会增加系统开销一样）

因此Mysql的存储引擎架构就可以发挥优势了，可以根据当前业务，来选择合适的存储引擎。

1.3.1、隔离级别

SQL标准定义了四种隔离级别：

每一种级别规定了一个事物所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。

较低级别的隔离，通常可以执行更高的并发，系统开销也更低。

1. READ UNCOMMITTED（未提交读）

事务可以读取未提交的数据，这也称为脏读（Dirty Read）。

1. READ COMMITTED（提交读）（大多数数据库系统默认的隔离级别，但Mysql不是）

事务开始时，只能“看见”已经提交的事务所做的修改。

换句话说：一个事务从开始到提交之前，所做的任何修改对其他的事务都是不可见的。该级别叫做：不可重复读（nonrepeatable read）。因为执行两次同样的查询，会得到不一样的结果。

1. REPEATABLE READ（可重复读）（Mysql默认的隔离级别）

解决了脏读的问题。保证了同一个事务多次读取同样记录的结果是一致的。

理论上，该级别会产生幻读（Phantom Read）问题：某个事务在读取某个范围内的记录时，另外一二事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）

1. SERIALIZABLE（可串行化）

最高的隔离级别，可以解决幻读问题。它会给读取的每一行数据加锁，因此可能会导致大量的超时和锁争用的问题。

1.3.2、死锁

指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

解决这个问题，数据库系统实现了各种死锁检测和死锁超时机制。

InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚。

1.3.3、事务日志

目的：提高事务的效率。使用事务日志，修改数据时的步骤：

1. 修改其内存拷贝。
2. 将修改行为持久化到事务日志中。（事务日志采用追加方式，因此写日志的操作是在磁盘一小块区域内的顺序I/O）
3. 后台慢慢刷回到磁盘。

因此，修改数据需要写两次磁盘。

1.3.4、Mysql中的事务

自动提交（AUTOCOMMIT）

Mysql默认采用自动提交模式。若不是显式开始一个事务，则每个查询都被当作一个事务执行提交操作。

隐式锁定：InnoDB采用的是两阶段锁定协议（tow-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。

显示锁定：InnoDB也支持特定的语句进行显式锁定：LOCK TABLES 和UNLOCK TABLES，这是在服务器层实现的，和存储引擎无关。

1.4、多版本并发控制（MVCC）

InnoDB的MVCC：通过每行记录后面保存两个隐藏的列来实现的。

一个保存了行的创建时间，一个是保存了行的过期时间（或删除时间）。（注：存储的不是具体的时间值，而是系统版本号）。

每开始一个新的事务，系统版本号会自动递增。

例子：在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT：

1. InnoDB只查找早于当前事务版本号的数据行。（行版本号<=系统版本号），这确保了事务读取的行，要么是事务开始前已经存在，要么是事务自身插入或修改过的。
2. 行删除版本号要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

INSERT：

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

DELETE：

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

UPDATE：

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外的系统版本号，是大多数读操作都可以不用加锁。使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。

这是典型的：用空间换时间

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。

READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。

SERIALIZABLE对所有读取的数据行加锁。

1.5、Mysql的存储引擎

文件系统将Mysql的每个数据库（也称为schema）保存在数据目录下的一个子目录下，创建表时，Mysql会在数据库的子目录下创建一个和表同名的.frm文件保存表的定义。

1.5.1、InnoDB存储引擎

Mysql默认的事务型引擎。使用最多、使用最广。

InnoDB概览：InnoDB的数据存储在表空间（tablespace）中，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。

Mysql4.1后，InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB表是基于聚簇索引建立的。它对主键查询有很高的性能。但：它的二级索引（secondary index，非主键索引）包含主键列，所以如果主键列很大的话，其他索引都会很大。，因此若表中索引很多的话，主键应该尽可能的小。

1.5.2、MyISAM存储引擎

使用场景：只读数据、表较小、可以忍受修复（repair）操作。

缺点：不支持事务和行级锁，崩溃后无法完全恢复。

存储：

MyISAM：会将表存储在两个文件中：数据文件和索引文件，分别以：.MYD和.MYI为扩展名。

 

1.5.5、选择合适的引擎

日志型应用：对插入速度有很高的要求，数据库不能成为瓶颈。MyISAM或者Archive存储引擎对这类应用比较合适，因为他们开销低，而且插入速度很快。

如果对记录日志做分析报表，有两种高效的方案：

1. 主库写，从库读。利用Mysql内置的复制方案将数据复制一份到备库，然后在备库上执行比较耗时和耗CPU的查询。
2. 分表。

 

只读或者大部分情况只读的表：用InnoDB。因为MyISAM不会保证将数据安全地写入磁盘中。

 

 

• Mysql基准测试

基准测试是针对系统设计的一种压力设计。

2.2、基准测试的策略

集成式（full-stack）：针对整个系统的整体测试。

单组件式（single-component）：单独测试Mysql

2.2.1、测试何种指标

吞吐量：单位时间内事务处理数。测试单位：TPS

响应时间或者延迟：测试任务所需的整体时间。通常可以用百分比响应时间来替代最大响应时间。

并发性：任意时间有多少同时发生的并发请求。

可扩展性：

注意：测试应该测对用户来说最重要的指标，应该多了解一下需求，比如什么样的响应时间是可以接受的，期待多少并发性。

• 服务器性能剖析

3.1、性能优化简介

性能：完成某件任务所需要的时间度量。性能即响应时间。

无法测量就无法有效地优化。

优化的方法：用90%的时间测量响应时间花在哪里。而不是花时间去调参！

3.2、对应用程序进行性能剖析

实际上，剖析程序程序比数据库服务器容易，并且回报更多。

3.3、剖析Mysql查询

3.3.1、剖析服务器负载

捕获Mysql的查询到日志文件中。

3.3.2、剖析单条查询

使用 show profile

3.4、诊断间隙性问题

尽量不要用试错的方式解决问题，这种方式有很大风险，并且是一种令人沮丧且低效的方式。

如果一时无法定位问题，可能是测量方式不对，测量点有问题，或者使用的工具不合适。

3.4.1、单条查询问题还是服务器问题

有三个方法可以排查：

1. 一秒执行一次 show global status来捕获数据。

其中观察：Threads_running,Threads_connected、Queries的值，来查看

1. 使用SHOW PROCESSLIST

观察线程是否有大量处于不正常状态或者其他不正常的特征。

通过以下语句，查看某个列值出现的次数：

Mysql -e ‘show processlist\G’ | grep state: |sort|uniq -c |sort -rn

1. 使用查询日志

将long_query_time设为0，这样使所有的查询都记录到日志中。

日志分析的魔法工具：sort|uniq|sort

推荐一个监控服务器的工具：Percona Toolkit。有了它就不用自己写脚本监控了。

 

3.6、总结

1、定义性能最有效的方法是响应时间。

2、无法测量就无法有效优化，性能优化工作，需要基于高质量、全方位及完整的响应时间测量。

3、有两种消耗时间的操作：工作或等待。大多数剖析器只能测量因为工作而消耗的时间。

4、优化和提升是两回事，当继续提升的成本超过收益时，应当停止优化。

5、注意你的直觉，应该根据你的直觉来指导解决问题的思路，而不是用于确定系统问题。

决策应当尽量基于数据而不是直觉。

• Schema与数据类型优化

4.1、选择优化的数据类型

不管选择哪种类型的数据，以下原则有助于做出更好的选择。

1）更小的通常更好

一般情况下，尽量使用可以正确存储数据的最小数据类型。选择一个认为不会超过最小类型的数据类型。

2）简单就好

整形比字符操作代价更低。

应该使用Mysql的内建的类型而不是字符串来存储日期和时间。

用整形存储IP地址。

3）尽量避免NULL

通常情况下，最好指定列为NOT NULL。若计划在列上建索引，就应该尽量避免设计成为NULL的列。

 

选择具体的类型：

DATETIME和TIMESTAMP都可以存储相同类型的数据，时间和日期，精确到秒。然而：TIMESTAMP只使用DATETIME一半的存储空间。

4.1.1、整数类型

整数：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别使用8,16,24,32,64位存储空间。可以存储的值的范围为：，N为存储空间位数。

整数类型还有UNSIGNED属性，表示不允许负值，这可使整数的上限提高一倍。

Mysql可以为整数类型指定宽度，例如：INT（11），对大多数应用这是没有意义的，它不会限制值的合法范围，只是规定了Mysql的一些交互工具（如：Mysql命令行客户端，用来显示字符的个数）。对存储和计算来说，INT（11）和INT（20）是相同的。

4.1.2、实数类型

实数是带有小数部分的数字。

FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。

DECIMAL类型用户存储精确的小数。

浮点类型在存储同样的范围的值时，通常比DECIMAI使用更小的空间。

FLOAT使用4个字节存储，DOUBLE使用8个字节。

Mysql使用DOUBLE作为内部浮点计算的类型。

浮点存储计算会带来不精确。DECIMAL精确计算的代价高。

4.1.3、字符串类型

1）varchar

用户存储可变长字符串。

Varchar使用1或2个额外的字节记录字符串长度，如果类最大长度<=255字节则用1个字节表示，否则用两个。

使用场景：

• • 字符串列的最大长度比平均长度大很多。
  • 列的更新很少，所以碎片不是问题。
  • 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

2）char

Char类型是定长的：Mysql总是根据定义的字符串长度，分配足够的空间。

使用场景：存储短的，或者所有值都接近同一个长度的。

 

慷慨是不明智的

使用varchar（5）和varchar（200）存储的空间是一样的，那使用短的列，有什么优势？

事实证明，更长的列消耗更多的内存，因为Mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内部临时表进行排序或操作时会特别糟糕。

所以：最好是只分配真正需要的空间。

 

3）BLOB和TEXT类型

它们是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符串方式存储。

 

4）使用枚举（ENUM）代替字符串类型

可以用枚举类型代替常用的字符串类型。

 

1. 日期和时间类型

Mysql能存储的最小时间粒度为秒（MariaDB支持微秒级别）。

日期类型有如下两种：

• DATETIME

能保存大范围的值，从1001到9999，精度为秒。使用8字节存储。它把时间封装到格式为YYYYMMDDHHMMSS整数中，与时区无关。

• TIMESTAMP

保存了从1970年1月1日午夜以来的秒数，和UNIX时间戳相同。使用4字节存储空间。只能表示从1970到2038年。

Mysql提供FROM_UNIXTIME()函数把时间戳转换为日期。并提供UNIX_TIMESTAMP()函数把日期转换为UNIX时间戳。

 

除特殊行为外，通常应该尽量使用TIMESTAMP，因为它比DATETIME空间效率更高。

有时人们会将UNIX时间戳存储为整数值，但这并不会带来任何收益。用整数保存时间戳的格式通常不方便处理，所以不推荐这样做。

 

4.1.6、选择标识符

使用自增整数最好。

4.1.7、特殊类型数据

应该用无符号整数存储IP地址。用小数点将地址分成四段的表示方法，只是让人们容易阅读。用整形存储能减少空间。但是可读性低。Mysql提供了两个函数进行装换：

Ip->整形：inet_aton（）

整形->IP：inet_ntoa（）

算法是：国际上对各国IP地址的区分中使用的ip number

a.b.c.d 的ip number是：
a * 256的3次方 + b * 256的2次方 + c * 256的1次方 + d * 256的0次方。

Mysql schema 设计中的陷阱

1. 太多的列
2. 太多的关联。Mysql每个关联操作最多只能有61张表。建议单个查询最好在12个表以内做关联。
3. 全能枚举。过度使用枚举，如下：

1. 变相枚举。

枚举列允许列中存储一组定义值中的单个列，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。

如下反例：

这种情况该使用枚举列代替集合列。

1. 非此发明（Not Invent Here）的NULL

列中避免使用NULL，可以用0，某个特殊值或空字符串作为代替。

但是当确实需要表示未知值时也不要害怕使用NULL。

注：Mysql的索引可以存储NULL值，而Oracle则不会。

4.3、范式和反范式

范式数据中，每个事实数据出现并且只出现一次。

反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

4.3.1、范式的优点和缺点

优点：

1. 更新操作比反范式快。数据范式化后，就只有很少的或者没有重复数据，所以需要修改更少的数据。
2. 范式化的表通常更小，可以更好地放在内存中，所以执行操作会更快。
3. 很少的冗余，意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

缺点：

1. 查询时通常需要关联。稍微复杂一点的查询，可能会更多的关联，这不但代价昂贵，可能会使一些索引失效。

4.3.2、反范式的优点和缺点

优点：

1. 数据都在一张表中，所以可以很好地避免关联。
2. 因为不需要关联表，则大部分查询最差的情况——即使表没有使用索引，是全表扫描，也可能比关联要快得多，因为这样避免随机I/O。
3. 单独的表也能使用更高效的索引策略。

4.3.3、混用范式化和反范式化

完全的范式化和完全的反范式化是实验室里才有的东西。

4.4、缓存表和汇总表

缓存表：表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表。

汇总表：保存的是使用GROUP BY语句聚合的表。

当重建汇总表和缓存表时，通常保证数据在操作时依然可用。这就需要通过使用“影子表”来实现。它是指一张在真实表“背后”创建的表，当完成建表操作后，可以通过一个原子的重命名操作切换影子表和原表。如下：

将my_summary这个名字分配给新建的表之前将原始的my_summary表重命名为my_summary_old，就可以在下一次重建之前一直保留旧版本的数据，如果新表有问题，可以很容易地进行快速回滚。

4.4.1、物化视图

Mysql并不原生支持物化视图。

可以使用开源工具Flexviews实现物化视图。

4.4.2、计数器表

更新计数器表会存在并发问题。

如旧表：

更新操作：

对更新一行的事务来说，这条记录上都有一个全局的互斥锁（mutex），导致这些事务只能串行执行。

要获得更高的并发更新性能，可以将计数器保存在多行中，每次选择一行进行更新。

新表：

预先在新表增加100行数据，然后随机选择一个槽（slot）进行更新

 

还有一个需求，每天开始一个新的计数器。表设计如下：

这个场景就不能用前面的例子那样预生成行，而要用on duplicate key update

4.5、加快ALTER TABLE 操作的速度

技巧：

1. 先在一台不提供服务的机器上执行alter table 操作，然后和提供服务的主库进行切换。
2. “影子拷贝”：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

有一些工具可以完成影子拷贝工作：“online schema change”、Percona Toolkit、Flexviews

不是所有的alter table 操作都会引起表重建。

如修改字段默认值：

慢方法：

理论上，Mysql可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。

然而，所有MODIFY COLUMN 操作都将导致表重建。（没验证过）

快方法：

这个语句直接修改.frm文件而不涉及表数据，所以这个操作非常快。

 

 

• 创建高性能的索引

索引（在Mysql也叫做“键（key）”）。

5.1、索引基础

索引可以包含一个或多个列的值。列的顺序很重要，因为Mysql只能高效地使用索引的最左前缀列。

主键是非空的唯一索引

5.1.1、索引类型

索引是存储引擎层实现的。所以没有统一的索引标准。

Mysql支持的索引类型如下：

 

1）B-Tree索引

存储引擎以不同的方式使用B-Tree索引，性能各有不同。（InnoDB叫B+Tree）

MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。

MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

 

B-Tree通常意味这所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。

InnoDB的索引结构如下：

流程：

1. 从索引的根节点开始搜索，根节点的槽中存放了指向子节点的指针。
2. 通过比较节点页的值和要查找的值，可以找到合适的指针（定义了子节点页中值的上限和下限）进入下层子节点。
3. 叶子节点的指针指向的是被索引的数据，而不是其他的节点页，这样就获取到所需的数据。

 

B-Tree对索引列式顺序组织存储的，很适合查找范围数据。

假设有如下数据表：

下图显示了索引是如何组织数据的存储的；

因为索引树的节点是有序的，所以索引还可以用于查询中的ORDER BY 操作。

 

B-Tree索引的限制：

1. 如果不是按照索引的最左类开始查找，则无法使用索引。
2. 不能跳过索引，索引无法用于查找姓为：Smith且在特定日期出生的人，如果不指定名（first_name），则Mysql只能使用索引的第一列。
3. 如果查询有某个列的范围查询，则右边所有列都无法使用索引优化查找。

如：where last_name = ‘Smith’ and first_name like ‘J%’ and job = ‘1976-12-23’。只能用到索引的前两列，因为like是个范围条件。

 

5.2、索引的优点

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

最常见的B-Tree索引，按照顺序存储数据，所以Mysql可以用来做ORDER BY 和GROUP BY操作。

 

三星系统（three-star system）：

一星：索引将相关的记录放到一起。

二星：索引中的数据顺序和查找中的排列顺序一致。

三星：索引中的列包含了查询中需要的全部列。

 

索引是最好的解决方案吗？

对于非常小的表，简单的全表扫描更高效。

对于中到大型表：索引非常有效。

对于特大型表；需要一种技术可以区分出查询需要的一组数据。（如分区、使用元数据表）。

 

5.3、高性能的索引策略

5.3.1、独立的列

如果查询中的列不是独立的，则Mysql不会使用索引。

我们应该养成简化Where条件的习惯，始终将索引列，放在单独放在比较符号的一侧。

如：这个查询无法使用actor_id列的索引

这也是一个：

5.3.2、前缀索引和索引选择性

当需要索引很长的字符列时，就要考虑前缀索引，否则索引变得大且慢，特别是对BLOB、TEXT或者很长的VARCHAR类型的类，必须要用前缀索引，因为Mysql不允许索引这些列的完整长度。

索引的选择性：不重复的索引值（也称基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引选择性越高，查询效率越高，因为选择性高的索引能在查找时过滤更多的行。

5.3.3、多列索引

当查询的where条件中有or 时或多个and时，会执行索引合并策略（or条件的联合（union）、and条件的相交（intersection），组合前两种情况的联合及相交）。

 

当出现索引的合并策略时，说明索引建得糟糕：

1. 服务器出现多个索引做相交操作时（通常有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2. 当服务器需要多个索引做联合操作时（通常有多个or条件），通常要消耗大量的CPU和内存资源在算法的缓存、排序和合并操作上。
3. 优化器不会把这些计算到“查询成本（cost）”中，优化器只关心随机页面读取。使得查询成本被低估。

 

解决方案：

1. 通过参数optimizer_switch来关闭索引合并功能。
2. 也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

 

5.3.4、选择合适的索引列顺序

在一个多列索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。

 

尽管选择性和基数的经验法则（选择性高的列放在最左边）值得去研究和分析，但一定要记住别忘了where子句的排序、分组和范围条件等其他因素。

5.3.5、聚簇索引

不是一种单独的索引类型，而是一种数据存储方式。

InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

聚簇：表示数据行和相邻的键值紧凑地存储在一起。

聚簇索引中的叶子页（left page）包含了行的全部数据，但是节点页只包含了索引列。如图所示：

InnoDB通过主键聚集数据，图中被索引的列就是主键列。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。

聚集数据的优点：

1. 把相关的数据保存在一起。
2. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据比非聚簇索引要快。
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

1. 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
2. 更新聚簇索引列的代价高。会强制将每个被更新的行移动到新的位置。
3. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。
4. 二级索引（非聚簇索引）可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列。
5. 二级索引访问需要两次索引查找，而不是一次。（二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值）

 

InnoDB和MyISAM的数据分布对比

InnoDB和MyISAM对该表的存储

MyISAM的数据分布：按数据插入顺序存储在磁盘上。

MyISAM在主键索引和其他索引在结构上是一样的：

MyISAM需要独立地对行进行存储。

所以说：主键索引是一个名为PRIMAAY的唯一非空索引。

 

InnoDB的数据分布，因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据。如下图：

在InnoDB中，聚簇索引就是表，它不仅存索引，还存了整个表的信息（col2）。

 

InnoDB的二级索引和聚簇索引很不同，InnoDB二级索引的叶子节点存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。该策略会减少当出现行移动或者数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

下图显示二级索引的分布结构图：

 

用一张图进行对比如下：

主键顺序最好使用：自增主键，避免用UUID。

顺序的主键什么时候造成更坏的结果？

高并发工作负载时，间隙锁的竞争、AUTO_INCREAMENT锁机制、主键的上界会成为“热点”。

5.3.6、覆盖索引

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

优点：

1. 索引条目远小于数据行大小，如果只需要读取索引，那Mysql会极大地减少数据访问量。
2. 索引按顺序储存的（至少在单页内如此），所以对于I/O密集型的访问查询会比随机从磁盘读取每一行数据的I/O要少得多。
3. 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。
4. 存储引擎MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。覆盖索引就能避免查询系统中的数据。

覆盖索引必须存储索引列的值，因此Mysql只能使用B-Tree索引做覆盖索引。

当发起以被索引覆盖的查询时，在Explain的Extra列可以看到“Using index”的信息。

5.3.7、使用索引扫描来做排序

有两种方式生成有序的结果：1、通过排序操作。2、按索引顺序扫描（如果Explain 出来的type列的值为“index”，说明使用了索引扫描来做排序）。

只有ORDER BY子句满足索引的最左前缀要求，Mysql才会利用索引排序。（例外，order by子句不满足索引的最左前缀的要求，但是多列索引的最左列为常量时，会利用索引排序）。

5.3.8、压缩（前缀压缩）索引

MyISAM压缩索引块的方法是：

如：索引块第一个值是“perform”，第二个值是：“performance”，那么第二个值的前缀压缩后存储的是类似“7，ance”这样的形式。

5.3.9、冗余和重复索引

1、创建了索引（A,B），再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。（这种冗余是对B-Tree索引来说）

2、索引扩展为（A,ID），其中ID是主键，对于InnoDB来说，主键列包含在二级索引中了，所以这也是冗余的。

大多数情况都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。

但也有出于性能方便的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。

一般来说：增加新索引将会导致Insert、update、delete等操作的速度变慢。

5.3.10、未使用的索引

有些服务器永远不用的索引，这些事累赘，建议删除。

5.3.11、索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB的访问行数，从而减少锁的数量。

5.5、总结

1. 单行访问时很慢的。
2. 按顺序访问访问数据是很快的。
3. 索引覆盖查询是很快的。

• 查询性能优化

高性能：需要库表结构优化、查询优化、索引优化齐头并进。

6.1、为什么查询速度会慢

查询的生命周期：

客户端——服务器——服务器解析——生成执行计划——执行——返回结果给客户端

执行是最重要的阶段：包括大量为了检索数据到存储引擎的调用以及调用后的数据处理（排序、分组）

6.2、慢查询基础：优化数据访问

对于慢查询，可以通过下面两个步骤来分析：

1. 确认应用程序是否在检索大量超过需要的数据。
2. 确认服务器层是否在分析大量超过需要的数据行。

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

1. 查询不需要的记录

常见错误：以为Mysql只会返回需要的数据，实际上Mysql却是先返回全部结果集再进行计算。

最简单的解决方法：在查询后面加limit。

1. 多表关联时返回全部列

如：你想查询所有在电影academy Dinosaur中出现的演员，千万不要这样写，它会返回三个表的全部数据。

正确的方式，只取需要的列：

1. 总是取出全部列

每次看到select * 的时候都需要用怀疑的眼光审视，是不是需要返回全部列。取全部列会让优化器无法完成索引覆盖扫描这类优化。

但是返回超过需要的列，会简化开发。

所以自己要权衡。

1. 重复查询相同的数据，可以自己做缓存。

6.2.2、Mysql是否在扫描额外的记录

查询查询开销的三个指标

1. 响应时间
2. 扫描的行数
3. 返回的行数
4. 访问类型：由慢到快分别是：全表、索引、范围、唯一索引、常数引用

 

Mysql使用如下三种方式应用where条件，从好到坏依次是：

1. 索引中使用where条件过滤不需要的行。在存储引擎完成。
2. 使用索引覆盖扫描（Extra列出现：Using index）来返回记录，在mysql服务器层完成。
3. 从数据表返回数据，然后过滤不满足条件的过滤（在Extra列中出现Using Where）。在Mysql服务层完成。

6.3、重构查询的方式

1. 一个复杂的查询还是多个简单的查询
2. 切分查询
3. 分解关联查询

上面的思路是：分而治之

6.4、查询执行的基础

查询的执行路径如下：

6.4.1、Mysql客户端/服务器通信协议

C/S的通信协议是：半双工的。

需要了解Mysql的查询状态。

6.4.2、查询缓存

查询缓存时一个对大小写敏感的哈希查找实现，查询和缓存中的查询及时只有一个字节不同，也不会匹配缓存结果。

6.4.3、查询优化处理

通过下图，展示Mysql如何进行关联：

1. 文件排序

当不能用索引进行排序时，Mysql需要自己进行排序，如果数据量小，则在内存中进行，如果数据量大则需要你使用磁盘，不过mysql统一称为：文件排序（fiiesort）

两种排序算法：（旧）

两次传输排序：读取行指针和需要排序的字段，排序后，再根据排序结果读取所需要的数据行。

单词传输排序：（新）

先读取所需要的所有列，然后再根据给定列进行排序，然后直接返回排序结果。

在关联查询时，如果需要排序，Msyql会分两种情况处理：

1. 当order by的所有列都来自关联的第一个表，那么在关联处理第一个表时，就进行文件排序。Extra字段显示：using filesort
2. 除此之外，会将关联的结果放到一个临时表中，然后所有的关联处理后，再进行文件排序。Extra字段显示：Using temporary；Using filesort。

6.4.4、查询执行引擎

6.4.5、返回结果给客户端

6.5、MySQL查询优化器的局限性

6.5.1、关联子查询

In 加子查询，性能通常非常糟糕。建议使用join或exists（）等效的改写查询来获取更好的效率。

注：需要通过测试来验证对子查询的执行计划和响应时间的假设，而不要听信什么“绝对真理”

6.5.2、UNION的限制

6.6、查询优化器的提示

通过显示配置sql语句，实现对优化器的提示。

常用：use index、ignore index 、force index

6.7、优化特定类型查询

6.7.1、优化count（）查询

1. Count（）作用

1. 统计某个列值的数量。在（）里指定了列和列的表达式，就统计这个表示是有值的结果数。
2. 统计行数。当确定（）内不可能为NULL，就统计行数。

Count（*）就是统计行数的标准写法。

1. MyISAM神话

MyISAM引擎使用count（*），且没有任何where条件时，速度会很快。

1. 简单的优化

例子1：

例子2：

1. 更复杂的优化

熟悉的困境：“快速、精确和实现简单”永远只能满足三选二。

6.7.2、优化关联查询

1. 确保on或者using子句中的列上有索引。
2. 确保任何GROUP BY和ORDER BY中的表达式只涉及到一个表中列。

6.7.3、优化子查询

6.7.4、优化GROUP BY 和DISTINCT

6.7.5、优化LIMIT分页

若表很大，需要分页时，优化方法是：尽可能使用索引覆盖扫描，而不是查询所有列，然后根据需要做一次关联操作再返回所需的列。

一定要加Order by

如：select * from payment_logs order by id limit 1000000,20

可改写为：

select * from payment_logs a inner JOIN

(

select id from payment_logs order by id limit 1000000,20

) b

on a.id = b.id

6.7.7、优化union查询

6.9、总结

优化需要三管齐下：不做、少做、快速地做。

• Mysql高级特性

7.1、分区表

对用户来说，分区表是一个独立的逻辑表，底层由多个物理子表组成。

分区实际上是对一组底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。

目的：将数据按照一个较粗的粒度分在不同的表上。

使用场景：

1. 表非常大，以至于无法全部放在内存中，或者表的最后部分有热点数据，其他均是历史数据。
2. 分区表的数据更容易维护。想批量删除大量的数据，可以使用清除整个分区的方式。
3. 可分布在不同的物理设备上。
4. 备份和恢复独立的分区。

分区的限制：

1. 一个表最多只能有1024个分区。
2. 分区表达式必须是整数，或返回整数的表达式。MySQL5.5中，可以直接使用列进行分区。
3. 分区字段有主键或唯一索引的列，那他们要包含进来。
4. 分区表无法使用外键约束。

7.1.1、分区表的原理

分区表由多个相关底层表实现，这些底层表也是由句柄对象（Handler Object）表示。存储引擎管理分区表和其他表都一样，从存储引擎来看，分区底层表和普通表没区别。

分区表的索引只是在各个底层表上各自加上一个完全相同的索引。

1. Select查询

先锁住所有底层表，再判断是否可以过滤部分分区，在访问各区数据。

1. Insert操作

先锁住——再确定——最后写入

1. Delete操作

先锁住——再确定——最后删除

1. Update操作

先锁住——确定更新记录放哪个分区——取数更新——判断更新后的数据在哪个分区——对底层表进行写入——对原数据底层表进行删除

虽说每个操作都会“先打开并锁住所有的底层表”，但并不一定是锁住全表，如果存储引擎能够自己实现行级锁，如InnoDB，就不会锁全表，这个加锁和解锁的过程与查询类似。

7.1.2、分区表的类型

一般用按范围分区。

7.1.3、如何使用分区表

数据量很大时，B-Tree索引就不起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表。

分区可理解为索引的最初形态。

7.1.4、什么情况下会出问题

1. Null值会使分区过滤无效。经测试，mysql5.6已处理这个问题
2. 分区列和索引列不匹配
3. 选择分区的成本很高
4. 打开并锁住所有底层表的成本可能很高
5. 维护分区可能成本很高。如重组分区。

7.1.5、查询优化

使用explain partition可以观察到优化器使用了哪些分区。

7.2、视图

视图本身是一个虚拟表、不存放任何数据。

方法一：实现视图有一个简单方法，将select结果放在一个临时表中。

Temporary table：只在当前连接可见，当关闭连接，Mysql会删除表并释放空间。

方法二：讲视图定义的Sql合并进查询SQL。

该两种算法的实现如下，建议使用合并算法。

7.3、外键约束

InnoDB是目前Mysql中唯一支持外键的内置索引。

使用外键是有成本的，外键通常要求在修改数据时都要在另外一张表中多执行一次查找操作。

7.4、Mysql内部存储代码

7.5、游标

Mysql在服务端提供只读的、单向的游标，而且只能在存储过程或者更底层的客户端API使用。

7.6、绑定变量

7.10、全文索引

使用场景：通过关键字的匹配来进行查询过滤，需要基于相似度的查询，而不是精确查询。

支持各种字符内容搜索：char、varchar、text

MATCH AGAINST

7.10.1、自然语言的全文索引

使用语法：match（列，【列】） against（需要查找的内容）

如下：

7.10.2、布尔全文索引

 

7.12、查询缓存

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

查询缓存对应用程序是完全透明的。

7.12.1、Mysql如何判断缓存命中

Mysql判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈希值引用。

当查询语句中有一些不确定的数据时，则不会被缓存。如函数now（）、currrent_date()的查询不会被缓存。

注意：查询缓存对读写带来额外消耗：

1. 读之前必须先检查是否命中缓存。
2. 如果这个读查询可以被缓存，当执行完后，Mysql若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
3. 当向某个表写入数据时，Mysql必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作可能带来很大的系统消耗。

7.12.3、什么情况下查询缓存能发挥作用

查询缓存可以由如下公式计算：Qcache_hits/(Qcache_hits+Com_select)

7.12.4、如何配置和维护查询缓存

7.12.7、查询缓存的替代方案

查询缓存工作的原则是：执行查询最快的方式是不去执行。

 

 

• 优化服务器设置

Mysql有大量的参数可以修改——但不应该随便去修改。通常只需要把基本配置项配置正确（大部分情况下只有很少一些参数是真正正确的），应该花更多时间在schema的优化、索引、以及查询设计上。

8.1、MySQL配置的工作原理

在类unix系统上：配置文件一般在/etc/my.cnf或者/etc/msyql/my.cnf中。

找到配置文件的路径；

Which mysqld

**/**/mysqld –verbose –help|grep -A 1 ‘Default options’

8.3、创建MySQL配置文件

InnoDB在大多数情况下运行得很好，配置大小合适的缓冲池（Buffer Pool）和日志文件（Log File）是必须的。

8.4、配置内存使用

8.4.5、InnoDB缓冲池（Buffer Pool）

如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。

InnoDB缓冲池并不仅仅缓存索引，还缓存行的数据、自适应哈希索引、插入缓冲、锁，以及其他内部数据结构。

8.5、配置Mysql的I/O行为

8.5.1、InnoDB I/O配置

InnoDB事务日志

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无需在每个事务提交时吧缓冲池的脏块刷新到磁盘中。

InnoDB用日志把随机I/O变成顺序I/O。一旦日志安全写到磁盘，事务就持久化了，即使变更还没写到数据文件，如果发生了糟糕的情况，InnoDB可以重放日志并且恢复已经提交的事务。

8.7、基于工作负载的配置

8.7.2、优化排序

 

 

• 操作系统和硬件优化

9.1、什么限制了MySQL的性能

最常见的两个瓶颈：CPU 和 I/O。

当找到一个限制系统的因素时，应该问问自己：“是这个部分本身的问题，还是系统中其他不合理的压力转移到这里所导致的？”

如：内存不足时，MySQL可能必须刷出缓存来腾出空间给需要的数据，然后过一会，再读回刚刚刷新的数据，本来是内存不足，去导致出现了I/O容量不足。

 

 

• 复制

10.1、复制概述

复制解决的基本问题是：让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。

MySQL支持两种复制方式：基于行的复制、基于语句的复制。

实现方式：通过在主库上记录二进制日志，在备库重放日志的方式来实现异步的数据复制。

Mysql复制大部分是向后兼容的。

10.1.1、复制解决的问题

10.1.1、复制如何工作

三个步骤：

1. 在主库上把数据更改记录到二进制日志（Binary log）中。（这些记录被称为二进制日志事件）。
2. 备库将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库读取中继日志的事件，将其重放到备库数据上。

逻辑图如下：

复制用到三个线程来处理：

1. 备库的I/O线程，它更主库建立一个普通的客户端连接。
2. 主库的二进制转储线程，它会读取主库上二进制日志中的事件。
3. 备库的SQl线程，它从中继日志中读取事件并在备库执行。

10.2、配置复制

三个步骤：

1. 在每台服务器上创建复制账号
2. 配置主库和备库
3. 通知备库连接到主库并从主库复制数据。

10.3、复制的原理

10.3.1、基于语句的复制

主库记录那些造成数据更改的查询，当备库服务并重放这些事件时，实际上只是把主库上执行过的SQL再执行一遍。

好处：实现简单。简单地记录和执行这些语句，能够 让主备保持同步。

不足：存在一些无法被真确复制的SQL，如使用CURRENT_USER()函数的语句。

10.3.2、基于行的复制

将实际数据记录在二进制日志中。

好处：正确复制每一行。

由于无需重放更新主库数据的查询，使用基于行的复制模式能够更高效地复制数据。

如：

这个语句最终只产生3条语句，使用基于行的复制方式，在备库上开销会小很多。

但是，

这个SQL做了全表更新，使用基于行复制开销会大很多，因为每一行数据都会被记录到二进制日志中。而使用基于语句的复制方式代价会小很多。

第十一章、可扩展的Mysql

11.1、什么是可扩展性

可扩展性，表明当需要增加资源以执行更多工作时，系统能够获得划算的等同提升（equal bnad for the buck）的能力。

容量：系统在一定时间内能够完成的工作量。

 

 

附录D：Explain

EXPLAIN 获取关于查询执行计划的信息，以及如何解释输出。

调用Explain

在查询语句前加一个EXPLAIN这个词。

可以通过Explain partiions 显示查询将访问的分区。

Explain是一个近似的结果。

1. 并不区分具有相同名字的事务。如：对内存排序和临时文件都使用filesort，对磁盘和内存的临时表都显示using temporary
2. Explain只能解析select查询。

Explain中的列

1. ID列

一个编号，标识Select 所属的行。

1. Select_type列

这一列显示了对应行是简单还是复杂select。

简单：SIMPLE意味着查询不包括子查询和union。

复杂：最外层标记为PRIMARY，其他部分标记为：

SUBQUERY：包含select 列表中的子查询中的select（子查询不在from 子句中）

如下sql：explain select (select 1 from login_logs limit 1) from login_logs limit 10

DERIVED：表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。服务器内部称为：派生表，因为该表是从子查询中派生来的。

如下sql：explain select id from (select * from login_logs limit 10) a

UNION：在UNION中的第二个和随后的SELECT被标记为UNION。

UNION RESULT：用来从UNION的匿名临时表搜索结果的SELECT

如下SQL：explain select id from games union all select id from games

1. Table列

显示对应行正在访问哪个表。

1. Type列

显示访问类型——Mysql决定如何查找表中的行，下面是最重要的访问方法，从最差到最优：all < index < range < ref < eq_ref < const,system < NULL

All:全表扫描。

Index：跟全表扫描一样，Mysql扫描表时按索引次序进行而不是行。主要的优点是：避免排序，缺点是：承担按索引次序读取整个表的开销。

若Extra列中看到“using index”，说明Mysql正在使用覆盖索引，它指扫描索引的数据，而不是按索引次序扫描每一行。

它比按索引次序全表扫描开销要少很多。

Range：有限制的索引扫描，它开始索引里的某一点，返回匹配这个值域的行。

Ref：索引访问，也叫索引查找，它返回所有匹配某个单个值的行。它会找到多个符合条件的行，因此，它是查找和扫描的混合体。把它叫做：ref，是因为索引要跟某个参考值相比较。这种索引访问只有使用非唯一索引或唯一索引的非唯一性前缀时才会发生。

Eq_ref:使用这种索引查找，Mysql知道最多只返回一条符合条件的记录。这种访问方法以在MySQL使用主键或唯一性索引查找时看到。

Const，system：当mysql能对查询的某部分进行优化并将其转换为一个常量时，就会使用这些访问类型。

NULL：这种访问方式意味着Mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。

1. Possible_keys:显示查询可以使用哪些索引。
2. Key列：改列决定哪个索引来优化对该表的访问。
3. Key_len列：Mysql在索引里使用的字节数。
4. Ref列：显示了之前的表在key列记录的索引中查找值所用的列或常量。
5. Rows列：MySQL估计为了找到所需的行而要读取的行数。这个值是mysql认为它要检查的行数，而不是结果集的行数。
6. Filtered列：使用explain extended时出现，它显示的是针对表里符合某个条件（where子句或联接条件）的记录数的百分比所做的一个悲观估算。
7. Extra列

该列包含的是不适合在其他列显示的额外信息：

“Using index”：使用覆盖索引，避免访问表。注意：不要和访问类型index弄混。

“Using where”：Mysql服务器将在存储引擎检索行后再进行过滤。

“Using temporary”：对查询结果使用一个临时表。

“Using filesort”：对查询结果使用一个外部索引排序，而不是按照索引次序从表里读取行。

MySQL5.6中的改进

Mysql5.6 支持对类似：Update、Insert等的查询进行解释。