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

《高性能Mysql》学习笔记（三）前言 ? 接续上文继续介绍：《高性能Mysql》学习笔记（二）。
索引 B-Tree 索引 ? 即没有特别指明的类型，大多数时候mysql 引擎都支持这种索引（Archive 是例外， 5.1 之前不支持，之后支持单个自增列的索引）
? 区别：

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

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

文章图片

示例
1. 首先创建一张表

文章图片

2. 内部存储结构

文章图片

? 索引对于多个值进行排序的根据是create table 当中定义索引时候的顺序，看一下最后两个条目
下面的查询类型有效

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

B-Tree 索引的限制：

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

哈希索引 ? 基于哈希表实现，只有精确匹配索引所有列的查询才有效
? mysql中只有 Memory 引擎支持哈希索引，这样说Memory 表默认的索引类型
限制

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

InnoDB 引擎的自适应哈希索引当某个索引值频繁使用的时候，会在内存中基于B-Tree 索引创建一个哈希索引
创建自定义哈希索引在B-Tree 上创建一个伪哈希索引
如下：

创建一个伪哈希索引；但是这样会有很高的查询开销
文章图片
使用下面语句可以对于性能的极大提升

文章图片

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

文章图片

空间索引（R-Tree） ? myisam 表支持，具体内容可以自行搜索，由于myisam引擎不是重点不做介绍
全文索引 ? 查找文本当中的关键字
? 搜索细节：

停用词
词干
复数
布尔搜索

其他索引 ? Toku 引擎使用的树索引
索引的优点

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

高性能索引策略独立的列
前缀索引和索引的选择性 ? 前缀索引可以使索引更小，更快的有效办法，但是mysql 有个缺陷无法对于前缀索引使用order by 和 group by,无法使用前缀索引做覆盖扫描。
多列索引
选择合适的索引列顺序
经验法则：

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

文章图片

聚簇索引

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

文章图片

InnoDB 使用的是主键聚集数据
图中被索引的列就是主键列

聚簇索引优点：

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

聚簇索引缺点：

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

myisam 数据分布

文章图片

文章图片

? myisam 主键索引其实就是 primary 的唯一非空索引
innodb 数据分布

文章图片

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

文章图片

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

文章图片

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

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

无法覆盖索引的原因

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

使用索引扫描来排序生成有序结果：

排序操作 order by
索引顺序扫描

使用索引扫描条件：
? order by 字句引用的第一个字段全部为第一个表的时候，需要满足索引的最左前缀的要求
具体的案例如下：

文章图片

无法使用覆盖索引的案例

文章图片

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

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

冗余和重复索引 ? mysql 允许相同的列上创建多个索引，而冗余索引
重复索引是指：相同的列上按照相同的顺序创建相同累心的索引绝对不能出现重复索引！！！
大多数时间都不需要使用冗余索引，应该尽量扩展已经有的索引而不是创建新的索引
提升性能的最简单办法就是扩展索引，让索引可以覆盖查询

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

使用索引的案例： ? 技巧1：使用 sex in ('m', 't') 来避开需要频繁使用但是不会被访问的某些列
? 技巧2：避免多个范围条件，将范围条件尽可能留到 where 后面
更新索引统计信息

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

? ananlyze table 频率不同引擎不同

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

? show index from 查看索引基数
减少索引和数据碎片表的数据存储可能碎片化

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

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

文章图片

查询性能优化（最最最重要的一节）慢查询基础：优化数据访问

确认应用程序是否检索了大量超过需要的数据，
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 配置工作原理

文章图片

语法、作用域、动态性常用变量的修改效果

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

操作系统的硬件优化调优服务器的目标

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

扩展多个cpu和核心数据库并发问题：

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

平衡内存和磁盘资源随机I/O和顺序I/O
顺序读取不能从缓存中受益的原因：
第一点原因：

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

第二点的原因：

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

总结：增加内存是解决随机i/o读取最好的办法
缓存读和写
? 缓存可以可以延缓写入，但是不能消除读取一样消除写入
? 事实上除了上面意外，缓存还允许被集中操作

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

工作集是什么找到有效的内存/磁盘计划传统磁盘读取数据的过程分为三个步骤

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

关键：访问时间和读取速度
选择磁盘的因素：

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

固态存储

SSD （固态硬盘）和 PCIE卡

高速闪存设备具备：

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

闪存概述一个设备规格的例子

设备读取性能最高达到 520MB/S
设备写入性能最高达到 480MB/S
持续写入速度可以稳定在420MB/S
每秒可以执行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 组成，
- 用处是存放出非常庞大的数据集

总结

文章图片

RAID 故障转移，迁移和镜像
RAID 配置和缓存

SAN 和 NAS 应该使用SAN 吗

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

使用多磁盘卷 mysql 创建了多类型文件

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

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

data = https://www.it610.com/article/writeback
data = https://www.it610.com/article/ordered
data=https://www.it610.com/article/journal

常见文件系统特性总结

文章图片

复制--mysql 关键特性概述

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

复制解决的问题

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

复制如何工作 mysql 如何复制数据

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

文章图片

配置复制配置步骤：

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

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

文章图片

复制账号事实上只需要主库的 replication slave 权限
为什么需要备库也拥有同样的权限呢

监控和管理复制账号需要 replication client 权限

如果在主库上创建了账号，从主库将数据克隆岛备库时候，备库也设置好了。变成主库需要配置。

简单来说：方便主备库角色的切换

配置主库和备库
? 打开主库二进制日志，指定一个独一无二的服务器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这种情况
- 某些情况下，如找不到修改行时候，可能导致复制停止