《高性能Mysql》学习笔记（三）

zxdsb666.

《高性能Mysql》学习笔记（三）

前言

​  接续上文继续介绍：《高性能Mysql》学习笔记（二）。

索引

B-Tree 索引

​ 即没有特别指明的类型，大多数时候mysql 引擎都支持这种索引（Archive 是例外， 5.1 之前不支持，之后支持单个自增列的索引）

​ 区别：

• myisam 使用物理位置保存 索引位置，并且对于索引进行了前缀压缩
• innodb 则按照原有数据格式保存数据，并且只要了主键的形式进行索引

​ 内部都是根据存储引擎的实现而有了不同的区别

示例

1. 首先创建一张表

2. 内部存储结构

​ 索引对于多个值进行排序的根据是create table 当中定义索引时候的顺序，看一下最后两个条目

下面的查询类型有效

• 全值匹配
  • 和索引当中所有的列进行匹配
• 匹配最左前缀
  • 只用索引的第一列
• 匹配列前缀
  • 匹配某一列值开头的部分
• 匹配范围值：精确匹配某一列并范围匹配另一列
• 只访问索引的查询
  • 即只需要访问索引即可，不需要索引，类似直接走聚簇索引

B-Tree 索引的限制：

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

哈希索引

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

​ mysql中只有 Memory 引擎支持哈希索引，这样说Memory 表默认的索引类型

限制

• 哈希索引只包含哈希值和行指针，不存储字段值
• 哈希索引数据并不是按照索引顺序存储，*无法用于排序
• 哈希索引不支持部分索引匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
• 哈希只支持等值的比较查询，不支持范围查询
• 访问哈希数据非常快，哈希冲突的时候需要对于链表进行遍历
• 哈希冲突高的时候，维护索引操作的代价也很高

InnoDB 引擎的自适应哈希索引

当某个索引值频繁使用的时候，会在内存中基于B-Tree 索引创建一个哈希索引

创建自定义哈希索引

在B-Tree 上创建一个伪哈希索引

如下：

1. 创建一个伪哈希索引；但是这样会有很高的查询开销

2. 使用下面语句可以对于性能的极大提升

   

缺点：

​ 需要手动维护哈希值，可以使用触发器或者手动维护实现

空间索引（R-Tree）

​ myisam 表支持，具体内容可以自行搜索，由于myisam引擎不是重点不做介绍

全文索引

​ 查找文本当中的关键字

​ 搜索细节：

1. 停用词
2. 词干
3. 复数
4. 布尔搜索

其他索引

​ Toku 引擎使用的树索引

索引的优点

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

高性能索引策略

独立的列

前缀索引和索引的选择性

​ 前缀索引可以使索引更小，更快的有效办法，但是mysql 有个缺陷无法对于前缀索引使用order by 和 group by,无法使用前缀索引做覆盖扫描。

多列索引

选择合适的索引列顺序

经验法则：

1. 将选择性最高的列放在索引的最前列（不一定准确）
2. 避免随机的IO和排序

聚簇索引

• 并不是单独的索引类型，而是数据存储方式
• InnoDB  实际上在同一个结构中保存了B-Tree 索引和数据行
• 聚簇：数据行和相邻的键值紧凑的存储在一起
• 一个表只能有一个聚簇索引
• 数据行实际上是存储在叶子页当中

InnoDB 使用的是主键聚集数据

图中被索引的列就是主键列

聚簇索引优点：

1. 相关数据保存在一起
2. 数据访问更快
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引缺点：

1. 最大限度的提高了I/O密集型的性能，但是如果在内存中就无效
2. 插入速度严重依赖插入顺序
3. 更新聚簇索引列代价很高
4. 基于聚簇索引插入新行或者主键被更新导致需要移动行的时候，可能面临“页分裂”
5. 可能导致全表扫描变慢

myisam 数据分布

​ myisam 主键索引其实就是 primary 的 唯一非空索引

innodb 数据分布

• innodb 中聚簇索引“就是“ 表

• 二级索引的叶子节点存储的不是“行指针”，而是指向主键值 -- 关键的一句话

innodb和Myisam 保存数据和索引的区别

覆盖索引

​ 如果一个索引包含了需要查询字段的值，那就是覆盖索引，这是一种查询方式

好处

• 索引条目远远小于数据行，只需要读取索引
• 索引按照列值顺序存储，对于i/p密集的范围查询比随机I/O要快
• 如myisam 中只缓存索引
• 由于inodb的聚簇索引，innodb二级索引存储形式有关

无法覆盖索引的原因

1. 没有任何索引能够覆盖查询
2. mysql 不能在索引中执行 like 操作（底层api 限制）

使用索引扫描来排序

生成有序结果：

1. 排序操作 order by
2. 索引顺序扫描

使用索引扫描条件：

​ order by 字句 引用的第一个字段全部为第一个表的时候，需要满足索引的最左前缀的要求

具体的案例如下：

无法使用覆盖索引的案例

压缩（前缀）索引

​ 主要运用是myisam 使用压缩前缀的方式减少索引的大小，默认只压缩字符串

压缩方式：

每次先保存索引块的第一个值，然后将其他值和第一个值比较得到相同前缀的字节数和剩余不同后缀的部分，吧这部分存储即可

冗余和重复索引

​ mysql 允许相同的列上创建多个索引，而冗余索引

重复索引是指： 相同的列上按照相同的顺序创建相同累心的索引 绝对不能出现重复索引！！！

大多数时间都不需要使用冗余索引，应该尽量扩展已经有的索引而不是创建新的索引

提升性能的最简单办法就是扩展索引，让索引可以覆盖查询

1. 使用common_schema 工具查看视图
2. 使用 pt-duplicate-key-cheker 工具，分析表找出冗余和重复索引。

使用索引的案例：

​ 技巧1： 使用 sex in ('m', 't') 来避开需要频繁使用但是不会被访问的某些列

​ 技巧2： 避免多个范围条件，将范围条件尽可能留到 where 后面

更新索引统计信息

1. records_in_range()： 通过存储引擎传入两个边界或者这个范围大概有多少记录
2. info()： 返回各种类型的数据，包括索引和基数

​ ananlyze table 频率不同引擎不同

1. Memeory 引擎不存储索引统计信息
2. myisam 将索引信心存在磁盘中
3. mysql5.5 版本，innodb也不在次哦按存储索引统计信息

​ show index from 查看索引基数

减少索引和数据碎片

表的数据存储可能碎片化

• 行碎片
  • 数据行被存储在多个地方的多个分片
• 行间碎片
  • 逻辑顺序上的页
• 剩余空间碎片
  • 值数据页中有大量的空余空间

总结

使用索引查询记住以下几点：

<font color='red'>查询性能优化</font>（最最最重要的一节）

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

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

核心：是否向数据库请求了不需要的数据

执行查询的基础

查询状态

​ 使用 SHOW FULL PROCESSLIST 命令

• sleep
• Query
• Locked
• Analyzing and statistics
• copy to tmp table [on disk]
• sorting result
• sending data

mysql 客户端 / 服务器通信协议

​ 使用了半双工的通信协议，即只能一端发送数据另一端响应。

查询优化处理

mysql优化器选择错误的执行计划

• 统计信息不准确
• 执行计划中成本估算不等于实际的成本
• mysql 最优可能和所想不一样
• Mysql 从不考虑其他并发执行的查询
• Mysql 并不是任何时候基于成本的优化
• mysql 不会考虑不受控制的操作的成本
• 有时候可能无法估算所有可能的执行计划

可以执行的优化类型：

• 重新定义表的关联顺序

• 将外连接转为内连接

• 使用等价变化规则

• 优化 count() 、min()和max()

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

• 覆盖索引扫描

• 子查询优化

• 提前终止查询

• 等值传播

• 列表 In() 的比较

• 查询执行引擎

• 返回结果给客户端

mysql 查询优化的局限性

• 关联子查询
• union 的限制
• 索引合并优化
• 等值传递
• 并行执行
• 哈希关联
• 松散索引扫描
• 最大值和最小值

​ 在同一张表上查询和更新

查询优化器的提示

建议直接阅读官方mysql 手册：

• hig_priority和low_priority
  • 多个语句同时访问一个表，那些语句优先级相对高一点
• delayed
  • 对于 Insert 和 replayce 有效
• straight_join
  • 让查询中的所有表按照在语句中出现顺序进行关联
  • 固定前后两个表的关联顺序
• sql_small_result  和 sql_big_result
  • 只对select 语句有效
  • 优化器对于group by 或者distinct查询如何使用两种临时表排序
• sql_buffer_result
  • 优化器将要吧查询结果放入一个临时表
• sql_cache 和 sql_no_cache
  • 查询结果集是否应该存在缓存当中
• sql_calc_found_row5
  • 严格说并不是一个优化器提示。
  • 让Mysql 返回结果集提供更多的信息
• for update 和 lock in share mode
  • 提示主要控制 select 语句的锁机制
  • 只对行级锁引擎有效（其实只有innodb 内置支持）
• use index/ ignor index  / force index
  • 告诉优化器使用或者不使用哪一些索引
• optimizer_search_depth
• optimizer_purne_level
• optimizer_switch

优化特定类型查询

优化count() 查询

​ 忽略所有的列而且直接统计所有的行数

简单优化

​ 使用总数减去不符合条件的数目即为符合条件的数目

使用近似值

​ 近似值匹配也是一种优化技巧

mysql 高级特性

分区表

​ 分区对于 sql层来说是一个完全封装底层实现的黑盒子

​ 目的： 按照一个比较粗的粒度分在不同的表中

下面场景中分区有很大的作用

• 表非常大以至于无法放到内存当中，
• 分区表的数据更容易维护
• 分区表的数据可以分步在不同的物理设备上
• 使用分区来避免某些特殊的瓶颈
• 可以备份和回复

分区本身也有限制

• 一个表最多只能有1024个分区（4M）
• Mysql 5.1 分区表达式必须是整数
• 如果分区字段有主键或者唯一索引的列，那么所有的主键列和唯一索引都必须包含进来
• 分区表无法使用外键约束

分区的原理

SELECT 查询：

​ 分区层先打开并锁住所有底层表

分区表的类型：

视图

mysql 5.0 之后引入视图

mysql 处理视图的办法：

• 合并算法
• 临时表算法

存储过程和函数

限制

• 优化器无法使用关键字deterministic 优化单个查询中多次调用存储函数的情况
• 无法评估存储还书的执行成本
• 每个连接都有独立的存储过程

绑定变量

绑定变量的优化

• 准备阶段
  • 解析sql , 移除不可能条件，重写子查询
• 第一次执行
  • 先嘉华嵌套循环的关联（如果可能），将外关联转为内关联
• 执行sql 语句时候
  • 过滤分区
  • 如果可能，尽量移除count()、min()、max()
  • 移除常数表达式
  • 检测常量表
  • 必要等值传播
  • 分析优化 ref, range 和索引优化等访问数据的方法
  • 优化关联顺序

查询缓存

查询缓存如何使用内存

查询缓存的碎片

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

​ 如何分析和配置查询缓存:

优化服务器设置

mysql 配置工作原理

语法、作用域、动态性

常用变量的修改效果

1. key_buffer_size
   1. 键缓冲区，分配所有指定的空间
   2. mysql 允许创建多个键缓存
2. table_cahce_size
   1. 会延迟到下次有线程打开表才有效果
   2. 如果值大于缓存中表的数量，线程可以把最新打开的表放入缓存
3. thread_cache_size
   1. 不会立即生效，下次有连接被关闭时候产生效果
   2. 检查缓存中是否有空间缓存线程
4. query_cache_size
   1. 修改之后会立即删除所有缓存的查询，重新分配缓存大小
5. read_buffer_size
   1. 只会在有查询需要使用的时候分配缓存
6. read_rnd_buffer_size
   1. 只会在有查询需要使用的时候分配缓存，只会分配需要内存大小而不是全部大小
7. sort_buffer_size
   1. 只会在查询需要做排序操作时候为该缓存分配内存
   2. 会立即分配该参数指定的大小，而不管排序是否需要

操作系统的硬件优化

调优服务器的目标

• 低延时
  • 需要高速cpu
• 高吞吐
  • 同时运行很多的查询，一般可以扩展到16个或者24个

扩展多个cpu和核心

数据库并发问题：

• 逻辑并发问题
  • 可以看到资源的竞争，如表或者行锁的争用
• 内部并发问题
  • 比如信号量，访问innoDB 缓冲池页面的资源争用

平衡内存和磁盘资源

随机I/O和 顺序I/O

顺序读取不能从缓存中受益的原因：

第一点原因：

• 一般只需要扫描一次数据
• 比随机读取快

第二点的原因：

• 顺序io比随机io快
  • 读取速度要更快
  • 访问内存行的速度也更快
• 存储引擎执行顺序顺序读比随机读快

总结：增加内存是解决随机i/o读取最好的办法

缓存 读和写

​ 缓存可以可以延缓写入，但是不能消除读取一样消除写入

​ 事实上除了上面意外，缓存还允许被集中操作

• 多次写入，一次刷新
• I/O合并

工作集是什么

找到有效的内存/磁盘计划

传统磁盘读取数据的过程分为三个步骤

1. 移动读取次哦图到磁盘表面上的正确位置
2. 等待磁盘旋转，等有所需数据在读取磁头下
3. 等待磁盘旋转过去，所有所需数据被读取磁头读出

关键：访问时间和读取速度

选择磁盘的因素：

• 存储容量
• 传输速度
• 访问时间
• 主轴转速
• 物理尺寸

固态存储

• SSD （固态硬盘） 和 PCIE卡

高速闪存设备具备：

• 相比硬盘有更好的随机读写性能
• 相比硬盘有更好的顺序读写性能
• 相比硬盘有更好的支持并发功能

闪存概述

一个设备规格的例子

1. 设备读取性能最高达到 520MB/S
2. 设备写入性能最高达到 480MB/S
3. 持续写入速度可以稳定在420MB/S
4. 每秒可以执行70000 个 4KB 的写操作

固态硬盘驱动器(SSD)

X-25E驱动器

什么时候应该使用闪存

使用 flashcache

数据库下层（至少）有三层

• INNODB 缓冲池
• 缓冲池没有命中，就会去flashcache设备去取
• flashcache设备缓存也没用命中，磁盘上找

优化固态存储上的mysql

改进包括

• 增加innodb 的 i/o容量
• 让innodb 日志文件更大
• 把一些文件从闪存一刀 raid
• 禁用预读
  • 随机预读
  • 线性预读
• 配置innodb 刷新算法
  • 建议闪存设备设置 Innodb_flush_neighbor_pages = 0, 避免 Innodb 尝试查找相邻脏页一起刷写
  • 建议设置 Innodb_adaptive_checkpoint 选项为 keep_average, 不要使用默认的 estimate,保证更持续的性能
• 禁用双写缓存的可能
• 限制插入缓冲大小

RAID 性能优化

等级划分

• RAID 0
  • 简单的评估成本和性能
  • 没有冗余，不担心数据丢失情况下使用
  • 没有提供任何冗余！！！！！！
• RAID 1
  • 多数情况提供很好的读性能
  • 很好的冗余性
• RAID 5
  • 通过分步奇偶校验来吧数据分散到多个磁盘
  • 任意一个盘失效，可以从奇偶校验中重建，但是两个磁盘失效，整个卷无法恢复！！！
  • 用作存放数据或者日志，以读为主的业务
  • 性能最大消耗发生在磁盘失效
• RAID 10
  • 数据存储的好选择
  • 很好在软件层实现
• RAID 50
  • 由条带化的 RAID 5 组成，
  • 用处是存放出非常庞大的数据集

总结

1. RAID 故障转移，迁移和镜像

2. RAID 配置和缓存

SAN 和 NAS

应该使用SAN 吗

• 备份
• 简化容量规划
• 存储整合还是服务器整合
• 高可用
• 服务器的交互
• 成本

使用多磁盘卷

mysql 创建了多类型文件

• 数据和索引文件
• 事务日志文件
• 二进制日志文件
• 常规日志
• 临时文件和临时表

使用ext3

如果使用ext3 或者继承者 ext4, 有是哪个选项控制记录日志，放在 /etc/fstab 作为挂载

• data = writeback
• data = ordered
• data=journal

常见文件系统特性总结

复制--mysql 关键特性

概述

• 基于行的复制
• 基于语句的复制

复制解决的问题

• 数据分布
  • 通常不会给宽带造成很大压力
• 负载均衡
  • 可以将读操作分布到多个服务器上面
• 数据备份
• 高可用和故障切换
  • 避免Mysql 单点失败
• mysql 升级测试

复制如何工作

mysql 如何复制数据

1. 在主库把数据更改记录到二进制日志中（二进制日志事件）
2. 备库将主库的日志复制到自己的中继日志
3. 备库读取中继日志的时间，将其重放到备库数据之上

配置复制

配置步骤：

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

创建复制账号

​ Mysql 会赋予一些特殊权限给复制线程，通过以下语句创建用户账号。

复制账号事实上只需要主库的 replication slave 权限

为什么需要备库也拥有同样的权限呢

1. 监控和管理复制账号需要 replication client 权限
2. 如果在主库上创建了账号，从主库将数据克隆岛备库时候，备库也设置好了。变成主库需要配置。
3. 简单来说：方便主备库角色的切换

配置主库和备库

​ 打开主库二进制日志，指定一个独一无二的服务器id ，在主库的my.cnf 文件当中增加或者修改如下内容

log_bin = mysql-bin
server_id = 10

​ 必须指定一个唯一的服务器id

​ 查看二进制日志文件是否在主库上创建，使用show master status

备库添加类似配置

log_bin = mysql_bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

启动复制

​ 下面是复制开始的基本命令

​ 开始复制：start slave，没有错误使用 show slave status\G检查配置

从另一个服务器开始复制

​ 下面三个条件来让主库和备库保持同步：

• 某个时间点的主库的数据快照
• 主库当前的二进制日志文件，和或者数据快照在该二进制日志文件中的偏移量，我们吧这两个值为日志为日志文件坐标，可以通过show master status 命令来获取这些值
• 从快照到现在的二进制日志

从别的服务器克隆备库的方法

• 使用冷备份

  • 关闭主库，吧数据复制到备库，重启主库之后会有一个新的二进制文件，然后备库执行 change master to  执行这个文件起始处，
  • 缺点：需要关闭主库

• 使用热备份

  • 如果仅仅使用myisam 表， 主库运行时使用mysqlhotcopy 或者 rsync 复制数据

• 使用mysqldump

  • 如果只包含innodb 表， 使用一下命令来转储主库数据并将其加载到备库

  

• 使用快照或者备份

  • 只要知道对应二进制日志坐标，就可以使用主库的快照或者备份初始化备库

• 使用percona Xtrabackup

  • 是一款开源的热备份工具
  • 如果是从主库获得备份，可以从 xtrabackup

• 使用另外的备份

推荐的复制配置

​ 主库二进制日志最重要的选项：sync_binlog = 1，开启之后，每次提交事务之前将二进制日志同步到磁盘上

使用Innodb 推荐的复制配置

复制的原理（重要）

基于语句的复制

​ msyql5.0 之前只支持基于语句的复制

基于行的复制

​ mysql 5.1 之后支持

对比区别：

理论上基于行的复制整体更优，而且实际应用也是用与基于行的复制

• 基于语句复制的优点
  • 主备模式不同时候，逻辑复制可以多种情况工作
  • 基本就是执行sql 语句，出现问题可以很好的定位错误
• 基于语句复制的缺点
  • 如果使用触发器或者存储过程，不要使用基于语句模式复制，会有大量的bug,除非清楚不会碰到问题
• 基于行复制的优点
  • 能处理几乎所有的场景，基本不会出现bug
  • 可以减少锁的使用，因为它并不要求这种强串行化是可以重复的
  • 占用更少的sql
  • 更快找到并解决数据不一致的情况
• 基于行复制的缺点
  • 无法判断执行的sql
  • 出现问题很难定位错误
  • 无法处理在备库修改表的schema这种情况
  • 某些情况下，如找不到修改行时候，可能导致复制停止

复制文件

1. mysql0bin.index ：没有此文件msyql 识别不了二进制日志文件
2. mysql-relay-bin-index ：中继日志的索引文件
3. master.info ：保存备库连接到主库所需要的信息（纯文本格式），不能删除，否则备库重启后无法连接到主库（<font color='red'>保存着密码，需要严格权限控制</font>）
4. relay-log.info ： 包含了当前备库复制二进制日志和中继日志的坐标

发送复制文件到其他备库

复制过滤器

复制拓扑

基本原则：

• 一个mysql 备库实例只能有一个主库
• 每个备库必须要有唯一服务器id
• 一个主库可以有多个备库
• 如果打开了 log_slave_updates 选项， 一个备库可以吧主库的数据变化传播到备库

一主多备库

主动-主动模式下的主-主复制 和 主动-被动模式下的主-主复制

如何配置主-主服务器对，在两台服务器执行对称后，使其拥有相同的配置

• 确保两台服务器数据相同
• 启用二进制日志，选择唯一服务器id, 创建账号
• 启用备库更新日志记录（故障转移和恢复的关键）
• 被动服务器设置成只读，防止可能与主服务器上的更新产生冲突（可选）
• 启动每个服务器mysql 实例
• 将每个主库设置为对方的备库，使用新创建的二进制日志文件进行运行

拥有备库的主- 主结构

增加了冗余，可以消除站点单点失效的问题

环形复制

主库、分发主库以及备库

模拟多主库复制

模拟多主库复制另一种方式

为什么使用日志服务器比用 mysqlbinlog 来实现恢复更好

复制和容量计划

​ 假设工作负载为20%的写和 80%的读， 为了计算简单，假设有如下条件

• 读和写查询包含同样的工作量
• 所有服务器等同，每秒能进行100次查询
• 备库和主库有同样的性能特征
• 所有读操作转移到备库

为什么无法扩展写操作

复制管理和维护

1. 监控复制

2. 测试备库延迟

3. 确认主备是否一致

4. 从主库重新同步到备库

5. 在一个主-主配置中交换角色

数据会损坏或者丢失的错误

• 主库意外关闭
• 备库意外关闭

由于msyql 非常关闭产生的常见情况

• 主库上的二进制日志损坏
• 备库的中级日志损坏
• 二进制日志与Innodb 事务日志不同步

二进制日志损坏时候，能恢复多少数据取决于损坏的类型

• 数据改变，但事件仍然是有效的sql
• 数据改变并且事件是无效的sql
• 数据泄露并且（或者）事件长度是错误的
• 某些事件已经损坏或者被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的

使用非事务性表

非事务性表和混合事务性

不确定的语句

主库和备库使用不同的存储引擎

备库发生数据改变

不是唯一的服务器ID

未定义的服务器Id

对未复制数据的依赖性

不复制所有的更新

innodb 加锁导致锁争用

其他复制技术

SPARK Tungsten : java编写的开源中间件复制产品

他的缺点如下：

可扩展的mysql

高可用性

如何实现高可用性

• 尝试避免导致宕机的原因来减少宕机时间

• 尽量保证发生宕机后能在快速回复

1. 提升平均失效时间
2. 降低平均恢复时间

避免单点失效

系统增加冗余：

• 增加空余容量
• 重复组件

共同存储或者磁盘复制

优点：

• 避免除了存储之外的其他任何组件失效引起数据丢失
• 有助于减少系统一些部分的可用性需求
• （但是SAN 本身失效则全部失效）

总结

​ 这篇笔记主要介绍了索引和复制的相关内容，如果感兴趣建议还是看看原书的内容。

Jersing

感谢楼主分享