360SDN.COM

首页/MySQL/列表

MySQL王者晋级之路(一)安装、体系结构和存储引擎【MySQL5.6,MySQL5.7,MySQL8.0】

来源:  2018-08-03 09:47:14    评论:0点击:

《MySQL王者晋级之路》张甦,电子工业出版社,2018.3

基于MySQL5.6和MySQL5.7

MySQL简介与主流分支版本

MySQL简介

MySQL AB -> Sun -> Oracle

MySQL主流分支版本

MySQL、Percona Server、MariaDB

官方版本的MySQL

MySQL 5.5 5.6 5.7 8.0

MySQL 8.0对源代码进行了重构,对MySQL Optimizer优化器进行了改进,支持隐藏索引

Percona Server

在InnoDB存储引擎的基础上,提升性能和易管理性,最后形成增强版的XtraDB引擎,可以更好地发挥硬件性能。

XtraDB-Cluster支持多点写入强同步高可用集群,实现实时同步,解决MySQL主从复制延迟问题

TokuDB存储引擎,支持数据压缩、高扩展性和优秀的查询插入性能

MariaDB

由开源社区维护,高度兼容

包含XtraDB存储引擎、TokuDB存储引擎、Spider水平分片存储引擎,支持基于表的并行复制、多源复制、Galera Cluster集群

MySQL数据库的安装、启动和关闭

下载MySQL二进制软件包

官网MySQL有四个版本:GA版、DMR版、RC版、Beta版。一般生产和测试环境使用GA版(常规可用的版本,经过bug修复测试)

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

安装前系统环境检测

1、SELinux和系统防火墙iptables需要关闭
2、I/O调度系统默认是cfq模式,强烈建议使用deadline模式
cat /sys/block/sda/queue/scheduler
#cfq完全公平队列;noop电梯式调度;deadline截止时间调度
3、swap分区的设置
不分配swap或分配4GB的swap
swappiness控制如何使用swap分区,0表示最大限度使用物理内存,然后才使用swap,可能导致内存溢出,导致MySQL被意外kill掉;100,积极使用swap,把内存数据及时搬到swap,不建议
cat /proc/sys/vm/swappiness
编辑 /etc/sysctl.conf 加入vm.swappiess的值
4、文件系统的选择 [xfs]
5、操作系统限制
ulinit -a
open files和max user processes,建议均设置为65535
vim /etc/security/limits.conf
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
6、numa需要关闭
numa --interleave=all /usr/local/mysql/bin/mysqld_safe -defaults-file=/etc/my.cnf &
关闭numa,可以更好地分配内存,不需要采用swap的方式来获取内存
可以在BIOS、操作系统或者在数据库启动过程中关闭
yum install numactl
numastat -H 、lscpu

MySQL 5.6版本的安装过程

#第一部曲
1、创建MySQL用户,指定用户组
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
2、软件包放到/usr/local目录
cd /usr/local
tar zxf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
3、做个链接,方便以后升级
ln -s mysql-5.6.40-linux-glibc2.12-x86_64/ mysql
4、给MySQL目录授权
chown -R mysql:mysql mysql-5.6.40-linux-glibc2.12-x86_64/
#第二部曲
1、创建数据目录
mkdir -p /data/mysql
2、授权
chown mysql:mysql -R /data/mysql/
#第三部曲
配置数据库配置文件
cat /etc/my.cnf 
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysql]
default-character-set = utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
open_files_limit = 65535
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.5
server-id = 1323306
log-bin = /data/mysql/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 7
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server = utf8
default-storage-engine = InnoDB
binlog_format = row
#gtid_mode = on
#log_slave_updates = 1
#enforce_gtid_consistency = 1
interactive_timeout = 300
wait_timeout = 300
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 1434M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
[mysqldump]
quick
max_allowed_packet = 32M
#一步走
初始化数据库
cd /usr/local/mysql/scripts/
#依赖 yum install perl-Data-Dumper
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql#启动MySQL
cd /usr/local/mysql/bin/
./mysqld_safe --defaults-file=/etc/my.cnf &

MySQL读取配置文件顺序
/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/my.cnf -> ~/.my.cnf
手动指定配置文件 --defaults-file
--defaults-extra-file,除读取默认配置文件,还读取额外的配置文件
--no-defaults,忽略所有配置文件
如果指定多个配置文件,以最后一次读取为准

创建密码
use mysql;
update user set password=password('root123') where user='root';
flush privileges;

关闭MySQL数据库
正常关闭
cd /usr/local/mysql/bin
./mysqladmin -uroot -proot123 shutdown
非正常关闭
kill掉MySQL进程

基础数据库

information_schema是在安装MySQL过程中的初始化阶段自动生成的,提供访问数据库元数据的所有信息,只读。常用的表有
TABLES,记录所有表基本信息,访问该表可以收集表的统计信息
PROCESSLIST,查看当前数据库连接
GLOBAL_STATUS,查看数据库运行的各种状态值
GLOBAL_VARIABLES,查看数据库中的参数
PARTITIONS,数据库中表分分区情况
INNODB_LOCKS,INNODB_TRX,INNODB_LOCK_WAITS,监控数据库中锁情况

MySQL 5.7版本的安装

唯一区别在初始化数据库那一步,因为5.7废弃了mysql_install_db这个初始化命令

cat /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
prompt = "\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log-error = /data/mysql/error.log
long_query_time = 0.5
server-id = 3306100
log-bin = /data/mysql/mysql-binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
 
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group =2
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqldump]
quick
max_allowed_packet = 32M
#初始化
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql --initialize
#--initialize参数,生成一个临时数据库初始密码,记录在log-error里面
#--initialize-insecure参数,无密码进入#启动数据库
./mysqld_safe --defaults-file=/etc/my.cnf &
#修改密码
cat /data/mysql/error.log |grep password
set password = 'root123';
alter user 'root'@'localhost' password expire never;
use mysql;
update user set host='%' where user='root';  #允许远程访问
flush privileges;#关闭数据库
./mysqladmin -uroot -proot123 shutdown

MySQL数据库root密码丢失的问题

通过添加--skip-grant-tables跳过权限表

kill掉mysql进程(共两个进程)
./mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
./mysql
设置新的密码并刷新权限
use mysql
update user set authentication_string=password('root123') where user='root';
flush privileges;
重启数据库

MySQL数据库的连接方式

TCP/IP连接,socket连接,name pipe连接(windows)

mysql -u username -p password -P port -h IP
mysql -u username -p password -S /tmp/mysql.sock

用户权限管理

root用户避免作为web连接用,超管权限的用户(root和all privileges权限用户)只能归DBA管理
创建用户时,最好保证专库专帐号
主机IP避免使用%,可以分配一个IP地址段
只读权限只能查询,不能进行DML操作
读写权限包含insert/update/delete/select,没有create/alter等建表改表权限

select user,host,authentication_string from user;
create user 用户名@主机IP identified by '密码';
#只读用户
create user 'erp_read'@'192.168.56.%' identified by 'erp123';
grant select on erp.* to 'erp_read'@'192.168.56.%' identified by 'erp123';
flush privileges;
#读写用户
create user 'erp_user'@'192.168.56.*' identified by 'erp456';
grant select,insert,update,delete on erp.* to 'erp_user'@'192.168.56.%' identified by 'erp456';
flush privileges;

MySQL体系结构与存储引擎

MySQL体系结构

MySQL Server层(连接层和SQL层)和存储引擎层

1、应用程序通过接口连接MySQL
2、连接层:通信协议、线程处理、用户名密码认证
通信协议检测客户端版本是否兼容;线程处理负载分配线程;用户名密码认证负责鉴权
3、SQL层:权限判断、查询缓存、解析器、预处理、查询优化器、缓存、执行计划
权限判断审核用户对库、表、行的权限;查询解析器解析SQL语句,判断语法;预处理对解析器无法解析的语义进行处理;优化器生成最优执行计划

Query Cache详解

Query Cache建议在生产中关闭,会造成不必要的读写。数据变化很少时可以开启。5.6版本之前默认开启,5.7之后默认关闭

show variables like "%query_cache_size%";
show variables like "%query_cache_type%";

要关闭query cache,必须一开始就把query_cache_type设置为OFF,启动后设置也会影响数据库的TPS

sysbench安装
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
命令参数:
--num-threads=N,创建测试线程的数目
--max-requests=N,请求最大数,默认10000,0代表不限制
--max-time=N,最大执行时间,单位s,默认0,不限制
--thread-stack-size=SIZE,每个线程堆栈大小,默认64KB
--init-rng=[on|off],测试开始时是否初始化随机数发生器,默认off
--test=STRING,测试项目名称
--debug=[on|off],是否显示调试信息,默认off
--validate=[on|off],在可能情况下执行验证检查,默认off
--help=[on|off],帮助信息
内建测试
fileio,测试IO
cpu,测试cpu
memory,测试内存
threads,测试线程
mutex,测试互斥性能
存储引擎

主要存储引擎有:InnoDB、MyISAM、Memory、blackhole、TokuDB、MariaDB columnstore

InnoDB体系结构
数据库和数据库实例

MySQL数据库是一个单进程多线程模型的数据库。InnoDB体系结构实际由内存结构、线程、磁盘文件三层组成

InnoDB存储结构

InnoDB逻辑存储单元分为表空间、段、区、页
层级关系为:tablespace -> segment -> extent(64 page,1MB) -> page

1、表空间
所有数据都是存储在表空间中的。表空间区分为系统表空间和独立表空间
ibdata1为系统表空间,安装过程中初始化就是在创建ibdata1表空间文件,它会存储所有数据的信息及回滚(undo)信息。undo可以单独设置存储位置,从ibdata1中独立出来。innodb_data_file_path定义系统表空间路径、初始大小、自动扩展策略。默认自动扩展大小为64MB (show variables like "%auto%"; -> innodb_autoextend_increment 64)
(show variables like "%innodb_data%";) innodb_data_file_path = ibdata1:1G:autoextend
默认ibdata1大小为10MB,在遇到高并发事务时,会受到影响,建立初始值大小设置为1GB
独立表空间,设置innodb_file_per_table=1即可,每个表都有自己的表空间。独立表空间存储对应表的B+树数据、索引和插入缓冲等,其余信息还在存储于默认表空间中。

独立表空间即每个表都有自己的表空间,可以实现表空间的转移,回收表空间也很方便,使用alter table table_name engine=innodb或pt-online_schema_change即可。但不好的地方在于每个表都有.frm和.ibd两个文件,若单表增长过快易出现性能问题
共享表空间数据和文件放在一起,但无法在线回收,回收需将全部InnoDB表中数据备份,删除原表,再把数据导回到与原表结构一样的新表中。统计分析、日志类系统不适合用共享表空间
5.7中还有临时表空间temporary tablespace 、通用表空间general tablespace
临时表空间
innodb_temp_data_file_path=ibtmp1:12M:autoextend
临时表的相关检索信息保存在information_schema的innodb_temp_table_info中
通用表空间
多个表放在同一个表空间中,很少使用

2、段
表空间由段组成,可以将一个表理解为一个段。有数据段、回滚段、索引段等,每个段由N个区和32个零散的页组成,段空间的扩展是以区为单位进行的。创建一个索引的同时会创建2个段,分别是非叶子节点和叶子节点段。一个表有4个段,是索引个数的2倍

3、区
区由连续的页组成,是物理上连续分配的一段空间,大小固定为1MB

4、页
通常一个区由64个连续的页组成,页默认大小16KB 64x16KB=1MB。5.6开始可以调小页的大小,如8KB/4KB,5.7开始可以调大,如32KB/64KB
通常page会预留1/16的空间用于更新数据,一个page最少可以存2行数据。保证B+树节点是双向链表结构

5、行
有两种文件格式:Antelope和Barracuda。在Antelope下,有compact和redundant两种记录格式;在Barracuda下,有compressed和dynamic两种记录格式
5.7默认dynamic行记录格式和Barracuda文件格式
show table status; show variables like "%row_format%"; 里面的Row_format
show variables like "%innodb_file%"; innodb_file_format=Barracuda
innodb_default_row_format是5.7新增的
生产环境建议使用dynamic。redundant会消耗更多的存储空间;compressed会多cpu消耗

内存结构

分为SGA系统全局区和PGA程序缓存区
show variables like "%buffer%";

SGA系统全局区:
1、innodb_buffer_pool,缓存innodb表数据、索引、插入缓冲、数据字典等
2、innodb_log_buffer,事务在内存中的缓冲,即redo log buffer的大小
3、query cache,高速查询缓存,建议关闭
4、key_buffer_size,只用于myisam,缓存myisam的索引,不缓存数据
5、innodb_additional_mem_pool_size,保存数据字典信息和其他内部数据结构的内存池大小,5.7.4中被移除

PGA程序缓存区:
1、sort_buffer_size,SQL语句在内存中的临时排序
2、join_buffer_size,表连接,用于BKA
3、read_buffer_size,表顺序扫描的缓存,只应用于myisam
4、read_rnd_buffer_size,随机读缓冲区大小,用于做mrr

特殊:
tmp_table_size,SQL语句在排序或者分组时没有用到索引,就会使用临时表
max_heap_table_size,管理heap、memory存储引擎表

生产中建议把tmp_buffer_size和max_heap_table_size设置成一样,若不一致,会按照两者中小的值限制。且不能太小,太小会报错:converted heap to myisam
show variables like "%heap%";
show variables like "%tmp_table_size%";

针对tmp还有两个重要的参数:default_tmp_storage_engine临时表默认存储引擎和internal_tmp_disk_storage_engine磁盘上临时表管理(决定create temporary table)

Buffer状态及其链表结构

buffer三种状态:
free buffer,从未被使用
clean buffer,内存中的buffer数据和磁盘上page一致
dirty buffer,内存中新写入的数据,还没有写入到磁盘
三种不同的buffer衍生出三条链表:
free list,free状态的buffer
lru list,把最近最少使用的clean buffer串联起来,释放出free buffer
flush list,将dirty buffer串联起来,方便刷新线程把脏数据刷到磁盘,推进checkpoint lsn,在实例崩溃后,可以快速恢复

各刷新线程及作用

master thread线程,后台线程中的主线程,优先级最高,内部有四个循环:主循环loop、后台循环background loop、刷新循环flush loop、暂停循环suspend loop。根据数据运行状态在四个循环中切换。
loop主循环:
每1s操作:
日志缓冲刷新到磁盘;刷新脏页到磁盘;执行合并插入缓冲操作;产生checkpoint;清除无用table cache;若当前无用户活动,可能切换到background loop
每10s操作:
日志缓冲刷新到磁盘;执行合并插入缓冲操作;刷新脏页到磁盘;删除无用undo页;产生checkpoint

四大I/O线程,read thread、write thread、redo log thread、change buffer thread。
redo log thread把日志缓冲中内容刷新到redo log文件中;change buffer thread把插入缓冲内容刷新到磁盘;
read/write thread是读写请求线程,默认均为4个,若使用高速磁盘,可以适当调大
innodb_write_io_threads = 8
innodb_read_io_threads = 8
page cleaner thread,负责脏页刷新,5.7后可以增加多个。innodb_page_cleaners = 4
purge thread,负责删除无用的undo页,个数默认是1个,最大可以调整到32个。innodb_purge_threads = 4
checkpoint线程,在redo log发生切换时,执行checkpoint
error monitor thread,负责数据库报错
lock monitor thread,负责锁的监控

内存刷新机制

日志先行策略,即一条DML进入数据库后,先写日志,再写数据文件

1、redo log
重做日志,记录事务操作的变化,记录的是数据修改后的值。默认至少有两个redo log文件:ib_logfile0 ib_logfile1
redo log是顺序写、循环写。写满日志文件会执行切换操作,并执行checkpoint,触发脏页刷新。
redo log buffer -> ib_logfile
通过innodb_flush_log_at_trx_commit控制,0,每隔1秒刷一次,但在事务提交时并不触发;1,每次事务提交时刷新到磁盘,最安全的模式,在主机断电、crash下不丢失任何提交的数据;2,每次事务提交刷一次,但不同时刷盘。0性能最好,1安全性最高,2介于前两者间。0会最多丢失1秒的数据
master thread每秒刷新,redo log buffer使用超过1半会触发刷新

2、binlog
二进制日志,用于备份恢复和主从复制 binlog cache -> binlog
通过sync_binlog参数决定,取0时系统自行刷新磁盘,或者cache满了后再同步到磁盘;取n时每执行n次事务提交,同步刷新一次
将sync_binlog和innodb_flush_log_at_trx_commit均设置为1,被称为数据库的双一模式,可确保数据更加安全

MySQL两阶段提交:prepare和commit
准备阶段prepare,事务SQL->redo log buffer->事务准备标记->redo log
提交阶段commit,binlog->磁盘->redo log中事务提交标记-redo log

脏页刷新
刷新条件:
1)ib_logfile写满后,执行checkpoint,触发刷新
2)通过innodb_max_dirty_pages_pct参数控制,buffer pool中dirty page百分比。innodb_max_dirty_pages_pct = 50,生产环境可设置为25%~50%间,默认75%
3)由innodb_adaptive_flushing参数控制,影响每秒刷新脏页数目。默认是开启的,innodb_adaptive_flushing = ON

InnoDB三大特性

插入缓冲change buffer、两次写double write、自适应哈希索引adaptive hash index

1、插入缓冲
把普通索引上的DML操作从随机IO变成顺序IO
innodb_change_buffer_max_size=25,占innodb_buffer_pool的最大比例,默认为25%。建议调整为50
innodb_change_buffering:类型,建议使用默认all
all,缓冲全部insterts、delete标记操作和purges操作
none,关闭
inserts,deletes,
changes,未进行实际insert和delete,只是标记,等待后续purge
purges,缓冲后台进程的purges(物理删除)操作

2、两次写
保证写入的安全性,防止宕机时,数据页发生partial page write问题。页的一个副本,先通过副本把页还原出来,再通过redo log进行恢复、重做
双写缓冲位于系统表空间中的存储区域

3、自适应哈希索引
如果InnoDB发现查询可以通过建立哈希索引得到优化,会自动完成这件事。
可以通过innodb_adaptive_hash_index参数控制,默认是开启的
自适应哈希索引搜索是分区的,通过innodb_adaptive_hash_index_parts参数控制,默认为8,最大可以设置到512
通过设置分区,可以降低争用,提高并发性
可以通过show engine innodb status\G中SEMAPHORES部分监控

转载请注明: MySQL王者晋级之路(一)安装、体系结构和存储引擎

为您推荐

友情链接 |九搜汽车网 |手机ok生活信息网|ok生活信息网|ok微生活
 Powered by www.360SDN.COM   京ICP备11022651号-4 © 2012-2016 版权