MYSQL学习笔记
2022-08-22 22:53:05 0 举报系统的列出了mysql的知识点，可提供解答
作者其他创作
大纲/内容
执行原理
索引合并
交集
等值匹配
主键和二级索引合并，主键列可以范围，
union合并 or
sort合并
连接查询
连接的本质就是笛卡尔积
驱动表和被驱动表 驱动表只查询一次，被驱动表可能反问多次
连接最大成本就是IO
嵌套循环连接
外连接 左外连接即左表为驱动表 反之亦然
join buffer 嵌套循环连接
最好不要写*
查询的成本 IO 、cpu in 为三个范围
explain format=json select 。。。。。 可以显示sql的成本
连接查询 ：单次查询驱动表的成本 多次查询被驱动表的成本
主从复制延迟怎么解决?
分库分表有什么规则？
读写分离怎么划分？
索引
B+树索引
聚簇索引
主键索引是聚簇索引，聚簇索引的叶子节点存全部数据
没有主键，mysql默认也会建立主键索引 rowid
辅助索引
不包含全部数据，叶子节点除了键值存放主键
回表：通过辅助索引进行查询到主键再查主键索引获取数据的过程就叫回表。 回表过多会放弃索引走全表扫描
复合索引：将多个列组合起来定义成索引 即 复合索引 。复合索引可以减少回表
覆盖索引：不指索引类型，指使用复合索引覆盖要查询的列，减少回表。
将选择性最高的列放到最前列
B+树的查询效率：依赖树的高度。 mysql一般是 3到4层
前缀索引 ：针对 blob 、大varchar 建立前缀索引 前缀索引无法创建覆盖索引
三星索引
索引将相关记录放在一起即相关记录是连续的则获得一星
索引的数据顺序与排序顺序一致
索引列包含了所有查询列
哈希索引
INNODB针对热数据使用自适应哈希索引 mysql 5.7默认开启
全文索引
索引的创建策略
只为搜索、排序或者分组的列建立索引
mysql 8新的索引
隐藏索引 不会被优化器使用，不可见 。软删除、灰度发布
降序索引
函数索引 新增一个列对用户不可见
调优
架构调优
系统设计：使用 es、mq、redis 读写分离、安全
sql调优
查询优化：慢查询核心原因：数据太多了。
响应时间
扫描的行数和返回的行数
访问的类型
慢查询配置 slow_query_log long_query_time log_queries_not_use_index
慢查询分析
explain关键字
id 标识执行几个查询
table 表
select_type
SIMPLE 简单查询 不包括子查询或者union
PRIMARY 主查询
UNION 组合查询
UNIONRESULT union的去重结果
SUBQUERY 子查询
type
system const ：主键等值 eq_ref 连接表索引连接 ref 二级索引等值 fulltext ref_or_null index_merge 查询条件在多个索引上 unique_subquery 子查询关联主查询 index_subquery range 范围索引 index 联合索引 all
一般保证到range 最好 ref
key_len 索引的长度 联合索引这个变值
ref 索引匹配的值
filtered 预估满足剩余条件的数据占比
extra
驱动表 ：连表查询时 查询出一张表的数据作为条件去查连接表的数据 则前者称为驱动表 后者为被驱动表
查询优化器 ： 缓存 ->解析查询 ->优化-> 执行查询
索引使用
不要在索引上做操作、运算、函数
尽量全值匹配 --联合索引 最左原则
尽量不使用* 不等于要慎用 not null 慎用
字符类型加引号
like 主要不要双向模糊
or 关键字会导致索引失效
硬件和os调优
事务和事务的隔离级别
事务的特性 ACID
事务并发引起的问题 脏读 不可重复读 幻读
事务的mysql参数 ： transcation_islation
保存点 savepoint
mvcc 多版本并发机制
保证事务并发
版本链 ：row_id undo.log ：链表形式 事务id
readview 读视图
m_ids 活跃的事务id列表 （未提交）
select的时候生效
锁
锁定读 ：读取当前版本，并且加锁阻塞其它事务修改，避免安全问题。
例：for update为独占锁 lock in share mode 共享锁
共享锁 s 锁 多个事务可共享一把锁
独占锁 x锁上锁后排他 独占锁与共享锁不兼容
元数据锁 alter drop 修改表的元数据 也会加锁
写操作锁 delete 获取X锁 先锁定读到数据然后锁定 insert 新增不需要加锁
update锁
修改得时候所在的存储空间未发生变化 获取x锁
修改时存储空间发生变化，获取X 锁，删除然后再变化位置插入一条，插入前获取X锁
锁粒度 跟存储引擎有关
行锁 加在索引上。用到索引才能加行锁
表锁 lock tables XX read；
意向锁 ：如果需要在某行加锁时在表级别加一个意向锁。简称IS 、IX ，没有行级意向锁
间隙锁 for update 上行锁在索引的前后上间隙锁 防止插入
锁参数 ： innodb_autoinc_lock_mode 1混合使用锁 2 全部使用轻量锁 0全部重量锁
死锁 两个或更多的进程事务 竞争资源，阻塞 mysql检测到死锁，或重启某个事务
mysql系统库
performance_schema 监控mysql 运行中的资源消耗，采集的比较底层的性能记录 保存在内存
show performance_schema
events_statements_ sql 语句记录表
event_wait 等待事件
event_tran 事务相关
file_ 文件先关
最近执行失败的sql 事务的执行情况
information_schema
sys
mysql
0 条评论
下一页
