mysql事务：可重复读
mysql是单线程的服务
mysql提升性能：提高内存（缓存和数据库文件相等，效果将降低）
mysql引擎：
MyISAM：
特性：并发性与锁级别 (表级锁）
表损坏修复(repair table tablename)
支持数据压缩(myisampack) 只读不能写操作
限制：mysql5.0默认表小于4G，储存更大修改MAX_Rows和AVG_ROW_LENGTH(乘积)，大于5.0默认支持256T
使用场景：非事务型应用 空间类应用
Innodb：
mysql5.5之后默认存储引擎
使用表空间进行数据存储
innodb_file_per_tble
ON:独立表空间：tablename.idb
OFF:系统表空间:ibdataX
系统表空间和独立表空间如何选择：
系统表空间无法简单地收缩文件大小
独立表空间可以通过optimize table命令收缩文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
建议使用独立表空间
将系统表空间转移到独立表空间
1.使用mysqldump导出所有数据库表数据
2.停止mysql服务，修改参数，并删除innodb相关文件
3.重启mysql服务，重建innodb系统表空间
4.重新导入数据
事务性存储引擎
ACID特性
Redo Log和Undo Log
Redo Log:已提交事务
Undo Log:未提交的事务回滚
innodb支持行级锁（存储引擎层实现）
什么是锁？
管理共享资源的并发访问
实现事务隔离性
锁的分类：
共享锁（读锁）
独占锁（写锁）
表级锁
行级锁
阻塞和死锁
阻塞：事务的并发（一个等待另一个执行）
死锁：等待同一个资源
Innodb状态检查：show engine innodb status;
使用场景：
5.7支持全文索引和空间函数
大多数OLTP应用
CSV存储引擎：
数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM存储表的元数据如表状态和数据量
.frm存储表结构信息
特点：
以CSV格式进行数据存储
所有列不能为NULL
不支持索引（不适合大表）
对文件数据可直接编辑
使用场景：
数据交换的中间表
Archive存储引擎：
以zlib对表数据进行压缩，磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点：
只支持insert和select操作
只允许在自增ID列上加索引
使用场景：日志和数据采集类应用
Memory存储引擎：
也称HEAP存储引擎，数据保存在内存中
特点：
支持HASH索引和BTree索引（默认HASH索引）
等值查找：HASH
范围查找：BTree
所有字段固定长度varchar(10)=char(10)
不支持BLOG和TEXT等大字段
表级锁
最大大小有max_heap_table_size参数决定（默认16M）
使用场景：
查找或者映射表，例如邮编和地区的对应表
用于存储数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
Federated存储引擎：
特点：
提供了访问远程Mysql服务器上表的方法
本地不存储数据，数据存储在远程服务器上
本地保存表结构和连接信息
使用场景：
偶尔统计分析及手工查询
MySQL服务器参数：
内存相关参数：
确定可以使用的内存上限
确定每个连接使用的内存（每个连接的）
sort_buffer_size(排序缓存大小)
join_buffer_size(关联缓存大小)
read_buffer_size(读取缓存池大小 4k的倍数)
read_rnd_buffer_size(索引缓冲池的大小)
确定为操作系统保留的内存
为缓冲池分配内存
Innodb_buffer_pool_size
总内存-（每个线程所需要的内存*连接数）-系统保留内存
key_buffer_size(MyISAM占用大小)
select sum(index_length) from information_schema.tables
where engine = 'myisam'
I/O相关配置参数
Innodb I/O相关配置
Innodb_log_file_size(事务日志大小)
Innodb_log_file_in_group(日志个数)
事务日至总大小 = Innodb_log_file_in_group*Innodb_log_file_size
Innodb_log_buffer_size(事务日志缓冲区) 32-128M
Innodb_flush_log_at_trx_commit：
0:每秒进行一次log写入cache，并刷新日志到磁盘
1（默认）:每次事务提交执行log写入cache，并刷新到磁盘
2[建议]:每次事务提交，执行log数据写入到cache，每秒执行一次刷新日志到磁盘
Innodb_flush_method=O_DIRECT
Innodb_file_per_table=1
Innodb_doublewirte=1(支持双写缓存)
MyISAM I/O相关配置
delay_key_write
OFF:每次写操作后刷新键缓冲中的脏块到磁盘
ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
ALL:对所有表都使用延迟键写入
安全相关配置参数
expire_logs_days指定自动清理binlog的天数
max_allowed_packet控制MySQL可以接受的包大小
skip_name_resolve禁用DNS查找
sysdate_is_now确保sysdate()返回确定性日期
read_only 禁止非super权限的用户写权限
skip_slave_start 禁用Slave自动恢复
sql_mode设置MySQL所使用的SQL模式
strict_trans_tables
no_engine_subtitution
no_zero_date
no_zero_in_date
only_full_group_by
其他常用配置
sync_binlog控制MySQL如何向磁盘刷新binlog，默认为0
tmp_table_size和max_heap_table_size 控制内存临时表大小
max_connections控制允许的最大连接数
性能优化顺序：
数据库结构设计和SQL语句
数据库存储引擎的选择和参数配置
系统选择及优化
硬件优化
mysqlslap：
常用参数说明：
--auto-generate-sql 有系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中自增ID
--auto-generate-sql-load-type 指定测试中使用的查询类型
--auto-generate-sql-write-number 指定初始化数据时生成的数据量
--concurrency 指定并发线程的数量
--engine 指定测试表的存储引擎，可以用逗号分割多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及CPU统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-cols 指定测试表中包含的varchar类型的数量
--create-schema 指定了用于执行测试的数据库的名字
--query 用于指定自定义SQL的脚本
--only--print 并不运行测试脚本，而是把生成的脚本打印出来
sysbench:
常用参数：
--test用于指定所要执行的的测试类型，支持以下参数
Fileio 文件系统I/O性能测试
cpu cpu性能测试
memory 内存性能测试
Oltp 测试要指定具体的lua脚本
Lua脚本位于sysbench-0.5/sysbench/tests/db
--mysql-db 用于指定执行基准测试的数据库名
--mysql-table-engine 用于指定所使用的存储引擎
--oltp-table-count 执行测试的表的数量
--oltp-tables-size 指定每个表中的数据行数
--num-threads 指定测试的并发线程数量
--max-time 指定最大的测试时间
--report-interval 指定间隔多长时间输出一次统计信息
--mysql-user 指定执行测试的MySQL用户
--mysql-password 密码
prepare 用于准备测试数据
run 用于实际进行测试
cleanup 用于清理测试数据
需求分析 --> 逻辑设计 --> 物理设计 --> 维护优化
数据库设计范式：
第一范式：单一数据
第二范式：单一业务主键
varchar：
变长字符串，只占用必要的存储空间（1就是一个字符）
长度小于255占用一个额外的字节存储值，大于需要两个
选择：
使用最小符合需求的长度
使用场景：
很少被更新
多字节字符
char：
定长
使用场景：
长度接近的值（md5）
存储短字符串
经常更新的字符串
datatime：
年月日 时分秒
datatime（6）微秒
与时区无关，占用8个字节存储空间
timestamp：
存储了1970.1.1到当前时间的秒数，占用4个字节
显示依赖于所指定的时区
date：
只需要3个字节
使用日期时间函数
time
注意事项：
不要使用字符串存储日期时间数据
使用Int存储日期不如使用timestamp类型
innodb选择主键：
主键尽可能小
主键应该是顺序增长
增加插入效率
innodb的主键和业务主键可以不同
mysql复制：
不同服务器上的数据分布
利用二进制增量进行
不需要太多的带宽
但是使用基于行的复制再进行大批量的更改时会对带宽带来一定的压力
分批进行
数据的负载均衡
利用DNS轮询的方式把程序的读连接到不同的备份数据库，使用LVS、haproxy这样的代理方式
增强了数据安全性
复制不能代替备份
实现数据库高可用和故障切换
实现数据库在线升级
mysql服务层日志：
二进制日志:
记录了对mysql数据库的修改事件，包括增删改查和对表结构的修改事件
日志格式：
基于段的格式 binlog_format=STATEMENT
优点：
日志记录量相对较小，节约磁盘及网络I/OFF
只对一条记录修改或者插入，row格式所产生的日志量小于段生产的日志量
缺点：
必须要记录上下文信息
非确定函数无法复制
行的日志格式 binlog_format=ROW
Row格式可以避免MySQL复制中出现主从不一致的问题
优点：
主从复制更安全
每一行数据的修改比基于段的复制高效
缺点：
日志量大
binlog_row_image=[full|minimal|noblob]
混合日志格式 binlog_format=MIXED
根据SQL语句由系统决定基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL语句决定
建议使用混合或者row（binlog_row_image=minimal）
mysql存储引擎层日志：
基于SQL语句的复制（SBR）
二进制日志格式使用的是statement格式
基于行的复制（RBR）
二进制日志格式使用的是基于行的日志格式
混合模式
格式对复制的影响
SBR：
优点：
生成的日志量少，节约网络传输I/O
并不强制要求主从数据库的表定义完全相同
相比于基于行的复制更为灵活
缺点：
非确定性事件，无法保证主从复制数据的一致性
相对于基于行的复制方式在从上执行时需要更多的锁行
RBR：
优点：
主从数据完全一致
减少数据库锁的使用
缺点：
要求主从数据库表结构相同，否则可能会中断复制
无法再从上单独执行触发器
复制工作方式：
1.主讲变更写入二进制日志
2.从读取主的二进制日志变更并写入到relay_log中
3.再从上重放relay_log中的日志
基于段的日志是在从库上重新执行记录的SQL
基于行的日志实在从库上直接应用对数据库行的修改
基于日志点的复制配置步骤
在主DB服务器上建立复制账号
create user 'repl' @'IP段'identified by 'PassWord';
授权语句：grant replication slave on *.* to 'repl' @ 'IP段';
配置主数据库服务器
bin_log = mysql-bin
server_id = 100
配置从数据库服务器
bin_log = mysql-bin
server_id = 101
relay_log = mysql-relay-bin
log_slave_update = on[可选]
read_only = on[可选]
初始化从数据库数据
mysqldump --master-data=2 -single-transaction
xtrabackup --slave-info
启动复制连路
change master to master_host='master_host_ip',
master_user='repl',
master_password='PassWord',
master_log_file='mysql_log_file_name',
master_log_pos=4;
优点：
是Mysql最早支持的复制技术，bug相对较少
对sql查询没有任何限制
故障处理比较容易
缺点：
故障转移时重新获取新主的日志点信息比较困难
mysql复制常见问题处理
由于数据损坏所引起的主从复制错误
主库或从库意外宕机引起的错误
是用跳过二进制日志事件
注入空事物的方式先恢复中断的复制链路
在使用其他方法来对比主从服务器上的数据
主库上的二进制文件损坏
备库上的中继日志损坏
在从库上进行数据修改造成的主从复制错误
不唯一的server_id或者server_uuid
server_uuid是记录在数据目录中的auto.cnf文件中
max_allow_packet设置引起的主从复制错误
复制无法解决的问题
分单注数据库的写负载
自动进行故障转移及主从切换
提供读写分离功能
高可用
MMM：
监控和管理Mysql的主主复制拓扑，并在当前的主服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作。
功能：
监控Mysql主从复制健康情况
在主库出现宕机时进行故障转移并自动配置其他从对新主的复制
提供了主，写虚拟IP，在主从服务器出现问题时可以自动迁移虚拟Ip
部署所需要资源：
2 主db服务器
0-N 从db服务器
1 监控服务器
2（n+1） Ip地址（n为服务器数量）
1 监控用户 用于监控数据库状态的Mysql用户（replication client）
1 代理用户 用于MMM代理的Mysql用户 （super replication client process）
1 复制用户 用户配置Mysql复制的Mysql用户（replication slave）
优点：
使用perl脚本语言开发及完全开源
提供了读写虚拟IP，是服务器角色的变更跟对前端应用透明
MMM提供了从服务器的延迟监控
提供了主数据库故障转移后从服务器对新主的重新同步功能
很容易对发生故障的主数据库重新上线
提供了从服务器的延迟监控
缺点：
不支持Mysql新的复制功能
没有读负载均衡的功能
再进行主从切换时，容易造成数据丢失
mmm监控服务存在单点故障
MHA：
功能：
监控主数据库服务器是否可用，当主DB不可用，在多个从服务器中选举出新的主数据库服务器
提供了主从切换和故障转移
主从切换：
出现故障的主数据库保存二进制日志
从多个备选从服务中选举出新的备选主服务器
同步差异数据
配置步骤：
配置集群内所有主机的SSH免认证登录
安装MHA-node软件包和MHA-manager软件包
建立主从复制集群
配置MHA管理节点
使用masterha_check_ssh和masterha_check_repl对配置进行检验
启动并测试MHA服务
优点：
由perl语言开发的开源工具
可以支持基于GTID的复制模式
MHA再进行故障转移时更不易产生数据丢失
同一个监控节点可以监控多个集群
缺点：
需要编写脚本或者利用第三方工具来实现VIp的配置
MHA启动后只会对主数据库进行监控
需要基于SSH免认证配置，存在一定的安全隐患
没有提供从服务器的读负载均衡
读写分离：
程序实现读写分离：
优点：灵活、性能较高
缺点：加大工作量，容易出错
中间件实现：
优点：sql语法分析，自动完成读写分离，程序不用再做调整
缺点：查询效率有损坏，不能自动识别业务敏感度
mysql-proxy:
maxScale:
插件：Authentication认证插件、Protocal协议插件、Router路由插件、Monitor监控插件、Filter&Logging 日志和过滤插件
安装：
索引：
B-tree索引的特点：
B+树的结构存储数据
加快数据的查询速度
更适合进行范围查找
使用环境：
全值匹配的查询
匹配最左前端的查询
匹配列前缀查询
匹配范围值的查询
精确匹配做前列并范围匹配另一列
只访问索引的查询
限制：
不是按照索引最左列开始查找，则无法使用索引
使用索引时不能跳过索引中的列
Not in和<>操作无法使用索引
如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引
Hash索引的特点：
hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能使用到hash索引
对于hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码
限制：
Hash索引必须进行二次查找
Hash索引无法用于排序
不支持部分索引查找也不支持范围查询
Hash码冲突
优点：
减少需要扫描的数据量
帮助我们进行排序以避免使用临时表
把随机I/O变为顺序I/O
是不是越多越好？
会增加写操作成本
会增加查询优化器的选择时间
mysql演示数据库
http://downloads.mysql.com/docs/sakila-db.tar.gz
两个脚本文件（sakila-schema.sql、sakila-data.sql）
索引优化策略：
索引列上不能使用表达式或函数
前缀索引和索引列的选择性
create index index_name on table(col_name(n))
索引的选择性是不重复的索引值和表的记录数的比值
联合索引：
索引列的顺序：
经常会被使用的列优先
选择性高的列优先
宽度小的列优先
覆盖索引
优点：
可以优化缓存，减少磁盘IO操作
可以减少随机IO，变随机IO转变为顺序IO操作
可以避免对Innodb主索引的二次查询
可以避免MyISAm表进行系统调用
无法使用的场景：
存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%号的like查询
使用索引优化查询：
使用索引扫描来优化排序
索引的列顺序和Order By子句的顺序完全一致
索引中的所有烈的方向和Order By子句完全一致
order By中的字段全部在关联表中的第一张表
模拟Hash索引优化查询
利用索引优化锁
删除重复和冗余的索引
检查冗余索引
pt-duplicate-key-checker h=127.0.0.1
查找未被使用过的索引
慢查询：
slow_query_log、slow_query_log_file、long_query_time(秒，默认10秒，建议0.0001秒)
log_queries_not_using_indexes(是否记录未使用索引的SQL)
日志分析工具：
mysqldumpslow -s r -t 10 slow-mysql.log
-s order(c(总次数),t(总时间),l(锁的时间),r(总行数据),at,al,ar(t,l,r平均数，eg：at=总时间/总次数))
-t top(指定取前几条作为结束输出)
pt-query-digest
-explain h=端口,u=用户名,p=密码
slow-mysql.log
查询缓存对SQL性能的影响
query_cache_type 设置查询缓存是否可用
query_cache_size 设置查询缓存内存的大小
query_cahce_limit 设置值查询缓存可存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位
数据库分库分表
数据库分片前的准备
如何选择分区键
分区键要尽量避免跨分片查询的发生
分区键要尽量使分片中的数据平均
如何存储无需分片的表
每个分片中存储相同的数据
额外的节点统一存储
如何在节点上部署分片
每个分片使用单一数据库，并且数据库名也相同
如何生成全局唯一ID
使用auto_increment和auto_increment_offect参数
数据库监控：可用性、性能、主从复制、服务器资源
可用性监控：
mysqladmin -umonitor_user -p -h ping
telnet ip db_port
使用程序通过网络建立数据库连接
确认数据库是否可读写
检查数据库的read_only参数是否为off（主从切换）
建立监控表对表数据进行更改
执行简单的查询select @@version
监控数据库连接数
show variables like 'max_connections';
show global status like 'Threads_connectecd'
Threads_connectecd / max_connections > 0.8
性能监控
QPS（每秒查询数量）和TPS（每秒更新数量）
并发请求数量(并发处理数量远小于连接数)
show global status like 'Threads_running'
监控Innodb的阻塞
主从复制监控
监控复制链路的状态
监控主从复制延迟
验证主从复制数据是否一致
扫一扫
4401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
