linux mysql dba 博客,MYSQL数据库学习系列四

MYSQL数据库学习系列四

四．MYSQL的应用优化

4.1-MySQL索引优化与设计

什么是索引

索引的意义 —— 快速定位要查找的数据

数据库索引查找

全表扫描 VS 索引查找

如何根据首字母找到所在行

二分查找

B+tree

InnoDB表聚簇索引

索引中只放着排序字段和ID

创建索引

单列索引

create index idx_test1 on tb_student (name);

联合索引

create index idx_test2 on tb_student (name, age);

索引中先根据name排序，name相同的情况下，根据age排序

索引维护

索引维护由数据库自动完成

插入/修改/删除每一个索引行都会变成一个内部封装的事务

索引越多，事务越长，代价越高

索引越多对表的插入和索引字段修改就越慢

控制表上索引的数量，切忌胡乱添加无用索引

如何使用索引

依据WHERE查询条件建立索引

select a, b from tab_a where c=? ;

idx_c (c)select a, b from tab_a where c=? and d=?;

idx_cd (c, d)

排序order by, group by, distinct字段添加索引

select from tb_a order by a;select a, count() from tb_a group by a;

idx_a (a)

select from tb_a order by a, b;

idx_a_b (a, b)

select from tb_a order where c=? by a;

idx_c_a (c, a)

索引与字段选择性



某个字段其值的重复程度





选择性很差的字段通常不适合创建单列索引



o男女比例相仿的列表中性别不适合创建单列索引

o如果男女比例极不平衡，要查询的又是少数方(理工院校查女生)可以考虑使用索引



联合索引中选择性好的字段应该排在前面



select from tab_a where gender=? and name=?;

idx_a1 (name, gender)

联合索引与前缀查询

联合索引能为前缀单列，复列查询提供帮助

idx_smp (a, b, c)where a=? ;where a=? and b=? ;where a=? and c=? ;(部分ok)

合理创建联合索引，避免冗余 (a) , (a, b) , (a, b, c) X (a, b, c) ok

长字段上的索引

在非常长的字段上建立索引影响性能

InnoDB索引单字段(utf8)只能取前767 bytes

对长字段处理的方法

oEmail类，建立前缀索引

Mail_addr varchar(2048)

idx_mailadd (Mail_addr(30)) ok

o住址类，拆分字段

Home_address varchar(2048)

idx_mailadd (Mail_addr(30)) ? -- 很可能前半段都是相同的省市区街道名称

Province varchar(1024), City varchar(1024), District varchar(1024), Local_address varchar(1024) ... -- 建立联合索引或单列索引

索引覆盖扫

最核心SQL考虑索引覆盖

select Name from tb_user where UserID=?

Key idx_uid_name(UserID, Name)



不需要回表获取name字段，IO最小，效率最高

无法使用索引的情况

索引列进行数学运算或函数运算

where id+1=10; Xwhere id = (10-1); ok

year(col) < 2007; X

col < '2007-01-01'; ok



未含符合索引的前缀字段

Idxabc (a, b, c):where b=? and c=?; X

(b, c) ok



前缀通配,''和'%'通配符

Like '%xxx%'; XLike 'xxx%'; ok



where 条件使用NOT, <>, !=

字段类型匹配

o并不绝对，但是无法预测地会造成问题，不要使用

a int(11), idx_a (a)where a = '123'; Xwhere a = 123 ; ok

利用索引排序

idx_a_b (a, b)

能够使用索引帮助排序的查询：

order by a

a = 3 order by border by a, border by a desc, b desc

a > 5 order by a

不能使用索引帮助排序的查询：

order by b

a > 5 order by b

a in (1, 3) order by border by a asc, b desc

如何确定一个查询走没走索引，走了哪个索引

explain是确定一个查询如何走索引最简便有效的方法 explain select from tb_test ;

关注的项目

otype:查询access的方式

okey:本次查询最终选择使用哪个索引，NULL为未使用索引

okey_len:选择的索引使用的前缀长度或者整个长度

orows:可以理解为查询逻辑读，需要扫描过的记录行数

oextra:额外信息，主要指的fetch data的具体方式

4.2-MySQL数据库设计

什么是Schema设计

设计数据库的表，索引，以及表和表的关系

o在数据模型的基础上将关系模型转化为数据库表

o满足业务模型需要基础上根据数据库和应用特点优化表结构

为什么Schema需要设计

Schema关系到应用程序功能与性能

o满足业务功能需要

o同性能密切相关

o数据库扩展性

o满足周边需求(统计，迁移等)

关系型数据库修改Schema经常是高危操作

oSchema设计要体现一定的前瞻性

完全由开发者主导的Schema设计

着眼于实现当前功能

完全基于功能的设计可能存在一些隐患

o不合理的表结构或索引设计造成性能问题

o没有合理评估到数据量的增长造成空间紧张而且难以维护

o需求频繁修改造成表结构经常变更

o业务重大调整导致数据经常需要重构订正

基于性能的表设计

根据查询需要设计好索引

根据核心查询需求，适当调整表结构

基于一些特殊业务需求，调整实现方式

索引

正确使用索引

更新尽可能使用主键或唯一索引

主键尽可能使用自增ID字段

核心查询覆盖扫描

o用户登录需要根据用户名返回密码用于验证create index idx_uname_passwd on tb_user (username, password);

o建立联合索引避免回表取数据

反范式，冗余必要字段

针对核心SQL保留查询结果所必须的冗余字段，避免频繁join

o例：消息表中冗余了每次读消息必须返回的nickname字段，避免每次读消息都变成join操作。代价是用户修改nickname成本变高。

拆分大字段

拆分大字段到单独表中，避免范围扫描代价大

o例：博文表拆分两份，标题表只保留标题和内容缩略部分，用于快速批量返回标题列表，正文表保存大段博文内容，用于点开文章单个读取

避免过多字段或过长行

根据SQL必要返回设计字段，有必要就拆表，避免过多字段

一次没有必要获取那么多列数据

行过长导致表数据页记录变少，范围扫描性能降低

更新数据也代价增加

16K也最少放2行，可能出现行迁移

分页查询

避免limit + offset过大

应该使用自增主键ID模拟分页

o第一页，直接查

o得到第一页的max(id)=123(一般是最后一条记录)

o第二页，带上id>123查询：where id>123 limit 100

o这样每次只需要扫描100条数据

要求业务上禁止查询XX页之后的数据

热点读数据特殊处理

根据数据获取的频率或数量不同对热点数据做特殊处理

o例1：论坛系统中置顶帖、公告贴，可以单独拆分存储，由于每次访问都要全部读出来，单独放在一起，避免每次都到普通表中随机找出来

热点写数据特殊处理

根据数据获取的频率或数量不同对热点数据做特殊处理

o例2：微博系统中对于大量关注的热点账号消息从"推"改为"拉"，避免过量insert操作。

准实时统计

对不需要精确结果的计数等统计要求，建立定期更新结果表

o例：首页要求展示动态成交总金额，维护一个计数表，每分钟根据原表注册时间获取增量sum值更新计数表，避免每次用户刷新都要扫描交易全记录表

实时统计改进1 - 触发器实时统计

对需要精确统计的计数利用数据库触发器维护计数表

o例：用户量冲亿活动要求实时统计，用户表上加触发器，每次有新用户插入就同时在计数表+1

实时统计改进2 - 缓存实时统计

对需要精确统计的计数利用前端缓存实时维护计数

o例：用户量冲亿活动要求实时统计，注册数量在缓存中实时维护，每注册一个就+1，完全避免数据库读写操作。缓存万一故障失效，可从数据库整体count重新获取。

实时统计改进2 - 最大自增ID获取总数

很多逻辑可以利用自增ID主键最大值直接作为总数

o例：用户量冲亿活动要求实时统计，用户表加上自增ID作为主键，只要取当时max(ID)就可以得到用户总数

课拓展性设计

可拓展性

o硬件资源增长有极限的情况下处理尽可能久的线上业务

数据分级，冷数据归档与淘汰

o可以不断释放空间供新数据使用

为数据分布式做准备

o分库分表

o水平拆分

o牺牲一定的关系模型支持

分区表与数据淘汰

range分区

适合数据需要定期过期的大表

单个分区扫描迁移数据到历史库避免全表扫描IO开销

删除单个分区非常高效

分区表与垂直分区

list分区

适合将来可能要基于地区，类目等方式垂直拆分数据的方式

清理节点上不要的数据非常高效

分区表与水平分区

hash分区

适合将来需要做水平拆分的表

清理节点上不要的数据非常高效

MySQL分区表的局限

主键或唯一键必须包含在分区字段内

分区字段必须是整数类型，或者加上返回整数的函数

满足周边需求

为周边需求额外增加表设计

o为后台统计任务增加特殊索引

o为数据迁移或统计需求增加时间戳

统计和后台需求

统计运行SQL往往和线上有很大不同

o利用MySQL——主多从，主从可以建不同索引的特性将统计分流到特定从库

o包括一些特殊用户批量查询等，所有对线上有IO压力的查询都要读写分离

自动更新时间戳

统计需求经常要求从线上读走增量数据

表的第一个timestamp类型字段再写入时如果不填值，会自动写入系统时间戳

表的第一个timestamp类型字段每次记录发生更新后都会自动更新

在update_time字段上建索引用于定时导出增量数据

Schema设计与前瞻性

基于历史经验教训，预防和解决同类问题

把折腾DBA够呛的所有Schema改造的原因记录并分析总结 例：

业务为例用户信息加密做了大改造

o数据库结果大量改动，增加了加密字段，验证策略表，所有表重新订正数据等等

o是否所有用到用户信息管理的应用都要去上线就用密文？

程序bug误删数据，线上风险大

o改造业务流程，不再删除数据，加入is_deleted标记位，经常给各种表加

o今后的类似表是否一上线就都用标记位的方式，并加上修改原因字段？

支付类应用后期做了风控改造

o对线上订单大表改造，加了限额，终端类型等字段

o遇到支付类应用，是否一上线就提示业务是否需要考虑风控并留好相关字段？

4.3-MySQL容量评估

性能容量评估

分析线上业务场景

评估数据库服务器所需性能指标

预估可能成为瓶颈的服务器资源

帮助数据库性能调优

数据库服务器硬件性能指标

磁盘IO性能

内存容量

CPU

网络吞吐量

磁盘容量

数据库业务特点关键词

OLTP/OLAP类型

并发请求

读写比例

数据量

冷热数据比

数据分级存储

OLTP/OLAP

T = Transaction

面向广大用户，高并发，较短事务操作

互联网应用绝大部分属于OLTP

OLTP看重服务器CPU，内存，写事务较多或内存不够则依赖磁盘IO

A = Analytical

通常面向内部人员，大规模复查询

OLAP看重磁盘扫描的IO能力，部分依赖内存排序

并发请求 - 衡量线上业务繁忙程度

业务高峰时数据库的每秒并发访问量是多少

通过应用服务器数量，连接池配置判断

通过产品估算初上线用户规模和用户增长速度判断

通过实际业务业务类型判断

并发量相关资源：CPU

读写比例 - 描述应用程序如何使用数据库

线上业务select只读与update/delete/insert写操作比例

delete/update通常都是先读再写

insert需要区分数据写入时持续insert还是大量导入数据

根据业务实际场景分析

多读场景相关资源：内存

多写场景相关资源：磁盘IO

数据量 - 总量

数据库服务器存储设备可扩容能力的上限

根据估算的业务量，写入模式，分析数据增长量

预估一个硬件升级周期内数据库可存放数据的总量，上线时要留好余量

数据总量相关资源：磁盘容量

冷数据与热数据 - 有用数据的实时集合

热数据，线上最新一定周期内将被反复访问的数据

冷数据，线上保存着的，最近不会被在线用户用到的数据

估算活跃用户量，数据增长量等预估热数据量

内存大小尽可能足够存放线上实时热数据

热数据相关资源：内存

线上数据分层存储 - 缓解线上磁盘空间压力

最新热数据确保放在内存中

还可能访问到的较早数据存放在线上库磁盘中

更早的不会常规访问的数据定期迁移至历史库中

区分哪些数据时效性强可以迁移

服务器资源选型 - 将可选方案列出来

资源指标 可选方案

磁盘IO性能 单盘 -> 盘阵; SATA -> SAS; HDD -> SSD

内存容量 较小内存 -> 较大内存

CPU 普通 -> 多核，超线程

网络吞吐量 千兆 -> 万兆; 单网卡 -> 多路;

磁盘容量 单盘 -> 盘阵; 单盘 -> LVM

案例一，网易云音乐曲库数据库服务器评估

用于存放线上数千万歌曲信息

确定属于OLTP线上类型数据库

并发请求量

o50台应用服务器，每台最大连接数100

o可能峰值5000qps，并发请求量较大

CPU需求高

读写比例

o访问模式以用户列出歌单和播放歌曲时查询歌曲信息为主，用户只有只读查询

o写数据发生在录入新歌或修改歌曲信息时后台操作，写比例小，且为批量导入

o读写比100:1

数据总量

o估算每首歌信息8K，总计5000万，总量400G

o数据总量增长相对缓慢

冷热数据

o5000万歌曲中大约40%可能被访问，10%属于热点歌曲

o热数据大约<=40G

数据分级存储需求

o由于没有用户产生的数据，歌曲信息无法分级存储

内存需求一般，>=40G

磁盘IO能力需求一般

网络流量要求，8k*2500/1024 ≈ 20MB/S，一般

资源指标 可选方案

磁盘IO性能 两块SAS做RAID1

内存容量 96G内存

CPU 2c8core超线程 相当于32核

网络吞吐量 千兆双网卡bunding

磁盘容量 900G

案例二，网易理财销售数据库服务器评估

用于存放理财用户线上订单

确定属于OLTP线上类型数据库

业务场景有明显特征

o特定高息产品秒杀销售时间窗有大量并发订单写入

o平时只有少量订单查询和请求，和较低的常规产品购买请求

评估应以满足最关键的业务高峰为基准

并发请求量

o秒杀期间持续时间短，但是并发量预估30台应用服务器约2000tps

读写比例

o高峰时写订单是主要开销操作

CPU要求高

磁盘IO要求很高

数据总量

o根据业务分析，订单属于写入瞬时量大，总量较小，单笔金额较高

o总量预估一年成交百万级别，增长较稳定

o判断数据存储需求小于200G

冷热数据

o峰值写入为主，内存要求存放热点期间产生的脏数据即可

数据分级存储需求

o用户订单业务约定页面展示最近半年订单，半年前的需要到历史查询页面专门查询

o因此可以做分级存储，迁移所有半年前的订单至历史库

内存需求一般， >= 30G

磁盘空间需求一般， >=200G

磁盘IO能力需求很高

网络要求较高

o并发流量较高

o响应速度要求高

资源指标 可选方案

磁盘IO性能 两块SSD做RAID1

内存容量 64G内存

CPU 2c8core超线程 相当于32核

网络吞吐量 万兆双网卡bunding

磁盘容量 600G

4.4-MySQL性能测试

为什么需要性能测试

对线上产品缺乏心理预估

重现线上异常

规划未来的业务增长

测试不同硬件软件配置

性能测试的分类

设备层的测试

业务层的测试

数据库层的测试

设备层的测试

关注的指标

o服务器、磁盘性能

o磁盘坏块率

o服务器寿命

业务层测试

针对业务进行测试

数据库层测试

什么情况下要做MySQL的测试

o测试不同的MySQL分支版本

o测试不同的MySQL版本

o测试不同的MySQL参数搭配

MySQL测试分类

CPU Bound

IO Bound

写入测试 更新测试 纯读测试 混合模式

常用的测试工具

开源的MySQL性能测试工具

osysbench

otpcc-mysql

omysqlslap

针对业务编写性能测试工具

oblogbench

性能测试衡量指标

服务吞吐量(TPS, QPS)

服务响应时间

服务并发性

Sysbench

业界较为出名的性能测试工具

可以测试磁盘、CPU、数据库

支持多种数据库：Oracle, DB2, MySQL

需要自己下载编译安装

建议版本：sysbench0.5

编译安装Sysbench

下载sysbench

ogit clone https://github.com/akopytov/sysbench.git

编译&安装

o./autogen.sh

o./configure

omake && make install

Sysbench流程

常见的做法

初始化数据 -> 运行测试 -> 清理数据

Prepare语法

sysbench --test=parallel_prepare.lua --oltp_tables_count=1 --rand-init=on --oltp-table-size=500000000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-requests=0 prepare

参数 含义

--test=parallel_prepare.lua 运行导数据的脚本

--oltp_tables_count 测试需要几张表

--oltp-table-size 每张表的大小

--mysql-host MySQL Host

--mysql-port MySQL Port

--mysql-db MySQL DB

--mysql-user MySQL User

--mysql-password MySQL Password

--rand-init 是否随机初始化数据

--max-requests 执行多少个请求之后停止

prepare 执行导数据

Sysbench表结构

create table 'sbtest1'(

'id' int(10) unsigned not null AUTO_INCREMENT,

'k' int(10) unsigned not null DEFAULT '0',

'c' char(120) not null DEFAULT '',

'pad' char(60) not null DEFAULT '',

PRIMARY KEY ('id'),

KEY 'k_1' ('k')

) ENGINE=InnoDB AUTO_INCREMENT=3000000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000

Run语法

sysbench --test=oltp.lua --oltp_tables_count=1 --num-threads=100 --oltp-table-size=500000000 --oltp-read-only=off --report-interval=10 --rand-type=uniform --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-time=1000 --max-requests=0 run

参数 含义

--test=oltp.lua 需要运行的lua脚本

--oltp_tables_count 测试需要几张表

--oltp-table-size 每张表的大小

--num-threads 测试并发线程数

--oltp-read-only 是否为只读测试

--report-interval 结果输出间隔

--rand-type 数据分布模式，热点数据或者随机数据

--max-time 最大运行时间

--max-requests 执行多少个请求之后停止

prepare 开始测试

特殊情况

写入测试

写入数据进行测试 -> 清理数据

cleanup

手动drop掉表和database

使用sysbench提供的cleanup命令

sysbench --test=parallel_prepare.lua --oltp_tables_count=1 --rand-init=on --oltp-table-size=500000000 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sys --mysql-password=netease --mysql-db=sbtest --max-requests=0 cleanup

Tpcc-mysql



TPC-C是专门针对联机交易处理系统(OLTP系统)的规范





Tpcc-mysql由percona根据规范实现





下载Tpcc-mysql



obzr branch lp:~percona-dev/perconatools/tpcc-mysql



编译安装



使用Tpcc-mysql的步骤

创建表结构和索引 -> 导数据 -> 运行测试 -> 数据清理

创建表结构

create_table.sql

add_fkey_idx.sql

Tpcc-load

tpcc_load [server] [DB] [user] [pass] [warehouse]

函数 含义

server 数据库IP

DB DB名称

user 用户名

pass 密码

warehouse 仓库数量

Tpcc-start

tpcc_start -h server_host -P port -d database_name -u mysql_user -p mysql_password -w warehouse -c connections -r warmup_time -I running_time -i report-interval -f report-file

函数 含义

warehouse 仓库数量

connections 并发线程数

warmup_time 预热时间

running_time 运行时间

report_interval 输出时间间隔

report_file 输出文件

总结

IO Bound测试数据量要远大于内存、CPU Bound测试数据量要小于内存

测试时间建议大于60分钟，减小误差

Sysbench更倾向于测试MySQL性能、TPCC更接近于业务

运行测试程序需要同时监控机器负载，MySQL各项监控指标