当前位置：首页 > news >正文

【优】B+树，Mysql优化慢查询执行计划优化表结构避免死锁大量插入数据大数据后果

news 2026/5/25 19:21:10

1.为什MySQL选择B+树作为索引?

索引(Index)是帮助MySQL高效获取数据的数据结构

其实MySQL的InnoDB存储引擎支持B+树索引还有哈希索引。而针对关系型的数据，

哈希：范围查找排序组合索引的部分 hash冲突

如果选用哈希索引会有以下的问题:

1、哈希索引只能匹配是否相等，不能实现范围查找;

2、当需要按照索引进行order by时，哈希索引没办法支持排序;

3、B+树索引支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和b也可以查询的，如果使用哈希索引，没办法支持部分索引;

hash 是 abc 加一起做个 hash

4、当数据量很大时，哈希索引的hash冲突的概率也会非常大。

B+树平衡多叉树

不是二叉，是平衡多叉树

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值(Key Value)快速找到数据。注意B+树中的B不是代表二叉(binary)，而是代表平衡(balance)，因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

在讲二叉树之前，我们必须了解一下二分查找:二分查找法(binary search)也称为折半查找法，用来查找一组有序的记录数组中的某一记录。在以下数组中找到数字48对应的下标

数值5 10 19 21 31 37 48 50 52 42

通过3次二分查找就找到了我们所要的数字，而顺序查找需8次。对于上面10个数来说，顺序查找平均查找次数为(1+2+3+4+5+6+7+8+9+10)/10=5.5次。而二分查找法为(4+3+2+4+3+1+4+3+2+3)/10=2.9次。在最坏的情况下，顺序查找的次数为10，而二分查找的次数为4。所以为了索引查找的高效性，我们引入了B+树来查找。

借助网页工具:Data Structure Visualization(usfca.edu) 我们把之前的数据插入，形成以下的B+树。

上图中，最下面的每个叶子页(LeafPage)存储了实际的数据，叶子页就可以存放多条数据，叶子节点由小到大(有序)串联在一起(通过链表:相邻的叶子节点之间用指针相连)，叶子页中的数据也是排好序的;
而通过以上图中可以看到，通过B+树去查找一个数据，二分查找的次数最多为3次。
同时B+树叶非常方便的支持顺序查询:
比 select *from table where id >=21 and id <=48
那么只需要根据21和48定位到对应的叶子节点数据，然后从叶子节点的21到48顺序的读取出来即可，这样就非常快速的完成了范围查询。另外出来的数据也已经排好了顺序。

2.MySQL的优化可以从哪些方面考虑?

1.索引优化：频次高避免创造多

索引是加速数据库查询的关键。在设计表结构时，应该根据查询的需求添加合适的索引。常用的索引包括主键、唯一索引、普通索引、全文索引等。
同时，要避免过多的索引，因为每个索引都需要占用存储空间，会影响写入性能。

查询频次高的，
避免创建更多的索引

2.查询优化避免使用子查询多余的字段 %

优化查询语句是提高MySQL性能的重要手段。要尽可能使用索引，避免全表扫描。同时，要避免使用子查询，尽可能使用连接查询;避免在查询中使用"%”通配符;避免多余的字段等等。

因为子查询的优化空间不大

explain

v. 解释，说明；说明（……的）原因，解释（……的）理由

是否使用到了索引全表扫描
表之间的关联数据量

3.数据库表结构优化：大字段冗余字段

合理的表结构可以提高查询效率和减少存储空间，应该避免使用大字段，如TEXT、BLOB等，因为这些字段会占用大量的存储空间。同时，应该避免冗余字段，避免更新和维护时的复杂性。

可以单独的抽出一张大字段的表
冗余字段要使用频次高的

4.缓存优化 Redis InfluxDB

使用缓存可以大大减轻MySQL数据库的压力，提高查询效率。常用的缓存技术包括Memcached和Redis等。

5.分区优化：分表分库

对于数据量较大的表，可以使用分区技术将表分成多个部分。这样可以提高查询效率，同时降低了单个表的存储空间和索引大小。

6.配置：缓冲区、连接数、线程数、查询缓存

MySQL的参数配置会影响MySQL的性能。需要根据实际情况进行调整，包括缓冲区、连接数、线程数、查询缓存等等。

7.硬件优化

硬件设备也会影响MySQL的性能。要选择更快速的硬件设备，如更快的磁盘、更快的CPU和更多的内存等等。同时，要根据实际情况来决定使用RAID、SSD等技术。

成本越来越高：从下到上

硬件和OS调优
MySQL调优
架构调优

效果越来越好：从上到下

很明显从图上可以看出，越往上走，难度越来越高，收益却是越来越小的。
对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做;然后考虑写的并发量有多大，是否需要采用分布式;最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。所以在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，即使是DBA能做的事情其实是也是比较有限的。

对于MYSQL调份，需要确认业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等

比如硬件和OS调优，需要对硬件和OS有着非常深刻的了解，仅仅就磁盘一项来说，一般非DBA能想到的调整就是SSD盘比用机械硬盘更好。DBA级别考虑的至少包括了，使用什么样的磁盘阵列(RAID)级别、是否可以分散磁盘IO、是否使用裸设备存放数据，使用哪种文件系统(目前比较推荐的是XFS)，操作系统的磁盘调度算法选择，是否需要调整操作系统文件管理面比如atime属性等等。

所以我们重点关注MySQL方面的调优，特别是索引。SQL/索引调优要求对业务和数据流非常清楚。在阿里巴巴内部，有三分之二的DBA是业务DBA，从业务需求讨论到表结构审核、SQL语句审核、上线、索引更新、版本迭代升级，甚至哪些数据应该放到非关系型数据库中，哪些数据放到数据仓库、搜索引擎或者缓存中，都需要这些DBA跟踪和复审。他们甚至可以称为数据架构师(Data Architecher)。

3.什么是慢查询，如何避免?

slow_query_log x_file long_query_time

慢查询日志，顾名思义，就是查询花费大量时间的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的sQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。如何开启，我们稍后再说。
慢查询是指执行时间较长的查询操作。为避免慢查询，可以采取以下方法:

使用合适的索引:根据具体查询需求创建适当的索引，以提高查询速度。
优化查询语句:通过分析查询语句，找出潜在的性能问题并进行优化。
避免全表扫描:尽可能地利用索引来进行查询，避免对整个表进行扫描。
配置合理的缓存:通过合理配置数据库的缓存参数来提高查询性能。

show variables like 'slow_query_log' -- 默认是慢查询日志是off set global slow_query_log=1; -- 打开 -- 慢查询的阈值，默认是10,10秒钟 show variables like '%long_query_time% --这个参数就 long_query_time set global long_query_time=0; -- 任何一个sql 都进，需要重启mysql explain select * from s1 where order no like'%a show VARIABLES like '%slow_query_log_file% --在 \mysql-5.7.40\data\DESKTOP-8DEFEQ2-slow.log

4.什么是执行计划?如何理解?

通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈
执行计划的语法
执行计划的语法其实非常简单:在SQL查询的前面加上EXPLAIN关键字就行。比如:EXPLAIN select * from table

id select_type 如：SIMPLE 简单查询 table partitions 这次查询扫了哪些分区；普通表是 NULL；分区表只扫少数分区才是优化到位。 type 如果是all，就是全表扫描。 system reference range index possible_keys 可能使用到的一些索引 key 实际使用到 key_len 索引长度 ref 等值匹配，与之之匹配的对象信息 rows 预估读取的记录行数 filtered 通过搜索条件，过滤完记录的百分比 Extra 如：Using Where

如下第二个表用了主键索引

explainselect*froms1whereorder_no='a'type为 ref possible_keys idx_order_nokeyidx_order_nolike'%a';这样是全表扫描。最左匹配原则。放后面会走索引

5. 如何优化MySQL的表结构?

列小选择性前缀索引搜索分组多列索引

5.如何优化MySQL的表结构?
1、索引列的类型尽量小

使用业务，越小越好

2、索引的选择性

优先选择高基数，离散度高的

3、前缀索引

-- 格式：ALTER TABLE 表名 ADD INDEX 索引名 (字段名(N));ALTERTABLEt_userADDINDEXidx_name(name(10));-- 格式：CREATE TABLE 表名 (字段定义, INDEX 索引名 (字段名(N)));CREATETABLEt_goods(idINTPRIMARYKEY,goods_nameVARCHAR(100),INDEXidx_goods_name(goods_name(15))-- 对goods_name前15字符建前缀索引);

4、只为用于搜索、排序或分组的列创建索引

order by insert_time,order_status,expire_time; //索引 也按照这个顺序

5、多列索引的优化

最左匹配原则—B+树的结构–联合索引。

6、MySQL如何避免死锁?

加锁顺序使用悲观锁超时时间

死锁的本质是：多个事务互相持有对方需要的锁，且都不释放，形成循环等待。

死锁是指两个或多个事务互相等待资源释放，从而导致无法继续进行的情况。为避免死锁，可以采取以下措施:·

给事务加锁顺序:让所有事务按照相同的顺序获取锁，可以避免死锁的发生。

使用悲观锁:即在事务开始时直接加锁，确保事务执行的完整性，但会对性能产生影响。

加全局锁或乐观锁版本号比较，cas比较和交换，redis 或 zookeeper分布式锁

设置合理的超时时间:对于被锁定的资源，设置合理的超时时间，避免长时间等待导致死锁。

MySQL中的死锁的案例
会话1:

会话1 拥有资源1
会话2 拥有资源3
会话1 想要有用资源3，此时会卡死，资源3还没有放开
1. MySQL检测到了死锁，并结束了会话2中事务的执行，对事物2进行回滚
回话2 想要资源1，也是卡死

show engine innodb status\G #展示死锁信息 \G 是为了让结果按行垂直展示（替代默认的表格展示），更易读

查看事务加锁的情况，不过一般情况下，看不到哪个事务对哪些记录加了那些锁，需要修改系统变量
innodb_status_output_locks (MySQL5.6.16 入), 缺省OFF.

show variables like 'innodb_status_output_locks'; set global 'innodb_status_output_locks' = ON;

死锁的例子和悲观锁

而悲观锁的思路是：让所有事务按 “相同的顺序” 一次性把需要的锁都加上，从根源上打破 “循环等待” 的条件，自然就不会产生死锁。

无悲观锁 → 必现死锁

-- 事务1（终端1） START TRANSACTION; UPDATE t_user SET balance = balance - 100 WHERE id = 1; -- 持有id=1的锁 -- 此时事务2执行下面的语句，持有id=2的锁 UPDATE t_user SET balance = balance + 100 WHERE id = 2; -- 等待id=2的锁（被事务2持有） -- 事务2（终端2） START TRANSACTION; UPDATE t_user SET balance = balance + 100 WHERE id = 2; -- 持有id=2的锁 UPDATE t_user SET balance = balance - 100 WHERE id = 1; -- 等待id=1的锁（被事务1持有） -- 此时两个事务互相等对方的锁 → 死锁

用悲观锁（提前加锁）→ 避免死锁

IN (1,2) FOR UPDATE

-- 事务1（终端1）：先一次性锁住需要的所有行，按id从小到大的顺序 START TRANSACTION; -- 第一步：显式加锁（悲观锁核心），先锁id=1，再锁id=2（固定顺序） SELECT * FROM t_user WHERE id IN (1,2) FOR UPDATE; -- 第二步：执行更新（此时锁已持有，不会等别人） UPDATE t_user SET balance = balance - 100 WHERE id = 1; UPDATE t_user SET balance = balance + 100 WHERE id = 2; COMMIT; -- 释放锁 -- 事务2（终端2）：同样按id从小到大的顺序加锁 START TRANSACTION; -- 此时事务1还没提交，事务2会等待锁（但不会形成循环，只是排队） SELECT * FROM t_user WHERE id IN (1,2) FOR UPDATE; -- 等事务1释放锁后，事务2拿到锁，再执行更新 UPDATE t_user SET balance = balance + 100 WHERE id = 2; UPDATE t_user SET balance = balance - 100 WHERE id = 1; COMMIT;