牛皮了！7000字MySQL学习笔记，从入到放弃

 

MySQL数据库简介

MySQL近两年一直稳居第二，随时有可能超过Oracle计晋升为第一名，因为MySQL的性能一直在被优化，同时安全机制也是逐渐成熟，更重要的是开源免费的。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

MySQL InnoDB存储引擎

• 存储引擎InnoDB是目前MySQL版本默认的存储引擎，也是MySQL推荐使用的存储引擎，是集高可靠性和高性能于一身的存储引擎。
• 在MySQL5.7版本中，除非在配置文件中显示指定default storage engine或者创建表时显示使用engine=语句指定其它的存储引擎，否则默认都是InnoDB。

InnoDB存储引擎的优势：

• DML语句支持事务功能，保证ACID特性
• 性能锁的使用保证了高并发的属性
• InnoDB对有主键的表会依据主键优化查询性能，也称聚簇索引，将所有数据存储在聚簇索引上以减少对主键查询的IO消耗
• 为保证数据的一致性，InnoDB还支持外键属性，确保有外键约束的表之间不会有不一致的数据
• 当服务器硬件或者软件故障导致MySQL重启后，InnoDB会自动识别已经在故障之前提交的数据，并回退所有故障时未提交的数据，最大限度的保护数据不会丢失(crash recovery)

1、事物（Transaction）
2、MVCC（多版本并发控制）
3、行级锁（Row-level Lock）
4、支持外键
5、ACSR（Auto Crrash safe Recovery）自动的故障安全恢复
6、支持热备份

MySQL复制集群原理与实战

MySQL复制有两种方法：

• 传统方式：基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以 应用来达到主从同步的目的。
• Gtid方式：global transaction identifiers是基于事务来复制数据，因此也就不 依赖日志文件位置，同时又能更好的保证主从库数据一致性。

数据备份多种方式：

• 物理备份是指通过拷贝数据库文件的方式完成备份，这种备份方式适用于数据库很大，数据重要且需要快速恢复的数据库
• 逻辑备份是指通过备份数据库的逻辑结构（create database/table语句)和数据内容（insert语句或者文本文件）的方式完成备份。这种备份方式适用于数据库不是很大，或者你需要对导出的文件做一定的修改，又或者是希望在另外的不同类型服务器上重新建立此数据库的情况
• 通常情况下物理备份的速度要快于逻辑备份，另外物理备份的备份和恢复粒度范围为整个数据库或者是单个文件。对单表是否有恢复能力取决于存储引擎，比如在MyISAM存储引擎下每个表对应了独立的文件，可以单独恢复；但对于InnoDB存储引擎表来说，可能每个表示对应了独立的文件，也可能表使用了共享数据文件
• 物理备份通常要求在数据库关闭的情况下执行，但如果是在数据库运行情况下执行，则要求备份期间数据库不能修改
• 逻辑备份的速度要慢于物理备份，是因为逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式；通常输出的备份文件大小也要比物理备份大；另外逻辑备份也不包含数据库的配置文件和日志文件内容；备份和恢复的粒度可以是所有数据库，也可以是单个数据库，也可以是单个表；逻辑备份需要在数据库运行的状态下执行；它的执行工具可以是mysqldump或者是select … into outfile两种方式

MySQL复制有多种类型：

• 异步复制：一个主库，一个或多个从库，数据异步同步到从库。
• 同步复制：在MySQL Cluster中特有的复制方式。
• 半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至 少有一个从库已经收到该事务并日志记录下来。
• 延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间， 即保证数据延迟至少是这个参数。

MySQL高可用架构设计与实战

MHA

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点。

• MHA Manager: 可以单独部署在一*立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。
• MHA Node: 行在每台MySQL服务器上。
• MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

MGR

• Mysql Group Replication(MGR)是从5.7.17版本开始发布的一个全新的高可用和高扩张的MySQL集群服务。
• 高一致性，基于原生复制及paxos协议的组复制技术，以插件方式提供一致数据安全保证；
• 高容错性，大多数服务正常就可继续工作，自动不同节点检测资源征用冲突，按顺序优先处理，内置动防脑裂机制；
• 高扩展性，自动添加移除节点，并更新组信息；
• 高灵活性，单主模式和多主模式。单主模式自动选主，所有更新操作在主进行；多主模式，所有server同时更新。

MySQL性能优化

MySQL索引原理：

• 顾名思义，B-tree索引使用B-tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引空间更小，而InnoDB则按照原数据格式存储，且MyISAM索引在索引中记录了对应数据的物理位置，而InnoDB则在索引中记录了对应的主键数值。B-Tree通常意味着所有的值都是按顺序存储，并且每个叶的叶到根的距离相同。
• B-Tree索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。
• 全键值匹配：和索引中的所有列都进行匹配，比如查找姓名为zhang san，出生于1982-1-1的人
• 匹配最左前缀：和索引中的最左边的列进行匹配，比如查找所有名为zhang的人
• 匹配列前缀：匹配索引最左边列的开头部分，比如查找所有以z开头的姓名的人
• 匹配范围值：匹配索引列的范围区域值，比如查找姓在li和wang之间的人
• 精确匹配左边列并范围匹配右边的列：比如查找所有姓为Zhang，且名字以K开头的人
• 只访问索引的查询：查询结果完全可以通过索引获得，也叫做覆盖索引，比如查找所有名为zhang的人的姓名

MySQL表分区介绍：

• 可以允许在⼀个表⾥存储更多的数据，突破磁盘限制或者⽂件系统限制。
• 对于从表⾥将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可。
• 对某些查询和修改语句来说，可以⾃动将数据范围缩⼩到⼀个或⼏个表分区上，优化语句执⾏效率。⽽且可以通过显示指定表分区来执⾏语句，⽐如 select * from temp partition(p1,p2) where store_id < 5;
• 表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储，这个规则就叫做分区函数，可以有不同的分区规则。
• MySQL5.7版本可以通过show plugins语句查看当前MySQL是否⽀持表分区功能。
• MySQL8.0版本移除了show plugins⾥对partition的显示，但社区版本的表分区功能是默认开启的。
• 但当表中含有主键或唯⼀键时，则每个被⽤作分区函数的字段必须是表中唯⼀键和主键的全部或⼀部分，否则就⽆法创建分区表。

MySQL分库分表

• 能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。
• 分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进 行扩容，增加分片数量。
• 分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访 问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片， 一致性Hash分片，这几种分片都有利于扩容。
• 尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题。
• 查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量 带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。

MySQL数据库读写分离高可用

海量数据的存储和访问成为了系统设计的瓶颈问题，日益增长的业务数据，无疑对数据库造成了相当大的负载，同时对于系统的稳定性和扩展性提出很高的要求。随着时间和业务的发展，数据库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作的开销也会越来越大；另外，无论怎样升级硬件资源，单台服务器的资源（CPU、磁盘、内存、网络IO、事务数、连接数）总是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。分表、分库和读写分离可以有效地减小单台数据库的压力。

MySQL性能监控

MySQL性能监控的指标大体可以分为以下4大类：

• 查询吞吐量
• 查询延迟与错误
• 客户端连接与错误
• 缓冲池利用率

对于MySQL性能监控，官方也提供了相关的服务插件：MySQL-Percona，下面简单介绍一下插件的安装

[root@db01 ~]# yum -y install php php-mysql
[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@db01 ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing...
   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates

最后，可以配合其它监控工具来实现对MySQL的性能监控。

MySQL服务器配置插件：

• 修改php脚本连接MySQL的monitor@localhost用户
• 修改MySQL的sock文件路径

[root@db01 ~]# sed -i '30c $mysql_user = "monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '31c $mysql_pass = "123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[root@db01 ~]# sed -i '33c $mysql_socket = "/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

测试是否可用（ 可以从MySQL中获取到监控值 ）

[root@db01 ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:12

# 确保当前文件的 属主 属组 是zabbix，否则zabbix监控取值错误。
[root@db01 ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt
4.0K -rw-rw-r-- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txt

移动zabbix-agent配置文件到 /etc/zabbix/zabbix_agentd.d/目录

[root@db01 ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[root@db01 ~]# systemctl restart zabbix-agent.service

导入并配置Zabbix模板与主机：

默认模板监控时间为 5分钟 （ 当前测试修改为 30s） 同时也要修改Zabbix模板时间

# 如果要修改监控获取值的时间不但要在zabbix面板修改取值时间，bash脚本也要修改。
[root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then   
# 这个 300 代表 300s 同时也要修改。

默认模板版本为 2.0.9，无法在4.0版本使用，可以先从3.0版本导出，然后再导入4.0版本 。

其实，在实际生产过程中，还是有相关的专业监控数据库的第三方开源软件的，LZ之前也写过相关的文章

MySQL用户行为安全

• 假设这么一个情况，你是某公司mysql-DBA，某日突然公司数据库中的所有被人给删了。
• 尽管有数据备份，但是因服务停止而造成的损失上千万，现在公司需要查出那个做删除操作的人。
• 但是拥有数据库操作权限的人很多，如何排查，证据又在哪？
• 是不是觉得无能为力？
• mysql本身并没有操作审计的功能，那是不是意味着遇到这种情况只能自认倒霉呢？

本文地址：https://blog.csdn.net/yelvgou9995/article/details/107166536