笔记12 性能与SQL优化
1.性能优化
复习一下什么是性能:
- 吞吐与延迟 : 有些结论是反直觉的,指导我们关注什么
- 没有量化就没有改进:监控与度量指标,指导我们怎么去入手
- 80/20原则:先优化性能瓶颈问题,指导我们如何去优化
- 过早的优化是万恶之源:指导我们要选择优化的时机
- 脱离场景谈性能都是耍流氓:指导我们对性能要求要符合实际
所以说,性能是一个综合性问题
DB/SQL 优化是业务系统性能优化的核心
- 例如传统软件改成 SaaS 软件
- 业务系统的分类:计算密集型、数据密集型
- 业务处理本身无状态,数据状态最终要保存到数据库
- 一般来说,DB/SQL 操作的消耗在一次处理中占比最大
- 业务系统发展的不同阶段和时期,性能瓶颈要点不同,类似木桶装水
2.关系数据库 MySQL
什么是关系数据库
- 1970年 Codd 提出关系模型,以关系代数理论为数学基础
《A Relational Model of Data for Large Shared Data Banks》
数据库设计范式
- 第一范式(1NF):关系 R 属于第一范式,当且仅当R中的每一个属性A的值域只包含原子项
- 第二范式(2NF):在满足 1NF 的基础上,消除非主属性对码的部分函数依赖
- 第三范式(3NF):在满足 2NF 的基础上,消除非主属性对码的传递函数依赖
- BC 范式(BCNF):在满足 3NF 的基础上,消除主属性对码的部分和传递函数依赖
- 第四范式(4NF):消除非平凡的多值依赖
- 第五范式(5NF):消除一些不合适的连接依赖
解释一下:
- 1NF:消除重复数据,即每一列都是不可再分的基本数据项;
每个列都是原子的。 - 2NF:消除部分依赖,表中没有列只与主键的部分相关,即每一行都被主键唯一标识;每个列都有主键。
- 3NF:消除传递依赖,消除表中列不依赖主键,而是依赖表中的非主键列的情况,即没有列是与主键不相关的。
从表只引用主表的主键,即表中每列都和主键相关。 - BCNF:Boyce-Codd Normal Form(巴斯-科德范式)
3NF 的基础上消除主属性对于码的部分与传递函数依赖。
常见关系数据库
开源:MySQL、PostgreSQL
商业:Oracle,DB2,SQL Server
内存数据库:Redis?,VoltDB
图数据库:Neo4j,Nebula
时序数据库:InfluxDB、openTSDB
其他关系数据库:Access、Sqlite、H2、Derby、Sybase、Infomix 等
NoSQL 数据库:MongoDB、Hbase、Cassandra、CouchDB
NewSQL/ 分布式数据库:TiDB、CockroachDB、NuoDB、OpenGauss、OB、TDSQL
SQL 语言
概念
SQL 语言1974年由 Boyce 和 Chamberlin 提出,并首先在 IBM 公司研制的关系数据库
系统 SystemR 上实现。
1979年 ORACLE 公司首先提供商用的 SQL,IBM 公司在 DB2和SQL/DS 数据库系统中
也实现了 SQL。
1986年10月,美国 ANSI 采用 SQL 作为关系数据库管理系统的标准语言(ANSI X3.
135-1986),后为国际标准化组织(ISO)采纳为国际标准。
1989年,美国 ANSI 采纳在 ANSI X3.135-1989 报告中定义的关系数据库管理系统的
SQL 标准语言,称为 ANSI SQL 89,该标准替代 ANSI X3.135-1986版本。
结构化查询语言包含 6 个部分:
1、数据查询语言(DQL: Data Query Language):其语句,也称为“数据检索语句”,用以从表中获得数据,确定数据怎样在应用程序给出。保留字 SELECT 是 DQL(也是所有 SQL)用得最多的动词,其他 DQL 常用的保留字有 WHERE,ORDER BY,GROUP BY 和 HAVING。这些 DQL 保留字常与其它类型的 SQL 语句一起使用。
2、数据操作语言(DML:Data Manipulation Language):其语句包括动词 INSERT、UPDATE 和 DELETE。它们分别用于添加、修改和删除。
3、事务控制语言(TCL):它的语句能确保被 DML 语句影响的表的所有行及时得以更新。包括COMMIT(提交)命令、SAVEPOINT(保存点)命令、ROLLBACK(回滚)命令。
4、数据控制语言(DCL):它的语句通过 GRANT 或 REVOKE 实现权限控制,确定单个用户和用户组对数据库对象的访问。某些 RDBMS 可用 GRANT 或 REVOKE 控制对表单个列的访问。
5、数据定义语言(DDL):其语句包括动词 CREATE,ALTER 和 DROP。在数据库中创建新表或修改、删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。
6、指针控制语言(CCL):它的语句,像 DECLARE CURSOR,FETCH INTO 和 UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作。
MySQL的版本
- 4.0支持InnoDB,事务
- 2003年,5.0 -5.6 ==> 历史使用多的版本
- 5.7 ==> 近期使用多的版本
- 8.0 ==>新和功能完善的版本
版本分析:
5.6/5.7的差异
5.7支持:
-多主
-MGR高可用
-分区表
-json
-性能
-修复XA等
-5.7/8.0的差异
-通用表达式
-窗口函数
-持久化参数
-自增列持久化
-默认编码utf8mb4
-DDL原子性
-JSON增强
-不再对group by进行隐式排序??==> 坑。类似jdk8 hashmap
3.数据库原理
1架构
2MySQL存储
独占模式
1)、日志组文件:ib_logfile0和ib_logfile1,默认均为5M
2)、表结构文件:.frm
3)、独占表空间文件:.ibd
4)、字符集和排序规则文件:db.opt
5)、binlog二进制日志文件:记录主数据库服务器的DDL和DML操作
6)、二进制日志索引文件:master-bin.index 共享模式innodb_file_per_table=1
1)、数据都在ibdata1
MySQL执行流程
执行流程
简单流程图
MySQL执行引擎和状态
MySQL对sql执行顺序
实际上这个过程也并不是绝对这样的, 中间mysql 会有部分的优化以达到佳 的优化效果,比如在select 筛选出找到 的数据集
mysql索引原理
数据是按页来分块的,当一个数据被用 到时,其附近的数据也通常会马上被使 用。
InnoDB使用B+树实现聚集索引
mysql操作
-安装的几种方式,安装文件或命令,docker
-操作工具,mysql-cli或IDE(DataGrip,MySQL-WorkBench,MySQL-Front,Navicat等)0
-MySQL库结构,操作语句与命令
-MySQL SQL语法演示
4.MySQL配置优化
1)连接请求的变量
1、max_connections
2、back_log
3、wait_timeout和interative_timeout
查看参数配置 -show variables like xxx
my.cnf 文件
*2)缓冲区变量 *
4、key_buffer_size
5、query_cache_size(查询缓存简称QC)
6、max_connect_errors:
7、sort_buffer_size:
8、max_allowed_packet=32M
9、join_buffer_size=2M
10、thread_cache_size=300
3)配置Innodb的几个变量
11、innodb_buffer_pool_size
12、innodb_flush_log_at_trx_commit
13、innodb_thread_concurrency=0
14、innodb_log_buffer_size
15、innodb_log_file_size=50M
16、innodb_log_files_in_group=3
17、read_buffer_size=1M
18、read_rnd_buffer_size=16M
19、bulk_insert_buffer_size=64M
20、binary log
5数据库设计优化
-如何恰当选择引擎?
-库表如何命名?
-如何合理拆分宽表?
-如何选择恰当数据类型:明确、尽量小
-char、varchar的选择
-(text/blob/clob)的使用问题?
-文件、图片是否要存入到数据库?
-时间日期的存储问题?
-数值的精度问题?
-是否使用外键、触发器?
-唯一约束和索引的关系?
-是否可以冗余字段?
-是否使用游标、变量、视图、自定义函数、存储过程?
-自增主键的使用问题? -能够在线修改表结构(DDL操作)?
-逻辑删除还是物理删除?
-要不要加create_time,update_time时间戳?
-数据库碎片问题?
-如何快速导入导出、备份数据?
性能与 SQL 优化(2)
select now()和select sysdate()的区别
1.MySQL 事务与锁*
MySQL 事务
事务可靠性模型 ACID:
•Atomicity: 原子性, 一次事务中的操作要么全部成功, 要么全部失败。
•Consistency*: 一致性, 跨表、跨行、跨事务, 数据库始终保持一致状态。
•Isolation: 隔离性, 可见性, 保护事务不会互相干扰, 包含4种隔离级别。
//关联着系统并发性能,大规模交易系统的并发瓶颈大多卡在数据库上。
//性能vs可靠性
•Durability:, 持久性, 事务提交成功后,不会丢数据。如电源故障, 系统崩溃。
为了保证以上,InnoDB:
双写缓冲区、故障恢复、操作系统、fsync() 、磁盘存储、缓存、UPS、网络、备份策略 ……
表级锁
•意向锁: 表明事务稍后要进行哪种类型的锁定
•共享意向锁(IS): 打算在某些行上设置共享锁
•排他意向锁(IX): 打算对某些行设置排他锁
•Insert 意向锁: Insert 操作设置的间隙锁
其他
•自增锁(AUTO-IN)
•LOCK TABLES/DDL //DDL:创建表和修改表结构,如加一个列,创建一个索引。
1.X锁和其他锁都冲突。2.意向锁之间都兼容。3.共享锁和待共享的都兼容。4.s锁和意向排他锁不兼容
行级锁(InnoDB)
•记录锁(Record): 始终锁定索引记录,注意隐藏的聚簇索引;
•间隙锁(Gap): 锁住一个范围
•临键锁(Next-Key): 记录锁+间隙锁的组合; 可“锁定”表中不存在记录
•谓词锁(Predicat): 空间索引
死锁:
-阻塞与互相等待
-增删改、锁定读
-死锁检测与自动回滚
-锁粒度与程序设计
隔离
《SQL:1992标准》规定了四种事务隔离级别(Isolation):
• 读未提交: READ UNCOMMITTED //脏读
• 读已提交: READ COMMITTED //RC
• 可重复读: REPEATABLE READ //RR
• 可串行化: SERIALIZABLE
事务隔离是数据库的基础特征。
隔离级别 vs(并发性 可靠性 一致性 可重复性)
MySQL:
• 可以设置全局的默认隔离级别
• 可以单独设置会话的隔离级别
• InnoDB 实现与标准之间的差异
a.读未提交: READ UNCOMMITTED
•很少使用
•不能保证一致性
•脏读(dirty read) : 使用到从未被确认的数
据(例如: 早期版本、回滚)
锁:
•以非锁定方式执行
•可能的问题: 脏读、幻读、不可重复读
b.读已提交: READ COMMITTED
•每次查询都会设置和读取自己的新快照。
•仅支持基于行的 bin-log
•UPDATE 优化: 半一致读(semi-consistent read)
•不可重复读: 不加锁的情况下, 其他事务 UPDATE 或 DELETE 会对查询结果有影响
•幻读(Phantom): 加锁后, 不锁定间隙, 其他事务可以 INSERT。
锁:
•锁定索引记录, 而不锁定记录之间的间隙
•可能的问题: 幻读、不可重复读
c.可重复读: REPEATABLE READ(mysql默认级别)
•InnoDB 的默认隔离级别
•使用事务第一次读取时创建的快照
•多版本技术 //MVCC(多版本并发控制技术,创建一个快照,一个版本的数据固定了,只能看到当前的版本之前的,可重复读,详细见下)
锁:
•使用唯一索引的唯一查询条件时, 只锁定查找到的索引记录, 不锁定间隙。
•其他查询条件, 会锁定扫描到的索引范围, 通过间隙锁或临键锁来阻止其他会话在这个
范围中插入值。
•可能的问题: InnoDB 不能保证没有幻读, 需要加锁
d.串行化: SERIALIZABLE
最严格的级别,事务串行执行,资源消耗最大;
问题回顾:
•脏读(dirty read) : 使用到从未被确认的数据(例如: 早期版本、回滚)
•不可重复读: 不加锁的情况下, 其他事务 update 或 delete 会对结果集有影响
•幻读(Phantom): 加锁之后, 相同的查询语句, 在不同的时间点执行时, 产生不同的结果集
怎么解决?
提高隔离级别、使用间隙锁或临键锁
mysql日志
undo log: 撤消日志
•保证事务的原子性
•用处: 事务回滚, 一致性读、崩溃恢复。
•记录事务回滚时所需的撤消操作
•一条 INSERT 语句,对应一条 DELETE 的 undo log
•每个 UPDATE 语句,对应一条相反 UPDATE 的 undo log
保存位置:
•system tablespace (MySQL 5.7默认)
•undo tablespaces (MySQL 8.0默认)
回滚段(rollback segment)
redo log: 重做日志
•确保事务的持久性,防止事务提交后数据未刷新到磁盘就掉电或崩
溃。
•事务执行过程中写入 redo log,记录事务对数据页做了哪些修改。
•提升性能: WAL(Write-Ahead Logging) 技术, 先写日志, 再写磁盘。
•日志文件: ib_logfile0, ib_logfile1
•日志缓冲: innodb_log_buffer_size
•强刷: fsync()
注:
MVCC(多版本并发控制)
•使 InnoDB 支持一致性读: READ COMMITTED 和 REPEATABLE READ 。
•让查询不被阻塞、无需等待被其他事务持有的锁,这种技术手段可以增加并发性能。
•InnoDB 保留被修改行的旧版本。
•查询正在被其他事务更新的数据时,会读取更新之前的版本。
•每行数据都存在一个版本号, 每次更新时都更新该版本
•这种技术在数据库领域的使用并不普遍。 某些数据库, 以及某些 MySQL 存储引擎都不支持。
聚簇索引的更新 = 替换更新
二级索引的更新 = 删除+新建
MVCC 实现机制
•隐藏列
•事务链表, 保存还未提交的事务,事务提交则会从链表中摘除
//类似并发中AQS中的双向链表
•Read view: 每个 SQL 一个, 包括 rw_trx_ids, low_limit_id, up_limit_id, low_limit_no 等
•回滚段: 通过 undo log 动态构建旧版本数据
DB_TRX_ID:事务id;相当重要,带有时间戳,可以用来记录事务时间
实践事务与锁
先select * from lockt where id = 5 for update;
然后另一个线程中select * from lockt where id = 5
卡住了;
show engine innodb state \G
查看TRANSACTIONS
卡住的具体信息在这
rollback
select * from lockt where id between 5 and 10 for update;
卡住的原因变了
2.DB 与 SQL 优化*
例子分析
查询会根据if中的逻辑排列
说说 SQL 优化
如何发现需要优化的 SQL?
你了解的 SQL 优化方法有哪些?
SQL 优化有哪些好处?
实例分析 模拟一个需求:
需求版本1:
分析:
设计数据大小时,当前使用最大值多一点即可,否则积少成多会有很大的浪费。
压缩简要原理:数据库保存是文本文件,很多内容重复,如数据格式,各种关键字。
小结-1
-注意数据类型的选择
–设计表之前,通读DBA的指导手册/dbaprinciples
需求升级到版本2
需求-2
1.根据身份证号查询用户详细信息
2可根据用户名密码登陆
3.可统计当日新增用户个数
上图中的问题:
1.密码格式的不同在数据库中自动隐式转换,可能转换的字符串出现错误,并且错位的格式会导致不走索引
2.直接注入时密码可能在日志中泄露了
所以尽量加密,分别通过不同sql获得信息,不让用户名和密码出现在同一个SQL的日志中;获取用户输入时加密,从数据库读取也加密,然后比较加密后的值,日志中不会出现真实密码。
安全级别更高的话,考虑加密机,如使用非对称的加密,证书等进行安全性验证,数据库中没有强安全性信息。
小结-2
简单的SQL可能带来大的问题,where条件中注意数据类型,避免类型转换;隐私与安全
需求-3
系统经过一个月的运行,用户表增长约100万,DBA接到告警,CPU升高,查询越来越慢,请定位问题并给出解决方案
分析-3
定位问题的方法:
-慢查询日志
-看应用和运维的监控
分析慢SQL语句,查询时间是否超过阈值,SQL使用次数是否频繁=》越频繁优化的必要性越高,相当频繁的SQL哪怕没有超过阈值也值得优化。
深入分析:
索引的类型:
hash
B-Tree/B+Tree
用的比较少
B树和B+树的区别
1.B:每个节点都存储key和data,所有节点组成这棵树,并且叶子节点指针为null。
B+树:只有叶子节点存储data,叶子节点包含了这棵树的所有键值,叶子节点不存储指针。
B树每个块上同时储存索引和数据,快上容纳的索引量有限,可能导致层级变多,查询的性能不稳定
B+树除了叶子结点只有索引,层高可以比较低,同样大的块可以容纳较多的索引,查询速度比较快
2. 后来,在B+树上增加了顺序访问指针,也就是每个叶子节点增加一个指向相邻叶子节点的指针,这样一棵树成了数据库系统实现索引的首选数据结构。
b+更适合范围查询
引申:为什么主键要单调递增?
索引思考题
1.为什么不使用hash index
hash根据key的值做映射,完了是个链表,天然遍历不如树查询走的路径短
2.为什么b+tree更适合做索引
上文b与b+的比较
3.为什么主键长度不能过大
导致同一页上容纳的索引变少,导致数据库容量变小
4.
主键快,不需要回表
主键直接获得数据,二级获得对应数据的主键,再用主键查具体数据
f越接近1越适合做索引
修改表结构的危害
1.索引重建
2.锁表
3.抢占资源
4.主从延时
数据量
·业务初期考虑不周,字段类型使用不合理,需要变更数据类型
·随着业务的发展,需要增加新的字段(建议加拓展表)
·在无索引字段增加新的业务查询,需要增加索引
总结
总结1:写入优化
大批量写入的优化
PreparedStatement 减少 SQL 解析
Multiple Values/Add Batch 减少交互
Load Data,直接导入
索引和约束问题
总结2:数据更新
数据的范围更新
注意 GAP Lock 的问题
导致锁范围扩大
总结3:模糊查询
Like 的问题
前缀匹配
否则不走索引
全文检索,
solr/ES
总结4:连接查询
连接查询优化
驱动表的选择问题
避免笛卡尔积
总结5:索引失效
索引失效的情况汇总
NULL,not,not in,函数等
减少使用 or,可以用 union(注意 union all 的区别),以及前面提
到的like
大数据量下,放弃所有条件组合都走索引的幻想,出门左拐“全文检
索”
必要时可以使用 force index 来强制查询走某个索引
总结6:查询 SQL 到底怎么设计?
查询数据量和查询次数的平衡
避免不必须的大量重复数据传输
避免使用临时文件排序或临时表
分析类需求,可以用汇总表
3.常见场景分析*
怎么实现主键 ID
还有没有其他办法?
- 自增
- sequence
- 模拟 seq :取数后乘数,如取1,获得前1000个ID,预分配1000个id,使用完才再取。但是会导致ID不连续。
- UUID
- 时间戳/随机数
- snowflake 雪花
存在问题:可以从自增中估计出一段时间的订单数,存在数据泄露的危险。
高效分页
- 分页:count/pageSize/pageNum, 带条件的查询语句
- 常见实现-分页插件:使用查询 SQL,嵌套一个 count,性能的坑?
- 改进一下1,重写 count
- 大数量级分页的问题,limit 100000,20
- 改进一下2,反序
- 继续改进3,技术向:带 id,
- 继续改进4,需求向:非精确分页
- 所有条件组合? 索引?
- 全文检索
乐观锁与悲观锁
有什么区别?
select * from xxx for update
update xxx
commit;
意味着什么?
select * from xxx
update xxx where value=oldValue
为什么叫乐观锁