学习笔记：会是什么影响了数据库查询速度

最新推荐文章于 2023-09-05 23:05:18 发布

qq_21867467

最新推荐文章于 2023-09-05 23:05:18 发布

阅读量277

点赞数

文章标签：数据库 mysql java 分布式索引

本文链接：https://blog.csdn.net/qq_21867467/article/details/108901138

版权

本文探讨了影响数据库查询速度的四大因素：高网络流量可能导致连接问题，大表和大事务的风险及其处理方法，包括磁盘性能、并发连接数、表锁和事务管理。还分析了MySQL中InnoDB存储引擎的特性，强调了独立表空间的优势和迁移方法，旨在提升数据库性能和管理效率。

摘要由CSDN通过智能技术生成

一、什么影响了数据库查询速度

1.1 影响数据库查询速度的四个因素

1.2 风险分析

QPS： Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
TPS：是 TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。

Tips：最好不要在主库上数据库备份，大型活动前取消这样的计划。

效率低下的sql：超高的QPS与TPS。
大量的并发：数据连接数被占满（max_connection默认100，一般把连接数设置得大一些）。
并发量:同一时刻数据库服务器处理的请求数量
超高的CPU使用率：CPU资源耗尽出现宕机。
磁盘IO：磁盘IO性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。

1.3 网卡流量：如何避免无法连接数据库的情况

减少从服务器的数量（从服务器会从主服务器复制日志）
进行分级缓存（避免前端大量缓存失效）
避免使用select * 进行查询
分离业务网络和服务器网络

1.4 大表带来的问题（`重要`）

1.4.1 大表的特点

记录行数巨大，单表超千万
表数据文件巨大，超过10个G

1.4.2 大表的危害

1.慢查询：很难在短时间内过滤出需要的数据
查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘io -> 降低磁盘效率

2.对DDL影响：

建立索引需要很长时间：

MySQL -v<5.5 建立索引会锁表
MySQL -v>=5.5 建立索引会造成主从延迟（mysql建立索引，先在组上执行，再在库上执行）

修改表结构需要长时间的锁表：会造成长时间的主从延迟('480秒延迟')

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表

难点：

分表主键的选择
分表后跨分区数据的查询和统计

1.5 大事务带来的问题（`重要`）

1.5.1 什么是事务

1.5.2事务的`ACID`属性

1、原子性（ atomicity)：全部成功，全部回滚失败。银行存取款。
2、一致性（consistent)：银行转账的总金额不变。
3、隔离性（isolation)：

隔离性等级：

未提交读(READ UNCOMMITED) 脏读,两个事务之间互相可见；-- 存在脏读、不可重复读、幻读的问题
已提交读(READ COMMITED)符合隔离性的基本概念,一个事务进行时，其它已提交的事物对于该事务是可见的，即可以获取其它事务提交的数据（不可重复读）。-- 解决脏读的问题，存在不可重复读、幻读的问题。
可重复读(REPEATABLE READ) InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！ -- mysql 默认级别，解决脏读、不可重复读的问题，存在幻读的问题。使用 MVCC（多版本并发控制，提高并发，不加锁）机制实现可重复读。But，MySQL在可重复读级别已经解决幻读，插不进数据，有间隙锁。
可串行化（SERIALIZABLE）在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。 -- 解决脏读、不可重复读、幻读，可保证事务安全，但完全串行执行，性能最低。

不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

也就是说，当前事务读第一次取到的数据比后来读取到数据条目少。

不可重复读和幻读比较：
两者有些相似，但是前者针对的是update或delete，后者针对的insert。

查看系统的事务隔离级别：show variables like '%iso%';
开启一个新事务：begin;
提交一个事务：commit;
修改事物的隔离级别：set session tx_isolation='read-committed';