数据库优化知识小结(二)

1. 数据库调优维度

数据库调优的目的: 让数据库运行的更快,相应时间更快, 吞吐量更大

  • 用户的反馈----> 找到数据相关问题
  • 日志的分析-----> 日志和异常定位问题
  • 服务器资源的监控------> CPU / 内存 / I/O等
  • 数据库内部监控-----> (Active Session) 监控会话,是否繁忙/SQL堆积
  • 第一步: 选择合适DBMS (MYSQL /ORACLE / NOSQL …)
  • 第二步: 优化表设计: (表结构的范式设计原则)
  • 第三步: 优化逻辑查询 ( 对SQL语句的等价重写)— > 包括子查询优化/等价谓词重写/视图重写/条件简化/连接消除/嵌套连接消除等
    比如 EXISTS 与 in的使用, 需要小表驱动大表, (2个for循环 )
  • 第四步: 优化物理查询: 可以通过建立索引等方式
  • 第五步: 使用Redis 或者memcached 做缓存
  • 第六步: 库级优化

主从 配置 (主写从读)
垂直切分: 数据表过多将关联的表部署在同一个库上,
表的列过多, 可对列进行垂直切分, 分成多个小表
水平切分: 表数据过多, 可把表数据按照哦年份等某些字段拆分为表结构相同,的多个表进行存储

2. 范式设计

数据库设计范式

1NF (第一范式) 2NF, 3NF BCNF(巴斯-科德范式) 4NF 5NF
范式要求逐级提升
1NF:列的原子性,不可拆分
2NF:针对于联合主键,非主属性完全依赖于联合主键,而非部分
3NF:非主属性只能直接依赖于主键

反范式设计

会造成数据的冗余, 但是减少了表与表之间跨幅查询时间, 可以说是用空间换时间吧 , 有时候设计表的时候我们有必要这么做的

3. 索引的使用

什么情况下创建索引? 什么情况下不需要索引?

索引能帮我们提高查询的效率, 但是索引不是万能的, 有些情况下使用索引反而会影响查询效率

  1. 数据行少的情况下, 使用索引与不使用的却别不大
  2. 重复性大的字段,且数据量超过总数据量10%以上可不需要创建索引; 因为索引只是记录原始数据的位置, 使用的普通索引的话, 比如100W数据, 使用普通索引会去遍历普通索引N次, 然后数据N次
    如果重复性低的话, 遍历的次数少,可用
  3. 索引会占用存储空间,降低写操作的性能,多个索引还会增加索引选择的时间等
索引的种类

索引的目的: 定位想要查找的数据: 好比字典: 有字典目录

  1. 功能上分: 普通索引 / 唯一索引 / 主键索引 / 全文索引
    普通索引无限制—> 唯一索引 数据唯一—>主键索引 不为空唯一----> 全文索引一般可使用solr或者ES
  2. 物理方式分: 聚集索引和非聚集索引
    聚集索引: 按照主键来排序存储数据, 在索引后直接跟内容
    非聚集索引: 二级索引或者辅助索引: 会有单独的空间存储非聚集索引: 这些索引项是有序的, 但是索引项对应的内容位置顺序不确定
  3. 字段个数分: 单一索引, 和联合索引
    在使用联合索引的时候, 顺序位置的不同可能存在效率差异,联合索引存在最左匹配原则
  4. 一个表只能有一个聚集索引(只有一种排序存储的方式–>存在文件上) ,可以有多个非聚集索引,通过他们来查找数据
  5. 使用聚集索引查询效率高,增删改, 慢 ,有索引的更新操作
索引使用位置
  1. 在where子句后使用索引字段 ,可提升查询效率
  2. 采用聚集索引查询数据比非聚集索引效率高些

4. 索引的原理 (B+树)

索引的数据结构设计?

数据库存储服务: 硬盘 与 内存

  1. 内存读取速度快, 但是是临时的; 硬盘相对慢, 但是数据时持久性的
  2. 索引建立在硬盘上的, 通过索引查找数据的时候, 会产生磁盘的I/O读写, 如果读写次数越大则耗时间越长
二叉树的局限性

二叉树:

  1. 子节点小于父节点 放在左侧
  2. 子节点大于父节点放在右侧
    缺点: 如果树为一个链表了. 复杂度增加了 查找树节点次数增加

平衡二叉树:

  1. 满足上诉条件, 左子树和右子树 高度差不能超过1
    如: 树的高度是5, 每访问一次节点就需要一次I/O读写, 如果树的深度过高, 则会影响查询效率(查找树的复杂度增大)

平衡 M 叉树 – > 会降低树的高度

什么是B树

一个M阶的B树:

  1. 父节点的子节点树在[2,m] 个
  2. 每一个中间节点包含 k-1 个关键字和k个子节点 , 子节点个数: = 关键字的数量+1 ; k的取值范围[ceil(M/2),M]
  3. 叶子节点包括k-1个关键字 (叶子节点没有子节点), k的取值范围你[ceil(M/2),M]
  4. 通过指针—>指向下一个子节点, 每一个子节点块 有不同的指针,分别指向不同的子节点---->
    A —>B,C ; B—>D,F; C—>E,G ; D—> …
什么是B+树

在这里插入图片描述

5.Hash索引

Hash本身是一个函数, 散列函数,可以提升数据检索的效率
1,Hash索引有很大的限制,如联合索引、模糊查询、范围查询,以及列里有重复值多。
2,需要遍历链表中所有行指针,逐一进行比较,直到找到所有符合条件的

6. 通过索引提升查询效率

创建索引的地方
  1. 字段数值 有唯一性 限制的字段
  2. 频繁作为WHERE查询条件的字段, (在大数据量的情况下提升效率)
  3. 需要经常GROUP BY 或者 ORDER BY 的字段后面
  4. UPDATE 或者DELETE 的条件列 也会经常需要添加索引
  5. 对于DISTINCT字段,去重 有时候我们 也需要创建索引来提高查询效率
  6. 做多表 JOIN 的时候(表最好不要超过3张), 连接字段 或者WHERE后字段做索引过滤
不适合创建索引的地方
  1. 在WHERE 或者(GROUP BY ORDER BY ) 之后没有用到的字段, 建立索引也白搭
  2. 表的记录太少, 无需建立索引,没有太大的明显效果
  3. 字段中有大量的重复数据, 需视情况而定
  4. 频繁更新的字段,无需, 因为会更新索引,也需要消耗时间
索引失效的地方
  1. 如果索引列使用了表达式计算会失效
  2. 如果对索引使用了函数会失效
  3. 在WHERE 子句中如果使用了OR , 只要有一方没有使用索引, 则会失效(进行全部扫)
  4. 当使用LIKE 进行模糊匹配的时候, 不能以%开头进行匹配,否则失效
  5. 在使用联合索引时, 注意最左原则,使用的是最左侧的列

7.数据库数据存储

数据库中的存储结构
  1. 数据库管理存储的 单位是 页 (Page)
    2.一个页中存储着多个行数据
  2. 由 行 ----> 页--------------> 区-------------------> 段--------------------------------> 表空间
  3. 一个表空间包括多个段, 一个段包括多个区, 一个区包括多个页,一个页包括多行数据
数据库缓冲池
  1. InnoDB 查看缓冲池大小
    show variables like ‘innodb_buffer_pool_size’
  2. 设置缓冲池大小
    set global innodb_buffer_pool_size = ‘大小’
  3. 查看缓冲池的个数
    show variables like ‘innodb_buffer_pool_instance’
  4. 统计SQL的查询成本
    show status like ‘last_qurey_cost’

8.悲观锁和乐观锁

锁划分
  1. 按照锁的粒度划分
    锁分为: 行锁 页锁 表锁
  2. 在表上加锁(只读)
    LOCK TABLE 表名 READ;
  3. 解除锁
    UNLOCK TABLE;
  4. 排它锁(独占锁,需要等待自己释放后,其他人才可以使用)
    LOCK TABLE 表名 WRITE
  5. 释放锁
    UNLOCK TABLE
  6. 乐观锁
    不同过数据库的锁机制来实现, 通过程序来控制(版本号/时间戳)
    多用于读操作
  7. 悲观锁
    排他锁/表锁/行锁/ 多用于写操作
  8. 死锁
    多个事务竞争相同的资源, 造成资源阻塞

9. 查询优化器是如何工作的

  • SQL 执行环节
    在这里插入图片描述

10. 使用性能分析工具的定位SQL执行慢的原因

在这里插入图片描述
以上是一个调优的流程思路,

  • SQL调优3步骤
  1. 慢查询
  2. EXPLAIN
  3. SHOW PROFILE
使用慢查询定位查询慢的SQL
  1. 查看慢查询日志是否开启:
    mysql > show variables like ‘%slow_query_log’;
    mysql > set global slow_query_log=‘ON’; --开启
  2. 查看慢查询的时间阀值
    mysql > show variables like ‘%long_query_time%’;
    mysql > set global long_query_time = 3; – 修改时间阀值为3秒
  3. mysql工具 mysqldumpslow 统计慢查询日志爱
  • mysqldumpslow 具体参数:
    -s : 采用order排序, c(访问次数) t (查询时间) l (锁定时间) r (返回记录) ac(平均查询次数) al(平均锁定时间) ar (平均返回记录数) at(平均查询时间) , at是-s的默认
    -t : 返回前N条数据
    -g : 后面可以是正则表达式, 大小写不敏感
  1. eg
    perl mysqldumpslow.pl -s t -t 2 “D:\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log”

开启了慢查询日志, 并设置了慢查询时间的阀值, 只要查询时间大于了该阀值,SQL就会保存在慢查询日志中, 通过mysqldumpslow工具就可抓取该SQL信息

使用EXPLAIN查看执行计划

定位了慢查询SQL , 就可通过EXPLAIN针对性的分析

EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name FROM product_comment JOIN user on product_comment.user_id = user.user_id
在这里插入图片描述

  • explain 的作用

了解数据表的读取顺序
SELECT子句的类型
数据表的访问类型
可使用的索引
实际使用的索引
使用索引的长度
上一个表的连接匹配条件
被优化器查询的行的数量以及额外的信息(外部排序/临时索引等)

  • 数据类型type列对应的信息
    在这里插入图片描述
    除了 all 类型外,其他类型都可以使用到索引,但是不同的连接方式的效率也会有所不同,效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system。我们在查看执行计划的时候,通常希望执行计划至少可以使用到 range 级别以上的连接方式,如果只使用到了 all 或者 index 连接方式,我们可以从 SQL 语句和索引设计的角度上进行改进。
使用 SHOW PROFILE 查看 SQL 的具体执行成本

mysql > show variables like ‘profiling’; – 开启回话级别; 比EXPLAIN 更进一步
mysql > set profiling = ‘ON’; – 开启
mysql > show profiles; – 查看当前会话有哪些
mysql > show profile; – 查看上一次会话
show profile 中可以查看不同部分的开销 比如 cpu / io
show profile for query 2 – 通过query ID 查看

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值