本文主要向大家介绍了MySQL数据库之青铜到王者，快速提升你 MySQL 数据库的段位！ 通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

新的一周，老张（superZS）再次与大家见面，我们又要面临快速的生活节奏而令人厌恶的工作！现在大多数人选择放松自己的方式就是玩游戏，最为突出的可能就要属手游"王者荣耀"。

据说这款游戏上到70旬老者，下至小学生都玩，老张我也玩。段位低得可怜（PS：最近刚玩哈），刚刚白银。

当时也想让别人带带我，说你只要给多少钱，就能快速带你从倔强青铜到最强王者，但最后我在装逼和省钱的抉择上，我选择了省钱。我心想就玩一个游戏，无非你就是比我玩的时间长，有技巧，有经验嘛，但凡我多花点时间，绝对比你玩的好。

话虽这么说，老张我也不喜欢把时间浪费在游戏上，但我喜欢抽时间写博文，给大家多分享知识。因为我认为技术重在交流，沟通，只有互相多学习，才能进步得更快！既然玩个游戏都可以分段位，那么我们所工作于技术这个领域更是层级分明。

虽然我不能教大家怎么在游戏中提升自己，但我可以给大家分享让自己在数据库领域里面级别提升。做一个人人敬仰的大神，一个最强的王者！

MySQL 数据库知识脉络，大致可以分为四大模块：

● MySQL 体系结构；

● MySQL 备份恢复；

● MySQL 高可用集群；

● MySQL 优化。

从四大模块中，抽离7个部分给大家做分析

第一部分：倔强青铜篇

刚接触 MySQL 数据库的小白首先要了解，MySQL 常用操作命令以及 MySQL 各个版本的特点。从官方 5.1 到 MySQL 5.7，每个版本之间的跨度经历了哪些功能和性能上面的提升。

当然在这个阶段，我们也要学会如何安装 MySQL 数据库和一些常用命令的使用。

常用命令总结：

create database name; 创建数据库
use databasename; 选择数据库
drop database name; 直接删除数据库，不提醒
show tables; 显示表
describe tablename; 表的详细描述
select 中加上distinct去除重复字段

显示当前mysql版本和当前日期
select version(),current_date;

修改mysql中root的密码：
shell>mysql -u root -p
mysql> update user set password=password(“root123″) where user=’root’;

mysql> flush privileges  刷新权限
mysql>use dbname； 打开数据库
mysql>show databases; 显示所有数据库
mysql>show tables; 显示数据库mysql中所有的表
mysql>desc user; 显示表mysql数据库中user表的列信息）
 grant
创建一个可以从任何地方连接到服务器的一个超管账户，必须分配一个密码
mysql> grant all privileges on *.* to 'user_name'@'localhost' identified by  'password' ;
格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

删除授权：
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;

重命名表:
mysql > alter table t1 rename t2;

备份：
mysqldump -hhostname -uusername -ppassword databasename > backup.sql;

恢复：
mysql -hhostname -uusername -ppassword databasename< backup.sql;

在这里举两个典型案例，MySQL 5.6 和 MySQL 5.7 在初始化数据时候的安装差异。

MySQL 5.6：初始化数据时需要进到家目录的 script 目录下

执行：

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ 
--datadir=/data/mysql --defaults-file=/etc/my.cnf --user=mysql

此时数据库密码为空。

MySQL 5.7：初始化数据时需要进到家目录的 bin 目录下

执行：

/usr/local/mysql/bin/mysqld  --user=mysql --datadir=/data/mysql 
--basedir=/usr/local/mysql/ --initialize

已然已经废弃了使用 mysql_install_db 这个命令进行初始化数据的操作了。

注：--initialize 会自动生成密码在 error log 里面。如果加  --initialize-insecure  密码为空

第二部分：秩序白银篇

大概了解完 MySQL 的安装，我们来介绍下 MySQL 的体系结构。先看下官方版本的图：

从图中我们可以看出：MySQL 体系结构分两部分（mysql server 层 + mysql 存储引擎层）

通过一条 sql 语句进入数据库的过程细分，又可以由8个小部分组成如下图：

1-6 都是经历 mysql-server 层部分，7 是我们数据库的存储引擎层部分。因此抛出了我们要学习各个存储引擎的区别。

这里只介绍两种最长使用的 Innodb 和 Myisam 区别

1. 事务的支持不同（innodb支持事务，myisam不支持事务）

2. 锁粒度（innodb行锁应用，myisam表锁）

3. 存储空间（innodb既缓存索引文件又缓存数据文件，myisam只能缓存索引文件）

4. 存储结构
    （myisam：数据文件的扩展名为.MYD myData ，索引文件的扩展名是.MYI myIndex）
    （innodb：所有的表都保存在同一个数据文件里面 即为.Ibd）

5. 统计记录行数
    （myisam：保存有表的总行数，select count(*) from table;会直接取出出该值）
    （innodb：没有保存表的总行数，select count(*) from table；就会遍历整个表，消耗相当大）

第三部分：荣耀黄金篇

想学好数据库，就要先学习体系结构。体系结构就好比房子的地基，如果地基不稳，是盖不了高楼的。由于在 mysql server 层各个版本之间差异不大，所以我主要研究存储引擎层部分。我们来看下 Innodb 的体系结构图：

我们要学会把这体系结构分成主要的三大部分：内存组成 、线程工作、磁盘存储

在内存组成里面需要学习：数据库内存模块由 data_buffer，index_buffer，insert buffer，redo log buffer，double writer buffer 主要内存组成。

针对 Innodb 存储引擎的三大特性有：两次写，自适应哈希索引，插入缓冲；

1. double write（两次写）作用：可以保证页损坏之后，有副本直接可以进行恢复。

2. adaptive hash index（自适应哈希索引）作用：Innodb 存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引。读写速度上也有所提高。

3. insert buffer （插入缓冲）作用：针对普通索引的插入把随机 IO 变成顺序 IO，并合并插入磁盘

——主要内存模块-->磁盘的刷新机制：

a. binlog cache--->binlog 文件

通过参数 sync_binlog 控制

这个参数是对于 MySQL 系统来说是至关重要的，他不仅影响到 Binlog 对 MySQL 所带来的性能损耗，而且还影响到 MySQL 中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

● sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁盘。

● sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

在 MySQL 中系统默认的设置是 sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被丢失。

而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使系统 Crash，也最多丢失 binlog_cache 中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达5倍甚至更多。

b. redo log buffer--->redo log

通过参数 innodb_flush_log_at_trx_commit 控制

有三个参数值：
0：log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时 mysql 都会把 log buffer 的数据写入 log file，并且 flush (刷到磁盘) 中去，该模式为系统默认。

2：每次事务提交时 mysql 都会把 log buffer 的数据写入 log file，但是 flush (刷到磁盘) 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush (刷到磁盘) 操作

c. 脏页 data_buffer---->数据文件

1. 通过参数 innodb_max_dirty_pages_pct 控制：它的含义代表脏页刷新占 buffer_pool 的比例；个人建议调整为 25-50%；

2. 日志切换会产生检查点 checkpoint，可以诱发对脏页的刷新

——线程工作：

Innodb 四大 IO 线程：write thread，read thread，insert buffer thread，redo log thread

master thread 是数据库的主线程，优先级别最高，里面包含 1s 和 10s 对数据库的操作。

page cleaner thread：帮助刷新脏页的线程，5.7 版本可以增加多个。

purge thread ：删除无用 undo 页。默认1个，最大可以调整到 32。

主要的数据文件也是我们需要学习：

参数文件：MySQL 5.6 版本 my.cnf 和 MySQL 5.7 版本的 my.cnf

这里给大家两个模板：老张根据生产环境上测试而出的参数。其中根据真实内存去适当调整 innodb_buffer_pool 大小就可以了。（建议物理内存的50-80%）

[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    = 3072
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.05
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=100
wait_timeout=100
transaction_isolation = REPEATABLE-READ
innodb_additional_mem_pool_size = 16M
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

MySQL 5.7 版本的参数文件：

[client]
port    = 3306
socket    = /data/mysql/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    = /data/mysql/mysql.sock
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 500
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
interacti    

本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助，更多内容请关注职坐标数据库MySQL数据库频道！