Redis 技术在业务场景中的应用与常见面试要点解析
文章主要围绕 Redis 展开，首先介绍了 Redis 在业务中的使用场景，如缓存、会话管理、消息队列等。接着详细阐述了 Redis 常见的面试问题，包括数据类型、缓存击穿/穿透/雪崩的定义及应对方法、布隆过滤器的概念及注意事项、数据双写一致性的保证方式、持久化方式（RDB 和 AOF）、缓存删除策略、分布式锁的实现、集群方式（主从复制、哨兵模式、Cluster 集群）以及 Redis 虽是单线程却速度快的原因等，为读者全面了解 Redis 提供了丰富的知识和参考（一）Redis的业务使用场景Redis 是一个开源的内存数据结构存储系统，具有高性能、丰富的数据结构和多种特性，在业务中经常做为中间来进行使用，场景中常见应用场景如下：缓存：对于一些频繁访问且相对静态的数据，比如电商网站中的商品基本信息、用户配置信息等，将其存储在 Redis 中。当应用程序需要获取这些数据时，首先从 Redis 中查询，如果 Redis 中不存在，再从数据库中查询并将结果存入 Redis，下次访问时就可以直接从 Redis 中获取，大大减少数据库的访问压力，提高响应速度会话管理：传统的 Web 应用中，会话信息通常存储在服务器的内存中或者使用 Cookie /Session存储。然而，当多服务器集群部署时，使用服务器内存存储会话会面临会话同步的问题，Redis 可以作为一个集中式的会话存储解决方案，将用户的会话信息存储在 Redis 中，便于多台服务器共享数据，避免重复登录或登录失效等问题消息队列: Redis 的列表数据结构可以用作简单的消息队列。生产者将任务或者消息以 LPUSH 命令推入 Redis 列表的一端，消费者使用 RPOP 或者 BRPOP 命令从列表的另一端取出任务或消息进行处理,在Java来说Redision第三方库直接可使用的队列类型进行使用发布/订阅模式：Redis 提供了发布/订阅功能，发布者将消息发布到特定的频道，订阅了该频道的所有客户端都可以收到消息。这种模式适用于实时性要求较高、一对多的消息通知场景，比如构建聊天室或者集群中广播接收可用该特性实现业务功能分布式锁：在分布式系统中，多个进程或线程可能需要访问共享资源，为了保证资源的互斥访问，可以使用 Redis 实现分布式锁。通过 SETNX 命令设置一个键值对，如果键不存在则设置成功并获取锁，处理完业务后通过 DEL 命令释放锁。限速器实现：利用 Redis 的过期时间和计数功能可以实现限速功能。例如，限制用户在一定时间内的请求次数，防止恶意请求或滥用。可以通过记录用户的请求时间戳或请求次数，并结合 Redis 的过期设置来控制访问频率排行榜: 如玩家得分排行榜、等级排行榜等可以使用 Redis 的有序集合来实现。玩家的游戏数据（如得分、等级等）作为有序集合的元素分值，玩家 ID 作为元素。通过 Redis 的 ZADD、ZRANK 等命令可以方便地添加玩家数据到排行榜、查询玩家的排名等操作。地理位置服务:Redis 从 3.2 版本开始支持地理位置相关的操作。在社交应用、外卖应用、打车应用等场景中，可以使用 Redis 存储用户或地点的地理位置信息，并通过相关命令查询附近的用户或地点。同时我们还可以利用 Redis 的地理位置功能可以实现地理围栏的应用。例如，在物流配送系统中，可以设置地理围栏区域，当配送车辆进入或离开某个区域时，触发相应的事件和操作（二）Redis常见面试问题1. Redis常见的数据类型有哪些？答： 字符串类型（string）,哈希（hash）,列表（list）,集合（set）,有序集合（sorted set）2. 什么是缓存击穿/穿透/雪崩是指什么，如何应对处理？答：缓存击穿：缓存击穿是指一个 key 非常热点，在不停的扛着大并发，大并发集中对这一个点进行访问，当这个 key 突然失效的瞬间，持续的大并发就穿破缓存，直接请求查询数据库，就像在一道坚固的屏障上击破了一个洞，处理方式有如下几个方式互斥锁：当发现缓存失效时，不是立即去访问数据库，而是先通过互斥锁（如 Java 中的 synchronized 或 ReentrantLock）控制只有一个线程去访问数据库，其他线程等待。获取数据后更新缓存，然后释放锁，其他等待的线程直接从缓存中获取数据，设置热点数据永不过期：对于一些特别热点且更新不频繁的数据，可以考虑不设置过期时间，这样就不会出现因为过期而导致的缓存击穿问题。但这种方法需要注意数据的更新操作，当数据更新时，需要同时更新缓存和数据库以保持一致性。缓存穿透：缓存穿透是指查询一个数据库一定不存在的数据。正常的使用缓存流程大致是，数据先查询缓存，缓存没有的话再去查询数据库，数据库没有的话就返回空结果。但当用户故意去查询一个数据库中不存在的数据时，缓存中自然也不会有，每次请求都会直接打到数据库，就像穿透了缓存直接打到数据库，处理方式有如下几个方式布隆过滤器：将所有可能存在的数据哈希到一个足够大的 bitmap 中，一个一定不存在的数据会被这个 bitmap 拦截，从而避免了对数据库的查询。当请求过来时，先通过布隆过滤器判断数据是否存在，如果不存在则直接返回，不再访问缓存和数据库空值缓存：当数据库查询结果为空时，也把这个空结果进行缓存（可以设置较短的过期时间，比如几分钟），这样下次相同的请求就不会再访问数据库，该方法实现简单，但是并不太推荐使用，可能会造成大量伪造访问导致Redis中存在大量空值缓存数据占用大量内存空间（配合key过期时间使用）缓存雪崩： 缓存雪崩是指缓存中大量的 key 在同一时间过期或者缓存服务出现故障不可用，导致大量请求直接访问数据库，就像一场突如其来的雪崩，瞬间给数据库带来巨大压力，可能导致数据库崩溃，处理方式有如下几个方式分散过期时间：在设置缓存过期时间时，避免大量 key 集中在同一时间过期。可以给 key 的过期时间加上一个随机时间或者采用不同的过期策略，比如让一部分数据在高峰期前过期，另一部分在高峰期后过期。缓存降级：当缓存服务出现故障时，可以启动缓存降级策略。比如暂时停止一些不重要业务的数据缓存查询，优先保证核心业务的正常运行；或者设置一个默认的缓存数据返回给用户，减少对数据库的访问压力。多级缓存：构建多级缓存，除了使用 Redis 等集中式缓存，还可以在应用服务器本地使用本地缓存（如 Ehcache、Guava Cache 等）。当集中式缓存出现问题时，本地缓存还可以抵挡一部分请求，减轻数据库压力。实时监控与报警：建立对缓存和数据库的实时监控系统，当发现缓存命中率突然下降、数据库负载急剧上升等异常情况时，及时发出报警，以便运维人员能够快速响应和处理。3. 什么布隆过滤器，使用的时候应该注意什么问题？答： 布隆过滤器（Bloom Filter）是一种空间效率很高的随机数据结构，它利用位数组很简洁地表示一个集合，并能快速判断一个元素是否属于这个集合，使用需要注意如下：误判问题： 布隆过滤器存在误判的可能，即当布隆过滤器判断一个元素在集合中时，该元素实际上可能不在集合中。这是因为不同元素的哈希值可能会碰撞到相同的位上。所以了解布隆过滤器的误判特性，在对准确性要求极高的场景下我们应该谨慎使用。对于可以容忍一定误判率的场景，如网页爬虫判断已访问的URL，这种误判是可以接受的哈希函数的选择：哈希函数的质量直接影响布隆过滤器的性能和误判率。如果哈希函数过于简单或相关性强，会增加哈希冲突的概率，导致误判率升高。应对措施：使用多个不同的哈希函数来降低冲突概率，常见的有MD5、SHA等哈希算法的组合使用。数据量预估和空间大小设置：初始设置的位数组空间过小，随着元素的不断添加，误判率会迅速增加；而空间过大则会浪费内存资源。4. 如何保证数据双写一致性？答： 使用什么方案需要根据业务场景分析使用合适的方式，常见的方式如下：延时双删：先删除 Redis 中的缓存数据，然后再更新数据库，更新数据库后，经过一定的延时（比如几百毫秒，具体时间需要根据业务和系统性能测试来确定）再次删除缓存，该方式会出现“延时期”时间内数据不一致的情况，适用于对数据一致性要求较高，能容忍一定时间内（延时期内）数据不一致的场景，比如一些电商系统中的商品信息、用户信息等非实时强一致性要求的数据更新。数据通知更新：当有数据更新操作时如使用'阿里canal'中间件，数据更新后将任务放入一个可靠的消息队列中，费者服务从队列中取出任务，先执行删除缓存操作，然后再进行数据库更新操作，确保操作的顺序性，适用于高并发的数据更新场景，尤其是多个不同系统或服务之间需要协同保证数据一致性的情况，但消息的处理存在一定的延迟，对实时性要求极高的场景可能不太适用分布式锁：适用严格保证同一时间只有一个操作进行数据更新和缓存操作的场景，如金融系统中的关键数据更新、高并发的抢购活动中的库存数据更新等,比如一些电商系统中的商品信息、用户信息等非实时强一致性要求的数据更新场景。使用分布式锁机制会带来一定的性能开销，获取和释放锁需要一定的时间，因此会有一定的效率，常见使用锁为读写锁5. Redis的持久化有哪些方式？答： Redis 持久化的方式主要有两种：RDB（Redis Database）和 AOF（Append Only File）RDB 是 Redis 默认的持久化方式，它是一种快照（snapshot）形式的持久化，将某一时刻的内存数据全部写入到磁盘中，形成一个数据快照文件，可以手动执行 SAVE 或 BGSAVE 命令来生成 RDB 文件。SAVE 命令会阻塞 Redis 服务器进程，直到 RDB 文件创建完毕；BGSAVE 命令会在后台异步进行快照操作，不会阻塞服务器的正常处理，优点：文件紧凑，体积小，便于备份和恢复，缺点：数据的安全性相对较低。由于是间隔一段时间进行快照，如果在两次快照之间发生故障，会丢失这期间的数据AOF 以日志的形式记录服务器所处理的每一个写操作命令，如 SET、SADD 等，在 Redis 服务器启动时会重新执行这些命令来恢复数据，过设置 appendfsync 选项来控制 AOF 同步的频率，有 always、everysec 和 no 三种选项。always 表示每个写操作都立即同步到 AOF 文件，数据安全性最高但性能影响较大；everysec 表示每秒同步一次，是默认选项，在性能和数据安全性之间取得平衡；no 表示由操作系统来控制何时同步，速度最快但数据安全性最低优点：数据安全性高。通过不同的同步频率设置，可以尽可能保证数据不丢失，即使在服务器崩溃时，最多也只会丢失一秒钟的数据缺点：AOF 文件通常比 RDB 文件大，因为它记录了每一个写操作命令，恢复数据的速度相对较慢6. Redis缓存删除策略是什么？答： 定期删除+惰性删除，定期删除：Redis 服务器每隔一段时间（默认是 100ms）就会从设置了过期时间的键集合中随机抽取一定数量的键进行检查，并删除其中的过期key,惰性删除：数据到期时不主动删除，而是在访问该键时，对其是否过期进行检查判断。如果发现键已过期，才执行删除操作；如果未过期或者键不存在，则正常进行其他操作7. Redis如何实现分布式？答： 使用 SETNX 命令实现简单分布式锁，SETNX（SET if Not eXists）是 Redis 的一个原子性操作命令。它尝试将一个键值对设置到 Redis 中，如果键不存在则设置成功并返回 1；如果键已经存在，则设置失败并返回，但实际业务中我们还要考虑锁的续期，死锁等问题8. Redis集群了解吗，都有哪些集群方式？答：主从复制：一个主节点（Master）可以有多个从节点（Slave），主节点负责处理写操作和数据同步到从节点，从节点主要负责处理读操作应用场景：适用于读操作远多于写操作的场景，通过多个从节点分担读负载，提高系统的读性能，可以作为数据备份的一种方式，当主节点出现故障时，从节点可以快速提升为主节点继续提供服务，增强数据的可靠性优点：实现相对简单，部署容易，可以通过增加从节点线性地扩展读性能，成本较低。缺点：写操作只能在主节点上进行，写性能受限于单个主节点，当主节点出现故障时，需要手动将从节点提升为主节点，存在一定的切换时间和数据丢失风险（取决于故障时的数据同步情况）哨兵模式集群：由 Redis 主从节点、哨兵节点（Sentinel）组成，哨兵节点是特殊的 Redis 实例，不存储数据，主要用于监控 Redis 主从节点的运行状态。应用场景：适用于对高可用性要求较高的场景，能够自动进行故障切换，减少人工干预，在主从复制的基础上，进一步提高了系统的可靠性和稳定性优点：实现了自动故障检测和转移，提高了系统的可用性，配置相对简单，客户端可以通过哨兵节点自动获取主节点信息缺点：仍然存在单个主节点的写性能瓶颈问题，当进行故障转移时，可能会出现短暂的服务中断（虽然时间较短，但对于某些对实时性要求极高的应用可能会有影响Cluster 集群(分片集群)：采用去中心化的架构，没有中心节点，数据被自动分割到多个节点上，每个节点负责一部分数据，master之间通过ping监测彼此健康状态应用场景: 适用于大规模数据存储和高并发访问的场景，能够满足海量数据和高吞吐量的需求,可以方便地进行横向扩展，通过增加节点来提高系统的存储能力和处理能力优点： 自动数据分片和负载均衡，提高了系统的扩展性和性能，去中心化架构，单个节点故障对系统的整体影响较小缺点： 配置和管理相对复杂，需要对数据分片和集群机制有深入的理解，数据迁移和槽重新分配时可能会对系统性能产生一定的影响9. Redis是单线程吗，为什么它还可以这么快？答： Redis 是单线程，但却很快。原因是它存于内存，内存读写比磁盘快很多，且有针对内存优化的数据结构，如 SDS、跳表、压缩列表。其次，采用高效事件驱动模型和非阻塞 I/O 多路复用，还有合适的数据淘汰策略。最后，单线程模型避免了线程切换开销和线程竞争问题，不用考虑同步机制带来的性能损耗，能将资源专注用于数据处理，这就是 Redis 虽单线程但速度快的主要原因。才哥最近开发了一个对于Java开发人员在线面试刷题的小程序，有需要的可以看看文章末尾，目前是免费阶段，也没有广告，才哥自己也在刷题备战明年黄金期，在线刷题很方便
一、什么是索引先说结论，mysql索引就是通过数据结构或存储结构(B+树)，从而提高查找的效率，举个例子，MySQL索引就像书的目录。想象一下我们小学使用的新华字典，当你想在一本厚厚字典中查询某个字的相关内容时，如果没有目录，你可能需要从头到尾一页一页地翻，非常耗时。而有了目录，你只需查看目录，然后直接跳到相关的页面，速度快了很多，在这个例子中，mysql表的数据，就好比新华字典里的字，而索引则对应字典的目录二、 索引有什么用（1）提高查询效率从新华字典的例子中，我们知道索引能显著加快数据的查找速度，当你在一个有数百万行数据的表中搜索某个特定值时，有了索引，MySQL可以快速定位到相关数据，而不需要扫描逐行扫描（2）排序数据有了索引可以帮助我们对某个数据按某个顺序排序，进一步提高查询效率，比如新华字典按拼音字母的顺序编排方式（2）唯一性约束可以创建唯一索引来确保某列的值是唯一的，这有助于保证数据的完整性和一致性。例如在用户登录账号通常应该是唯一的，可以在该列上创建唯一索引。三、索引分类与使用我们先新建一张数据表,在后面使用CREATE TABLE `t_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_no` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '订单编号', `good_name` varchar(125) COLLATE utf8_bin DEFAULT NULL COMMENT '商品名称', `good_content` varchar(125) COLLATE utf8_bin DEFAULT NULL COMMENT '商品介绍', `user_id` int(11) DEFAULT NULL COMMENT '用户ID', `order_price` decimal(10,2) DEFAULT NULL COMMENT '订单金额', `status` tinyint(2) DEFAULT NULL COMMENT '订单状态：0待支付，1已支付，2已取消', `create_time` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='订单表';1. 索引分类根据索引列数和数据结构等进行分类，索引的分类大致如下（1）按索引列个数分类单一(单列表)索引：基于单个列创建的索引。复合(联合)索引：基于多个列创建的索引。使用技巧： 如果查询通常基于单个列进行，且该列的选择性较高（即该列不同值的数量较多），那么单一索引可能是更好的选择。例如，在一个订单表中，如果经常根据订单号（order_no）来查询订单详情，为 order_no 列创建单一索引是合适的。然而，如果查询经常涉及多个列的组合条件，那么复合索引可能更有优势。比如，在一个员工表中，经常根据员工的部门（department）和职位（position）来查询员工信息，创建一个基于 department 和 position 的复合索引会更高效。但需要注意的是，复合索引的列顺序很重要。通常，将选择性更高（不同值更多）的列放在前面（最左前缀原则）另外，过多的索引会增加数据插入、更新和删除操作的开销，并且会占用更多的存储空间，基于这方面考虑更多考虑复合索引在决定使用单一索引还是复合索引时，需要综合考虑查询模式、数据分布、表的更新频率等因素进行权衡和选择。 创建索引# 单一索引,默认为Btree，B+树结构 ALTER TABLE t_order ADD INDEX idx_order_no (order_no); #复合索引，使用user_id和status建立联合索引 ALTER TABLE t_order ADD INDEX idx_user_id_status (user_id, status);（2）按索引数据结构分类B 树索引(B-Tree)：包括 B+树索引，是 MySQL 中常见的索引结构，适用于范围查询和排序操作。哈希索引(Hash)：通过哈希函数计算索引值的位置，适用于精确匹配查询，但不支持范围查询。使用技巧：hash索引对于精确匹配的查询效率比较高，可以快速定位数据，单hash索引不支持范围查询如大于、小于、介于，like 等范围索引查询操作，也不支持列索引排序，同时如果索引列重复值较多，会有hash冲突，同时它也不适用于频繁更新的列，需要重新计算hash维护成本低，例如t_order表中，可以考虑使用hash结构的索引B-Tree结构索引支持范围查询，同时也支持排序操作，插入和删除索引相比于hash索引更具有优势，适合高并发场景，但相比于哈希索引，需要更多的存储空间来存储索引结构，对于精确匹配查询，速度可能略逊于哈希索引，比如t_order表中，我们需要对订单的创建时间create_time做排序，同时经常需要对时间范围内的订单做范围查询，因为create_time更适合B-Tree结构 创建索引# BTREE索引不指定'USING BTREE'时默认就是BTREE类型索引 ALTER TABLE `t_order` ADD INDEX `idx_create_time`(`create_time`) USING BTREE; #Hash索引，创建order_no的hash索引 ALTER TABLE `t_order` ADD INDEX `idx_order_no`(`order_no`) USING HASH;（3） 按索引作用分类主键索引(PRIMARY KEY)：用于唯一标识表中的每一行数据，基于主键创建。唯一索引(UNIQUE)：确保索引列的值是唯一的，但允许为 NULL 值。普通索引(INDEX)：用于提高查询性能，对数据的唯一性没有严格要求。全文索引(FULLTEXT)：主要用于文本列，可以对大量文本进行快速搜索。空间索引(SPATIAL)：用于快速查询空间数据，如地理信息系统 (GIS) 和位置相关的查询前缀索引：建立前缀索引可以减少索引的大小和维护成本，同时在大多数情况下仍然能够满足常见的查询需求。例如，当查询条件中经常根据商品名称的前 10 个字符进行筛选时，该索引将发挥作用，提高查询效率。创建索引#创建主键索引，一般主键索引在建表时候就确定了 ALTER TABLE `t_order` ADD PRIMARY KEY (`id`); #创建唯一索引 ALTER TABLE `t_order`ADD UNIQUE INDEX `idx_order_no`(`order_no`); # 创建普通索引 ALTER TABLE `t_order`ADD UNIQUE INDEX `idx_user_id`(`user_id`); # 全文索引(FULLTEXT)，使用 MATCH AGAINST 操作符进行全文搜索 ALTER TABLE t_order ADD FULLTEXT INDEX `idx_good_content` (good_name, good_content); # 全文索引查询 SELECT * FROM t_order WHERE MATCH(good_name, good_content) AGAINST('格力'); # 前缀索引，使用like 查询如 '手机%' ALTER TABLE t_order ADD INDEX idx_good_name (good_name(10));（4）按存储方式分类聚簇索引：表数据和索引的叶子节点存储在一起。也就是说，通过聚簇索引可以直接获取到数据，聚簇索引通常基于主键创建，如果表没有定义主键，会选择一个不允许为 NULL 的唯一索引作为聚簇索引非聚簇索引：索引的叶子节点存储的是指向数据行的指针，非聚簇索引在范围查询时可能需要多次回表查询，性能相对较差下图我们可以看出聚簇索引Id的叶子节点存储了表行的数据，而非聚簇索引name叶子节点存储的是指向聚簇索引行的记录指针2. 索引创建原则MySQL 索引创建的原则：1.选择合适的列通常选择经常用于查询、连接、排序和分组操作的列创建索引，对于很少使用或几乎不用于查询的列，避免创建索引，以免增加插入、更新和删除操作的开销。2.索引列的选择性优先为选择性高（不同值的数量较多）的列创建索引。例如，性别列只有两个可能的值（启用停用），选择性低，通常不太适合创建索引；而用户 ID 列通常具有较高的选择性。3.避免过度索引过多的索引会增加数据维护的成本，并可能降低写入性能。只创建真正必要的索引。4.优先考虑联合索引如果经常基于多个列的组合条件进行查询，考虑创建联合索引。但要注意列的顺序，将选择性高的列放在前面。5.主键和唯一索引为表定义主键，确保数据的唯一性和完整性，对于需要确保唯一性但又不是主键的列，可以创建唯一索引。6.短索引如果可能尽量使用较短的数据类型来创建索引，这可以减少索引存储空间和提高性能，对于较长内容的字段使用前缀索引7.覆盖索引（名词2）尽量创建能够覆盖查询的索引，即通过索引就可以获取到查询所需的全部数据，避免回表操作，提高查询效率。8. 避免索引列NULL值在 MySQL 中，允许为 NULL 的列创建索引，当索引列为 NULL 值可能会对索引的使用和性能产生一些影响：索引的存储：NULL 值需要额外的空间来标识，这可能会增加索引的存储空间。比较操作：在比较操作中，NULL 的处理方式与其他值不同。例如，WHERE column = NULL 通常不会返回任何结果，应该使用 WHERE column IS NULL 来判断是否为 NULL索引的选择性：如果某一列中存在大量的 NULL 值，可能会降低索引的选择性，从而影响索引的效果。复合索引：在复合索引中，如果某一列允许为 NULL，可能会导致一些复杂的情况，影响索引的使用效率。四、索引失效场景MySQL中的索引并不是在所有情况下都能有效工作，有时会失效，导致查询性能下降。了解这些情况有助于开发设计除更高效的查询和索引。以下是一些常见的索引失效情况：1.条件使用函数或表达式当在查询条件中使用了函数或表达式时，索引会失效。# 使用date SELECT * FROM t_order WHERE FROM_UNIXTIME(create_time, '%Y-%m-%d') = '2024-07-20';2.使用LIKE以通配符开头当使用LIKE查询时，如果通配符（%）出现在字符串的开头，索引会失效。SELECT * FROM t_order WHERE good_name LIKE '%手机'3.复合索引为遵循最左前缀原则在多列索引中，如果查询条件不包括索引的最左边列，索引会失效。这称为“最左前缀”原则。#假设我们订单表的复合多列索引有user_id和order_no ALTER TABLE t_order ADD INDEX idx_user_order (user_id, order_no); #以下查询，未遵循最左前缀原则，索引失效 SELECT * FROM t_order WHERE order_no='2024072822201'4. 数据类型不匹配查询条件中的数据类型与索引列的数据类型不匹配时，可能会导致索引失效。# user_id为整型 SELECT * FROM t_order where user_id='3'5. 使用OR条件当OR条件中的某些列没有索引时，索引会失效。# order_price没有索引 SELECT * FROM t_order where user_id=300 OR order_price=100五、SQL查询分析-EXPLAIN当我们API接口或函数执行较慢的时候，我们可通过一些工具来定位是否是sql慢查询,索引是否命中等。常见的工具如Arthas，Skywalking，或者开启mysql的慢查询日志，来分析哪些sql查询耗时较长。而EXPLAIN 是 MySQL 提供的一个命令，用于显示执行计划，让你了解 MySQL 是如何执行查询的。通过 EXPLAIN 输出的信息，可以分析和优化查询性能。以下是如何使用 EXPLAIN 以及解释其输出的基本方法。1. EXPLAIN结果字段说明id: 查询的序列号，标识查询中执行的顺序。复杂的查询可能会有多个步骤，每个步骤都有一个 id。select_type: 查询的类型，表示查询中各个部分的类型。常见的有 SIMPLE（简单查询，不包含子查询或联合查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。table: 正在访问的表。type: 表示连接类型或访问类型。常见的有：ALL: 全表扫描，性能最差。index: 全索引扫描，类似于全表扫描，但扫描的是索引。range: 范围扫描，通常用于带有 BETWEEN 或范围条件的查询。ref: 使用非唯一索引扫描。eq_ref: 使用唯一索引扫描，通常用于主键或唯一键的查找。const/system: 表示表只有一行匹配，性能最好。possible_keys: 查询中可能使用的索引。key: 实际使用的索引。如果没有使用索引，则显示 NULLkey_len: 使用的索引的长度（字节数），越短越好。ref: 显示索引的哪一列被用于查找。rows: 估计要读取的行数。这个值是一个估计值，不是精确值。Extra: 额外的信息，提供更多关于查询执行的信息以及优化建议，常见的如下Using index：查询只使用索引而不需要访问表数据。Using where：使用 WHERE 子句过滤行。Using temporary：查询需要使用临时表来存储中间结果。Using filesort：MySQL 需要额外的排序操作，而不是按索引顺序读取行。Using index condition：部分条件通过索引扫描完成。通常我们根据结果的通过执行结果调整设计表的索引或sql查询语句，而我们则需要重点关注的字段主要为如下几个字段key和key_len：检查是否命中了索引（索引本身存在是否有失效等情况）type：查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描extra建议:，判断是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复六、名词说明1.回表查询回表查询是指通过索引获取到的数据并不是最终需要的完整数据，还需要根据索引找到的主键值再次到主键索引中查找完整的行数据。我们已t_order表为例，如果假设good_name为我们的一个普通索引SELECT * FROM t_order WHERE good_name = '手机%';首先，通过 good_name 索引可以快速定位到满足条件的记录的主键值。但由于索引中只包含 good_name 和主键值，而上述查询需要获取所有字段（*），所以此时就需要根据主键值再到主键索引中进行查询，获取完整的行数据，这个过程就是回表查询。回表查询会增加数据库的 I/O 操作，可能会影响查询性能。在某些情况下，可以通过创建覆盖索引来避免回表查询，提高查询效率。2.覆盖索引覆盖索引（Covering Index）是指一个索引包含了查询中需要的所有列的数据，这样在查询时无需回表查询获取完整的行数据，直接从索引中就能获取到所有需要的信息，从而提高查询性能。我们还以为上面的例子为例子,sql如下SELECT id, good_name FROM t_student WHERE good_name = '手机%';如果在 good_name 列上创建了索引，并且索引中同时包含了 id 、 good_name 两列的数据，那么这个索引就是覆盖索引。覆盖索引的优点在于：减少磁盘 I/O 操作：无需回表查询，降低了磁盘的读取次数，提高了查询效率。提高并发性能：减少了锁的竞争，因为不需要对表数据进行锁定来获取完整的行数据。例如，在一个电商系统中，对于订单表经常根据订单状态和下单时间进行查询并获取订单号和金额，那么可以在订单状态和下单时间列上创建一个包含订单号和金额的联合索引，以形成覆盖索引，加快查询速度同时我们也可以通过覆盖索引实现超大表的分页查询,优化查询效率SELECT * FROM tb_order t , (SELECT id FROM t_order order by id limit 9000000,0) t1 where t.id=t1.id
在xml中我们可以这样拼接like关键词MyBatis+Oracle的like用法field_name like CONCAT(CONCAT('%',#{field_name}),'%')MyBatis+MySQL的like用法field_name like concat('%',#{field_name},'%')
Mysql数据表生成文档表结构，导出并生成word文档的方法
因为项目需要编写库表结构word文档，需要交付验收，下面给大家介绍一种我用的方式步骤一：使用sql生成表结构数据SELECT ORDINAL_POSITION 序号, COLUMN_NAME 列名, COLUMN_TYPE 数据类型, -- COLUMN_DEFAULT 默认值, (CASE IS_NULLABLE WHEN 'NO' THEN '否' WHEN 'YES' THEN '是' ELSE '' END) 允许为空, COLUMN_KEY 主键, COLUMN_COMMENT 注释 FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='dbname' -- 库名 AND table_name='table_demo' -- 表明步骤二：使用Navicat Premium工具导出Excle我们将查询成功的数据导出Excel，并将内容复制到doc中即可有其它更好方式的可以推荐下哦，虽然这个方法步骤有点多，但是总比一个个复制要好些，写代码动手能力比较强的伙伴，可以考虑使用上面的sql查询代码来生成表格，然后用代码生成word文档，navicat premium破解版获取点击： navicat premium破解版下载
场景生产数据定时备份的意义大家应该都是知道的，很多人备份只是备份到了本地，但是也会存在一定的隐患，比如客户一直不用系统，我们也不去注意，哪天服务器到期了过了几个月客户突然要用方向数据没了。。。使用到的工具主要看大家的服务器的安装情况，如果是安装了宝塔面板这种，有定时任务工具直接使用，而且也有免费的七牛云插件，设置起来超级简单，如果安装宝塔的不懂设置可以评论区留言，实在是太简单了本文就不讲了，说实话是会点鼠标就可以弄了基于linux系统内备份大概的意思就是你的mysql是自己用命令安装的。七牛平台shell脚本linux定时任务需要七牛云账号，用户需要自己创建，或者其它用其它云盘原理都是一样的（一）下载qshell工具并安装设置1. 工具下载说明文档与下载地址： 七牛工具下载 工具是七牛官方提供的，下载工具后传到服务器，主要看自己服务器的架构，我的服务是linux x86的，根据自己服务器去下载，把文件上传至服务目录/home/backup/shell 目录。并赋予权限chomd +x qshell2. 登录设置七牛云账号注意需要在工具目录下执行，如果需要任何目录执行需要添加环境变量可查看官方文档说明，更多说明以官方最新文档为准# 参数说明可自己根据情况修改 #ak :官方获取 #sk: 官方平台获取 #name : 任意用户名，不是七牛的账户，比如 db-test qshell account <ak> <sk> <name> #完成命令参考（二） shell编辑脚本在目录/home/backup/shell 下新建脚本backup.sh#!/bin/sh # mysql data backup script # # use mysqldump --help,get more detail. # 需要设置环境变量，crond中默认读取不到我们的一些环境 PATH=$PATH:/home/backup/shell export PATH dbname=<dbname> #备份文件目录 bakDir=/home/backup/sql logFile=/home/backup/backup.log month=` date +%Y%m ` # 七牛的备份目录，当前按数据库为一级目录，月份为2级目录如：dbname/202004/****.gz.sql qiniuPath=typecho_joe_theme/$month # 七牛空间名 qiniuBucket=<bucketName> datetime=`date +%Y%m%d%H%M%S` keepDay=7 echo "-------------------------------------------" >> $logFile echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile echo "--------------------------" >> $logFile cd $bakDir bakFile=$dbname.$datetime.sql.gz # yum,apt-get,源码编译安装释放 mysqldump -u$user -p$password $dbname | gzip > $bakFile #docker安装执行方式,需要修改<dbname>为自己的数据库 #docker exec mysql5.6 sh -c 'exec mysqldump <dbname> -uroot -p"$MYSQL_ROOT_PASSWORD"' | gzip > $bakDir/$bakFile echo "数据库 [$dbname] 备份完成" >> $logFile echo "$bakDir/$bakFile" >> $logFile echo "开始上传备份文件至七牛云存储" >> $logFile #七牛上传命令，分片上传适用大文件，当前使用低频储存，具体命令可以查看文档 qshell rput $qiniuBucket $qiniuPath/$bakFile $bakDir/$bakFile --storage 1 | sed -r "s/\x1B\[([0-9]{1,2}(;[0-9]{1,2})?)?[m|K]//g" >> $logFile 2>&1 echo "删除${keepDay}天前的备份文件" >> $logFile find $bakDir -ctime +$keepDay >> $logFile find $bakDir -ctime +$keepDay -exec rm -rf {} \; echo " " >> $logFile echo " " >> $logFile 参考文献：qshell命令说明rput 操作说明（二） 添加定时任务需要使用到cron命令，本文不展开讲这个，需要了解的可以参考以下文献文献一： crontab配置方法文献二： Linux系统crontab定时任务的配置概要与常见问题文献三： crontab 与 环境变量下面直说具体操作，执行器需要先检查crond的状态service crond status，如果是在运行中就没什么问题编辑定时任务#修改当前用户定时任务 crontab -e 添加任务脚本，操作和vi是一样的# 每天凌晨3点执行脚本， 0 3 * * * /home/backup/backup.sh # 如果是测试可以用以下的脚本,意思是没2分钟执行一次，看看有没有成本 */2 * * * * /home/backup/backup.sh查看定时任务#查看当前任务情况 crondtab -l（三） 完成设置完成以后最好要检查下是否成功，建议先设置按分钟执行备份看看，如果中间有什么问题要多百度，一般都可以搜得到
【MySql】Specified key was too long; max key length is 767 bytes的解决方案
问题背景由于业务需要，需要做数据库迁移，迁移由5.7版本得mysql到现有的mysql5.6版本，主要是因为更innodb_large_prefix 参数有关由于MySQL的InnoDB引擎表索引字段长度的限制为767字节，因此对于多字节字符集的大字段或者多字段组合，创建索引时会出现该问题。在5.6中innodb_large_prefix 默认关闭，在5.7中默认开启。在5.6中默认为 offSpecified key was too long; max key length is 767 bytes原来是主键的长度太长，最大长度仅支持767字节，注意，这里的是字节。那么，我定义一个主键是varchar(500)的字段，不管我使用utf8编码，那么500字符长度将占用5003的字节长度（utf8最多支持3个字节长度）；或是我使用utf8mb4编码，那么500字符长度将占用5004的字节长度（utf8mb4最多支持4个字节长度），都大大超过了767字节。如何去解决呢？先检查一下是不是数据库被限制了索引的大小，查询innodb_large_prefix这个字段，这个字段限制了索引前缀的大小。关闭此限制后，索引前缀的大小将可以达到3072字节。方法1：修改全局变量修改全局变量 ，当mysql重启后将失效，建议直接修改配置文件SHOW variables like 'innodb_large_prefix';如果查询的值是OFF的话 执行下面命令SET GLOBAL INNODB_LARGE_PREFIX = ON;另外，innodb_large_prefix这个属性在5.6上是默认关闭的，而在5.7上是默认开启的。执行完了 之后 还得查看当前的innodb_file_format引擎格式类型是不是BARRACUDA执行SHOW variables like 'innodb_file_format';如果不是的话则需要修改SET GLOBAL innodb_file_format = BARRACUDA;方法二：修改配置[mysqld] innodb_large_prefix = on innodb_file_format = BARRACUDA最后，创建表的时候，还需要指定表的 row format 格式为 Dynamic 或者 Compressed，如下示例：CREATE TABLE test( name varchar(500) CHARACTER SET utf8 COLLATE utf8_bin, PRIMARY KEY (`name `) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;版权声明：本文为CSDN博主「SunAlwaysOnline」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。参考原文链接：https://blog.csdn.net/qq_33591903/article/details/103927547
Mysql数据库误删/错删数据恢复-binlog日志使用
一、binlog 基本认识MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的，也就是大家比较常听到主从复制，和读写分离的应用场景数据恢复，通过使用mysqlbinlog工具来使恢复数据，也是大家说的数据恢复，用于数据误删找回的场景使用。二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。二、开启binlog日志编辑打开mysql配置文件vi /usr/local/mysql/etc/my.cnf在[mysqld] 区块下，设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)； #开启log日志 log-bin = mysql-bin #默认路径可修改 log_bin = /var/lib/mysql/mysql-bin.log #日志过期时间，设置为0则永不过期，默认设置不过期 expire_logs_days = 7 #模式 binlog_format = ROW #超过max_binlog_size或超过6小时会切换到下一序号文件 max_binlog_size = 100M #二进制日志缓冲大小,通过show status like 'binlog_%';查看调整写入磁盘的次数，写入磁盘为0最好 binlog_cache_size = 16M max_binlog_cache_size = 256M #改参数默认是打开的，在数据库启动后立即启动自动relay log恢复，在恢复过程中，创建一个新的relay log文件，将sql线程的位置初始化到新的relay log，并将i/o线程初始化到sql线程位置。 relay_log_recovery = 1 关于MYSQL中binlog_format参数说明重启mysqld服务使配置生效三、登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启登录服务器mysql -uroot -p123456 mysql> show variables like 'log_%'; +----------------------------------------+---------------------------------------+ | Variable_name | Value | +----------------------------------------+---------------------------------------+ | log_bin | ON | ------> ON表示已经开启binlog日志 | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /usr/local/mysql/data/martin.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+---------------------------------------+四、常用binlog日志操作命令1.查看所有binlog日志列表 mysql> show master logs;2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值 mysql> show master status;3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件 mysql> flush logs; 注： 每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；4.重置(清空)所有binlog日志，请勿在正式环境操作mysql> reset master;五、查看某个binlog日志内容，常用有两种方式：1.使用mysqlbinlog自带查看命令法：注: binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看binlog日志与数据库文件在同目录中(我的环境配置安装是选择在/usr/local/mysql/data中)使用mysqlbinlog命令时如果报错，就加上 --no-defaults选项mysqlbinlog /usr/local/mysql/data/mysql-bin.000013下面截取一个片段分析： # at 552 #131128 17:50:46 server id 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0 ---->执行时间:17:50:46；pos点:665 SET TIMESTAMP=1385632246/*!*/; update zyyshop.stu set name='李四' where id=4 ---->执行的SQL /*!*/; # at 665 #131128 17:50:46 server id 1 end_log_pos 692 Xid = 1454 ---->执行时间:17:50:46；pos点:692 注:server id 1 数据库主机的服务号；end_log_pos 665 pos点thread_id=11 线程号2.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令： show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 选项解析：IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)LIMIT [offset,] 偏移量(不指定就是0)row_count 查询总条数(不指定就是所有行) 截取部分查询结果： *************************** 20. row *************************** Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名 Pos: 11197 ----------------------------------------------------------> pos起始点: Event_type: Query ----------------------------------------------------------> 事件类型：Query Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的 End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点) Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句 *************************** 21. row *************************** Log_name: mysql-bin.000021 Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点) Event_type: Query Server_id: 1 End_log_pos: 11417 Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */ *************************** 22. row *************************** Log_name: mysql-bin.000021 Pos: 11417 Event_type: Query Server_id: 1 End_log_pos: 11510 Info: use `zyyshop`; DROP TABLE IF EXISTS `type`这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；A.查询第一个(最早)的binlog日志： mysql> show binlog events\G; B.指定查询 mysql-bin.000021 这个文件：mysql> show binlog events in 'mysql-bin.000021';C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：mysql> show binlog events in 'mysql-bin.000021' from 8224;D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条 mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10;六、binlog日志恢复实验我们来模拟数据恢复的场景 ,zyyshop是数据库1.(时间线1)假设现在是凌晨4:00，我的计划任务开始执行一次完整的数据库备份：将zyyshop数据库备份到 /root/BAK.zyyshop.sql 文件中：mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql大约过了若干分钟，备份完成了，我不用担心数据丢失了，因为我们有备份了由于我使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下： mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000023 | 120 | | | +------------------+----------+--------------+------------------+也就是说， mysql-bin.000023 是用来记录4:00之后对数据库的所有“增删改”操作。(时间线2)早9:00上班了，业务的需求会对数据库进行各种“增删改”操作~~~ @ 比如：创建一个学生表并插入、修改了数据等等： CREATE TABLE IF NOT EXISTS `tt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(16) NOT NULL, `sex` enum('m','w') NOT NULL DEFAULT 'm', `age` tinyint(3) unsigned NOT NULL, `classid` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;导入实验数据 insert into zyyshop.tt(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');查看数据 mysql> select * from zyyshop.tt; +----+----------+-----+-----+---------+ | id | name | sex | age | classid | +----+----------+-----+-----+---------+ | 1 | yiyi | w | 20 | cls1 | | 2 | xiaoer | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | | 4 | lisi | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | +----+----------+-----+-----+---------+中午时分又执行了修改数据操作mysql> update zyyshop.tt set name='李四' where id=4;mysql> update zyyshop.tt set name='小二' where id=2;修改后的结果： mysql> select * from zyyshop.tt; +----+----------+-----+-----+---------+ | id | name | sex | age | classid | +----+----------+-----+-----+---------+ | 1 | yiyi | w | 20 | cls1 | | 2 | 小二 | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | | 4 | 李四 | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | +----+----------+-----+-----+---------+(时间线3)假设此时是下午18:00，莫名地执行了一条悲催的SQL语句，整个数据库都没了： # 删除数据库请勿使用正式环境数据 mysql> drop database zyyshop;七、怎么解决恢复 ？别慌！先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；备份一下最后一个binlog日志文件： ll /usr/local/mysql/data | grep mysql-bin cp -v /usr/local/mysql/data/mysql-bin.000023 /root/此时执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，理论说 mysql-bin.000023 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件； mysql> flush logs; mysql> show master status;2 .读取binlog日志，分析问题方式一：使用mysqlbinlog读取binlog日志：mysqlbinlog /usr/local/mysql/data/mysql-bin.000023方式二：登录服务器，并查看(推荐)： mysql> show binlog events in 'mysql-bin.000023'; 以下为末尾片段： +------------------+------+------------+-----------+-------------+------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+------------+-----------+-------------+------------------------------------------------------------+ | mysql-bin.000023 | 922 | Xid | 1 | 953 | COMMIT /* xid=3820 */ | | mysql-bin.000023 | 953 | Query | 1 | 1038 | BEGIN | | mysql-bin.000023 | 1038 | Query | 1 | 1164 | use `zyyshop`; update zyyshop.tt set name='李四' where id=4| | mysql-bin.000023 | 1164 | Xid | 1 | 1195 | COMMIT /* xid=3822 */ | | mysql-bin.000023 | 1195 | Query | 1 | 1280 | BEGIN | | mysql-bin.000023 | 1280 | Query | 1 | 1406 | use `zyyshop`; update zyyshop.tt set name='小二' where id=2| | mysql-bin.000023 | 1406 | Xid | 1 | 1437 | COMMIT /* xid=3823 */ | | mysql-bin.000023 | 1437 | Query | 1 | 1538 | drop database zyyshop | +------------------+------+------------+-----------+-------------+------------------------------------------------------------+ 通过分析，造成数据库破坏的pos点区间是介于 1437--1538 之间，只要恢复到1437前就可。3.现在把凌晨备份的数据恢复：mysql -uroot -p123456 -v < /root/BAK.zyyshop.sql;注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。但今天一整天(4:00--18:00)的数据肿么办呢？就得从前文提到的 mysql-bin.000023 新日志做文章了......4 .从binlog日志恢复数据恢复语法格式：mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 --start-datetime="2013-11-29 13:18:54" 起始时间点 --stop-datetime="2013-11-29 13:21:53" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志) 不常用选项： -u --user=name Connect to the remote server as username.连接到远程主机的用户名 -p --password[=name] Password to connect to remote server.连接到远程主机的密码 -h --host=name Get the binlog from server.从远程主机上获取binlog日志 --read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；A.完全恢复(本例不靠谱，因为最后那条 drop database zyyshop 也在日志里，必须想办法把这条破坏语句排除掉，做部分恢复)/mysqlbinlog /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop `` B.指定pos结束点恢复(部分恢复)： @ --stop-position=953 pos结束点 注：此pos结束点介于“导入实验数据”与更新“name='李四'”之间，这样可以恢复到更改“name='李四'”之前的“导入测试数据” # /usr/local/mysql/bin/mysqlbinlog --stop-position=953 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop 在另一终端登录查看结果(成功恢复了)： mysql> select * from zyyshop.tt; +----+----------+-----+-----+---------+ | id | name | sex | age | classid | +----+----------+-----+-----+---------+ | 1 | yiyi | w | 20 | cls1 | | 2 | xiaoer | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | | 4 | lisi | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | +----+----------+-----+-----+---------+``C.指定pso点区间恢复(部分恢复)：更新 name='李四' 这条数据，日志区间是Pos[1038] --> End_log_pos[1164]，按事务区间是：Pos[953] --> End_log_pos[1195]；更新 name='小二' 这条数据，日志区间是Pos[1280] --> End_log_pos[1406]，按事务区间是：Pos[1195] --> End_log_pos[1437]；c1.单独恢复 name='李四' 这步操作，可这样：mysqlbinlog --start-position=1038 --stop-position=1164 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop也可以按事务区间单独恢复，如下：mysqlbinlog --start-position=953 --stop-position=1195 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshopc2.单独恢复 name='小二' 这步操作，可这样：mysqlbinlog --start-position=1280 --stop-position=1406 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop也可以按事务区间单独恢复，如下：mysqlbinlog --start-position=1195 --stop-position=1437 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshopc3.将 name='李四'、name='小二' 多步操作一起恢复，需要按事务区间，可这样：mysqlbinlog --start-position=953 --stop-position=1437 --database=zyyshop /usr/local/mysql/data/mysql-bin.000023 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshopD.在另一终端登录查看目前结果(两名称也恢复了)： mysql> select * from zyyshop.tt; +----+----------+-----+-----+---------+ | id | name | sex | age | classid | +----+----------+-----+-----+---------+ | 1 | yiyi | w | 20 | cls1 | | 2 | 小二 | m | 22 | cls3 | | 3 | zhangsan | w | 21 | cls5 | | 4 | 李四 | m | 20 | cls4 | | 5 | wangwu | w | 26 | cls6 | +----+----------+-----+-----+---------+E.也可指定时间区间恢复(部分恢复)：除了用pos点的办法进行恢复，也可以通过指定时间区间进行恢复，按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。比如，我把刚恢复的tt表删除掉，再用时间区间点恢复 mysql> drop table tt; @ --start-datetime="2013-11-29 13:18:54" 起始时间点 @ --stop-datetime="2013-11-29 13:21:53" 结束时间点执行命令 mysqlbinlog --start-datetime="2013-11-29 13:18:54" --stop-datetime="2013-11-29 13:21:53" --database=zyyshop /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop总结：所谓恢复的原理 ，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
MYSQL中BINLOG_FORMAT的三种模式mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。① STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)② ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。③ MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。binlog复制配置在mysql的配置文件my.cnf中，可以通过一下选项配置binglog相关代码如下 复制代码binlog_format = MIXED //binlog日志格式，mysql默认采用statement，建议使用mixedlog-bin = /data/mysql/mysql-bin.log //binlog日志文件expire_logs_days = 7 //binlog过期清理时间max_binlog_size = 100m //binlog每个日志文件大小binlog_cache_size = 4m //binlog缓存大小max_binlog_cache_size = 512m //最大binlog缓存大小三 MIXED说明对于执行的SQL语句中包含now()这样的时间函数，会在日志中产生对应的unix_timestamp()*1000的时间字符串，slave在完成同步时，取用的是sqlEvent发生的时间来保证数据的准确性。另外对于一些功能性函数slave能完成相应的数据同步，而对于上面指定的一些类似于UDF函数，导致Slave无法知晓的情况，则会采用ROW格式存储这些Binlog，以保证产生的Binlog可以供Slave完成数据同步。现在来比较以下 SBR 和 RBR 2中模式各自的优缺点：SBR 的优点：历史悠久，技术成熟binlog文件较小binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况binlog可以用于实时的还原，而不仅仅用于复制主从版本可以不一样，从服务器版本可以比主服务器版本高SBR 的缺点：不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。调用具有不确定因素的 UDF 时复制也可能出问题使用以下函数的语句也无法被复制：LOAD_FILE()UUID()USER()FOUND_ROWS()SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)INSERT ... SELECT 会产生比 RBR 更多的行级锁复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事确定了的 UDF 也需要在从服务器上执行数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错执行复杂语句如果出错的话，会消耗更多资源RBR 的优点：任何情况都可以被复制，这对复制来说是最安全可靠的和其他大多数数据库系统的复制技术一样多数情况下，从服务器上的表如果有主键的话，复制就会快了很多复制以下几种语句时的行锁更少：INSERT ... SELECT包含 AUTO_INCREMENT 字段的 INSERT没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句执行 INSERT，UPDATE，DELETE 语句时锁更少从服务器上采用多线程来执行复制成为可能RBR 的缺点：binlog 大了很多复杂的回滚时 binlog 中会包含大量的数据主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题UDF 产生的大 BLOB 值会导致复制变慢无法从 binlog 中看到都复制了写什么语句当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生另外，针对系统库 mysql 里面的表发生变化时的处理规则如下：如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 SBR 模式记录注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。原文转载：https://www.cnblogs.com/xingyunfashi/p/8431780.html
这个问题会导致以下几个问题出现。mysqsl查询视图，函数时显示密码错误迁移数据库时，显示查询密码错误等1.mysql中的definer是什么，有什么作用？我们在mysql创建view、trigger、function、procedure、event时都会定义一个Definer=‘xxx’,类似如下：CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `v_ questions` AS SELECT `q`.`id` AS `id`, `q`.`title` AS `title` FROM Test q; -- 或者像这样的： CREATE DEFINER=`root`@`%` PROCEDURE `user_count`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN select count(*) from mysql.user; END加红的部分SQL SECURITY 其实后面有两个选项，一个为DEFINER，一个为INVOKERSQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示按定义者拥有的权限来执行INVOKER 表示用调用者的权限来执行。默认情况下，系统指定为DEFINER 以存储过程为例：（1）MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；（2）如果SQL SECURITY子句指定为DEFINER，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限；（3）如果SQL SECURITY子句指定为INVOKER，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；（4）如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。我们来看下面几个小例子。先授权一个： grant all on testdb.* to 'user1'@'%' identified by '000000' with grant option; 然后我们创建一个存储过程如下： USE `testdb`; DROP procedure IF EXISTS `user_count`; DELIMITER $$ USE `testdb`$$ CREATE DEFINER=`root`@`%` PROCEDURE `user_count`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER COMMENT '' BEGIN select count(*) from mysql.user; END$$ DELIMITER ;用root帐号登陆： mysql> use testdb; Database changed mysql> call user_count(); +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 可以正常查询出来。 我们再用user1进行登陆： mysql> use testdb; Database changed mysql> call user_count(); ERROR 1142 (42000): SELECT command denied to user 'user1'@'localhost' for table 'user' 现系统报错查询不到了，这是因为我们在上述定义的SQL SECURITY值为INVOKER，存储过程执行过程中会以user1具有的权限来执行，其中调用到了mysql的库，而我们的user1帐户只有testdb库的使用权限，所以会返回失败。我们把上面的invoker改为definer再来试一下：update mysql.proc set security_type='DEFINER' where db='testdb' and name='user_count'; 再次用user1进行登陆： mysql> use testdb; Database changed mysql> call user_count(); +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)2.如果方便修改mysql中所有已经定义到的definer？由于前期在测试库上开发的缘故，我们经常定义到的definer为root@%，后来搬移到生产库上又得改回来，存在着大量的更新，上百个的视图，函数等一个个改不免太麻烦并且也可能遗漏。如下为总结出的方便修改所有definer的方法，可以直到查漏补缺的作用。现在在mysql涉及的definer有view、trigger、function、procedure、event。我们一个个作介绍。1.修改function、procedure的definerselect definer from mysql.proc; -- 函数、存储过程update mysql.proc set definer='user@localhost'; -- 如果有限定库或其它可以加上where条件2.修改event的definerselect DEFINER from mysql.EVENT; -- 定时事件update mysql.EVENT set definer=' user@localhost ';3.修改view的definer相比function的修改麻烦点：select DEFINER from information_schema.VIEWS; select concat("alter DEFINER=user@localhost SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER<>'user@localhost'; 查询出来的语句再执行一遍就好了。4.修改trigger的definer目前还没有具体方便的方法，可以借助工具端如HeidiSQL、sqlyog等来一个个修改。注意改前有必要锁表,因为如果改的过程中有其它表改变而触发，会造成数据不一致。Flush tables with readlockUnlock tables文章转载出处：https://www.cnblogs.com/zejin2008/p/4767531.html
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'如何解决？
原因：mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令。添加 --no-defaults 参数mysqlbinlog --no-defaults mysql-bin.000001 > 01.sql注意：加入该项会导致utf8mb4 变为空字符串，会影响数据一致性问题