MYSQL学习笔记

交集

等值匹配

主键和二级索引合并，主键列可以范围，

union合并 or

sort合并

索引合并

连接的本质就是笛卡尔积

驱动表和被驱动表 驱动表只查询一次，被驱动表可能反问多次 

连接最大成本就是IO 

嵌套循环连接

外连接  左外连接即左表为驱动表 反之亦然

join buffer 嵌套循环连接

最好不要写*

连接查询

explain format=json select 。。。。。 可以显示sql的成本

连接查询 ：单次查询驱动表的成本  多次查询被驱动表的成本

查询的成本 IO 、cpu in 为三个范围

执行原理

主从复制延迟怎么解决?

分库分表有什么规则？

读写分离怎么划分？

主键索引是聚簇索引，聚簇索引的叶子节点存全部数据

没有主键，mysql默认也会建立主键索引 rowid

聚簇索引 

不包含全部数据，叶子节点除了键值存放主键

回表：通过辅助索引进行查询到主键再查主键索引获取数据的过程就叫回表。 回表过多会放弃索引走全表扫描

复合索引：将多个列组合起来定义成索引 即 复合索引 。复合索引可以减少回表

覆盖索引：不指索引类型，指使用复合索引覆盖要查询的列，减少回表。

将选择性最高的列放到最前列

辅助索引

B+树的查询效率：依赖树的高度。 mysql一般是 3到4层 

前缀索引 ：针对 blob 、大varchar 建立前缀索引 前缀索引无法创建覆盖索引

索引将相关记录放在一起即相关记录是连续的则获得一星

索引的数据顺序与排序顺序一致

索引列包含了所有查询列

三星索引

B+树索引

INNODB针对热数据使用自适应哈希索引 mysql 5.7默认开启

哈希索引

全文索引

只为搜索、排序或者分组的列建立索引

索引的创建策略

隐藏索引 不会被优化器使用，不可见 。软删除、灰度发布

降序索引 

函数索引 新增一个列对用户不可见

mysql 8新的索引 

索引

系统设计：使用 es、mq、redis 读写分离、安全

架构调优

响应时间

扫描的行数和返回的行数

访问的类型

查询优化：慢查询核心原因：数据太多了。

慢查询配置 slow_query_log  long_query_time  log_queries_not_use_index

explain关键字

 id  标识执行几个查询  

table 表

SIMPLE   简单查询 不包括子查询或者union

PRIMARY  主查询

UNION  组合查询

UNIONRESULT  union的去重结果

SUBQUERY 子查询

select_type

system   const ：主键等值   eq_ref  连接表索引连接    ref  二级索引等值  fulltext  ref_or_null   index_merge  查询条件在多个索引上  unique_subquery 子查询关联主查询  index_subquery  range  范围索引  index 联合索引 all

一般保证到range 最好 ref

type

key_len  索引的长度 联合索引这个变值

ref  索引匹配的值

filtered 预估满足剩余条件的数据占比

extra 

慢查询分析

驱动表 ：连表查询时 查询出一张表的数据作为条件去查连接表的数据 则前者称为驱动表 后者为被驱动表

查询优化器 ： 缓存 ->解析查询 ->优化-> 执行查询

不要在索引上做操作、运算、函数

尽量全值匹配  --联合索引   最左原则

尽量不使用*  不等于要慎用  not null  慎用

字符类型加引号

like 主要不要双向模糊  

or 关键字会导致索引失效

索引使用

sql调优

硬件和os调优

调优

事务的特性  ACID

事务并发引起的问题 脏读 不可重复读 幻读 

事务的mysql参数 ： transcation_islation

保存点 savepoint

保证事务并发

版本链 ：row_id  undo.log ：链表形式 事务id

m_ids 活跃的事务id列表 （未提交）

readview  读视图

select的时候生效

mvcc 多版本并发机制

事务和事务的隔离级别

锁定读 ：读取当前版本，并且加锁阻塞其它事务修改，避免安全问题。 

例：for update为独占锁 lock in share mode 共享锁 

共享锁 s 锁  多个事务可共享一把锁

独占锁 x锁上锁后排他 独占锁与共享锁不兼容

元数据锁 alter drop 修改表的元数据  也会加锁

写操作锁  delete 获取X锁 先锁定读到数据然后锁定 insert 新增不需要加锁 

修改得时候所在的存储空间未发生变化 获取x锁

修改时存储空间发生变化，获取X 锁，删除然后再变化位置插入一条，插入前获取X锁

update锁

行锁 加在索引上。用到索引才能加行锁

表锁 lock tables  XX read；

意向锁 ：如果需要在某行加锁时在表级别加一个意向锁。简称IS 、IX ，没有行级意向锁

间隙锁  for update 上行锁在索引的前后上间隙锁 防止插入

锁粒度 跟存储引擎有关

锁参数 ： innodb_autoinc_lock_mode  1混合使用锁 2 全部使用轻量锁 0全部重量锁

死锁 两个或更多的进程事务 竞争资源，阻塞  mysql检测到死锁，或重启某个事务

锁

show performance_schema

events_statements_ sql 语句记录表

event_wait 等待事件

event_tran 事务相关

file_ 文件先关

最近执行失败的sql 事务的执行情况

performance_schema 监控mysql 运行中的资源消耗，采集的比较底层的性能记录  保存在内存

information_schema 

sys

mysql

mysql系统库

MYSQL