MySQL数据库

X-Dragon烟雨任平生

已于 2025-02-24 15:35:52 修改

阅读量279

点赞数

分类专栏：数据库文章标签： mysql 数据库索引

于 2021-01-20 01:10:41 首次发布

本文链接：https://blog.youkuaiyun.com/xielong0509/article/details/112855815

版权

数据库专栏收录该内容

10 篇文章

订阅专栏

本文围绕MySQL展开，介绍了索引模块，包括二叉树、B树、B+树、Hash索引等结构及特点，还说明了分库分表的原因与方法。此外，探讨了慢SQL优化的原因、定位与分析手段，以及MySQL保障数据一致性的方式，如本地事务、主从集群和与Redis的数据一致性处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

语法

关系型数据库

1、索引模块：

为啥用索引：快速查询数据。
索引结构：
- 二叉树：二分查找
- B Tree
- B+ Tree
- HASH结构

参考链接: https://blog.youkuaiyun.com/wl044090432/article/details/53423333?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&dist_request_id=1328603.11577.16149284984438891&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control

1.1 二叉树索引

时间复杂度：O（logn）
缺点：
- 1、数据增删，有可能变成（线性树）链表。O(n)
- 2、数据增删，I/O太频繁。

在这里插入图片描述

1.2 B树

查找效率Olog(n)
特征：前四条限定孩子树和深度。第五条限定节点关键字和大小。
- 根节点至少两个孩子。
- 树种每个节点最多m个孩子。(m>2)
- 除了根节点和叶子节点，其他每个节点至少有ceil(m/2)个孩子。
  - ceil（取上限函数,整数）ceil(3/2)=2。
- 所有叶子结点都是同一层。
- 第五点：下图

在这里插入图片描述

在这里插入图片描述
优势：
- 增删节点，会采取(分裂合并)策略，不会变成线性树。

1.3 B+树

B+树比B树多4条特点：

在这里插入图片描述

1.4 Hash索引：

1.4.1 Hash索引优势

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

1.4.2 缺点：

在这里插入图片描述

1.5 BitMap:

位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。
此外，位图索引适合静态数据，而不适合索引频繁更新的列。举个例子，有这样一个字段busy，记录各个机器的繁忙与否，当机器忙碌时，busy为1，当机器不忙碌时，busy为0。
这个时候有人会说使用位图索引，因为busy只有两个值。好，我们使用位图索引索引busy字段！假设用户A使用update更新某个机器的busy值，比如update table set table.busy=1 where rowid=100;，但还没有commit，而用户B也使用update更新另一个机器的busy值，update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了，需要等待用户A commit。
原因：用户A更新了某个机器的busy值为1，会导致所有busy为1的机器的位图向量发生改变，因此数据库会将busy＝1的所有行锁定，只有commit之后才解锁。

1.6 如何指定索引数据类型：

InnoDB引擎：
- Hash索引自动适应，会根据表的使用情况自动为表生成哈希索引，不能人为干预。

2、分库分表sharding

2.1、为啥要分库分表

(1)数据库单张表存放有上限。MySql 2个G或者500W条数据(阿里巴巴的JAVA开发手册)。
数据量达到3KW,B+树过高，I/O次数过多，从而导致SQL查询变慢。
(2)读写会变慢。特别是没命中索引，全表扫描的话，耗时更长了。
(3)连接数问题。MYSQL的连接数是有最大限制的，由参数max_connections决定。这个参数设置没有固定的值，主要看业务场景，需要注意的是每个链接都需要占用内存，以及有昂贵的线程切换开销。
如果数据量过大就需要用路由因子分表。
容量评估：
1、根据已有的业务数据来分析，每天、每周、每月的数据量。然后根据数据量业务增长比例时间就能估算出总体的数据量 sum。然后总体的数据量sum/2-3KW。就能计算出要分的库的大小。一般分库都是2的次方数，比如64 128 256。一般评估十年的时间。

2.2、如何分库分表

分表：一般情况是C端用户数据量大需要分库分表。通常来讲一个用户只能看到自己的订单信息。所以订单表可以用用户ID作为分库分表的分片键。
分库：一个数据库的连接数有上限。如果表太多连接太多会有连接数和带宽的问题。一个库通常会存放32张表或者64张表。
分库分表又分为垂直拆分和水平拆分，垂直拆分是把各个字段分拆到不同的多张表，本质并未解决数据量过大的问题。水平拆分则根据主键等信息将很多条记录，拆分多多张表上，一条记录仅存在一个分区上。
参考链接：https://zhuanlan.zhihu.com/p/137368446
设置路由因子：通常选择一个不变的字段，这个字段要跟具体的数据无关。

X、常见面试题

1、慢SQL优化：

参考：https://www.cnblogs.com/zjxiang/p/9157398.html

1.1原因

定义：执行比较耗时的SQL。超过2秒就是慢查询。
原因：
- 数据量很大，或者走全表扫描。
- 建立了索引，SQL有问题没有走索引。

1.2 如何定位：

（1）设置开启：SET GLOBAL slow_query_log = 1;　　　#默认未开启，开启会影响性能，mysql重启会失效
（2）查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
（3）设置阈值：SET GLOBAL long_query_time=3;
（4）查看阈值：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#重连或新开一个会话才能看到修改值
（5）通过修改配置文件my.cnf永久生效，在[mysqld]下配置：
　　[mysqld]
　　slow_query_log = 1;　　#开启
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

获取慢SQL信息

查看慢查询日志记录数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';

在这里插入图片描述

1.3 分析原因:

1.3.1 explain执行计划

通过explain分析慢SQL。

1.3.2 Show Profile分析

Show Profile也是分析慢SQL的一种手段，但它能获得比explain更详细的信息，能分析当前会话中语句执行的资源消耗情况，能获得这条SQL在整个生命周期的耗时，相当于执行时间的清单，也很重要。
1、默认关闭。开启后，会在后台保存最近15次的运行结果，然后通过Show Profile命令查看结果。

开启：set profiling = on;
查看：SHOW VARIABLES LIKE 'profiling%';

2、通过Show Profile能查看SQL的耗时
在这里插入图片描述

3、通过Query_ID可以得到具体SQL从连接 - 服务 - 引擎 - 存储四层结构完整生命周期的耗时。
在这里插入图片描述

2、Mysql 如何保障数据一致性:

2.1 本地事务数据库断电的这种情况，它是怎么保证数据一致性的呢？即事务的底层原理。

数据库分别是由数据库文件和日志文件两种类型的文件组成的，通常情况下，日志文件要比数据库文件大很多。数据库进行任何写入操作的时候都是要先写日志的。同样的道理，我们在执行事务的时候数据库首先会记录下这个事务的redo操作日志，然后才开始真正操作数据库，在操作之前首先会把日志文件写入磁盘，那么当突然断电的时候，即使操作没有完成，在重新启动数据库时，数据库会根据当前数据的情况进行undo回滚或者是redo前滚，这样就保证了数据的强一致性。Undo Log记录某数据被修改前的值，可以用来在事务失败时进行rollback。Redo Log记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据（用于数据库缓存在内存中内容的持久性）。

2.2 数据库主从集群：

mysql数据库集群怎样保障数据一致性: https://www.yisu.com/ask/31307094.html

主要包括主从复制、Galera集群、事务支持、ACID特性、唯一约束和外键约束、触发器、存储过程等。以下是具体的保障方法：
MySQL主从复制
异步复制：主服务器提交事务后立即返回成功响应，不等待从服务器的确认。这种方式性能较高，但可能导致数据不一致。
同步复制：主服务器在提交事务前必须等待所有从服务器确认接收到并应用了事务。这种方式提供了更高的数据一致性保障。
半同步复制：介于异步和同步之间，主服务器在提交事务前至少等待一个从服务器确认接收到日志。这种方式减少了数据丢失的风险，同时性能开销相对较小。

事务支持和ACID特性
MySQL支持事务，通过事务可以保证数据库操作要么全部执行成功，要么全部失败回滚，从而确保数据的一致性。
遵循ACID特性（原子性、一致性、隔离性、持久性），其中一致性是指事务执行前后数据的状态保持一致。

2.3 Mysql和Redis数据一致性:

用最终一次性来保证性能和一致性。
强一致性：需要加锁，影响性能一般不采用。
延迟双删：先删缓存，后更新，延迟，再删缓存。由于延迟导致并发不高。
比如2个线程同时访问REDIS。A线程先访问，B线程后访问。
1、线程A进来，要修改REDIS。A先删除REDIS缓存，后写MYSQL，MYSQL写完以后，A线程再更新REDIS数据。
2、如果先修改数据库，后修改REDIS。会导致后面进来的线程B读的是老的REDIS数据。
3、A先删除REDIS缓存，后写MYSQL，redisMYSQL还没写完以后，B线程进来了

canal方案：监听mysql bin log 如果修改了数据，通过消息更新缓存。