Mysql管理

mysqlbinlog db01-binlog.000003 #查看这个binlog日志的事件过程；

mysqladmin -S /tmp/mysql3307.sock flush-logs #日志切割

系统命令

安装依赖

解压缩，创建快捷方式

编写环境变量

创建目录，用户等

初始化

数据库安装

命令查看

记录了客户端从会话连接开始，执行过的所有SQL语句信息；

默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；

通用日志（general_log）

记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息；

默认处于激活状态

错误日志（log_error）

记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；语句以事件的形式保存，描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用。

利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；

作用

GTID

二进制日志（log_bin）

记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小于min_examined_row_limit的所有SQL语句的日志

默认处于关闭状态，实际使用当中应当开启。

慢查询日志（slow_query_log）

日志分类

第一步查看数据的起始位置第二步导出起始位置的内容第三步导入到数据库中

日志恢复

日志备份

日志管理

备份命令

可以在任意时间节点在不停机不停止业务时，然后利用专业的mysqldump(MDP)逻辑备份工具进行数据备份；可以在任意时间节点在不停机不停止业务时，然后利用二进制日志binlog文件实现逻辑备份数据操作；可以在任意时间节点在不停机不停止业务时，然后利用主从数据库架构实现备份数据信息；

备份方式

当企业数据库服务产生的需要备份的数据量在50G以内，可以选择逻辑备份（mysqldump）当企业数据库服务产生的需要备份的数据量在50G以上，可以选择物理备份（xtrabackup）；

实际应用

逻辑备份

冷备份其实就是停掉数据库服务，cp数据文件的方法；这种方法对MyISAM和InnoDB存储引擎都合适，但是一般很少使用；

冷备份

使用mysqlhotcopy工具

# 对数据库中所有表加读锁：mysql> flush tables for read;-- 然后cp数据文件到备份目录即可；

手工锁表copy

MyISAM

ibbackup 收费的工具

命令

增量备份的实质是，基于上一次备份LSN变化过的数据页，进行相应的备份操作，从而可以不断实现增量备份操作；在备份同时产生的新的变更，会将redo日志信息备份；第一次增量备份时依赖于全量备份的，将来的恢复操作也要合并到全备中，再进行统一恢复；

增量备份

Xtrabackup（PXB）开源的工具

InnoDB

热备份

物理备份

备份操作

查看命令

启动克隆操作的MySQL数据库服务器实例中的数据，将会克隆到同服务器或同节点上的一个目录里；

本地克隆步骤加载克隆插件信息创建克隆专用用户进行本地克隆操作观测本地克隆状态（另开窗口使用管理员用户查看）实现克隆日志观测启动运行克隆实例

本地克隆

默认情况下，远程克隆操作会删除接受者（recipient）数据目录中的数据，并将其替换为捐赠者（donor）的克隆数据；主要用于实现数据远程的快速热迁移操作，在迁移过程中，除了DDL操作情况，其他操作都不会出现阻塞情况；还可以利用远程克隆技术，实现快速构建数据库的主从架构环境，实现主从数据信息快速复制同步；

远程克隆步骤克隆操作环境准备加载克隆插件信息创建克隆专用用户进行远程克隆操作观测本地克隆状态

步骤

远程克隆

在进行数据库克隆操作时，会经历几个重要的过程或步骤：01 Page copy：02 Redo copy：03 Done：

对于不同版本的MySQL数据库实例之间，是不能进行克隆操作的。对于捐赠者和接受者必须是确切相同数据库服务版本；

整个克隆过程都会以事件信息记录，可以很清晰的看到克隆的流程，如果克隆过程中断，也会以追加方式进行继续克隆；

简介

克隆

MySQL复制的优点主要包含以下3个方面：如果主库出现问题，可以快速切换到从库提供服务；可以在从库上执行查询操作，降低主库的访问压力；可以在从库上执行备份操作，以避免备份期间影响主库的服务；

主从复制的原理1.在进行主从数据复制时，是依靠相应线程信息来完成数据复制同步操作的；主库涉及线程binlog dump thread；从库涉及线程slave IO thread，slave SQL thread；如果是一主多从的架构，将会看见多个binlog dump线程信息，实现对多个从库的日志信息投递；2.在进行主从数据复制时，是依靠相应文件信息来完成数据复制过程中的数据保存的；主库涉及文件binlog；从库涉及文件relaylog，master.info，relay-log.info；

复制原理过程详述：在从库上执行change master to命令，将主库连接信息和binlog位置信息写入master.info文件或 slave_master_info表中；在从库上执行start slave命令，用于启动从库的IO和SQL线程功能；从库IO线程主要用于读取主库连接信息，实现和主库建立连接，从而使主库派生出binlog dump线程（自动监控binlog）；从库IO线程根据change master to命令所定义的数据位置点，获取最新的binlog日志信息mysql主库在事务提交时会把数据变更为事件Events记录在二进制日志文件binlog中；mysql主库上的sync_binlog参数控制binlog日志刷新到磁盘；binlog dump线程会截取binlog日志并投递其日志给从库IO线程，此时主库并不关心投递日志信息的结果；此时从库IO线程接收binlog投递信息（缓存），随之会立即更新master.info文件 或 slave_master_info数据表信息；从库缓存的binlog日志数据信息会被写入到relaylog中继日志中；主库推送二进制日志文件binlog中的事件到从库的中继日志relay log，之后从库根据中继日志relay log重做数据变更操作，从库SQL线程将会读取relaylog.info文件或者slave_relay_log_info数据表中信息，获取上次数据回放同步位置点；随之继续向后回放同步数据，一旦回放同步数据完成后，再次更新relay.info或slave_relay_log_info数据表信息；在从库中回放过的relaylog日志信息，会被relay_log_purge线程定期删除处理这些日志；通过逻辑复制以此来达到主库和从库的数据一致；

概述

表示人为主动方式将一个从库进行配置，使从库可以按照指定的时间延时后，再进行和主库完成相应数据信息同步；主要是对逻辑原因造成的数据损坏进行弥补修复，从而避免全备数据恢复业务产生的代价较高问题；当出现逻辑损坏操作时，可以利用延时从库的延时同步特性，将异常操作不做同步，将从库未做破坏的数据信息恢复到主库中；主库有逻辑删除的时候，从库接受binlog日志，但是不进行同步，延时一段时间后，再同步，在此不同步的时间里，把主库误删除或更改的数据在从库里面恢复出来，再导给主库使用；

过程跟主从复制一样，多了一条命令：change master to master_delay=300;

延时从库

当在企业数据库服务应用当中，如果在主库上有多个数据库业务，希望将不同的数据库业务同步到不同的从库上，实现数据库业务分离；基于过滤复制功能，还是可以实现在主从同步数据信息时，排除指定库的数据信息不做主从同步操作；

过滤复制

利用半同步复制机制，主要是用于解决主从数据复制不一致的问题，即解决主从数据一致性问题，也可以避免SQL线程故障；半同步功能以生效，在主库做DDL操作，如果从库IO线程终止了，或者网络不通了，那么主库的操作则会夯住10S钟，这个时间可以更改

半同步复制

准备两个主机设备（可以利用两个实例）在主库上创建复制用户进行主库全备操作/从库进行全备操作从库上定义连接主库的配置信息进行主从关联检查测试

构建过程

GTID是对于一个已提交事务的唯一编号，并且是一个全局唯一编号（主从复制过程）；是数据库5.6版本开始的一个功能新特性，主要是用于解决主从复制的一致性问题；

概念

master节点在更新数据的时候，会在事务前产生GTID信息，一同记录到binlog日志中；slave节点的io线程将主库推送的binlog写入到本地relay log中；然后SQL线程从relay log中读取GTID，设置gtid_next的值为该gtid，然后对比slave端的binlog是否有记录；如果有记录的话，说明该GTID的事务已经运行，slave会忽略；如果没有记录的话，slave就会执行该GTID对应的事务，并记录到binlog中。

原理机制

准备主从主机信息进行gtid主从配置进行主从关系构建

GTID复制（主流）

利用clone plugin方式可以实现数据迁移备份恢复操作，同样也可以利用克隆技术实现主从数据同步操作，即完成快速构建从库；主要应用于运行一段时间的数据库，需要进行主从架构环境的构建时，可以实现主库数据信息的快速迁移；利用克隆复制备份恢复迁移数据信息，可以使备份恢复数据的效率提升；

Clone复制

在mysql应用过程中，为了实现数据中台构建，可以将所有业务的主库数据信息，汇总到一个从库中，即实现多源复制功能（5.7+）；实现多源复制，会对不同业务主库有一定的性能影响，并且对于多源复制的目标从库，并不会做写操作，只用于做数据分析使用；

MSR复制

MGR基于分布式paxos协议，实现组复制，保证数据一致性。MGR采用多副本，在2N+1个节点集群中，集群只要N+1个节点还存活着，数据库就能稳定的对外提供服务；数据库组复制功能，可以理解为是数据库主从关系的高可用环境，一般需要三个数据库实例，构成一个具有高可用、高一致性的复制环境

主要涉及的功能应用包含：具有多节点之间互相通过投票的方式进行监控功能；（基于paxos协议）具有内置故障检测和自动选主功能，只要不是集群中的大多数节点都宕机，就可以继续正常工作；   - 如果主节点异常，会自动选举新节点实现故障转移   - 如何从节点异常，会自动将从节点从复制节点踢除提供单主模式与多主模式，多主模式支持多点写入；

单主模式下，如果主节点挂了，那么其他的成员会自动选举出新的主成员，成员之间可以通过配置权重来确定下一个主成员是谁，如果没有配置权重，则会对所有在线成员的UUID进行排序，然后选取UUID最小的成员作为主成员。

设置本地root用户密码和密码插件（所有节点）安装部署MGR组复制功能插件（所有节点）设置创建MGR组复制功能账号（所有节点）启动MGR单主模式：启动MGR引导节点（在主库上执行）遇到集群构建异常，可以进行重置操作

配置思路

单主模式

在多主的模式下，没有单个主概念。无需进行节点选举，因为没有服务器扮演任何特殊角色，所有服务器均设置为读写模式。多主模式，所有的组内成员对外提供读写服务，是真正意义上的并发，MGR对于高并发有很好的的处理能力。多主模式下，组内所有成员没有主从之分，对用户来说，就像在操作一个MySQL一样。所以在多主模式下，不存在选举主节点，因为所有节点都是主节点。

在所有主机配置文件中激活MGR功能，并生成组成员其他主机DB02 DB03

多主模式

在应用MGR组复制功能时，也存在一些应用的限制条件：仅支持innodb存储引擎应用组复制功能；MGR集群中只支持innodb存储引擎，能够创建非innodb引擎的表，但是无法写入数据，向非innodb表写入数据直接报错；数据表中必须有主键，或者非null的唯一键；MGR集群中只支持innodb存储引擎，并且该表必须有显示的主键，或者非null的唯一键，否则即使能够创建表，也无法向表中写数据组复制存在网络限制，MGR组通信引擎目前仅支持IPv4网络，并且对节点间的网络性能要求较高；对于低延迟、高带宽的网络是部署MGR集群的基础；组复制功能会自动忽略表锁和命名锁，在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将忽略MGR多主模式中，默认不支持 SERIALIZABLE 隔离级别，建议使用RC隔离级别；组复制多主模式中，对同一个对象进行并发是有冲突的，ddl和dml操作导致这种冲突在部分成员节点中无法检测到；最终可能导致数据不一致组复制多主模式中，不支持级联约束的外键，可能造成有冲突的操作无法检查；组复制功能不支持超大事务同步；组复制多主模式下可能导致死锁，比如select ... for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁；组复制是不支持复制过滤的，如果有节点设置了复制过滤功能，将影响节点间决议的达成；组复制功能最多支持9个节点，当大于9个节点，将拒绝新节点的加入；

应用限制说明

安装软件程序进行数据库初始化配置添加实例到mysqlsh中，使mysqlsh可以批量管理所有数据库节点构建MGR主从架构构建MIC集群mysql-router中间件读写分离测试

基于MGR组成的MIC集群架构配置（读写分离）

利用mycat可以实现：· 可以实现读写分离· 可以实现高可用冗余· 可以实现垂直分表   （将一个库中的表分别存储到不同主从架构中）· 可以实现水平分表   （将一个大表中的数据存储到不同主从架构中

mycat分布式中间件管理

读写分离

MGR复制（利用GTID）

主从复制

主从架构

数据库高可用技术的出现主要解决的是数据逻辑损坏问题，而主从架构技术主要解决的是数据物理损坏问题；MHA Manager 会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master；然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序是完全透明的；MHA是一次性的，如何切换完成自动失效，需要手动再次开启

可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave上。

MHA Manager（管理节点）

运行在每台MySQL服务器上MHA Manager 会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master；然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序是完全透明的；

MHA Node（数据节点）

构建GTID主从关系创建程序命令软链接配置各节点互信安装软件程序在db01主库中创建mha需要的用户Manager配置文件准备(db03)MHA状态检查（db03）开启MHA-manager查看MHA状态

① 如何在高可用架构中，当主库宕机异常后，使之及时的发现主库服务程序产生了运行异常？      解决此痛点问题，需要实现高可用的监控需求；    ② 如何在高可用架构中，当主库宕机异常后，可以找到可以替代主库的服务器主机进行切换？        解决此痛点问题，需要实现高可用的选主功能；（并且选择数据量越接近主库的从库成为新主）     ③ 如何在高可用架构中，当主库宕机异常后，新的主库接管后可以保证与原有主库数据一致          解决此痛点问题，需要实现高可用的数据补偿；    ④ 如何在高可用架构中，当主库宕机异常后，将应用程序的读写请求对接切换到新的主库上？        解决此痛点问题，需要实现高可用的应用透明；（VIP技术）      ⑤ 如何在高可用架构中，当主库宕机异常后，能够及时向管理员发起告知提醒使之进行修复？(MHA切换是一次性的)        解决此痛点问题，需要实现高可用的报警功能；     ⑥ 如何在高可用架构中，当主库宕机异常后，当整体主库系统环境都异常时实现数据的补偿？      解决此痛点问题，需要实现高可用的额外补偿；     ⑦ 如何在高可用架构中，当主库宕机异常后，根据主库服务器的异常情况进行原有主库修复？         解决此痛点问题，需要实现高可用的自愈功能；（待开发，只有云平台RDS具有此功能）

数据库高可用MHA原理机制

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

01 MHA软件启动

利用/usr/bin/masterha_master_monitor脚本，真正连接指定主库，进行操作，识别主库运行状态

02 MHA实现监控

利用4个数组，进行从库选主

03 MHA选主原理

1）所有从库，同步主库的binlog，进行自动补偿2）所有从库，彼此之间同步relaylog，进行自动补偿 3）MHA管理主机，实时同步主库binlog，从库可以获取管理节点的binlog，进行额外数据补偿

04 MHA数据补偿

选举结束，其他从库会重置主从关系，选择新主同步数据

05 MHA业务切换（主从关系切换）

利用vip功能，实现对前端访问chmod +x /usr/local/bin/*dos2unix /usr/local/bin/*

06 MHA应用透明

MHA的设计原理分析（Failover 过程）

MHA健康检查报错，显示主数据库节点无法正常连接MHA进行重新选主，根据数组信息选择合适的备用新主节点MHA进行节点关闭，选择完新的主节点后会将原有主节点的VIP地址消除MHA进行节点切换，在新的主节点上进行非同步数据信息的补偿MHA进行主从重构，将从库连接到新的主库上MHA切换完毕过程，架构故障转移切换完毕后做清理阶段，并进行最终汇报

高可用故障切换过程

读写分离架构最终目的：实现业务写的请求到达主库，实现业务读的请求到达从库，从而减少主库的压力，实现不同请求的压力分担；

proxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理层，可以实现读写分离：proxySQL数据库中间件支持Query路由功能；pxoxySQL数据库中间件支持动态指定某个SQL进行缓存；proxySQL数据库中间件支持动态加载配置信息（无需重启ProxySQL服务）proxySQL数据库中间件支持故障切换和SQL的过滤功能说明：一般服务是通过配置文件保存功能配置信息，proxySQL是通过数据库中的表进行配置信息的存储设置；

代表proxySQL当前正在使用的配置，无法直接修改此配置，必须要从下一层（MEM层）load加载进来；

第一层：RUNTIME:

memory层上面连接runtime层，下面连接disk持久化存储层；在这层可以在线操作ProxySQL配置，随意进行修改，不会影响生产环境，确认正常之后再加载到runtime和持久化保存到磁盘上具体修改操作方法为：insert、update、delete、select；

第二层：MEMORY（主要修改的配置表

持久化配置信息，重启时可以从磁盘快速加载回来；

第三层：DISK/CFG FILE

需要注意：只有load到runtime状态时才会验证配置，在保存到mem或disk时，都不会发生任何警告或错误；当load到runtime时，如果出现了错误信息，将恢复为之前保存的状态，这时可以根据错误日志信息做检查；总结：日常配置过程大部分时间是在mem中进行配置，然后load到runtime，或者save到disk中，对于cfg很少使用；

注意事项

ProxySQL管理接口的多层配置关系

第一步：安装读写分离软件程序第二步：如何对proxy进行功能配置：第三步：进行读写分离测试

配置步骤

proxySQL

数据库服务读写分离

MHA

高可用架构

服务器选型优化BIOS优化网卡优化存储优化

硬件服务器选择

SWAP空间优化脏页优化读写连接数优化

系统服务器优化

连接层优化服务层优化引擎层优化主从复制优化开发语句规范索引服务优化架构设计优化安全优化事务与锁的优化

数据库服务结构优化

优化

作用：可以实现将数据表中的数据进行归档，迁移恢复到其他数据表中或文件中

pt-archiver应用

pt-osc工具对于修改表结构、索引创建删除比较擅长，pt工具应用不能加快改写速度，但能减少业务影响，主要是锁对业务的影响;

pt-osc应用

pt-table-checksum工具主要用于校验主从数据一致性情况，主要针对数据库或者数据表进行一致性检查;此工具可以在主从复制时，当SQL线程出现异常报错时，可以利用此工具进行校验检查;

pt-table-checksum应用：

pt-table-sync工具可以对主从不一致的数据信息，进行同步复制修复，实现恢复主从数据的一致性;

pt-table-sync应用：

pt-duplicate-key-checker工具主要用于检查数据库重复索引信息:

pt-duplicate-key-checker应用：

pt-kill应用

pt-slave-find工具主要用于输出主从关系的拓扑结构信息;

pt-slave-find应用：

pt-heartbeat工具主要用于监控主从延时的情况

pt-heartbeat应用：

导出主库的授权表，利用这个授权表在新库重新创建

pt-show-grants应用：

PT工具包的使用

mysql

Mariadb

Oracle

SQLserver

PostgreSQL

关系型

Redis

Mongodb

非关系型

数据库分类

负责管理数据库的基础数据（不会对表的内容修改），比如增删库、增删表、增删索引、增删用户等；

CREATE（创建）、ALTER（修改）、DROP（删除）等；

DDL （数据定义语言）

主要用来定义访问权限和安全级别

GRANT（用户授权）、REVOKE（权限回收）、COMMIT（提交）、ROLLBACK（回滚）

DCL （数据控制语言）

主要针对数据库里的表里的数据进行操作，用来定义数据库记录（数据）

SELECT（查）、INSERT（增）、DELETE（删）、UPDATE（改）

DML（数据操作语言）

主要用来查询记录（数据）

SELECT（查）

DQL （数据查询语言）

管理语言

字符编码

数据类型

数据模式

数据库语句基础

数据库管理系统（DBMS）

数据库（DB）

数据表（Table）

数据字段（Field）

数据库结构

B+Tree（默认）

Hash

R+Tree

Fulltext

类型

数据库索引相当于书的目录，可以借助索引有针对的查看相应数据的信息，避免了全盘检索带来的工作量；

聚簇索引主要是：将多个簇（区-64个数据页-1M）聚集在一起就构成了所谓聚簇索引，也可以称之为主键索引；聚簇索引作用是：用来组织存储表的数据行信息的，也可以理解为数据行信息都是按照聚簇索引结构进行存储的，即按区分配空间的；聚簇索引的存储：聚簇是多个簇，簇是多个连续数据页(64个)，页是多个连续数据块(4个)，块是多个连续扇区(8个)；

数据表创建时，显示的构建了主键信息（pk），主键（pk）就是聚簇索引；数据表创建时，没有显示的构建主键信息时，会将第一个不为空的UK的列做为聚簇索引；数据表创建时，以上条件都不符合时，生成一个6字节的隐藏列作为聚簇索引；

构建方式

索引方式一：聚簇索引（集群索引/聚集索引）

辅助索引主要是：主要用于辅助聚簇索引查询的索引，一般按照业务查找条件，建立合理的索引信息，也可以称之为一般索引；辅助索引作用是：主要是将需要查询的列信息可以和聚合索引信息建立有效的关联，从而使数据查询过程更高效，节省IO和CPU消耗辅助索引的存储：调取需要建立的辅助索引列信息，并加上相应主键列的所有信息，存储在特定的数据页中；

数据表创建时，显示的构建了一般索引信息（mul），一般索引信息（mul）就是辅助索引；数据表创建时，没有显示的构建一般索引信息时，在查询检索指定数据信息，会进行全表扫描查找数据；

索引方式二：辅助索引

数据库索引构建过程

执行计划就是最优的一种执行SQL语句的方案，表示相应SQL语句是如何完成的数据查询与过滤，以及获取；

可以利用命令进行获取执行计划信息：explain/descdesc select * from oldboy.t100w where k2='VWlm';

数据库执行计划获取

ALL - ok 表示全表扫描方式，没用利用索引扫描类型；index 表示全索引扫描方式，需要将索引树全部遍历，才能获取查询的信息（主键index=全表扫描）range 表示范围索引方式，按照索引的区域范围扫描数据，获取查询的数据信息；ref 表示辅助索引等值（常量）查询，精准定义辅助索引的查询条件eq_ref 表示多表连接查询时，被驱动表的连接条件是主键或者唯一键时，获取的数据信息过程；const/system 表示主键或者唯一键等值（常量）查询，精准定义索引的查询条件

数据库索引应用类型

在执行计划列中，key_len主要用来判断联合索引覆盖长度（字节），当覆盖长度越长，就表示匹配度更高，回表查询的次数越少；到底联合索引被覆盖了多少，是可以通过key_len计算出来；

数据库索引覆盖长度

联合索引可以优化表中多列信息的查询，当需要多列信息查询时最好应用联合索引，不要应用多个单列索引；在进行联合索引应用设置时，也是需要满足一定规范要求的，即使建立的联合索引，可能某些情况下，联合索引也不能大部分被使用；因此，建立了联合索引，肯定是希望联合索引走的越多越好，但也有可能联合索引建立存在问题，也会导致查询效率较低；

创建好联合索引 + 合理应用联合索引 发挥联合索引最大价值联合索引建立没有问题，但是查询语句书写有问题，导致联合索引应用效果不好；查询语句书写没有问题，但是联合索引建立有问题，导致数据查询结果性能过低；

联合索引建立异常分析思路

建立索引的时候，最左列使用选择度高（cardinality-重复值少的列/唯一值多的列）的列执行查询的时候，一定包含索引的最左条件

需要满足最左原则；（尽量）需要定义条件信息时，将所有联合索引条件都引用；（必要）

联合索引全部覆盖

需要满足最左原则；需要定义条件信息时，将所有联合索引条件部分引用；

联合索引部分覆盖

需要定义条件信息时，将所有联合索引条件都不做引用

联合索引完全不覆盖

联合索引应用要遵循最左原则

数据库联合索引应用

Extar列表示额外的情况或额外的信息说明，其中重点需要关注点信息为：filesort 表示涉及到额外排序操作，将严重浪费CPU资源；哪些查询语句情况涉及到排序操作：情况一：查询语句中含有 order by ，表示触发式的排序；情况二：查询语句中含有 group by，表示隐藏式的排序；情况三：查询语句中含有 DISTINCT，表示会先进行排序后再取消重复；

数据库索引扩展信息

数据表中必须要有主键索引（创建表时指定），建议是与业务无关的自增列；数据表中某些列若经常作为 where/order by/group by/join on/distinct条件信息，最好将相应列设置索引（产品功能/用户行为）数据表中最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引；（最左列-减少回表次数 - 减少磁盘IO）数据表中列值长度较长的索引列，建议可以使用前缀索引；(防止索引树层次过高)数据表中不建议建立大量索引，最好降低索引条目，不要创建无用索引，不常用的索引要定期清理（percona toolkit）数据表中的索引信息做调整维护时，尽量避开业务繁忙期，或者通过软件工具做调整维护（pt-ost）数据表中的联合索引创建过程要遵循索引最左原则；

建立索引原则规范（DBA运维规范）

数据表信息查询时，没有设置查询条件信息；数据表信息查询时，查询的条件没有建立索引；

索引应用失效情况（开发工作规范

当查询结果集数据是原表中的大部分数据，超过了总行数的25%，优化器便自动判断没必要走索引了，因为可以借助预读功能获取数据可以通过精细查找指定数据的范围，从而达到优化的效果；（read_head预读相关参数）

查询结果规范要求

当频繁的对数据表中索引列值做修改、删除等操作时，会导致索引统计信息过旧或不真实，最终造成索引功能失效；本身索引是有自我维护的机制能力，但并不是实时调整更新的，需要有一定的间隔时间做调整；一般索引失效的表现情况为：select查询语句平常查询时很快，但突然某天执行就变慢了，就是索引失效了，统计数据不真实；

索引失效情况处理

数据库索引应用总结

数据库服务索引功能特性

AHI全称（中文名称）为自适应的hash索引/散列索引，用于在内存中建立索引，快速锁定内存中的热点数据索引页位置；可以对内存中经常被访问数据索引页建立一个hash索引，从而可以帮助数据库服务快速定位内存中想要找的索引数据页

自主优化功能一：AHI（索引的索引

change buffer主要是针对辅助索引的缓冲区，属于内存结构上的应用；

自主优化功能二：CHANGE BUFFER

属于5.6之后引用的数据库服务新特性，称之为索引下推功能，主要是针对联合索引功能起作用；

自主优化功能三：ICP （索引下推）

简单来说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

自主优化功能四：MRR

数据库服务自主优化能力

数据库索引知识扩展

数据库服务执行计划

索引

1.mysql中支持的存储引擎种类：InnoDB、MyISAM、CSV、MEMORY

InnoBD支持：事务、mvcc、聚簇索引、外键、缓冲区、AHI、DW；MyISAM均不支持

InnoDB支持：行级锁，MyISAM只支持表级锁

InnoDB支持：数据热备，可以保证业务正常运行，对业务影响低，MyISAM只支持温备份，需要锁表备份；

InnoDB支持：支持CR自动故障恢复，宕机自动恢复，数据安全和一致性可以得到保证；MyISAM不支持，宕机可能丢失当前数据

2.InnoDB与MyISAM区别

特性与种类

8.0版本默认都是innoDB

引擎应用

MySQL 5.7设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展MySQL 8.0设置共享表空间1个即可，大小建议512M或1G如果要修改表空间则需要再数据库初始化的时候在配置中修改。

数据存储数据方式为：ibdata1~ibdataN

共享(系统)表空间

8.0版本默认就是独立文件，不用修改在数据库5.7环境中，每个表数据信息会存储生成两个表 *.frm、*. ibd而8.0就只有*.ibd文件了。

在数据库服务8.0版本前          用户表包含三个部分组成（表.ibd 表.frm ibdata1-全局数据字典信息存储）；          所以在8.0之前，如果想修改表数据结构信息(元数据修改)，都会修改frm和ibdata文件信息，每次更新都会锁表(元数据锁)；          因为要保证数据一致性，并且两个表均更新完，才能释放解锁，因此在8.0前修改元数据信息，要避开业务繁忙时间段；在数据库服务8.0版本后           用户表数据进行统一存储（表.ibd）; 如果想修改表数据结构信息(元数据修改)，只会修改ibd文件信息；           此时不需要对两个表文件均更新，只要更新一个文件即可，因此对文件锁的代价降低了，降低了对业务的影响；        

简介说明

独立表空间

在实际生产环境中，建议在5.7版本之后，都将undo表空间进行独立文件存储；而5.7的版本默认并没有开启独立存储；8.0的版本就啥也不用动，保持默认即可

利用undo表空间主要用来完成撤销工作（回滚操作）

子主题

undo表空间

临时表空间主要用于存储临时表信息，主要是在使用group by，order by，having，unique all，子查询等情况都会使用临时表；

建议数据初始化之前设定好临时表空间，建议2~3个，大小512M~1G；MySQL 5.7设置共享表空间2~3个，大小建议512M或1G，最后一个定制为自动扩展MySQL 8.0设置共享表空间1个即可，大小建议512M或1G

temp表空间

redo log属于事务重做日志文件，主要用于记录内存数据页的变化默认存储在数据库服务的数据目录下，默认大小为48M

在实际生产环境中，建议大小为512M~4G，应用组数为2~4组（写入数据过程轮询写入）保持默认即可

redo事务日志

磁盘结构

内存结构

引擎结构

存储引擎

数据库服务中为了保证线上交易的\"和谐\"，便加入了\"事务\"工作机制（保证交易行为安全性）

原子性（Atomicity

一致性（Consistency）

隔离性（Isolation）

持久性：（Durability）

特性

事务生命周期中，只能使用DML语句，其中包括：select、update、delete、insert；DDL语句会隐式进行提交

begin  DML;DML;DML;DML commit   -- 完整生命周期 begin  DML;DML;DML;DML rollback   -- 完整生命周期

默认为自动提交

生命周期

RU（READ-UNCOMMITTED 表示读未提交）  低

RC（READ-COMMITTED 表示读已提交）    中

RR（REPEATABLE-READ 表示可重复读）   高（默认）

SR（SERIALIZABLE 可串行化）严格

隔离级别

一般不用修改

事务机制管理

- Redis具有丰富的数据类型可以进行应用，其中包括:key-value、list、set、zset、hash等数据结构类型- Redis具有持久化存储能力，可以将内存中的数据保存在磁盘中，重启的时候可以再次加载进行使用- Redis具有多种内存分配和回收的解决方案:- Redis具有原有关系型数据库的事务处理能力(弱事务):- Reids具有消息队列相关功能，支持消息订阅能力，但是不支持消息回和消息堆积:- Redis具有原生态的高可用架构构建技术;- Redis具有数据的备份能力，即master-slave模式的数据备份:Redis具有原生态的分布式架构和分片集群:

第一步：上传解压软件程序|第二步：安装软件程序

安装服务

应用一：string （字符串信息）

应用二：hash （实现将数据库中数据调取 缓存到redis中）

应用三：list （可以快速加载留言或评论信息

应用四：set 集合

应用五：SortedSet（zset）

redis各种数据类型应用

第一步：部署多个实例

第二步：实现主从同步

缓存服务主从复制原理- Redis缓存的服务主从复制功能又称为主副本集(Master-Replicaset)，具体实现原理如下:- 副本库通过slaveof192.168.30.1016379命令，连接到主库缓存服务，并发送SYNC给主库:- 主库收到SYNC数据后，会立即触发BGSAVE，在后台保存RDB信息，并发送给副本库:- 副本库接收后会应用RDB快照信息;- ·主库会陆续将中间产生的新操作数据，保存并发送给副本库，至此主复制集就正常工作了- 后面应用过程中，主库只要发生新的操作，都会以命令传输的形式自动发送给副本库;- 所有复制相关的信息，从info信息中都可以查到，即使重启任何节点，它的主从关系依然都在;- 如果发生主从关系断开时，从库数据没有任何损坏，在下一次重连之后，从库发送PSYNC给主库:- 主库只会将从库缺失部分的数据同步给从库应用，达到快速恢复主从的目的;

redis主从同步架构搭建

第一步：安装部署哨兵服务

第二步：进行高可用切换验证

redis实现高可用（哨兵组件）

第一步：创建多个实例信息

第二步：创建集群成员

redis集群构建

redis 非关系型数据库缓存服务

Mysql管理