🍊 MySQL调优
🎉 表结构设计
在进行数据库设计时，开发者需要关注表的规划。首先，开发者要了解MySQL数据库的页大小。当表中的单行数据达到16KB时，这意味着表中只能存储一条数据，这对于数据库来说是不合理的。MySQL数据库将数据从磁盘读取到内存，它使用磁盘块作为基本单位进行读取。如果一个数据块中的数据一次性被读取，那么查询效率将会提高。
以InnoDB存储引擎为例，它使用页作为数据读取单位。页是磁盘管理的最小单位，默认大小为16KB。由于系统的磁盘块存储空间通常没有这么大，InnoDB在申请磁盘空间时会使用多个地址连续的磁盘块来达到页的大小16KB。
查询数据时，一个页中的每条数据都能帮助定位到数据记录的位置，从而减少磁盘I/O操作，提高查询效率。InnoDB存储引擎在设计时会将根节点常驻内存，尽力使树的深度不超过3。这意味着在查询过程中，I/O操作不超过3次。树形结构的数据可以让系统高效地找到数据所在的磁盘块。
在这里讨论一下B树和B+树的区别。B树的结构是每个节点既包含key值也包含value值，而每个页的存储空间是16KB。如果数据较大，将会导致一个页能存储数据量的数量很小。相比之下，B+树的结构是将所有数据记录节点按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息。这样可以大大加大每个节点存储的key值数量，降低B+树的高度。
通过了解MySQL数据库底层存储的原理和数据结构，开发者在设计表时应该尽量减少单行数据的大小，将字段宽度设置得尽可能小。
在设计表时，开发者要注意以下几点以提高查询速度和存储空间利用率：
(1)避免使用text、Blob、Clob等大数据类型，它们占用的存储空间更大，读取速度较慢。
(2)尽量使用数字型字段，如性别字段用0/1的方式表示，而不是男女。这样可以控制数据量，增加同一高度下B+树容纳的数据量，提高检索速度。
(3)使用varchar/nvarchar代替char/nchar。变长字段存储空间较小，可以节省存储空间。
(4)不在数据库中存储图片、文件等大数据，可以通过第三方云存储服务存储，并提供图片或文件地址。
(5)金额字段使用decimal类型，注意长度和精度。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。
(6)避免给数据库留null值。尤其是时间、整数等类型，可以在建表时就设置非空约束。NULL列会使用更多的存储空间，在MySQL中处理NULL值也更复杂。为NULL的列可能导致固定大小的索引变成可变大小的索引，例如只有整数列的索引。
🎉 建索引
在建立索引时，需要权衡数据的维护速度和查询性能。以下是一些关于如何确定是否为表中字段建立索引的示例：
(1)对于经常修改的数据，建立索引会降低数据维护速度，因此不适合对这些字段建立索引，例如状态字段。
(2)对于性别字段，通常用0和1表示，但由于其区分度不高（100万用户中90万为男性，10万为女性），因此一般不需要建立索引。然而，如果性别字段的区分度非常高（例如90万男性和10万女性），而且该字段不经常更改，则可以考虑为该字段建立索引。
(3)可以在where及order by涉及的列上建立索引。
(4)对于需要查询排序、分组和联合操作的字段，适合建立索引，以提高查询性能。
(5)索引并非越多越好，一个表的索引数最好不要超过6个。当为多个字段创建索引时，表的更新速度会减慢，因此应选择具有较高区分度且不经常更改的字段创建索引。
(6)尽量让字段顺序与索引顺序一致，复合索引中的第一个字段作为条件时才会使用该索引。
(7)遵循最左前缀原则：尽量确保查询中的索引列按照最左侧的列进行匹配。例如如果为(a, b)和(c, d)创建了联合索引，查询示例，代码如下：
SELECT * FROM table WHERE a = ? AND b = ?
将使用索引，以下查询，代码如下：
SELECT * FROM table WHERE c = ? AND d = ?
将无法使用索引。
🎉 SQL优化
为了优化SQL语句，需要了解数据库的架构、索引、查询优化器以及各种SQL执行引擎的机制等技术知识。
📝 SQL编写
在编写SQL语句时，开发者需要注意一些关键点以提高查询性能。以下是一些建议：
(1)避免在WHERE子句中对查询的列执行范围查询（如NULL值判断、!=、<>、or作为连接条件、IN、NOT IN、LIKE模糊查询、BETWEEN）和使用“=”操作符左侧进行函数操作、算术运算或表达式运算，因为这可能导致索引失效，从而导致全表扫描。
(2)对于JOIN操作，如果数据量较大，先分页再JOIN可以避免大量逻辑读，从而提高性能。
(3)使用COUNT()可能导致全表扫描，如有WHERE条件的SQL，WHERE条件字段未创建索引会进行全表扫描。COUNT()只统计总行数，聚簇索引的叶子节点存储整行记录，非聚簇索引的叶子节点存储行记录主键值。非聚簇索引比聚簇索引小，选择最小的非聚簇索引扫表更高效。
(4)当数据量较大时，查询只返回必要的列和行，LIMIT 分页限制返回的数据，减少请求的数据量，插入建议分批次批量插入，以提高性能。
(5)对于大连接的查询SQL，由于数据量较多、又是多表，容易出现整个事务日志较大，消耗大量资源，从而导致一些小查询阻塞，所以优化方向是将它拆分成单表查询，在应用程序中关联结果，这样更利于高性能可伸缩，同时由于是单表减少了锁竞争效率上也有一定提升。
(6)尽量明确只查询所需列，避免使用SELECT *。SELECT *会导致全表扫描，降低性能。若必须使用SELECT ，可以考虑使用MySQL 5.6及以上版本，因为这些版本提供了离散读优化（Discretized Read Optimization），将离散度高的列放在联合索引的前面，以提高性能。
索引下推（ICP，Index Condition Pushdown）优化：ICP优化将部分WHERE条件的过滤操作下推到存储引擎层，减少上层SQL层对记录的索取，从而提高性能。在某些查询场景下，ICP优化可以大大减少上层SQL层与存储引擎的交互，提高查询速度。
多范围读取（MRR，Multi-Range Read）优化：MRR优化将磁盘随机访问转化为顺序访问，提高查询性能。当查询辅助索引时，首先根据结果将查询得到的索引键值存放于缓存中。然后，根据主键对缓存中的数据进行排序，并按照排序顺序进行书签查找。
这种顺序查找减少了对缓冲池中页的离散加载次数，可以提高批量处理对键值查询操作的性能。在编写SQL时，使用EXPLAIN语句观察索引是否失效是个好习惯。索引失效的原因有以下几点：
(1)如果查询条件中包含OR，即使其中部分条件带有索引，也无法使用。
(2)对于复合索引，如果不使用前列，后续列也无法使用。
(3)如果查询条件中的列类型是字符串，则在条件中将数据使用引号引用起来非常重要，否则索引可能失效。
(4)如果在查询条件中使用运算符（如+、-、、/等）或函数（如substring、concat等），索引将无法使用。
(5)如果MySQL认为全表扫描比使用索引更快，则可能不使用索引。在数据较少的情况下尤其如此。
📝 SQL优化工具
常用的SQL优化方法包括：业务层逻辑优化、SQL性能优化、索引优化。
业务层逻辑优化：开发者需要重新梳理业务逻辑，将大的业务逻辑拆分成小的逻辑块，并行处理。这样可以提高处理效率，降低数据库的访问压力。
SQL性能优化：除了编写优化的SQL语句、创建合适的索引之外，还可以使用缓存、批量操作减少数据库的访问次数，以提高查询效率。
索引优化：对于复杂的SQL语句，人工直接介入调节可能会增加工作量，且效果不一定好。开发者的索引优化经验参差不齐，因此需要使用索引优化工具，将优化过程工具化、标准化。最好是在提供SQL语句的同时，给出索引优化建议。
📝 慢SQL优化
影响程度一般的慢查询通常在中小型企业因为项目赶进度等问题常被忽略，对于大厂基本由数据库管理员通过实时分析慢查询日志，对比历史慢查询，给出优化建议。
影响程度较大的慢查询通常会导致数据库负载过高，人工故障诊断，识别具体的慢查询SQL，及时调整，降低故障处理时长。
当前未被定义为慢查询的SQL可能随时间演化为慢查询，对于核心业务，可能引发故障，需分类接入：
(1)未上线准慢查询：需要通过发布前集成测试流水线，通常都是经验加上explain关键字识别慢查询，待解决缺陷后才能发布上线。
(2)已上线准慢查询：表数据量增加演变为慢查询，比较常见，通常会变成全表扫描，开发者可以增加慢查询配置参数log_queries_not_using_indexes记录至慢日志，实时跟进治理。
🎉 数据分区
在面对大量数据时，分区可以帮助提高查询性能。分区主要分为两类：表分区和分区表。
📝 表分区
表分区是在创建表时定义的，需要在表建立的时候创建规则。如果要修改已有的有规则的表分区，只能新增，不能随意删除。表分区的局限性在于单个MySQL服务器支持1024个分区。
📝 分区表
当表分区达到上限时，可以考虑垂直拆分和水平拆分。垂直拆分将单表变为多表，以增加每个分区承载的数据量。水平拆分则是将数据按照某种策略拆分为多个表。
垂直分区的优点是可以减少单个分区的数据量，从而提高查询性能。但缺点是需要考虑数据的关联性，并在SQL查询时进行反复测试以确保性能。
对于包含大文本和BLOB列的表，如果这些列不经常被访问，可以将它们划分到另一个分区，以保证数据相关性的同时提高查询速度。
📝 水平分区
随着数据量的持续增长，需要考虑水平分区。水平分区有多种模式，例如：
(1)范围（Range）模式：允许DBA将数据划分为不同的范围。例如DBA可以将一个表按年份划分为三个分区，80年代的数据、90年代的数据以及2000年以后的数据。
(2)哈希（Hash）模式：允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如DBA可以建立一个根据主键进行分区的表。
(3)列表（List）模式：允许系统通过DBA定义列表的值所对应行数据进行分割。例如DBA建立了一个横跨三个分区的表，分别根据2021年、2022年和2023年的值对应数据。
(4)复合模式（Composite）：允许将多个模式组合使用，如在初始化已经进行了Range范围分区的表上，可以对其中一个分区再进行Hash哈希分区。
🎉 灾备处理
在MySQL中，冷热备份可以帮助 开发者在不影响性能的情况下确保数据的安全性。
📝 冷备份
当某些数据不再需要或不常访问时，可以考虑进行冷备份。冷备份是在数据库关闭时进行的数据备份，速度更快，安全性也相对更高。例如您可以将一个不再需要的月度报告数据备份到外部存储设备，以确保在需要时可以轻松访问这些数据。
📝 热备份
对于需要实时更新的数据，可以考虑热备份。热备份是在应用程序运行时进行的数据备份，备份的是数据库中的SQL操作语句。例如您可以将用户的购物记录备份到一个在线存储服务中，以便在需要时可以查看这些数据。
📝 冷备份与热备份的权衡
(1)冷备份速度更快，因为它不涉及应用程序的运行，但可能需要外部存储设备。
(2)热备份速度较慢，因为它涉及应用程序的运行和数据库操作的记录。
(3)冷备份更安全，因为它在数据库关闭时进行，不受应用程序影响。
(4)热备份安全性稍低，因为它在应用程序运行时进行，需要保持设备和网络环境的稳定性。
📝 备份注意事项
(1)备份过程中要保持设备和网络环境稳定，避免因中断导致数据丢失。
(2)备份时需要仔细小心，确保备份数据的正确性，以防止恢复过程中出现问题。
(3)热备份操作要特别仔细，备份SQL操作语句时不能出错。
总之，通过对冷热数据进行备份，可以在不影响应用程序性能的情况下确保数据的安全性。在实际应用中，应根据数据的需求和业务场景选择合适的备份策略。
🎉 高可用
在生产环境中，MySQL的高可用性变得越来越重要，因为它是一个核心的数据存储和管理系统，任何错误或中断都可能导致严重的数据丢失和系统瘫痪。因此，建立高可用的MySQL环境是至关重要的。
📝 MMM
用于监控和故障转移MySQL集群。它使用虚拟IP（VIP）机制实现集群的高可用。集群中，主节点通过一个虚拟IP地址提供数据读写服务，当出现故障时，VIP会从原主节点漂移到其他节点，由这些节点继续提供服务。双主故障切换（MMM）的主要缺点是故障转移过程过于简单粗暴，容易丢失事务，因此建议采用半同步复制以降低失败概率。
📝 MHA
它是一种用于故障切换的工具，能在30秒内完成故障切换，并在切换过程中最大程度地保证数据一致性。高可用性与可伸缩性（MHA）主要监控主节点的状态，当检测到主节点故障时，它会提升具有最新数据的从节点成为新的主节点，并通过其他从节点获取额外信息来避免数据一致性方面的问题。MHA可以单独部署，分为Manager节点和Node节点，分别部署在单独的机器上和每台MySQL机器上。Node节点负责解析MySQL日志，而Manager节点负责探测Node节点并判断各节点的运行状况。当检测到主节点故障时，Manager节点会直接提升一个从节点为新主节点，并让其他从节点挂载到新主节点上，实现完全透明。为了降低数据丢失的风险，建议使用MHA架构。
📝 MGR
它是MySQL官方在5.7.17版本中正式推出的一种组复制机制，主要用于解决异步复制和半同步复制中可能产生的数据不一致问题。组复制（MGR）由若干个节点组成一个复制组，事务提交后，必须经过超过半数节点的决议并通过后才能提交。引入组复制主要是为了解决传统异步复制和半同步复制可能出现的数据不一致问题。组复制的主要优点是基本无延迟，延迟较异步复制小很多，且具有数据强一致性，可以保证事务不丢失。然而，它也存在一些局限性：
(1)仅支持InnoDB存储引擎。
(2)表必须具有主键。
(3)仅支持GTID模式，日志格式为row格式。
🎉 异常发现处理
在使用MySQL时，可能会遇到各种异常情况，例如连接错误、查询错误、数据删除错误等等。在处理这些异常情况时，开发人员需要了解异常的原因和处理方法，以便及时排除问题，保障系统的稳定性和可靠性。
📝 数据库监控
及时将数据库异常通过短信、邮件、微信等形式通知给管理员，并且可以将数据库运行的实时指标统计分析图表显示出来，便于更好地对数据库进行规划和评估，目前市面上比较主流的数据库监控工具有Prometheus + Grafana + mysqld_exporter（比较受欢迎）、SolarWinds SQL Sentry、Database Performance Analyzer、OpenFalcon。
📝 数据库日志
在MySQL中，有一些关键的日志可以用作异常发现并通过这些日志给出解决方案：
(1)重做日志（redo log）：记录物理级别的页修改操作，例如页号123、偏移量456写入了“789”数据。可以通过“show global variables like ‘innodb_log%’;”命令查看。主要用于事务提交时保证事务的持久性和回滚。
(2)回滚日志（undo log）：记录逻辑操作日志，例如添加一条记录时会记录一条相反的删除操作。可以通过“show variables like ‘innodb_undo%’;”命令查看。主要用于保证事务的原子性，在需要时回滚事务。
(3)变更日志/二进制日志（bin log）：记录数据库执行的数据定义语句（DDL）和数据操作语句（DML）等操作。例如数据库意外挂机时，可以通过二进制日志文件查看用户执行的命令，并根据这些操作指令恢复数据库或将数据复制到其他数据库中。可以通过“show variables like ‘%log_bin%’;”命令查看。主要用于性能优化和复制数据。
(4)慢查询日志：记录响应时间超过指定阈值的SQL语句。主要用于性能优化。可以通过“show variables like ‘%slow_query_log%’;”命令查看。
(5)错误日志：记录MySQL服务启动、运行、停止时的诊断信息、错误信息和警告提示。主要用于排查MySQL服务出现异常的原因。可以通过“SHOW VARIABLES LIKE ‘log_err%’;”命令查看。
(6)通用查询日志：记录用户的所有操作，无论是所有的SQL语句还是调整MySQL参数或者启动和关闭MySQL都会记录。可以还原操作的场景。通过SHOW VARIABLES LIKE ‘%general%’;命令查看。
(7)中继日志(relay log)：只存在主从数据库的从数据库上，用于主从同步，可以在xx-relaybin.index索引文件和-relaybin.0000x数据文件查看。
(8)数据定义语句日志(ddl.log)：记录数据定义的SQL，比如ALTER TABLE。
(9)processlist日志：查看正在执行的sql语句。
(10) innodb status日志：查看事务、锁、缓冲池和日志文件，主要用于诊断数据库性能。
📝 数据库巡检
巡检工作保障系统平稳有效运行，比如飞机起飞巡检保证起飞后能够正常工作。巡检工作主要由数据库管理员和后端开发工程师负责。
数据库管理员主要负责处理数据库基础功能/高可用/备份/中间件/报警组件、集群拓扑、核心参数等集群层面的隐患、服务器硬件层面隐患，对于磁盘可用空间预测等范围。
后端开发工程师主要负责库表设计缺陷、数据库使用不规范等引起的业务故障或性能问题的隐患，定期采集整型字段值有没有超过最大值，因为整型类型的字段保存的数值有上限。对于读写情况需要定期观察表大小，找出有问题的大表进行优化调整。
📝 资源评估
测试人员进行压测，观察极限环境下数据库各项指标是否正常工作，运维工程师或者数据库管理员对数据容量进行评估，服务器资源需要提前规划，同时设置预警通知，超过阈值安排相关人员进行扩容，从而保证数据库稳定运行。
🎉 数据服务
数据服务的主要目的是帮助用户规划和迁移数据，备份和恢复数据库以及进行数据校验等功能，以确保用户的数据始终处于安全可靠的状态。
📝 子表结构生成
一个表进行拆分，会根据业务实际情况进行拆解，例如用户表可以根据地区拆分tb_user可拆分成上海地区的用户表（tb_user_sh）、广州地区的用户表（tb_user_gz），那么全国有很多个城市，每个地方都需要创建一张子表并且维护它会比较费时费力，通常情况下，会开发3个接口做表结构同步：根据主表创建子表、主表字段同步到子表、主表索引同步子表。下面对这3个接口提供思路以及关键代码。
根据主表创建子表接口，代码如下：
//第6章/6.9.1 主表创建子表
/** * { * "tableName": "tb_user", * "labCodes": [ * "sh",//上海 * "gz"//广州 * ] * } */ public Boolean createTable(ConfigReq reqObject) { if (CollectionUtils.isEmpty(reqObject.getLabCodes())) { return false; } List<String> labCodes = reqObject.getLabCodes(); for (String labCode: labCodes){ //主表表名 String tableName = reqObject.getTableName(); //子表后表名 String newTable = String.format("%s_%s", tableName, labCode); //校验子表是否存在 Integer checkMatrix = configExtMapper.checkTable(newTable); if(checkMatrix == null || checkMatrix.intValue() < 0){ //创建子表结构 configExtMapper.createConfigTable(tableName, newTable); } } return true; }
主表字段同步到子表，代码如下：
主表字段同步到子表
/** * 主表字段同步到子表 * @param masterTable 主表 * @return */ private Boolean syncAlterTableColumn(String masterTable) { String table = masterTable + "%"; //获取子表名 List<String> tables = configExtMapper.getTableInfoList(table); if(CollectionUtils.isEmpty(tables)){ return false; } //获取主表结构列信息 List<ColumnInfo> masterColumns = configExtMapper.getColumnInfoList(masterTable); if (masterColumns.isEmpty()){ return false; } String alterName = null; for (ColumnInfo column: masterColumns) { column.setAlterName(alterName); alterName = column.getColumnName(); } for(String tableName : tables){ if(StringUtils.equalsIgnoreCase(tableName, masterTable)){ continue; } //获取子表结构列信息 List<ColumnInfo> columns = configExtMapper.getColumnInfoList(tableName); if(CollectionUtils.isEmpty(columns)){ continue; } for (ColumnInfo masterColumn : masterColumns) { ColumnInfo column = columns.stream().filter(c -> StringUtils.equalsIgnoreCase(c.getColumnName(), masterColumn.getColumnName())).findFirst().orElse(null); if (column == null){ column = new ColumnInfo(); column.setColumnName(masterColumn.getColumnName());//列名 column.setAddColumn(true);//是否修改 } if (column.hashCode() == masterColumn.hashCode()){ continue; } column.setTableName(tableName);//表名 column.setColumnDef(masterColumn.getColumnDef());//是否默认值 column.setIsNull(masterColumn.getIsNull());//是否允许为空（NO：不能为空、YES：允许为空） column.setColumnType(masterColumn.getColumnType());//字段类型（如：varchar(512)、text、bigint(20)、datetime） column.setComment(masterColumn.getComment());//字段备注（如：备注） column.setAlterName(masterColumn.getAlterName());//修改的列名 //创建子表字段 configExtMapper.alterTableColumn(column); } } return true; }
主表索引同步子表，代码如下：
主表索引同步子表
/** * 主表索引同步子表 * @param masterTableName 主表名 * @return */ private Boolean syncAlterConfigIndex(String masterTableName) { String table = masterTableName + "%"; //获取子表名 List<String> tableInfoList = configExtMapper.getTableInfoList(table); if (tableInfoList.isEmpty()){ return false; } // 获取所有索引 List<String> allIndexFromTableName = configExtMapper.getAllIndexNameFromTableName(masterTableName); if (CollectionUtils.isEmpty(allIndexFromTableName)) { return false; } for (String indexName : allIndexFromTableName) { //获取拥有索引的列名 List<String> indexFromIndexName = configExtMapper.getAllIndexFromTableName(masterTableName, indexName); for (String tableName : tableInfoList) { if (!tableName.startsWith(masterTableName)) { continue; } //获取索引名称 List<String> addIndex = configExtMapper.findIndexFromTableName(tableName, indexName); if (CollectionUtils.isEmpty(addIndex)) { //创建子表索引 configExtMapper.commonCreatIndex(tableName, indexName, indexFromIndexName); } } } return true; }
