MySQLSQL调优
2022-04-01 16:50:32 1 举报AI智能生成
为你推荐
查看更多
MySQL面试八股文
作者其他创作
大纲/内容
数据库事务代表一次完整的操作
全部成功、全部失败
A 原子性
开始和结束必须保持一致
C 一致性
事务相互隔离，中间状态不可见
I 隔离性
修改是永久的
D 持久性
ACID
原因：多个单元操作，并发执行会出现，脏读，不可重复读 幻读
读取的时候不加锁
未提交读（R-U）
读取时候加行级共享锁，一旦读完立刻释放，读取成功后释放
已提交读 (R-C)
事务默认级别， 读取时候加行锁，事务结束释放
可重复读 (R - R)
无并发可言 ，读取时候加表锁
串行化 (SE)
解决方案进行并发处理时候隔离
隔离级别
锁
生成redoLog，进行数据回滚
CAS 版本控制 + 读写分离
mvcc
实现（InnoDB）
数据库事务
范式
反范式
多加一些冗余，减少JOIN选择。以空间换时间的思想进行设计
其他注意点
范式和反范式
与业务无关、无意义的数字序列
代理主键（推荐）
事务属性中的自然唯一标识
自然主键
主键选择
数据库字符集
聚簇索引、支持事务、表锁、行锁、全文索引
InnoDB
非聚簇索引、不支持事务、不支持表锁、不支持行锁、使用大量select
MyISAM
数据存储在内存中
MEMORY
InnoDB 与 MyISAM 对比
存储引擎
适当拆分
schema与数据类型优化
explain select * from users
id号分为三种情况：1、如果id相同，那么执行顺序从上到下2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
ID 越大 越先执行
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询sample:简单的查询，不包含子查询和unionprimary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primaryunion:若第二个select出现在union之后，则被标记为uniondependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响union result:从union表获取结果的selectsubquery:在select或者where列表中包含子查询dependent subquery:subquery的子查询要受到外部表查询的影响DERIVED: from子句中出现的子查询，也叫做派生类，UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
select_type 查询类型
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
type 访问方式
possible_key 显示可能用到那些索引
key 实际用到索引
rows 预估行数
extra 额外信息 例如排序类型 、覆盖索引
执行计划
哈希表
B+树
了解常用数据结构 哈希表、二叉树、红黑树、B树、B+树
索引结构
随机IO变为顺序IO
优点
快速匹配where子句
MySQL会排除多余数据扫描，总会命中行数最少的索引
用途
主键
唯一
普通
全文
组合
分类
回表
覆盖索引
最左匹配
索引下推
技术名词
全值匹配
匹配最左前缀
匹配列前缀
匹配范围
精确匹配某一列并范围匹配另一列
只访问索引查询
索引匹配方式
索引基础知识
memory 存储引擎
基于哈希表实现，只有精确匹配才能被查找
只存储hash值，数据紧凑，索引非常快
只包含hash值，不进行字段存储，不是顺序存储，无法进行排序
不支持部分列存储
考虑哈希冲突情况，冲突比较多会造成数据列维护不方便，代价也很高
限制
当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大select id from url where url=\"\"也可以利用将url使用CRC32做哈希，可以使用以下查询方式：select id fom url where url=\"\" and url_crc=CRC32(\"\")此查询性能较高原因是使用体积很小的索引来完成查找
使用案例
hash索引
包含多个列作为索引，需要注意的是正确的顺序依赖改索引的查询，同事满足最左匹配原则
a
a = 3
a 、 b
a=3 and b=5
a=3 and b=4 and c=5
没使用索引
b=3 or c=4
a = 3 and c=4
a = 3 and b>10 and c = 7
a = 3 and b like '%1%' and c = 7
组合索引
可以把相关数据保存在一起
数据访问更快，不需要另外查找
覆盖索引扫描可以直接到叶节点
聚簇数据极大的现在了数据IO的扩展性
插入速度验证依赖插入书序，按照主键插入顺序最快
更新聚簇索引列代价很高，会强制将每个更新的行移动到新的位置
新插入行如果超过索引页大小，会导致页分裂的问题
全表扫描变慢
缺点
聚簇索引
非聚簇索引
聚簇索引与非聚簇索引
如果一个索引包含所有需要查询的字段的值，称之为覆盖索引
不是所有类型的索引都成为覆盖索引，覆盖索引必须要存储索引列的值
不同存储引擎实现索引的方式不同，不是所有引擎都支持覆盖索引，memory不支持
介绍
索引条目通常小于数据行大小，如果只需要读取索引，会极大的减少数据访问
IO密集型
索引按照顺序存储的，对IO密集型范围查找会比随机查找快很多
InnoDB是聚簇索引，覆盖索引效率特别高
优势
案例
索引列进行扫描时，尽量不要用表达式，将计算放到业务层而不是数据层
尽量使用主键索引，避免发生回表
使用前缀索引
使用索引扫描进行排序
范围列可以用到索引 但是只能用一列索引
强制转换可能会导致全表扫描
更新十分频繁的操作不宜建立索引
索引列不能为空
表进行连接时候，最好不要超过三张表，因为join需要的字段数据类型必须一致
能使用limit时候尽量使用limit limit 限制输出
单表索引建议控制在5个以内
组合索引字段不应该超过5个
优化细节
索引监控
索引优化简单案例
索引优化
网络
CPU
IO
上下文切换
系统调用
生成统计信息
1：分析存储引擎
2：读锁、写锁
锁等待时间
查询慢的原因
大数据量
加载多余数据
优化数据访问
查询缓存
执行过程的优化
重新定义关联查询顺序
外连接转内连接
等价变换规则、简化SQL
带条件 ， 索引分组
优化 count min max
索引覆盖扫描
子查询进行优化、进行缓存
两个列的值关联，MySQL可以进行参数传播
等值传播
MySQL 优化机制
第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
两次传输排序
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据
一次传输排序
当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式
算法优化
排序优化
查询优化
分区表
服务器参数设置
MySQL集群
事务具有隔离性
单一版本控制
读未提交
读已提交
默认
可重复读
串行化
InnoDB存储引擎默认一个数据页大小为16kb，非叶子节点存放（key，pointer），pointer为6个字节，key为4个字节，即非叶子节点能存放16kb/14左右的key，pointer，而叶子节点如果一条数据大小为100字节，那一个叶子节点大约可存放160条数据。如果高度为3，则可存放数据为：16kb/14 * 16kb/14 * 160大约1亿多数据。因此InnoDB存储引擎b+树的高度基本为2-3.
高度计算
面试
CRUD 操作 -- 写入LOG-Buffer
更新操作
redo-log
原子性、执行前的状态 ， 用于记录回滚
undo-log
服务端日志文件
bin-log
log
主 -- 从 进行 log复制
1：主库 binlog 日志生成后进行网络传输2：从库将binlog 日志落盘 SQL 解析3：5.7 以后 IO线程是多线程，从库SQL解析是串行化解析的，延迟非常高 relaylog
主库 1000/s 操作以后会造成延迟1 、 2 ns 的延迟
必须同步到从库，才操作成功
semi sync半同步复制
SQL 线程开启并行复制
分库 、 削峰分多个库，开启并行复制代码、重新设计
show status ；seconds_behind_master 查看当前落后从库的状态 ms
问题解决
主从复制
slow_query_log -- on 打开慢查询
show_query_log_file -- 默认记录文件
log_query_time -- 10S 超过执行时间会被记录
开启慢查询日志
type 索引类型
explain 分析sql
联合索引最左匹配原则
慢查询查看
SQL调优
0 条评论
回复 删除
下一页
