《高性能MySQL》阅读笔记

MySQL

基准测试

测试策略

• 集成式（整个系统）

• 单组件式（MySQL）

测试指标

• 吞吐量

• 响应时间或者延迟

• 并发性

• 可扩展性

基准测试应避免的错误

• 使用真实数据的子集

• 错误的数据分布

• 多用户场景中，只做单用户的测试

• 单服务器上测试分布式应用

• 与真实用户行为不匹配

• 反复执行同一个查询

• 没有检查错误

• 忽略了系统预热

• 使用默认的服务器配置

• 测试时间太短

服务器性能剖析

性能优化简介

通过性能剖析进行优化

• 步骤

  • 测量任务所花费的时间

  • 对结果进行统计和排序，将重要的任务排到前面

• 分类

  • 基于执行时间的分析：什么任务执行额时间长？

  • 基于等待的分析：判断任务在什么地方被阻塞？

理解性能剖析

除了性能剖析软件展示出的结果，需额外考虑

• 值得优化的查询

  • 占总响应时间比重很小的查询是不值得优化的

  • 成本过高的优化，不值得优化

• 异常情况

  • 执行次数少，但每次时间都长

• 未知的位置

  • 丢失的时间，需核对时间总和

• 被掩藏的细节

  • 平均时间不可靠，应关注实时时间，响应时间的直方图、 百分比、标准差、偏差指数等。

对应用程序进行性能剖析

剖析MySQL查询

剖析服务器负载

• 捕获MYSQL的查询到日志文件中

  • 慢查询日志：在I/O密集型场景，慢查询日志带来的开销忽略不计

  • 通用日志：在查询请求到服务器时进行记录，不包含响应时间和执行计划等重要信息，意义不大。

  • MySQL5.1之后将日志记录到数据库中没什么用，影响性能。将慢查询日志的开销从微妙级退化到秒级。

分析查询的日志

• 工具：pt-query-digest

• 离散指数(V/M)：离散指数高的查询对应的执行时间的变化较大，值得优化。

• InvitesNew后面的?:？表示分片的表，某个分片负载过高则需注意。

• 查询的执行时间出现峰值

  • 需考虑是否是查询条件传递了不同的值，而值的分布不均匀，导致选用不同索引。

  • 查询缓存没命中

剖析单条查询

• Show profile命令：分析sql的执行资源消耗情况

  • 使用方法

    --https://blog.csdn.net/hnjsjsac/article/details/107320055
    -- 1.执行sql语句
    -- 2. 执行show profile命令
    show profiles;
    -- 3.查看某一个查询语句的详细信息
    show profile for query 1; -- 假设sql id为1
    
    -- 注意，show profile命令无法使结果按照花费时间排序，可以使用直接查询INFORMATION_SCHEMA获得
    

  • 返回结果字段

    status：sql语句的执行状态
    duration：sql执行过程中每一个步骤的耗时
    cpu_user:当前用户占用的cpu资源
    cpu_system:系统占用的cpu资源
    block_ops_in:I/O输入
    block_ops_out:I/O输出
    

• 使用show status命令

  -- 大部分结果是计数器
  --常用的计数器包括
  --    句柄计数器
  --    临时文件
  --    表计数器
  show status where Variable_name like 'Handler%' or Variable_name like 'Created%'
  
  -- 注意，show status本身也会创建一个临时表，且会通过句柄操作访问此表，因此会导致句柄计数器不准
  

• 使用慢查询日志：慢查询日志包含了show status和show profile的所有信息

• 使用performance_schema

  -- 主要保存 MySQL 服务器运行过程中的一些状态信息,包括：最近执行了哪些语句、在执行过程的每个阶段都花费了多长时间、内存的使用情况等。
  

使用性能剖析

诊断间歇性问题

一些间歇性数据库性能问题的实际案例

• 应用通过curl 以 一个运行得很慢的外部服务来获取汇率报价的数据。

• memcac hed 缓存中的一些重要条目过期，导致大量请求落到MySQL以 重新生成缓 存条目。

• DNS 查询偶尔会有超时现象。

• 可能是由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL 的 查询缓存有时候会导致服务有短暂的停顿。

• 当并发度超过某个阙值时，Inn oDB 的扩展性限制导致查询计划的优化需要很长的时 间

诊断单条查询问题还是服务器问题

• 高频率执行show global status

  通过一些计数器（Threads_running, Threads_connected,Questions和Queries）的尖刺或者凹陷来发现。
  
  Queries,Threads_connected,Threads_running这三个数据的趋势对于服务器级别偶尔停顿的敏感性很高。
  
      一般发生此类问题时，根据原因的不同和应用连接数据库方式的不同，每秒的查询数 一般会下跌，而其他两个则至少 有一个会出现尖刺。
      可能的原因包括：1.服务器内部碰到了某种瓶颈，导致新查询在开始执行前因为需要获取老查询 正在等待的锁而造成堆积。2.服务器突然遇到了大量查询请求的冲击，memcached突然失效导致的查询风暴。
  

• 使用show processlist：通过不停捕获输出，观察是否有大量线程处于不正常的状态

  • 查询长时间处于“statistics”状态，一般指服务器在查询优化阶段如何确定表关联的顺序，速度很快。

  • 大量线程报告当前连接用户是“Unauthenticateduser未经验证的用户”，连接握手的中间过程的状态，客户端输入用于登录的用户信息时才会出现。

• 使用查询日志

  需要设置慢查询日志，且在全局级别设置long_query_time=0
  

推荐诊断问题时，先使用show status和show processlist，这两种方法的开销很低。

捕获诊断数据

• 诊断触发器

  • Percona Toolkit中的pt-stalk

• 需要收集什么样的数据

  系统的状态、CPU利用率、磁盘使用率、磁盘可用空间、ps的输出采样、内存利用率、show status的信息、show processlist信息、show innodb status信息
  

• 解释结果数据

  • 捕获到问题后应检查（1）问题是否真的发生了；（2）是否有明显的跳跃性变化。

  • 排除问题

    • 性能低下的SQL查询

    • 使用不当的索引

    • 设计糟糕的数据库逻辑架构

Schema与数据类型优化

选择优化的数据类型

• 更小的通常更小

• 简单就好：使用MySQL内建类型存储日期和时间，如使用整形存储IP地址

• 尽量避免Null

  1.会占用更多的空间，如compact格式有NULL值列表用bitmap表示列值是否为NULL；Redundant格式每个字段偏移第一位用于标识该值是否为NULL
  2.会使聚合函数出现变差，如max(),count(),min()
  3.干扰排序、去重、分组结果
  

整数类型

• 可选类型：tinyint, smallint,mediumint,int,bigint。分别为1,2,3,4,8字节。

• 可选unsigned，存储范围大一倍

实数类型

• float、double：分别占用空间4,8字节，不是特别精确。

• decimal：精确计算，代价高。

varchar和char类型

• varchar：需要额外的空间存储长度，如小于255字节的需要额外一个字节存储长度，大于255字节的需要更多字节存储长度。

• char：适用（1）存储密码的MD5；（2）只有Y和N的值；等定长值或很短的值。会删除最后的空格

Blob和Text类型

• 当值过大时，会在每行存储一个指针，指针指向真正存储blob和text类型的数据位置

• blob和text的不同在于blob存储二进制，没有排序规则或字符集，而text有。

枚举Enum

不推荐使用，把类型写死了，推荐使用tinyint和smallint

日期和时间

• Datetime：范围为1001到9999年，精度为秒。

• timestamp：只占四字节，能根据时区自动变化。范围为1970-2038年。

位数据类型

• Bit ： 慎用

• Set：去重，慎用

特殊类型数据

IPv4地址，不推荐用字符串存储。推荐用inet_aton('192.168.2.12')，inet_ntoa(3232236044)

MySQL schema设计中的陷阱

• 太多的列：存储引擎工作时需要在服务层和存储引擎层行缓冲格式拷贝数据，然后服务器需要将行缓冲格式的数据转换成行数据结构，这种转换操作代价十分高。列越多，操作代价越高。

• 太多的关联

• 全能枚举

• 变相枚举

  CREATE TABLE ...( is_defaultset("Y','N') NOT NULL default 'N'）
  枚举 (ENUM) 列允许在列中存储一组定义值中的单个值，集合 (SET)列则允许在列 中存储一组定义值中的一个或多个值。有时候这可能比较容易导致混乱。
  

• 当需要表示未知情况时，可以用NULL

范式的优点和缺点

• 优点

  • 更新操作比反范式化更快

  • 当数据较好地范式化时，就只有很少或没有数据重复，只需要修改更少的数据

  • 范式化的表通常更小，可以更好地放在内存，执行操作更快

  • 很少有多余的数据，意味检索时更少需要distinct和group by语句

• 缺点

  • 需要更多关联

反范式的优点和缺点

混用范式化和反范式化

• 把经常需要join查询的字段，在其他表里冗余一份。但是更新的代价会更高，需要更新多张表的数据。

• 排序的需要可以冗余。

  例如，在范式化的schema 里通 过作者的名字对消,息做排序的代价將会非常高，但是如果在message 表中缓存author_ name 字段并且建好素引，则可以非常高效地完成排序。
  

计数器表

• 将计数器保存在多个行中，每次随机选择一行进行更新。增高并发性

• 每隔一段时间开始一个新的计数器。

CREATE TABLE daily_hit_count (
day date not null,
slot tinyint unsigned not null,
cnt int unsigned not null,
primary key(day, slot)
) ENGINE=InnoDB;

加快Alter table操作的速度

• alter table本质：创建一个新的表结构，将旧的表中的数据插入到新表，再删除旧表

• 常见的方式

  • 现在一台不提供服务的机器上执行alter table，然后和提供服务的主库进行切换

  • 影子拷贝：影子拷贝的技巧是用要求的表结构 . 创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

• Alter table 中的modify column、alter column、change column

  • Modify column：都会使表重建

  • Alter column：直接修改.frm文件，避免表重建，不涉及表数据

只修改.frm文件

• 不需要重建表

  • 移除一个列的Auto_increment属性

  • 移除、增加、更改ENUM和Set常量

• 步骤：创建一个新空表，更改列结构，将新表的.frm文件替换旧表的.frm文件，删除新表

Performance Schema（第四版）

启用或禁用performance schema

show variables like 'performance_schema'; -- 查看是否开启
-- 在配置文件中修改
-- performance_schema=on

启用或禁用performance_schema插桩

• 使用setup_instruments表（重启会失效）

update setup_instruments set enabled='YES' where name='statement/sql/select';

• sys存储过程（重启会失效）

call sys.ps_setup_enable_instrument('statement/sql/select');

• 配置文件启动选项

参数：performance-schema-instrument

支持语法：performance-schema-instrument='instrument_name=value'

例子:

performance-schema-instrument='statement/sql/select=onselec'

5 创建高性能索引

5.1 索引基础

5.1.1 索引的类型

• B-Tree索引

  • 全值匹配

  • 匹配最左前缀

  • 匹配列前缀

  • 匹配范围值

  • 精准匹配某一列病范围匹配另外一列

  • 索引覆盖

• 哈希索引

  • 哈希索引质包含哈希值和行指针，而不存储字段值

  • 不是按照索引值顺序存储的

  • 不支持部分索引列匹配查找

  • 只支持等值比较查询

  • 访问哈希索引的数据非常快

  • 哈希冲突高时，维护成本高

  • 自适应哈希索引：InnoDB注意到某些索引值被使用得非常频繁时，会在内存中基于BTree索引建立哈希索引。

  • 创建自定义哈希索引

    -- 例如需要存储大量的URL，并需要根据URL进行搜索查找
    
    select id from url where url="http://ww.mysql.com"
    
    -- <==>
    select id from url where url="http://ww.mysql.com" and url_crc=CRC32("http://ww.mysql.com")
    -- 删除原表中的url的索引，在原有表中新加一个对url做哈希索引的列
    

5.2 索引的优点

• 索引大大减少了服务器需要扫描的数据量

• 帮助服务器避免排序和临时表

• 将随机I/O变成顺序I/O

5.3 高性能的索引策略

5.3.1 独立的列

1.不能是表达式的一部分
select ... from ... where id + 1 = 5
2.不能进行函数操作
select ... from ... where to_days(current_date) = ...;

5.3.2 前缀索引和索引选择性

• blob、text或很长的varchar必须使用前缀索引

• 查看不同长度前缀索引分布

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

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

  select count(distinct city)/count(*) from city_demo;
  select count(distinct left(city, 7))/count(*) from city_demo;
  

• 前缀索引缺点

  • 无法利用前缀索引做order by和group by

  • 无法利用索引做覆盖扫描

• 后缀索引：如电子邮件。MySQL不支持后缀索引，但是可以倒叙存储。

• 创建前缀索引

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

5.3.3 多列索引

• 索引合并

  • intersection合并

    要求：每个索引获取的二级索引记录都是按照主键值排序的
    key1和key2有单列索引
    
    例1：select .. from s_table where key1='a' and key2='b';
    根据key1和key2获得的记录都是按照主键值排序的，所以可以使用and合并索引
    
    例2：select .. from s_table where key1>'a' and key2='b';
    这个就不能用到索引合并，因为二级索引不同时，无法保证主键值有序
    
    idx_key_part是key_part1和key_part2的联合索引
    例3：例2：select .. from s_table where key1='a' and key_part1='a';
    因为匹配key_part1走的是idx_key_part联合索引，所以无法保证主键值有序。是按照key_part1排序后再按照key_part2排序的，所以主键无法保证有序。
    
    例4：select .. from s_table where key1='a' and id=>9000;
    可以使用索引合并，因为id是主键，当使用key1的索引时，获取的二级索引记录是可以保证主键有序的，而id本身就是主键，在主键索引树上是有序的。
    

  • union合并

    条件与intersection合并相同
    

  • sort-union合并

    union合并的条件过于苛刻，于是有了sortunion合并。
    1. 根据or条件连接的条件获取二级索引记录
    2. 将获取到的二级索引记录按照主键值进行排序
    3. 利用union进行合并
    

出现了索引合并优化，说明：

• 意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引

• 对个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。

• 优化器不会把第二点的消耗计算到“查询成本中”，优化器只关心随机页面读取。会使优化器误判。

5.3.4 选择合适的索引列顺序

• 多列索引应将能过滤掉大量数据的字段放在前面。

5.3.5 聚簇索引

优点

• 可以把相关数据保存在一起。

• 数据访问更快。索引和数据在同一个B+Tree中。

• 使用覆盖索引扫描的查询可以直接使用叶节点的主键值。

缺点

• 最大限度提升了IO密集型应用的性能，如果数据全放在内存中，访问顺序就没那么重要了，聚簇索引也就没什么优势了。

• 插入速度严重依赖于插入顺序。

• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

• 可能存在页分裂的问题

• 聚簇索引会导致全表扫描更慢，尤其时行比较稀疏，或者由于页分裂导致数据存储不连续的时候(行稀疏或数据不连续都会导致全表扫描的时候在硬盘上频繁跳转)。

• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了行的主键列。

• 二级索引访问需要两次索引查找，而不是一次。

无序字段（以uuid为例）作为主键的缺点

• 写入的目标页可能已经刷到磁盘并从缓存中移除，或者还没被加载到缓存中，InnoDB在插入之前必须从硬盘读取目标页到缓存中。这导致大量的随机IO。

• 写入是乱序的，InnoDB频繁地做页分裂操作。

• 频繁的页分裂会导致页变得稀疏并被不规则地填充，数据会有碎片。

5.3.6 覆盖索引

优点

• 只读取索引，会极大地减少数据访问量

• 范围查询比随机从磁盘读取数据的I/O要少得多

• InnoDB聚簇索引，如果二级主键能够覆盖查询，可以避免对主键的二次查询

延迟关联（没有索引下推的时候，有索引下推就不用这个了）

• 原查询语句

  EXPLAIN SELECT * FROM products HHERE actor="SEAN CARREY’ ->AND title 1ike %APOLLO%'
  
  
  

• 优化后的查询语句

  先将索引扩展至覆盖三个数据列(artist,title,prod_id)。
  
  explain select *
  from products
      join(
          select prod_id
          from products
          where actor='sean carrey' and title like '%apollo%'    
      ) as t1 on (t1.proid_id=products.prod_id)
      
  

5.3.7 使用索引扫描来做排序

Explain出来的type列的值为"index",则说明使用了索引扫描来做排序。

5.4 索引案例学习

5.4.1 支持多种过滤条件

• 建议在创建不同组合索引的时候将(sex,country)列作为前缀。

  查询跟性别相关时可以使用
  sex in ('m','f')
  来操作
  

• 需要考虑常见where条件的组合

      (sex, country, age)上的素引就是一个很明显的选择，另外很有可能还 需要(sex,country, region,age)和(sex,country,region,city,age)这样的组合素引。
       1. 可以通过in()的技巧避免同时需要(sex,country,age)和(sex,country,region,age)索引。
  

• 将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

5.4.2 避免多个范围查询

通常一个查询无法使用多个索引。

5.4.3 优化排序

5.5 维护索引和表

5.5.1 找到并修复损坏的表

• check table：通常能找出大多数的表和索引的错误。

• repair table：修复损坏的表

6 查询性能优化

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

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

• 查询了不需要的记录

  • 使用SELECT 语句查询大量的结果，然后获取前面的N行后关闭结果集

  • 解决：用limit限制

• 多表关联时返回全部列

  • Select * ....

  • 解决：select时只返回需要的列

• 重复查询相同的数据

  • 在用户评论的地方需要查询用户头像的URL，那 么用户多次评论的时候，可能就会反复查询这个数据。

  • 解决：初次查询的时候将该数据缓存起来。

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

最简单的衡量查询开销的三个指标：

• 响应时间

• 扫描的行数

• 返回的行数

扫描的行

• 服务时间：数据库执行这个查询真正用到的时间

• 排队时间：服务器应等待某些资源而没有真正值得查询的时间

扫描的行数和返回的行数

扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间。

扫描的行数和访问类型

MySQL能够使用如下三种方式应用where条件，从好到坏以此为：

• 在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的。

• 使用索引覆盖扫描（Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。在MySQL服务器层完成的，但不需要回表。

• 从数据表中返回数据，然后过滤不满足条件的记录（Extra列出现Using where）。在MySQL服务器层完成，MysQL 需要先从数据表读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，则可以使用下面的技巧取优化：

• 使用索引覆盖，把所有用到的列都放到索引中去

• 改变库表结构。例如使用单独的汇总表

• 重写这个复杂的查询

6.3 重构查询的方式

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

• 其他条件都相同的时候，使用尽可能少的查询是好的。

• 将一个大查询分解为多个小查询是很有必要的。（需衡量会不会减少工作量）

6.3.2 切分查询

定期清楚大数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。例如：

DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH;

优化后：

rows affected = 0 
do {
rows affected =do_query(
"DELETE FROM messages WHERE created <DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

6.3.3 分解关联查询

连接查询分解成多个单个查询更高效的场景

• 可以缓存和重用之前查询结果中的数据时

• 当在多台服务器上分发数据时

• 当能够使用in()列表代替连接查询结果中的数据时

• 当一次连接查询中多次引用同一张表时

将一个关联查询分解成若干个单表查询，在应用层做分解，提高缓存效率。但是，当这些相关联的表有一个被更新之后，就无法利用缓存查询了。

6.4 查询执行的基础

查询优化器选择错误执行计划的原因

• 统计信息不准确

• 成本指标并不完全等于实际执行时的成本，即使统计信息准确，但也有可能跟实际执行情况有区别。

• MySQL的最优成本并不完全等于执行时间最短，它只是基于成本模型选择最优的执行计划。

• MySQL不考虑并发执行

• MySQL并不是任何时候都是基于成本选择执行计划， 有时也会基于固定规则优化。

• MySQL不考虑不受其控制的执行成本，如存储过程和用户自定义函数的成本。

• 优化器有时候可能无法估算所有的执行计划，所以可能会错过最优的。

6.4.3 查询优化处理

• 重新定义关联表的顺序

• 将外连接转化成内连接

• 使用等价变换规则：移除恒成立和恒不成立的判断，合并减少一些比较。

• 优化count()、min()和max():如果min和max字段有索引，则直接找到索引树的最左端或最右端，看做一个常数。

• 预估并转化为常数表达式

• 覆盖索引扫描

• 子查询优化

• 提前终止查询：（1）使用limit子句的时候；（2）发下不成立的条件

• 等值传播

• 列表in()的比较：将in中的数值进行排序，再使用二分查找。

MySQL有两种排序算法：

• 两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，再根据行指针获取所需要的数据。需要进行两次数据传输。

• 单词传输排序（新版本使用）：先读取查询所需要的列，然后再根据给定列进行排序，最后直接返回排序结果。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

关于in的子查询优化：

• 生成物化表

• 物化表子查询转半连接

  • 子查询上拉

  • 消除重复值

  • 松散扫描

  • 首次匹配

• 用exists或not exists

6.5.2 union的限制

如果需要将两个表合并，取前20条记录。应该在每个子句中分别用limit。否则的话会将两个子句的所有结果拼在一起，再取前20条记录。

-- 应该用
(select ... from table1  order by ... limit 20) union all (select ... from table2 order by ... limit 20) limit 20;
-- 而不是
(select ... from table1 order by ...) union all (select ... from table2 order by ...) limit 20;

6.7.1 优化count()查询

• 分类统计时可以用sum(if(color='blue', 1, 0)) as blue , sum(if(color='red', 1, 0))

• 使用近似值

• 更复杂的优化：索引覆盖还不能满足，只能修改架构，或增加汇总表。

6.7.2 优化关联查询

• 如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。一般来说，只需在关联顺序中的第二个表中建索引。

• 确保group by 和 order by中的表达式值设计到一个表中的列，这样MySQL才有可能用索引优化。

• 当升级MysQL的时候需要注意:关联语法 、运算符优先级等其他可能会发生变化 的地方。

6.7.7 优化union查询

• 除非确实需要服务器消除重复的行，否则就一定要使用union all。因为没有all关键字，MySQL会使用distinct选项，这会导致对整个临时表的数据做唯一性检查，代价非常高。