高性能Mysql
2019-10-22 15:22:45 73 举报AI智能生成
高性能MySql思维导图
作者其他创作
大纲/内容
基准测试
基准测试工具
集成测试工具
ad
http_load
Jmeter
单组件测试工具
mysqldump
MySQL BenchmarkSuite(sql-bench)
super Smack
Database Test Suite
Percona's TPCC-MySQLtool
sysbench
dbt2
原因
验证假设
重现异常行为
测试运行情况
模拟负载
规划业务增长
测试适应能力
测试软硬件配置
验证软硬件的正确性
策略
针对整个系统整体测试
集成测试
单独测试
单组件测试
标准
吞吐量
每秒的事务数（TPS）
响应时间或延时
任务执行时间
并发性
任意时间同时发生的并发请求
可扩展性
设计和规划
提出问题和目标
将参数和结果文档化
需要运行足够的时间
获得准确的测试结果
Schema和数据类型
数据类型
数据类型选择
更小的通常更好
简单就好
尽量避免NULL
数字、字符串、时间
标识符选择
整数类型通常是最标识符最好的选择
，可以更好地支持AUTO_INCREMENT
，可以更好地支持AUTO_INCREMENT
如果可能尽量避免字符串类型
整数类型
整数
TINYINT
8
SMALLINT
16
INT
32
BIGINT
64
有UNSIGNED属性
实数
DECIMAL
最多65个数字
FLOAT
DOUBLE
字符串类型
VARCHAR
存储可变长字符串
CHAR
类型是定长的
大的数据存储
TEXT
字符类型家族
BLOB
二进制家族
枚举ENUM
替代常用字符串类型
时间和日期类型
DATETIME
从1001年到9999年，精度为妙
TIMESTAMP
从1970年1.1日以来的（格林尼治时间）秒数，依赖时区
位数据类型
BIT
最大64位
当做字符串类型
特殊类型
IP地址
实际是32位的无符号整数
可以用INET_ATON和INET_NTOA转换
Schema设计
设计中陷阱
太多的列
宽表很多数据并不经常使用
太多的关联
mysql限制了每个关联操作最多只能有61个表
全能的枚举
防止过度使用枚举
变相的枚举
非此发明的NULL
子主题
范式和反范式
范式
1范式
表中的每一列都是不可分割的基本数据项，同一列中不能有多个值
2范式
在1范式基础上，表中的每个实例或行必须可以被唯一地区分
3范式
在2NF基础上，任何非主属性不依赖于其它非主属性
巴斯-科德范式（BCNF）
3NF基础上，任何非主属性不能对主键子集依赖
优点
更新操作比反范式化要快
只有很少或没有重复数据
表通常更小
很少有多余的数据
缺点
需要关联
反范式
优点
在一张表中，可以很好的避免关联
单独的表也能使用更有效的索引策略
缓存表和汇总表
为了提升性能，有时需要增加一张冗余的表
需要决定是需要实时维护数据还是定期重建
物化视图
预先计算并且存储在磁盘上的表
工具
Flexviews
通过提取原表的更改，可以增量地重新计算物化视图的内容
加快ALTER TABLE操作
所有的modify column操作都将导致表重建
ALTER COLUMNT 语句只会修改.frm文件，不会涉及表数据
只修改.frm文件
为想要的表结构构建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm
步骤：
1、创建一张有相同结构的空表，并进行所乣的修改
2、执行flush tables with read lock ，关闭所有正在使用表，并禁止任何表被打开
3、交换.frm文件
4、执行unlok tables来释放读锁
快速创建索引
禁用索引：DISABLE KEYS
启用所用：ENABLE KEYS
先删除所有非唯一索引，然后创建新的列，最后重新创建新的索引
索引
基础
索引优化应该是对查询性能优化最有效的手段
包含多个列的索引，列的顺序十分重要，MYsql只能高效的使用索引最左前缀列
MySql中索引是在存储引擎层
B-Tree索引
所有的值都是按顺序存储的
适合范围查找
每个叶子页到根的距离相同
B-Tree索引能改加快访问数据速度的原因:存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始查找
树的深度和表的大小直接相关
索引对多个值进行排序的依据是CREATE TABLE 语句中定义索引时列的顺序
B-Tree索引使用与全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找
查询类型
全值匹配
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另外一列
值访问索引的查询
限制
如果不能按照索引的最左列开始查找，则无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找
哈希索引
基于哈希表实现，只有精确匹配索引所有列的查询才有效
只有memory引擎支持哈希索引
哈希索引只支持等值比较查询
InnoDB支持自适应哈希索引：当某些索引值被频繁使用时，它会在内存中基于B-Tree索引上创建一个哈希索引
空间数据索引
MyISAM表支持空间索引
全文索引
一种特殊的索引。它查找的是文本中的关键字
索引的优点
快速定位到表的位置
可以支持ORDER BY和GROUP BY
索引可以使随机I/O变成顺序I/O
三星系统
一星：索引将相关的记录放在一起
二星：索引中的数据顺序和查找中的排序一致
三星：索引中的列包含了查询中需要的全部列
高性能的索引策略
独立的列
索引列不能是表达式的一部分
索引选择性
不重复的索引值
索引的选择性越高，查询效率越高
多列索引
优化索引列的顺序，或者创建全覆盖索引
选合适的索引顺序
争取的顺序依赖于使用该索引的查询
B-Tree索引列的顺序意味着索引首先按照最左列进行排序
将选择性最高的列放到 索引最前列
性能不只依赖于所有索引列的选择性，也和查询条件的具体值相关（值的分布）
高级特性
分区表
特点
实现分区代码实际上是对一组底层表的句柄对象的封装
每个分区表都有一个#分割命名的文件
MySql创建表时使用Partition By子语句定义分区存放的数据
一个表只能有1024个分区
分区表无法使用外键约束
分区表达式必须是整数，或者是返回整数的表达式
如果分区字段中有主键或唯一索引，那么所有主键列和唯一索引必须包含进来
使用分区表，要在where条件中带入分区列
使用场景
表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
分区表的数据更容易维护
分区表的数据可以分布在不同的的物理设备上，从而高效的利用多个硬件设备
可以使用分区表来避免
分区类型
根据范围分区
根据键值分区
使用数学模函数分区
使用策略
全量扫描数据，不要任何索引
索引数据，并分离热点
问题
NULL值会使分区过滤无效
分区列和索引列不匹配
选择分区的成本可能很高
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高
工具
EXPLAIN PARTITION
合并表
合并表相当于容器，里面包含了多个真是表，使用UNION指定包含哪些表
合并表和各个真实表字段完全相同，在合并表中的索引在字表中也有
无法使用REPLACE，无法使用自增字段
查询访问合并表，需要访问所有子表
视图
特点
本身是一个虚拟表，不存放任何数据
视图不能使用触发器
视图的实现算法是视图本身属性，与作用在视图上的查询语句无关
可更新视图
可以通过更新这个视图更新视图涉及额相关表
如果视图定义中包含了group、union、聚合函数，就不能被更新了
所有使用临时表算法实现的视图无法被更新
任何通过视图更新的行，都必须符合视图本身的WHERE条件定义
性能影响
可以使用视图实现基于列的权限控制
使用临时表算法实现的视图，有时性能会分行糟
外层查询的where条件无法下推到视图的临时表中，临时表也无法建立索引
视图的性能很难预测
限制
物化视图
将视图结果数据存放在一个可以查看的表中，定期刷新数据到这个表
工具
SHOW CREATE VIEW
LOAD FILE
读取.frm中的视图创建信息
外键约束
外键使用是有成本的
通常要求每次在修改数据时都要再另外一个表中多执行一次查询
可以使用外键检查数据一致性
可以使用触发器代替外键
可以在应用程序中进行约束，代替外键
内部存储代码
触发器
对每一个表的每一个事件最多只能定义一个触发器
只支持基于行的触发
缺点
无法保证原子性，出现错误无法回滚
事件
定时任务
存储程序
存储过程
函数
优点
提升安全
服务器端存储执行计划，可以降低消耗
在服务器端备份、维护
更好的分工（应用程序、数据库开发）
缺点
没有好的调试和开发工具
存储代码效率稍差
可能会对应用程序代码部署带来复杂性
没有更好的办法控制资源消耗
游标
只读
只能在存储过程或更底层的客户端API使用
指向的对象是临时表中，而不是实际查询到的数据
绑定变量
提高客户端与服务器端的数据传输效率
通过返回客户端指向计划句柄，客户端后续通过指定句柄提交数据
优点
可以高效的执行重复语句
服务器端只解析一次
已二进制的方式只发送参数和句柄
mysql存储参数时直接存储到缓存中
更安全
SQL接口的绑定变量
在存储过程中执行动态语句
限制
会话级的，连接不能共用绑定变量
sql总数的限制是个全局限制
用户自定义函数
动态链接库
插件
常见插件
存储过程插件
后台插件
INFORMATION_SCHEMA表
审计插件
全文解析插件
认证插件
字符集
只有基于字符的值才真正的有字符集的概念
控制字符集的选项
创建对象时的默认值
character_set_server
服务器和客户端通信时的设置
character_set_connection
character_set_result
选择与校对
工具
SHOW CHARACTERSET
SHOW COLLATION
EXPLAIN EXTENDED -> SHOW WARNING
规则
_cs
_ci
_bin
全文索引
基于相似度匹配的查询
根据WHERE子句中的MATCH AGAINST来区分是否使用全文索引
在使用全文索引排序时无法再使用索引排序
MATCH
返回关键词匹配的相关度
布尔全文索引
可以在查询中自定义某个搜索的词语的相关性
返回结果是未排序
通用修饰符
~\+\-\*
分布式（XA）事务
数据库层面或多个数据库的ACID
内部XA事务
外部XA事务
查询缓存
缓存完整的SELECT查询结果
语句中有不确定的数据时不缓存
子查询和存储过程不做缓存
跳过了解析、优化、执行阶段
跟踪查询涉及的每个表，如果这些表发生变化，和这些表相关的缓存将失效
通过哈希值引用缓存结果
只有当缓存带来的资源节约大于其本身带来的资源消耗时才会给系统带来性能提升
命中率
Qcache_hits/(Qcache_hits+Com_select)
工具
Com_*(update\delete)
Qcache_lowmen_prunes
配置和维护缓存
query_cache_type
ON、OFF、DEMAND
query_cache_size
1024的整数倍
query_cache_min_res_unit
query_cache_limit
query_cache_wlock_invalidate
减少碎片化
碎片整理
FLUSH_QUERY_CACHE
Innodb
如果表上有锁，对这个表的任何查询都无法使用缓存
当前事务ID小于内存字段中的计数事务ID则无法使用缓存
内存数据字典
事务ID
可以显式的告诉MySql某个表的缓存失效（ON DELETE CASCADE）
缓存优化
多个小表代替一个大表
批量写入时只做一次缓存失效
控制缓存空间的大小防止过期操作锁死（query_cache_size）
通过SQL_CACHE、SQL_NO_CACHE控制SELECT语句是否需要缓存
对密集写的场景，有时关闭缓存会更好
mysql架构
三层架构
工具/服务
核心服务功能
存储引擎
并发控制
读写锁
共享锁
读锁
排它锁
写锁
锁粒度
尽量只锁定需要修改额部分
表锁
基本的锁策略
行级锁
只在存储引擎层实现
事务
ACID
A:Atomicity
原子性
C:Consistency
一致性
I:Isolation
隔离性
D:durability
持久性
隔离级别
READUNCOMMITTED（未提交读）
READ COMMITED（提交读）
REPEATABLE READ（可重复读）
SERIALIZABLE（可串行）
自动提交
存储引擎
InnoDB
NDB Cluster
隐式和显示锁定
两阶段锁定
事务执行中随时锁定
根据隔离级别根据需要加锁
多版本并发（MVCC）
行级锁的一种变种
通过保持数据某个时间点的快照实现
InnoDB通过在每行后面保存两个隐藏列来实现（行创建时间，行的过期时间）
存储引擎
数据库（Schema）保存为数据目录下的子目录
表保存为同名的frm文件
SHOW TABLE STATUS
显示相关表的信息
InnoDB 存储引擎
发展
可测量性
可扩展性
可配置化
性能
兼容性
表是基于聚簇索引建立的
存储格式平台独立
支持热备份
子主题
MyISAM存储引擎
全文索引
压缩
空间函数等
不支持事务和行级锁
表被存储在数据文件和索引文件两个文件中
对整张表加锁
其他引擎
Archive引擎
Blackhole引擎
CSV引擎
Federated引擎
Memory引擎
Merge引擎
NDB集群引擎
第三方存储引擎
OLTP类引擎
TokuDB引擎
XtraDB引擎
面向列的存储引擎
大数据处理是面向列的存储引擎更有效
Infobright引擎
10TB以上
如果查询无法在存储层使用面向列的模式执行，则需要在服务器层转换成按行处理
社区存储引擎
引擎的选择
事务
备份
崩溃恢复
特有的特性
引擎转化
ALTER TABLE table_name ENGINE=engine_name
导入导出
mysqldump工具
性能剖析
性能
完成某项任务所需要的时间
响应时间
完成任务
1、执行时间
2、等待时间
优化
减少或者消除那些对查询结果不必要的工作
步骤
1、测量任务所花费的时间
2、对结果进行统计和排序
工具
Percona Toolkit
pt-query-digest
New Relic
xhprof
PHP应用程序测试
xdebug、Valgrind、cachegrind
剖析
基于执行时间的性能剖析
什么任务的执行时间最长
基于等待时间的剖析
什么地方被阻塞的时间最长
理解
值得优化的查询
异常情况
未知的未知
被掩藏的细节
影响因素
剖析服务器负载
慢查询日志
SHOW FULL PROCESSLIST
剖析单条查询
钻取
SHOW PROFILES
INFORMATION_SCHEMA
SHOW GLOBAL STATUS
EXPLAIN
SHOW INNODB STATUS
诊断触发器
要在问题开始是就捕获数据
收集的数据
系统状态
CPU利用率
磁盘使用率
可用空间
ps的输出采样
内存利用率
其他剖析工具
USER_STATISTICS 表
STRACE
查询系统调用情况
查询性能优化
基础
为什么速度慢
任务
子任务
响应时间
基本原因：访问的数据太多
MySql客户端与服务器之间的通信是“半双工”的
同一时间只能单向发送数据，而且必须是整包
MySql执行查询的过程
1、客户端发送一条查询给服务器
2、服务器先检查查询缓存，命中立即返回，否则进入下一步
3、服务器进行SQL解析、预处理，再由优化器生成对应的执行计划
4、MySql根据优化器生成的执行计划，调用存储引擎的API来执行查询
5、将结果返回给客户端
查询状态
Sleep
正在等待客户端发送新的数据
Query
正在执行查询或正在将结果发送给客户端
Locked
正在等待表锁
Analyzing and statistics
正在收集存储引擎的统计信息
Copying to tmp table【On disk】
正在执行查询，并且将结果复制到临时表中，一般是在做Group或者排序或者Union，On disk表示正在将内存临时表放到磁盘上
Sorting result
正在对结果排序
Sending data
多个状态间传送数据
生成结果集
向客户端发送数据
工具
EXPLAIN EXTENDED
SHOW WARNINGS
查询重构出的查询
Mysql无法利用多核特性来并行执行查询
MySql不支持松散扫描
MySql不支持在同一个表中同时执行查询和更新
慢查询
查询性能低下的基本原因：访问的数据太多
优化：
减少访问数据量的方式
确认应用程序是否检索大量超过需要的数据
确认MySql服务器层是否在分析大量超过需要的的数据行
案例：
查询不需要的数据
多表关联时返回全部列
总是取出全部列
无法完成索引覆盖扫描
重复查询相同数据
衡量查询开销三个指标
响应时间
服务时间+排队时间
扫描的行数
访问类型
索引扫描
范围扫描
唯一索引扫描
常数引用
返回的行数
查询优化处理
解析SQL
解析SQL语句生成“解析树”并根据语法规则效验
预处理
进一步检查解析树的是否合法
优化SQL执行计划
找到最好的执行计划
重新定义关联表的顺序
将外连接转化为内连接
使用等价变换规则
优化COUNT、MIN、MAX
预估并转化为常数表达式
覆盖索引扫描
子查询优化
提前终止查询
列表IN的比较
关联查询
MySQL认为任何一个查询都是一次“关联”
Mysql对任务关联都执行嵌套循环关联操作
排序优化
两次传输排序（旧）
读取行指针和需要排序的字段，对其进行排序，然后再跟进排序结果读取所需要的数据行
单次传输排序（新）
先读取查询所需要的所有列，然后再根据给定列进行排序，最好返回排序结果
重构查询方式
一次查询
切分查询
返回一部分
分解关联查询
每一个表一次查询
关联子查询
一般情况下关联子查询性能都比较糟糕
使用IN()加子查询，性能经常会非常糟，建议用EXISTS(）等效的改写查询
一旦使用了GROUP BY和DISTINCT,在执行查询过程中通常需要产生临时表
查询优化器的提示
通过在查询中加入相应的提示，可以控制查询的执行计划
提示
HIGH_PRIORITY和LOW_PRIORITY
指定优先级
DELAYED
立即返回查询，并写入缓存待时机处理
STRAIGHT_JOIN
让管理表按指定顺序管理
SQL_SMALL_RESULT和SQL_BIG_RESULT
告诉优化器结果集可能很大
SQL_CACHE和SQL_NO_CACHE
SQL_BUFFER_RESULT
SQL_CALL_FOUND_ROWS
FOR UPDATE和LOCK IN SHARE MODE
USE INDEX
特定的优化
COUNT
用COUNT（*）统计全表
通过SHOW STATUS 的结果数
使用EXPAIN的近似值
关联查询优化
确保ON和UNION子句中的列上有索引
确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列
优化子查询
尽可能的使用管理查询代替
GROUP BY和DISTINCT优化
使用索引优化
优化LIMIT分页
LIMIT加偏移量，同时加上合适的ORDER BY
尽可能的使用索引覆盖扫描
优化UNION查询
将GROUP BY ，ORDER BY，LIMIT等子句下推到各个子查询中
除非需要消除重复行，否则一定要使用UNION ALL
总结：
尽量少做事，可能的话不做事
快速的完成事情，确保设计中尽可能的使用上合适的索引
避免复杂计算
尽可能的让应用程序完成一些计算
配置
获取配置方式
命令行参数
配置文件
语法、作用域和动态性
作用域
服务器作用域（全局）
会话作用域
对象级作用域
动态配置
在服务器运行时修改
SHOW GLOBAL VARIABLES检查是否更新
通过SET命令赋值
常用变量
key_buffer_size
缓冲区空间
table_cache_size
打开表时生效
thread_cache_size
有连接关闭时生效
query_cache_size
立即生效
read_buffer_size
需要时生效
sort_buffer_size
需要时生效
工具
基准测试包
通过监控来确认生产环境的修改
Percona Toolkit
pt_mext
pt_mysql_summary
设置缓冲池大小
1、从服务器内存总量开始
2、减去操作系统的内存占用
3、减去一些MySql自身需要的内存
4、减去足够让操作系统缓存InnoDB日志文件的内存
5、减去其他配置的MySql缓冲和缓存需要的内存
6、除以105%
7、把结果四舍五入，向下去一个合理的数值
配置内存
配置
1、确定可以使用的内存上限
2、确定每个连接MYSql需要的使用的内存
3、确定操作系统需要多少内存才够用
如果没有虚拟内存正在交换到磁盘，就是操作系统内存足够的表现
4、把剩下的内存全部给MySql的缓存
缓存内存
不需要为操作系统及查询处理预留的内存都可以作为缓存
重要的缓存
InnoDB缓冲池
索引
行数据
哈希索引
插入缓冲
锁
延迟写入
InnoDB日志文件和MyISAM数据的操作系统缓存
MyISAM键缓存
查询缓存
无法手工配置的缓存
线程缓存
表缓存
InnoDB表缓存（数据字典）
包含相关表.frm文件的解析结果
工具
innodb_use_sys_stats_table
innodb_stats_on_metadata
innodb_open_files
I/O
innodb使用日志减少事务的开销
innodb用日志把随机IO编程顺序IO
innodb使用后台线程智能地刷新变更到数据文件
工具
SHOW INNODB STATUS的日志，监控日志和日志缓冲区的I/O性能
innodb_flush_log_at_trx_commit
0\1\2
日志放在有电池的RAID卷中
innodb_flush_method
fdatasync
0_DIRECT
ALL_0_DERICT
0_DSYNC
async_unbuffered
unbuffered
nosnyc和littlesync
0 条评论
下一页
