MySQL千万级数据的表如何优化

喜欢ヅ旅行 2024-02-05 12:29 33阅读 0赞

#### MySQL千万级数据的表如何优化 ####

*  数据量大导致的问题
 *  实战调优
 *   *  1. 两个需求的出现
     *  2. 尝试索引优化无效
     *  3. 单独建表存储每张表的count值
     *  4. 存在条件查询时使用索引提速
     *  5. 分页查询优化
     *  6. 补充
 *  总结

## 数据量大导致的问题 ##

许多人提到的阿里规定500W数据就要分库分表。实际上，这个500w并不是定义死的，而是与MySQL的配置以及机器的硬件有关。MySQL为了提升性能，会将表的索引装载到内存中。但是当表的数据到达一定的量的时候，会导致内存无法存储这些索引。无法存储索引，就只能进行磁盘IO，从而导致性能下降。

## 实战调优 ##

一张表，数据有1000w,目前只有一个主键索引  
![在这里插入图片描述][ffda81236f7b4c72ad885ad4268ea2a2.png]  
查询所有大概16s，如下图所示：  
![在这里插入图片描述][d3aee01fe1e1461e9a760be45882d8d5.png]  
通常我们一个后台系统，比如这个是一个电商平台，这个是用户表。后台管理系统，一般会查询这些用户信息，做一些操作，比如后台直接新增用户，或者删除用户这些操作。

### 1. 两个需求的出现 ###

所以这里就诞生了两个需求，**一个是查询count,一个是分页查询**。我们分别来测试一下count用的时间和分页查询所用的时间。  
![在这里插入图片描述][6017e3956eed477a93ccfe8809be9ea5.png]  
从上面查询所用时间可以看出来，如果是分页查询的话，查询的数据越往后用时是越长的，查询count也需要  
1.7s。这显然是不符合我们的要求的。所以，这里我们就需要优化。

### 2. 尝试索引优化无效 ###

我们这里进行索引优化试试。

首先看一下这是**只有主键索引**的执行计划。  
![在这里插入图片描述][510b82bb3b7e4e92804df20901012c4f.png]  
通过图可以看见**执行计划修改**了，虽然type是从all->index,走了sindex索引，但是实际上查询速度没有发生改变。

其实，**创建联合索引**，是为了有条件查询的时候速度更快，而不是全表查询。  
![在这里插入图片描述][047842675baa430d8d4bf6aa845ca246.png]  
所以这就是有联合索引和无索引的差距。

这里基本上可以证明，**加了索引和不加索引**，进行**全表查询**的时候，效率就是会**很慢**。

### 3. 单独建表存储每张表的count值 ###

既然索引这个结果已经不好使了，那就只能找其他方案了。我们可以单独**把count存储到一个表里面**。  
![在这里插入图片描述][c9ad8ef3921943009138879dd1a96509.png]  
这里说一下，这种表一般不会查所有，只会查询一条，所以建表的时候，可以**建成hash**。  
![在这里插入图片描述][049db80440044bbfa7762bd941a11bf3.png]  
count默认点进去了，没有选择查询条件，那就直接查询这个count。这里其实就优化了1.7s。

### 4. 存在条件查询时使用索引提速 ###

count就进行优化完了。如果上面**有选择条件**的话，就可以**建立索引**，通过走索引筛选的形式来查询，这样就可以不用读这个count了。

### 5. 分页查询优化 ###

那么，count是没问题了，分页查询优化要如何优化呢？这里可以**使用子查询**来优化。  
![在这里插入图片描述][7c12455c70bb429493fc863353a1b2b3.png]  
其实子查询这种写法，判断id,其实就是通过覆盖索引来查询，效率会大大增加。

### 6. 补充 ###

但是如果说数据量太大了，还是**建议走es或者进行一些默认选择**，count可以单独列出来。

## 总结 ##

1.  **优化查询count**：表数据大时，查询表count会很慢。所以另建一个表来存两个数据，即这个表的名称和所对应的count数。
2.  **分页查询优化**：给表数据创建索引，因为在有索引的情况下条件查询会比较快，然后进行子查询。

**参考资料**：[MySQL千万级数据的表如何优化][MySQL]

[ffda81236f7b4c72ad885ad4268ea2a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/621428c6bfda48938ba029e17e4994ba.png
[d3aee01fe1e1461e9a760be45882d8d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/ad7cf6cabce94ab3ae5114c8e3081187.png
[6017e3956eed477a93ccfe8809be9ea5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/83f97f7cf912425e9cb3464fc3b8b1b1.png
[510b82bb3b7e4e92804df20901012c4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/c453b95e8a4f49ca94c69df9550d2c9f.png
[047842675baa430d8d4bf6aa845ca246.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/acb61a04a5da4fc2916ddd731501f8b2.png
[c9ad8ef3921943009138879dd1a96509.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/89c339b1350045c0b5cbf223132133fa.png
[049db80440044bbfa7762bd941a11bf3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/0625db67feae4e1a89b6d3d2d4899530.png
[7c12455c70bb429493fc863353a1b2b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/baf4d9908866411ebf07f33ee0458c1f.png
[MySQL]: https://www.bilibili.com/video/BV1dv411E7oz/?spm_id_from=333.337.search-card.all.click&vd_source=a1af903e57688df870625021f7f3a7eb