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. 分表主键的选择

2. 分表后跨分区数据的查询和统计

1.5 大事务带来的问题(重要)

1.5.1 什么是事务

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机制保证事务更新的同等性和长期性

1.5.3 大事务

运转时辰长，把持数据斗劲多的事务;

风险：锁定命据太多，回滚时辰长，实行时辰长。

• 锁定太多数据，形成大量梗阻和锁超时;

• 回滚时所需时辰斗劲长，且数据仍然会处于锁定;

• 若是实行时辰长，将形成主从耽误，由于只需当主理事器全数实行完写入日志时，从办事器才会起头停止同步，形成耽误。

处理思绪：

• 按捺一次措置太多数据，可以分批次措置;

• 移出不必要的SELECT把持，保证事务中只需必要的写把持。

二、什么影响了MySQL机能(很是重要)

2.1 影响机能的几个方面

• 办事器硬件。

• 办事器体系(体系参数优化)。

• 存储引擎。

MyISAM： 不支撑事务，表级锁。

InnoDB: 支撑事务，支撑行级锁，事务ACID。

• 数据库参数设置装备安排。

• 数据库构造设计和SQL语句。(重点优化)

2.2 MySQL体系构造

分三层：客户端->办事层->存储引擎

1. MySQL是插件式的存储引擎，其中存储引擎分良多种。只需实现适宜mysql存储引擎的接口，可以开发本身的存储引擎!

2. 所有跨存储引擎的功能都是在办事层实现的。

3. MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不合的存储引擎。可是不建议如许做。

2.3 InnoDB存储引擎

MySQL5.5及之后版本默认的存储引擎：InnoDB。

2.3.1 InnoDB使用表空间停止数据存储。

show variables like 'innodb_file_per_table

若是innodb_file_per_table 为 ON 将建立独立的表空间，文件为tablename.ibd;

若是innodb_file_per_table 为 OFF 将数据存储到体系的共享表空间，文件为ibdataX(X为从1起头的整数);

.frm ：是办事器层面产生的文件，近似办事器层的数据字典，记实表构造。

2.3.2 (MySQL5.5默认)体系表空间与(MySQL5.6及往后默认)独立表空间

1.1 体系表空间无法简单的缩漫笔件巨细，形成空间华侈，并会产生大量的磁盘碎片。

1.2 独立表空间可以经由过程optimeze table 缩短体系文件，不必要重启办事器也不会影响对表的正常访谒。

2.1 若是对多个表停止刷新时，实际上是挨次停止的，会产生IO瓶颈。

2.2 独立表空间可以同时向多个文件刷新数据。

强烈建立对Innodb 使用独立表空间，优化什么的更便当，可控。

2.3.3 体系表空间的表转移到独立表空间中的编制

1、使用mysqldump 导出所稀有据库数据(存储过程、触发器、方案使命一起都要导出 )可以在从办事器上把持。

2、停止MYsql 办事器，改削参数(my.cnf参加innodb_file_per_table)，并删除Inoodb相干文件(可以重修Data目录)。

3、重启MYSQL，并重修Innodb体系表空间。

4、 重新导入数据。

或者 Alter table 同样可以的转移，可是无法收受接收体系表空间中占用的空间。

2.4 InnoDB存储引擎的特征

2.4.1 特征一：事务性存储引擎及两个不凡日志类型：Redo Log 和 Undo Log

1. Innodb 是一种事务性存储引擎。

2. 完全支撑事务的ACID特征。

3. 支撑事务所必要的两个不凡日志类型：Redo Log 和Undo Log

Redo Log：实现事务的长期性(已提交的事务)。

Undo Log：未提交的事务，独立于表空间，必要随机访谒，可以存储在高机能io设备上。

Undo日志记实某数据被改削前的值，可以用来在事务失败时停止rollback;Redo日志记实某数据块被改削后的值，可以用来恢复未写入data file的已成功事务更新的数据。

2.4.2 特征二：支撑行级锁

1. InnoDB支撑行级锁。

2. 行级锁可以最洪流平地支撑并发。

3. 行级锁是由存储引擎层实现的。

• 上一篇: 蓝盟IT外包，元宇宙：四梁八柱是什么
  下一篇: 2019年学MySQL，最佳的10本新书

• 分享到：