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

来源：http://t.cn/RnU0h2o

• 1 影响数据库查询速度的四个因素
• 2 风险分析
• 3 网卡流量：如何避免无法连接数据库的情况
• 4 大表带来的问题（重要）
• 5 大事务带来的问题（重要）

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

2 风险分析

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

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

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

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

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

4 大表带来的问题（重要）

4.1 大表的特点

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

4.2 大表的危害

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

2.对DDL影响：

建立索引需要很长时间：

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

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

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

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

难点：

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

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

5.1 什么是事务

5.2 事务的`ACID`属性

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

隔离性等级：

• 未提交读(READ UNCOMMITED) 脏读,两个事务之间互相可见；
• 已提交读(READ COMMITED)符合隔离性的基本概念,一个事务进行时，其它已提交的事物对于该事务是可见的，即可以获取其它事务提交的数据。
• 可重复读(REPEATABLE READ) InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！
• 可串行化（SERIALIZABLE） 在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。

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

4、持久性(DURABILITY)：从数据库的角度的持久性，磁盘损坏就不行了

redo log机制保证事务更新的一致性和持久性

5.3 大事务

运行时间长，操作数据比较多的事务；

风险：锁定数据太多，回滚时间长，执行时间长。

1. 锁定太多数据，造成大量阻塞和锁超时；
2. 回滚时所需时间比较长，且数据仍然会处于锁定；
3. 如果执行时间长，将造成主从延迟，因为只有当主服务器全部执行完写入日志时，从服务器才会开始进行同步，造成延迟。

解决思路：

1. 避免一次处理太多数据，可以分批次处理；
2. 移出不必要的SELECT操作，保证事务中只有必要的写操作。