mysql相关知识点

题目-- https://www.cnblogs.com/williamjie/p/11081592.html
[mysql操作json]https://javastack.blog.csdn.net/article/details/120848578

日志

https://zhuanlan.zhihu.com/p/150105821?from_voters_page=true

逻辑架构

https://www.jb51.net/article/154417.html

1、连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2、服务层

management services & utilities	系统管理和控制工具
SQL Interface	sql接口，接受用户的sql命令，并且返回用户需要查询的结果
optimizer	查询优化器
cache 和Buffer	查询缓存

3、引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4、存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

大致的查询流程

mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。

然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

SQL性能

索引失效

关联查询太多join

服务器调优以及各个参数设置（缓存、线程数）

索引

官方定义：index是帮助mysql高效获取数据的数据结构。

本质

索引是数据结构，理解为“排好序的快速查找数据结构”

优势

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以索引也是要占空间的
• 虽然索引大大提高了查询速度，同时会降低更新表的速度，如对表的Insert，update，delete
• 索引需要根据业务变动

索引的分类

单值索引-一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：一个索引包含多个列

基本语法：

create unique index indexname on table tablename(colume(length));

有4种方式来添加数据表的索引：
alter table tb_name add primary key (column_list); 添加一个主键，索引值必须是唯一的，且不能为Null
alter table tb_name add unique index_name(column_list); 索引的值必须是唯一的，null值除外
alter table tb_name add index index_name(column_list); 添加普通索引，索引值可出现多次
alter table tb_name add fulltext index_name (column_list); 指定了索引为fulltext,用于全文索引

全文索引相关
https://blog.csdn.net/yygg329405/article/details/97110984

索引结构

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引
5. where 条件用不到的字段不创建索引
6. 在高并发下倾向于创建组合索引
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高
   排序速度
8. 查询中统计或者分组字段

哪些情况不需要创建索引

1. 表记录太少
2. 经常增删改的表
3. 某个数据列包含许多重复的内容(数据重复且分布平均的字段)，建立索没有太大的实际效果。一个索引的选择性越接近于1，这个索引的效率就越高

性能分析

explain
explain详解
如果是子查询。id的序号会递增,id值越大优先级越高，越先执行
DERIVED = 衍生

select _type

select type	注解
simple	简单的select 查询，查询中不包含子查询或者union
primary	查询中若包含任何复杂的子部分，最外层查询则被标记为primary
subquery	在select或where列表中包含子查询
dedrived	在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询，把结果放在临时表里
union	若第二个select 出现在Union之后，则被标记为union,若union包含在from子句的子查询中，外层select 将被标记为derived
Union result	从union表获取结果的select

type

• 访问类型
• 从最好到最差依次是 system>cons>eq_ref>ref>range>index>all

type	注解
system	表只有一行记录，这是const类型的特列
const	表示通过索引一次就找到了，如将主键置于where列表中
eq_ref	唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行，可能会找到多个符合条件的行，应该属于查找和扫描的混合体
range	检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在where语句中出现了between、<，>,in 等的查询，这种范围扫描比全表扫描要好，因为只需要开始于索引的某一个，结束于某一个点，不用扫描全部索引
index	全索引扫描
all	遍历全表，从硬盘中扫描全表

• 保证查询至少达到range级别

possible_keys

• 显示可能应用在这张表中的索引，一个或多个。但是不一定被查询使用
  key
• 实际使用的索引，如果为null，则没有使用索引
• 查询中若使用了覆盖索引，则索引仅出现在key列表中

key_len

• 索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref
显示索引的哪一列被使用了
rows
大致估算出找到所需记录需要读取的行数
extra

• using filesort,说明Mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，Mysql 中无法利用索引完成的排序操作称为“文件排序”
• using temporary ,使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和group by
• using index, 表示相应的select操作使用了覆盖索引，如果同时出现using where,表明索引被用来执行索引键值的查找，如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
• using where,
• using join bugger,使用了链接缓存
• impossible where
• select tables optimized away
• distinct

索引优化

• 全值匹配我最爱
• 最佳左前缀法则，如果索引了许多列，要遵守最左前缀法则，查询从索引的最左前列开始并且不跳过索引中的列
• 不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少select *
• mysql在使用不等于（！=或者<> ）的时候无法使用索引会导致全表扫描
• is null,is not null也无法使用索引
• like以通配符开头(’%abc…’)，MySQL索引失效会变成全表扫描的操作
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效
  备注：
  解决 like '%字符串%'索引不被使用的方法？？？----->覆盖索引

show profile

https://blog.csdn.net/gaoshan12345678910/article/details/78840158

mysqldumpslow

慢查询分析
https://zhuanlan.zhihu.com/p/106405711

MySQL锁进制

1. 从对数据操作德类型分

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

2. 从对数据操作的粒度

• 表锁
• 行锁
  读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都堵塞。

SQL:show status like ‘table%’;

Table_locks_immediate:表示立即释放表锁数
Table_locks_waited:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况

事务管理（ACID）

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务隔离级别

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

隔离级别	数据一致性	脏读	不可重复读	幻读
读未提交（read-uncommitted	最低级别	是	是	是
不可重复读（read-committed）	语句级	否	是	是
可重复读（repeatable-read）	事务级	否	否	是
串行化（serializable）	最高级别，事务级	否	否	否