高性能MySQL总结
2016-05-13 15:03:12 0 举报AI智能生成
MySQL
作者其他创作
大纲/内容
1、架构
1、逻辑架构
2、并发控制
服务器层
存储引擎层
表锁
MyISAM
行级锁
InnoDB
3、事务
1、特性
1、原子性
2、一致性
3、隔离性
4、持久性
2、隔离级别
1、未提交读
2、提交读
3、可重复读
4、可串行化
3、死锁
1、what
两个以上事务在统一资源上相互占用，并请求锁定对方占用的资源，导致恶性循环
2、why
数据冲突
存储引擎实现方式
3、how
InnodDB 将持有最少行级排他锁的事务进行回滚
4、事务日志
4、多版本并发控制MVCC
1、作用
避免加锁
2、原理
保存数据在某个时间点的快照
3、分类
乐观并发控制
悲观并发控制
4、实现方法
在每行记录后面保存两个隐藏的列，行的创建时间，行的过期时间。并非实际时间值，而是系统版本号。
5、隔离级别限制
提交读
可重复读
5、存储引擎
1、分类
1、InnoDB(默认)
1、用来处理大量的短期事务
2、采用MVCC支持高并发
3、基于聚簇索引建立
4、支持热备份
2、MyISAM
不支持事务和行级锁
崩溃后无法安全恢复
3、Archive
4、Blackhole
5、CSV
6、Federated
7、Memory
8、Merge
9、NDB集群
10、第三方存储引擎
1、面向列的
2、社区
3、OLTP类
2、如何选择
考虑因素
1、事务
2、备份
3、崩溃恢复
4、特有的特性
应用场景
1、日志型应用
MyISAM/Archive
2、大部分只读的表
InnoDB
3、订单处理
InnoDB
4、电子公告牌和主体讨论的论坛
5、CD-ROM应用
6、大数据量
3~5TB
InnoDB
10TB以上
数据仓库
3、如何转换
1、Alter table
需要执行很长时间，复制，会在原表上加读锁。
2、导出与导入
需修改表名和去掉Drop table语句，不安全
3、创建与查询
先创建一个新的存储引擎表
insert...select
2、基准测试
1、why
1、验证假设
2、重现异常
3、测试系统
4、预测扩展性瓶颈
5、规划未来的业务增长，评估所需资源
6、测试应用适应可变环境的能力
7、测试不同的硬件、软件和操作系统配置
8、证明新采购的设备是否配置正确
2、策略
1、集成式：针对整个系统的整体测试
1、组成
1、Web服务器
2、应用代码
3、网络
4、数据库
2、why
1、用户关注的是应用整体的性能
2、MySQL并非总是应用的瓶颈
3、发现各部分之间的缓存带来的影响
4、更能揭示应用的真实表现
3、测试工具
1、ab
2、http_load
3、JMeter
2、单组件式：单独测试MySQL
why
1、比较不同的schema或者查询的性能
2、针对应用中的某个具体问题的测试
3、快速检测出某些调整后的效果
测试工具
1、mysqlslap
作用
模拟服务器负载，输出计时信息
2、MySQL Benchmark Suite (sql-bench)
作用
单线程，测试服务器执行查询的速度
优点
包含大量预定义的测试
缺点
单用户模式
3、Super Smack
作用
压力测试
负载生成
4、Database Test Suite
5、Percona's TPCC-MySQL Tool
6、sysbench
测试内容
1、文件I/O
2、操作系统调度器
3、内存分配和传输速度
4、POSIX线程
5、数据库服务器
3、测试何种指标
1、吞吐量
what
单位时间内的事务处理数
常用测试单位
每秒事务数(TPS)
每分钟事务数(TPM)
2、响应时间或延迟
任务所需的整体时间
3、并发性
what
在任意时间有多少同事发生的并发请求
why
为了测试应用在不同并发下的性能
4、可扩展性
给系统增加一倍的工作，在理想情况下获得两倍的结果(吞吐量)
3、服务器性能剖析
1、性能问题
1、如何确认服务器是否达到了性能最佳状态
2、找出某条语句为什么执行不够快
3、卡死等某些间歇性疑难故障
2、解决方案
1、测量任务所花费的时间
执行时间
服务器需要做大量的工作，从而导致大量消耗CPU
方法
Percona Toolkit中的pt-collect
等待时间
在等待某些资源被释放
方法
GDB的堆栈跟踪
pt-pmp穷人剖析器
2、对结果进行统计和排序，将重要的任务排到前面
3、对应用程序进行性能剖析
1、影响因素
1、外部资源，比如调用了外部的Web服务或搜索引擎
2、应用需要处理大量的数据，比如分析一个超大的XML文件
3、在循环中执行昂贵操作，比如滥用正则
4、使用了低效算法，比如暴力搜索算法
2、工具
New Relic的软件即服务(software-as-a-service)产品
4、剖析MySQL查询
1、剖析服务器负载
捕获查询到日志文件中
分析查询日志
2、剖析单条查询
1、使用 show profile
测量耗费时间和查询执行状态变更相关数据
2、使用慢查询日志
3、使用Performance Schema
4、使用 show status
返回一些计数器
3、使用性能剖析
5、诊断间歇性问题
1、单条查询问题还是服务器问题
1、使用show global status
2、使用show processlist
3、使用查询日志
2、捕获诊断数据
1、一个可靠且实时的触发器，就是什么时候问题会出现
Percona Toolkit的pt-stalk
2、收集什么样的数据
1、系统状态
2、CPU利用率
3、磁盘使用率和可用空间
4、ps的输出采样
5、内存利用率
3、解释结果数据
1、检查问题是否真的发生了，避免误报
2、是否有非常明显的跳跃性变化
3、将Percona Toolkit中pt-mysql-summary和pt-summary的输出结果打包，用pt-sift快速检查收集到的样本数据
4、什么导致资源性能低下
1、资源过度使用，余量不足以正常工作
2、资源没有被正确配置
3、资源已经损坏或者失灵
6、其他剖析工具
1、使用USER_STATISTICS表
1、可以查找使用得最多或者使用得最少的表和索引
2、可以查找出从未使用的索引，可以考虑删除之
3、可以看看复制用户的CONNECTED_TIME和BUSY_TIME，以确认复制是否会很难跟上主库的进度
2、使用strace
调查系统调用情况
4、Schema与数据类型优化
1、选择优化的数据类型
1、原则
1、更小的通常更好
2、简单就好
3、尽量避免NULL
2、组成
1、整数类型
组成
1、TINYINT
2、SMALLINT
3、MEDIUMINT
4、INT
5、BIGINT
属性
UNSIGNED 不允许负值
宽度
不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数
2、实数类型
类型
精确
DECIMAL
MySQL自身实现，运算较慢
不精确
DOUBLE
CPU直接支持，运算较快
FLOAT
CPU直接支持，运算较快
提升效率方法
在数据量较大时，使用BIGINT代替DECIMAL。乘以相应倍数即可。
3、字符串类型
VARCHAR和CHAR类型
长度是否可变
VARCHAR
可变长，节省空间
CHAR
定长
适用场景
VARCHAR
字符串列的最大长度比平均长度大很多
列的更新很少，所以碎片不是问题
使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储
CHAR
存储很短的字符串
经常变更
BLOB和TEXT类型
存储类型
BLOB
二进制
TEXT
字符
是否有排序规则和字符集
BLOB
否
TEXT
是
使用枚举代替字符串类型
优点
ENUM和ENUM关联会很快
缺点
避免使用数字作为枚举常量，双重性容易导致混乱
字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE
4、日期和时间类型
5、位数据类型
6、选择标识符
7、特殊类型数据
问题2
问题3
结束
要点1
要点2
要点3
0 条评论
下一页
