mysql学习笔记

 　

第一章 mysql架构与历史

mysql 存储引擎架构 （程序处理和存储分离）

     程序处理：

           1.query processing 查询处理

            2. server task 服务处理

      存储 ：

          1.数据存储

          2.数据提取

    

第一层 客户端 ：认证-->授权-->连接-->每个连接开单个线程

 

workbench 

https://www.mysql.com/products/workbench/

 

navicat 

https://www.navicat.com/en/products/navicat-for-mysql

 

 

第二层

           1.查询，优化，分析，缓存 ：解析树-->优化-->重写Sql,表读取顺序,选择索引(hint 影响优化，explain 查看执行效率)

           2. 内置函数 

           3.其他扩存储引擎的功能 ：存储过程，触发器，视图

第三层 

       存储引擎 ：数据的存储和提取-->存储引擎提供的API 

 

并发控制              

  锁

       1.共享锁(读锁)

        2.排它锁(写锁) 

锁粒度

    1.table lock 表级锁

    2.row lock 行级锁

事务

ACID 

    atom 原子性

    consistency 一致性

   isolation  隔离性

  durability 持久性

隔离级别

 read_uncommited 读未提交 脏读

read_comminted 读已提交

repeatable read 可重复读  :会产生幻读，innoDb 通过MVCC解决幻读

serializable 可串行化

 死锁

1.两个或两个以上事务请求同一资源，并请求锁定对方资源，导致恶性循环现象

  比如 A,B两个事务 ，A需要B的资源 B需要A的资源  ，A等待B释放锁，B等待A释放锁 。无线等待

解决死锁： 检查死锁循环依赖 抛错

                  等待超时 放弃锁请求

                 事务中 持有最小行级排它锁 的事务进行回滚(找一个锁最少的 回滚)

事务日志

   1.预写入式

mysql 事务 默认自动提交

事务是存储引擎实现的 

InnoDB 会根据隔离级别 自动加锁

锁 只在 commit 和 rollback的时候释放

多版本并发控制

不加锁 ，做类似数据库中的标识字段处理(系统版本号)

mvcc -->行级锁的变种-->避免加锁处理 -->快照-->隐藏记录，保存行数据版本号 

mvcc  在 repeatable read 和  read  commited隔离级别下使用

 MySql存储引擎

查看表的存储引擎

show table status like 'user'

InnoDB是mysql默认的事务存储引擎 

InnoDB  默认repeatable read 隔离级别，通过next-key locking(间隙锁) 策略，防止幻读

间隙锁：对索引中的间隙进行锁定，防止幻影行的插入 

InnoDB基于聚簇索引建立的，一个表只能有一个聚簇索引（聚簇索引的 叶节点 就是数据存储的节点），非主键索引必须包含主键列

myISAM存储引擎

nyISAM 不支持事务和行级锁  崩溃无法恢复，表级锁 支持全文索引，使用 myisampack压缩表 （pack 打包） 压缩的表示不允许修改的 除非解压   

myISAM设计简单  数据以紧密格式存储 某些场景下性能会很好

其他存储引擎：Archive  只支持 insert  select 操作 适合日志 和统计 

                         CSV 存储引擎

存储引擎选择

   1.订单处理 支持事务的引擎必选

   

转换表的存储引擎 ：

alter table user ENGINE=INNODB

　　

 第二章 mysql 基准测试

 1.为什么要进行基准测试 (硬件测试和软件测试)

   --软件：

     验证系统假设

    重现系统异常

    测试当前运行情况

    模拟比当前系统更高负载

    规划未来业务增长

 --硬件

    测试适应可变环境的能力

    测试不同的硬件、软件和操作系统配置

     证明新采购设配 配置是否正确

 

基准测试策略

   ·1.full-stack : 集成式  测试整系统

    2.single-component : 单组件式  单独测试MySQL

测试指标

   1.吞吐量

    2.响应时间或延迟

    3.并发性

    4.可扩展性

 基准测试方法

 绘图

集成测试工具

单组件测试工具

mysql基准测试套件 

第三章 服务性能剖析

  1. 通过性能剖析 进行优化

    值得优化的查询

   异常情况(执行频率低)

   未知

 mysql企业监控查询分析功能

   mysql企业监控器

剖析mysql查询

剖析服务器负载

捕获查询日志

分析查询日志

 

查看查询响应时间　　

show profiles

查看单个查询 各步骤响应详情

show profile for query 4

　

预估风险

show status;
show global status;

　　

　

 

 查看线程连接

show processlist;

 

　性能下降 可能原因

   1.资源过度使用

   2.资源没有正确配置

   2.资源已损坏或失灵　

第四章　schema与数据类型优化　

 字段数据类型选择

  1.最小的数据类型更好

  2.简单最好 ：简单数据类型 通常占用cpu比较小， 整数类型比字符串操作代价低，比如：用MySQL自带日期格式存储，而不是字符串存储日期 

  3.避免null

 

整数类型 （）

   tinyint  8位

  smallint 16位

  mediumint 24 位

  int 32位

 bigint 64位

 

实数类型 （带小数）

 float

double

decimal

字符串类型

 varchar 可变长度

char  定长

varchar(5) 和varchar(200)  存储 'hello'空间开销是一样的 小的列 主要消耗的处理内存少

 

blob 和text

Blob 二进制存储

tinyblob smallblob blob mediumblob longblob

text 字符存储（有字符集和排序规则）

tinytext smalltext text mediumtext longtext

 

mysql 会把 blob 和text当做独立对象 单独处理

当blob和text值太大时 ，innoDB会用外部的存储区域来存储值 并建立指针(1-4字节) 指向实际值

blob和text全部长度字符 不能索引

 

enum 枚举类型

枚举类型有一定的开销  枚举类型 和 varchar或char 连接查询可能会慢

 

日期和时间类型

DateTime  不分时区

范围 1001 - 9999 精度为秒，它把日期和时间 分装成YYMMDDHHMMSS 格式的整数中 ，8字节存储空间

TimeStamp 有时区差异

保存了  1970.01.01 午夜以来的秒数 和Unix时间戳相同 只能表示到 2038年  4个字节存储空间

mysql  提供 from_unixtime() 将unix时间转化成日期 

                   unix_time()将日期转化成unix时间戳

 

如果要存储更小级别的时间格式 怎么办

使用 bigint 存储微妙级别的时间戳或者使用 double秒之后 用小数存储

位数据类型

bit

5.0之前 和 tinyint同义  之后版本有变化

set 

若要保存很多列 可以将其和并到一个set 数据类型 函数支持：find_in_set() ,field()

缺点 ：改变列的定 使用 alter 代价大 ，不能使用索引

整数列上按位操作

 

选择表示符

标识列 identifier column

一般 整数类型  然后 自增 auto_increment

字符串做标识列 消耗性能

特殊类型数据

 ip 实际是无符号整数  ，小数点 分成4段只是为了更好阅读 

MySQL提供  函数转换 ： inet_aton 和 inet_ntoa

-- address to number

SELECT INET_ATON('192.168.1.1');

 

-- number to address

SELECT INET_NTOA(3232235777);

Mysql schema 设计陷阱 

1.应避免太多列

2.应避免太多关联

3.避免过度使用枚举类型

4.避免使用null

 

范式和反范式

 

范式的优缺点

优点

      1.范式的更新操作通常比反范式快

      2.减少数据重复

      3.范式话的表通常最小 操作快

      4.减少使用 distinct

缺点

    1.范式通常需要关联 

    2.范式可能需要拆成多个表

    3.可能是单表索引策略无效

 

反范式优缺点

 

优点

    1.表关联较少

    2.单表索引策略有效使用

 

混用范式和反范式

 

汇总表和缓存表

 

加快Alter table的速度

 

1.影子copy : 不提供服务的数据库上进行 alter操作 ，切换数据库

有些工具可以帮助我们完成影子 copy:https://launchpad.net/mysqlatfacebook

2.修改字段的默认值 有较快的方法 ：修改 .frm文件

语句为  alter column

（用 modify column效率很低 会进行重建表）

 

只修改 .frm文件

 ps：mysql创建表对应目录会创建 .frm文件，用来保存数据表的元信息 数据结构定义等，和存储引擎无关 数据表必有 命名 tableName.frm 如：user.frm

有肯能不需要重建表的操作

1.删除 auto_increment

2.增加、移除或更改 emum ，set 常量值

实现方法 :

 1.创建相同结构空表，修改上述常量

  2.锁表 ：flush tables with read lock

  3.交换 .frm文件

  3.释放锁：unlock tables

 

高效的载入数据 可以先禁用索引 载入完成 在开启索引

 

第五章 创建高性能索引

 

简单理解索引 ：索引到对应值-->找到对应行

 

索引类型　　

 

索引在存储引擎层实现 没有统一标准

B-tree索引 所有的值都是按顺序存储

 

 

 B-tree索引加快访问速度

 原因：不需要全表扫描-->索引根节点 指针 指向-->下一节点-->节点中的值和要查询的值比较-->找到合适的节点进入下层

  key1<=值<key2

 叶子节点指向被索引数据

B-tree索引限制：不是按照索引最左端查找  不能使用索引  

 

哈希索引

索引列计算哈希码 

Memery引擎支持 哈希索引

哈希索引无法进行排序

不支持部分索引列 索引 如 （A,B）列 建立哈希索引 ，查询 只使用A列 无法使用索引

哈希索引只支持等值比较  不支持 范围查询 意思是 where price>100用不到索引

有哈希冲突时 访问哈希索引速度会变慢

哈希冲突较多 维护哈希索引代价较高

 

InnoDB有自适应哈希：当某些索引值使用频繁 InnoDB会在内存中基于B-tree索引创建一个哈希索引

伪哈希索引 例子

数据库中有 url字段  该字段B-tree索引性能较低 

可以去除 url 现有索引  新加一列url_crc  其值为 url对应的 哈希值  CRC32('url-value');

用该列进行数据索引  查询性能提高 

select * from clicks where url_crc=crc32('http://yahoo.com');

　--缺陷  url_crc列需要维护

全文索引

 全文索引  查找文本中的关键词 

 

索引的优点

三大优点：

              1.减少数据扫描数据量

               2.索引避免排序和临时表

               3.索引可以将随机I/O变为顺序I/O

高性能的索引策略

 1.独立的列

 2.前缀索引和 索引选择性

       索引选择性：不重复的索引值 也叫基数   索引选择性越高 查询效率越高

  3.多列索引

  4.选择合适的索引列顺序        

  5.聚簇索引  是一种存储方式

        叶子页存放 数据行

        

 

 

      聚簇索引优点：

            1.相关数据放在一起

             2.  访问速度快

             3.覆盖索引的扫描 可以直接使用叶子节点中的主键

      缺点：

             1.插入速度严重依赖插入顺序

              2.聚簇索引更新代价高

              3.插入新行或者更新主键 肯能导致页分裂     

              4.可能导致全表扫描变慢

               5.二级索引肯能更大  二级索引包含主键列

               6.二级索引查找需要两次索引

 

  

 

 innoDB按组件顺序插入行

 

uuID 主键插入 消耗内存  且索引占用空间大

 

顺序主键 并发插入可能导致间隙锁竞争 

 

使用索引扫描来排序

 

explain 的type  为 index 说明使用索引扫描来排序 

 

前缀压缩索引

 

myISAM 压缩索引块的方法 

            1.保留索引块的第一个值

            2.其他值和第一值进行比较

            如：第一值 ‘perform’ 第二值 ‘performance’ ,前缀压缩 ：'7,ance'

 

冗余和重复索引

重复索引 :相同列按相同顺序创建 同种类型的索引

可以查询 infomation_schema  来查看重复索引

也可以 使用第三方工具 common_schema 来定位  

common_schema 安装到服务器上 视图来定位

https://code.google.com/archive/p/common-schema/

它比自己查询快

select * from information_schema.statistics where  
TABLE_SCHEMA not in ('mysql','performance_schema','information_schema','common_schema') and table_schema='sunny' and table_name='user';

　　

未使用的索引

 

 

 索引和锁

 

索引可以使查询锁定更少的行 

 InnoDB只有访问行的时候才会加锁，索引能给减少InnoDB访问的行数。

 

范围要放在索引的最后

MySQL使用某个索引进行范围查询 就无法在使用另一个索引 进行排序了。

 

支持多种过滤条件

 

设计索引时不要只为现有的查询考虑索引，还要考虑对查询进行优化

如我们在country和sex上创建索引 。sex列的选择性显然很低 ，可以我们在 查询条件中添加  sex in('m','f','o') 来让 查询使用该索引 

这样做 既不会减少过滤行，也不影响返回结果 但是提高了查询速度。

但是如果 sex有太多的值 in()列表太长 这样做就不行了  

 

避免多个范围条件

 

修复损坏表

检查表是否损坏

check table user

修复表

repair table user

不是所有的引擎都支持 repair 

InnoDB就不支持 可以使用第三方工具

https://launchpad.net/percona-data-recovery-tool-for-innodb　　

 

查看索引基数　

show index from user

 

 

cardinality：基数

    索引列的基数 ：存储引擎估算索引列有多少不同取值

 

减少索引和数据碎片

 

B-tree索引可能会碎片化 索引会无序 降低查询效率

碎片化类型：

     1. 行碎片化

           数据行被存储为多个多个地方的片段

     2.行间碎片

     3.剩余空间碎片

        数据页中大量的剩余空间，导致服务器读取大量不需要的数据

使用 optimize命令优化

optimaize table user

InnoDB不支持 该命令

使用 alter命令重建表  将表存储引擎改为当前存储引擎

alter table user ENGINE=INNODB

　　

第六章 查询性能优化

 

1.查询所需的列或行 不要全部返回

2.去除不需要的关联

3.切分查询

4.避免查询刚更新的数据

5.where 中进行运算  取值和运算发生在同一阶段 效率高

 

mysql的查询优化处理

 

查询优化器

       1.重定义表的关联顺序

       2.将外连接转化成内连接

       3.使用等价变化规则

       4.优化count() ,min(),max()

       5.预估并转化为常数表达式

       6.覆盖索引扫描

       7.子查询优化

       8.提前终止查询

       9.等值传播

       10.列表 in()的比较

 

 第七章 MySQL高级特性

 

分区表

 分区表 是个独立的逻辑表，但底层由多个物理表组成。

通过对底层表句柄对象的封装 实现分区

请求分区表-->句柄对象-->调用存储引擎的接口

mysql实现分区表的方式-->对底层表封装-->索引按照分区表的子表定义 没有全局索引

使用 partition by 定义分区存放的数据

allert table exchange partition

分区表将相关数据放在一起   

1.分区表的作用

1. 表非常大以至于无法全部放在内存
2. 分区表的数据更容易维护
3. 分区表的数据可以分布在不同的物理设备上
4. 用分区表来避免特殊瓶颈 
5. 分区可以独立备份和恢复

 

2. 分区表的限制

 

1. 一个表最多有1024个分区
2. 分区表达式必须是整数
3. 如果分区字段中有主键或者唯一索引，主键列和唯一索引必须包含进来
4. 分区表无法使用外键约束

 

3.分区表的原理

 

分区表由多个相关底层表实现，底层表由句柄对象表示。

分区表操作逻辑：

select查询

        分区层 -->打开并锁住所有  底层表-->优化器 过滤部分分区-->调用存储引擎接口 访问各分区

insert操作

       分区层-->打开并锁住所有 底层表-->确定接收数据分区-->向底层表写入数据

delete操作

     分区层-->打开并锁住所有表-->确定分区-->删除底层表数据

update操作

    分区层-->打开并锁住所有表-->确定分区-->取出数据并更新-->确定updated数据存放分区-->底层表写入-->删除原数据

 

4.分区表的类型

1. 根据范围进行分区
2. 根据键值进行分区
3. 使用数学模函数进行分区

 

5.使用分区

 

要保证大数据量的可扩展性 一般有下面两个策略

1. 全量扫描数据 不需要任何索引
2. 索引数据并分离热点

 

6.可能会遇到的问题

 

1. null值会使分区过滤无效
2. 分区列和索引列不匹配 （无法进行分区过滤）
3. 选择分区的成本可能很高
4. 打开并锁住所有底层表成本很高
5. 维护分区的成本很高

 

7.查询优化

 

分区最大的优点 ：优化器可以根据分区函数过滤一些分区 

where 条件中带入分区列 

 

视图

视图是个虚拟表 不存放任何数据 

不能对视图创建触发器，不能用 drop table 删除视图

 

1.可更新视图

   updatable view 通过视图来更新视图涉及的相关表，删除甚至向视图写入数据

 

  限制条件

1.   如果视图中包含 group by , union ,聚合函数 等其他特殊情况 就不能更新了  
2.   更新中有多表关联语句  更新列 必须在同一个视图中
3.   临时表算法实现的视图无法被更新

 

 2.视图对性能的yingx

3.视图的限制

   mysql不会保存 视图定义的原始sql语句

   只能从 .frm文件中获取

 

外键约束

 

innoDB 是 mysql中唯一支持外键的存储引擎，使用外键有成本 修改主表示 相应关联表 要做查询操作

 

 

mysql内部存储代码

1.  触发器
2. 存储过程
3. 函数

优点：

1. 服务内部执行，节省带宽和网络延迟
2. 代码重用，简化应用代码维护
3. 提升安全
4. 服务器端可以缓存 执行计划 若反复调用降低消耗
5. 应用开发和数据库开发分工

 

缺点：

1. mysql未提供好用的开发和测试工具
2. 存储代码使用的函数有限
3. 存储代码可能会带来部署的复杂性
4. 给数据服务器带来太大压力
5. 一个存储过程错误 所有应用都无法访问
6. 日志不怎么好用

 

1.存储过程和函数

 

2.触发器

定义触发器 注意点

1. 每个表每一事件 最多定义一个触发器
2. mysql只支持行的触发

劣势

1.  触发器 掩盖服务器背后的工作 ，可能很多看不见的工作被触发器执行
2.  触发器问题很难排查
3. 触发器可能导致死锁和等待

3.事件

  

4.在存储过程中保存日志

5.游标

 MySQL在服务器端提供单向 只读游标

6.绑定变量

7.绑定变量优化

 每次执行时服务器 优化策略：

1. 过滤分区
2. 尽量移除 count() ,min().max()
3. 移除常数表达式
4. 检测常量表
5. 做必要的等值传播
6. 分析和优化 ref ,range 索引优化访问数据
7. 优化管理顺序

8.插件

 存储过程插件，后台插件，全文解析插件 ，审计插件，认证插件

9.字符集

 二进制编码到某类字符的映射

使用 collate 来指定字符串的字符集

select _utf8 'hello' collate utf8_bin;

　　

解决字符集问题 

1.alter table将对应列转成兼容的字符集

2.还可以使用编码前缀和 collate子句将列转换成兼容的编码

 字符集校对规则消耗内存

10.全文索引

  全文索引支持各种字符内容的搜索，也支持自然语言的搜索 

  myISAM全文索引也是一种特殊的B-tree索引 分为两层

1. 关键字
2. 文档指针

     

11.自然语言全文索引

关键字整个索引中出现的次数越少 匹配相关度越高

12. 布尔全文索引

myISAM引擎才能使用布尔全文索引

13.全文索引的限制

1. insert,update,delete全文索引的代价很大：
2. 修改文本100个单词 需要100次索引操作
3. 列的长度影响全文索引的性能
4. 全文索引会有更多的碎片 可能需要更多的 optimize table
5. 全文索引影响优化器的工作

 

 

 

使用全文索引时 返回主键值 当程序真的需要数据时 再查使用其他索引

 

14.全文索引的配置和优化

保证索引缓存足够大

保证索引能够缓存在内存中 

为全文索引设置单独的键缓存(key cache)

建立停用词表

忽略太短的单词

15.分布式事务

16.内部XA事务

   跨存储引擎的事务

17.外部事务

18.查询缓存

19.如何判断缓存命中

查询有一些不确定数据时，则不会被缓存 如：now(),current_date()

查询中包含任何用户自定义的函数、用户变量、临时表 或者包含列权限级别的表 都不会被缓存

缓存也会带来一定开销：

1. 缓存检查
2. 缓存数据添加
3. 数据插入时缓存失效

20.查询缓存对内存的使用

查询缓存完全存储在内存中的

查询缓存的内存 分成变长的数据块

数据块自身有类型 大小和数据本身

数据块类型有：存储 ->  查询结果、查询、数据表映射、查询文本等 

21.查询 缓存配置

query_cache_type

query_cache_size

query_cache_min_res_unit

query_cahce_limit

query_cache_wlock_invalidate 

 

 

查询缓存空间太小 查询缓存使用率会很低

 

22. InnoDB缓存机制

 

innoDB有mvccjizhi  所以 使用缓存更复杂 

 

当前事务ID-->内存中数据字典事务ID号(计数器)比较-->大于计数器的ID号才可以使用 缓存 -->如果表上有任何锁 不可以使用缓存-->事务提交-->当前系统事务ID 修改该表计数器

 

23.查询缓存优化

1. 多个小表代替一个大表对缓存有帮助
2. 批量写入只需要做一次缓存失效，效率高于单条写入
3. 因缓存空间太大 所以要控制缓存大小 防止过期操作服务器僵死
4. 可以通过 sql_cahe 和sql_no_cahe来控制查询是否需要缓存
5. 对于密集型的应用 关闭缓存可以提高系统性能
6. 因为互斥信号量的竞争 关闭查询缓存 对读密集型的应用也会有好处 可以测试打开和管查询缓存性能差异

第八章 优化服务器设置 

 

1.mysql配置的工作原理

 

配置文件一般在 /etc/my.cnf  或者 /etc/mysql/my.cnf 

 

2.mysql 基础配置文件

2.配置缓冲池  和 日志文件  因为默认太小了

 

缓冲池 流行的经验是设置为 服务器内存的 75% - 80% 

更好的办法：

( 内存总量 - 系统内存占用  -  mysql自身需要内存 -  操作系统缓存innoDB日志文件所需内存 - 其他配置缓冲或缓存所需内存 如 key cache ,query cache )

/ 1.05(InnoDB 缓冲池自身开销)

结果 四合五入

 

3.配置内存使用

内存消耗

可以控制的内存：可以通过参数配置的内存

不可以控制的内存：mysql服务运行、解析查询、内部管理消耗等

 

配置内存步骤

1. 确定内存使用上限
2. 确定mysql每个连接所需内存 ：如 排序缓冲和临时表
3. 确定操作系统需要多少内存才够用
4. 把剩下的内存全部给mysql缓存

重要的缓存

1. InnoDB缓冲池
2. InnoDB日志文件和myISAM数据系统缓存
3. MyisAM键缓存
4. 无法手工配置的缓存 如二进制日志 表定义文件的操作系统缓存

 

innoDB缓冲池 

缓存索引 缓存行数据 自适应hash ,insert buffer ,锁 ，以及其他内部数据结构 ，缓冲数据 延迟写入 实现合并写入。

缓冲池太大 预热和关闭 消耗时间  关闭之前需要把脏页写回数据库

查看脏页数 Innodb_buffer_pool_pages_dirty 

show global status;

 innodb_max_dirty_pages_pct 刷新 脏页数 阀值

show variables

 

key cache

键缓存  只缓存索引

 不超过索引总大小

或者 不超过操作系统缓存总内存的 25% - 50%

 

缓冲区的使用率 公式

 

100-（（key_block_unuser * key_cache_block_size）*100/key_buffer_size）;

 

show global status where variable_name in('Key_blocks_unused','Key_cache_block_size','Key_buffer_size');
show variables where variable_name in('Key_blocks_unused','Key_cache_block_size','Key_buffer_size');

 100 -（6582 * 1024）*100/16777216=59.82666

 

key block size

mysql键缓存块的大小

 

InnoDB事务日志 

innoDB使用日志减少事务提交开销 -->InnoDB把随机I/O变成顺序I/O--> 日志写入磁盘 -->事务持久化

                                                                                                          -->日志恢复已提交的事务

 

日志文件大小 

innoDb_log_file_size, innoDb_log_files_in_group 

show variables where variable_name like'Innodb_log%'

size=2*167772160

 

日志缓冲区 innodb_buffer_size 推荐大小为  1M - 8M

show VARIABLES where variable_name like'innodb_log%'

16777216  这是默认的大小 我们来算一下多少M 

16777216 B/1024/1024=16M

InnoDB表空间

InnoDB把数据保存在表空间内，本质上是由一个或多个磁盘文件组成的虚拟文件系统。

InnoD 

1. 存储表 索引
2. 保存回滚日志
3. 插入缓冲 
4. 双写缓冲

　innoDb_data_file_path 配置表空间文件

   这些文件存放在 innodb_data_home_dir 指定的目录

show VARIABLES where variable_name like'innodb_data%';

autoextend：超过分配空间还能增长

max:设置上限 如：ibdata1:12M:autoextend:max:2G

 

innodb_file_per_table 设置innoDb每个表使用一个 表空间文件

show VARIABLES where variable_name like'innodb_file%';

innodb_file_per_table  on

优点：易于管理

缺点：删 除表慢  可以先将.ibd指向  0字节文件  然后手动删除表文件

删除表 需要扫描缓冲池 找到属于表空间的页面

　　

InnDb双写缓冲  DoubleWrite buffer

 

双写缓冲避免页没写完整 导致的数据损坏

innodb_doublewrite

 

配置mysql并发

innodb_thread_concurrency 控制线程并发数  0为不限制

show VARIABLES where variable_name like'innodb_thread%';

理论上的配置

并发值=cpu数量 * 磁盘数量 * 2;

实践中 小于理论中  且需要验证

innodb_thread_sleep_delay 微妙  线程休眠时间  当 并发值 >=设置值 线程delay 然后重试  如果还不能进入内核 则会进入等待队列

innodb_concurrency_tickets  线程进入内核 会有一定量 ticket供其免费返回内核 不需要并发检测

show VARIABLES where variable_name like'innodb_concurrency%';

innodb_commit_concurrency  并发提交量 0 不限制

　　

 MyIsAM  并发配置

 

myIsAM 设置 concurrent_insert 　控制并发

                 0 不允许并发插入

                 1　默认值  没有空洞 就允许并发插入

                 2  插入表的末尾 即使有空洞

delay_key_write 延迟写索引 

 

优化 Blob和 text

1.使用 substring()函数

优化排序

使用 max_length_for_sort_data 

第九章 操作系统和硬件优化

 

cpu和I/O资源 影响mysql性能

 

调优服务器的目标

1. 低延时
2. 高吞吐

cpu架构

多个cpu和核心

cpu联机处理事务 OLTP  on-line transaction processing 

数据库并发问题

1. 逻辑并发问题

                 应用程序可以看到的资源竞争：表和行的锁争用

        2.内部并发问题

平衡内存和磁盘资源

高速缓存

随机I/O和顺序I/O

顺序I/O比随机I/O快 

存储引擎执行顺序 比随机读取快

缓存，读和写

多次写入 一次刷新

I/O合并

 

工作集

完成某某项工作所需要的数据集

工作集包括索引和数据 

 

选择硬盘

传统磁盘读取数据步骤：

移动读取磁头到正确位置

等待磁盘旋转，磁头读取数据

 

磁盘选择

1. 存储容量
2. 传输速度
3. 访问时间(随机查找速度)
4. 主轴转速(15000RPM)
5. 物理尺寸（其他条件相同 物理尺寸越小 读取磁头时间越短）

固态存储

使用非易失性闪存芯片组成，NV RAM

SSD固态硬盘

PCle卡

SSD通过实现SATA（串行高级技术附件）接口 模拟标准硬盘 可以直接代替硬盘驱动 插入现有卡槽

高质量闪存设备应具备：

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

闪存

闪存读取快  写入慢 且要做好垃圾回收 技术

闪存有两种技术

1.   SLC 单层单元
2.   MLC 多层单元

flashcache

flashcache是linux内核模块 使用linux设备映射 他在内存和磁盘之间建立一个中间层 是facebook开源和使用技术之一 。可以优化数据库负载

RAID:磁盘冗余队列

为备份库选择硬件

备份库主要考虑的是成本

 

RAID 磁盘冗余队列优化 

 

网络配置

tcp的积压 back_log配置 tcp队列大小

文件系统

线程 

 

GNU/Linux 提供两种线程库

1. LinuxThreads
2. 原生Posix（NPTL）

NPTL更轻量 更高效

操作系统状态

 

vmstat 5

 

vmstat : vm stat工具 5s 打印一次报告

 

　procs :r 显示多少进程正在等待

              b 显示多少进程正在不可中断的休眠（等待 I/O 等待 网络 等待用户输入等）

    memory:

                  swpd 页面交换  

                  free  空闲

                  buff 缓冲

                  cache 操作系统缓存

  io

  system:

    in:每秒中断

    cs:上下文切换

 

iostat -dx 5

io stat 查看 io信息

rrqm/s和wrqm/s

每秒合并的读写请求

r/s和w/s

每秒发送到设备的读写请求

rsec/s 和 wsec/s

每秒读和写的扇贝数

avgrq -sz

请求的扇贝数

avgqu -sz 

设备队列中的等待请求数

await

队列等待

svctm

请求花费秒数

%util

活跃请求所占时间百分比

 

设备并发请求数

　concurrency=(r/s+w/s)*(svctm/1000);　

 

第10章 复制

 

复制概述

复制解决的基本问题是 让一台服务器的数据和另一台服务器数据保持同步 一台主库数据同步到多台备份库。

mysql两种复制方式

1. 基于行的复制
2. 基于语句的复制

复制解决的问题

复制的用途

1. 数据分布
2. 负载均衡
3. 备份
4. 高可用性和故障切换

如何复制

主库-->binary log-->备份库relay log-->备份库读取relay log重放数据

每个备份库在主库上 -->各自创建线程 执行 binlog dump命令 -->读取主库二进制文件 将数据发送给备库

binlog dump不共用 

复制有三个步骤

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

配置复制

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

sync-binlog=1

 

 发送复制事件到其他设备

复制过滤器

允许复制服务器上部分数据：重放的时候进行过滤

复制拓扑

1. 一个mysql备库实例 只能有一个主库
2. 每个备库必须有一个唯一服务器ID
3. 一个主库可以有多个备库
4. 如果打开了 log_slave_updates选项 备库可以把主库上数据变化传播到其他备库

一主多备

少量写 大量读  的时候非常有用

双主复制

两台服务器每个都设置成对方的主库和备库

最大的问题是解决数据冲突

mysql不支持多主复制

 被动模式下的主-主复制

其中一台服务器是只读的被动服务器

 配置主-主服务器对

1. 确保两台服务器上数据相同
2. 启用二进制日志 ，主库设置唯一服务器ID  并创建复制账号
3.  启用备库更新日志记录，这是故障转移和故障恢复的关键
4. 把被动服务器配置成只读 (可选)
5. 启动每个服务器的Mysql实例
6. 将每个主库设置成对方的备库，使用新创建的二进制日志开始工作

拥有备库的 主-主结构

为每个主库增加一个备库

环形复制

每个服务器是它之前服务器的主库，是它之后服务器的主库

 

 

主库 分发主库 备份库

 

分发主库 减少 备份库 创建线程  binlog dump 对主库内存的消耗， 专门负责分发

设置 slave_compressed_protocal 节约主库带宽

分发库上 每个表的存储引擎 必须是 blackhole

 

 树或金字塔型

优点 减轻主库负担

缺点 中间层出错 影响节点下面多个服务器

选择性复制

 

将主库中的数据--.> 划分到不同的数据库里-->数据库 备份到不同备份库

 

监控复制

show  master logs

show binlog EVENTS

　查看复制事件

 

备库提升为主库

1. 备库追赶上主库(数据)
2. 停止向老的主库写入
3. 将备库配置为新的主库
4. 写操作指向新的主库，开启主库写入　

 

 确定主备是否一致

checksum table 来检测

checksum table user;

但是当复制正在进行时这种方法是不可行的

使用 pt-table-checksum 工具

 

数据损坏或丢失

 

主库意外关闭

1.  设置sync_binlog 
2. 指定备库从下一个二进制日志开头读(部分日志事件将永久丢失)
3. 使用pt-table-checksum 来检查主备一致性 以便数据修复

备库意外关闭

1. 使用pt-slave-restart工具 查找上次停止的位置　　

主库上的二进制日志损坏 

1. 忽略损坏位置
2. flush logs创建新日志文件
3. 备库指向新文件开始位置

备库上的中继日志损坏

1. 若主库日志完好 使用change master to 命令 丢弃并重新获取事件

二进制日志与innoDB事务日志不同步

1. 无法修复

数据改变但事件仍是有效的sql

1. 无法修复

数据改变且事件是无效的sql

1. 忽略

数据遗漏 事件长度是错误的

1. 忽略 找下个事件的开始

第11章  可扩展的mysql

 

可扩展性

容量 ：最大吞吐量不是容量 容量是有效利用量 

 

向上扩展 （垂直扩张）

强悍的硬件  内核 cpu

水平扩展

1. 复制
2. 拆分
3. 数据分片

最简单的是 加服务器

工作负载分布到多个节点

按功能拆分

功能被捆绑到单个mysql，就只能垂直扩张 。其中一个功能非常庞大 应寻求不同策略

 数据分片

 

目前 扩展大型mysql的应用方案中 数据分片是最通用且最成功的方法

将数据切割成写块 存储到不同的节点。

公共服务不拆分，数据庞大增长快的部分进行数据分片 或许可以使用面向服务架构

选择分区键

 

多个分区键

跨分片查询

分配数据 分片和节点

分片和节点不一定是一对一 分片的大小应小于节点容量 这样就可以在单个节点存储多个分片

分片小  易于管理 易于数据恢复

 

节点上部署分片

1. 每个分片使用使用一个数据库  表名包含分片号 ：查询语句需要重新 使用占位符
2. 每个节点运行多个mysql实例 每个实例上有一个或多个分片
3. 每个分片各自使用一个数据库 数据库名要相同
4. 每个分片使用使用一个数据库 数据库名包含分片号，  表明不包含分片号

固定分配

数据分配到分片有两种方法

1. 固定分配
2. 动态分配 

固定分区优点：简单开销低  甚至可以硬编码

缺点：

1. 如果分片很大数量不多 很难平衡不同分片间的负载
2. 固定分片无法自定义数据 放在哪个分片 有些数据可能比其他数据活跃 也许被分配到同一个片内 各个分片间的负载不均衡
3. 修改分片策略比较困难

动态分配

  将每个数据单元映射到分片 如 建立一个分区函数表

给定用户ID 获取分片号 存储数据

混合动态分配和固定分配

显示分配

重新均衡分片数据

生成全局唯一ID

  1.auto_increment

   可以在 一台auto_increment_offset 设置为 1 auto_increment_increment 设置为2 

      那就是 从1 开始每次增长 2     1,3,5,7,9.....全为奇数

  另一台auto_increment_offset 设置为 2 auto_increment_increment 设置为2 

    那就是 从2 开始每次增长 2   2,4,6,8,10.....全为偶数

2.全局节点中创建表

  在全局数据节点中创建表 生成唯一ID

3.使用 redis或memcached

4.批量分配数字（号段）

5.复合值 如 分片号+自增ID

6.UUID

分片工具

通过多实例扩展

mysql 不能完全发挥硬件性能，当扩展超过24个cpu时 mysql性能趋于平缓  当内存 超过128G也同样如此

通过集群扩展

nosql

cap: 一致性（consistency）、可用性（Availability）、分区容错（partition-tolerance）

保持活跃数据独立

负载均衡

一个服务器群尽可能平均负载 通常设置负载均衡器

负载均衡的目的

1. 可扩展性
2. 高效性
3. 可用性
4. 透明性
5. 一致性

 

 引入中间件

 中间件  可以是硬件也可以是软件  中间件派发请求到指定服务器 并把执行结果 发送给请求机器

负载均衡器

负载均衡算法

 

可用方法

1. 随机
2. 轮询
3. 最少连接数
4. 最快响应
5. 哈希
6. 权重

第12章 高可用性

更少的宕机时间

宕机的原因 

1. 35% 环境问题：磁盘空间耗尽
2. 35%性能问题： 垃圾sql 还有些bug或错误   bad  schema 和索引设计
3. 20%复制：主库和备库数据不一致
4. 10% 数据丢失或损坏：手动删除  误操作

 如何实现高可用性

 避免宕机 1. 对宕机的原因进行配置和监控 ：平均失效时间  MTBF

                 2.发生宕机时要快速恢复：平均恢复时间 MTTR

           

 避免单点失效

增加冗余 1.增加冗余量 2.重复组件

       

 共享存储或磁盘复制

优点：

    避免数据丢失，为非存储组件建立冗余

   减少系统部分需求 提高组件高可用性

缺点

  mysql崩溃 文件损坏 备库受影响

  共享存储建议使用 innodb引擎 ACID

 

中间件解决方案

可以使用代理 端口转发 网络地址转换（NAT）或者硬件的负载均衡来实现故障转移和恢复

 

第13章  云端的mysql

 

粗略分为两类

Iaas:基础设施及服务

  Iaas 用于托管Mysql服务器的云端基础架构 

Dbaas 数据库及服务

mysql本身作为云端管理资源 用户收到mysql服务器的访问许可 才能访问  如 amazon运行的mysql rds 其中一些服务器并非真实的mysql 但其兼容MySQL协议和查询

 

云的优缺点

优点：

1. 减少管理和运维成本

缺点：

1. 资源共享不可预测
2. 虚拟的共享资源排查故障困难

四种基础资源

1. cpu周期
2. 内存
3. I/O
4. 网络

 

 第14章 应用层优化

sql语句优化

建立了没必要的连接

连接池是否合理

是否保持长连接

web服务器问题

不要使用apche做静态内容服务 可以使用 nginx 或 lighttpd 

不要让Apache填鸭式服务客户端

不要为长距离连接 apache 启用 keep-alive 这样会使重量级进程存活很长时间

寻找最优并发度

缓存

通常缓存越接近客户端 ，节省资源效率越高

主动缓存

被动缓存 memcached

 

 

应用缓存有多种：

1.  本地缓存
2. 本地共享内存缓存
3. 分布式内存缓存
4. 磁盘上的缓存

缓存控制策略

 

1. TTL time to  live 存活时间 ：设置一个过期时间
2. 显示失效 ：更新数据时使缓存失效
3. 读时失效

handlerSocket 和msmcached

 

hadoop

 

 

第15章 mysql备份与恢复

 

逻辑备份  

物理备份

 

增量备份

差异备份

 

 

文件快照 是一种非常好的在下备份方法