共计 8914 个字符,预计需要花费 23 分钟才能阅读完成。
《高性能 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 查看索引基数
缩小索引和数据碎片
表的数据存储可能碎片化
-
行碎片
- 数据行被存储在多个中央的多个分片
-
行间碎片
- 逻辑程序上的页
-
残余空间碎片
- 值数据页中有大量的空余空间
总结
应用索引查问记住以下几点:
<font color=’red’> 查问性能优化 </font>(最最最重要的一节)
慢查问根底:优化数据拜访
- 确认应用程序是否检索了大量超过须要的数据,
- 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
- 键缓冲区,调配所有指定的空间
- mysql 容许创立多个键缓存
-
table_cahce_size
- 会提早到下次有线程关上表才有成果
- 如果值大于缓存中表的数量,线程能够把最新关上的表放入缓存
-
thread_cache_size
- 不会立刻失效,下次有连贯被敞开时候产生成果
- 查看缓存中是否有空间缓存线程
-
query_cache_size
- 批改之后会立刻删除所有缓存的查问,重新分配缓存大小
-
read_buffer_size
- 只会在有查问须要应用的时候调配缓存
-
read_rnd_buffer_size
- 只会在有查问须要应用的时候调配缓存,只会调配须要内存大小而不是全副大小
-
sort_buffer_size
- 只会在查问须要做排序操作时候为该缓存分配内存
- 会立刻调配该参数指定的大小,而不论排序是否须要
操作系统的硬件优化
调优服务器的指标
-
低延时
- 须要高速 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 = writeback
- data = ordered
- data=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 这种状况
- 某些状况下,如找不到批改行时候,可能导致复制进行
复制文件
- mysql0bin.index:没有此文件 msyql 辨认不了二进制日志文件
- mysql-relay-bin-index:中继日志的索引文件
- master.info:保留备库连贯到主库所须要的信息(纯文本格式),不能删除,否则备库重启后无奈连贯到主库(<font color=’red’> 保留着明码,须要严格权限管制 </font>)
- relay-log.info:蕴含了以后备库复制二进制日志和中继日志的坐标
发送复制文件到其余备库
复制过滤器
复制拓扑
根本准则:
- 一个 mysql 备库实例只能有一个主库
- 每个备库必须要有惟一服务器 id
- 一个主库能够有多个备库
- 如果关上了 log_slave_updates 选项,一个备库能够吧主库的数据变动流传到备库
一主多备库
被动 - 被动模式下的主 - 主复制 和 被动 - 被动模式下的主 - 主复制
如何配置主 - 主服务器对,在两台服务器执行对称后,使其领有雷同的配置
- 确保两台服务器数据雷同
- 启用二进制日志,抉择惟一服务器 id, 创立账号
- 启用备库更新日志记录(故障转移和复原的要害)
- 被动服务器设置成只读,避免可能与主服务器上的更新产生抵触(可选)
- 启动每个服务器 mysql 实例
- 将每个主库设置为对方的备库,应用新创建的二进制日志文件进行运行
领有备库的主 - 主构造
减少了冗余,能够打消站点单点生效的问题
环形复制
主库、散发主库以及备库
模仿多主库复制
模仿多主库复制另一种形式
为什么应用日志服务器比用 mysqlbinlog 来实现复原更好
复制和容量打算
假如工作负载为 20% 的写和 80% 的读,为了计算简略,假如有如下条件
- 读和写查问蕴含同样的工作量
- 所有服务器等同,每秒能进行 100 次查问
- 备库和主库有同样的性能特色
- 所有读操作转移到备库
为什么无奈扩大写操作
复制治理和保护
- 监控复制
- 测试备库提早
- 确认主备是否统一
- 从主库从新同步到备库
- 在一个主 - 主配置中替换角色
数据会损坏或者失落的谬误
- 主库意外敞开
- 备库意外敞开
因为 msyql 十分敞开产生的常见状况
- 主库上的二进制日志损坏
- 备库的中级日志损坏
- 二进制日志与 Innodb 事务日志不同步
二进制日志损坏时候,能复原多少数据取决于损坏的类型
- 数据扭转,但事件依然是无效的 sql
- 数据扭转并且事件是有效的 sql
- 数据泄露并且(或者)事件长度是谬误的
- 某些事件曾经损坏或者被笼罩,或者偏移量曾经扭转并且下一个事件的起始偏移量也是谬误的
应用非事务性表
非事务性表和混合事务性
不确定的语句
主库和备库应用不同的存储引擎
备库产生数据扭转
不是惟一的服务器 ID
未定义的服务器 Id
对未复制数据的依赖性
不复制所有的更新
innodb 加锁导致锁争用
其余复制技术
SPARK Tungsten : java 编写的开源中间件复制产品
他的毛病如下:
可扩大的 mysql
高可用性
如何实现高可用性
- 尝试防止导致宕机的起因来缩小宕机工夫
- 尽量保障产生宕机后能在疾速回复
- 晋升均匀生效工夫
- 升高均匀复原工夫
防止单点生效
零碎减少冗余:
- 减少空余容量
- 反复组件
独特存储或者磁盘复制
长处:
- 防止除了存储之外的其余任何组件生效引起数据失落
- 有助于缩小零碎一些局部的可用性需要
- (然而 SAN 自身生效则全副生效)
总结
这篇笔记次要介绍了索引和复制的相干内容,如果感兴趣倡议还是看看原书的内容。