关于mysql:高性能Mysql学习笔记三

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

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

区别：

myisam 应用物理地位保留索引地位，并且对于索引进行了 前缀压缩
innodb 则依照原有数据格式保留数据，并且只有了主键的模式进行索引

外部都是依据存储引擎的实现而有了不同的区别

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

全值匹配
- 和索引当中所有的列进行匹配
匹配最左前缀
- 只用索引的第一列
匹配列前缀
- 匹配某一列值结尾的局部
匹配范畴值：准确匹配某一列并范畴匹配另一列
只拜访索引的查问
- 即只须要拜访索引即可，不须要索引，相似间接走聚簇索引

如果不是从最左侧查找无奈应用索引
不能跳过索引中的列
如果查问中有 某个列的范畴查问，则其左边所有的列都无奈应用优化查问

基于哈希表实现，只有准确匹配索引所有列的查问才无效

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

哈希索引只蕴含哈希值和行指针，不存储字段值
哈希索引数据并不是依照索引顺序存储，* 无奈用于排序
哈希索引不反对局部索引匹配查找，因为哈希索引始终是应用索引列的全部内容来计算哈希值的
哈希只反对等值的比拟查问，不反对范畴查问
拜访哈希数据十分快，哈希抵触的时候须要对于链表进行遍历
哈希抵触高的时候，保护索引操作的代价也很高

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

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

如下：

创立一个伪哈希索引；然而这样会有很高的查问开销
应用上面语句能够对于性能的极大晋升

须要手动保护哈希值，能够应用触发器或者手动保护实现

myisam 表反对，具体内容能够自行搜寻，因为 myisam 引擎不是重点不做介绍

查找文本当中的 关键字

搜寻细节：

停用词
词干
复数
布尔搜寻

Toku 引擎应用的树索引

打打缩小服务器须要扫描的数据量
帮忙服务器防止排序和长期表
将随机 I / O 转变为程序 I /O

前缀索引能够使索引更小，更快的无效方法，然而 mysql 有个缺点 无奈对于前缀索引应用 order by 和 group by, 无奈应用前缀索引做笼罩扫描。

教训法令：

 1. 将选择性最高的列放在索引的最前列（不肯定精确）2. 防止随机的 IO 和排序

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

InnoDB 应用的是主键汇集数据

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

相干数据保留在一起
数据拜访更快
应用笼罩索引扫描的查问能够间接应用页节点中的主键值

最大限度的进步了 I / O 密集型的性能，然而如果在内存中就有效
插入速度重大依赖插入程序
更新聚簇索引列代价很高
基 于聚簇索引插入新行 或者 主键被更新 导致须要挪动行的时候，可能面临“页决裂”
可能导致全表扫描变慢

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

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

如果一个索引蕴含了须要查问字段的值，那就是笼罩索引，这是一种查问形式

索引条目远远小于数据行，只须要读取索引
索引依照列值顺序存储，对于 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 不会思考不受管制的操作的老本
有时候可能无奈估算所有可能的执行打算

从新定义表的关联程序
将外连贯转为内连贯
应用等价变动规定
优化 count()、min()和 max()
预估并转化为常数表达式
笼罩索引扫描
子查问优化
提前终止查问
等值流传
列表 In() 的比拟
查问执行引擎
返回后果给客户端

关联子查问
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

疏忽所有的列而且间接统计所有的行数

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

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

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

目标：依照一个比拟粗的粒度分在不同的表中

表十分大以至于无奈放到内存当中，
分区表的数据更容易保护
分区表的数据能够分步在不同的物理设施上
应用分区来防止某些非凡的瓶颈
能够备份和回复

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

分区层先关上并锁住所有底层表

mysql 5.0 之后引入视图

mysql 解决视图的方法：

合并算法
长期表算法

限度

优化器无奈应用关键字 deterministic 优化单个查问中屡次调用存储函数的状况
无奈评估存储还书的执行老本
每个连贯都有独立的存储过程

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

如何剖析和配置查问缓存:

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 个

数据库并发问题：

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

程序读取不能从缓存中受害的起因：

第一点起因：

个别只须要扫描一次数据
比随机读取快

第二点的起因：

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

总结：减少内存是解决随机 i / o 读取最好的方法

缓存能够能够延缓写入，然而不能打消读取一样打消写入

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

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

挪动读取次哦图到磁盘外表上的正确地位
期待磁盘旋转，等有所需数据在读取磁头下
期待磁盘旋转过来，所有所需数据被读取磁头读出

要害：拜访工夫 和读取速度

存储容量
传输速度
拜访工夫
主轴转速
物理尺寸

SSD（固态硬盘）和 PCIE 卡

相比硬盘有更好的随机读写性能
相比硬盘有更好的程序读写性能
相比硬盘有更好的反对并发性能

设施读取性能最高达到 520MB/S
设施写入性能最高达到 480MB/S
继续写入速度能够稳固在 420MB/S
每秒能够执行 70000 个 4KB 的写操作

X-25E 驱动器

数据库上层（至多）有三层

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

改良包含

减少 innodb 的 i/ o 容量
让 innodb 日志文件更大
把一些文件从闪存一刀 raid
禁用预读
- 随机预读
- 线性预读
配置 innodb 刷新算法
- 倡议闪存设施设置 Innodb_flush_neighbor_pages = 0, 防止 Innodb 尝试查找相邻脏页一起刷写
- 倡议设置 Innodb_adaptive_checkpoint 选项为 keep_average, 不要应用默认的 estimate, 保障更继续的性能
禁用双写缓存的可能
限度插入缓冲大小

RAID 0
- 简略的评估老本和性能
- 没有冗余，不放心数据失落状况下应用
- 没有提供任何冗余！！！！！！
RAID 1
- 少数状况提供很好的读性能
- 很好的冗余性
RAID 5
- 通过分步奇偶校验来吧数据扩散到多个磁盘
- 任意一个盘生效，能够从奇偶校验中重建，然而两个磁盘生效，整个卷无奈复原！！！
- 用作存放数据或者日志，以读为主的业务
- 性能最大耗费产生在磁盘生效
RAID 10
- 数据存储的好抉择
- 很好在软件层实现
RAID 50
- 由条带化的 RAID 5 组成，
- 用途是寄存出十分宏大的数据集

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

备份
简化容量布局
存储整合还是服务器整合
高可用
服务器的交互
老本

mysql 创立了多类型文件

数据和索引文件
事务日志文件
二进制日志文件
惯例日志
临时文件和长期表

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

data = writeback
data = ordered
data=journal

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

数据分布
- 通常不会给宽带造成很大压力
负载平衡
- 能够将读操作散布到多个服务器下面
数据备份
高可用和故障切换
- 防止 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，开启之后，每次提交事务之前将二进制日志同步到磁盘上

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 实例
将每个主库设置为对方的备库，应用新创建的二进制日志文件进行运行

减少了冗余，能够打消站点单点生效的问题

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

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

监控复制
测试备库提早

确认主备是否统一
从主库从新同步到备库
在一个主 - 主配置中替换角色

数据会损坏或者失落的谬误

主库意外敞开
备库意外敞开

因为 msyql 十分敞开产生的常见状况

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

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

数据扭转，但事件依然是无效的 sql
数据扭转并且事件是有效的 sql
数据泄露并且（或者）事件长度是谬误的
某些事件曾经损坏或者被笼罩，或者偏移量曾经扭转并且下一个事件的起始偏移量也是谬误的

应用非事务性表

非事务性表和混合事务性

不确定的语句

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

备库产生数据扭转

不是惟一的服务器 ID

未定义的服务器 Id

对未复制数据的依赖性

不复制所有的更新

innodb 加锁导致锁争用

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

他的毛病如下：

尝试防止导致宕机的起因来缩小宕机工夫
尽量保障产生宕机后能在疾速回复

晋升均匀生效工夫
升高均匀复原工夫

零碎减少冗余：

减少空余容量
反复组件

长处：

防止除了存储之外的其余任何组件生效引起数据失落
有助于缩小零碎一些局部的可用性需要
（然而 SAN 自身生效则全副生效）

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

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

前言

索引

B-Tree 索引

示例

1. 首先创立一张表

2. 外部存储构造

上面的查问类型无效

B-Tree 索引的限度：

哈希索引

限度

InnoDB 引擎的自适应哈希索引

创立自定义哈希索引

毛病：

空间索引（R-Tree）

全文索引

其余索引

索引的长处

高性能索引策略

独立的列

前缀索引和索引的选择性

多列索引

抉择适合的索引列程序

聚簇索引

聚簇索引长处：

聚簇索引毛病：

myisam 数据分布

innodb 数据分布

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

笼罩索引

益处

无奈笼罩索引的起因

应用索引扫描来排序

无奈应用笼罩索引的案例

压缩（前缀）索引

冗余和反复索引

应用索引的案例：

更新索引统计信息

缩小索引和数据碎片

总结

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

慢查问根底：优化数据拜访

执行查问的根底

查问状态

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

查问优化解决

能够执行的优化类型：

mysql 查问优化的局限性

查问优化器的提醒

优化特定类型查问

优化 count() 查问

简略优化

应用近似值

mysql 高级个性

分区表

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

分区自身也有限度

分区的原理

SELECT 查问：

分区表的类型：

视图

存储过程和函数

绑定变量

绑定变量的优化

查问缓存

查问缓存如何应用内存

查问缓存的碎片

什么状况下查问缓存能发挥作用

优化服务器设置

mysql 配置工作原理

语法、作用域、动态性

罕用变量的批改成果

操作系统的硬件优化

调优服务器的指标

扩大多个 cpu 和外围

均衡内存和磁盘资源

随机 I / O 和 程序 I /O

缓存 读和写

工作集是什么

找到无效的内存 / 磁盘打算

随机 I / O 和程序 I /O

缓存读和写

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