高性能Mysql3-开发基础

第1章 Mysql架构与历史

1.1 Mysql逻辑架构

最上层：应用层。如连接处理、授权认证、安全等。

第二层：服务器层。如查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能：存储过程、触发器、视图等。

第三层：存储引擎层。负责Mysql中数据的存储和提取。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。

1.1.2 优化与执行

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

对于SELECT语句，在解析语句之前，服务器会先检查查询缓存，如果能够在其中找到对应的查询，服务器就不必在执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

Mysql在两个层面的并发控制： 服务器层、存储引擎层。

1.2.1 读写锁

​ 读锁：共享锁（shared lock），多个用户在同一时刻可以同时读取同一个资源，而互不干扰。

​ 写锁：排他锁（exclusive lock），在给定的时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

1.2.2 锁粒度

任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

每种mysql存储引擎都可以实现自己的锁策略和锁粒度。

表锁（table lock）：最基本的锁策略，并且是开销最小的策略。它会锁定整张表。

行级锁（row lock）：行级锁只在存储引擎层实现，而mysql服务器层没有实现。

1.3 事物ACID

原子性（atomicity）：一个事物被视为一个不可分割的最小工作单元。要么全部成功，要么全部失败。

一致性（consistency）: 数据库总是从一个一致性的状态转换到另外一个一致性的状态。

隔离性（isolation）: 一个事物所做的修改在最终提交之前，对其他事物是不可见的。

持久性（duraability）: 一旦事物提交，则其所做的修改就会永久保存到数据库中。

1.3.1 隔离级别

READ UNCOMMITIED(读未提交)：事物中的修改，即使没有提交，对其他事物也都是可见的。 会导致脏读（dirty read）。

COMMITIED（提交读｜不可重复读）：大多数数据库的默认事物隔离级别。一个事物开始时，只能看见已经提交的事物所做的修改。

REPEATABLE READ(可重复读)：Mysql默认的事物隔离级别。保证了在同一个事物中多次读取同样记录的结果是一致的。但无法处理幻读（phantom read），InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrentcy Control)解决了幻读问题。

幻读：当某个事物在读取某个范围内的记录时，另外一个事物又在该范围内插入了新的记录，当之前的事物再次读取该范围的记录时，会产生幻行。

SERIALIZABLE(可串行化)： 通过强制事物串行执行，避免了幻读问题。该级别会在读取的每一行数据上都加锁。

1.3.2 死锁

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

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

锁的行为和顺序是和存储引擎相关的。

1.3.3 事物日志

事物日志可以帮助提高事物的效率。使用事物日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘的事物日志中，而不用每次都将修改的数据本身持久到磁盘中。

事物日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事物日志的方式相对来说快得多。

事物日志持久后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。通常称之为预写式日志（Write-Ahead Logging）,修改日志需要写两次磁盘。

1.3.4 Mysql中的事物

• 自动提交（AUTOCOMMIT）: 如果不是显示地开始一个事物，则每个查询都被当作一个事物执行提交操作。

查询自动提交模式：

show VARIABLES LIKE 'AUTOCOMMIT';

• 设置隔离级别:

set session TRANSACTION ISOLATION LEVEL READ COMMITED;

• 在事务中混合使用存储引擎

在同一事物中，使用多种存储引擎是不可靠的。如果在事务中混合使用了事务型表和非事务表（例如InnoDB和MyISAM表），如果该事物需要回滚，非事务型表上的变更就无法撤销。

• 隐式和显式锁定

InnoDB采用的是两阶段锁定协议（two-phase locking protocol)。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放。这些都是隐式锁定，In noDB会根据隔离级别在需要的时候自动加锁。

另外，InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范。

SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR UPDATE;

1.4 多版本并发控制

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

不管需要执行多长时间，每个事务看到的数据都是一致的。根据事物开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

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

InnoDB的实现机制：

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

这两个列，一个保存了行的创建日期，一个保存了行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。

在REPEATABLE READ隔离级别下，MVCC的实现方式：

• SELECT

  InnoDB会根据以下两个条件检查每行记录：

  ​ a. InnoDB只查找版本小于等于当前事务版本的数据行。

  ​ b. 行的删除版本要么未定义，要么大于当前事务版本号。

  只有符合上述两个条件的记录，才能返回作为查询结果。

• INSERT

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

• DELETE

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

• UPDATE

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

1.5 存储引擎

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

因为Mysql使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。不同的存储引擎保存数据和索引的方式是不同的。

1.5.1 InnoDB引擎

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成。在MySQL4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发，实现了四个隔离级别。其默认的隔离级别是REPEATABLE READ。通过间隙锁（next-key locking)策略来防止幻读的出现。

InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。

1.5.2 MyISAM存储引擎

不支持事务和行级锁，不支持崩溃后的安全恢复。

1.5.3 Mysql内建的其他存储引擎

• Archive引擎
• Blackhole引擎
• CSV引擎
• Federated引擎
• Memory引擎
• Merge一起
• NDB引擎

1.5.4 第三方存储引擎

• OLTP类引擎
• 面向列的存储引擎
• 社区存储引擎

1.5.5 选择合适的存储引擎

除非万不得已，否则不要混合使用多种存储引擎。

如果应用需要不同的存储引擎，请先考虑一下几个因素。

• 事务
• 备份
• 崩溃恢复
• 特有的特性

1.5.6 转换表的引擎

• ALTER TABLE

  alter table mytable engine = InnoDB;
  

• 导出与导入

• 创建与查询

1.6 Mysql时间线

1.7 Mysql的开发模式

遵循GPL开源协议。

第4章 Schema与数据类型优化

Mysql为了兼容性支持很多别名，例如INTEGER、BOOL，以及NUMERIC，但实际使用的还是基本类型。

4.1 选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要，不管存储哪种类型的数据，以下几个简单的原则可以有助于做出更好的选择：

• 更小的通常更好

• 简单就好。简单数据类型的操作通常需要更少的CPU周期。

• 尽量避免NULL

  如果查询汇总包含可为NULL的咧，对Mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在Mysql里也需要特殊处理。当可为null的列被索引时，每个索引记录需要一个额外的字节。

4.1.1 整数类型

• TINYINT(8位)、SMALLINT(16)、MEDIUMINT(24)、INT(32)、BIGINT(64).
• 有符号和无符号类型使用相同的存储空间，并具有相同的性能。
• 整数类型有可选的UNSIGNED属性，表示不允许负值，这大致可以使整数的上限提高一倍。
• 对于存储和计算来说，INT(1)和INT(20)是相同的，只是规定了显示字符的个数。
• 整数计算一般使用64位的BIGINT整数。

4.1.2 实数类型(real number)

• DECIMAL类型用于存储精确的小数。DECIMAL只是一种存储格式，在计算中DECIMAL会转换成DOUBLE类型。

• 浮点运算，依赖所使用平台的浮点数的具体实现。

• 浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。和整数类型一样，能选择的只是存储类型；mysql使用DOUBLE作为内部浮点计算的类型。

• 在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

4.1.3 字符串类型

• VARCHAR和CHAR类型

  1、值存在磁盘和内存，和存储引擎的具体实现有关。

  2、VARCHAR类型用于存储可变长字符串，是最常见的数据类型。它比定长类型更节省空间，因为它仅使用必要的空间。

  3、VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

  4、VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这导致需要做额外的工作。

4.2 MySQL schema设计中的陷阱

• 太多的列。mysql的存储引擎API工作时，需要在服务器层和存储引擎层之间通过行缓存格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转成行数据结构的操作代价是非常高的。
• 太多的关联。mysql限制了每个关联操作最多只有61张表。
• 全能的枚举enum
• 变相的枚举set
• 非此发明的NULL

4.3 范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

4.3.1 范式的优点和缺点

优点如下：

• 范式化的更新操作通常比反范式化要快
• 数据的修改量小
• 范式化表的表通常更小，可以更好的放在内存里，所以执行操作会很快
• 很少有多余的数据，意味着更少需要DISTINCT或者GROUP BY语句。

缺点：

• 通常需要关联，关联次数更多，容易使一些索引策略失效。

4.3.2 反范式的优点和缺点

优点：

• 所有数据都在一张表，有效地避免了关联
• 单表能使用更有效的索引策略

缺点：

• 表数据量过大，操作耗时长

4.3.3 混用范式化和反范式化

在实际应用中，经常需要混用范式化和反范式化，可能使用部分范式化的schema、缓存表，以及其他技巧。

最常用的反范式化数据的方式是复制或者缓存，利用触发器更新值。

从父表冗余一些数据到子表的理由是排序、查询的需要。

4.4 缓存表和汇总表

缓存表：存储可以比较简单地从Schema其他表获取（但是每次获取的速度比较慢）数据的表（例如逻辑上冗余的数据）。

汇总表：保存的是使用GROUP BY语句聚合数据的表（例如逻辑上不是冗余的表）。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多的碎片，以及有完全顺序组织的索引。

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。影子表和真实表切换。

4.4.1 物化试图

mysql不支持物化视图，使用Flexviews可以实现。

4.4.2 计数器表

计数器表，在更新时可能会遇到并发问题。

要获得更高的并发更新性能，也可以将计数器保存在多行，每次随机选择一行进行更新。在获取计数器时，使用count().

4.5 加快ALTER TABLE操作的速度

一般，大部分ALTER TABLE操作将导致Mysql服务中断。对常见场景，能使用的技巧有两种：

• 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。
• 用要求的表结构创建一张和表无关的新表，然后通过重命名和删表操作交换两张表。

所有的MODIFY COLUMN操作都将导致表重建。

通过ALTER COLUMN操作来改变列的默认值，这个语句会直接修改.frm文件而不涉及表结构，这个操作是非常快的。

4.5.1 只修改.frm文件

// 不建议，非DBA不要求掌握

4.5.2 快速创建MyISAM索引

// 不建议，非DBA不要求掌握

4.6 总结

第5章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构，

索引优化应该是对查询性能优化最有效的手段来。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”的索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

5.1 索引基础

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

5.1.1 索引的类型

1. B-Tree索引

   存储引擎以不同的方式使用B-Tree。性能也各有不同。InnoDB按照原数据格式进行存储，根据主键引用被索引的行。

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

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

   可以使用B-Tree索引的查询类型。使用于全键值、键值范围或键最左前缀查找，对以下类型的查询有效：

   • 全值匹配
   • 匹配最左前缀
   • 匹配列前缀
   • 匹配范围值
   • 匹配某一列并范围匹配另一列
   • 只访问索引的查询
   • 用于查询中的ORDER BY操作

   B-Tree索引的限制：

   • 如果不是按照索引的最左列开始查找，则无法使用索引。
   • 不能跳过索引中的列。
   • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

2. 哈希索引

   哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

   在Mysql中，只有Memory引擎显式支持哈希索引，也是其默认索引类型。InnoDB引擎也有特殊的功能-自适应哈希索引，也可实现自定义哈希索引（需要手动维护）。

   – 不常用跳过

3. 空间数据索引

   – 不常用跳过

4. 全文索引

   – 不常用跳过

5. 其他索引

   – 不常用跳过

5.2 索引的优点

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

5.3 高性能的索引策略

5.3.1 独立的列

alter table demo add key(city);

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

始终将索引列放在比较符号的一侧。

5.3.2 前缀索引和索引选择性

alter table demo add key(city(7));

索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，mysql不允许索引这些列的完整长度。（实际上没啥意义）

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：Mysql无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

计算合适的前缀长度办法

• 确定前缀长度。

  为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较，直至前缀的选择性接近列的选择性。

  select count(*) as cnt, city from demo group by city desc limit 10;
  select count(*) as cnt, left(city, 7) as pref from demo group by pref order by cnt limit 10;
  

• 计算完整列的选择性，并使前缀的选择性接近于完整列的选择性：

select count(distinct city) / count(*) from demo;

5.3.3 多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高Mysql的查询性能。Mysql5.0和更新版本引入了一种叫“索引合并“的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。实际上该策略说明表上的索引建的很糟糕。

可以使用optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX让优化忽略到某些索引。

5.3.4 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常时很好的，这个时候索引的作用只是用于优化WHERE条件的查找。

可以跑一些查询来确定在表中值的分布情况，并确定哪个列的选择性更高。

select count(distinct staff_id)/count(*) as staff_id_sleectivity, count(distinct customer_id)/count(*) as customer_id_selectivity, count(*) from demo;

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。一个表只能有一个聚簇索引，一般是primary或者unique列。

优点：

• 可以把相关数据保存在一起，减少磁盘I/O
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 聚簇数据最大限度提高了I/O密集型应用的性能，但数据如果全部放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序
• 更新聚簇索引列的代价很高
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子结点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

InnoDB的数据分布：

聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有剩余列。

在InnoDB表中按主键顺序插入行：

避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集性的应用。例如使用UUID是特别糟糕的选择。使用InnoDB时应该尽可能地按照主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

5.3.6 覆盖索引

如果一个索引包含｜覆盖所有需要查询的字段的值，那么称之为覆盖索引。

好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大地减少数据访问量。
• 因为索引是按照列值顺序（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。

mysql使用B-Tree索引做覆盖索引。

mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE条件中的字段，但不是整个查询涉及的字段。如果条件为假，mysql5.5以及更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

mysql不能在索引中执行LIKE操作。这是低层存储引擎API的限制，Mysql5.5和更早版本中只允许在索引中做简单比较操作（例如等于、不等于以及大于）。mysql能在索引中做最左前缀匹配的LIKE匹配，因为该操作可以转换为简单的比较操作。

explain select * from products where actor = 'sean carrey' and title like '%apollo%';

延迟优化关联后：

explain select * from products join (
	select prod_id from products where actor = 'seac carrey' and title like '%apollo%'
) as t1 on (t1.prod_id = products.prod_id);

5.3.7 使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为index，则说明mysql使用了索引扫描来做排序（不要和Extra的Using index混淆）。

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序｜正序）都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用全部为第一个表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求。

5.3.8 压缩（前缀压缩）排序

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中。

5.3.9 冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。

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

5.3.10 未使用的索引

在Persona Server或者中先打开userstates服务器变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

另外可以使用Persona Toolkit中的pt-index-usage工具。

5.3.11 索引和锁

索引可以让查询锁定更少的行。

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得select for update比 lock in share mode或非锁定查询要慢很多。

5.4 索引案例学习

5.4.1 支持多种过滤条件

存在索引（sex, coutry），当不查询sex时，可以sex in (‘m’,‘f’)来匹配最左前缀。但in个数过多则不行。

基本原则：

• 考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。
• 尽可能地将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

5.4.2 避免多个范围条件

对于 范围条件查询，mysql无法再使用范围列后面的其他索引列，但是对于“多个等值条件”则没有这个限制。

如果最后一个索引列前面的索引列，取值固定，可以生成默认值，定时任务去更新值。

5.4.3 优化排序

1、对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。

2、使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的行。

5.5 维护索引和表

即使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.5.1 找到并修复损坏的表

1、CHECK TABLE通常能够找出大多数的表和索引的错误。可以使用REPAIR TABLE来修复损坏的表。

2、设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据，更多细节可以参考mysql手册。也可以使用InnoDB Data Recovery Toolkit。

3、从备份中恢复表，也可以尝试从损坏的数据文件中尽可能地恢复数据。

5.5.2 更新索引统计信息

mysql的查询优化器会通过两个API来了解存储引擎的索引值的分布情况，以决定如何使用索引。

1、records_in_range()。InnoDB返回估算值，MyISAM返回精确值。

2、info()。返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

mysql优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息来解决这个问题。

InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在老版本，样本页面数是8，新版本可以通过参数innodb_stats_sample_pages来设置样本页的数量。

InnoDB会在表首次打开，或者执行ANALYZE TABLE，抑或表的大小发生非常大的变化（大小变化超过十六分之一或者新插入20亿行数据都会触发）的时候计算索引的统计信息。

InnoDB在打开某些INFORMATION_SCHEMA的表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或在mysql客户端开启自动补全功能的时候都会触发索引统计信息的更新。

统计信息更新时，会可能会导致大量的锁，可以通过innodb_stats_on_metadata参数来避免上面提到的问题。

如果想要更稳定的执行计划，并在系统重启后更快地生成这些统计信息，那么可以使用系统表来持久化这些索引统计信息。甚至还可以在不同机器间迁移索引统计信息。通过innodb_analyze_is_persistent参数控制。

5.5.3 减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。

表的数据存储也可能碎片化，主要有三种类型的数据碎片：

• 行碎片
• 行间碎片
• 剩余空间碎片

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。新版本的InnoDB新增了“在线”添加和删除索引的功能，可以通过删除然后再重新创建索引的方式来消除索引的碎片化。

对于不支持OPTIMIZE TABLE的存储引擎，可以通过ALTER TABLE操作来重建表，只需要将表的存储引擎修改为当前的引擎即可：

alter table <table> engine = <engine>;

对于开启了expand_fast_index_creation参数的Percona Server，按这种方式重建表，则会同时消除表和索引的碎片化。对于标准版本的mysql则只会消除表（聚簇索引）的碎片化。可用先删除所有索引，再重建表，最后重新创建索引的方式实现。

5.6 总结

理解索引是如何工作的？这是创建索引的基础。

如何判断一个系统创建的索引是合理的呢？一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、sql和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是有太多回表查询那些不在索引中的列的操作。

第6章 查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进，一个不落。在获得编写mysql查询的经验的同时，也将学习到如何为高效的查询设计表和索引。同样的，也可以学习到在优化库表结构时会影响到那些类型的查询。

1、理解mysql如何真正地执行查询，并明白高效和低效的原因何在。

2、学会如何去改变一个查询的执行计划。

3、探索查询优化的模型，以帮助mysql更有效地执行查询。

6.1 为什么查询速度会慢

在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。如何把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数。

通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向低层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

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

对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

1、确认应用程序是否在检索大量超过需要的数据。

2、确认mysql服务器层是否在分析大量超过需要的数据行。

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

典型案例：

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

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

对于mysql，最简单的衡量查询开销的三个指标如下：

• 响应时间。服务时间和排队时间之和。
• 扫描的行数
• 返回的行数

一般mysql能够使用以下三种方式应用where条件，从好到坏依次为：

• 在索引中使用where条件来过滤不匹配的记录。在存储引擎层完成。
• 使用索引覆盖扫描（在extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在mysql服务器层中完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在服务器层完成，mysql需要先从数据表读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，那么可以通过以下办法去优化它：

• 使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表就可以返回数据。
• 改变库表结构。例如使用汇总表
• 重写这个复杂的查询。

6.3 重构查询的方式

有时候不一定要从mysql获取一模一样的数据集，也可以通过应用代码完成同样效果的查询。

6.3.1 一个复杂查询还是多个简单查询

有时候将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

在删除旧数据时，如果用一个大语句一次性完成，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但重要的查询。

6.3.3 分解关联查询

select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';

–>

select * from tag where tag = 'mysql';
select * from tag_post where tag.id = 1234;
select * from post where post_id in (123,456,789);

优点：

• 让缓存的效率更高。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也有可能有所提升。
• 可以减少冗余记录的查询。
• 相当于在应用中实现了哈希关联。

6.4 查询执行的基础

mysql的执行流程：

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

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

mysql客户端和服务端之间的通信协议是“半双工”的。一旦客户端发送了请求，它能做的事情就只有等待结果了。参数max_allowed_packet就非常重要了。mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束，早点释放相应的资源。

查询状态：

show full processList

返回结果中的command就表示当前状态：

• sleep：线程正在等待客户端发送新的请求。
• Query：线程正在执行查询或者正在将结果发送给客户端。
• Locked：在mysql服务器层，该线程正在等待表锁。
• Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的统计计划。
• Copying to tmp table [on desk]：线程正在执行查询，并且把其结果集都复制到一个临时表中，这种状态一般要么是在做group by操作，要么是文件排序操作，或者是union操作。如果这个状态后面还有“on desk”标记，那表示mysql正在将一个内存临时表放到磁盘上。
• Sorting result：线程正在对结果集进行排序。
• Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

6.4.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询，是否命中查询缓存中的数据。这个检查是通过检查一个对大小写敏感的哈希查找实现的。

6.4.3 查询优化处理

• 语法解析器和预处理

  首先，mysql通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。

  mysql解析器将使用mysql语法规则和解析查询。

  预处理器则根据一些mysql规则进一步检查解析树是否合法。

  下一步预处理器会验证权限。

• 查询优化器

  现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。

  mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。以下是查询语句：

  show status like 'Last_query_cost';
  

  优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

  很多种情况会导致mysql优化器选择错误的执行计划，如下：

  • 统计信息不准确
  • 执行计划中的成本估算不等同于实际执行的成本
  • mysql的最优可能和你想的最优不一样
  • mysql从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
  • mysql也并不是任何时候都是基于成本的优化。
  • mysql不会考虑不受其控制的操作的成本。例如执行存储过程或用户自定义函数的成本。

  mysql的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，另一种是动态优化。

  • 静态优化：直接对解析树进行分析，并完成优化。在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。编译时优化。
  • 动态优化：和查询的上下文有关，也可能和很多其他因素有关。例如where条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，“运行时优化”。

  下面是一些mysql能够处理的优化类型：

  • 重新定义关联表的顺序。数据表中的关联并不总是按照在查询中指定的顺序执行。
  • 将外连接转化成内连接。
  • 使用等价变换规则
  • 优化count()、min()、max()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较

• mysql如何执行关联查询

  mysql认为任何一个查询都是一次“关联”。

  当前mysql关联执行的策略很简单：mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表中匹配的行，返回查询中需要的各个列。

• 执行计划

  mysql生成查询的一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。

• 关联查询优化器

  mysql优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通过评估成本来确定最优的关联顺序。

• 排序优化

  排序是一个成本很高的操作，应尽可能避免排序或者尽可能避免对大量数据进行排序。

  文件排序：当不能使用索引生成排序结果时，mysql需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。

  如果需要排序的数据量小于“排序缓冲区”，mysql使用内存进行“快速排序”操作。如果内存不够排序，那么mysql会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将每个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。

  mysql有两种排序算法：

  • 两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，然后在根据排序结果读取所需要的数据行。
  • 单次传输排序（新版本使用）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。

6.4.4 查询执行引擎

执行计划是一个数据结构，而不是和很多其他关系型数据库那样会生成对应的字节码。

mysql只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的借口来完成，这些接口也就是我们成为“handler API“的接口。查询中的每个表由一个handler的实例表示。 这种简单的接口模式，让mysql的存储引擎插件式架构成为可能。

6.4.5 返回结果给客户端

将结果返回给客户端。即使查询不需要返回结果集给客户端，mysql仍然会返回这个查询的一些信息，如该查询的影响到的一个行数。

如果查询可以被缓存，那么mysql在这个阶段也会将结果存放到查询缓存中。

mysql将结果集返回客户端是一个增量、逐步返回的过程。这样处理的好处：

• 服务器无需存储太多的结果，减少内存消耗。
• 让mysql客户端第一时间获得返回的结果。

数据集中的每一行都会以一个满足mysql客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对mysql的封包进行缓存然后批量传输。

6.5 Mysql查询优化器的局限性

mysql万能的嵌套循环并不是对每种查询都是最优的，往往可以通过改写查询让mysql高效地完成工作。mysql5.6本班，消除了很多限制，优化了查询效率。

所有的优化，都必须依赖版本和实际效果。

6.5.1 关联子查询

EXPLAIN select * from student st
WHERE st.id in (select cs.sid from class_stu cs where cs.cid = 'c_2');

exist -->

EXPLAIN select * from student st
where EXISTS (select * from class_stu cs where cs.cid = 'c_2' and st.id = cs.sid);

6.5.2 UNION的限制

– 不是很有必要

6.5.3 索引合并优化

– 无介绍

6.5.4 等值传递

– 无介绍

6.5.5 并行执行

mysql不支持并行。

6.5.6 哈希关联

mysql旧版本不支持哈希关联，可以通过建立一个哈希索引来曲线地实现哈希关联。

6.5.7 松散索引扫描

由于历史原因，并不支持松散索引扫描。

6.5.8 最大值和最小值优化

用limit 1来优化。

select max(actor_id) from sakila.actor where first_name = "22" 

–>

select actor_id from sakila.actor use index(primary) where first_name = "22" limit 1;

6.5.9 在同一表上查询和更新

mysql不允许对同一个表进行查询和更新。通过生成临时表的形式来绕过上面限制。

6.6 查询优化器的提示（hint）

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。具体看mysql官方文档。

6.7 优化特定类型的查询

6.7.1 优化count()的查询

在统计列值时，不统计NULL值。

-- 0.407s 0.401
EXPLAIN select count(*) from student where id > 232334243;
-- 0.415s 0.423s
EXPLAIN select (select count(*) from student)-count(*) from student where id <= 232334243

很难进行优化，索引覆盖扫描、增加汇总表、外部缓存系统。“快速、精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

6.7.2 优化关联查询

• 确保on和using子句中的列上有索引。当表A和和表B用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要在表B上建立索引。

• 确保任何的group by和order by中的表达式只涉及到其中一个表的列，这样才有可能使用索引。

• 当升级mysql版本时，关联语法、运算法优先级等可能会发生变化。

6.7.3 优化子查询

mysql5.6之间尽可能用关联查询替代。

6.7.4 优化GROUP BY和DISTINCT

• 使用索引
• 尽可能将group by with rollup转移到应用程序中处理。

小概率，不在乎排序结果的优化：

-- 0.420s
EXPLAIN select name,count(*) from student where id < 232334243 GROUP BY NAME;
-- 0.415s
EXPLAIN select name,count(*) from student where id < 232334243 GROUP BY NAME ORDER BY null;

6.7.5 优化limit分页

limit 1000, 20这样的查询，mysql需要查询10020条记录，然后返回最后的20条记录。

解决思路：

• 在页面中限制分页数量
• 优化大偏移量的性能

处理办法：

• 覆盖索引

• 延迟关联

  -- 1.764s
  select * from student order by name limit 10000,20;
  -- 1.056s
  select * from student INNER JOIN (select id from student order by name limit 10000,20) as li USING(id)
  

• 对于单调递增主键

  -- 第一页
  select * from rental order by rental_id limit 20;
  -- 分页
  select * from rental where rental_id <16030 order by rental_id desc limit 20;
  

• 汇总表

• 关联冗余表，冗余表只包括主键列和需要做排序的数据列

• 下一页，不记录总数、页数。

• 可以考虑使用使用explain的rows列的近似值

6.7.6 优化SQL_CALC_FOUND_ROWS

在 MySQL 中，SQL_CALC_FOUND_ROWS 是一个查询选项，用于在执行带有限制条件的 SELECT 查询时，同时获取匹配记录的总数。通常，在执行带有 LIMIT 子句的查询时，MySQL 会返回符合条件的查询结果行数和实际返回的数据行数，这两个值是分开计算的。

使用 SQL_CALC_FOUND_ROWS 可以告诉 MySQL 在返回查询结果之前先计算匹配记录的总数，并将该总数存储在内部变量中。然后，可以通过调用 SELECT FOUND_ROWS() 来检索存储的总数值，而不需要再次执行原始的查询。

-- 不建议使用
select SQL_CALC_FOUND_ROWS * from student where id > 232334243 limit 2000,20;
SELECT FOUND_ROWS(); 

6.7.7 优化UNION查询

除非确实需要服务器消除重复的行，否则就一定要使用union all。

6.7.8 静态查询分析

使用Percona Toolkit中的pt-query-advisor

6.7.9 使用用户自定义变量

set @rownum :=0;
select actor_id,@rownum := @rownum + 1 as rownum from actor limit 1;

用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程都存在。在实际中有一定用处，可以处理冷门场景。

6.8 案例学习

6.8.1 使用Mysql构建一个队列表

6.8.2 计算两点之间的距离

6.8.3 使用用户自定义函数