一次mysql大数据量查询慢查询导致服务阻塞后的学习

缺乏、安全感 2023-10-18 18:27 27阅读 0赞

场景还原：前一个月给朋友写了个简单的登录功能，简单的查询数据库登录逻辑，使用mysbatis-plus进行的dao层代码生成（吐槽一下这个工具，真是方便一时爽，后面维护难，比较喜欢自己能够组装和优化sql，大数据量插入时候mybatis-plus性能极差都是生成的单条插入sql然后flush），没想到啊，哥们的应用流量这么，数据量这么多。。很多问题都是这样，在小数据量，低频访问时候都是正常的，一旦有了流量很多问题就都出现了。用户点击登录按钮后，服务端长时间未响应。听到朋友描述后，我背后一凉，猜到可能是mysql出问题了，用的都是我自己搭建的，单台的mysql

查了一下资源使用情况

看完心凉了。。。mysql没加任何索引，都是全表查询，当时紧急处理，把所有用户登录数据导入redis（百万级的数据量，且密码是服务端生成，用户无法修改），暂时抗住了压力（之后回查了一下当时的流量 Max QPS 870左右）

通过这个应该可以算上事故的问题，下定决心要学习一下mysql的索引创建以及使用场景应用

学习过程：首先先创建一张测试表

CREATE TABLE \`user\` (  
\`id\` int(11) unsigned NOT NULL AUTO\_INCREMENT,  
\`user\_name\` varchar(32) NOT NULL DEFAULT '',  
\`nick\_name\` varchar(32) NOT NULL,  
\`pass\_word\` varchar(32) NOT NULL DEFAULT '',  
\`create\_time\` timestamp NOT NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP,  
PRIMARY KEY (\`id\`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

写一个生成1000万条数据的存储过程：

DELIMITER //  
create procedure test\_proc()  
begin  
declare num int DEFAULT 1;  
while num <= 10000000 do  
insert into user(user\_name,nick\_name,pass\_word) values(num,'保密',PASSWORD(num));  
set num=num+1;  
end while;  
end  
//  
DELIMITER ;  
执行存储过程：(要等几分钟，如果觉得慢可以写一些拼接sql批插入)

call test\_proc();  
测试一下查询时间

select user\_name,pass\_word from user where user\_name = '100000' or pass\_word = '\*8AB26805E964C278E555D5DA0C9F0D8';

MyISAM 查询需要1.53s

换一下InnoDB试一下 2.85s （MyISAM一般作为查询库，InnoDB有事务一般用在写比较的库）

查看一下当前表的索引情况的sql ： show index from user;  
这篇博客先使用一下最常用的普通索引进行一下优化：

alter table user add index index\_user\_name(user\_name);

创建了一个简单索引之后的查询结果如下：

下面介绍一下可能会踩到的索引失效的坑：

1、如果是varchar类型没有加\`\` 符号还是会进行全表扫描

2、sql语句上尽可能不要用like，在索引字段上使用like还是会进行全表扫描

3、使用is null 或 is not null

4、使用函数作为where查询的条件

5、使用不等于操作符（<> ,!=,not in,in）

只是简单的加一下索引在user\_name上就能优化这么明显，那是不是索引就能随便添加了呢？

1、如果是频繁更新的字段建了索引，更新字段的同时需要额外去更新索引

2、索引会占用比较大的磁盘空间去存储

3、唯一性太差的字段也不适合做索引

索引这么快速的提升了查询速度是怎么做到的呢？

索引的本质  
MySQL官方对于索引的定义为：索引是帮助MySQL高效获取数据的数据结构。即可以理解为：索引是数据结构。  
  
我们知道，数据库查询是数据库最主要的功能之一，我们都希望查询数据的速度尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找，当然这种时间复杂度为O(n)的算法在数据量很大时显然是糟糕的，于是有了二分查找、二叉树查找等。但是二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树，但是数据本身的组织结构不可能完全满足各种数据结构。所以，在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。  
  
B-Tree和B+Tree  
目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。  
  
索引  
索引的目的：提高查询效率  
原理：通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。  
数据结构：B+树  
图解B+树与查找过程：

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。  
  
b+树的查找过程  
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。  
  
b+树性质  
通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表

周天的一个小小学习过程分享出来，比较初级，如有错误还请大神们指教  
\---------------------  
作者：Mr\_yaodefine  
来源：CSDN  
原文：https://blog.csdn.net/name\_javahadoop/article/details/80469800  
版权声明：本文为博主原创文章，转载请附上博文链接！